--- a/plugins/mod_storage_sql.lua Sat Apr 01 19:25:34 2017 +0200
+++ b/plugins/mod_storage_sql.lua Sat Apr 01 19:26:27 2017 +0200
@@ -57,7 +57,12 @@
local function keyval_store_get()
local haveany;
local result = {};
- for row in engine:select("SELECT `key`,`type`,`value` FROM `prosody` WHERE `host`=? AND `user`=? AND `store`=?", host, user or "", store) do
+ local select_sql = [[
+ SELECT `key`,`type`,`value`
+ FROM `prosody`
+ WHERE `host`=? AND `user`=? AND `store`=?;
+ ]]
+ for row in engine:select(select_sql, host, user or "", store) do
haveany = true;
local k = row[1];
local v = deserialize(row[2], row[3]);
@@ -74,21 +79,30 @@
end
end
local function keyval_store_set(data)
- engine:delete("DELETE FROM `prosody` WHERE `host`=? AND `user`=? AND `store`=?", host, user or "", store);
+ local delete_sql = [[
+ DELETE FROM `prosody`
+ WHERE `host`=? AND `user`=? AND `store`=?
+ ]];
+ engine:delete(delete_sql, host, user or "", store);
+ local insert_sql = [[
+ INSERT INTO `prosody`
+ (`host`,`user`,`store`,`key`,`type`,`value`)
+ VALUES (?,?,?,?,?,?);
+ ]]
if data and next(data) ~= nil then
local extradata = {};
for key, value in pairs(data) do
if type(key) == "string" and key ~= "" then
local t, encoded_value = assert(serialize(value));
- engine:insert("INSERT INTO `prosody` (`host`,`user`,`store`,`key`,`type`,`value`) VALUES (?,?,?,?,?,?)", host, user or "", store, key, t, encoded_value);
+ engine:insert(delete_sql, host, user or "", store, key, t, encoded_value);
else
extradata[key] = value;
end
end
if next(extradata) ~= nil then
local t, encoded_extradata = assert(serialize(extradata));
- engine:insert("INSERT INTO `prosody` (`host`,`user`,`store`,`key`,`type`,`value`) VALUES (?,?,?,?,?,?)", host, user or "", store, "", t, encoded_extradata);
+ engine:insert(insert_sql, host, user or "", store, "", t, encoded_extradata);
end
end
return true;
@@ -115,7 +129,12 @@
end
function keyval_store:users()
local ok, result = engine:transaction(function()
- return engine:select("SELECT DISTINCT `user` FROM `prosody` WHERE `host`=? AND `store`=?", host, self.store);
+ local select_sql = [[
+ SELECT DISTINCT `user`
+ FROM `prosody`
+ WHERE `host`=? AND `store`=?;
+ ]];
+ return engine:select(select_sql, host, self.store);
end);
if not ok then return ok, result end
return iterator(result);
@@ -129,14 +148,20 @@
map_store.remove = {};
function map_store:get(username, key)
local ok, result = engine:transaction(function()
+ local query = [[
+ SELECT `type`, `value`
+ FROM `prosody`
+ WHERE `host`=? AND `user`=? AND `store`=? AND `key`=?
+ LIMIT 1
+ ]];
local data;
if type(key) == "string" and key ~= "" then
- 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
+ for row in engine:select(query, host, username or "", self.store, key) do
data = deserialize(row[1], row[2]);
end
return data;
else
- 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
+ for row in engine:select(query, host, username or "", self.store, "") do
data = deserialize(row[1], row[2]);
end
return data and data[key] or nil;
@@ -151,24 +176,38 @@
end
function map_store:set_keys(username, keydatas)
local ok, result = engine:transaction(function()
+ local delete_sql = [[
+ DELETE FROM `prosody`
+ WHERE `host`=? AND `user`=? AND `store`=? AND `key`=?;
+ ]];
+ local insert_sql = [[
+ INSERT INTO `prosody`
+ (`host`,`user`,`store`,`key`,`type`,`value`)
+ VALUES (?,?,?,?,?,?);
+ ]];
+ local select_extradata_sql = [[
+ SELECT `type`, `value`
+ FROM `prosody`
+ WHERE `host`=? AND `user`=? AND `store`=? AND `key`=?
+ LIMIT 1;
+ ]];
for key, data in pairs(keydatas) do
if type(key) == "string" and key ~= "" then
- engine:delete("DELETE FROM `prosody` WHERE `host`=? AND `user`=? AND `store`=? AND `key`=?",
+ engine:delete(delete_sql,
host, username or "", self.store, key);
if data ~= self.remove then
local t, value = assert(serialize(data));
- engine:insert("INSERT INTO `prosody` (`host`,`user`,`store`,`key`,`type`,`value`) VALUES (?,?,?,?,?,?)", host, username or "", self.store, key, t, value);
+ engine:insert(insert_sql, host, username or "", self.store, key, t, value);
end
else
local extradata = {};
- 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
+ for row in engine:select(select_extradata_sql, host, username or "", self.store, "") do
extradata = deserialize(row[1], row[2]);
end
- engine:delete("DELETE FROM `prosody` WHERE `host`=? AND `user`=? AND `store`=? AND `key`=?",
- host, username or "", self.store, "");
+ engine:delete(delete_sql, host, username or "", self.store, "");
extradata[key] = data;
local t, value = assert(serialize(extradata));
- engine:insert("INSERT INTO `prosody` (`host`,`user`,`store`,`key`,`type`,`value`) VALUES (?,?,?,?,?,?)", host, username or "", self.store, "", t, value);
+ engine:insert(insert_sql, host, username or "", self.store, "", t, value);
end
end
return true;
@@ -187,8 +226,17 @@
when = when or os.time();
with = with or "";
local ok, ret = engine:transaction(function()
+ local delete_sql = [[
+ DELETE FROM `prosodyarchive`
+ WHERE `host`=? AND `user`=? AND `store`=? AND `key`=?;
+ ]];
+ local insert_sql = [[
+ INSERT INTO `prosodyarchive`
+ (`host`, `user`, `store`, `when`, `with`, `key`, `type`, `value`)
+ VALUES (?,?,?,?,?,?,?,?);
+ ]];
if key then
- engine:delete("DELETE FROM `prosodyarchive` WHERE `host`=? AND `user`=? AND `store`=? AND `key`=?", host, user or "", store, key);
+ engine:delete(delete_sql, host, user or "", store, key);
else
key = uuid.generate();
end
@@ -233,12 +281,33 @@
local args_len = #args
-- Before or after specific item, exclusive
if query.after then -- keys better be unique!
- where[#where+1] = "`sort_id` > COALESCE((SELECT `sort_id` FROM `prosodyarchive` WHERE `key` = ? AND `host` = ? AND `user` = ? AND `store` = ? LIMIT 1), 0)"
+ where[#where+1] = [[
+ `sort_id` > COALESCE(
+ (
+ SELECT `sort_id`
+ FROM `prosodyarchive`
+ WHERE `key` = ? AND `host` = ? AND `user` = ? AND `store` = ?
+ LIMIT 1
+ ), 0)
+ ]];
args[args_len+1], args[args_len+2], args[args_len+3], args[args_len+4] = query.after, args[1], args[2], args[3];
args_len = args_len + 4
end
if query.before then
- 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`))"
+ 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`
+ )
+ )
+ ]]
args[args_len+1], args[args_len+2], args[args_len+3], args[args_len+4] = query.before, args[1], args[2], args[3];
end
end
@@ -248,7 +317,12 @@
local user,store = username,self.store;
local total;
local ok, result = engine:transaction(function()
- local sql_query = "SELECT `key`, `type`, `value`, `when`, `with` FROM `prosodyarchive` WHERE %s ORDER BY `sort_id` %s%s;";
+ local sql_query = [[
+ SELECT `key`, `type`, `value`, `when`, `with`
+ FROM `prosodyarchive`
+ WHERE %s
+ ORDER BY `sort_id` %s%s;
+ ]];
local args = { host, user or "", store, };
local where = { "`host` = ?", "`user` = ?", "`store` = ?", };
@@ -256,7 +330,8 @@
-- Total matching
if query.total then
- local stats = engine:select("SELECT COUNT(*) FROM `prosodyarchive` WHERE " .. t_concat(where, " AND "), unpack(args));
+ local stats = engine:select("SELECT COUNT(*) FROM `prosodyarchive` WHERE "
+ .. t_concat(where, " AND "), unpack(args));
if stats then
for row in stats do
total = row[1];
@@ -273,7 +348,8 @@
args[#args+1] = query.limit;
end
- sql_query = sql_query:format(t_concat(where, " AND "), query.reverse and "DESC" or "ASC", query.limit and " LIMIT ?" or "");
+ sql_query = sql_query:format(t_concat(where, " AND "), query.reverse
+ and "DESC" or "ASC", query.limit and " LIMIT ?" or "");
return engine:select(sql_query, unpack(args));
end);
if not ok then return ok, result end
@@ -405,7 +481,11 @@
end
-- COMPAT w/pre-0.10: Upgrade table to UTF-8 if not already
- 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' );";
+ 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' );
+ ]];
check_encoding_query = check_encoding_query:format(engine.charset, engine.charset);
success,err = engine:transaction(function()
local result = engine:execute(check_encoding_query);