local function create_table()
local Table,Column,Index = mod_sql.Table,mod_sql.Column,mod_sql.Index;
- --[[
+
local ProsodyTable = Table {
name="prosody";
Column { name="host", type="TEXT", nullable=false };
Column { name="store", type="TEXT", nullable=false };
Column { name="key", type="TEXT", nullable=false };
Column { name="type", type="TEXT", nullable=false };
- Column { name="value", type="TEXT", nullable=false };
+ Column { name="value", type="MEDIUMTEXT", nullable=false };
Index { name="prosody_index", "host", "user", "store", "key" };
};
engine:transaction(function()
ProsodyTable:create(engine);
- end);]]
- if not module:get_option("sql_manage_tables", true) then
- return;
- end
-
- local create_sql = "CREATE TABLE `prosody` (`host` TEXT, `user` TEXT, `store` TEXT, `key` TEXT, `type` TEXT, `value` TEXT);";
- if params.driver == "PostgreSQL" then
- create_sql = create_sql:gsub("`", "\"");
- elseif params.driver == "MySQL" then
- create_sql = create_sql:gsub("`value` TEXT", "`value` MEDIUMTEXT")
- :gsub(";$", " CHARACTER SET 'utf8' COLLATE 'utf8_bin';");
- end
-
- local index_sql = "CREATE INDEX `prosody_index` ON `prosody` (`host`, `user`, `store`, `key`)";
- if params.driver == "PostgreSQL" then
- index_sql = index_sql:gsub("`", "\"");
- elseif params.driver == "MySQL" then
- index_sql = index_sql:gsub("`([,)])", "`(20)%1");
- end
-
- local success,err = engine:transaction(function()
- engine:execute(create_sql);
- engine:execute(index_sql);
end);
- if not success then -- so we failed to create
- if params.driver == "MySQL" then
- success,err = engine:transaction(function()
- local result = engine:execute("SHOW COLUMNS FROM prosody WHERE Field='value' and Type='text'");
- if result:rowcount() > 0 then
- module:log("info", "Upgrading database schema...");
- engine:execute("ALTER TABLE prosody MODIFY COLUMN `value` MEDIUMTEXT");
- module:log("info", "Database table automatically upgraded");
- 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",
- err or "unknown error");
- end
- end
- end
+
local ProsodyArchiveTable = Table {
name="prosodyarchive";
- Column { name="sort_id", type="INTEGER PRIMARY KEY AUTOINCREMENT", nullable=false };
+ Column { name="sort_id", type="INTEGER", primary_key=true, auto_increment=true };
Column { name="host", type="TEXT", nullable=false };
Column { name="user", type="TEXT", nullable=false };
Column { name="store", type="TEXT", nullable=false };
Column { name="when", type="INTEGER", nullable=false }; -- timestamp
Column { name="with", type="TEXT", nullable=false }; -- related id
Column { name="type", type="TEXT", nullable=false };
- Column { name="value", type=params.driver == "MySQL" and "MEDIUMTEXT" or "TEXT", nullable=false };
- Index { name="prosodyarchive_index", "host", "user", "store", "key" };
+ Column { name="value", type="MEDIUMTEXT", nullable=false };
+ Index { name="prosodyarchive_index", unique = true, "host", "user", "store", "key" };
};
engine:transaction(function()
ProsodyArchiveTable:create(engine);
end);
end
-local function set_encoding()
- if params.driver == "SQLite3" then return end
- local set_names_query = "SET NAMES 'utf8';";
- if params.driver == "MySQL" then
- set_names_query = set_names_query:gsub(";$", " COLLATE 'utf8_bin';");
- end
- local success,err = engine:transaction(function() return engine:execute(set_names_query); end);
- if not success then
- module:log("error", "Failed to set database connection encoding to UTF8: %s", err);
- return;
- end
+
+local function upgrade_table()
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
+ module:log("info", "Upgrading database schema...");
+ engine:execute("ALTER TABLE prosody MODIFY COLUMN `value` MEDIUMTEXT");
+ module:log("info", "Database table automatically upgraded");
+ 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",
+ err or "unknown error");
+ return false;
+ end
-- COMPAT w/pre-0.9: Upgrade tables to UTF-8 if not already
local check_encoding_query = "SELECT `COLUMN_NAME`,`COLUMN_TYPE` FROM `information_schema`.`columns` WHERE `TABLE_NAME`='prosody' AND ( `CHARACTER_SET_NAME`!='utf8' OR `COLLATION_NAME`!='utf8_bin' );";
- local success,err = engine:transaction(function()
+ success,err = engine:transaction(function()
local result = engine:execute(check_encoding_query);
local n_bad_columns = result:rowcount();
if n_bad_columns > 0 then
module:log("info", "Database encoding upgrade complete!");
end
end);
- local success,err = engine:transaction(function() return engine:execute(check_encoding_query); end);
+ success,err = engine:transaction(function() return engine:execute(check_encoding_query); end);
if not success then
module:log("error", "Failed to check/upgrade database encoding: %s", err or "unknown error");
end
do -- process options to get a db connection
params = params or { driver = "SQLite3" };
-
+
if params.driver == "SQLite3" then
params.database = resolve_relative_path(prosody.paths.data or ".", params.database or "prosody.sqlite");
end
-
+
assert(params.driver and params.database, "Both the SQL driver and the database need to be specified");
--local dburi = db2uri(params);
engine = mod_sql:create_engine(params);
-
- -- Encoding mess
- set_encoding();
- -- Automatically create table, ignore failure (table probably already exists)
- create_table();
+ engine:set_encoding();
+
+ if module:get_option("sql_manage_tables", true) then
+ -- Automatically create table, ignore failure (table probably already exists)
+ create_table();
+ -- Encoding mess
+ upgrade_table();
+ end
end
local function serialize(value)
end
local function keyval_store_set(data)
engine:delete("DELETE FROM `prosody` WHERE `host`=? AND `user`=? AND `store`=?", host, user or "", store);
-
+
if data and next(data) ~= nil then
local extradata = {};
for key, value in pairs(data) do
local archive_store = {}
archive_store.__index = archive_store
-function archive_store:append(username, when, with, value)
+function archive_store:append(username, key, when, with, value)
+ if value == nil then -- COMPAT early versions
+ when, with, value, key = key, when, with, value
+ end
local user,store = username,self.store;
return engine:transaction(function()
- local key = uuid.generate();
+ if key then
+ engine:delete("DELETE FROM `prosodyarchive` WHERE `host`=? AND `user`=? AND `store`=? AND `key`=?", host, user or "", store, key);
+ else
+ key = uuid.generate();
+ end
local t, value = 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);
end
+
+-- Helpers for building the WHERE clause
+local function archive_where(query, args, where)
+ -- Time range, inclusive
+ if query.start then
+ args[#args+1] = query.start
+ where[#where+1] = "`when` >= ?"
+ end
+
+ if query["end"] then
+ args[#args+1] = query["end"];
+ if query.start then
+ where[#where] = "`when` BETWEEN ? AND ?" -- is this inclusive?
+ else
+ where[#where+1] = "`when` <= ?"
+ end
+ end
+
+ -- Related name
+ if query.with then
+ where[#where+1] = "`with` = ?";
+ args[#args+1] = query.with
+ end
+
+ -- Unique id
+ if query.key then
+ where[#where+1] = "`key` = ?";
+ args[#args+1] = query.key
+ end
+end
+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)"
+ 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)"
+ 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
+
function archive_store:find(username, query)
query = query or {};
local user,store = username,self.store;
local args = { host, user or "", store, };
local where = { "`host` = ?", "`user` = ?", "`store` = ?", };
- -- Time range, inclusive
- if query.start then
- args[#args+1] = query.start
- where[#where+1] = "`when` >= ?"
- end
- if query["end"] then
- args[#args+1] = query["end"];
- if query.start then
- where[#where] = "`when` BETWEEN ? AND ?" -- is this inclusive?
- else
- where[#where+1] = "`when` >= ?"
- end
- end
-
- -- Related name
- if query.with then
- where[#where+1] = "`with` = ?";
- args[#args+1] = query.with
- end
-
- -- Unique id
- if query.key then
- where[#where+1] = "`key` = ?";
- args[#args+1] = query.key
- end
+ archive_where(query, args, where);
-- Total matching
if query.total then
end
end
- -- Before or after specific item, exclusive
- if query.after then
- where[#where+1] = "`sort_id` > (SELECT `sort_id` FROM `prosodyarchive` WHERE `key` = ? LIMIT 1)"
- args[#args+1] = query.after
- end
- if query.before then
- where[#where+1] = "`sort_id` < (SELECT `sort_id` FROM `prosodyarchive` WHERE `key` = ? LIMIT 1)"
- args[#args+1] = query.before
- end
+ archive_where_id_range(query, args, where);
if query.limit then
args[#args+1] = query.limit;
end, total;
end
+function archive_store:delete(username, query)
+ query = query or {};
+ local user,store = username,self.store;
+ return engine:transaction(function()
+ local sql_query = "DELETE FROM `prosodyarchive` WHERE %s;";
+ local args = { host, user or "", store, };
+ local where = { "`host` = ?", "`user` = ?", "`store` = ?", };
+ if user == true then
+ table.remove(args, 2);
+ table.remove(where, 2);
+ end
+ 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
+
local stores = {
keyval = keyval_store;
archive = archive_store;
projects / prosody.git / blobdiff