diff options
| -rw-r--r-- | plugins/mod_storage_sql.lua | 122 |
1 files changed, 101 insertions, 21 deletions
diff --git a/plugins/mod_storage_sql.lua b/plugins/mod_storage_sql.lua index 25a9dc56..40a59911 100644 --- a/plugins/mod_storage_sql.lua +++ b/plugins/mod_storage_sql.lua @@ -57,7 +57,12 @@ local user, store; 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 @@ local function keyval_store_get() 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 @@ function keyval_store:set(username, data) 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.__index = map_store; 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 @@ function map_store:set(username, key, data) 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 @@ function archive_store:append(username, key, value, when, with) 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 function archive_where_id_range(query, args, where) 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 @@ function archive_store:find(username, query) 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 @@ function archive_store:find(username, query) -- 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 @@ function archive_store:find(username, query) 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 @@ local function upgrade_table(params, apply_changes) 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); |