prosody
6aaa7ad4463c
plugins/mod_storage_sql2.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_sql2.lua @ 5847:6aaa7ad4463c

mod_storage_sql2: Split out code for building WHERE clauses into separate functions
author Kim Alvefur <zash@zash.se>
date Sat, 28 Sep 2013 21:58:01 +0200
parent 5776:bd0ff8ae98a8
child 5848:06156bfd4eaf
comparison
equal deleted inserted replaced
5846:b8c7656481ac 5847:6aaa7ad4463c
254 local t, value = serialize(value); 254 local t, value = serialize(value);
255 engine:insert("INSERT INTO `prosodyarchive` (`host`, `user`, `store`, `when`, `with`, `key`, `type`, `value`) VALUES (?,?,?,?,?,?,?,?)", host, user or "", store, when, with, key, t, value); 255 engine:insert("INSERT INTO `prosodyarchive` (`host`, `user`, `store`, `when`, `with`, `key`, `type`, `value`) VALUES (?,?,?,?,?,?,?,?)", host, user or "", store, when, with, key, t, value);
256 return key; 256 return key;
257 end); 257 end);
258 end 258 end
259
260 -- Helpers for building the WHERE clause
261 local function archive_where(query, args, where)
262 -- Time range, inclusive
263 if query.start then
264 args[#args+1] = query.start
265 where[#where+1] = "`when` >= ?"
266 end
267
268 if query["end"] then
269 args[#args+1] = query["end"];
270 if query.start then
271 where[#where] = "`when` BETWEEN ? AND ?" -- is this inclusive?
272 else
273 where[#where+1] = "`when` >= ?"
274 end
275 end
276
277 -- Related name
278 if query.with then
279 where[#where+1] = "`with` = ?";
280 args[#args+1] = query.with
281 end
282
283 -- Unique id
284 if query.key then
285 where[#where+1] = "`key` = ?";
286 args[#args+1] = query.key
287 end
288 end
289 local function archive_where_id_range(query, args, where)
290 -- Before or after specific item, exclusive
291 if query.after then -- keys better be unique!
292 where[#where+1] = "`sort_id` > (SELECT `sort_id` FROM `prosodyarchive` WHERE `key` = ? LIMIT 1)"
293 args[#args+1] = query.after
294 end
295 if query.before then
296 where[#where+1] = "`sort_id` < (SELECT `sort_id` FROM `prosodyarchive` WHERE `key` = ? LIMIT 1)"
297 args[#args+1] = query.before
298 end
299 end
300
259 function archive_store:find(username, query) 301 function archive_store:find(username, query)
260 query = query or {}; 302 query = query or {};
261 local user,store = username,self.store; 303 local user,store = username,self.store;
262 local total; 304 local total;
263 local ok, result = engine:transaction(function() 305 local ok, result = engine:transaction(function()
264 local sql_query = "SELECT `key`, `type`, `value`, `when` FROM `prosodyarchive` WHERE %s ORDER BY `sort_id` %s%s;"; 306 local sql_query = "SELECT `key`, `type`, `value`, `when` FROM `prosodyarchive` WHERE %s ORDER BY `sort_id` %s%s;";
265 local args = { host, user or "", store, }; 307 local args = { host, user or "", store, };
266 local where = { "`host` = ?", "`user` = ?", "`store` = ?", }; 308 local where = { "`host` = ?", "`user` = ?", "`store` = ?", };
267 309
268 -- Time range, inclusive 310 archive_where(query, args, where);
269 if query.start then
270 args[#args+1] = query.start
271 where[#where+1] = "`when` >= ?"
272 end
273 if query["end"] then
274 args[#args+1] = query["end"];
275 if query.start then
276 where[#where] = "`when` BETWEEN ? AND ?" -- is this inclusive?
277 else
278 where[#where+1] = "`when` >= ?"
279 end
280 end
281
282 -- Related name
283 if query.with then
284 where[#where+1] = "`with` = ?";
285 args[#args+1] = query.with
286 end
287
288 -- Unique id
289 if query.key then
290 where[#where+1] = "`key` = ?";
291 args[#args+1] = query.key
292 end
293 311
294 -- Total matching 312 -- Total matching
295 if query.total then 313 if query.total then
296 local stats = engine:select(sql_query:gsub("^(SELECT).-(FROM)", "%1 COUNT(*) %2"):format(t_concat(where, " AND "), "DESC", ""), unpack(args)); 314 local stats = engine:select(sql_query:gsub("^(SELECT).-(FROM)", "%1 COUNT(*) %2"):format(t_concat(where, " AND "), "DESC", ""), unpack(args));
297 if stats then 315 if stats then
301 if query.limit == 0 then -- Skip the real query 319 if query.limit == 0 then -- Skip the real query
302 return noop, total; 320 return noop, total;
303 end 321 end
304 end 322 end
305 323
306 -- Before or after specific item, exclusive 324 archive_where_id_range(query, args, where);
307 if query.after then
308 where[#where+1] = "`sort_id` > (SELECT `sort_id` FROM `prosodyarchive` WHERE `key` = ? LIMIT 1)"
309 args[#args+1] = query.after
310 end
311 if query.before then
312 where[#where+1] = "`sort_id` < (SELECT `sort_id` FROM `prosodyarchive` WHERE `key` = ? LIMIT 1)"
313 args[#args+1] = query.before
314 end
315 325
316 if query.limit then 326 if query.limit then
317 args[#args+1] = query.limit; 327 args[#args+1] = query.limit;
318 end 328 end
319 329