X-Git-Url: https://git.enpas.org/?a=blobdiff_plain;f=plugins%2Fmod_storage_sql2.lua;h=7a2ec4a76e9fa307eec850adab21ee921f35220b;hb=fea2a20a1c12fe7415426658e55095fee94fe656;hp=7560d1f1963e8d994e03ce38b2334cb30514caec;hpb=2e07422de9b867dce18ff2ffbbf1f6e6863e5a82;p=prosody.git

diff --git a/plugins/mod_storage_sql2.lua b/plugins/mod_storage_sql2.lua
index 7560d1f1..7a2ec4a7 100644
--- a/plugins/mod_storage_sql2.lua
+++ b/plugins/mod_storage_sql2.lua
@@ -27,7 +27,7 @@ local engine; -- TODO create engine
 
 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 };
@@ -35,56 +35,16 @@ local function create_table()
 		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 };
@@ -92,67 +52,75 @@ local function create_table()
 		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
-		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);
+
+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 set database connection encoding to UTF8: %s", err);
-			return;
+			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
-		if params.driver == "MySQL" then
-			-- 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()
-				local result = engine:execute(check_encoding_query);
-				local n_bad_columns = result:rowcount();
-				if n_bad_columns > 0 then
-					module:log("warn", "Found %d columns in prosody table requiring encoding change, updating now...", n_bad_columns);
-					local fix_column_query1 = "ALTER TABLE `prosody` CHANGE `%s` `%s` BLOB;";
-					local fix_column_query2 = "ALTER TABLE `prosody` CHANGE `%s` `%s` %s CHARACTER SET 'utf8' COLLATE 'utf8_bin';";
-					for row in result:rows() do
-						local column_name, column_type = unpack(row);
-						engine:execute(fix_column_query1:format(column_name, column_name));
-						engine:execute(fix_column_query2:format(column_name, column_name, column_type));
-					end
-					module:log("info", "Database encoding upgrade complete!");
+		-- 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' );";
+		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("warn", "Found %d columns in prosody table requiring encoding change, updating now...", n_bad_columns);
+				local fix_column_query1 = "ALTER TABLE `prosody` CHANGE `%s` `%s` BLOB;";
+				local fix_column_query2 = "ALTER TABLE `prosody` CHANGE `%s` `%s` %s CHARACTER SET 'utf8' COLLATE 'utf8_bin';";
+				for row in result:rows() do
+					local column_name, column_type = unpack(row);
+					engine:execute(fix_column_query1:format(column_name, column_name));
+					engine:execute(fix_column_query2:format(column_name, column_name, column_type));
 				end
-			end);
-			local 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");
+				module:log("info", "Database encoding upgrade complete!");
 			end
+		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
 	end
 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)
@@ -205,7 +173,7 @@ local function keyval_store_get()
 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
@@ -248,15 +216,66 @@ end
 
 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;
@@ -266,31 +285,7 @@ function archive_store:find(username, query)
 		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
@@ -304,15 +299,7 @@ function archive_store:find(username, query)
 			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;
@@ -331,6 +318,25 @@ function archive_store:find(username, query)
 	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;