prosody
6c3cae9b96cb
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 @ 8033:6c3cae9b96cb

mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
author Kim Alvefur <zash@zash.se>
date Sat, 01 Apr 2017 19:26:27 +0200
parent 8032:aa9f198cb3c9
child 8034:149553feb04e
comparison
equal deleted inserted replaced
8032:aa9f198cb3c9 8033:6c3cae9b96cb
55 local user, store; 55 local user, store;
56 56
57 local function keyval_store_get() 57 local function keyval_store_get()
58 local haveany; 58 local haveany;
59 local result = {}; 59 local result = {};
60 for row in engine:select("SELECT `key`,`type`,`value` FROM `prosody` WHERE `host`=? AND `user`=? AND `store`=?", host, user or "", store) do 60 local select_sql = [[
61 SELECT `key`,`type`,`value`
62 FROM `prosody`
63 WHERE `host`=? AND `user`=? AND `store`=?;
64 ]]
65 for row in engine:select(select_sql, host, user or "", store) do
61 haveany = true; 66 haveany = true;
62 local k = row[1]; 67 local k = row[1];
63 local v = deserialize(row[2], row[3]); 68 local v = deserialize(row[2], row[3]);
64 if k and v then 69 if k and v then
65 if k ~= "" then result[k] = v; elseif type(v) == "table" then 70 if k ~= "" then result[k] = v; elseif type(v) == "table" then
72 if haveany then 77 if haveany then
73 return result; 78 return result;
74 end 79 end
75 end 80 end
76 local function keyval_store_set(data) 81 local function keyval_store_set(data)
77 engine:delete("DELETE FROM `prosody` WHERE `host`=? AND `user`=? AND `store`=?", host, user or "", store); 82 local delete_sql = [[
78 83 DELETE FROM `prosody`
84 WHERE `host`=? AND `user`=? AND `store`=?
85 ]];
86 engine:delete(delete_sql, host, user or "", store);
87
88 local insert_sql = [[
89 INSERT INTO `prosody`
90 (`host`,`user`,`store`,`key`,`type`,`value`)
91 VALUES (?,?,?,?,?,?);
92 ]]
79 if data and next(data) ~= nil then 93 if data and next(data) ~= nil then
80 local extradata = {}; 94 local extradata = {};
81 for key, value in pairs(data) do 95 for key, value in pairs(data) do
82 if type(key) == "string" and key ~= "" then 96 if type(key) == "string" and key ~= "" then
83 local t, encoded_value = assert(serialize(value)); 97 local t, encoded_value = assert(serialize(value));
84 engine:insert("INSERT INTO `prosody` (`host`,`user`,`store`,`key`,`type`,`value`) VALUES (?,?,?,?,?,?)", host, user or "", store, key, t, encoded_value); 98 engine:insert(delete_sql, host, user or "", store, key, t, encoded_value);
85 else 99 else
86 extradata[key] = value; 100 extradata[key] = value;
87 end 101 end
88 end 102 end
89 if next(extradata) ~= nil then 103 if next(extradata) ~= nil then
90 local t, encoded_extradata = assert(serialize(extradata)); 104 local t, encoded_extradata = assert(serialize(extradata));
91 engine:insert("INSERT INTO `prosody` (`host`,`user`,`store`,`key`,`type`,`value`) VALUES (?,?,?,?,?,?)", host, user or "", store, "", t, encoded_extradata); 105 engine:insert(insert_sql, host, user or "", store, "", t, encoded_extradata);
92 end 106 end
93 end 107 end
94 return true; 108 return true;
95 end 109 end
96 110
113 return keyval_store_set(data); 127 return keyval_store_set(data);
114 end); 128 end);
115 end 129 end
116 function keyval_store:users() 130 function keyval_store:users()
117 local ok, result = engine:transaction(function() 131 local ok, result = engine:transaction(function()
118 return engine:select("SELECT DISTINCT `user` FROM `prosody` WHERE `host`=? AND `store`=?", host, self.store); 132 local select_sql = [[
133 SELECT DISTINCT `user`
134 FROM `prosody`
135 WHERE `host`=? AND `store`=?;
136 ]];
137 return engine:select(select_sql, host, self.store);
119 end); 138 end);
120 if not ok then return ok, result end 139 if not ok then return ok, result end
121 return iterator(result); 140 return iterator(result);
122 end 141 end
123 142
127 local map_store = {}; 146 local map_store = {};
128 map_store.__index = map_store; 147 map_store.__index = map_store;
129 map_store.remove = {}; 148 map_store.remove = {};
130 function map_store:get(username, key) 149 function map_store:get(username, key)
131 local ok, result = engine:transaction(function() 150 local ok, result = engine:transaction(function()
151 local query = [[
152 SELECT `type`, `value`
153 FROM `prosody`
154 WHERE `host`=? AND `user`=? AND `store`=? AND `key`=?
155 LIMIT 1
156 ]];
132 local data; 157 local data;
133 if type(key) == "string" and key ~= "" then 158 if type(key) == "string" and key ~= "" then
134 for row in engine:select("SELECT `type`, `value` FROM `prosody` WHERE `host`=? AND `user`=? AND `store`=? AND `key`=? LIMIT 1", host, username or "", self.store, key) do 159 for row in engine:select(query, host, username or "", self.store, key) do
135 data = deserialize(row[1], row[2]); 160 data = deserialize(row[1], row[2]);
136 end 161 end
137 return data; 162 return data;
138 else 163 else
139 for row in engine:select("SELECT `type`, `value` FROM `prosody` WHERE `host`=? AND `user`=? AND `store`=? AND `key`=? LIMIT 1", host, username or "", self.store, "") do 164 for row in engine:select(query, host, username or "", self.store, "") do
140 data = deserialize(row[1], row[2]); 165 data = deserialize(row[1], row[2]);
141 end 166 end
142 return data and data[key] or nil; 167 return data and data[key] or nil;
143 end 168 end
144 end); 169 end);
149 if data == nil then data = self.remove; end 174 if data == nil then data = self.remove; end
150 return self:set_keys(username, { [key] = data }); 175 return self:set_keys(username, { [key] = data });
151 end 176 end
152 function map_store:set_keys(username, keydatas) 177 function map_store:set_keys(username, keydatas)
153 local ok, result = engine:transaction(function() 178 local ok, result = engine:transaction(function()
179 local delete_sql = [[
180 DELETE FROM `prosody`
181 WHERE `host`=? AND `user`=? AND `store`=? AND `key`=?;
182 ]];
183 local insert_sql = [[
184 INSERT INTO `prosody`
185 (`host`,`user`,`store`,`key`,`type`,`value`)
186 VALUES (?,?,?,?,?,?);
187 ]];
188 local select_extradata_sql = [[
189 SELECT `type`, `value`
190 FROM `prosody`
191 WHERE `host`=? AND `user`=? AND `store`=? AND `key`=?
192 LIMIT 1;
193 ]];
154 for key, data in pairs(keydatas) do 194 for key, data in pairs(keydatas) do
155 if type(key) == "string" and key ~= "" then 195 if type(key) == "string" and key ~= "" then
156 engine:delete("DELETE FROM `prosody` WHERE `host`=? AND `user`=? AND `store`=? AND `key`=?", 196 engine:delete(delete_sql,
157 host, username or "", self.store, key); 197 host, username or "", self.store, key);
158 if data ~= self.remove then 198 if data ~= self.remove then
159 local t, value = assert(serialize(data)); 199 local t, value = assert(serialize(data));
160 engine:insert("INSERT INTO `prosody` (`host`,`user`,`store`,`key`,`type`,`value`) VALUES (?,?,?,?,?,?)", host, username or "", self.store, key, t, value); 200 engine:insert(insert_sql, host, username or "", self.store, key, t, value);
161 end 201 end
162 else 202 else
163 local extradata = {}; 203 local extradata = {};
164 for row in engine:select("SELECT `type`, `value` FROM `prosody` WHERE `host`=? AND `user`=? AND `store`=? AND `key`=? LIMIT 1", host, username or "", self.store, "") do 204 for row in engine:select(select_extradata_sql, host, username or "", self.store, "") do
165 extradata = deserialize(row[1], row[2]); 205 extradata = deserialize(row[1], row[2]);
166 end 206 end
167 engine:delete("DELETE FROM `prosody` WHERE `host`=? AND `user`=? AND `store`=? AND `key`=?", 207 engine:delete(delete_sql, host, username or "", self.store, "");
168 host, username or "", self.store, "");
169 extradata[key] = data; 208 extradata[key] = data;
170 local t, value = assert(serialize(extradata)); 209 local t, value = assert(serialize(extradata));
171 engine:insert("INSERT INTO `prosody` (`host`,`user`,`store`,`key`,`type`,`value`) VALUES (?,?,?,?,?,?)", host, username or "", self.store, "", t, value); 210 engine:insert(insert_sql, host, username or "", self.store, "", t, value);
172 end 211 end
173 end 212 end
174 return true; 213 return true;
175 end); 214 end);
176 if not ok then return nil, result; end 215 if not ok then return nil, result; end
185 function archive_store:append(username, key, value, when, with) 224 function archive_store:append(username, key, value, when, with)
186 local user,store = username,self.store; 225 local user,store = username,self.store;
187 when = when or os.time(); 226 when = when or os.time();
188 with = with or ""; 227 with = with or "";
189 local ok, ret = engine:transaction(function() 228 local ok, ret = engine:transaction(function()
229 local delete_sql = [[
230 DELETE FROM `prosodyarchive`
231 WHERE `host`=? AND `user`=? AND `store`=? AND `key`=?;
232 ]];
233 local insert_sql = [[
234 INSERT INTO `prosodyarchive`
235 (`host`, `user`, `store`, `when`, `with`, `key`, `type`, `value`)
236 VALUES (?,?,?,?,?,?,?,?);
237 ]];
190 if key then 238 if key then
191 engine:delete("DELETE FROM `prosodyarchive` WHERE `host`=? AND `user`=? AND `store`=? AND `key`=?", host, user or "", store, key); 239 engine:delete(delete_sql, host, user or "", store, key);
192 else 240 else
193 key = uuid.generate(); 241 key = uuid.generate();
194 end 242 end
195 local t, encoded_value = assert(serialize(value)); 243 local t, encoded_value = assert(serialize(value));
196 engine:insert(insert_sql, host, user or "", store, when, with, key, t, encoded_value); 244 engine:insert(insert_sql, host, user or "", store, when, with, key, t, encoded_value);
231 end 279 end
232 local function archive_where_id_range(query, args, where) 280 local function archive_where_id_range(query, args, where)
233 local args_len = #args 281 local args_len = #args
234 -- Before or after specific item, exclusive 282 -- Before or after specific item, exclusive
235 if query.after then -- keys better be unique! 283 if query.after then -- keys better be unique!
236 where[#where+1] = "`sort_id` > COALESCE((SELECT `sort_id` FROM `prosodyarchive` WHERE `key` = ? AND `host` = ? AND `user` = ? AND `store` = ? LIMIT 1), 0)" 284 where[#where+1] = [[
285 `sort_id` > COALESCE(
286 (
287 SELECT `sort_id`
288 FROM `prosodyarchive`
289 WHERE `key` = ? AND `host` = ? AND `user` = ? AND `store` = ?
290 LIMIT 1
291 ), 0)
292 ]];
237 args[args_len+1], args[args_len+2], args[args_len+3], args[args_len+4] = query.after, args[1], args[2], args[3]; 293 args[args_len+1], args[args_len+2], args[args_len+3], args[args_len+4] = query.after, args[1], args[2], args[3];
238 args_len = args_len + 4 294 args_len = args_len + 4
239 end 295 end
240 if query.before then 296 if query.before then
241 where[#where+1] = "`sort_id` < COALESCE((SELECT `sort_id` FROM `prosodyarchive` WHERE `key` = ? AND `host` = ? AND `user` = ? AND `store` = ? LIMIT 1), (SELECT MAX(`sort_id`)+1 FROM `prosodyarchive`))" 297 where[#where+1] = [[
298 `sort_id` < COALESCE(
299 (
300 SELECT `sort_id`
301 FROM `prosodyarchive`
302 WHERE `key` = ? AND `host` = ? AND `user` = ? AND `store` = ?
303 LIMIT 1
304 ),
305 (
306 SELECT MAX(`sort_id`)+1
307 FROM `prosodyarchive`
308 )
309 )
310 ]]
242 args[args_len+1], args[args_len+2], args[args_len+3], args[args_len+4] = query.before, args[1], args[2], args[3]; 311 args[args_len+1], args[args_len+2], args[args_len+3], args[args_len+4] = query.before, args[1], args[2], args[3];
243 end 312 end
244 end 313 end
245 314
246 function archive_store:find(username, query) 315 function archive_store:find(username, query)
247 query = query or {}; 316 query = query or {};
248 local user,store = username,self.store; 317 local user,store = username,self.store;
249 local total; 318 local total;
250 local ok, result = engine:transaction(function() 319 local ok, result = engine:transaction(function()
251 local sql_query = "SELECT `key`, `type`, `value`, `when`, `with` FROM `prosodyarchive` WHERE %s ORDER BY `sort_id` %s%s;"; 320 local sql_query = [[
321 SELECT `key`, `type`, `value`, `when`, `with`
322 FROM `prosodyarchive`
323 WHERE %s
324 ORDER BY `sort_id` %s%s;
325 ]];
252 local args = { host, user or "", store, }; 326 local args = { host, user or "", store, };
253 local where = { "`host` = ?", "`user` = ?", "`store` = ?", }; 327 local where = { "`host` = ?", "`user` = ?", "`store` = ?", };
254 328
255 archive_where(query, args, where); 329 archive_where(query, args, where);
256 330
257 -- Total matching 331 -- Total matching
258 if query.total then 332 if query.total then
259 local stats = engine:select("SELECT COUNT(*) FROM `prosodyarchive` WHERE " .. t_concat(where, " AND "), unpack(args)); 333 local stats = engine:select("SELECT COUNT(*) FROM `prosodyarchive` WHERE "
334 .. t_concat(where, " AND "), unpack(args));
260 if stats then 335 if stats then
261 for row in stats do 336 for row in stats do
262 total = row[1]; 337 total = row[1];
263 end 338 end
264 end 339 end
271 346
272 if query.limit then 347 if query.limit then
273 args[#args+1] = query.limit; 348 args[#args+1] = query.limit;
274 end 349 end
275 350
276 sql_query = sql_query:format(t_concat(where, " AND "), query.reverse and "DESC" or "ASC", query.limit and " LIMIT ?" or ""); 351 sql_query = sql_query:format(t_concat(where, " AND "), query.reverse
352 and "DESC" or "ASC", query.limit and " LIMIT ?" or "");
277 return engine:select(sql_query, unpack(args)); 353 return engine:select(sql_query, unpack(args));
278 end); 354 end);
279 if not ok then return ok, result end 355 if not ok then return ok, result end
280 return function() 356 return function()
281 local row = result(); 357 local row = result();
403 err or "unknown error"); 479 err or "unknown error");
404 return false; 480 return false;
405 end 481 end
406 482
407 -- COMPAT w/pre-0.10: Upgrade table to UTF-8 if not already 483 -- COMPAT w/pre-0.10: Upgrade table to UTF-8 if not already
408 local check_encoding_query = "SELECT `COLUMN_NAME`,`COLUMN_TYPE`,`TABLE_NAME` FROM `information_schema`.`columns` WHERE `TABLE_NAME` LIKE 'prosody%%' AND ( `CHARACTER_SET_NAME`!='%s' OR `COLLATION_NAME`!='%s_bin' );"; 484 local check_encoding_query = [[
485 SELECT `COLUMN_NAME`,`COLUMN_TYPE`,`TABLE_NAME`
486 FROM `information_schema`.`columns`
487 WHERE `TABLE_NAME` LIKE 'prosody%%' AND ( `CHARACTER_SET_NAME`!='%s' OR `COLLATION_NAME`!='%s_bin' );
488 ]];
409 check_encoding_query = check_encoding_query:format(engine.charset, engine.charset); 489 check_encoding_query = check_encoding_query:format(engine.charset, engine.charset);
410 success,err = engine:transaction(function() 490 success,err = engine:transaction(function()
411 local result = engine:execute(check_encoding_query); 491 local result = engine:execute(check_encoding_query);
412 local n_bad_columns = result:rowcount(); 492 local n_bad_columns = result:rowcount();
413 if n_bad_columns > 0 then 493 if n_bad_columns > 0 then