aboutsummaryrefslogtreecommitdiffstats
path: root/plugins/mod_storage_sql.lua
diff options
context:
space:
mode:
Diffstat (limited to 'plugins/mod_storage_sql.lua')
-rw-r--r--plugins/mod_storage_sql.lua106
1 files changed, 84 insertions, 22 deletions
diff --git a/plugins/mod_storage_sql.lua b/plugins/mod_storage_sql.lua
index 13c961f8..56cef569 100644
--- a/plugins/mod_storage_sql.lua
+++ b/plugins/mod_storage_sql.lua
@@ -11,7 +11,7 @@ local is_stanza = require"util.stanza".is_stanza;
local t_concat = table.concat;
local noop = function() end
-local unpack = unpack
+local unpack = table.unpack or unpack;
local function iterator(result)
return function(result_)
local row = result_();
@@ -43,12 +43,17 @@ local function deserialize(t, value)
elseif t == "boolean" then
if value == "true" then return true;
elseif value == "false" then return false; end
- elseif t == "number" then return tonumber(value);
+ return nil, "invalid-boolean";
+ elseif t == "number" then
+ value = tonumber(value);
+ if value then return value; end
+ return nil, "invalid-number";
elseif t == "json" then
return json.decode(value);
elseif t == "xml" then
return xml_parse(value);
end
+ return nil, "Unhandled value type: "..t;
end
local host = module.host;
@@ -65,7 +70,8 @@ local function keyval_store_get()
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]);
+ local v, e = deserialize(row[2], row[3]);
+ assert(v ~= nil, e);
if k and v then
if k ~= "" then result[k] = v; elseif type(v) == "table" then
for a,b in pairs(v) do
@@ -136,7 +142,7 @@ function keyval_store:users()
]];
return engine:select(select_sql, host, self.store);
end);
- if not ok then return ok, result end
+ if not ok then error(result); end
return iterator(result);
end
@@ -154,15 +160,17 @@ function map_store:get(username, key)
WHERE "host"=? AND "user"=? AND "store"=? AND "key"=?
LIMIT 1
]];
- local data;
+ local data, err;
if type(key) == "string" and key ~= "" then
for row in engine:select(query, host, username or "", self.store, key) do
- data = deserialize(row[1], row[2]);
+ data, err = deserialize(row[1], row[2]);
+ assert(data ~= nil, err);
end
return data;
else
for row in engine:select(query, host, username or "", self.store, "") do
- data = deserialize(row[1], row[2]);
+ data, err = deserialize(row[1], row[2]);
+ assert(data ~= nil, err);
end
return data and data[key] or nil;
end
@@ -200,9 +208,10 @@ function map_store:set_keys(username, keydatas)
engine:insert(insert_sql, host, username or "", self.store, key, t, value);
end
else
- local extradata = {};
+ local extradata, err = {};
for row in engine:select(select_extradata_sql, host, username or "", self.store, "") do
- extradata = deserialize(row[1], row[2]);
+ extradata, err = deserialize(row[1], row[2]);
+ assert(extradata ~= nil, err);
end
engine:delete(delete_sql, host, username or "", self.store, "");
extradata[key] = data;
@@ -356,7 +365,9 @@ function archive_store:find(username, query)
return function()
local row = result();
if row ~= nil then
- return row[1], deserialize(row[2], row[3]), row[4], row[5];
+ local value, err = deserialize(row[2], row[3]);
+ assert(value ~= nil, err);
+ return row[1], value, row[4], row[5];
end
end, total;
end
@@ -374,7 +385,41 @@ function archive_store:delete(username, query)
end
archive_where(query, args, where);
archive_where_id_range(query, args, where);
- sql_query = sql_query:format(t_concat(where, " AND "));
+ if query.truncate == nil then
+ sql_query = sql_query:format(t_concat(where, " AND "));
+ else
+ args[#args+1] = query.truncate;
+ local unlimited = "ALL";
+ if engine.params.driver == "SQLite3" then
+ sql_query = [[
+ DELETE FROM "prosodyarchive"
+ WHERE %s
+ ORDER BY "sort_id" %s
+ LIMIT %s OFFSET ?;
+ ]];
+ unlimited = "-1";
+ elseif engine.params.driver == "MySQL" then
+ sql_query = [[
+ DELETE result FROM prosodyarchive AS result JOIN (
+ SELECT sort_id FROM prosodyarchive
+ WHERE %s
+ ORDER BY "sort_id" %s
+ LIMIT %s OFFSET ?
+ ) AS limiter on result.sort_id = limiter.sort_id;]];
+ unlimited = "18446744073709551615";
+ else
+ sql_query = [[
+ DELETE FROM "prosodyarchive"
+ WHERE "sort_id" IN (
+ SELECT "sort_id" FROM "prosodyarchive"
+ WHERE %s
+ ORDER BY "sort_id" %s
+ LIMIT %s OFFSET ?
+ );]];
+ end
+ sql_query = string.format(sql_query, t_concat(where, " AND "),
+ query.reverse and "ASC" or "DESC", unlimited);
+ end
return engine:delete(sql_query, unpack(args));
end);
return ok and stmt:affected(), stmt;
@@ -423,11 +468,11 @@ end
--- Initialization
-local function create_table(engine, name) -- luacheck: ignore 431/engine
+local function create_table(engine) -- luacheck: ignore 431/engine
local Table, Column, Index = sql.Table, sql.Column, sql.Index;
local ProsodyTable = Table {
- name= name or "prosody";
+ name = "prosody";
Column { name="host", type="TEXT", nullable=false };
Column { name="user", type="TEXT", nullable=false };
Column { name="store", type="TEXT", nullable=false };
@@ -451,7 +496,7 @@ local function create_table(engine, name) -- luacheck: ignore 431/engine
Column { name="with", type="TEXT", nullable=false }; -- related id
Column { name="type", type="TEXT", nullable=false };
Column { name="value", type="MEDIUMTEXT", nullable=false };
- Index { name="prosodyarchive_index", unique = true, "host", "user", "store", "key" };
+ Index { name="prosodyarchive_index", unique = engine.params.driver ~= "MySQL", "host", "user", "store", "key" };
Index { name="prosodyarchive_with_when", "host", "user", "store", "with", "when" };
Index { name="prosodyarchive_when", "host", "user", "store", "when" };
};
@@ -464,20 +509,37 @@ local function upgrade_table(engine, params, apply_changes) -- luacheck: ignore
local changes = false;
if params.driver == "MySQL" then
local success,err = engine:transaction(function()
- local result = engine:execute("SHOW COLUMNS FROM \"prosody\" WHERE \"Field\"='value' and \"Type\"='text'");
- if result:rowcount() > 0 then
- changes = true;
- if apply_changes then
- module:log("info", "Upgrading database schema...");
- engine:execute("ALTER TABLE \"prosody\" MODIFY COLUMN \"value\" MEDIUMTEXT");
- module:log("info", "Database table automatically upgraded");
+ do
+ local result = assert(engine:execute("SHOW COLUMNS FROM \"prosody\" WHERE \"Field\"='value' and \"Type\"='text'"));
+ if result:rowcount() > 0 then
+ changes = true;
+ if apply_changes then
+ module:log("info", "Upgrading database schema (value column size)...");
+ assert(engine:execute("ALTER TABLE \"prosody\" MODIFY COLUMN \"value\" MEDIUMTEXT"));
+ module:log("info", "Database table automatically upgraded");
+ end
+ end
+ end
+
+ do
+ -- Ensure index is not unique (issue #1073)
+ local result = assert(engine:execute([[SHOW INDEX FROM prosodyarchive WHERE key_name='prosodyarchive_index' and non_unique=0]]));
+ if result:rowcount() > 0 then
+ changes = true;
+ if apply_changes then
+ module:log("info", "Upgrading database schema (prosodyarchive_index)...");
+ assert(engine:execute[[ALTER TABLE "prosodyarchive" DROP INDEX prosodyarchive_index;]]);
+ local new_index = sql.Index { table = "prosodyarchive", name="prosodyarchive_index", "host", "user", "store", "key" };
+ assert(engine:_create_index(new_index));
+ module:log("info", "Database table automatically upgraded");
+ end
end
end
return true;
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
generated by cgit v1.2.3 (git 2.25.1) at 2024-11-06 15:48:31 +0000