Changeset

13633:6b84d11aa09b

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;
author Kim Alvefur <zash@zash.se>
date Thu, 23 Jan 2025 19:33:05 +0100
parents 13632:844e7bf7b48a
children 13634:7209769e88bc
files plugins/mod_storage_sql.lua
diffstat 1 files changed, 23 insertions(+), 1 deletions(-) [+]
line wrap: on
line diff
--- 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";