Software `/` code `/` prosody

Annotate

plugins/mod_storage_sql.lua @ 13832:5973a5d22ab5 13.0

mod_storage_sql: Delay showing SQL library error until attempted load This should ensure that e.g. failure to load LuaSQLite3 is not logged unless it is needed, since module failures are very verbose. Closes #1919

author	Kim Alvefur <zash@zash.se>
date	Mon, 07 Apr 2025 20:23:00 +0200
parent	13784:b30eaa74d35b
child	13833:497efa2cbf2c

rev	line source
7156 18b7ffd08d56 mod_storage_sql: Silence some harmless luacheck warnings Kim Alvefur <zash@zash.se> parents: 7153 diff changeset	1
18b7ffd08d56 mod_storage_sql: Silence some harmless luacheck warnings Kim Alvefur <zash@zash.se> parents: 7153 diff changeset	2 -- luacheck: ignore 212/self
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	3
12977 74b9e05af71e plugins: Prefix module imports with prosody namespace Kim Alvefur <zash@zash.se> parents: 12875 diff changeset	4 local cache = require "prosody.util.cache";
74b9e05af71e plugins: Prefix module imports with prosody namespace Kim Alvefur <zash@zash.se> parents: 12875 diff changeset	5 local json = require "prosody.util.json";
74b9e05af71e plugins: Prefix module imports with prosody namespace Kim Alvefur <zash@zash.se> parents: 12875 diff changeset	6 local xml_parse = require "prosody.util.xml".parse;
74b9e05af71e plugins: Prefix module imports with prosody namespace Kim Alvefur <zash@zash.se> parents: 12875 diff changeset	7 local uuid = require "prosody.util.uuid";
74b9e05af71e plugins: Prefix module imports with prosody namespace Kim Alvefur <zash@zash.se> parents: 12875 diff changeset	8 local resolve_relative_path = require "prosody.util.paths".resolve_relative_path;
74b9e05af71e plugins: Prefix module imports with prosody namespace Kim Alvefur <zash@zash.se> parents: 12875 diff changeset	9 local jid_join = require "prosody.util.jid".join;
4096 3b991ceb228e mod_storage_sql: Make the 'database' value for the SQLite3 driver relative to the data path Matthew Wild <mwild1@gmail.com> parents: 4007 diff changeset	10
12977 74b9e05af71e plugins: Prefix module imports with prosody namespace Kim Alvefur <zash@zash.se> parents: 12875 diff changeset	11 local is_stanza = require"prosody.util.stanza".is_stanza;
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	12 local t_concat = table.concat;
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	13
13242 0d3881bf29a8 mod_storage_sql: Spell out missing dependencies Kim Alvefur <zash@zash.se> parents: 13241 diff changeset	14 local have_dbisql, dbisql = pcall(require, "prosody.util.sql");
0d3881bf29a8 mod_storage_sql: Spell out missing dependencies Kim Alvefur <zash@zash.se> parents: 13241 diff changeset	15 local have_sqlite, sqlite = pcall(require, "prosody.util.sqlite3");
0d3881bf29a8 mod_storage_sql: Spell out missing dependencies Kim Alvefur <zash@zash.se> parents: 13241 diff changeset	16 if not (have_dbisql or have_sqlite) then
0d3881bf29a8 mod_storage_sql: Spell out missing dependencies Kim Alvefur <zash@zash.se> parents: 13241 diff changeset	17 module:log("error", "LuaDBI or LuaSQLite3 are required for using SQL databases but neither are installed");
0d3881bf29a8 mod_storage_sql: Spell out missing dependencies Kim Alvefur <zash@zash.se> parents: 13241 diff changeset	18 module:log("error", "Please install at least one of LuaDBI and LuaSQLite3. See https://prosody.im/doc/depends");
13832 5973a5d22ab5 mod_storage_sql: Delay showing SQL library error until attempted load Kim Alvefur <zash@zash.se> parents: 13784 diff changeset	19 module:log("debug", "Could not load LuaDBI: %s", dbisql);
5973a5d22ab5 mod_storage_sql: Delay showing SQL library error until attempted load Kim Alvefur <zash@zash.se> parents: 13784 diff changeset	20 module:log("debug", "Could not load LuaSQLite3: %s", sqlite);
13242 0d3881bf29a8 mod_storage_sql: Spell out missing dependencies Kim Alvefur <zash@zash.se> parents: 13241 diff changeset	21 error("No SQL library available")
0d3881bf29a8 mod_storage_sql: Spell out missing dependencies Kim Alvefur <zash@zash.se> parents: 13241 diff changeset	22 end
0d3881bf29a8 mod_storage_sql: Spell out missing dependencies Kim Alvefur <zash@zash.se> parents: 13241 diff changeset	23
13832 5973a5d22ab5 mod_storage_sql: Delay showing SQL library error until attempted load Kim Alvefur <zash@zash.se> parents: 13784 diff changeset	24 local function get_sql_lib(driver)
5973a5d22ab5 mod_storage_sql: Delay showing SQL library error until attempted load Kim Alvefur <zash@zash.se> parents: 13784 diff changeset	25 if driver == "SQLite3" and have_sqlite then
5973a5d22ab5 mod_storage_sql: Delay showing SQL library error until attempted load Kim Alvefur <zash@zash.se> parents: 13784 diff changeset	26 return sqlite;
5973a5d22ab5 mod_storage_sql: Delay showing SQL library error until attempted load Kim Alvefur <zash@zash.se> parents: 13784 diff changeset	27 elseif have_dbisql then
5973a5d22ab5 mod_storage_sql: Delay showing SQL library error until attempted load Kim Alvefur <zash@zash.se> parents: 13784 diff changeset	28 return dbisql;
5973a5d22ab5 mod_storage_sql: Delay showing SQL library error until attempted load Kim Alvefur <zash@zash.se> parents: 13784 diff changeset	29 else
5973a5d22ab5 mod_storage_sql: Delay showing SQL library error until attempted load Kim Alvefur <zash@zash.se> parents: 13784 diff changeset	30 error(dbisql);
5973a5d22ab5 mod_storage_sql: Delay showing SQL library error until attempted load Kim Alvefur <zash@zash.se> parents: 13784 diff changeset	31 end
5973a5d22ab5 mod_storage_sql: Delay showing SQL library error until attempted load Kim Alvefur <zash@zash.se> parents: 13784 diff changeset	32 end
5973a5d22ab5 mod_storage_sql: Delay showing SQL library error until attempted load Kim Alvefur <zash@zash.se> parents: 13784 diff changeset	33
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	34 local noop = function() end
12589 39ae08180c81 compat: Remove handling of Lua 5.1 location of 'unpack' function Kim Alvefur <zash@zash.se> parents: 12574 diff changeset	35 local unpack = table.unpack;
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	36 local function iterator(result)
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	37 return function(result_)
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	38 local row = result_();
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	39 if row ~= nil then
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	40 return unpack(row);
4105 08560575762f mod_storage_sql: Reconnect in all cases, and rollback on error. Waqas Hussain <waqas20@gmail.com> parents: 4101 diff changeset	41 end
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	42 end, result, nil;
4105 08560575762f mod_storage_sql: Reconnect in all cases, and rollback on error. Waqas Hussain <waqas20@gmail.com> parents: 4101 diff changeset	43 end
08560575762f mod_storage_sql: Reconnect in all cases, and rollback on error. Waqas Hussain <waqas20@gmail.com> parents: 4101 diff changeset	44
13633 6b84d11aa09b mod_storage_sql: Detect SQLite3 without UPSERT (or SQLCipher 3.x) Kim Alvefur <zash@zash.se> parents: 13611 diff changeset	45 -- COMPAT Support for UPSERT is not in all versions of all compatible databases.
6b84d11aa09b mod_storage_sql: Detect SQLite3 without UPSERT (or SQLCipher 3.x) Kim Alvefur <zash@zash.se> parents: 13611 diff changeset	46 local function has_upsert(engine)
6b84d11aa09b mod_storage_sql: Detect SQLite3 without UPSERT (or SQLCipher 3.x) Kim Alvefur <zash@zash.se> parents: 13611 diff changeset	47 if engine.params.driver == "SQLite3" then
6b84d11aa09b mod_storage_sql: Detect SQLite3 without UPSERT (or SQLCipher 3.x) Kim Alvefur <zash@zash.se> parents: 13611 diff changeset	48 -- SQLite3 >= 3.24.0
13661 5a7f042fe05d mod_storage_sql: Fix tests for SQLite3 Kim Alvefur <zash@zash.se> parents: 13634 diff changeset	49 return engine.sqlite_version and (engine.sqlite_version[2] or 0) >= 24;
13633 6b84d11aa09b mod_storage_sql: Detect SQLite3 without UPSERT (or SQLCipher 3.x) Kim Alvefur <zash@zash.se> parents: 13611 diff changeset	50 elseif engine.params.driver == "PostgreSQL" then
6b84d11aa09b mod_storage_sql: Detect SQLite3 without UPSERT (or SQLCipher 3.x) Kim Alvefur <zash@zash.se> parents: 13611 diff changeset	51 -- PostgreSQL >= 9.5
6b84d11aa09b mod_storage_sql: Detect SQLite3 without UPSERT (or SQLCipher 3.x) Kim Alvefur <zash@zash.se> parents: 13611 diff changeset	52 -- Versions without support have long since reached end of life.
6b84d11aa09b mod_storage_sql: Detect SQLite3 without UPSERT (or SQLCipher 3.x) Kim Alvefur <zash@zash.se> parents: 13611 diff changeset	53 return true;
6b84d11aa09b mod_storage_sql: Detect SQLite3 without UPSERT (or SQLCipher 3.x) Kim Alvefur <zash@zash.se> parents: 13611 diff changeset	54 end
6b84d11aa09b mod_storage_sql: Detect SQLite3 without UPSERT (or SQLCipher 3.x) Kim Alvefur <zash@zash.se> parents: 13611 diff changeset	55 -- We don't support UPSERT on MySQL/MariaDB, they seem to have a completely different syntax, uncertaint from which versions.
6b84d11aa09b mod_storage_sql: Detect SQLite3 without UPSERT (or SQLCipher 3.x) Kim Alvefur <zash@zash.se> parents: 13611 diff changeset	56 return false
6b84d11aa09b mod_storage_sql: Detect SQLite3 without UPSERT (or SQLCipher 3.x) Kim Alvefur <zash@zash.se> parents: 13611 diff changeset	57 end
6b84d11aa09b mod_storage_sql: Detect SQLite3 without UPSERT (or SQLCipher 3.x) Kim Alvefur <zash@zash.se> parents: 13611 diff changeset	58
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	59 local default_params = { driver = "SQLite3" };
5776 bd0ff8ae98a8 Remove all trailing whitespace Florian Zeitz <florob@babelmonkeys.de> parents: 5209 diff changeset	60
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	61 local engine;
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	62
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	63 local function serialize(value)
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	64 local t = type(value);
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	65 if t == "string" or t == "boolean" or t == "number" then
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	66 return t, tostring(value);
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	67 elseif is_stanza(value) then
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	68 return "xml", tostring(value);
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	69 elseif t == "table" then
8032 aa9f198cb3c9 mod_storage_sql: Rename variables to avoid name clashes [luacheck] Kim Alvefur <zash@zash.se> parents: 8031 diff changeset	70 local encoded,err = json.encode(value);
8138 cb605fb60e32 mod_storage_sql: Fix logic error introduced by variable rename in aa9f198cb3c9 (thanks waqas) Matthew Wild <mwild1@gmail.com> parents: 8082 diff changeset	71 if encoded then return "json", encoded; end
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	72 return nil, err;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	73 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	74 return nil, "Unhandled value type: "..t;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	75 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	76 local function deserialize(t, value)
3743 5adfb8d0444d mod_storage_sql: Fixed the deserialization of string-typed values. Waqas Hussain <waqas20@gmail.com> parents: 3732 diff changeset	77 if t == "string" then return value;
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	78 elseif t == "boolean" then
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	79 if value == "true" then return true;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	80 elseif value == "false" then return false; end
8537 d7f31badd359 mod_storage_sql: Catch and report value deserialization errors Kim Alvefur <zash@zash.se> parents: 8480 diff changeset	81 return nil, "invalid-boolean";
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors Kim Alvefur <zash@zash.se> parents: 8480 diff changeset	82 elseif t == "number" then
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors Kim Alvefur <zash@zash.se> parents: 8480 diff changeset	83 value = tonumber(value);
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors Kim Alvefur <zash@zash.se> parents: 8480 diff changeset	84 if value then return value; end
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors Kim Alvefur <zash@zash.se> parents: 8480 diff changeset	85 return nil, "invalid-number";
3772 e1f6fe098404 mod_storage_sql: Fix a couple of bugs in "JSON" decoding Matthew Wild <mwild1@gmail.com> parents: 3744 diff changeset	86 elseif t == "json" then
3980 6b2fac6602b3 mod_storage_sql: Use util.json instead of util.serialization. Waqas Hussain <waqas20@gmail.com> parents: 3978 diff changeset	87 return json.decode(value);
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	88 elseif t == "xml" then
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	89 return xml_parse(value);
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	90 end
8537 d7f31badd359 mod_storage_sql: Catch and report value deserialization errors Kim Alvefur <zash@zash.se> parents: 8480 diff changeset	91 return nil, "Unhandled value type: "..t;
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	92 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	93
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	94 local host = module.host;
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	95
13241 0419de4e4db1 mod_storage_sql: Pass variables as arguments instead of upvalues Kim Alvefur <zash@zash.se> parents: 13229 diff changeset	96 local function keyval_store_get(user, store)
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	97 local haveany;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	98 local result = {};
8033 6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	99 local select_sql = [[
8073 7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 8050 diff changeset	100 SELECT "key","type","value"
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 8050 diff changeset	101 FROM "prosody"
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 8050 diff changeset	102 WHERE "host"=? AND "user"=? AND "store"=?;
8033 6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	103 ]]
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	104 for row in engine:select(select_sql, host, user or "", store) do
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	105 haveany = true;
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	106 local k = row[1];
8537 d7f31badd359 mod_storage_sql: Catch and report value deserialization errors Kim Alvefur <zash@zash.se> parents: 8480 diff changeset	107 local v, e = deserialize(row[2], row[3]);
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors Kim Alvefur <zash@zash.se> parents: 8480 diff changeset	108 assert(v ~= nil, e);
3977 6724853adb80 mod_storage_sql: Remove the subkey column from the Prosody table, and make the map store compatible with the key-value store. Waqas Hussain <waqas20@gmail.com> parents: 3976 diff changeset	109 if k and v then
6724853adb80 mod_storage_sql: Remove the subkey column from the Prosody table, and make the map store compatible with the key-value store. Waqas Hussain <waqas20@gmail.com> parents: 3976 diff changeset	110 if k ~= "" then result[k] = v; elseif type(v) == "table" then
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	111 for a,b in pairs(v) do
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	112 result[a] = b;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	113 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	114 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	115 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	116 end
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	117 if haveany then
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	118 return result;
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	119 end
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	120 end
13241 0419de4e4db1 mod_storage_sql: Pass variables as arguments instead of upvalues Kim Alvefur <zash@zash.se> parents: 13229 diff changeset	121 local function keyval_store_set(data, user, store)
8033 6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	122 local delete_sql = [[
8073 7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 8050 diff changeset	123 DELETE FROM "prosody"
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 8050 diff changeset	124 WHERE "host"=? AND "user"=? AND "store"=?
8033 6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	125 ]];
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	126 engine:delete(delete_sql, host, user or "", store);
5776 bd0ff8ae98a8 Remove all trailing whitespace Florian Zeitz <florob@babelmonkeys.de> parents: 5209 diff changeset	127
8033 6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	128 local insert_sql = [[
8073 7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 8050 diff changeset	129 INSERT INTO "prosody"
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 8050 diff changeset	130 ("host","user","store","key","type","value")
8033 6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	131 VALUES (?,?,?,?,?,?);
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	132 ]]
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	133 if data and next(data) ~= nil then
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	134 local extradata = {};
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	135 for key, value in pairs(data) do
3974 af40a7ce4f77 mod_storage_sql: Quote identifiers in SQL with backquotes, and use the empty string for NULL, and '=' instead of 'IS' for comparison, to work with MySQL's limitations... Waqas Hussain <waqas20@gmail.com> parents: 3772 diff changeset	136 if type(key) == "string" and key ~= "" then
8032 aa9f198cb3c9 mod_storage_sql: Rename variables to avoid name clashes [luacheck] Kim Alvefur <zash@zash.se> parents: 8031 diff changeset	137 local t, encoded_value = assert(serialize(value));
8050 c0f81dea4662 mod_storage_sql: Fix to use correct SQL query (thanks carlos) Kim Alvefur <zash@zash.se> parents: 8038 diff changeset	138 engine:insert(insert_sql, host, user or "", store, key, t, encoded_value);
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	139 else
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	140 extradata[key] = value;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	141 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	142 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	143 if next(extradata) ~= nil then
8032 aa9f198cb3c9 mod_storage_sql: Rename variables to avoid name clashes [luacheck] Kim Alvefur <zash@zash.se> parents: 8031 diff changeset	144 local t, encoded_extradata = assert(serialize(extradata));
8033 6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	145 engine:insert(insert_sql, host, user or "", store, "", t, encoded_extradata);
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	146 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	147 end
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	148 return true;
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	149 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	150
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	151 --- Key/value store API (default store type)
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	152
4007 062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback. Waqas Hussain <waqas20@gmail.com> parents: 4004 diff changeset	153 local keyval_store = {};
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback. Waqas Hussain <waqas20@gmail.com> parents: 4004 diff changeset	154 keyval_store.__index = keyval_store;
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback. Waqas Hussain <waqas20@gmail.com> parents: 4004 diff changeset	155 function keyval_store:get(username)
13241 0419de4e4db1 mod_storage_sql: Pass variables as arguments instead of upvalues Kim Alvefur <zash@zash.se> parents: 13229 diff changeset	156 local ok, result = engine:transaction(keyval_store_get, username, self.store);
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	157 if not ok then
13241 0419de4e4db1 mod_storage_sql: Pass variables as arguments instead of upvalues Kim Alvefur <zash@zash.se> parents: 13229 diff changeset	158 module:log("error", "Unable to read from database %s store for %s: %s", self.store, username or "<host>", result);
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	159 return nil, result;
4105 08560575762f mod_storage_sql: Reconnect in all cases, and rollback on error. Waqas Hussain <waqas20@gmail.com> parents: 4101 diff changeset	160 end
6953 b9276d677e76 mod_storage_sql: Whitespace fix (backported from trunk) Matthew Wild <mwild1@gmail.com> parents: 6951 diff changeset	161 return result;
4007 062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback. Waqas Hussain <waqas20@gmail.com> parents: 4004 diff changeset	162 end
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback. Waqas Hussain <waqas20@gmail.com> parents: 4004 diff changeset	163 function keyval_store:set(username, data)
13241 0419de4e4db1 mod_storage_sql: Pass variables as arguments instead of upvalues Kim Alvefur <zash@zash.se> parents: 13229 diff changeset	164 return engine:transaction(keyval_store_set, data, username, self.store);
4007 062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback. Waqas Hussain <waqas20@gmail.com> parents: 4004 diff changeset	165 end
5154 429c8eeea035 mod_storage_sql: Add support for iterating over users with data in a store Kim Alvefur <zash@zash.se> parents: 5133 diff changeset	166 function keyval_store:users()
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	167 local ok, result = engine:transaction(function()
8033 6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	168 local select_sql = [[
8073 7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 8050 diff changeset	169 SELECT DISTINCT "user"
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 8050 diff changeset	170 FROM "prosody"
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 8050 diff changeset	171 WHERE "host"=? AND "store"=?;
8033 6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	172 ]];
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	173 return engine:select(select_sql, host, self.store);
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	174 end);
9492 c03c60a2dede mod_storage_sql: Have :users() throw an error on failure instead of returning non-iterator values Matthew Wild <mwild1@gmail.com> parents: 9479 diff changeset	175 if not ok then error(result); end
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	176 return iterator(result);
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	177 end
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	178
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	179 --- Archive store API
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	180
13213 50324f66ca2a plugins: Use integer config API with interval specification where sensible Kim Alvefur <zash@zash.se> parents: 13190 diff changeset	181 local archive_item_limit = module:get_option_integer("storage_archive_item_limit", nil, 0);
13223 b88b3e414550 mod_storage_sql: Use integer config option for cache size Kim Alvefur <zash@zash.se> parents: 13222 diff changeset	182 local archive_item_count_cache = cache.new(module:get_option_integer("storage_archive_item_limit_cache_size", 1000, 1));
9883 f76bd399267c mod_storage_internal,_sql: Add limit to number of items in an archive store (fixes #733) Matthew Wild <mwild1@gmail.com> parents: 9691 diff changeset	183
10993 b5e7f4d533e2 mod_storage_sql: Measure hits/misses on the item count cache Kim Alvefur <zash@zash.se> parents: 10925 diff changeset	184 local item_count_cache_hit = module:measure("item_count_cache_hit", "rate");
b5e7f4d533e2 mod_storage_sql: Measure hits/misses on the item count cache Kim Alvefur <zash@zash.se> parents: 10925 diff changeset	185 local item_count_cache_miss = module:measure("item_count_cache_miss", "rate")
b5e7f4d533e2 mod_storage_sql: Measure hits/misses on the item count cache Kim Alvefur <zash@zash.se> parents: 10925 diff changeset	186
10033 ca8333d1a7fe mod_storage_sql: Ignore shadowed error variable [luacheck] Kim Alvefur <zash@zash.se> parents: 10032 diff changeset	187 -- luacheck: ignore 512 431/user 431/store 431/err
6954 400badaf1fc7 mod_storage_sql: Add map store (backported from trunk) Matthew Wild <mwild1@gmail.com> parents: 6953 diff changeset	188 local map_store = {};
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk) Matthew Wild <mwild1@gmail.com> parents: 6953 diff changeset	189 map_store.__index = map_store;
7153 89fa66d4e502 mod_storage_sql: Implement map:set_keys, allowing multiple keys to be set in the same transaction Kim Alvefur <zash@zash.se> parents: 7149 diff changeset	190 map_store.remove = {};
6954 400badaf1fc7 mod_storage_sql: Add map store (backported from trunk) Matthew Wild <mwild1@gmail.com> parents: 6953 diff changeset	191 function map_store:get(username, key)
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk) Matthew Wild <mwild1@gmail.com> parents: 6953 diff changeset	192 local ok, result = engine:transaction(function()
8033 6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	193 local query = [[
8073 7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 8050 diff changeset	194 SELECT "type", "value"
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 8050 diff changeset	195 FROM "prosody"
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 8050 diff changeset	196 WHERE "host"=? AND "user"=? AND "store"=? AND "key"=?
8033 6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	197 LIMIT 1
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	198 ]];
8537 d7f31badd359 mod_storage_sql: Catch and report value deserialization errors Kim Alvefur <zash@zash.se> parents: 8480 diff changeset	199 local data, err;
6954 400badaf1fc7 mod_storage_sql: Add map store (backported from trunk) Matthew Wild <mwild1@gmail.com> parents: 6953 diff changeset	200 if type(key) == "string" and key ~= "" then
8033 6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	201 for row in engine:select(query, host, username or "", self.store, key) do
8537 d7f31badd359 mod_storage_sql: Catch and report value deserialization errors Kim Alvefur <zash@zash.se> parents: 8480 diff changeset	202 data, err = deserialize(row[1], row[2]);
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors Kim Alvefur <zash@zash.se> parents: 8480 diff changeset	203 assert(data ~= nil, err);
6954 400badaf1fc7 mod_storage_sql: Add map store (backported from trunk) Matthew Wild <mwild1@gmail.com> parents: 6953 diff changeset	204 end
7274 e0727512bb99 mod_storage_sql: Allow loops over results to end on their own Kim Alvefur <zash@zash.se> parents: 7273 diff changeset	205 return data;
6954 400badaf1fc7 mod_storage_sql: Add map store (backported from trunk) Matthew Wild <mwild1@gmail.com> parents: 6953 diff changeset	206 else
8033 6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	207 for row in engine:select(query, host, username or "", self.store, "") do
8537 d7f31badd359 mod_storage_sql: Catch and report value deserialization errors Kim Alvefur <zash@zash.se> parents: 8480 diff changeset	208 data, err = deserialize(row[1], row[2]);
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors Kim Alvefur <zash@zash.se> parents: 8480 diff changeset	209 assert(data ~= nil, err);
7149 bb0fd02ae70f mod_storage_sql: Support non-string keys in map store Kim Alvefur <zash@zash.se> parents: 7008 diff changeset	210 end
7274 e0727512bb99 mod_storage_sql: Allow loops over results to end on their own Kim Alvefur <zash@zash.se> parents: 7273 diff changeset	211 return data and data[key] or nil;
6954 400badaf1fc7 mod_storage_sql: Add map store (backported from trunk) Matthew Wild <mwild1@gmail.com> parents: 6953 diff changeset	212 end
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk) Matthew Wild <mwild1@gmail.com> parents: 6953 diff changeset	213 end);
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk) Matthew Wild <mwild1@gmail.com> parents: 6953 diff changeset	214 if not ok then return nil, result; end
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk) Matthew Wild <mwild1@gmail.com> parents: 6953 diff changeset	215 return result;
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk) Matthew Wild <mwild1@gmail.com> parents: 6953 diff changeset	216 end
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk) Matthew Wild <mwild1@gmail.com> parents: 6953 diff changeset	217 function map_store:set(username, key, data)
7169 d00d8cfcc9a8 mod_storage_sql: Fix setting value to false in map store Kim Alvefur <zash@zash.se> parents: 7156 diff changeset	218 if data == nil then data = self.remove; end
d00d8cfcc9a8 mod_storage_sql: Fix setting value to false in map store Kim Alvefur <zash@zash.se> parents: 7156 diff changeset	219 return self:set_keys(username, { [key] = data });
7153 89fa66d4e502 mod_storage_sql: Implement map:set_keys, allowing multiple keys to be set in the same transaction Kim Alvefur <zash@zash.se> parents: 7149 diff changeset	220 end
89fa66d4e502 mod_storage_sql: Implement map:set_keys, allowing multiple keys to be set in the same transaction Kim Alvefur <zash@zash.se> parents: 7149 diff changeset	221 function map_store:set_keys(username, keydatas)
6954 400badaf1fc7 mod_storage_sql: Add map store (backported from trunk) Matthew Wild <mwild1@gmail.com> parents: 6953 diff changeset	222 local ok, result = engine:transaction(function()
8033 6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	223 local delete_sql = [[
8073 7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 8050 diff changeset	224 DELETE FROM "prosody"
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 8050 diff changeset	225 WHERE "host"=? AND "user"=? AND "store"=? AND "key"=?;
8033 6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	226 ]];
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	227 local insert_sql = [[
8073 7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 8050 diff changeset	228 INSERT INTO "prosody"
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 8050 diff changeset	229 ("host","user","store","key","type","value")
8033 6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	230 VALUES (?,?,?,?,?,?);
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	231 ]];
13143 3ec48555b773 mod_storage_sql: Add UPSERT support Kim Alvefur <zash@zash.se> parents: 12977 diff changeset	232 local upsert_sql = [[
3ec48555b773 mod_storage_sql: Add UPSERT support Kim Alvefur <zash@zash.se> parents: 12977 diff changeset	233 INSERT INTO "prosody"
3ec48555b773 mod_storage_sql: Add UPSERT support Kim Alvefur <zash@zash.se> parents: 12977 diff changeset	234 ("host","user","store","key","type","value")
3ec48555b773 mod_storage_sql: Add UPSERT support Kim Alvefur <zash@zash.se> parents: 12977 diff changeset	235 VALUES (?,?,?,?,?,?)
3ec48555b773 mod_storage_sql: Add UPSERT support Kim Alvefur <zash@zash.se> parents: 12977 diff changeset	236 ON CONFLICT ("host", "user","store", "key")
3ec48555b773 mod_storage_sql: Add UPSERT support Kim Alvefur <zash@zash.se> parents: 12977 diff changeset	237 DO UPDATE SET "type"=?, "value"=?;
3ec48555b773 mod_storage_sql: Add UPSERT support Kim Alvefur <zash@zash.se> parents: 12977 diff changeset	238 ]];
8033 6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	239 local select_extradata_sql = [[
8073 7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 8050 diff changeset	240 SELECT "type", "value"
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 8050 diff changeset	241 FROM "prosody"
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 8050 diff changeset	242 WHERE "host"=? AND "user"=? AND "store"=? AND "key"=?
8033 6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	243 LIMIT 1;
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	244 ]];
7153 89fa66d4e502 mod_storage_sql: Implement map:set_keys, allowing multiple keys to be set in the same transaction Kim Alvefur <zash@zash.se> parents: 7149 diff changeset	245 for key, data in pairs(keydatas) do
13633 6b84d11aa09b mod_storage_sql: Detect SQLite3 without UPSERT (or SQLCipher 3.x) Kim Alvefur <zash@zash.se> parents: 13611 diff changeset	246 if type(key) == "string" and key ~= "" and has_upsert(engine) and data ~= self.remove then
13143 3ec48555b773 mod_storage_sql: Add UPSERT support Kim Alvefur <zash@zash.se> parents: 12977 diff changeset	247 local t, value = assert(serialize(data));
3ec48555b773 mod_storage_sql: Add UPSERT support Kim Alvefur <zash@zash.se> parents: 12977 diff changeset	248 engine:insert(upsert_sql, host, username or "", self.store, key, t, value, t, value);
3ec48555b773 mod_storage_sql: Add UPSERT support Kim Alvefur <zash@zash.se> parents: 12977 diff changeset	249 elseif type(key) == "string" and key ~= "" then
8033 6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	250 engine:delete(delete_sql,
7153 89fa66d4e502 mod_storage_sql: Implement map:set_keys, allowing multiple keys to be set in the same transaction Kim Alvefur <zash@zash.se> parents: 7149 diff changeset	251 host, username or "", self.store, key);
89fa66d4e502 mod_storage_sql: Implement map:set_keys, allowing multiple keys to be set in the same transaction Kim Alvefur <zash@zash.se> parents: 7149 diff changeset	252 if data ~= self.remove then
89fa66d4e502 mod_storage_sql: Implement map:set_keys, allowing multiple keys to be set in the same transaction Kim Alvefur <zash@zash.se> parents: 7149 diff changeset	253 local t, value = assert(serialize(data));
8033 6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	254 engine:insert(insert_sql, host, username or "", self.store, key, t, value);
7153 89fa66d4e502 mod_storage_sql: Implement map:set_keys, allowing multiple keys to be set in the same transaction Kim Alvefur <zash@zash.se> parents: 7149 diff changeset	255 end
89fa66d4e502 mod_storage_sql: Implement map:set_keys, allowing multiple keys to be set in the same transaction Kim Alvefur <zash@zash.se> parents: 7149 diff changeset	256 else
8537 d7f31badd359 mod_storage_sql: Catch and report value deserialization errors Kim Alvefur <zash@zash.se> parents: 8480 diff changeset	257 local extradata, err = {};
8033 6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	258 for row in engine:select(select_extradata_sql, host, username or "", self.store, "") do
8537 d7f31badd359 mod_storage_sql: Catch and report value deserialization errors Kim Alvefur <zash@zash.se> parents: 8480 diff changeset	259 extradata, err = deserialize(row[1], row[2]);
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors Kim Alvefur <zash@zash.se> parents: 8480 diff changeset	260 assert(extradata ~= nil, err);
7153 89fa66d4e502 mod_storage_sql: Implement map:set_keys, allowing multiple keys to be set in the same transaction Kim Alvefur <zash@zash.se> parents: 7149 diff changeset	261 end
8033 6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	262 engine:delete(delete_sql, host, username or "", self.store, "");
7153 89fa66d4e502 mod_storage_sql: Implement map:set_keys, allowing multiple keys to be set in the same transaction Kim Alvefur <zash@zash.se> parents: 7149 diff changeset	263 extradata[key] = data;
89fa66d4e502 mod_storage_sql: Implement map:set_keys, allowing multiple keys to be set in the same transaction Kim Alvefur <zash@zash.se> parents: 7149 diff changeset	264 local t, value = assert(serialize(extradata));
8033 6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	265 engine:insert(insert_sql, host, username or "", self.store, "", t, value);
6954 400badaf1fc7 mod_storage_sql: Add map store (backported from trunk) Matthew Wild <mwild1@gmail.com> parents: 6953 diff changeset	266 end
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk) Matthew Wild <mwild1@gmail.com> parents: 6953 diff changeset	267 end
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk) Matthew Wild <mwild1@gmail.com> parents: 6953 diff changeset	268 return true;
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk) Matthew Wild <mwild1@gmail.com> parents: 6953 diff changeset	269 end);
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk) Matthew Wild <mwild1@gmail.com> parents: 6953 diff changeset	270 if not ok then return nil, result; end
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk) Matthew Wild <mwild1@gmail.com> parents: 6953 diff changeset	271 return result;
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk) Matthew Wild <mwild1@gmail.com> parents: 6953 diff changeset	272 end
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk) Matthew Wild <mwild1@gmail.com> parents: 6953 diff changeset	273
10680 19692fc5c106 storagemanager, mod_storage_sql: Rename methods to :get_all() and :delete_all() Matthew Wild <mwild1@gmail.com> parents: 10677 diff changeset	274 function map_store:get_all(key)
10677 0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests) Matthew Wild <mwild1@gmail.com> parents: 10656 diff changeset	275 if type(key) ~= "string" or key == "" then
10680 19692fc5c106 storagemanager, mod_storage_sql: Rename methods to :get_all() and :delete_all() Matthew Wild <mwild1@gmail.com> parents: 10677 diff changeset	276 return nil, "get_all only supports non-empty string keys";
10677 0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests) Matthew Wild <mwild1@gmail.com> parents: 10656 diff changeset	277 end
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests) Matthew Wild <mwild1@gmail.com> parents: 10656 diff changeset	278 local ok, result = engine:transaction(function()
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests) Matthew Wild <mwild1@gmail.com> parents: 10656 diff changeset	279 local query = [[
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests) Matthew Wild <mwild1@gmail.com> parents: 10656 diff changeset	280 SELECT "user", "type", "value"
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests) Matthew Wild <mwild1@gmail.com> parents: 10656 diff changeset	281 FROM "prosody"
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests) Matthew Wild <mwild1@gmail.com> parents: 10656 diff changeset	282 WHERE "host"=? AND "store"=? AND "key"=?
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests) Matthew Wild <mwild1@gmail.com> parents: 10656 diff changeset	283 ]];
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests) Matthew Wild <mwild1@gmail.com> parents: 10656 diff changeset	284
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests) Matthew Wild <mwild1@gmail.com> parents: 10656 diff changeset	285 local data;
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests) Matthew Wild <mwild1@gmail.com> parents: 10656 diff changeset	286 for row in engine:select(query, host, self.store, key) do
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests) Matthew Wild <mwild1@gmail.com> parents: 10656 diff changeset	287 local key_data, err = deserialize(row[2], row[3]);
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests) Matthew Wild <mwild1@gmail.com> parents: 10656 diff changeset	288 assert(key_data ~= nil, err);
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests) Matthew Wild <mwild1@gmail.com> parents: 10656 diff changeset	289 if data == nil then
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests) Matthew Wild <mwild1@gmail.com> parents: 10656 diff changeset	290 data = {};
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests) Matthew Wild <mwild1@gmail.com> parents: 10656 diff changeset	291 end
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests) Matthew Wild <mwild1@gmail.com> parents: 10656 diff changeset	292 data[row[1]] = key_data;
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests) Matthew Wild <mwild1@gmail.com> parents: 10656 diff changeset	293 end
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests) Matthew Wild <mwild1@gmail.com> parents: 10656 diff changeset	294
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests) Matthew Wild <mwild1@gmail.com> parents: 10656 diff changeset	295 return data;
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests) Matthew Wild <mwild1@gmail.com> parents: 10656 diff changeset	296
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests) Matthew Wild <mwild1@gmail.com> parents: 10656 diff changeset	297 end);
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests) Matthew Wild <mwild1@gmail.com> parents: 10656 diff changeset	298 if not ok then return nil, result; end
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests) Matthew Wild <mwild1@gmail.com> parents: 10656 diff changeset	299 return result;
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests) Matthew Wild <mwild1@gmail.com> parents: 10656 diff changeset	300 end
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests) Matthew Wild <mwild1@gmail.com> parents: 10656 diff changeset	301
10680 19692fc5c106 storagemanager, mod_storage_sql: Rename methods to :get_all() and :delete_all() Matthew Wild <mwild1@gmail.com> parents: 10677 diff changeset	302 function map_store:delete_all(key)
10677 0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests) Matthew Wild <mwild1@gmail.com> parents: 10656 diff changeset	303 if type(key) ~= "string" or key == "" then
10680 19692fc5c106 storagemanager, mod_storage_sql: Rename methods to :get_all() and :delete_all() Matthew Wild <mwild1@gmail.com> parents: 10677 diff changeset	304 return nil, "delete_all only supports non-empty string keys";
10677 0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests) Matthew Wild <mwild1@gmail.com> parents: 10656 diff changeset	305 end
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests) Matthew Wild <mwild1@gmail.com> parents: 10656 diff changeset	306 local ok, result = engine:transaction(function()
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests) Matthew Wild <mwild1@gmail.com> parents: 10656 diff changeset	307 local delete_sql = [[
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests) Matthew Wild <mwild1@gmail.com> parents: 10656 diff changeset	308 DELETE FROM "prosody"
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests) Matthew Wild <mwild1@gmail.com> parents: 10656 diff changeset	309 WHERE "host"=? AND "store"=? AND "key"=?;
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests) Matthew Wild <mwild1@gmail.com> parents: 10656 diff changeset	310 ]];
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests) Matthew Wild <mwild1@gmail.com> parents: 10656 diff changeset	311 engine:delete(delete_sql, host, self.store, key);
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests) Matthew Wild <mwild1@gmail.com> parents: 10656 diff changeset	312 return true;
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests) Matthew Wild <mwild1@gmail.com> parents: 10656 diff changeset	313 end);
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests) Matthew Wild <mwild1@gmail.com> parents: 10656 diff changeset	314 if not ok then return nil, result; end
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests) Matthew Wild <mwild1@gmail.com> parents: 10656 diff changeset	315 return result;
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests) Matthew Wild <mwild1@gmail.com> parents: 10656 diff changeset	316 end
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests) Matthew Wild <mwild1@gmail.com> parents: 10656 diff changeset	317
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	318 local archive_store = {}
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	319 archive_store.caps = {
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	320 total = true;
9884 9751c17f5281 mod_storage_internal,_sql: Expose archive capabilities feature set Kim Alvefur <zash@zash.se> parents: 9883 diff changeset	321 quota = archive_item_limit;
9751c17f5281 mod_storage_internal,_sql: Expose archive capabilities feature set Kim Alvefur <zash@zash.se> parents: 9883 diff changeset	322 truncate = true;
11271 41a962b72a6e mod_storage_sql: Advertise support for id range query Kim Alvefur <zash@zash.se> parents: 10993 diff changeset	323 full_id_range = true;
11279 f225070e2b8a mod_storage_sql: Support query for set of IDs Kim Alvefur <zash@zash.se> parents: 11271 diff changeset	324 ids = true;
11967 0f2b5182e80b mod_storage_sql: Expose multi-user deletion capability Kim Alvefur <zash@zash.se> parents: 11625 diff changeset	325 wildcard_delete = true;
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	326 };
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	327 archive_store.__index = archive_store
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	328 function archive_store:append(username, key, value, when, with)
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	329 local user,store = username,self.store;
9890 b5ac12f97f34 mod_storage_sql: Fix to use currently queried store Kim Alvefur <zash@zash.se> parents: 9889 diff changeset	330 local cache_key = jid_join(username, host, store);
9885 64e16d1e91f6 mod_storage_internal,_sql: Key item count cache on both username and store Kim Alvefur <zash@zash.se> parents: 9884 diff changeset	331 local item_count = archive_item_count_cache:get(cache_key);
13150 9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set Kim Alvefur <zash@zash.se> parents: 13149 diff changeset	332
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set Kim Alvefur <zash@zash.se> parents: 13149 diff changeset	333 if archive_item_limit then
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set Kim Alvefur <zash@zash.se> parents: 13149 diff changeset	334 if not item_count then
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set Kim Alvefur <zash@zash.se> parents: 13149 diff changeset	335 item_count_cache_miss();
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set Kim Alvefur <zash@zash.se> parents: 13149 diff changeset	336 local ok, ret = engine:transaction(function()
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set Kim Alvefur <zash@zash.se> parents: 13149 diff changeset	337 local count_sql = [[
13158 cf2a907ac130 mod_storage_sql: Adjust indentation to align with surrounding code Kim Alvefur <zash@zash.se> parents: 13157 diff changeset	338 SELECT COUNT(*) FROM "prosodyarchive"
cf2a907ac130 mod_storage_sql: Adjust indentation to align with surrounding code Kim Alvefur <zash@zash.se> parents: 13157 diff changeset	339 WHERE "host"=? AND "user"=? AND "store"=?;
cf2a907ac130 mod_storage_sql: Adjust indentation to align with surrounding code Kim Alvefur <zash@zash.se> parents: 13157 diff changeset	340 ]];
13150 9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set Kim Alvefur <zash@zash.se> parents: 13149 diff changeset	341 local result = engine:select(count_sql, host, user, store);
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set Kim Alvefur <zash@zash.se> parents: 13149 diff changeset	342 if result then
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set Kim Alvefur <zash@zash.se> parents: 13149 diff changeset	343 for row in result do
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set Kim Alvefur <zash@zash.se> parents: 13149 diff changeset	344 item_count = row[1];
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set Kim Alvefur <zash@zash.se> parents: 13149 diff changeset	345 end
9883 f76bd399267c mod_storage_internal,_sql: Add limit to number of items in an archive store (fixes #733) Matthew Wild <mwild1@gmail.com> parents: 9691 diff changeset	346 end
13150 9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set Kim Alvefur <zash@zash.se> parents: 13149 diff changeset	347 end);
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set Kim Alvefur <zash@zash.se> parents: 13149 diff changeset	348 if not ok or not item_count then
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set Kim Alvefur <zash@zash.se> parents: 13149 diff changeset	349 module:log("error", "Failed while checking quota for %s: %s", username, ret);
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set Kim Alvefur <zash@zash.se> parents: 13149 diff changeset	350 return nil, "Failure while checking quota";
9883 f76bd399267c mod_storage_internal,_sql: Add limit to number of items in an archive store (fixes #733) Matthew Wild <mwild1@gmail.com> parents: 9691 diff changeset	351 end
13150 9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set Kim Alvefur <zash@zash.se> parents: 13149 diff changeset	352 archive_item_count_cache:set(cache_key, item_count);
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set Kim Alvefur <zash@zash.se> parents: 13149 diff changeset	353 else
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set Kim Alvefur <zash@zash.se> parents: 13149 diff changeset	354 item_count_cache_hit();
9883 f76bd399267c mod_storage_internal,_sql: Add limit to number of items in an archive store (fixes #733) Matthew Wild <mwild1@gmail.com> parents: 9691 diff changeset	355 end
f76bd399267c mod_storage_internal,_sql: Add limit to number of items in an archive store (fixes #733) Matthew Wild <mwild1@gmail.com> parents: 9691 diff changeset	356
9896 dafe1b544822 mod_storage_sql: No archive item limit by default Kim Alvefur <zash@zash.se> parents: 9893 diff changeset	357 module:log("debug", "%s has %d items out of %d limit", username, item_count, archive_item_limit);
dafe1b544822 mod_storage_sql: No archive item limit by default Kim Alvefur <zash@zash.se> parents: 9893 diff changeset	358 if item_count >= archive_item_limit then
dafe1b544822 mod_storage_sql: No archive item limit by default Kim Alvefur <zash@zash.se> parents: 9893 diff changeset	359 return nil, "quota-limit";
dafe1b544822 mod_storage_sql: No archive item limit by default Kim Alvefur <zash@zash.se> parents: 9893 diff changeset	360 end
9883 f76bd399267c mod_storage_internal,_sql: Add limit to number of items in an archive store (fixes #733) Matthew Wild <mwild1@gmail.com> parents: 9691 diff changeset	361 end
f76bd399267c mod_storage_internal,_sql: Add limit to number of items in an archive store (fixes #733) Matthew Wild <mwild1@gmail.com> parents: 9691 diff changeset	362
12631 9524bb7f3944 mod_storage_sql: Drop archive timestamp precision pending schema update Kim Alvefur <zash@zash.se> parents: 12600 diff changeset	363 -- FIXME update the schema to allow precision timestamps
13221 2a9ce229efac mod_storage_sql: Allow higher precision timestamps in SQLite3 Kim Alvefur <zash@zash.se> parents: 13213 diff changeset	364 when = when or os.time();
2a9ce229efac mod_storage_sql: Allow higher precision timestamps in SQLite3 Kim Alvefur <zash@zash.se> parents: 13213 diff changeset	365 if engine.params.driver ~= "SQLite3" then
2a9ce229efac mod_storage_sql: Allow higher precision timestamps in SQLite3 Kim Alvefur <zash@zash.se> parents: 13213 diff changeset	366 -- SQLite3 doesn't enforce types :)
2a9ce229efac mod_storage_sql: Allow higher precision timestamps in SQLite3 Kim Alvefur <zash@zash.se> parents: 13213 diff changeset	367 when = math.floor(when);
2a9ce229efac mod_storage_sql: Allow higher precision timestamps in SQLite3 Kim Alvefur <zash@zash.se> parents: 13213 diff changeset	368 end
8031 ef838b7f8f53 mod_storage_sql: Sensible defaults for 'when' and 'with' arguments Kim Alvefur <zash@zash.se> parents: 8029 diff changeset	369 with = with or "";
8032 aa9f198cb3c9 mod_storage_sql: Rename variables to avoid name clashes [luacheck] Kim Alvefur <zash@zash.se> parents: 8031 diff changeset	370 local ok, ret = engine:transaction(function()
8033 6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	371 local delete_sql = [[
8073 7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 8050 diff changeset	372 DELETE FROM "prosodyarchive"
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 8050 diff changeset	373 WHERE "host"=? AND "user"=? AND "store"=? AND "key"=?;
8033 6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	374 ]];
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	375 local insert_sql = [[
8073 7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 8050 diff changeset	376 INSERT INTO "prosodyarchive"
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 8050 diff changeset	377 ("host", "user", "store", "when", "with", "key", "type", "value")
8033 6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	378 VALUES (?,?,?,?,?,?,?,?);
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	379 ]];
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	380 if key then
13151 7ebb3d6afcd1 mod_storage_sql: Add some TODO comments for future UPSERT work Kim Alvefur <zash@zash.se> parents: 13150 diff changeset	381 -- TODO use UPSERT like map store
10564 3098eac31139 mod_storage_sql: Remove unused and not actually returned return value [luacheck] Kim Alvefur <zash@zash.se> parents: 10222 diff changeset	382 local result = engine:delete(delete_sql, host, user or "", store, key);
13150 9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set Kim Alvefur <zash@zash.se> parents: 13149 diff changeset	383 if result and item_count then
9883 f76bd399267c mod_storage_internal,_sql: Add limit to number of items in an archive store (fixes #733) Matthew Wild <mwild1@gmail.com> parents: 9691 diff changeset	384 item_count = item_count - result:affected();
f76bd399267c mod_storage_internal,_sql: Add limit to number of items in an archive store (fixes #733) Matthew Wild <mwild1@gmail.com> parents: 9691 diff changeset	385 end
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	386 else
13318 4a437dd62cc9 mod_storage_sql: Use UUIDv7 as keys Kim Alvefur <zash@zash.se> parents: 13242 diff changeset	387 key = uuid.v7();
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	388 end
8032 aa9f198cb3c9 mod_storage_sql: Rename variables to avoid name clashes [luacheck] Kim Alvefur <zash@zash.se> parents: 8031 diff changeset	389 local t, encoded_value = assert(serialize(value));
aa9f198cb3c9 mod_storage_sql: Rename variables to avoid name clashes [luacheck] Kim Alvefur <zash@zash.se> parents: 8031 diff changeset	390 engine:insert(insert_sql, host, user or "", store, when, with, key, t, encoded_value);
13150 9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set Kim Alvefur <zash@zash.se> parents: 13149 diff changeset	391 if item_count then
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set Kim Alvefur <zash@zash.se> parents: 13149 diff changeset	392 archive_item_count_cache:set(cache_key, item_count+1);
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set Kim Alvefur <zash@zash.se> parents: 13149 diff changeset	393 end
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	394 return key;
5154 429c8eeea035 mod_storage_sql: Add support for iterating over users with data in a store Kim Alvefur <zash@zash.se> parents: 5133 diff changeset	395 end);
8032 aa9f198cb3c9 mod_storage_sql: Rename variables to avoid name clashes [luacheck] Kim Alvefur <zash@zash.se> parents: 8031 diff changeset	396 if not ok then return ok, ret; end
aa9f198cb3c9 mod_storage_sql: Rename variables to avoid name clashes [luacheck] Kim Alvefur <zash@zash.se> parents: 8031 diff changeset	397 return ret; -- the key
5154 429c8eeea035 mod_storage_sql: Add support for iterating over users with data in a store Kim Alvefur <zash@zash.se> parents: 5133 diff changeset	398 end
4007 062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback. Waqas Hussain <waqas20@gmail.com> parents: 4004 diff changeset	399
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	400 -- Helpers for building the WHERE clause
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	401 local function archive_where(query, args, where)
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	402 -- Time range, inclusive
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	403 if query.start then
12723 4cfd09343947 mod_storage_sql: Strip timestamp precision in queries to fix error (thanks muppeth) Kim Alvefur <zash@zash.se> parents: 12631 diff changeset	404 args[#args+1] = math.floor(query.start);
8073 7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 8050 diff changeset	405 where[#where+1] = "\"when\" >= ?"
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	406 end
5776 bd0ff8ae98a8 Remove all trailing whitespace Florian Zeitz <florob@babelmonkeys.de> parents: 5209 diff changeset	407
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	408 if query["end"] then
12723 4cfd09343947 mod_storage_sql: Strip timestamp precision in queries to fix error (thanks muppeth) Kim Alvefur <zash@zash.se> parents: 12631 diff changeset	409 args[#args+1] = math.floor(query["end"]);
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	410 if query.start then
8073 7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 8050 diff changeset	411 where[#where] = "\"when\" BETWEEN ? AND ?" -- is this inclusive?
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	412 else
8073 7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 8050 diff changeset	413 where[#where+1] = "\"when\" <= ?"
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	414 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	415 end
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	416
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	417 -- Related name
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	418 if query.with then
8073 7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 8050 diff changeset	419 where[#where+1] = "\"with\" = ?";
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	420 args[#args+1] = query.with
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	421 end
5776 bd0ff8ae98a8 Remove all trailing whitespace Florian Zeitz <florob@babelmonkeys.de> parents: 5209 diff changeset	422
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	423 -- Unique id
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	424 if query.key then
8073 7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 8050 diff changeset	425 where[#where+1] = "\"key\" = ?";
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	426 args[#args+1] = query.key
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	427 end
11279 f225070e2b8a mod_storage_sql: Support query for set of IDs Kim Alvefur <zash@zash.se> parents: 11271 diff changeset	428
f225070e2b8a mod_storage_sql: Support query for set of IDs Kim Alvefur <zash@zash.se> parents: 11271 diff changeset	429 -- Set of ids
f225070e2b8a mod_storage_sql: Support query for set of IDs Kim Alvefur <zash@zash.se> parents: 11271 diff changeset	430 if query.ids then
f225070e2b8a mod_storage_sql: Support query for set of IDs Kim Alvefur <zash@zash.se> parents: 11271 diff changeset	431 local nids, nargs = #query.ids, #args;
12574 18d33668c5fa mod_storage_sql: Remove Lua 5.1 compatibility hack Kim Alvefur <zash@zash.se> parents: 11971 diff changeset	432 where[#where + 1] = "\"key\" IN (" .. string.rep("?", nids, ",") .. ")";
11279 f225070e2b8a mod_storage_sql: Support query for set of IDs Kim Alvefur <zash@zash.se> parents: 11271 diff changeset	433 for i, id in ipairs(query.ids) do
f225070e2b8a mod_storage_sql: Support query for set of IDs Kim Alvefur <zash@zash.se> parents: 11271 diff changeset	434 args[nargs+i] = id;
f225070e2b8a mod_storage_sql: Support query for set of IDs Kim Alvefur <zash@zash.se> parents: 11271 diff changeset	435 end
f225070e2b8a mod_storage_sql: Support query for set of IDs Kim Alvefur <zash@zash.se> parents: 11271 diff changeset	436 end
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	437 end
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	438 local function archive_where_id_range(query, args, where)
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	439 -- Before or after specific item, exclusive
10020 deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325) Kim Alvefur <zash@zash.se> parents: 10017 diff changeset	440 local id_lookup_sql = [[
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325) Kim Alvefur <zash@zash.se> parents: 10017 diff changeset	441 SELECT "sort_id"
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325) Kim Alvefur <zash@zash.se> parents: 10017 diff changeset	442 FROM "prosodyarchive"
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325) Kim Alvefur <zash@zash.se> parents: 10017 diff changeset	443 WHERE "key" = ? AND "host" = ? AND "user" = ? AND "store" = ?
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325) Kim Alvefur <zash@zash.se> parents: 10017 diff changeset	444 LIMIT 1;
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325) Kim Alvefur <zash@zash.se> parents: 10017 diff changeset	445 ]];
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	446 if query.after then -- keys better be unique!
10020 deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325) Kim Alvefur <zash@zash.se> parents: 10017 diff changeset	447 local after_id = nil;
10032 de5ab807e438 mod_storage_sql: Fix to use correct arguments to archive id lookup Kim Alvefur <zash@zash.se> parents: 10031 diff changeset	448 for row in engine:select(id_lookup_sql, query.after, args[1], args[2], args[3]) do
10020 deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325) Kim Alvefur <zash@zash.se> parents: 10017 diff changeset	449 after_id = row[1];
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325) Kim Alvefur <zash@zash.se> parents: 10017 diff changeset	450 end
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325) Kim Alvefur <zash@zash.se> parents: 10017 diff changeset	451 if not after_id then
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325) Kim Alvefur <zash@zash.se> parents: 10017 diff changeset	452 return nil, "item-not-found";
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325) Kim Alvefur <zash@zash.se> parents: 10017 diff changeset	453 end
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325) Kim Alvefur <zash@zash.se> parents: 10017 diff changeset	454 where[#where+1] = '"sort_id" > ?';
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325) Kim Alvefur <zash@zash.se> parents: 10017 diff changeset	455 args[#args+1] = after_id;
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	456 end
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	457 if query.before then
10020 deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325) Kim Alvefur <zash@zash.se> parents: 10017 diff changeset	458 local before_id = nil;
10925 73e95ecec733 mod_storage_sql: Fix incorrect results when fetching items before specific archive id Matthew Wild <mwild1@gmail.com> parents: 10793 diff changeset	459 for row in engine:select(id_lookup_sql, query.before, args[1], args[2], args[3]) do
10020 deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325) Kim Alvefur <zash@zash.se> parents: 10017 diff changeset	460 before_id = row[1];
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325) Kim Alvefur <zash@zash.se> parents: 10017 diff changeset	461 end
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325) Kim Alvefur <zash@zash.se> parents: 10017 diff changeset	462 if not before_id then
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325) Kim Alvefur <zash@zash.se> parents: 10017 diff changeset	463 return nil, "item-not-found";
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325) Kim Alvefur <zash@zash.se> parents: 10017 diff changeset	464 end
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325) Kim Alvefur <zash@zash.se> parents: 10017 diff changeset	465 where[#where+1] = '"sort_id" < ?';
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325) Kim Alvefur <zash@zash.se> parents: 10017 diff changeset	466 args[#args+1] = before_id;
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	467 end
10020 deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325) Kim Alvefur <zash@zash.se> parents: 10017 diff changeset	468 return true;
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	469 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	470
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	471 function archive_store:find(username, query)
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	472 query = query or {};
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	473 local user,store = username,self.store;
9893 15270547c91c mod_storage_sql: Return cached count if only this is queried for Kim Alvefur <zash@zash.se> parents: 9892 diff changeset	474 local cache_key = jid_join(username, host, self.store);
15270547c91c mod_storage_sql: Return cached count if only this is queried for Kim Alvefur <zash@zash.se> parents: 9892 diff changeset	475 local total = archive_item_count_cache:get(cache_key);
10993 b5e7f4d533e2 mod_storage_sql: Measure hits/misses on the item count cache Kim Alvefur <zash@zash.se> parents: 10925 diff changeset	476 (total and item_count_cache_hit or item_count_cache_miss)();
11971 0b350909da24 mod_storage_sql: Return cached total where it makes sense Kim Alvefur <zash@zash.se> parents: 11970 diff changeset	477 if query.start == nil and query.with == nil and query["end"] == nil and query.key == nil and query.ids == nil then
0b350909da24 mod_storage_sql: Return cached total where it makes sense Kim Alvefur <zash@zash.se> parents: 11970 diff changeset	478 -- the query is for the whole archive, so a cached 'total' should be a
0b350909da24 mod_storage_sql: Return cached total where it makes sense Kim Alvefur <zash@zash.se> parents: 11970 diff changeset	479 -- relatively accurate response if that's all that is requested
0b350909da24 mod_storage_sql: Return cached total where it makes sense Kim Alvefur <zash@zash.se> parents: 11970 diff changeset	480 if total ~= nil and query.limit == 0 then return noop, total; end
0b350909da24 mod_storage_sql: Return cached total where it makes sense Kim Alvefur <zash@zash.se> parents: 11970 diff changeset	481 else
0b350909da24 mod_storage_sql: Return cached total where it makes sense Kim Alvefur <zash@zash.se> parents: 11970 diff changeset	482 -- not usable, so refresh it later if needed
0b350909da24 mod_storage_sql: Return cached total where it makes sense Kim Alvefur <zash@zash.se> parents: 11970 diff changeset	483 total = nil;
9893 15270547c91c mod_storage_sql: Return cached count if only this is queried for Kim Alvefur <zash@zash.se> parents: 9892 diff changeset	484 end
10031 17c175ad65f9 mod_storage_sql: Correctly return item-not-found error Kim Alvefur <zash@zash.se> parents: 10020 diff changeset	485 local ok, result, err = engine:transaction(function()
8033 6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	486 local sql_query = [[
8073 7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 8050 diff changeset	487 SELECT "key", "type", "value", "when", "with"
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 8050 diff changeset	488 FROM "prosodyarchive"
8033 6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	489 WHERE %s
8073 7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 8050 diff changeset	490 ORDER BY "sort_id" %s%s;
8033 6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	491 ]];
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	492 local args = { host, user or "", store, };
8073 7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 8050 diff changeset	493 local where = { "\"host\" = ?", "\"user\" = ?", "\"store\" = ?", };
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	494
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	495 archive_where(query, args, where);
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	496
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	497 -- Total matching
11971 0b350909da24 mod_storage_sql: Return cached total where it makes sense Kim Alvefur <zash@zash.se> parents: 11970 diff changeset	498 if query.total and not total then
0b350909da24 mod_storage_sql: Return cached total where it makes sense Kim Alvefur <zash@zash.se> parents: 11970 diff changeset	499
8073 7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 8050 diff changeset	500 local stats = engine:select("SELECT COUNT(*) FROM \"prosodyarchive\" WHERE "
8033 6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	501 .. t_concat(where, " AND "), unpack(args));
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	502 if stats then
7274 e0727512bb99 mod_storage_sql: Allow loops over results to end on their own Kim Alvefur <zash@zash.se> parents: 7273 diff changeset	503 for row in stats do
e0727512bb99 mod_storage_sql: Allow loops over results to end on their own Kim Alvefur <zash@zash.se> parents: 7273 diff changeset	504 total = row[1];
e0727512bb99 mod_storage_sql: Allow loops over results to end on their own Kim Alvefur <zash@zash.se> parents: 7273 diff changeset	505 end
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	506 end
11970 f0e78fa8f24c mod_storage_sql: Adjust handling of 'total' for multi-id queries Kim Alvefur <zash@zash.se> parents: 11969 diff changeset	507 if query.start == nil and query.with == nil and query["end"] == nil and query.key == nil and query.ids == nil then
9892 4803a25c7e36 mod_storage_sql: Cache total count if it's calculated as part of the current query Kim Alvefur <zash@zash.se> parents: 9891 diff changeset	508 archive_item_count_cache:set(cache_key, total);
4803a25c7e36 mod_storage_sql: Cache total count if it's calculated as part of the current query Kim Alvefur <zash@zash.se> parents: 9891 diff changeset	509 end
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	510 if query.limit == 0 then -- Skip the real query
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	511 return noop, total;
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	512 end
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	513 end
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	514
10020 deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325) Kim Alvefur <zash@zash.se> parents: 10017 diff changeset	515 local ok, err = archive_where_id_range(query, args, where);
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325) Kim Alvefur <zash@zash.se> parents: 10017 diff changeset	516 if not ok then return ok, err; end
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	517
8033 6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	518 sql_query = sql_query:format(t_concat(where, " AND "), query.reverse
12829 fba795dd99c8 mod_storage_sql: Fix #1639 Kim Alvefur <zash@zash.se> parents: 12823 diff changeset	519 and "DESC" or "ASC", query.limit and " LIMIT " .. query.limit or "");
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	520 return engine:select(sql_query, unpack(args));
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	521 end);
10031 17c175ad65f9 mod_storage_sql: Correctly return item-not-found error Kim Alvefur <zash@zash.se> parents: 10020 diff changeset	522 if not ok then return ok, result; end
17c175ad65f9 mod_storage_sql: Correctly return item-not-found error Kim Alvefur <zash@zash.se> parents: 10020 diff changeset	523 if not result then return nil, err; end
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	524 return function()
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	525 local row = result();
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	526 if row ~= nil then
8537 d7f31badd359 mod_storage_sql: Catch and report value deserialization errors Kim Alvefur <zash@zash.se> parents: 8480 diff changeset	527 local value, err = deserialize(row[2], row[3]);
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors Kim Alvefur <zash@zash.se> parents: 8480 diff changeset	528 assert(value ~= nil, err);
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors Kim Alvefur <zash@zash.se> parents: 8480 diff changeset	529 return row[1], value, row[4], row[5];
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	530 end
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	531 end, total;
4007 062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback. Waqas Hussain <waqas20@gmail.com> parents: 4004 diff changeset	532 end
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback. Waqas Hussain <waqas20@gmail.com> parents: 4004 diff changeset	533
11354 10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	534 function archive_store:get(username, key)
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	535 local iter, err = self:find(username, { key = key })
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	536 if not iter then return iter, err; end
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	537 for _, stanza, when, with in iter do
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	538 return stanza, when, with;
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	539 end
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	540 return nil, "item-not-found";
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	541 end
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	542
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	543 function archive_store:set(username, key, new_value, new_when, new_with)
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	544 local user,store = username,self.store;
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	545 local ok, result = engine:transaction(function ()
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	546
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	547 local update_query = [[
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	548 UPDATE "prosodyarchive"
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	549 SET %s
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	550 WHERE %s
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	551 ]];
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	552 local args = { host, user or "", store, key };
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	553 local setf = {};
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	554 local where = { "\"host\" = ?", "\"user\" = ?", "\"store\" = ?", "\"key\" = ?"};
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	555
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	556 if new_value then
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	557 table.insert(setf, '"type" = ?')
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	558 table.insert(setf, '"value" = ?')
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	559 local t, value = serialize(new_value);
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	560 table.insert(args, 1, t);
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	561 table.insert(args, 2, value);
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	562 end
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	563
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	564 if new_when then
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	565 table.insert(setf, 1, '"when" = ?')
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	566 table.insert(args, 1, new_when);
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	567 end
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	568
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	569 if new_with then
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	570 table.insert(setf, 1, '"with" = ?')
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	571 table.insert(args, 1, new_with);
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	572 end
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	573
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	574 update_query = update_query:format(t_concat(setf, ", "), t_concat(where, " AND "));
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	575 return engine:update(update_query, unpack(args));
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	576 end);
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	577 if not ok then return ok, result; end
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	578 return result:affected() == 1;
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	579 end
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	580
9904 bf061f5512f7 mod_storage_sql: Implement archive summary API Kim Alvefur <zash@zash.se> parents: 9896 diff changeset	581 function archive_store:summary(username, query)
bf061f5512f7 mod_storage_sql: Implement archive summary API Kim Alvefur <zash@zash.se> parents: 9896 diff changeset	582 query = query or {};
bf061f5512f7 mod_storage_sql: Implement archive summary API Kim Alvefur <zash@zash.se> parents: 9896 diff changeset	583 local user,store = username,self.store;
bf061f5512f7 mod_storage_sql: Implement archive summary API Kim Alvefur <zash@zash.se> parents: 9896 diff changeset	584 local ok, result = engine:transaction(function()
bf061f5512f7 mod_storage_sql: Implement archive summary API Kim Alvefur <zash@zash.se> parents: 9896 diff changeset	585 local sql_query = [[
10222 51f145094648 mod_storage_: Also include timestmap of first message in :summary API Kim Alvefur <zash@zash.se>* parents: 10221 diff changeset	586 SELECT DISTINCT "with", COUNT(*), MIN("when"), MAX("when")
9904 bf061f5512f7 mod_storage_sql: Implement archive summary API Kim Alvefur <zash@zash.se> parents: 9896 diff changeset	587 FROM "prosodyarchive"
bf061f5512f7 mod_storage_sql: Implement archive summary API Kim Alvefur <zash@zash.se> parents: 9896 diff changeset	588 WHERE %s
12599 5aafcf6a0730 mod_storage_sql: Fix summary API with Postgres (fixes #1766) Kim Alvefur <zash@zash.se> parents: 12592 diff changeset	589 GROUP BY "with";
9904 bf061f5512f7 mod_storage_sql: Implement archive summary API Kim Alvefur <zash@zash.se> parents: 9896 diff changeset	590 ]];
bf061f5512f7 mod_storage_sql: Implement archive summary API Kim Alvefur <zash@zash.se> parents: 9896 diff changeset	591 local args = { host, user or "", store, };
bf061f5512f7 mod_storage_sql: Implement archive summary API Kim Alvefur <zash@zash.se> parents: 9896 diff changeset	592 local where = { "\"host\" = ?", "\"user\" = ?", "\"store\" = ?", };
bf061f5512f7 mod_storage_sql: Implement archive summary API Kim Alvefur <zash@zash.se> parents: 9896 diff changeset	593
bf061f5512f7 mod_storage_sql: Implement archive summary API Kim Alvefur <zash@zash.se> parents: 9896 diff changeset	594 archive_where(query, args, where);
bf061f5512f7 mod_storage_sql: Implement archive summary API Kim Alvefur <zash@zash.se> parents: 9896 diff changeset	595
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	596 archive_where_id_range(query, args, where);
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	597
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	598 if query.limit then
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	599 args[#args+1] = query.limit;
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	600 end
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	601
12599 5aafcf6a0730 mod_storage_sql: Fix summary API with Postgres (fixes #1766) Kim Alvefur <zash@zash.se> parents: 12592 diff changeset	602 sql_query = sql_query:format(t_concat(where, " AND "));
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	603 return engine:select(sql_query, unpack(args));
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	604 end);
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	605 if not ok then return ok, result end
10220 1e2b444acb72 mod_storage_: Tweak :summary API to allow future expansion with more fields Kim Alvefur <zash@zash.se>* parents: 10033 diff changeset	606 local counts = {};
10222 51f145094648 mod_storage_: Also include timestmap of first message in :summary API Kim Alvefur <zash@zash.se>* parents: 10221 diff changeset	607 local earliest, latest = {}, {};
9904 bf061f5512f7 mod_storage_sql: Implement archive summary API Kim Alvefur <zash@zash.se> parents: 9896 diff changeset	608 for row in result do
bf061f5512f7 mod_storage_sql: Implement archive summary API Kim Alvefur <zash@zash.se> parents: 9896 diff changeset	609 local with, count = row[1], row[2];
10220 1e2b444acb72 mod_storage_: Tweak :summary API to allow future expansion with more fields Kim Alvefur <zash@zash.se>* parents: 10033 diff changeset	610 counts[with] = count;
10222 51f145094648 mod_storage_: Also include timestmap of first message in :summary API Kim Alvefur <zash@zash.se>* parents: 10221 diff changeset	611 earliest[with] = row[3];
51f145094648 mod_storage_: Also include timestmap of first message in :summary API Kim Alvefur <zash@zash.se>* parents: 10221 diff changeset	612 latest[with] = row[4];
9904 bf061f5512f7 mod_storage_sql: Implement archive summary API Kim Alvefur <zash@zash.se> parents: 9896 diff changeset	613 end
10220 1e2b444acb72 mod_storage_: Tweak :summary API to allow future expansion with more fields Kim Alvefur <zash@zash.se>* parents: 10033 diff changeset	614 return {
1e2b444acb72 mod_storage_: Tweak :summary API to allow future expansion with more fields Kim Alvefur <zash@zash.se>* parents: 10033 diff changeset	615 counts = counts;
10222 51f145094648 mod_storage_: Also include timestmap of first message in :summary API Kim Alvefur <zash@zash.se>* parents: 10221 diff changeset	616 earliest = earliest;
10221 068692cb9e78 mod_storage_: Include timestamp of latest message in :summary API Kim Alvefur <zash@zash.se>* parents: 10220 diff changeset	617 latest = latest;
10220 1e2b444acb72 mod_storage_: Tweak :summary API to allow future expansion with more fields Kim Alvefur <zash@zash.se>* parents: 10033 diff changeset	618 };
4007 062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback. Waqas Hussain <waqas20@gmail.com> parents: 4004 diff changeset	619 end
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback. Waqas Hussain <waqas20@gmail.com> parents: 4004 diff changeset	620
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	621 function archive_store:delete(username, query)
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	622 query = query or {};
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	623 local user,store = username,self.store;
7858 14fe60a65c69 mod_storage_sql: Fix to use correct order for return values from transaction (thanks postblue) Kim Alvefur <zash@zash.se> parents: 7847 diff changeset	624 local ok, stmt = engine:transaction(function()
8073 7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 8050 diff changeset	625 local sql_query = "DELETE FROM \"prosodyarchive\" WHERE %s;";
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	626 local args = { host, user or "", store, };
8073 7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 8050 diff changeset	627 local where = { "\"host\" = ?", "\"user\" = ?", "\"store\" = ?", };
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	628 if user == true then
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	629 table.remove(args, 2);
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	630 table.remove(where, 2);
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	631 end
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	632 archive_where(query, args, where);
10020 deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325) Kim Alvefur <zash@zash.se> parents: 10017 diff changeset	633 local ok, err = archive_where_id_range(query, args, where);
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325) Kim Alvefur <zash@zash.se> parents: 10017 diff changeset	634 if not ok then return ok, err; end
8395 fbb9a1c2120e mod_storage_sql: Add support for truncating deletion Kim Alvefur <zash@zash.se> parents: 8294 diff changeset	635 if query.truncate == nil then
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion Kim Alvefur <zash@zash.se> parents: 8294 diff changeset	636 sql_query = sql_query:format(t_concat(where, " AND "));
12829 fba795dd99c8 mod_storage_sql: Fix #1639 Kim Alvefur <zash@zash.se> parents: 12823 diff changeset	637 elseif engine.params.driver == "MySQL" then
fba795dd99c8 mod_storage_sql: Fix #1639 Kim Alvefur <zash@zash.se> parents: 12823 diff changeset	638 sql_query = [[
fba795dd99c8 mod_storage_sql: Fix #1639 Kim Alvefur <zash@zash.se> parents: 12823 diff changeset	639 DELETE result FROM prosodyarchive AS result JOIN (
fba795dd99c8 mod_storage_sql: Fix #1639 Kim Alvefur <zash@zash.se> parents: 12823 diff changeset	640 SELECT sort_id FROM prosodyarchive
fba795dd99c8 mod_storage_sql: Fix #1639 Kim Alvefur <zash@zash.se> parents: 12823 diff changeset	641 WHERE %s
fba795dd99c8 mod_storage_sql: Fix #1639 Kim Alvefur <zash@zash.se> parents: 12823 diff changeset	642 ORDER BY "sort_id" %s
fba795dd99c8 mod_storage_sql: Fix #1639 Kim Alvefur <zash@zash.se> parents: 12823 diff changeset	643 LIMIT 18446744073709551615 OFFSET %s
fba795dd99c8 mod_storage_sql: Fix #1639 Kim Alvefur <zash@zash.se> parents: 12823 diff changeset	644 ) AS limiter on result.sort_id = limiter.sort_id;]];
fba795dd99c8 mod_storage_sql: Fix #1639 Kim Alvefur <zash@zash.se> parents: 12823 diff changeset	645
fba795dd99c8 mod_storage_sql: Fix #1639 Kim Alvefur <zash@zash.se> parents: 12823 diff changeset	646 sql_query = string.format(sql_query, t_concat(where, " AND "),
fba795dd99c8 mod_storage_sql: Fix #1639 Kim Alvefur <zash@zash.se> parents: 12823 diff changeset	647 query.reverse and "ASC" or "DESC", query.truncate);
8395 fbb9a1c2120e mod_storage_sql: Add support for truncating deletion Kim Alvefur <zash@zash.se> parents: 8294 diff changeset	648 else
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion Kim Alvefur <zash@zash.se> parents: 8294 diff changeset	649 args[#args+1] = query.truncate;
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion Kim Alvefur <zash@zash.se> parents: 8294 diff changeset	650 local unlimited = "ALL";
10011 2408e6362c15 mod_storage_sql: Move code out of if-else chain Kim Alvefur <zash@zash.se> parents: 9628 diff changeset	651 sql_query = [[
2408e6362c15 mod_storage_sql: Move code out of if-else chain Kim Alvefur <zash@zash.se> parents: 9628 diff changeset	652 DELETE FROM "prosodyarchive"
2408e6362c15 mod_storage_sql: Move code out of if-else chain Kim Alvefur <zash@zash.se> parents: 9628 diff changeset	653 WHERE "sort_id" IN (
2408e6362c15 mod_storage_sql: Move code out of if-else chain Kim Alvefur <zash@zash.se> parents: 9628 diff changeset	654 SELECT "sort_id" FROM "prosodyarchive"
2408e6362c15 mod_storage_sql: Move code out of if-else chain Kim Alvefur <zash@zash.se> parents: 9628 diff changeset	655 WHERE %s
2408e6362c15 mod_storage_sql: Move code out of if-else chain Kim Alvefur <zash@zash.se> parents: 9628 diff changeset	656 ORDER BY "sort_id" %s
2408e6362c15 mod_storage_sql: Move code out of if-else chain Kim Alvefur <zash@zash.se> parents: 9628 diff changeset	657 LIMIT %s OFFSET ?
2408e6362c15 mod_storage_sql: Move code out of if-else chain Kim Alvefur <zash@zash.se> parents: 9628 diff changeset	658 );]];
8395 fbb9a1c2120e mod_storage_sql: Add support for truncating deletion Kim Alvefur <zash@zash.se> parents: 8294 diff changeset	659 if engine.params.driver == "SQLite3" then
13148 c6f46f33cecf mod_storage_sql: Record all SQLite3 compile options for potential use Kim Alvefur <zash@zash.se> parents: 13144 diff changeset	660 if engine.sqlite_compile_options.enable_update_delete_limit then
10012 acf4a7bfb6aa mod_storage_sql: Handle SQLite DELETE with LIMIT being optional (fixes #1359) Kim Alvefur <zash@zash.se> parents: 10011 diff changeset	661 sql_query = [[
acf4a7bfb6aa mod_storage_sql: Handle SQLite DELETE with LIMIT being optional (fixes #1359) Kim Alvefur <zash@zash.se> parents: 10011 diff changeset	662 DELETE FROM "prosodyarchive"
acf4a7bfb6aa mod_storage_sql: Handle SQLite DELETE with LIMIT being optional (fixes #1359) Kim Alvefur <zash@zash.se> parents: 10011 diff changeset	663 WHERE %s
acf4a7bfb6aa mod_storage_sql: Handle SQLite DELETE with LIMIT being optional (fixes #1359) Kim Alvefur <zash@zash.se> parents: 10011 diff changeset	664 ORDER BY "sort_id" %s
acf4a7bfb6aa mod_storage_sql: Handle SQLite DELETE with LIMIT being optional (fixes #1359) Kim Alvefur <zash@zash.se> parents: 10011 diff changeset	665 LIMIT %s OFFSET ?;
acf4a7bfb6aa mod_storage_sql: Handle SQLite DELETE with LIMIT being optional (fixes #1359) Kim Alvefur <zash@zash.se> parents: 10011 diff changeset	666 ]];
acf4a7bfb6aa mod_storage_sql: Handle SQLite DELETE with LIMIT being optional (fixes #1359) Kim Alvefur <zash@zash.se> parents: 10011 diff changeset	667 end
8395 fbb9a1c2120e mod_storage_sql: Add support for truncating deletion Kim Alvefur <zash@zash.se> parents: 8294 diff changeset	668 unlimited = "-1";
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion Kim Alvefur <zash@zash.se> parents: 8294 diff changeset	669 end
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion Kim Alvefur <zash@zash.se> parents: 8294 diff changeset	670 sql_query = string.format(sql_query, t_concat(where, " AND "),
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion Kim Alvefur <zash@zash.se> parents: 8294 diff changeset	671 query.reverse and "ASC" or "DESC", unlimited);
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion Kim Alvefur <zash@zash.se> parents: 8294 diff changeset	672 end
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	673 return engine:delete(sql_query, unpack(args));
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	674 end);
11969 395dce95508d mod_storage_sql: Fix traceback on multi-user deletion Kim Alvefur <zash@zash.se> parents: 11967 diff changeset	675 if username == true then
395dce95508d mod_storage_sql: Fix traceback on multi-user deletion Kim Alvefur <zash@zash.se> parents: 11967 diff changeset	676 archive_item_count_cache:clear();
395dce95508d mod_storage_sql: Fix traceback on multi-user deletion Kim Alvefur <zash@zash.se> parents: 11967 diff changeset	677 else
395dce95508d mod_storage_sql: Fix traceback on multi-user deletion Kim Alvefur <zash@zash.se> parents: 11967 diff changeset	678 local cache_key = jid_join(username, host, self.store);
13150 9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set Kim Alvefur <zash@zash.se> parents: 13149 diff changeset	679 if query.start == nil and query.with == nil and query["end"] == nil and query.key == nil and query.ids == nil and query.truncate == nil then
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set Kim Alvefur <zash@zash.se> parents: 13149 diff changeset	680 -- All items deleted, count should be zero.
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set Kim Alvefur <zash@zash.se> parents: 13149 diff changeset	681 archive_item_count_cache:set(cache_key, 0);
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set Kim Alvefur <zash@zash.se> parents: 13149 diff changeset	682 else
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set Kim Alvefur <zash@zash.se> parents: 13149 diff changeset	683 -- Not sure how many items left
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set Kim Alvefur <zash@zash.se> parents: 13149 diff changeset	684 archive_item_count_cache:set(cache_key, nil);
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set Kim Alvefur <zash@zash.se> parents: 13149 diff changeset	685 end
11969 395dce95508d mod_storage_sql: Fix traceback on multi-user deletion Kim Alvefur <zash@zash.se> parents: 11967 diff changeset	686 end
7858 14fe60a65c69 mod_storage_sql: Fix to use correct order for return values from transaction (thanks postblue) Kim Alvefur <zash@zash.se> parents: 7847 diff changeset	687 return ok and stmt:affected(), stmt;
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	688 end
5776 bd0ff8ae98a8 Remove all trailing whitespace Florian Zeitz <florob@babelmonkeys.de> parents: 5209 diff changeset	689
10002 b6b5b9d7417d mod_storage_sql: Add support for iterating over users in archive stores Kim Alvefur <zash@zash.se> parents: 9904 diff changeset	690 function archive_store:users()
b6b5b9d7417d mod_storage_sql: Add support for iterating over users in archive stores Kim Alvefur <zash@zash.se> parents: 9904 diff changeset	691 local ok, result = engine:transaction(function()
b6b5b9d7417d mod_storage_sql: Add support for iterating over users in archive stores Kim Alvefur <zash@zash.se> parents: 9904 diff changeset	692 local select_sql = [[
b6b5b9d7417d mod_storage_sql: Add support for iterating over users in archive stores Kim Alvefur <zash@zash.se> parents: 9904 diff changeset	693 SELECT DISTINCT "user"
b6b5b9d7417d mod_storage_sql: Add support for iterating over users in archive stores Kim Alvefur <zash@zash.se> parents: 9904 diff changeset	694 FROM "prosodyarchive"
b6b5b9d7417d mod_storage_sql: Add support for iterating over users in archive stores Kim Alvefur <zash@zash.se> parents: 9904 diff changeset	695 WHERE "host"=? AND "store"=?;
b6b5b9d7417d mod_storage_sql: Add support for iterating over users in archive stores Kim Alvefur <zash@zash.se> parents: 9904 diff changeset	696 ]];
b6b5b9d7417d mod_storage_sql: Add support for iterating over users in archive stores Kim Alvefur <zash@zash.se> parents: 9904 diff changeset	697 return engine:select(select_sql, host, self.store);
b6b5b9d7417d mod_storage_sql: Add support for iterating over users in archive stores Kim Alvefur <zash@zash.se> parents: 9904 diff changeset	698 end);
b6b5b9d7417d mod_storage_sql: Add support for iterating over users in archive stores Kim Alvefur <zash@zash.se> parents: 9904 diff changeset	699 if not ok then error(result); end
b6b5b9d7417d mod_storage_sql: Add support for iterating over users in archive stores Kim Alvefur <zash@zash.se> parents: 9904 diff changeset	700 return iterator(result);
b6b5b9d7417d mod_storage_sql: Add support for iterating over users in archive stores Kim Alvefur <zash@zash.se> parents: 9904 diff changeset	701 end
b6b5b9d7417d mod_storage_sql: Add support for iterating over users in archive stores Kim Alvefur <zash@zash.se> parents: 9904 diff changeset	702
13144 033ea8b46d6a mod_storage_sql: Compose a keyval+ store out of keyval and map store methods Kim Alvefur <zash@zash.se> parents: 13143 diff changeset	703 local keyvalplus = {
033ea8b46d6a mod_storage_sql: Compose a keyval+ store out of keyval and map store methods Kim Alvefur <zash@zash.se> parents: 13143 diff changeset	704 __index = {
033ea8b46d6a mod_storage_sql: Compose a keyval+ store out of keyval and map store methods Kim Alvefur <zash@zash.se> parents: 13143 diff changeset	705 -- keyval
033ea8b46d6a mod_storage_sql: Compose a keyval+ store out of keyval and map store methods Kim Alvefur <zash@zash.se> parents: 13143 diff changeset	706 get = keyval_store.get;
033ea8b46d6a mod_storage_sql: Compose a keyval+ store out of keyval and map store methods Kim Alvefur <zash@zash.se> parents: 13143 diff changeset	707 set = keyval_store.set;
033ea8b46d6a mod_storage_sql: Compose a keyval+ store out of keyval and map store methods Kim Alvefur <zash@zash.se> parents: 13143 diff changeset	708 items = keyval_store.users;
033ea8b46d6a mod_storage_sql: Compose a keyval+ store out of keyval and map store methods Kim Alvefur <zash@zash.se> parents: 13143 diff changeset	709 -- map
033ea8b46d6a mod_storage_sql: Compose a keyval+ store out of keyval and map store methods Kim Alvefur <zash@zash.se> parents: 13143 diff changeset	710 get_key = map_store.get;
033ea8b46d6a mod_storage_sql: Compose a keyval+ store out of keyval and map store methods Kim Alvefur <zash@zash.se> parents: 13143 diff changeset	711 set_key = map_store.set;
033ea8b46d6a mod_storage_sql: Compose a keyval+ store out of keyval and map store methods Kim Alvefur <zash@zash.se> parents: 13143 diff changeset	712 remove = map_store.remove;
033ea8b46d6a mod_storage_sql: Compose a keyval+ store out of keyval and map store methods Kim Alvefur <zash@zash.se> parents: 13143 diff changeset	713 set_keys = map_store.set_keys;
033ea8b46d6a mod_storage_sql: Compose a keyval+ store out of keyval and map store methods Kim Alvefur <zash@zash.se> parents: 13143 diff changeset	714 get_key_from_all = map_store.get_all;
033ea8b46d6a mod_storage_sql: Compose a keyval+ store out of keyval and map store methods Kim Alvefur <zash@zash.se> parents: 13143 diff changeset	715 delete_key_from_all = map_store.delete_all;
033ea8b46d6a mod_storage_sql: Compose a keyval+ store out of keyval and map store methods Kim Alvefur <zash@zash.se> parents: 13143 diff changeset	716 };
033ea8b46d6a mod_storage_sql: Compose a keyval+ store out of keyval and map store methods Kim Alvefur <zash@zash.se> parents: 13143 diff changeset	717 }
033ea8b46d6a mod_storage_sql: Compose a keyval+ store out of keyval and map store methods Kim Alvefur <zash@zash.se> parents: 13143 diff changeset	718
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	719 local stores = {
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	720 keyval = keyval_store;
6954 400badaf1fc7 mod_storage_sql: Add map store (backported from trunk) Matthew Wild <mwild1@gmail.com> parents: 6953 diff changeset	721 map = map_store;
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	722 archive = archive_store;
13144 033ea8b46d6a mod_storage_sql: Compose a keyval+ store out of keyval and map store methods Kim Alvefur <zash@zash.se> parents: 13143 diff changeset	723 ["keyval+"] = keyvalplus;
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	724 };
5776 bd0ff8ae98a8 Remove all trailing whitespace Florian Zeitz <florob@babelmonkeys.de> parents: 5209 diff changeset	725
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	726 --- Implement storage driver API
5776 bd0ff8ae98a8 Remove all trailing whitespace Florian Zeitz <florob@babelmonkeys.de> parents: 5209 diff changeset	727
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	728 -- FIXME: Some of these operations need to operate on the archive store(s) too
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	729
5122 b41c33dc7c36 mod_storage_: Don't explicitly set driver name, to ease copying/renaming modules. Waqas Hussain <waqas20@gmail.com>* parents: 5121 diff changeset	730 local driver = {};
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	731
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	732 function driver:open(store, typ)
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	733 local store_mt = stores[typ or "keyval"];
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	734 if store_mt then
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	735 return setmetatable({ store = store }, store_mt);
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	736 end
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	737 return nil, "unsupported-store";
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	738 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	739
5132 ce6546f867f9 mod_storage_sql: Remove obsolete comment Matthew Wild <mwild1@gmail.com> parents: 5130 diff changeset	740 function driver:stores(username)
8073 7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 8050 diff changeset	741 local query = "SELECT DISTINCT \"store\" FROM \"prosody\" WHERE \"host\"=? AND \"user\"" ..
5130 051d352ed03c storagemanager, datamanager, mod_storage_{internal,sql}: Replace list_stores() with an iterator version Kim Alvefur <zash@zash.se> parents: 5056 diff changeset	742 (username == true and "!=?" or "=?");
5035 874cab7b4b3e mod_storage_sql: Add method for listing stores Kim Alvefur <zash@zash.se> parents: 5034 diff changeset	743 if username == true or not username then
874cab7b4b3e mod_storage_sql: Add method for listing stores Kim Alvefur <zash@zash.se> parents: 5034 diff changeset	744 username = "";
874cab7b4b3e mod_storage_sql: Add method for listing stores Kim Alvefur <zash@zash.se> parents: 5034 diff changeset	745 end
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	746 local ok, result = engine:transaction(function()
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	747 return engine:select(query, host, username);
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	748 end);
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	749 if not ok then return ok, result end
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	750 return iterator(result);
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	751 end
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	752
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	753 function driver:purge(username)
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	754 return engine:transaction(function()
8292 2fc8b83dd736 mod_storage_sql: Remove return values from purge method Kim Alvefur <zash@zash.se> parents: 8138 diff changeset	755 engine:delete("DELETE FROM \"prosody\" WHERE \"host\"=? AND \"user\"=?", host, username);
8293 34814a908557 mod_storage_sql: Also clear the prosodyarchive table when an user is deleted (fixes #1009) Kim Alvefur <zash@zash.se> parents: 8292 diff changeset	756 engine:delete("DELETE FROM \"prosodyarchive\" WHERE \"host\"=? AND \"user\"=?", host, username);
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	757 end);
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	758 end
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	759
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	760 --- Initialization
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	761
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	762
9459 6c279302fff4 mod_storage_sql: Remove unused function argument [luacheck] Kim Alvefur <zash@zash.se> parents: 8537 diff changeset	763 local function create_table(engine) -- luacheck: ignore 431/engine
13832 5973a5d22ab5 mod_storage_sql: Delay showing SQL library error until attempted load Kim Alvefur <zash@zash.se> parents: 13784 diff changeset	764 local sql = get_sql_lib(engine.params.driver);
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	765 local Table, Column, Index = sql.Table, sql.Column, sql.Index;
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	766
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	767 local ProsodyTable = Table {
8470 81f894d08b17 mod_storage_sql: Remove ability to override name of key-value table (unreachable and would not work) Kim Alvefur <zash@zash.se> parents: 8410 diff changeset	768 name = "prosody";
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	769 Column { name="host", type="TEXT", nullable=false };
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	770 Column { name="user", type="TEXT", nullable=false };
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	771 Column { name="store", type="TEXT", nullable=false };
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	772 Column { name="key", type="TEXT", nullable=false };
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	773 Column { name="type", type="TEXT", nullable=false };
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	774 Column { name="value", type="MEDIUMTEXT", nullable=false };
13143 3ec48555b773 mod_storage_sql: Add UPSERT support Kim Alvefur <zash@zash.se> parents: 12977 diff changeset	775 Index { name = "prosody_unique_index"; unique = engine.params.driver ~= "MySQL"; "host"; "user"; "store"; "key" };
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	776 };
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	777 engine:transaction(function()
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	778 ProsodyTable:create(engine);
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	779 end);
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	780
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	781 local ProsodyArchiveTable = Table {
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	782 name="prosodyarchive";
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	783 Column { name="sort_id", type="INTEGER", primary_key=true, auto_increment=true };
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	784 Column { name="host", type="TEXT", nullable=false };
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	785 Column { name="user", type="TEXT", nullable=false };
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	786 Column { name="store", type="TEXT", nullable=false };
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	787 Column { name="key", type="TEXT", nullable=false }; -- item id
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	788 Column { name="when", type="INTEGER", nullable=false }; -- timestamp
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	789 Column { name="with", type="TEXT", nullable=false }; -- related id
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	790 Column { name="type", type="TEXT", nullable=false };
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	791 Column { name="value", type="MEDIUMTEXT", nullable=false };
9500 7de89d66cbb9 mod_storage_sql: Keep prosodyarchive_index unique constraint on non-MySQL DBs Matthew Wild <mwild1@gmail.com> parents: 9493 diff changeset	792 Index { name="prosodyarchive_index", unique = engine.params.driver ~= "MySQL", "host", "user", "store", "key" };
8478 384658c7a26a mod_storage_sql: Index over contact and timestamp (happy now Ge0rg?) Kim Alvefur <zash@zash.se> parents: 8409 diff changeset	793 Index { name="prosodyarchive_with_when", "host", "user", "store", "with", "when" };
8409 e98b4352d7df mod_storage_sql: Add indices that include the with and when columns of archives Kim Alvefur <zash@zash.se> parents: 8293 diff changeset	794 Index { name="prosodyarchive_when", "host", "user", "store", "when" };
10700 3fce670e0733 mod_storage_sql: Add index covering sort_id to improve performance (fixes #1505) Kim Alvefur <zash@zash.se> parents: 10655 diff changeset	795 Index { name="prosodyarchive_sort", "host", "user", "store", "sort_id" };
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	796 };
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	797 engine:transaction(function()
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	798 ProsodyArchiveTable:create(engine);
5130 051d352ed03c storagemanager, datamanager, mod_storage_{internal,sql}: Replace list_stores() with an iterator version Kim Alvefur <zash@zash.se> parents: 5056 diff changeset	799 end);
5035 874cab7b4b3e mod_storage_sql: Add method for listing stores Kim Alvefur <zash@zash.se> parents: 5034 diff changeset	800 end
874cab7b4b3e mod_storage_sql: Add method for listing stores Kim Alvefur <zash@zash.se> parents: 5034 diff changeset	801
8034 149553feb04e mod_storage_sql: Pass SQL engine as an argument to upgrade and table creator functions Kim Alvefur <zash@zash.se> parents: 8033 diff changeset	802 local function upgrade_table(engine, params, apply_changes) -- luacheck: ignore 431/engine
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	803 local changes = false;
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	804 if params.driver == "MySQL" then
13832 5973a5d22ab5 mod_storage_sql: Delay showing SQL library error until attempted load Kim Alvefur <zash@zash.se> parents: 13784 diff changeset	805 local sql = get_sql_lib("MySQL");
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	806 local success,err = engine:transaction(function()
9493 55b40f3fa659 mod_storage_sql: Change prosodyarchive_index to be non-unique (fixes #1087) Matthew Wild <mwild1@gmail.com> parents: 9492 diff changeset	807 do
9628 2fcf517b811e mod_storage_sql: Catch errors during schema upgrade (thanks Nothing4You) Matthew Wild <mwild1@gmail.com> parents: 9537 diff changeset	808 local result = assert(engine:execute("SHOW COLUMNS FROM \"prosody\" WHERE \"Field\"='value' and \"Type\"='text'"));
9493 55b40f3fa659 mod_storage_sql: Change prosodyarchive_index to be non-unique (fixes #1087) Matthew Wild <mwild1@gmail.com> parents: 9492 diff changeset	809 if result:rowcount() > 0 then
55b40f3fa659 mod_storage_sql: Change prosodyarchive_index to be non-unique (fixes #1087) Matthew Wild <mwild1@gmail.com> parents: 9492 diff changeset	810 changes = true;
55b40f3fa659 mod_storage_sql: Change prosodyarchive_index to be non-unique (fixes #1087) Matthew Wild <mwild1@gmail.com> parents: 9492 diff changeset	811 if apply_changes then
55b40f3fa659 mod_storage_sql: Change prosodyarchive_index to be non-unique (fixes #1087) Matthew Wild <mwild1@gmail.com> parents: 9492 diff changeset	812 module:log("info", "Upgrading database schema (value column size)...");
9628 2fcf517b811e mod_storage_sql: Catch errors during schema upgrade (thanks Nothing4You) Matthew Wild <mwild1@gmail.com> parents: 9537 diff changeset	813 assert(engine:execute("ALTER TABLE \"prosody\" MODIFY COLUMN \"value\" MEDIUMTEXT"));
9493 55b40f3fa659 mod_storage_sql: Change prosodyarchive_index to be non-unique (fixes #1087) Matthew Wild <mwild1@gmail.com> parents: 9492 diff changeset	814 module:log("info", "Database table automatically upgraded");
55b40f3fa659 mod_storage_sql: Change prosodyarchive_index to be non-unique (fixes #1087) Matthew Wild <mwild1@gmail.com> parents: 9492 diff changeset	815 end
55b40f3fa659 mod_storage_sql: Change prosodyarchive_index to be non-unique (fixes #1087) Matthew Wild <mwild1@gmail.com> parents: 9492 diff changeset	816 end
55b40f3fa659 mod_storage_sql: Change prosodyarchive_index to be non-unique (fixes #1087) Matthew Wild <mwild1@gmail.com> parents: 9492 diff changeset	817 end
55b40f3fa659 mod_storage_sql: Change prosodyarchive_index to be non-unique (fixes #1087) Matthew Wild <mwild1@gmail.com> parents: 9492 diff changeset	818
55b40f3fa659 mod_storage_sql: Change prosodyarchive_index to be non-unique (fixes #1087) Matthew Wild <mwild1@gmail.com> parents: 9492 diff changeset	819 do
9501 8715961bfa13 mod_storage_sql: Fix issue number in comment that was fixed by 55b40f3fa659 (fixes #1073) Matthew Wild <mwild1@gmail.com> parents: 9500 diff changeset	820 -- Ensure index is not unique (issue #1073)
9493 55b40f3fa659 mod_storage_sql: Change prosodyarchive_index to be non-unique (fixes #1087) Matthew Wild <mwild1@gmail.com> parents: 9492 diff changeset	821 local result = assert(engine:execute([[SHOW INDEX FROM prosodyarchive WHERE key_name='prosodyarchive_index' and non_unique=0]]));
55b40f3fa659 mod_storage_sql: Change prosodyarchive_index to be non-unique (fixes #1087) Matthew Wild <mwild1@gmail.com> parents: 9492 diff changeset	822 if result:rowcount() > 0 then
55b40f3fa659 mod_storage_sql: Change prosodyarchive_index to be non-unique (fixes #1087) Matthew Wild <mwild1@gmail.com> parents: 9492 diff changeset	823 changes = true;
55b40f3fa659 mod_storage_sql: Change prosodyarchive_index to be non-unique (fixes #1087) Matthew Wild <mwild1@gmail.com> parents: 9492 diff changeset	824 if apply_changes then
55b40f3fa659 mod_storage_sql: Change prosodyarchive_index to be non-unique (fixes #1087) Matthew Wild <mwild1@gmail.com> parents: 9492 diff changeset	825 module:log("info", "Upgrading database schema (prosodyarchive_index)...");
9628 2fcf517b811e mod_storage_sql: Catch errors during schema upgrade (thanks Nothing4You) Matthew Wild <mwild1@gmail.com> parents: 9537 diff changeset	826 assert(engine:execute[[ALTER TABLE "prosodyarchive" DROP INDEX prosodyarchive_index;]]);
9493 55b40f3fa659 mod_storage_sql: Change prosodyarchive_index to be non-unique (fixes #1087) Matthew Wild <mwild1@gmail.com> parents: 9492 diff changeset	827 local new_index = sql.Index { table = "prosodyarchive", name="prosodyarchive_index", "host", "user", "store", "key" };
9628 2fcf517b811e mod_storage_sql: Catch errors during schema upgrade (thanks Nothing4You) Matthew Wild <mwild1@gmail.com> parents: 9537 diff changeset	828 assert(engine:_create_index(new_index));
9493 55b40f3fa659 mod_storage_sql: Change prosodyarchive_index to be non-unique (fixes #1087) Matthew Wild <mwild1@gmail.com> parents: 9492 diff changeset	829 module:log("info", "Database table automatically upgraded");
55b40f3fa659 mod_storage_sql: Change prosodyarchive_index to be non-unique (fixes #1087) Matthew Wild <mwild1@gmail.com> parents: 9492 diff changeset	830 end
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	831 end
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	832 end
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	833 return true;
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	834 end);
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	835 if not success then
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	836 module:log("error", "Failed to check/upgrade database schema (%s), please see "
7359 a5a080c12c96 Update every link to the documentation to use HTTPS Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 7305 diff changeset	837 .."https://prosody.im/doc/mysql for help",
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	838 err or "unknown error");
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	839 return false;
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	840 end
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	841
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	842 -- COMPAT w/pre-0.10: Upgrade table to UTF-8 if not already
8033 6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	843 local check_encoding_query = [[
8073 7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 8050 diff changeset	844 SELECT "COLUMN_NAME","COLUMN_TYPE","TABLE_NAME"
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 8050 diff changeset	845 FROM "information_schema"."columns"
8081 a201f315de55 mod_storage_sql: Break long lines Kim Alvefur <zash@zash.se> parents: 8080 diff changeset	846 WHERE "TABLE_NAME" LIKE 'prosody%%'
8082 8ca11201bfe7 mod_storage_sql: Limit encoding check to current database Kim Alvefur <zash@zash.se> parents: 8081 diff changeset	847 AND "TABLE_SCHEMA" = ?
8081 a201f315de55 mod_storage_sql: Break long lines Kim Alvefur <zash@zash.se> parents: 8080 diff changeset	848 AND ( "CHARACTER_SET_NAME"!=? OR "COLLATION_NAME"!=?);
8033 6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	849 ]];
8038 cd6cef579e82 mod_storage_sql: Remove unused return values (should those actually be used?) Kim Alvefur <zash@zash.se> parents: 8037 diff changeset	850 -- FIXME Is it ok to ignore the return values from this?
cd6cef579e82 mod_storage_sql: Remove unused return values (should those actually be used?) Kim Alvefur <zash@zash.se> parents: 8037 diff changeset	851 engine:transaction(function()
8082 8ca11201bfe7 mod_storage_sql: Limit encoding check to current database Kim Alvefur <zash@zash.se> parents: 8081 diff changeset	852 local result = assert(engine:execute(check_encoding_query, params.database, engine.charset, engine.charset.."_bin"));
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	853 local n_bad_columns = result:rowcount();
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	854 if n_bad_columns > 0 then
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	855 changes = true;
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	856 if apply_changes then
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	857 module:log("warn", "Found %d columns in prosody table requiring encoding change, updating now...", n_bad_columns);
8073 7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 8050 diff changeset	858 local fix_column_query1 = "ALTER TABLE \"%s\" CHANGE \"%s\" \"%s\" BLOB;";
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 8050 diff changeset	859 local fix_column_query2 = "ALTER TABLE \"%s\" CHANGE \"%s\" \"%s\" %s CHARACTER SET '%s' COLLATE '%s_bin';";
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	860 for row in result:rows() do
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	861 local column_name, column_type, table_name = unpack(row);
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	862 module:log("debug", "Fixing column %s in table %s", column_name, table_name);
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	863 engine:execute(fix_column_query1:format(table_name, column_name, column_name));
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	864 engine:execute(fix_column_query2:format(table_name, column_name, column_name, column_type, engine.charset, engine.charset));
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	865 end
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	866 module:log("info", "Database encoding upgrade complete!");
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	867 end
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	868 end
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	869 end);
8081 a201f315de55 mod_storage_sql: Break long lines Kim Alvefur <zash@zash.se> parents: 8080 diff changeset	870 success,err = engine:transaction(function()
8082 8ca11201bfe7 mod_storage_sql: Limit encoding check to current database Kim Alvefur <zash@zash.se> parents: 8081 diff changeset	871 return engine:execute(check_encoding_query, params.database,
8ca11201bfe7 mod_storage_sql: Limit encoding check to current database Kim Alvefur <zash@zash.se> parents: 8081 diff changeset	872 engine.charset, engine.charset.."_bin");
13780 40185fad7505 mod_storage_sql: Fix indentation Kim Alvefur <zash@zash.se> parents: 13661 diff changeset	873 end);
40185fad7505 mod_storage_sql: Fix indentation Kim Alvefur <zash@zash.se> parents: 13661 diff changeset	874 if not success then
40185fad7505 mod_storage_sql: Fix indentation Kim Alvefur <zash@zash.se> parents: 13661 diff changeset	875 module:log("error", "Failed to check/upgrade database encoding: %s", err or "unknown error");
40185fad7505 mod_storage_sql: Fix indentation Kim Alvefur <zash@zash.se> parents: 13661 diff changeset	876 return false;
40185fad7505 mod_storage_sql: Fix indentation Kim Alvefur <zash@zash.se> parents: 13661 diff changeset	877 end
40185fad7505 mod_storage_sql: Fix indentation Kim Alvefur <zash@zash.se> parents: 13661 diff changeset	878 else
40185fad7505 mod_storage_sql: Fix indentation Kim Alvefur <zash@zash.se> parents: 13661 diff changeset	879 local indices = {};
40185fad7505 mod_storage_sql: Fix indentation Kim Alvefur <zash@zash.se> parents: 13661 diff changeset	880 engine:transaction(function ()
40185fad7505 mod_storage_sql: Fix indentation Kim Alvefur <zash@zash.se> parents: 13661 diff changeset	881 if params.driver == "SQLite3" then
40185fad7505 mod_storage_sql: Fix indentation Kim Alvefur <zash@zash.se> parents: 13661 diff changeset	882 for row in engine:select [[SELECT "name" FROM "sqlite_schema" WHERE "type"='index' AND "tbl_name"='prosody' AND "name"='prosody_index';]] do
40185fad7505 mod_storage_sql: Fix indentation Kim Alvefur <zash@zash.se> parents: 13661 diff changeset	883 indices[row[1]] = true;
13143 3ec48555b773 mod_storage_sql: Add UPSERT support Kim Alvefur <zash@zash.se> parents: 12977 diff changeset	884 end
13780 40185fad7505 mod_storage_sql: Fix indentation Kim Alvefur <zash@zash.se> parents: 13661 diff changeset	885 elseif params.driver == "PostgreSQL" then
40185fad7505 mod_storage_sql: Fix indentation Kim Alvefur <zash@zash.se> parents: 13661 diff changeset	886 for row in engine:select [[SELECT "indexname" FROM "pg_indexes" WHERE "tablename"='prosody' AND "indexname"='prosody_index';]] do
40185fad7505 mod_storage_sql: Fix indentation Kim Alvefur <zash@zash.se> parents: 13661 diff changeset	887 indices[row[1]] = true;
13143 3ec48555b773 mod_storage_sql: Add UPSERT support Kim Alvefur <zash@zash.se> parents: 12977 diff changeset	888 end
3ec48555b773 mod_storage_sql: Add UPSERT support Kim Alvefur <zash@zash.se> parents: 12977 diff changeset	889 end
13780 40185fad7505 mod_storage_sql: Fix indentation Kim Alvefur <zash@zash.se> parents: 13661 diff changeset	890 end)
40185fad7505 mod_storage_sql: Fix indentation Kim Alvefur <zash@zash.se> parents: 13661 diff changeset	891 if indices["prosody_index"] then
13784 b30eaa74d35b mod_storage_sql: Drop legacy index without confirmation to ease upgrades Kim Alvefur <zash@zash.se> parents: 13780 diff changeset	892 local success = engine:transaction(function ()
b30eaa74d35b mod_storage_sql: Drop legacy index without confirmation to ease upgrades Kim Alvefur <zash@zash.se> parents: 13780 diff changeset	893 return assert(engine:execute([[DROP INDEX "prosody_index";]]));
b30eaa74d35b mod_storage_sql: Drop legacy index without confirmation to ease upgrades Kim Alvefur <zash@zash.se> parents: 13780 diff changeset	894 end);
b30eaa74d35b mod_storage_sql: Drop legacy index without confirmation to ease upgrades Kim Alvefur <zash@zash.se> parents: 13780 diff changeset	895 if not success then
b30eaa74d35b mod_storage_sql: Drop legacy index without confirmation to ease upgrades Kim Alvefur <zash@zash.se> parents: 13780 diff changeset	896 module:log("error", "Failed to delete obsolete index \"prosody_index\"");
b30eaa74d35b mod_storage_sql: Drop legacy index without confirmation to ease upgrades Kim Alvefur <zash@zash.se> parents: 13780 diff changeset	897 return false;
13780 40185fad7505 mod_storage_sql: Fix indentation Kim Alvefur <zash@zash.se> parents: 13661 diff changeset	898 end
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	899 end
13780 40185fad7505 mod_storage_sql: Fix indentation Kim Alvefur <zash@zash.se> parents: 13661 diff changeset	900 end
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	901 return changes;
5040 685403a6fee1 mod_storage_sql: Add method for removing all data belonging to a user Kim Alvefur <zash@zash.se> parents: 5035 diff changeset	902 end
685403a6fee1 mod_storage_sql: Add method for removing all data belonging to a user Kim Alvefur <zash@zash.se> parents: 5035 diff changeset	903
8035 58d6c2ab3d16 mod_storage_sql: Ignore name clash [luacheck] Kim Alvefur <zash@zash.se> parents: 8034 diff changeset	904 local function normalize_database(driver, database) -- luacheck: ignore 431/driver
7752 56abe6a8e761 mod_storage_sql: Create a new table to hold normalized database parameters (fixes #636) Kim Alvefur <zash@zash.se> parents: 7751 diff changeset	905 if driver == "SQLite3" and database ~= ":memory:" then
56abe6a8e761 mod_storage_sql: Create a new table to hold normalized database parameters (fixes #636) Kim Alvefur <zash@zash.se> parents: 7751 diff changeset	906 return resolve_relative_path(prosody.paths.data or ".", database or "prosody.sqlite");
56abe6a8e761 mod_storage_sql: Create a new table to hold normalized database parameters (fixes #636) Kim Alvefur <zash@zash.se> parents: 7751 diff changeset	907 end
56abe6a8e761 mod_storage_sql: Create a new table to hold normalized database parameters (fixes #636) Kim Alvefur <zash@zash.se> parents: 7751 diff changeset	908 return database;
56abe6a8e761 mod_storage_sql: Create a new table to hold normalized database parameters (fixes #636) Kim Alvefur <zash@zash.se> parents: 7751 diff changeset	909 end
56abe6a8e761 mod_storage_sql: Create a new table to hold normalized database parameters (fixes #636) Kim Alvefur <zash@zash.se> parents: 7751 diff changeset	910
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	911 local function normalize_params(params)
7752 56abe6a8e761 mod_storage_sql: Create a new table to hold normalized database parameters (fixes #636) Kim Alvefur <zash@zash.se> parents: 7751 diff changeset	912 return {
8036 7ebaefc88b3d mod_storage_sql: Split long lines [luacheck] Kim Alvefur <zash@zash.se> parents: 8035 diff changeset	913 driver = assert(params.driver,
7ebaefc88b3d mod_storage_sql: Split long lines [luacheck] Kim Alvefur <zash@zash.se> parents: 8035 diff changeset	914 "Configuration error: Both the SQL driver and the database need to be specified");
7ebaefc88b3d mod_storage_sql: Split long lines [luacheck] Kim Alvefur <zash@zash.se> parents: 8035 diff changeset	915 database = assert(normalize_database(params.driver, params.database),
7ebaefc88b3d mod_storage_sql: Split long lines [luacheck] Kim Alvefur <zash@zash.se> parents: 8035 diff changeset	916 "Configuration error: Both the SQL driver and the database need to be specified");
7752 56abe6a8e761 mod_storage_sql: Create a new table to hold normalized database parameters (fixes #636) Kim Alvefur <zash@zash.se> parents: 7751 diff changeset	917 username = params.username;
56abe6a8e761 mod_storage_sql: Create a new table to hold normalized database parameters (fixes #636) Kim Alvefur <zash@zash.se> parents: 7751 diff changeset	918 password = params.password;
56abe6a8e761 mod_storage_sql: Create a new table to hold normalized database parameters (fixes #636) Kim Alvefur <zash@zash.se> parents: 7751 diff changeset	919 host = params.host;
7753 c276d72d4e17 mod_storage_sql: Include missing parameter table key 'port' Kim Alvefur <zash@zash.se> parents: 7752 diff changeset	920 port = params.port;
7752 56abe6a8e761 mod_storage_sql: Create a new table to hold normalized database parameters (fixes #636) Kim Alvefur <zash@zash.se> parents: 7751 diff changeset	921 };
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	922 end
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	923
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	924 function module.load()
7170 fb37aece3252 mod_storage_sql: Share SQL connections with same parameters across VirtualHosts (fixes #576) Kim Alvefur <zash@zash.se> parents: 7169 diff changeset	925 local engines = module:shared("/*/sql/connections");
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	926 local params = normalize_params(module:get_option("sql", default_params));
13832 5973a5d22ab5 mod_storage_sql: Delay showing SQL library error until attempted load Kim Alvefur <zash@zash.se> parents: 13784 diff changeset	927 local sql = get_sql_lib(params.driver);
10793 9ce67da45e54 mod_storage_sql: Log database connection parameters when creating engine Kim Alvefur <zash@zash.se> parents: 10701 diff changeset	928 local db_uri = sql.db2uri(params);
9ce67da45e54 mod_storage_sql: Log database connection parameters when creating engine Kim Alvefur <zash@zash.se> parents: 10701 diff changeset	929 engine = engines[db_uri];
7170 fb37aece3252 mod_storage_sql: Share SQL connections with same parameters across VirtualHosts (fixes #576) Kim Alvefur <zash@zash.se> parents: 7169 diff changeset	930 if not engine then
10793 9ce67da45e54 mod_storage_sql: Log database connection parameters when creating engine Kim Alvefur <zash@zash.se> parents: 10701 diff changeset	931 module:log("debug", "Creating new engine %s", db_uri);
8034 149553feb04e mod_storage_sql: Pass SQL engine as an argument to upgrade and table creator functions Kim Alvefur <zash@zash.se> parents: 8033 diff changeset	932 engine = sql:create_engine(params, function (engine) -- luacheck: ignore 431/engine
13229 bb7177efbf41 plugins: Use boolean config method in some places Kim Alvefur <zash@zash.se> parents: 13223 diff changeset	933 if module:get_option_boolean("sql_manage_tables", true) then
7170 fb37aece3252 mod_storage_sql: Share SQL connections with same parameters across VirtualHosts (fixes #576) Kim Alvefur <zash@zash.se> parents: 7169 diff changeset	934 -- Automatically create table, ignore failure (table probably already exists)
fb37aece3252 mod_storage_sql: Share SQL connections with same parameters across VirtualHosts (fixes #576) Kim Alvefur <zash@zash.se> parents: 7169 diff changeset	935 -- FIXME: we should check in information_schema, etc.
8034 149553feb04e mod_storage_sql: Pass SQL engine as an argument to upgrade and table creator functions Kim Alvefur <zash@zash.se> parents: 8033 diff changeset	936 create_table(engine);
7170 fb37aece3252 mod_storage_sql: Share SQL connections with same parameters across VirtualHosts (fixes #576) Kim Alvefur <zash@zash.se> parents: 7169 diff changeset	937 -- Check whether the table needs upgrading
8034 149553feb04e mod_storage_sql: Pass SQL engine as an argument to upgrade and table creator functions Kim Alvefur <zash@zash.se> parents: 8033 diff changeset	938 if upgrade_table(engine, params, false) then
7170 fb37aece3252 mod_storage_sql: Share SQL connections with same parameters across VirtualHosts (fixes #576) Kim Alvefur <zash@zash.se> parents: 7169 diff changeset	939 module:log("error", "Old database format detected. Please run: prosodyctl mod_%s upgrade", module.name);
fb37aece3252 mod_storage_sql: Share SQL connections with same parameters across VirtualHosts (fixes #576) Kim Alvefur <zash@zash.se> parents: 7169 diff changeset	940 return false, "database upgrade needed";
fb37aece3252 mod_storage_sql: Share SQL connections with same parameters across VirtualHosts (fixes #576) Kim Alvefur <zash@zash.se> parents: 7169 diff changeset	941 end
10652 0c00274528a4 mod_storage_sql: Fix check for deletion limits (fixes #1494) Kim Alvefur <zash@zash.se> parents: 10012 diff changeset	942 end
10655 ee6c12264420 mod_storage_sql: Move update limit check back one step, fixes traceback Kim Alvefur <zash@zash.se> parents: 10652 diff changeset	943 if engine.params.driver == "SQLite3" then
13148 c6f46f33cecf mod_storage_sql: Record all SQLite3 compile options for potential use Kim Alvefur <zash@zash.se> parents: 13144 diff changeset	944 local compile_options = {}
10655 ee6c12264420 mod_storage_sql: Move update limit check back one step, fixes traceback Kim Alvefur <zash@zash.se> parents: 10652 diff changeset	945 for row in engine:select("PRAGMA compile_options") do
13148 c6f46f33cecf mod_storage_sql: Record all SQLite3 compile options for potential use Kim Alvefur <zash@zash.se> parents: 13144 diff changeset	946 local option = row[1]:lower();
c6f46f33cecf mod_storage_sql: Record all SQLite3 compile options for potential use Kim Alvefur <zash@zash.se> parents: 13144 diff changeset	947 local opt, val = option:match("^([^=]+)=(.*)$");
c6f46f33cecf mod_storage_sql: Record all SQLite3 compile options for potential use Kim Alvefur <zash@zash.se> parents: 13144 diff changeset	948 compile_options[opt or option] = tonumber(val) or val or true;
10012 acf4a7bfb6aa mod_storage_sql: Handle SQLite DELETE with LIMIT being optional (fixes #1359) Kim Alvefur <zash@zash.se> parents: 10011 diff changeset	949 end
13633 6b84d11aa09b mod_storage_sql: Detect SQLite3 without UPSERT (or SQLCipher 3.x) Kim Alvefur <zash@zash.se> parents: 13611 diff changeset	950 -- COMPAT Need to check SQLite3 version because SQLCipher 3.x was based on SQLite3 prior to 3.24.0 when UPSERT was introduced
6b84d11aa09b mod_storage_sql: Detect SQLite3 without UPSERT (or SQLCipher 3.x) Kim Alvefur <zash@zash.se> parents: 13611 diff changeset	951 for row in engine:select("SELECT sqlite_version()") do
6b84d11aa09b mod_storage_sql: Detect SQLite3 without UPSERT (or SQLCipher 3.x) Kim Alvefur <zash@zash.se> parents: 13611 diff changeset	952 local version = {};
6b84d11aa09b mod_storage_sql: Detect SQLite3 without UPSERT (or SQLCipher 3.x) Kim Alvefur <zash@zash.se> parents: 13611 diff changeset	953 for n in row[1]:gmatch("%d+") do
6b84d11aa09b mod_storage_sql: Detect SQLite3 without UPSERT (or SQLCipher 3.x) Kim Alvefur <zash@zash.se> parents: 13611 diff changeset	954 table.insert(version, tonumber(n));
6b84d11aa09b mod_storage_sql: Detect SQLite3 without UPSERT (or SQLCipher 3.x) Kim Alvefur <zash@zash.se> parents: 13611 diff changeset	955 end
6b84d11aa09b mod_storage_sql: Detect SQLite3 without UPSERT (or SQLCipher 3.x) Kim Alvefur <zash@zash.se> parents: 13611 diff changeset	956 engine.sqlite_version = version;
6b84d11aa09b mod_storage_sql: Detect SQLite3 without UPSERT (or SQLCipher 3.x) Kim Alvefur <zash@zash.se> parents: 13611 diff changeset	957 end
13148 c6f46f33cecf mod_storage_sql: Record all SQLite3 compile options for potential use Kim Alvefur <zash@zash.se> parents: 13144 diff changeset	958 engine.sqlite_compile_options = compile_options;
13149 0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	959
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	960 local journal_mode = "delete";
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	961 for row in engine:select[[PRAGMA journal_mode;]] do
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	962 journal_mode = row[1];
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	963 end
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	964
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	965 -- Note: These things can't be changed with in a transaction. LuaDBI
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	966 -- opens a transaction automatically for every statement(?), so this
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	967 -- will not work there.
13222 cb6a17eaa0e6 mod_storage_sql: Use config enum for 'sqlite_tune' Kim Alvefur <zash@zash.se> parents: 13221 diff changeset	968 local tune = module:get_option_enum("sqlite_tune", "default", "normal", "fast", "safe");
13149 0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	969 if tune == "normal" then
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	970 if journal_mode ~= "wal" then
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	971 engine:execute("PRAGMA journal_mode=WAL;");
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	972 end
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	973 engine:execute("PRAGMA auto_vacuum=FULL;");
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	974 engine:execute("PRAGMA synchronous=NORMAL;")
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	975 elseif tune == "fast" then
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	976 if journal_mode ~= "wal" then
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	977 engine:execute("PRAGMA journal_mode=WAL;");
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	978 end
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	979 if compile_options.secure_delete then
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	980 engine:execute("PRAGMA secure_delete=FAST;");
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	981 end
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	982 engine:execute("PRAGMA synchronous=OFF;")
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	983 engine:execute("PRAGMA fullfsync=0;")
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	984 elseif tune == "safe" then
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	985 if journal_mode ~= "delete" then
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	986 engine:execute("PRAGMA journal_mode=DELETE;");
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	987 end
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	988 engine:execute("PRAGMA synchronous=EXTRA;")
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	989 engine:execute("PRAGMA fullfsync=1;")
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	990 end
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	991
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	992 for row in engine:select[[PRAGMA journal_mode;]] do
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	993 journal_mode = row[1];
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	994 end
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	995
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	996 module:log("debug", "SQLite3 database %q operating with journal_mode=%s", engine.params.database, journal_mode);
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	997 end
12872 a20923f7d5fd mod_storage_sql: Record connection to database as module status Kim Alvefur <zash@zash.se> parents: 12846 diff changeset	998 module:set_status("info", "Connected to " .. engine.params.driver);
12873 7f4f834fae79 mod_storage_sql: Silence luacheck warning Kim Alvefur <zash@zash.se> parents: 12872 diff changeset	999 end, function (engine) -- luacheck: ignore 431/engine
12872 a20923f7d5fd mod_storage_sql: Record connection to database as module status Kim Alvefur <zash@zash.se> parents: 12846 diff changeset	1000 module:set_status("error", "Disconnected from " .. engine.params.driver);
10655 ee6c12264420 mod_storage_sql: Move update limit check back one step, fixes traceback Kim Alvefur <zash@zash.se> parents: 10652 diff changeset	1001 end);
7170 fb37aece3252 mod_storage_sql: Share SQL connections with same parameters across VirtualHosts (fixes #576) Kim Alvefur <zash@zash.se> parents: 7169 diff changeset	1002 engines[sql.db2uri(params)] = engine;
12872 a20923f7d5fd mod_storage_sql: Record connection to database as module status Kim Alvefur <zash@zash.se> parents: 12846 diff changeset	1003 else
a20923f7d5fd mod_storage_sql: Record connection to database as module status Kim Alvefur <zash@zash.se> parents: 12846 diff changeset	1004 module:set_status("info", "Using existing engine");
7170 fb37aece3252 mod_storage_sql: Share SQL connections with same parameters across VirtualHosts (fixes #576) Kim Alvefur <zash@zash.se> parents: 7169 diff changeset	1005 end
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	1006
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	1007 module:provides("storage", driver);
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	1008 end
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	1009
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	1010 function module.command(arg)
12977 74b9e05af71e plugins: Prefix module imports with prosody namespace Kim Alvefur <zash@zash.se> parents: 12875 diff changeset	1011 local config = require "prosody.core.configmanager";
74b9e05af71e plugins: Prefix module imports with prosody namespace Kim Alvefur <zash@zash.se> parents: 12875 diff changeset	1012 local hi = require "prosody.util.human.io";
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	1013 local command = table.remove(arg, 1);
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	1014 if command == "upgrade" then
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	1015 -- We need to find every unique dburi in the config
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	1016 local uris = {};
8037 5a802653d50d mod_storage_sql: Ignore name clash [luacheck] Kim Alvefur <zash@zash.se> parents: 8036 diff changeset	1017 for host in pairs(prosody.hosts) do -- luacheck: ignore 431/host
7757 437fb77e5ded mod_storage_sql: Normalize parameters for upgrade command Kim Alvefur <zash@zash.se> parents: 7756 diff changeset	1018 local params = normalize_params(config.get(host, "sql") or default_params);
13832 5973a5d22ab5 mod_storage_sql: Delay showing SQL library error until attempted load Kim Alvefur <zash@zash.se> parents: 13784 diff changeset	1019 local sql = get_sql_lib(engine.params.driver);
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	1020 uris[sql.db2uri(params)] = params;
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	1021 end
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	1022 print("We will check and upgrade the following databases:\n");
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	1023 for _, params in pairs(uris) do
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	1024 print("", "["..params.driver.."] "..params.database..(params.host and " on "..params.host or ""));
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	1025 end
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	1026 print("");
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	1027 print("Ensure you have working backups of the above databases before continuing! ");
13634 7209769e88bc mod_storage_sql: Don't treat Enter or Ctrl-C as yes in upgrade command Kim Alvefur <zash@zash.se> parents: 13633 diff changeset	1028 if false == hi.show_yesno("Continue with the database upgrade? [yN]") then
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	1029 print("Ok, no upgrade. But you do have backups, don't you? ...don't you?? :-)");
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	1030 return;
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	1031 end
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	1032 -- Upgrade each one
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	1033 for _, params in pairs(uris) do
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	1034 print("Checking "..params.database.."...");
13832 5973a5d22ab5 mod_storage_sql: Delay showing SQL library error until attempted load Kim Alvefur <zash@zash.se> parents: 13784 diff changeset	1035 local sql = get_sql_lib(params.driver);
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	1036 engine = sql:create_engine(params);
8034 149553feb04e mod_storage_sql: Pass SQL engine as an argument to upgrade and table creator functions Kim Alvefur <zash@zash.se> parents: 8033 diff changeset	1037 upgrade_table(engine, params, true);
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	1038 end
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	1039 print("All done!");
7755 19aa9b1ef9e8 mod_storage_sql: Don't say 'Unknown command' if no command was given (fixes attempt to concatenate nil) Kim Alvefur <zash@zash.se> parents: 7753 diff changeset	1040 elseif command then
19aa9b1ef9e8 mod_storage_sql: Don't say 'Unknown command' if no command was given (fixes attempt to concatenate nil) Kim Alvefur <zash@zash.se> parents: 7753 diff changeset	1041 print("Unknown command: "..command);
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	1042 else
7756 274d0e57cc60 mod_storage_sql: List available commands if no commands given (currenly only one available) Kim Alvefur <zash@zash.se> parents: 7755 diff changeset	1043 print("Available commands:");
274d0e57cc60 mod_storage_sql: List available commands if no commands given (currenly only one available) Kim Alvefur <zash@zash.se> parents: 7755 diff changeset	1044 print("","upgrade - Perform database upgrade");
6951 99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	1045 end
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	1046 end

prosody

5973a5d22ab5

plugins/mod_storage_sql.lua

Software / code / prosody

Annotate

plugins/mod_storage_sql.lua @ 13832:5973a5d22ab5 13.0

Software `/` code `/` prosody