prosody
1f4bd8009961
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 @ 8039:1f4bd8009961

Merge 0.10->trunk
author Kim Alvefur <zash@zash.se>
date Sat, 01 Apr 2017 21:48:54 +0200
parent 7909:428d4abee723
parent 8038:cd6cef579e82
child 8051:b2681397bafa
comparison
equal deleted inserted replaced
8028:adfc7f3b29ce 8039:1f4bd8009961
30 if t == "string" or t == "boolean" or t == "number" then 30 if t == "string" or t == "boolean" or t == "number" then
31 return t, tostring(value); 31 return t, tostring(value);
32 elseif is_stanza(value) then 32 elseif is_stanza(value) then
33 return "xml", tostring(value); 33 return "xml", tostring(value);
34 elseif t == "table" then 34 elseif t == "table" then
35 local value,err = json.encode(value); 35 local encoded,err = json.encode(value);
36 if value then return "json", value; end 36 if value then return "json", encoded; end
37 return nil, err; 37 return nil, err;
38 end 38 end
39 return nil, "Unhandled value type: "..t; 39 return nil, "Unhandled value type: "..t;
40 end 40 end
41 local function deserialize(t, value) 41 local function deserialize(t, value)
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, 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, 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, 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, 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
181 archive_store.caps = { 220 archive_store.caps = {
182 total = true; 221 total = true;
183 }; 222 };
184 archive_store.__index = archive_store 223 archive_store.__index = archive_store
185 function archive_store:append(username, key, value, when, with) 224 function archive_store:append(username, key, value, when, with)
186 if type(when) ~= "number" then
187 when, with, value = value, when, with;
188 end
189 local user,store = username,self.store; 225 local user,store = username,self.store;
190 local ok, key = engine:transaction(function() 226 when = when or os.time();
227 with = with or "";
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 ]];
191 if key then 238 if key then
192 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);
193 else 240 else
194 key = uuid.generate(); 241 key = uuid.generate();
195 end 242 end
196 local t, value = assert(serialize(value)); 243 local t, encoded_value = assert(serialize(value));
197 engine:insert("INSERT INTO `prosodyarchive` (`host`, `user`, `store`, `when`, `with`, `key`, `type`, `value`) VALUES (?,?,?,?,?,?,?,?)", host, user or "", store, when, with, key, t, value); 244 engine:insert(insert_sql, host, user or "", store, when, with, key, t, encoded_value);
198 return key; 245 return key;
199 end); 246 end);
200 if not ok then return ok, key; end 247 if not ok then return ok, ret; end
201 return key; 248 return ret; -- the key
202 end 249 end
203 250
204 -- Helpers for building the WHERE clause 251 -- Helpers for building the WHERE clause
205 local function archive_where(query, args, where) 252 local function archive_where(query, args, where)
206 -- Time range, inclusive 253 -- Time range, inclusive
232 end 279 end
233 local function archive_where_id_range(query, args, where) 280 local function archive_where_id_range(query, args, where)
234 local args_len = #args 281 local args_len = #args
235 -- Before or after specific item, exclusive 282 -- Before or after specific item, exclusive
236 if query.after then -- keys better be unique! 283 if query.after then -- keys better be unique!
237 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 ]];
238 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];
239 args_len = args_len + 4 294 args_len = args_len + 4
240 end 295 end
241 if query.before then 296 if query.before then
242 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 ]]
243 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];
244 end 312 end
245 end 313 end
246 314
247 function archive_store:find(username, query) 315 function archive_store:find(username, query)
248 query = query or {}; 316 query = query or {};
249 local user,store = username,self.store; 317 local user,store = username,self.store;
250 local total; 318 local total;
251 local ok, result = engine:transaction(function() 319 local ok, result = engine:transaction(function()
252 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 ]];
253 local args = { host, user or "", store, }; 326 local args = { host, user or "", store, };
254 local where = { "`host` = ?", "`user` = ?", "`store` = ?", }; 327 local where = { "`host` = ?", "`user` = ?", "`store` = ?", };
255 328
256 archive_where(query, args, where); 329 archive_where(query, args, where);
257 330
258 -- Total matching 331 -- Total matching
259 if query.total then 332 if query.total then
260 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));
261 if stats then 335 if stats then
262 for row in stats do 336 for row in stats do
263 total = row[1]; 337 total = row[1];
264 end 338 end
265 end 339 end
272 346
273 if query.limit then 347 if query.limit then
274 args[#args+1] = query.limit; 348 args[#args+1] = query.limit;
275 end 349 end
276 350
277 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 "");
278 return engine:select(sql_query, unpack(args)); 353 return engine:select(sql_query, unpack(args));
279 end); 354 end);
280 if not ok then return ok, result end 355 if not ok then return ok, result end
281 return function() 356 return function()
282 local row = result(); 357 local row = result();
346 end 421 end
347 422
348 --- Initialization 423 --- Initialization
349 424
350 425
351 local function create_table(name) 426 local function create_table(engine, name) -- luacheck: ignore 431/engine
352 local Table, Column, Index = sql.Table, sql.Column, sql.Index; 427 local Table, Column, Index = sql.Table, sql.Column, sql.Index;
353 428
354 local ProsodyTable = Table { 429 local ProsodyTable = Table {
355 name= name or "prosody"; 430 name= name or "prosody";
356 Column { name="host", type="TEXT", nullable=false }; 431 Column { name="host", type="TEXT", nullable=false };
381 engine:transaction(function() 456 engine:transaction(function()
382 ProsodyArchiveTable:create(engine); 457 ProsodyArchiveTable:create(engine);
383 end); 458 end);
384 end 459 end
385 460
386 local function upgrade_table(params, apply_changes) 461 local function upgrade_table(engine, params, apply_changes) -- luacheck: ignore 431/engine
387 local changes = false; 462 local changes = false;
388 if params.driver == "MySQL" then 463 if params.driver == "MySQL" then
389 local success,err = engine:transaction(function() 464 local success,err = engine:transaction(function()
390 local result = engine:execute("SHOW COLUMNS FROM prosody WHERE Field='value' and Type='text'"); 465 local result = engine:execute("SHOW COLUMNS FROM prosody WHERE Field='value' and Type='text'");
391 if result:rowcount() > 0 then 466 if result:rowcount() > 0 then
404 err or "unknown error"); 479 err or "unknown error");
405 return false; 480 return false;
406 end 481 end
407 482
408 -- 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
409 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 ]];
410 check_encoding_query = check_encoding_query:format(engine.charset, engine.charset); 489 check_encoding_query = check_encoding_query:format(engine.charset, engine.charset);
411 success,err = engine:transaction(function() 490 -- FIXME Is it ok to ignore the return values from this?
491 engine:transaction(function()
412 local result = engine:execute(check_encoding_query); 492 local result = engine:execute(check_encoding_query);
413 local n_bad_columns = result:rowcount(); 493 local n_bad_columns = result:rowcount();
414 if n_bad_columns > 0 then 494 if n_bad_columns > 0 then
415 changes = true; 495 changes = true;
416 if apply_changes then 496 if apply_changes then
434 end 514 end
435 end 515 end
436 return changes; 516 return changes;
437 end 517 end
438 518
439 local function normalize_database(driver, database) 519 local function normalize_database(driver, database) -- luacheck: ignore 431/driver
440 if driver == "SQLite3" and database ~= ":memory:" then 520 if driver == "SQLite3" and database ~= ":memory:" then
441 return resolve_relative_path(prosody.paths.data or ".", database or "prosody.sqlite"); 521 return resolve_relative_path(prosody.paths.data or ".", database or "prosody.sqlite");
442 end 522 end
443 return database; 523 return database;
444 end 524 end
445 525
446 local function normalize_params(params) 526 local function normalize_params(params)
447 return { 527 return {
448 driver = assert(params.driver, "Configuration error: Both the SQL driver and the database need to be specified"); 528 driver = assert(params.driver,
449 database = assert(normalize_database(params.driver, params.database), "Configuration error: Both the SQL driver and the database need to be specified"); 529 "Configuration error: Both the SQL driver and the database need to be specified");
530 database = assert(normalize_database(params.driver, params.database),
531 "Configuration error: Both the SQL driver and the database need to be specified");
450 username = params.username; 532 username = params.username;
451 password = params.password; 533 password = params.password;
452 host = params.host; 534 host = params.host;
453 port = params.port; 535 port = params.port;
454 }; 536 };
459 local engines = module:shared("/*/sql/connections"); 541 local engines = module:shared("/*/sql/connections");
460 local params = normalize_params(module:get_option("sql", default_params)); 542 local params = normalize_params(module:get_option("sql", default_params));
461 engine = engines[sql.db2uri(params)]; 543 engine = engines[sql.db2uri(params)];
462 if not engine then 544 if not engine then
463 module:log("debug", "Creating new engine"); 545 module:log("debug", "Creating new engine");
464 engine = sql:create_engine(params, function (engine) 546 engine = sql:create_engine(params, function (engine) -- luacheck: ignore 431/engine
465 if module:get_option("sql_manage_tables", true) then 547 if module:get_option("sql_manage_tables", true) then
466 -- Automatically create table, ignore failure (table probably already exists) 548 -- Automatically create table, ignore failure (table probably already exists)
467 -- FIXME: we should check in information_schema, etc. 549 -- FIXME: we should check in information_schema, etc.
468 create_table(); 550 create_table(engine);
469 -- Check whether the table needs upgrading 551 -- Check whether the table needs upgrading
470 if upgrade_table(params, false) then 552 if upgrade_table(engine, params, false) then
471 module:log("error", "Old database format detected. Please run: prosodyctl mod_%s upgrade", module.name); 553 module:log("error", "Old database format detected. Please run: prosodyctl mod_%s upgrade", module.name);
472 return false, "database upgrade needed"; 554 return false, "database upgrade needed";
473 end 555 end
474 end 556 end
475 end); 557 end);
484 local prosodyctl = require "util.prosodyctl"; 566 local prosodyctl = require "util.prosodyctl";
485 local command = table.remove(arg, 1); 567 local command = table.remove(arg, 1);
486 if command == "upgrade" then 568 if command == "upgrade" then
487 -- We need to find every unique dburi in the config 569 -- We need to find every unique dburi in the config
488 local uris = {}; 570 local uris = {};
489 for host in pairs(prosody.hosts) do 571 for host in pairs(prosody.hosts) do -- luacheck: ignore 431/host
490 local params = normalize_params(config.get(host, "sql") or default_params); 572 local params = normalize_params(config.get(host, "sql") or default_params);
491 uris[sql.db2uri(params)] = params; 573 uris[sql.db2uri(params)] = params;
492 end 574 end
493 print("We will check and upgrade the following databases:\n"); 575 print("We will check and upgrade the following databases:\n");
494 for _, params in pairs(uris) do 576 for _, params in pairs(uris) do
502 end 584 end
503 -- Upgrade each one 585 -- Upgrade each one
504 for _, params in pairs(uris) do 586 for _, params in pairs(uris) do
505 print("Checking "..params.database.."..."); 587 print("Checking "..params.database.."...");
506 engine = sql:create_engine(params); 588 engine = sql:create_engine(params);
507 upgrade_table(params, true); 589 upgrade_table(engine, params, true);
508 end 590 end
509 print("All done!"); 591 print("All done!");
510 elseif command then 592 elseif command then
511 print("Unknown command: "..command); 593 print("Unknown command: "..command);
512 else 594 else