mod_storage_sql: Detect SQLite3 without UPSERT (or SQLCipher 3.x)

SQLCipher v3.4.1 (the version in Debian 12) is based on SQLite3 v3.15.2,
while UPSERT support was introduced in SQLite3 v3.24.0

This check was not needed before because we v3.24.0 has not been in a
version of Debian we support for a long, long time.

Note however that SQLCipher databases are not compatible across major
versions, upgrading from v3.x to v4.x requires executing a migration.
Attempts at making `prosodyctl mod_storage_sql upgrade` perform such a
migration has not been successful.

Executing the following in the `sqlcipher` tool should do the migration:

	PRAGMA key = '<key material>';
	PRAGMA cipher_migrate;

1 files changed, 23 insertions, 1 deletions

diff --git a/plugins/mod_storage_sql.lua b/plugins/mod_storage_sql.lua
index f24f11fc..605904ab 100644
--- a/plugins/mod_storage_sql.lua
+++ b/plugins/mod_storage_sql.lua
@@ -38,6 +38,20 @@ local function iterator(result)
 	end, result, nil;
 end
 
+-- COMPAT Support for UPSERT is not in all versions of all compatible databases.
+local function has_upsert(engine)
+	if engine.params.driver == "SQLite3" then
+		-- SQLite3 >= 3.24.0
+		return (engine.sqlite_version[2] or 0) >= 24;
+	elseif engine.params.driver == "PostgreSQL" then
+		-- PostgreSQL >= 9.5
+		-- Versions without support have long since reached end of life.
+		return true;
+	end
+	-- We don't support UPSERT on MySQL/MariaDB, they seem to have a completely different syntax, uncertaint from which versions.
+	return false
+end
+
 local default_params = { driver = "SQLite3" };
 
 local engine;
@@ -225,7 +239,7 @@ function map_store:set_keys(username, keydatas)
 		LIMIT 1;
 		]];
 		for key, data in pairs(keydatas) do
-			if type(key) == "string" and key ~= "" and engine.params.driver ~= "MySQL" and data ~= self.remove then
+			if type(key) == "string" and key ~= "" and has_upsert(engine) and data ~= self.remove then
 				local t, value = assert(serialize(data));
 				engine:insert(upsert_sql, host, username or "", self.store, key, t, value, t, value);
 			elseif type(key) == "string" and key ~= "" then
@@ -933,6 +947,14 @@ function module.load()
 					local opt, val = option:match("^([^=]+)=(.*)$");
 					compile_options[opt or option] = tonumber(val) or val or true;
 				end
+				-- COMPAT Need to check SQLite3 version because SQLCipher 3.x was based on SQLite3 prior to 3.24.0 when UPSERT was introduced
+				for row in engine:select("SELECT sqlite_version()") do
+					local version = {};
+					for n in row[1]:gmatch("%d+") do
+						table.insert(version, tonumber(n));
+					end
+					engine.sqlite_version = version;
+				end
 				engine.sqlite_compile_options = compile_options;
 
 				local journal_mode = "delete";