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"; |