Merge 0.10->trunk

[prosody.git] / plugins / mod_storage_sql.lua

diff --git a/plugins/mod_storage_sql.lua b/plugins/mod_storage_sql.lua
index 50d34a23c15cea615489c4140e9fea8f9a0acd58..60870a01afb6bcc5664eda54a5e88e0f24d7937e 100644 (file)
--- a/plugins/mod_storage_sql.lua
+++ b/plugins/mod_storage_sql.lua
@@ -82,16 +82,14 @@ local function keyval_store_set(data)
                local extradata = {};
                for key, value in pairs(data) do
                        if type(key) == "string" and key ~= "" then
-                               local t, value = serialize(value);
-                               assert(t, value);
+                               local t, value = assert(serialize(value));
                                engine:insert("INSERT INTO `prosody` (`host`,`user`,`store`,`key`,`type`,`value`) VALUES (?,?,?,?,?,?)", host, user or "", store, key, t, value);
                        else
                                extradata[key] = value;
                        end
                end
                if next(extradata) ~= nil then
-                       local t, extradata = serialize(extradata);
-                       assert(t, extradata);
+                       local t, extradata = assert(serialize(extradata));
                        engine:insert("INSERT INTO `prosody` (`host`,`user`,`store`,`key`,`type`,`value`) VALUES (?,?,?,?,?,?)", host, user or "", store, "", t, extradata);
                end
        end
@@ -133,15 +131,17 @@ map_store.__index = map_store;
 map_store.remove = {};
 function map_store:get(username, key)
        local ok, result = engine:transaction(function()
+               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`=?", host, username or "", self.store, key) do
-                               return deserialize(row[1], row[2]);
+                       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
+                               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`=?", host, username or "", self.store, "") do
-                               local data = deserialize(row[1], row[2]);
-                               return data and data[key] or nil;
+                       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
+                               data = deserialize(row[1], row[2]);
                        end
+                       return data and data[key] or nil;
                end
        end);
        if not ok then return nil, result; end
@@ -163,9 +163,8 @@ function map_store:set_keys(username, keydatas)
                                end
                        else
                                local extradata = {};
-                               for row in engine:select("SELECT `type`, `value` FROM `prosody` WHERE `host`=? AND `user`=? AND `store`=? AND `key`=?", host, username or "", self.store, "") do
+                               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
                                        extradata = deserialize(row[1], row[2]);
-                                       break;
                                end
                                engine:delete("DELETE FROM `prosody` WHERE `host`=? AND `user`=? AND `store`=? AND `key`=?",
                                        host, username or "", self.store, "");
@@ -196,7 +195,7 @@ function archive_store:append(username, key, value, when, with)
                else
                        key = uuid.generate();
                end
-               local t, value = serialize(value);
+               local t, value = assert(serialize(value));
                engine:insert("INSERT INTO `prosodyarchive` (`host`, `user`, `store`, `when`, `with`, `key`, `type`, `value`) VALUES (?,?,?,?,?,?,?,?)", host, user or "", store, when, with, key, t, value);
                return key;
        end);
@@ -235,12 +234,12 @@ 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` > (SELECT `sort_id` FROM `prosodyarchive` WHERE `key` = ? AND `host` = ? AND `user` = ? AND `store` = ? LIMIT 1)"
+               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` < (SELECT `sort_id` FROM `prosodyarchive` WHERE `key` = ? AND `host` = ? AND `user` = ? AND `store` = ? LIMIT 1)"
+               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
@@ -260,8 +259,9 @@ function archive_store:find(username, query)
                if query.total then
                        local stats = engine:select("SELECT COUNT(*) FROM `prosodyarchive` WHERE " .. t_concat(where, " AND "), unpack(args));
                        if stats then
-                               local _total = stats()
-                               total = _total and _total[1];
+                               for row in stats do
+                                       total = row[1];
+                               end
                        end
                        if query.limit == 0 then -- Skip the real query
                                return noop, total;
@@ -275,7 +275,6 @@ function archive_store:find(username, query)
                end
 
                sql_query = sql_query:format(t_concat(where, " AND "), query.reverse and "DESC" or "ASC", query.limit and " LIMIT ?" or "");
-               module:log("debug", sql_query);
                return engine:select(sql_query, unpack(args));
        end);
        if not ok then return ok, result end
@@ -301,7 +300,6 @@ function archive_store:delete(username, query)
                archive_where(query, args, where);
                archive_where_id_range(query, args, where);
                sql_query = sql_query:format(t_concat(where, " AND "));
-               module:log("debug", sql_query);
                return engine:delete(sql_query, unpack(args));
        end);
 end
@@ -401,7 +399,7 @@ local function upgrade_table(params, apply_changes)
                end);
                if not success then
                        module:log("error", "Failed to check/upgrade database schema (%s), please see "
-                               .."http://prosody.im/doc/mysql for help",
+                               .."https://prosody.im/doc/mysql for help",
                                err or "unknown error");
                        return false;
                end
@@ -453,7 +451,7 @@ function module.load()
        local params = normalize_params(module:get_option("sql", default_params));
        engine = engines[sql.db2uri(params)];
        if not engine then
-               module:log("info", "Creating new engine");
+               module:log("debug", "Creating new engine");
                engine = sql:create_engine(params, function (engine)
                        if module:get_option("sql_manage_tables", true) then
                                -- Automatically create table, ignore failure (table probably already exists)