prosody
0b63c2c3b563
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 @ 13837:0b63c2c3b563

Merge 13.0->trunk
author Kim Alvefur <zash@zash.se>
date Wed, 09 Apr 2025 20:28:28 +0200
parent 13836:c600794cafb6
child 13838:a93e7310bfcd
comparison
equal deleted inserted replaced
13831:bf4cd327966f 13837:0b63c2c3b563
11 local is_stanza = require"prosody.util.stanza".is_stanza; 11 local is_stanza = require"prosody.util.stanza".is_stanza;
12 local t_concat = table.concat; 12 local t_concat = table.concat;
13 13
14 local have_dbisql, dbisql = pcall(require, "prosody.util.sql"); 14 local have_dbisql, dbisql = pcall(require, "prosody.util.sql");
15 local have_sqlite, sqlite = pcall(require, "prosody.util.sqlite3"); 15 local have_sqlite, sqlite = pcall(require, "prosody.util.sqlite3");
16 if not have_dbisql then
17 module:log("debug", "Could not load LuaDBI: %s", dbisql)
18 dbisql = nil;
19 end
20 if not have_sqlite then
21 module:log("debug", "Could not load LuaSQLite3: %s", sqlite)
22 sqlite = nil;
23 end
24 if not (have_dbisql or have_sqlite) then 16 if not (have_dbisql or have_sqlite) then
25 module:log("error", "LuaDBI or LuaSQLite3 are required for using SQL databases but neither are installed"); 17 module:log("error", "LuaDBI or LuaSQLite3 are required for using SQL databases but neither are installed");
26 module:log("error", "Please install at least one of LuaDBI and LuaSQLite3. See https://prosody.im/doc/depends"); 18 module:log("error", "Please install at least one of LuaDBI and LuaSQLite3. See https://prosody.im/doc/depends");
19 module:log("debug", "Could not load LuaDBI: %s", dbisql);
20 module:log("debug", "Could not load LuaSQLite3: %s", sqlite);
27 error("No SQL library available") 21 error("No SQL library available")
22 end
23
24 local function get_sql_lib(driver)
25 if driver == "SQLite3" and have_sqlite then
26 return sqlite;
27 elseif have_dbisql then
28 return dbisql;
29 else
30 error(dbisql);
31 end
28 end 32 end
29 33
30 local noop = function() end 34 local noop = function() end
31 local unpack = table.unpack; 35 local unpack = table.unpack;
32 local function iterator(result) 36 local function iterator(result)
40 44
41 -- COMPAT Support for UPSERT is not in all versions of all compatible databases. 45 -- COMPAT Support for UPSERT is not in all versions of all compatible databases.
42 local function has_upsert(engine) 46 local function has_upsert(engine)
43 if engine.params.driver == "SQLite3" then 47 if engine.params.driver == "SQLite3" then
44 -- SQLite3 >= 3.24.0 48 -- SQLite3 >= 3.24.0
45 return engine.sqlite_version and (engine.sqlite_version[2] or 0) >= 24; 49 return engine.sqlite_version and (engine.sqlite_version[2] or 0) >= 24 and engine.has_upsert_index;
46 elseif engine.params.driver == "PostgreSQL" then 50 elseif engine.params.driver == "PostgreSQL" then
47 -- PostgreSQL >= 9.5 51 -- PostgreSQL >= 9.5
48 -- Versions without support have long since reached end of life. 52 -- Versions without support have long since reached end of life.
49 return true; 53 return engine.has_upsert_index;
50 end 54 end
51 -- We don't support UPSERT on MySQL/MariaDB, they seem to have a completely different syntax, uncertaint from which versions. 55 -- We don't support UPSERT on MySQL/MariaDB, they seem to have a completely different syntax, uncertaint from which versions.
52 return false 56 return false
53 end 57 end
54 58
755 759
756 --- Initialization 760 --- Initialization
757 761
758 762
759 local function create_table(engine) -- luacheck: ignore 431/engine 763 local function create_table(engine) -- luacheck: ignore 431/engine
760 local sql = engine.params.driver == "SQLite3" and sqlite or dbisql; 764 local sql = get_sql_lib(engine.params.driver);
761 local Table, Column, Index = sql.Table, sql.Column, sql.Index; 765 local Table, Column, Index = sql.Table, sql.Column, sql.Index;
762 766
763 local ProsodyTable = Table { 767 local ProsodyTable = Table {
764 name = "prosody"; 768 name = "prosody";
765 Column { name="host", type="TEXT", nullable=false }; 769 Column { name="host", type="TEXT", nullable=false };
796 end 800 end
797 801
798 local function upgrade_table(engine, params, apply_changes) -- luacheck: ignore 431/engine 802 local function upgrade_table(engine, params, apply_changes) -- luacheck: ignore 431/engine
799 local changes = false; 803 local changes = false;
800 if params.driver == "MySQL" then 804 if params.driver == "MySQL" then
801 local sql = dbisql; 805 local sql = get_sql_lib("MySQL");
802 local success,err = engine:transaction(function() 806 local success,err = engine:transaction(function()
803 do 807 do
804 local result = assert(engine:execute("SHOW COLUMNS FROM \"prosody\" WHERE \"Field\"='value' and \"Type\"='text'")); 808 local result = assert(engine:execute("SHOW COLUMNS FROM \"prosody\" WHERE \"Field\"='value' and \"Type\"='text'"));
805 if result:rowcount() > 0 then 809 if result:rowcount() > 0 then
806 changes = true; 810 changes = true;
891 if not success then 895 if not success then
892 module:log("error", "Failed to delete obsolete index \"prosody_index\""); 896 module:log("error", "Failed to delete obsolete index \"prosody_index\"");
893 return false; 897 return false;
894 end 898 end
895 end 899 end
900 if not indices["prosody_unique_index"] then
901 module:log("warn", "Index \"prosody_unique_index\" does not exist, performance may be worse than normal!");
902 engine.has_upsert_index = false;
903 else
904 engine.has_upsert_index = true;
905 end
896 end 906 end
897 return changes; 907 return changes;
898 end 908 end
899 909
900 local function normalize_database(driver, database) -- luacheck: ignore 431/driver 910 local function normalize_database(driver, database) -- luacheck: ignore 431/driver
918 end 928 end
919 929
920 function module.load() 930 function module.load()
921 local engines = module:shared("/*/sql/connections"); 931 local engines = module:shared("/*/sql/connections");
922 local params = normalize_params(module:get_option("sql", default_params)); 932 local params = normalize_params(module:get_option("sql", default_params));
923 local sql = params.driver == "SQLite3" and sqlite or dbisql; 933 local sql = get_sql_lib(params.driver);
924 local db_uri = sql.db2uri(params); 934 local db_uri = sql.db2uri(params);
925 engine = engines[db_uri]; 935 engine = engines[db_uri];
926 if not engine then 936 if not engine then
927 module:log("debug", "Creating new engine %s", db_uri); 937 module:log("debug", "Creating new engine %s", db_uri);
928 engine = sql:create_engine(params, function (engine) -- luacheck: ignore 431/engine 938 engine = sql:create_engine(params, function (engine) -- luacheck: ignore 431/engine
1010 if command == "upgrade" then 1020 if command == "upgrade" then
1011 -- We need to find every unique dburi in the config 1021 -- We need to find every unique dburi in the config
1012 local uris = {}; 1022 local uris = {};
1013 for host in pairs(prosody.hosts) do -- luacheck: ignore 431/host 1023 for host in pairs(prosody.hosts) do -- luacheck: ignore 431/host
1014 local params = normalize_params(config.get(host, "sql") or default_params); 1024 local params = normalize_params(config.get(host, "sql") or default_params);
1015 local sql = engine.params.driver == "SQLite3" and sqlite or dbisql; 1025 local sql = get_sql_lib(engine.params.driver);
1016 uris[sql.db2uri(params)] = params; 1026 uris[sql.db2uri(params)] = params;
1017 end 1027 end
1018 print("We will check and upgrade the following databases:\n"); 1028 print("We will check and upgrade the following databases:\n");
1019 for _, params in pairs(uris) do 1029 for _, params in pairs(uris) do
1020 print("", "["..params.driver.."] "..params.database..(params.host and " on "..params.host or "")); 1030 print("", "["..params.driver.."] "..params.database..(params.host and " on "..params.host or ""));
1026 return; 1036 return;
1027 end 1037 end
1028 -- Upgrade each one 1038 -- Upgrade each one
1029 for _, params in pairs(uris) do 1039 for _, params in pairs(uris) do
1030 print("Checking "..params.database.."..."); 1040 print("Checking "..params.database.."...");
1031 local sql = params.driver == "SQLite3" and sqlite or dbisql; 1041 local sql = get_sql_lib(params.driver);
1032 engine = sql:create_engine(params); 1042 engine = sql:create_engine(params);
1033 upgrade_table(engine, params, true); 1043 upgrade_table(engine, params, true);
1034 end 1044 end
1035 print("All done!"); 1045 print("All done!");
1036 elseif command then 1046 elseif command then
1038 else 1048 else
1039 print("Available commands:"); 1049 print("Available commands:");
1040 print("","upgrade - Perform database upgrade"); 1050 print("","upgrade - Perform database upgrade");
1041 end 1051 end
1042 end 1052 end
1053
1054 module:add_item("shell-command", {
1055 section = "sql";
1056 section_desc = "SQL management commands";
1057 name = "create";
1058 desc = "Create the tables and indices used by Prosody (again)";
1059 args = { { name = "host"; type = "string" } };
1060 host_selector = "host";
1061 handler = function(shell, _host)
1062 local logger = require "prosody.util.logger";
1063 local writing = false;
1064 local sink = logger.add_simple_sink(function (source, level, message)
1065 local print = shell.session.print;
1066 if writing or source ~= "sql" then return; end
1067 writing = true;
1068 print(message);
1069 writing = false;
1070 end);
1071
1072 local debug_enabled = engine._debug;
1073 engine:debug(true);
1074 create_table(engine);
1075 engine:debug(debug_enabled);
1076
1077 if not logger.remove_sink(sink) then
1078 module:log("warn", "Unable to remove log sink");
1079 end
1080 end;
1081 })