Software `/` code `/` prosody

Annotate

plugins/mod_storage_sql.lua @ 13833:497efa2cbf2c 13.0

mod_storage_sql: Add shell command to create tables and indices (again) This is meant as a way to diagnose e.g. issues creating indices. It would have been nice to capture e.g. PostgreSQL notices, but LuaDBI would need support for this first, see https://github.com/mwild1/luadbi/issues/62

author	Kim Alvefur <zash@zash.se>
date	Wed, 09 Apr 2025 15:06:48 +0200
parent	13832:5973a5d22ab5
child	13836:c600794cafb6

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
13833 497efa2cbf2c mod_storage_sql: Add shell command to create tables and indices (again) Kim Alvefur <zash@zash.se> parents: 13832 diff changeset	900 if not indices["prosody_unique_index"] then
497efa2cbf2c mod_storage_sql: Add shell command to create tables and indices (again) Kim Alvefur <zash@zash.se> parents: 13832 diff changeset	901 module:log("error", "New index \"prosody_unique_index\" does not exist!");
497efa2cbf2c mod_storage_sql: Add shell command to create tables and indices (again) Kim Alvefur <zash@zash.se> parents: 13832 diff changeset	902 return false;
497efa2cbf2c mod_storage_sql: Add shell command to create tables and indices (again) Kim Alvefur <zash@zash.se> parents: 13832 diff changeset	903 end
13780 40185fad7505 mod_storage_sql: Fix indentation Kim Alvefur <zash@zash.se> parents: 13661 diff changeset	904 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	905 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	906 end
685403a6fee1 mod_storage_sql: Add method for removing all data belonging to a user Kim Alvefur <zash@zash.se> parents: 5035 diff changeset	907
8035 58d6c2ab3d16 mod_storage_sql: Ignore name clash [luacheck] Kim Alvefur <zash@zash.se> parents: 8034 diff changeset	908 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	909 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	910 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	911 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	912 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	913 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	914
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	915 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	916 return {
8036 7ebaefc88b3d mod_storage_sql: Split long lines [luacheck] Kim Alvefur <zash@zash.se> parents: 8035 diff changeset	917 driver = assert(params.driver,
7ebaefc88b3d mod_storage_sql: Split long lines [luacheck] Kim Alvefur <zash@zash.se> parents: 8035 diff changeset	918 "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	919 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	920 "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	921 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	922 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	923 host = params.host;
7753 c276d72d4e17 mod_storage_sql: Include missing parameter table key 'port' Kim Alvefur <zash@zash.se> parents: 7752 diff changeset	924 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	925 };
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 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	927
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	928 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	929 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	930 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	931 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	932 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	933 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	934 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	935 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	936 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	937 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	938 -- 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	939 -- 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	940 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	941 -- 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	942 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	943 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	944 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	945 end
10652 0c00274528a4 mod_storage_sql: Fix check for deletion limits (fixes #1494) Kim Alvefur <zash@zash.se> parents: 10012 diff changeset	946 end
10655 ee6c12264420 mod_storage_sql: Move update limit check back one step, fixes traceback Kim Alvefur <zash@zash.se> parents: 10652 diff changeset	947 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	948 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	949 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	950 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	951 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	952 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	953 end
13633 6b84d11aa09b mod_storage_sql: Detect SQLite3 without UPSERT (or SQLCipher 3.x) Kim Alvefur <zash@zash.se> parents: 13611 diff changeset	954 -- 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	955 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	956 local version = {};
6b84d11aa09b mod_storage_sql: Detect SQLite3 without UPSERT (or SQLCipher 3.x) Kim Alvefur <zash@zash.se> parents: 13611 diff changeset	957 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	958 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	959 end
6b84d11aa09b mod_storage_sql: Detect SQLite3 without UPSERT (or SQLCipher 3.x) Kim Alvefur <zash@zash.se> parents: 13611 diff changeset	960 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	961 end
13148 c6f46f33cecf mod_storage_sql: Record all SQLite3 compile options for potential use Kim Alvefur <zash@zash.se> parents: 13144 diff changeset	962 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	963
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	964 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	965 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	966 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	967 end
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	968
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	969 -- 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	970 -- 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	971 -- will not work there.
13222 cb6a17eaa0e6 mod_storage_sql: Use config enum for 'sqlite_tune' Kim Alvefur <zash@zash.se> parents: 13221 diff changeset	972 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	973 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	974 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	975 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	976 end
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 auto_vacuum=FULL;");
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	978 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	979 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	980 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	981 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	982 end
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	983 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	984 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	985 end
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 synchronous=OFF;")
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	987 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	988 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	989 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	990 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	991 end
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	992 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	993 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	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 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	997 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	998 end
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	999
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	1000 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	1001 end
12872 a20923f7d5fd mod_storage_sql: Record connection to database as module status Kim Alvefur <zash@zash.se> parents: 12846 diff changeset	1002 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	1003 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	1004 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	1005 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	1006 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	1007 else
a20923f7d5fd mod_storage_sql: Record connection to database as module status Kim Alvefur <zash@zash.se> parents: 12846 diff changeset	1008 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	1009 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	1010
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	1011 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	1012 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	1013
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 function module.command(arg)
12977 74b9e05af71e plugins: Prefix module imports with prosody namespace Kim Alvefur <zash@zash.se> parents: 12875 diff changeset	1015 local config = require "prosody.core.configmanager";
74b9e05af71e plugins: Prefix module imports with prosody namespace Kim Alvefur <zash@zash.se> parents: 12875 diff changeset	1016 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	1017 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	1018 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	1019 -- 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	1020 local uris = {};
8037 5a802653d50d mod_storage_sql: Ignore name clash [luacheck] Kim Alvefur <zash@zash.se> parents: 8036 diff changeset	1021 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	1022 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	1023 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	1024 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	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("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	1027 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	1028 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	1029 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	1030 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	1031 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	1032 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	1033 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	1034 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	1035 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	1036 -- 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	1037 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	1038 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	1039 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	1040 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	1041 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	1042 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	1043 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	1044 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	1045 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	1046 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	1047 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	1048 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	1049 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	1050 end
13833 497efa2cbf2c mod_storage_sql: Add shell command to create tables and indices (again) Kim Alvefur <zash@zash.se> parents: 13832 diff changeset	1051
497efa2cbf2c mod_storage_sql: Add shell command to create tables and indices (again) Kim Alvefur <zash@zash.se> parents: 13832 diff changeset	1052 module:add_item("shell-command", {
497efa2cbf2c mod_storage_sql: Add shell command to create tables and indices (again) Kim Alvefur <zash@zash.se> parents: 13832 diff changeset	1053 section = "sql";
497efa2cbf2c mod_storage_sql: Add shell command to create tables and indices (again) Kim Alvefur <zash@zash.se> parents: 13832 diff changeset	1054 section_desc = "SQL management commands";
497efa2cbf2c mod_storage_sql: Add shell command to create tables and indices (again) Kim Alvefur <zash@zash.se> parents: 13832 diff changeset	1055 name = "create";
497efa2cbf2c mod_storage_sql: Add shell command to create tables and indices (again) Kim Alvefur <zash@zash.se> parents: 13832 diff changeset	1056 desc = "Create the tables and indices used by Prosody (again)";
497efa2cbf2c mod_storage_sql: Add shell command to create tables and indices (again) Kim Alvefur <zash@zash.se> parents: 13832 diff changeset	1057 args = { { name = "host"; type = "string" } };
497efa2cbf2c mod_storage_sql: Add shell command to create tables and indices (again) Kim Alvefur <zash@zash.se> parents: 13832 diff changeset	1058 host_selector = "host";
497efa2cbf2c mod_storage_sql: Add shell command to create tables and indices (again) Kim Alvefur <zash@zash.se> parents: 13832 diff changeset	1059 handler = function(shell, _host)
497efa2cbf2c mod_storage_sql: Add shell command to create tables and indices (again) Kim Alvefur <zash@zash.se> parents: 13832 diff changeset	1060 local logger = require "prosody.util.logger";
497efa2cbf2c mod_storage_sql: Add shell command to create tables and indices (again) Kim Alvefur <zash@zash.se> parents: 13832 diff changeset	1061 local writing = false;
497efa2cbf2c mod_storage_sql: Add shell command to create tables and indices (again) Kim Alvefur <zash@zash.se> parents: 13832 diff changeset	1062 local sink = logger.add_simple_sink(function (source, level, message)
497efa2cbf2c mod_storage_sql: Add shell command to create tables and indices (again) Kim Alvefur <zash@zash.se> parents: 13832 diff changeset	1063 local print = shell.session.print;
497efa2cbf2c mod_storage_sql: Add shell command to create tables and indices (again) Kim Alvefur <zash@zash.se> parents: 13832 diff changeset	1064 if writing or source ~= "sql" then return; end
497efa2cbf2c mod_storage_sql: Add shell command to create tables and indices (again) Kim Alvefur <zash@zash.se> parents: 13832 diff changeset	1065 writing = true;
497efa2cbf2c mod_storage_sql: Add shell command to create tables and indices (again) Kim Alvefur <zash@zash.se> parents: 13832 diff changeset	1066 print(message);
497efa2cbf2c mod_storage_sql: Add shell command to create tables and indices (again) Kim Alvefur <zash@zash.se> parents: 13832 diff changeset	1067 writing = false;
497efa2cbf2c mod_storage_sql: Add shell command to create tables and indices (again) Kim Alvefur <zash@zash.se> parents: 13832 diff changeset	1068 end);
497efa2cbf2c mod_storage_sql: Add shell command to create tables and indices (again) Kim Alvefur <zash@zash.se> parents: 13832 diff changeset	1069
497efa2cbf2c mod_storage_sql: Add shell command to create tables and indices (again) Kim Alvefur <zash@zash.se> parents: 13832 diff changeset	1070 local debug_enabled = engine._debug;
497efa2cbf2c mod_storage_sql: Add shell command to create tables and indices (again) Kim Alvefur <zash@zash.se> parents: 13832 diff changeset	1071 engine:debug(true);
497efa2cbf2c mod_storage_sql: Add shell command to create tables and indices (again) Kim Alvefur <zash@zash.se> parents: 13832 diff changeset	1072 create_table(engine);
497efa2cbf2c mod_storage_sql: Add shell command to create tables and indices (again) Kim Alvefur <zash@zash.se> parents: 13832 diff changeset	1073 engine:debug(debug_enabled);
497efa2cbf2c mod_storage_sql: Add shell command to create tables and indices (again) Kim Alvefur <zash@zash.se> parents: 13832 diff changeset	1074
497efa2cbf2c mod_storage_sql: Add shell command to create tables and indices (again) Kim Alvefur <zash@zash.se> parents: 13832 diff changeset	1075 if not logger.remove_sink(sink) then
497efa2cbf2c mod_storage_sql: Add shell command to create tables and indices (again) Kim Alvefur <zash@zash.se> parents: 13832 diff changeset	1076 module:log("warn", "Unable to remove log sink");
497efa2cbf2c mod_storage_sql: Add shell command to create tables and indices (again) Kim Alvefur <zash@zash.se> parents: 13832 diff changeset	1077 end
497efa2cbf2c mod_storage_sql: Add shell command to create tables and indices (again) Kim Alvefur <zash@zash.se> parents: 13832 diff changeset	1078 end;
497efa2cbf2c mod_storage_sql: Add shell command to create tables and indices (again) Kim Alvefur <zash@zash.se> parents: 13832 diff changeset	1079 })

prosody

497efa2cbf2c

plugins/mod_storage_sql.lua

Software / code / prosody

Annotate

plugins/mod_storage_sql.lua @ 13833:497efa2cbf2c 13.0

Software `/` code `/` prosody