# HG changeset patch
# User Kim Alvefur <zash@zash.se>
# Date 1737657185 -3600
# Node ID 6b84d11aa09b7eb74f2814c4b117a021e0c59c24
# Parent  844e7bf7b48a7e7f0a788e5240392d017ddd24f2
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;

diff -r 844e7bf7b48a -r 6b84d11aa09b plugins/mod_storage_sql.lua
--- a/plugins/mod_storage_sql.lua	Thu Jan 23 16:38:56 2025 +0100
+++ b/plugins/mod_storage_sql.lua	Thu Jan 23 19:33:05 2025 +0100
@@ -38,6 +38,20 @@
 	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 @@
 		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 @@
 					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";