prosody
3ec48555b773
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 @ 13143:3ec48555b773

mod_storage_sql: Add UPSERT support Currently limited to SQLite3 for lack of testing on other databases. Adds a migration to replace the non-UNIQUE prosody_index, renaming it prosody_unique_index since ALTER INDEX does not seem to be portable.
author Kim Alvefur <zash@zash.se>
date Sat, 10 Jun 2023 15:44:09 +0200
parent 12977:74b9e05af71e
child 13144:033ea8b46d6a
comparison
equal deleted inserted replaced
13142:879a6a33c21b 13143:3ec48555b773
201 local insert_sql = [[ 201 local insert_sql = [[
202 INSERT INTO "prosody" 202 INSERT INTO "prosody"
203 ("host","user","store","key","type","value") 203 ("host","user","store","key","type","value")
204 VALUES (?,?,?,?,?,?); 204 VALUES (?,?,?,?,?,?);
205 ]]; 205 ]];
206 local upsert_sql = [[
207 INSERT INTO "prosody"
208 ("host","user","store","key","type","value")
209 VALUES (?,?,?,?,?,?)
210 ON CONFLICT ("host", "user","store", "key")
211 DO UPDATE SET "type"=?, "value"=?;
212 ]];
206 local select_extradata_sql = [[ 213 local select_extradata_sql = [[
207 SELECT "type", "value" 214 SELECT "type", "value"
208 FROM "prosody" 215 FROM "prosody"
209 WHERE "host"=? AND "user"=? AND "store"=? AND "key"=? 216 WHERE "host"=? AND "user"=? AND "store"=? AND "key"=?
210 LIMIT 1; 217 LIMIT 1;
211 ]]; 218 ]];
212 for key, data in pairs(keydatas) do 219 for key, data in pairs(keydatas) do
213 if type(key) == "string" and key ~= "" then 220 if type(key) == "string" and key ~= "" and engine.params.driver == "SQLite3" and data ~= self.remove then
221 local t, value = assert(serialize(data));
222 engine:insert(upsert_sql, host, username or "", self.store, key, t, value, t, value);
223 elseif type(key) == "string" and key ~= "" then
214 engine:delete(delete_sql, 224 engine:delete(delete_sql,
215 host, username or "", self.store, key); 225 host, username or "", self.store, key);
216 if data ~= self.remove then 226 if data ~= self.remove then
217 local t, value = assert(serialize(data)); 227 local t, value = assert(serialize(data));
218 engine:insert(insert_sql, host, username or "", self.store, key, t, value); 228 engine:insert(insert_sql, host, username or "", self.store, key, t, value);
703 Column { name="user", type="TEXT", nullable=false }; 713 Column { name="user", type="TEXT", nullable=false };
704 Column { name="store", type="TEXT", nullable=false }; 714 Column { name="store", type="TEXT", nullable=false };
705 Column { name="key", type="TEXT", nullable=false }; 715 Column { name="key", type="TEXT", nullable=false };
706 Column { name="type", type="TEXT", nullable=false }; 716 Column { name="type", type="TEXT", nullable=false };
707 Column { name="value", type="MEDIUMTEXT", nullable=false }; 717 Column { name="value", type="MEDIUMTEXT", nullable=false };
708 Index { name="prosody_index", "host", "user", "store", "key" }; 718 Index { name = "prosody_unique_index"; unique = engine.params.driver ~= "MySQL"; "host"; "user"; "store"; "key" };
709 }; 719 };
710 engine:transaction(function() 720 engine:transaction(function()
711 ProsodyTable:create(engine); 721 ProsodyTable:create(engine);
712 end); 722 end);
713 723
801 end 811 end
802 end); 812 end);
803 success,err = engine:transaction(function() 813 success,err = engine:transaction(function()
804 return engine:execute(check_encoding_query, params.database, 814 return engine:execute(check_encoding_query, params.database,
805 engine.charset, engine.charset.."_bin"); 815 engine.charset, engine.charset.."_bin");
806 end); 816 end);
807 if not success then 817 if not success then
808 module:log("error", "Failed to check/upgrade database encoding: %s", err or "unknown error"); 818 module:log("error", "Failed to check/upgrade database encoding: %s", err or "unknown error");
809 return false; 819 return false;
810 end 820 end
811 end 821 else
822 local indices = {};
823 engine:transaction(function ()
824 if params.driver == "SQLite3" then
825 for row in engine:select [[SELECT "name" from "sqlite_schema" WHERE "name"='prosody_index';]] do
826 indices[row[1]] = true;
827 end
828 elseif params.driver == "PostgreSQL" then
829 for row in engine:select [[SELECT "relname" FROM "pg_class" WHERE "relname"='prosody_index';]] do
830 indices[row[1]] = true;
831 end
832 end
833 end)
834 if apply_changes then
835 local success = engine:transaction(function ()
836 return assert(engine:execute([[DROP INDEX "prosody_index";]]));
837 end);
838 if not success then
839 module:log("error", "Failed to delete obsolete index \"prosody_index\"");
840 return false;
841 end
842 else
843 changes = changes or indices["prosody_index"];
844 end
845 end
812 return changes; 846 return changes;
813 end 847 end
814 848
815 local function normalize_database(driver, database) -- luacheck: ignore 431/driver 849 local function normalize_database(driver, database) -- luacheck: ignore 431/driver
816 if driver == "SQLite3" and database ~= ":memory:" then 850 if driver == "SQLite3" and database ~= ":memory:" then