prosody
7361412a9664
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 @ 8073:7361412a9664

SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
author Kim Alvefur <zash@zash.se>
date Mon, 10 Apr 2017 23:13:39 +0200
parent 8050:c0f81dea4662
child 8074:4b403f881176
child 8075:a4dd08fd64cf
comparison
equal deleted inserted replaced
8072:194409dcba22 8073:7361412a9664
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 local select_sql = [[ 60 local select_sql = [[
61 SELECT `key`,`type`,`value` 61 SELECT "key","type","value"
62 FROM `prosody` 62 FROM "prosody"
63 WHERE `host`=? AND `user`=? AND `store`=?; 63 WHERE "host"=? AND "user"=? AND "store"=?;
64 ]] 64 ]]
65 for row in engine:select(select_sql, host, user or "", store) do 65 for row in engine:select(select_sql, host, user or "", store) do
66 haveany = true; 66 haveany = true;
67 local k = row[1]; 67 local k = row[1];
68 local v = deserialize(row[2], row[3]); 68 local v = deserialize(row[2], row[3]);
78 return result; 78 return result;
79 end 79 end
80 end 80 end
81 local function keyval_store_set(data) 81 local function keyval_store_set(data)
82 local delete_sql = [[ 82 local delete_sql = [[
83 DELETE FROM `prosody` 83 DELETE FROM "prosody"
84 WHERE `host`=? AND `user`=? AND `store`=? 84 WHERE "host"=? AND "user"=? AND "store"=?
85 ]]; 85 ]];
86 engine:delete(delete_sql, host, user or "", store); 86 engine:delete(delete_sql, host, user or "", store);
87 87
88 local insert_sql = [[ 88 local insert_sql = [[
89 INSERT INTO `prosody` 89 INSERT INTO "prosody"
90 (`host`,`user`,`store`,`key`,`type`,`value`) 90 ("host","user","store","key","type","value")
91 VALUES (?,?,?,?,?,?); 91 VALUES (?,?,?,?,?,?);
92 ]] 92 ]]
93 if data and next(data) ~= nil then 93 if data and next(data) ~= nil then
94 local extradata = {}; 94 local extradata = {};
95 for key, value in pairs(data) do 95 for key, value in pairs(data) do
128 end); 128 end);
129 end 129 end
130 function keyval_store:users() 130 function keyval_store:users()
131 local ok, result = engine:transaction(function() 131 local ok, result = engine:transaction(function()
132 local select_sql = [[ 132 local select_sql = [[
133 SELECT DISTINCT `user` 133 SELECT DISTINCT "user"
134 FROM `prosody` 134 FROM "prosody"
135 WHERE `host`=? AND `store`=?; 135 WHERE "host"=? AND "store"=?;
136 ]]; 136 ]];
137 return engine:select(select_sql, host, self.store); 137 return engine:select(select_sql, host, self.store);
138 end); 138 end);
139 if not ok then return ok, result end 139 if not ok then return ok, result end
140 return iterator(result); 140 return iterator(result);
147 map_store.__index = map_store; 147 map_store.__index = map_store;
148 map_store.remove = {}; 148 map_store.remove = {};
149 function map_store:get(username, key) 149 function map_store:get(username, key)
150 local ok, result = engine:transaction(function() 150 local ok, result = engine:transaction(function()
151 local query = [[ 151 local query = [[
152 SELECT `type`, `value` 152 SELECT "type", "value"
153 FROM `prosody` 153 FROM "prosody"
154 WHERE `host`=? AND `user`=? AND `store`=? AND `key`=? 154 WHERE "host"=? AND "user"=? AND "store"=? AND "key"=?
155 LIMIT 1 155 LIMIT 1
156 ]]; 156 ]];
157 local data; 157 local data;
158 if type(key) == "string" and key ~= "" then 158 if type(key) == "string" and key ~= "" then
159 for row in engine:select(query, host, username or "", self.store, key) do 159 for row in engine:select(query, host, username or "", self.store, key) do
175 return self:set_keys(username, { [key] = data }); 175 return self:set_keys(username, { [key] = data });
176 end 176 end
177 function map_store:set_keys(username, keydatas) 177 function map_store:set_keys(username, keydatas)
178 local ok, result = engine:transaction(function() 178 local ok, result = engine:transaction(function()
179 local delete_sql = [[ 179 local delete_sql = [[
180 DELETE FROM `prosody` 180 DELETE FROM "prosody"
181 WHERE `host`=? AND `user`=? AND `store`=? AND `key`=?; 181 WHERE "host"=? AND "user"=? AND "store"=? AND "key"=?;
182 ]]; 182 ]];
183 local insert_sql = [[ 183 local insert_sql = [[
184 INSERT INTO `prosody` 184 INSERT INTO "prosody"
185 (`host`,`user`,`store`,`key`,`type`,`value`) 185 ("host","user","store","key","type","value")
186 VALUES (?,?,?,?,?,?); 186 VALUES (?,?,?,?,?,?);
187 ]]; 187 ]];
188 local select_extradata_sql = [[ 188 local select_extradata_sql = [[
189 SELECT `type`, `value` 189 SELECT "type", "value"
190 FROM `prosody` 190 FROM "prosody"
191 WHERE `host`=? AND `user`=? AND `store`=? AND `key`=? 191 WHERE "host"=? AND "user"=? AND "store"=? AND "key"=?
192 LIMIT 1; 192 LIMIT 1;
193 ]]; 193 ]];
194 for key, data in pairs(keydatas) do 194 for key, data in pairs(keydatas) do
195 if type(key) == "string" and key ~= "" then 195 if type(key) == "string" and key ~= "" then
196 engine:delete(delete_sql, 196 engine:delete(delete_sql,
225 local user,store = username,self.store; 225 local user,store = username,self.store;
226 when = when or os.time(); 226 when = when or os.time();
227 with = with or ""; 227 with = with or "";
228 local ok, ret = engine:transaction(function() 228 local ok, ret = engine:transaction(function()
229 local delete_sql = [[ 229 local delete_sql = [[
230 DELETE FROM `prosodyarchive` 230 DELETE FROM "prosodyarchive"
231 WHERE `host`=? AND `user`=? AND `store`=? AND `key`=?; 231 WHERE "host"=? AND "user"=? AND "store"=? AND "key"=?;
232 ]]; 232 ]];
233 local insert_sql = [[ 233 local insert_sql = [[
234 INSERT INTO `prosodyarchive` 234 INSERT INTO "prosodyarchive"
235 (`host`, `user`, `store`, `when`, `with`, `key`, `type`, `value`) 235 ("host", "user", "store", "when", "with", "key", "type", "value")
236 VALUES (?,?,?,?,?,?,?,?); 236 VALUES (?,?,?,?,?,?,?,?);
237 ]]; 237 ]];
238 if key then 238 if key then
239 engine:delete(delete_sql, host, user or "", store, key); 239 engine:delete(delete_sql, host, user or "", store, key);
240 else 240 else
251 -- Helpers for building the WHERE clause 251 -- Helpers for building the WHERE clause
252 local function archive_where(query, args, where) 252 local function archive_where(query, args, where)
253 -- Time range, inclusive 253 -- Time range, inclusive
254 if query.start then 254 if query.start then
255 args[#args+1] = query.start 255 args[#args+1] = query.start
256 where[#where+1] = "`when` >= ?" 256 where[#where+1] = "\"when\" >= ?"
257 end 257 end
258 258
259 if query["end"] then 259 if query["end"] then
260 args[#args+1] = query["end"]; 260 args[#args+1] = query["end"];
261 if query.start then 261 if query.start then
262 where[#where] = "`when` BETWEEN ? AND ?" -- is this inclusive? 262 where[#where] = "\"when\" BETWEEN ? AND ?" -- is this inclusive?
263 else 263 else
264 where[#where+1] = "`when` <= ?" 264 where[#where+1] = "\"when\" <= ?"
265 end 265 end
266 end 266 end
267 267
268 -- Related name 268 -- Related name
269 if query.with then 269 if query.with then
270 where[#where+1] = "`with` = ?"; 270 where[#where+1] = "\"with\" = ?";
271 args[#args+1] = query.with 271 args[#args+1] = query.with
272 end 272 end
273 273
274 -- Unique id 274 -- Unique id
275 if query.key then 275 if query.key then
276 where[#where+1] = "`key` = ?"; 276 where[#where+1] = "\"key\" = ?";
277 args[#args+1] = query.key 277 args[#args+1] = query.key
278 end 278 end
279 end 279 end
280 local function archive_where_id_range(query, args, where) 280 local function archive_where_id_range(query, args, where)
281 local args_len = #args 281 local args_len = #args
282 -- Before or after specific item, exclusive 282 -- Before or after specific item, exclusive
283 if query.after then -- keys better be unique! 283 if query.after then -- keys better be unique!
284 where[#where+1] = [[ 284 where[#where+1] = [[
285 `sort_id` > COALESCE( 285 "sort_id" > COALESCE(
286 ( 286 (
287 SELECT `sort_id` 287 SELECT "sort_id"
288 FROM `prosodyarchive` 288 FROM "prosodyarchive"
289 WHERE `key` = ? AND `host` = ? AND `user` = ? AND `store` = ? 289 WHERE "key" = ? AND "host" = ? AND "user" = ? AND "store" = ?
290 LIMIT 1 290 LIMIT 1
291 ), 0) 291 ), 0)
292 ]]; 292 ]];
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]; 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];
294 args_len = args_len + 4 294 args_len = args_len + 4
295 end 295 end
296 if query.before then 296 if query.before then
297 where[#where+1] = [[ 297 where[#where+1] = [[
298 `sort_id` < COALESCE( 298 "sort_id" < COALESCE(
299 ( 299 (
300 SELECT `sort_id` 300 SELECT "sort_id"
301 FROM `prosodyarchive` 301 FROM "prosodyarchive"
302 WHERE `key` = ? AND `host` = ? AND `user` = ? AND `store` = ? 302 WHERE "key" = ? AND "host" = ? AND "user" = ? AND "store" = ?
303 LIMIT 1 303 LIMIT 1
304 ), 304 ),
305 ( 305 (
306 SELECT MAX(`sort_id`)+1 306 SELECT MAX("sort_id")+1
307 FROM `prosodyarchive` 307 FROM "prosodyarchive"
308 ) 308 )
309 ) 309 )
310 ]] 310 ]]
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]; 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];
312 end 312 end
316 query = query or {}; 316 query = query or {};
317 local user,store = username,self.store; 317 local user,store = username,self.store;
318 local total; 318 local total;
319 local ok, result = engine:transaction(function() 319 local ok, result = engine:transaction(function()
320 local sql_query = [[ 320 local sql_query = [[
321 SELECT `key`, `type`, `value`, `when`, `with` 321 SELECT "key", "type", "value", "when", "with"
322 FROM `prosodyarchive` 322 FROM "prosodyarchive"
323 WHERE %s 323 WHERE %s
324 ORDER BY `sort_id` %s%s; 324 ORDER BY "sort_id" %s%s;
325 ]]; 325 ]];
326 local args = { host, user or "", store, }; 326 local args = { host, user or "", store, };
327 local where = { "`host` = ?", "`user` = ?", "`store` = ?", }; 327 local where = { "\"host\" = ?", "\"user\" = ?", "\"store\" = ?", };
328 328
329 archive_where(query, args, where); 329 archive_where(query, args, where);
330 330
331 -- Total matching 331 -- Total matching
332 if query.total then 332 if query.total then
333 local stats = engine:select("SELECT COUNT(*) FROM `prosodyarchive` WHERE " 333 local stats = engine:select("SELECT COUNT(*) FROM \"prosodyarchive\" WHERE "
334 .. t_concat(where, " AND "), unpack(args)); 334 .. t_concat(where, " AND "), unpack(args));
335 if stats then 335 if stats then
336 for row in stats do 336 for row in stats do
337 total = row[1]; 337 total = row[1];
338 end 338 end
363 363
364 function archive_store:delete(username, query) 364 function archive_store:delete(username, query)
365 query = query or {}; 365 query = query or {};
366 local user,store = username,self.store; 366 local user,store = username,self.store;
367 local ok, stmt = engine:transaction(function() 367 local ok, stmt = engine:transaction(function()
368 local sql_query = "DELETE FROM `prosodyarchive` WHERE %s;"; 368 local sql_query = "DELETE FROM \"prosodyarchive\" WHERE %s;";
369 local args = { host, user or "", store, }; 369 local args = { host, user or "", store, };
370 local where = { "`host` = ?", "`user` = ?", "`store` = ?", }; 370 local where = { "\"host\" = ?", "\"user\" = ?", "\"store\" = ?", };
371 if user == true then 371 if user == true then
372 table.remove(args, 2); 372 table.remove(args, 2);
373 table.remove(where, 2); 373 table.remove(where, 2);
374 end 374 end
375 archive_where(query, args, where); 375 archive_where(query, args, where);
399 end 399 end
400 return nil, "unsupported-store"; 400 return nil, "unsupported-store";
401 end 401 end
402 402
403 function driver:stores(username) 403 function driver:stores(username)
404 local query = "SELECT DISTINCT `store` FROM `prosody` WHERE `host`=? AND `user`" .. 404 local query = "SELECT DISTINCT \"store\" FROM \"prosody\" WHERE \"host\"=? AND \"user\"" ..
405 (username == true and "!=?" or "=?"); 405 (username == true and "!=?" or "=?");
406 if username == true or not username then 406 if username == true or not username then
407 username = ""; 407 username = "";
408 end 408 end
409 local ok, result = engine:transaction(function() 409 local ok, result = engine:transaction(function()
413 return iterator(result); 413 return iterator(result);
414 end 414 end
415 415
416 function driver:purge(username) 416 function driver:purge(username)
417 return engine:transaction(function() 417 return engine:transaction(function()
418 local stmt,err = engine:delete("DELETE FROM `prosody` WHERE `host`=? AND `user`=?", host, username); 418 local stmt,err = engine:delete("DELETE FROM \"prosody\" WHERE \"host\"=? AND \"user\"=?", host, username);
419 return true, err; 419 return true, err;
420 end); 420 end);
421 end 421 end
422 422
423 --- Initialization 423 --- Initialization
465 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'");
466 if result:rowcount() > 0 then 466 if result:rowcount() > 0 then
467 changes = true; 467 changes = true;
468 if apply_changes then 468 if apply_changes then
469 module:log("info", "Upgrading database schema..."); 469 module:log("info", "Upgrading database schema...");
470 engine:execute("ALTER TABLE prosody MODIFY COLUMN `value` MEDIUMTEXT"); 470 engine:execute("ALTER TABLE prosody MODIFY COLUMN \"value\" MEDIUMTEXT");
471 module:log("info", "Database table automatically upgraded"); 471 module:log("info", "Database table automatically upgraded");
472 end 472 end
473 end 473 end
474 return true; 474 return true;
475 end); 475 end);
480 return false; 480 return false;
481 end 481 end
482 482
483 -- 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
484 local check_encoding_query = [[ 484 local check_encoding_query = [[
485 SELECT `COLUMN_NAME`,`COLUMN_TYPE`,`TABLE_NAME` 485 SELECT "COLUMN_NAME","COLUMN_TYPE","TABLE_NAME"
486 FROM `information_schema`.`columns` 486 FROM "information_schema"."columns"
487 WHERE `TABLE_NAME` LIKE 'prosody%%' AND ( `CHARACTER_SET_NAME`!='%s' OR `COLLATION_NAME`!='%s_bin' ); 487 WHERE "TABLE_NAME" LIKE 'prosody%%' AND ( "CHARACTER_SET_NAME"!='%s' OR "COLLATION_NAME"!='%s_bin' );
488 ]]; 488 ]];
489 check_encoding_query = check_encoding_query:format(engine.charset, engine.charset); 489 check_encoding_query = check_encoding_query:format(engine.charset, engine.charset);
490 -- FIXME Is it ok to ignore the return values from this? 490 -- FIXME Is it ok to ignore the return values from this?
491 engine:transaction(function() 491 engine:transaction(function()
492 local result = engine:execute(check_encoding_query); 492 local result = engine:execute(check_encoding_query);
493 local n_bad_columns = result:rowcount(); 493 local n_bad_columns = result:rowcount();
494 if n_bad_columns > 0 then 494 if n_bad_columns > 0 then
495 changes = true; 495 changes = true;
496 if apply_changes then 496 if apply_changes then
497 module:log("warn", "Found %d columns in prosody table requiring encoding change, updating now...", n_bad_columns); 497 module:log("warn", "Found %d columns in prosody table requiring encoding change, updating now...", n_bad_columns);
498 local fix_column_query1 = "ALTER TABLE `%s` CHANGE `%s` `%s` BLOB;"; 498 local fix_column_query1 = "ALTER TABLE \"%s\" CHANGE \"%s\" \"%s\" BLOB;";
499 local fix_column_query2 = "ALTER TABLE `%s` CHANGE `%s` `%s` %s CHARACTER SET '%s' COLLATE '%s_bin';"; 499 local fix_column_query2 = "ALTER TABLE \"%s\" CHANGE \"%s\" \"%s\" %s CHARACTER SET '%s' COLLATE '%s_bin';";
500 for row in result:rows() do 500 for row in result:rows() do
501 local column_name, column_type, table_name = unpack(row); 501 local column_name, column_type, table_name = unpack(row);
502 module:log("debug", "Fixing column %s in table %s", column_name, table_name); 502 module:log("debug", "Fixing column %s in table %s", column_name, table_name);
503 engine:execute(fix_column_query1:format(table_name, column_name, column_name)); 503 engine:execute(fix_column_query1:format(table_name, column_name, column_name));
504 engine:execute(fix_column_query2:format(table_name, column_name, column_name, column_type, engine.charset, engine.charset)); 504 engine:execute(fix_column_query2:format(table_name, column_name, column_name, column_type, engine.charset, engine.charset));