Diff

--- a/plugins/mod_storage_sql.lua	Sat Apr 01 19:25:34 2017 +0200
+++ b/plugins/mod_storage_sql.lua	Sat Apr 01 19:26:27 2017 +0200
@@ -57,7 +57,12 @@
 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 @@
 	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 @@
 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.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 @@
 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 @@
 	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 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 @@
 	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 @@
 
 		-- 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 @@
 			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 @@
 		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);