prosody
deb68066c7aa
plugins/mod_storage_sql.lua

Find changesets by keywords by author, files, the commit message, revision number or hash, or revset expression.

Software / code / prosody

Comparison

plugins/mod_storage_sql.lua @ 10020:deb68066c7aa

mod_storage_sql: Look up archive IDs in separate queries (fixes #1325) This is probably not good for performance.
author Kim Alvefur <zash@zash.se>
date Sat, 23 Mar 2019 00:51:10 +0100
parent 10017:994cccebb597
child 10031:17c175ad65f9
comparison
equal deleted inserted replaced
10019:c30c81176752 10020:deb68066c7aa
325 where[#where+1] = "\"key\" = ?"; 325 where[#where+1] = "\"key\" = ?";
326 args[#args+1] = query.key 326 args[#args+1] = query.key
327 end 327 end
328 end 328 end
329 local function archive_where_id_range(query, args, where) 329 local function archive_where_id_range(query, args, where)
330 local args_len = #args
331 -- Before or after specific item, exclusive 330 -- Before or after specific item, exclusive
331 local id_lookup_sql = [[
332 SELECT "sort_id"
333 FROM "prosodyarchive"
334 WHERE "key" = ? AND "host" = ? AND "user" = ? AND "store" = ?
335 LIMIT 1;
336 ]];
332 if query.after then -- keys better be unique! 337 if query.after then -- keys better be unique!
333 where[#where+1] = [[ 338 local after_id = nil;
334 "sort_id" > COALESCE( 339 for row in engine:select(id_lookup_sql, query.after, host, user or "", store) do
335 ( 340 after_id = row[1];
336 SELECT "sort_id" 341 end
337 FROM "prosodyarchive" 342 if not after_id then
338 WHERE "key" = ? AND "host" = ? AND "user" = ? AND "store" = ? 343 return nil, "item-not-found";
339 LIMIT 1 344 end
340 ), 0) 345 where[#where+1] = '"sort_id" > ?';
341 ]]; 346 args[#args+1] = after_id;
342 args[args_len+1], args[args_len+2], args[args_len+3], args[args_len+4] = query.after, args[1], args[2], args[3];
343 args_len = args_len + 4
344 end 347 end
345 if query.before then 348 if query.before then
346 where[#where+1] = [[ 349 local before_id = nil;
347 "sort_id" < COALESCE( 350 for row in engine:select(id_lookup_sql, query.after, host, user or "", store) do
348 ( 351 before_id = row[1];
349 SELECT "sort_id" 352 end
350 FROM "prosodyarchive" 353 if not before_id then
351 WHERE "key" = ? AND "host" = ? AND "user" = ? AND "store" = ? 354 return nil, "item-not-found";
352 LIMIT 1 355 end
353 ), 356 where[#where+1] = '"sort_id" < ?';
354 ( 357 args[#args+1] = before_id;
355 SELECT MAX("sort_id")+1 358 end
356 FROM "prosodyarchive" 359 return true;
357 )
358 )
359 ]]
360 args[args_len+1], args[args_len+2], args[args_len+3], args[args_len+4] = query.before, args[1], args[2], args[3];
361 end
362 end 360 end
363 361
364 function archive_store:find(username, query) 362 function archive_store:find(username, query)
365 query = query or {}; 363 query = query or {};
366 local user,store = username,self.store; 364 local user,store = username,self.store;
396 if query.limit == 0 then -- Skip the real query 394 if query.limit == 0 then -- Skip the real query
397 return noop, total; 395 return noop, total;
398 end 396 end
399 end 397 end
400 398
401 archive_where_id_range(query, args, where); 399 local ok, err = archive_where_id_range(query, args, where);
400 if not ok then return ok, err; end
402 401
403 if query.limit then 402 if query.limit then
404 args[#args+1] = query.limit; 403 args[#args+1] = query.limit;
405 end 404 end
406 405
464 if user == true then 463 if user == true then
465 table.remove(args, 2); 464 table.remove(args, 2);
466 table.remove(where, 2); 465 table.remove(where, 2);
467 end 466 end
468 archive_where(query, args, where); 467 archive_where(query, args, where);
469 archive_where_id_range(query, args, where); 468 local ok, err = archive_where_id_range(query, args, where);
469 if not ok then return ok, err; end
470 if query.truncate == nil then 470 if query.truncate == nil then
471 sql_query = sql_query:format(t_concat(where, " AND ")); 471 sql_query = sql_query:format(t_concat(where, " AND "));
472 else 472 else
473 args[#args+1] = query.truncate; 473 args[#args+1] = query.truncate;
474 local unlimited = "ALL"; 474 local unlimited = "ALL";