prosody
6b84d11aa09b
plugins/mod_storage_sql.lua

Find changesets by keywords by author, files, the commit message, revision number or hash, or revset expression.

Software / code / prosody

Comparison

plugins/mod_storage_sql.lua @ 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
parent 13611:3a23116cf063
child 13634:7209769e88bc
comparison
equal deleted inserted replaced
13632:844e7bf7b48a 13633:6b84d11aa09b
34 local row = result_(); 34 local row = result_();
35 if row ~= nil then 35 if row ~= nil then
36 return unpack(row); 36 return unpack(row);
37 end 37 end
38 end, result, nil; 38 end, result, nil;
39 end
40
41 -- COMPAT Support for UPSERT is not in all versions of all compatible databases.
42 local function has_upsert(engine)
43 if engine.params.driver == "SQLite3" then
44 -- SQLite3 >= 3.24.0
45 return (engine.sqlite_version[2] or 0) >= 24;
46 elseif engine.params.driver == "PostgreSQL" then
47 -- PostgreSQL >= 9.5
48 -- Versions without support have long since reached end of life.
49 return true;
50 end
51 -- We don't support UPSERT on MySQL/MariaDB, they seem to have a completely different syntax, uncertaint from which versions.
52 return false
39 end 53 end
40 54
41 local default_params = { driver = "SQLite3" }; 55 local default_params = { driver = "SQLite3" };
42 56
43 local engine; 57 local engine;
223 FROM "prosody" 237 FROM "prosody"
224 WHERE "host"=? AND "user"=? AND "store"=? AND "key"=? 238 WHERE "host"=? AND "user"=? AND "store"=? AND "key"=?
225 LIMIT 1; 239 LIMIT 1;
226 ]]; 240 ]];
227 for key, data in pairs(keydatas) do 241 for key, data in pairs(keydatas) do
228 if type(key) == "string" and key ~= "" and engine.params.driver ~= "MySQL" and data ~= self.remove then 242 if type(key) == "string" and key ~= "" and has_upsert(engine) and data ~= self.remove then
229 local t, value = assert(serialize(data)); 243 local t, value = assert(serialize(data));
230 engine:insert(upsert_sql, host, username or "", self.store, key, t, value, t, value); 244 engine:insert(upsert_sql, host, username or "", self.store, key, t, value, t, value);
231 elseif type(key) == "string" and key ~= "" then 245 elseif type(key) == "string" and key ~= "" then
232 engine:delete(delete_sql, 246 engine:delete(delete_sql,
233 host, username or "", self.store, key); 247 host, username or "", self.store, key);
931 for row in engine:select("PRAGMA compile_options") do 945 for row in engine:select("PRAGMA compile_options") do
932 local option = row[1]:lower(); 946 local option = row[1]:lower();
933 local opt, val = option:match("^([^=]+)=(.*)$"); 947 local opt, val = option:match("^([^=]+)=(.*)$");
934 compile_options[opt or option] = tonumber(val) or val or true; 948 compile_options[opt or option] = tonumber(val) or val or true;
935 end 949 end
950 -- COMPAT Need to check SQLite3 version because SQLCipher 3.x was based on SQLite3 prior to 3.24.0 when UPSERT was introduced
951 for row in engine:select("SELECT sqlite_version()") do
952 local version = {};
953 for n in row[1]:gmatch("%d+") do
954 table.insert(version, tonumber(n));
955 end
956 engine.sqlite_version = version;
957 end
936 engine.sqlite_compile_options = compile_options; 958 engine.sqlite_compile_options = compile_options;
937 959
938 local journal_mode = "delete"; 960 local journal_mode = "delete";
939 for row in engine:select[[PRAGMA journal_mode;]] do 961 for row in engine:select[[PRAGMA journal_mode;]] do
940 journal_mode = row[1]; 962 journal_mode = row[1];