Software `/` code `/` prosody

Annotate

plugins/mod_storage_sql.lua @ 13252:84c7779618b6

core.portmanager: Join strings broken into multiple lines Improves readability. Reduces line count. What's not to like? The code style and luacheck rules allows longer lines, and these strings aren't long enough to need breaking into multiple lines like this.

author	Kim Alvefur <zash@zash.se>
date	Sat, 29 Jul 2023 02:04:24 +0200
parent	13242:0d3881bf29a8

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 then
0d3881bf29a8 mod_storage_sql: Spell out missing dependencies Kim Alvefur <zash@zash.se> parents: 13241 diff changeset	17 module:log("debug", "Could not load LuaDBI, error was: %s", dbisql)
0d3881bf29a8 mod_storage_sql: Spell out missing dependencies Kim Alvefur <zash@zash.se> parents: 13241 diff changeset	18 dbisql = nil;
0d3881bf29a8 mod_storage_sql: Spell out missing dependencies Kim Alvefur <zash@zash.se> parents: 13241 diff changeset	19 end
0d3881bf29a8 mod_storage_sql: Spell out missing dependencies Kim Alvefur <zash@zash.se> parents: 13241 diff changeset	20 if not have_sqlite then
0d3881bf29a8 mod_storage_sql: Spell out missing dependencies Kim Alvefur <zash@zash.se> parents: 13241 diff changeset	21 module:log("debug", "Could not load LuaSQLite3, error was: %s", sqlite)
0d3881bf29a8 mod_storage_sql: Spell out missing dependencies Kim Alvefur <zash@zash.se> parents: 13241 diff changeset	22 sqlite = nil;
0d3881bf29a8 mod_storage_sql: Spell out missing dependencies Kim Alvefur <zash@zash.se> parents: 13241 diff changeset	23 end
0d3881bf29a8 mod_storage_sql: Spell out missing dependencies Kim Alvefur <zash@zash.se> parents: 13241 diff changeset	24 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	25 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	26 module:log("error", "Please install at least one of LuaDBI and LuaSQLite3. See https://prosody.im/doc/depends");
0d3881bf29a8 mod_storage_sql: Spell out missing dependencies Kim Alvefur <zash@zash.se> parents: 13241 diff changeset	27 error("No SQL library available")
0d3881bf29a8 mod_storage_sql: Spell out missing dependencies Kim Alvefur <zash@zash.se> parents: 13241 diff changeset	28 end
0d3881bf29a8 mod_storage_sql: Spell out missing dependencies Kim Alvefur <zash@zash.se> parents: 13241 diff changeset	29
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	30 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	31 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	32 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	33 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	34 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	35 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	36 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	37 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	38 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	39 end
08560575762f mod_storage_sql: Reconnect in all cases, and rollback on error. Waqas Hussain <waqas20@gmail.com> parents: 4101 diff changeset	40
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	41 local default_params = { driver = "SQLite3" };
5776 bd0ff8ae98a8 Remove all trailing whitespace Florian Zeitz <florob@babelmonkeys.de> parents: 5209 diff changeset	42
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	43 local engine;
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	44
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	45 local function serialize(value)
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	46 local t = type(value);
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	47 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	48 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	49 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	50 return "xml", tostring(value);
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	51 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	52 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	53 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	54 return nil, err;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	55 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	56 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	57 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	58 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	59 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	60 elseif t == "boolean" then
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	61 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	62 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	63 return nil, "invalid-boolean";
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors Kim Alvefur <zash@zash.se> parents: 8480 diff changeset	64 elseif t == "number" then
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors Kim Alvefur <zash@zash.se> parents: 8480 diff changeset	65 value = tonumber(value);
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors Kim Alvefur <zash@zash.se> parents: 8480 diff changeset	66 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	67 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	68 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	69 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	70 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	71 return xml_parse(value);
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	72 end
8537 d7f31badd359 mod_storage_sql: Catch and report value deserialization errors Kim Alvefur <zash@zash.se> parents: 8480 diff changeset	73 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	74 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	75
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	76 local host = module.host;
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	77
13241 0419de4e4db1 mod_storage_sql: Pass variables as arguments instead of upvalues Kim Alvefur <zash@zash.se> parents: 13229 diff changeset	78 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	79 local haveany;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	80 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	81 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	82 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	83 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	84 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	85 ]]
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	86 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	87 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	88 local k = row[1];
8537 d7f31badd359 mod_storage_sql: Catch and report value deserialization errors Kim Alvefur <zash@zash.se> parents: 8480 diff changeset	89 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	90 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	91 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	92 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	93 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	94 result[a] = b;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	95 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	96 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	97 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	98 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	99 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	100 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	101 end
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	102 end
13241 0419de4e4db1 mod_storage_sql: Pass variables as arguments instead of upvalues Kim Alvefur <zash@zash.se> parents: 13229 diff changeset	103 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	104 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	105 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	106 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	107 ]];
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	108 engine:delete(delete_sql, host, user or "", store);
5776 bd0ff8ae98a8 Remove all trailing whitespace Florian Zeitz <florob@babelmonkeys.de> parents: 5209 diff changeset	109
8033 6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	110 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	111 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	112 ("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	113 VALUES (?,?,?,?,?,?);
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	114 ]]
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	115 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	116 local extradata = {};
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	117 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	118 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	119 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	120 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	121 else
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	122 extradata[key] = value;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	123 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	124 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	125 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	126 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	127 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	128 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	129 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	130 return true;
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	131 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	132
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	133 --- 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	134
4007 062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback. Waqas Hussain <waqas20@gmail.com> parents: 4004 diff changeset	135 local keyval_store = {};
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback. Waqas Hussain <waqas20@gmail.com> parents: 4004 diff changeset	136 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	137 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	138 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	139 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	140 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	141 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	142 end
6953 b9276d677e76 mod_storage_sql: Whitespace fix (backported from trunk) Matthew Wild <mwild1@gmail.com> parents: 6951 diff changeset	143 return result;
4007 062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback. Waqas Hussain <waqas20@gmail.com> parents: 4004 diff changeset	144 end
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback. Waqas Hussain <waqas20@gmail.com> parents: 4004 diff changeset	145 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	146 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	147 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	148 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	149 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	150 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	151 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	152 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	153 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	154 ]];
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	155 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	156 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	157 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	158 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	159 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	160
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	161 --- 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	162
13213 50324f66ca2a plugins: Use integer config API with interval specification where sensible Kim Alvefur <zash@zash.se> parents: 13190 diff changeset	163 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	164 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	165
10993 b5e7f4d533e2 mod_storage_sql: Measure hits/misses on the item count cache Kim Alvefur <zash@zash.se> parents: 10925 diff changeset	166 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	167 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	168
10033 ca8333d1a7fe mod_storage_sql: Ignore shadowed error variable [luacheck] Kim Alvefur <zash@zash.se> parents: 10032 diff changeset	169 -- 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	170 local map_store = {};
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk) Matthew Wild <mwild1@gmail.com> parents: 6953 diff changeset	171 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	172 map_store.remove = {};
6954 400badaf1fc7 mod_storage_sql: Add map store (backported from trunk) Matthew Wild <mwild1@gmail.com> parents: 6953 diff changeset	173 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	174 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	175 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	176 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	177 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	178 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	179 LIMIT 1
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	180 ]];
8537 d7f31badd359 mod_storage_sql: Catch and report value deserialization errors Kim Alvefur <zash@zash.se> parents: 8480 diff changeset	181 local data, err;
6954 400badaf1fc7 mod_storage_sql: Add map store (backported from trunk) Matthew Wild <mwild1@gmail.com> parents: 6953 diff changeset	182 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	183 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	184 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	185 assert(data ~= nil, err);
6954 400badaf1fc7 mod_storage_sql: Add map store (backported from trunk) Matthew Wild <mwild1@gmail.com> parents: 6953 diff changeset	186 end
7274 e0727512bb99 mod_storage_sql: Allow loops over results to end on their own Kim Alvefur <zash@zash.se> parents: 7273 diff changeset	187 return data;
6954 400badaf1fc7 mod_storage_sql: Add map store (backported from trunk) Matthew Wild <mwild1@gmail.com> parents: 6953 diff changeset	188 else
8033 6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	189 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	190 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	191 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	192 end
7274 e0727512bb99 mod_storage_sql: Allow loops over results to end on their own Kim Alvefur <zash@zash.se> parents: 7273 diff changeset	193 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	194 end
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk) Matthew Wild <mwild1@gmail.com> parents: 6953 diff changeset	195 end);
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk) Matthew Wild <mwild1@gmail.com> parents: 6953 diff changeset	196 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	197 return result;
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk) Matthew Wild <mwild1@gmail.com> parents: 6953 diff changeset	198 end
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk) Matthew Wild <mwild1@gmail.com> parents: 6953 diff changeset	199 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	200 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	201 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	202 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	203 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	204 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	205 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	206 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	207 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	208 ]];
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	209 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	210 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	211 ("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	212 VALUES (?,?,?,?,?,?);
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	213 ]];
13143 3ec48555b773 mod_storage_sql: Add UPSERT support Kim Alvefur <zash@zash.se> parents: 12977 diff changeset	214 local upsert_sql = [[
3ec48555b773 mod_storage_sql: Add UPSERT support Kim Alvefur <zash@zash.se> parents: 12977 diff changeset	215 INSERT INTO "prosody"
3ec48555b773 mod_storage_sql: Add UPSERT support Kim Alvefur <zash@zash.se> parents: 12977 diff changeset	216 ("host","user","store","key","type","value")
3ec48555b773 mod_storage_sql: Add UPSERT support Kim Alvefur <zash@zash.se> parents: 12977 diff changeset	217 VALUES (?,?,?,?,?,?)
3ec48555b773 mod_storage_sql: Add UPSERT support Kim Alvefur <zash@zash.se> parents: 12977 diff changeset	218 ON CONFLICT ("host", "user","store", "key")
3ec48555b773 mod_storage_sql: Add UPSERT support Kim Alvefur <zash@zash.se> parents: 12977 diff changeset	219 DO UPDATE SET "type"=?, "value"=?;
3ec48555b773 mod_storage_sql: Add UPSERT support Kim Alvefur <zash@zash.se> parents: 12977 diff changeset	220 ]];
8033 6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	221 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	222 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	223 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	224 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	225 LIMIT 1;
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	226 ]];
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	227 for key, data in pairs(keydatas) do
13154 6cf5c215825d mod_storage_sql: Enable UPSERT with PostgreSQL Kim Alvefur <zash@zash.se> parents: 13151 diff changeset	228 if type(key) == "string" and key ~= "" and engine.params.driver ~= "MySQL" and data ~= self.remove then
13143 3ec48555b773 mod_storage_sql: Add UPSERT support Kim Alvefur <zash@zash.se> parents: 12977 diff changeset	229 local t, value = assert(serialize(data));
3ec48555b773 mod_storage_sql: Add UPSERT support Kim Alvefur <zash@zash.se> parents: 12977 diff changeset	230 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	231 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	232 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	233 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	234 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	235 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	236 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	237 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	238 else
8537 d7f31badd359 mod_storage_sql: Catch and report value deserialization errors Kim Alvefur <zash@zash.se> parents: 8480 diff changeset	239 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	240 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	241 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	242 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	243 end
8033 6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	244 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	245 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	246 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	247 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	248 end
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk) Matthew Wild <mwild1@gmail.com> parents: 6953 diff changeset	249 end
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk) Matthew Wild <mwild1@gmail.com> parents: 6953 diff changeset	250 return true;
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk) Matthew Wild <mwild1@gmail.com> parents: 6953 diff changeset	251 end);
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk) Matthew Wild <mwild1@gmail.com> parents: 6953 diff changeset	252 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	253 return result;
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk) Matthew Wild <mwild1@gmail.com> parents: 6953 diff changeset	254 end
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk) Matthew Wild <mwild1@gmail.com> parents: 6953 diff changeset	255
10680 19692fc5c106 storagemanager, mod_storage_sql: Rename methods to :get_all() and :delete_all() Matthew Wild <mwild1@gmail.com> parents: 10677 diff changeset	256 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	257 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	258 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	259 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	260 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	261 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	262 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	263 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	264 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	265 ]];
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests) Matthew Wild <mwild1@gmail.com> parents: 10656 diff changeset	266
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests) Matthew Wild <mwild1@gmail.com> parents: 10656 diff changeset	267 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	268 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	269 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	270 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	271 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	272 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	273 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	274 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	275 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	276
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 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	278
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 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	280 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	281 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	282 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	283
10680 19692fc5c106 storagemanager, mod_storage_sql: Rename methods to :get_all() and :delete_all() Matthew Wild <mwild1@gmail.com> parents: 10677 diff changeset	284 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	285 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	286 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	287 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	288 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	289 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	290 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	291 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	292 ]];
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 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	294 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	295 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	296 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	297 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	298 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
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	300 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	301 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	302 total = true;
9884 9751c17f5281 mod_storage_internal,_sql: Expose archive capabilities feature set Kim Alvefur <zash@zash.se> parents: 9883 diff changeset	303 quota = archive_item_limit;
9751c17f5281 mod_storage_internal,_sql: Expose archive capabilities feature set Kim Alvefur <zash@zash.se> parents: 9883 diff changeset	304 truncate = true;
11271 41a962b72a6e mod_storage_sql: Advertise support for id range query Kim Alvefur <zash@zash.se> parents: 10993 diff changeset	305 full_id_range = true;
11279 f225070e2b8a mod_storage_sql: Support query for set of IDs Kim Alvefur <zash@zash.se> parents: 11271 diff changeset	306 ids = true;
11967 0f2b5182e80b mod_storage_sql: Expose multi-user deletion capability Kim Alvefur <zash@zash.se> parents: 11625 diff changeset	307 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	308 };
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	309 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	310 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	311 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	312 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	313 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	314
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set Kim Alvefur <zash@zash.se> parents: 13149 diff changeset	315 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	316 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	317 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	318 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	319 local count_sql = [[
13158 cf2a907ac130 mod_storage_sql: Adjust indentation to align with surrounding code Kim Alvefur <zash@zash.se> parents: 13157 diff changeset	320 SELECT COUNT(*) FROM "prosodyarchive"
cf2a907ac130 mod_storage_sql: Adjust indentation to align with surrounding code Kim Alvefur <zash@zash.se> parents: 13157 diff changeset	321 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	322 ]];
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	323 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	324 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	325 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	326 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	327 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	328 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	329 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	330 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	331 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	332 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	333 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	334 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	335 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	336 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	337 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	338
9896 dafe1b544822 mod_storage_sql: No archive item limit by default Kim Alvefur <zash@zash.se> parents: 9893 diff changeset	339 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	340 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	341 return nil, "quota-limit";
dafe1b544822 mod_storage_sql: No archive item limit by default Kim Alvefur <zash@zash.se> parents: 9893 diff changeset	342 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	343 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	344
12631 9524bb7f3944 mod_storage_sql: Drop archive timestamp precision pending schema update Kim Alvefur <zash@zash.se> parents: 12600 diff changeset	345 -- 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	346 when = when or os.time();
2a9ce229efac mod_storage_sql: Allow higher precision timestamps in SQLite3 Kim Alvefur <zash@zash.se> parents: 13213 diff changeset	347 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	348 -- SQLite3 doesn't enforce types :)
2a9ce229efac mod_storage_sql: Allow higher precision timestamps in SQLite3 Kim Alvefur <zash@zash.se> parents: 13213 diff changeset	349 when = math.floor(when);
2a9ce229efac mod_storage_sql: Allow higher precision timestamps in SQLite3 Kim Alvefur <zash@zash.se> parents: 13213 diff changeset	350 end
8031 ef838b7f8f53 mod_storage_sql: Sensible defaults for 'when' and 'with' arguments Kim Alvefur <zash@zash.se> parents: 8029 diff changeset	351 with = with or "";
8032 aa9f198cb3c9 mod_storage_sql: Rename variables to avoid name clashes [luacheck] Kim Alvefur <zash@zash.se> parents: 8031 diff changeset	352 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	353 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	354 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	355 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	356 ]];
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	357 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	358 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	359 ("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	360 VALUES (?,?,?,?,?,?,?,?);
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	361 ]];
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	362 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	363 -- 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	364 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	365 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	366 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	367 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	368 else
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	369 key = uuid.generate();
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	370 end
8032 aa9f198cb3c9 mod_storage_sql: Rename variables to avoid name clashes [luacheck] Kim Alvefur <zash@zash.se> parents: 8031 diff changeset	371 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	372 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	373 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	374 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	375 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	376 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	377 end);
8032 aa9f198cb3c9 mod_storage_sql: Rename variables to avoid name clashes [luacheck] Kim Alvefur <zash@zash.se> parents: 8031 diff changeset	378 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	379 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	380 end
4007 062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback. Waqas Hussain <waqas20@gmail.com> parents: 4004 diff changeset	381
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	382 -- 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	383 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	384 -- 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	385 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	386 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	387 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	388 end
5776 bd0ff8ae98a8 Remove all trailing whitespace Florian Zeitz <florob@babelmonkeys.de> parents: 5209 diff changeset	389
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	390 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	391 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	392 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	393 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	394 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	395 where[#where+1] = "\"when\" <= ?"
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	396 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	397 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	398
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	399 -- 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	400 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	401 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	402 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	403 end
5776 bd0ff8ae98a8 Remove all trailing whitespace Florian Zeitz <florob@babelmonkeys.de> parents: 5209 diff changeset	404
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	405 -- 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	406 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	407 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	408 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	409 end
11279 f225070e2b8a mod_storage_sql: Support query for set of IDs Kim Alvefur <zash@zash.se> parents: 11271 diff changeset	410
f225070e2b8a mod_storage_sql: Support query for set of IDs Kim Alvefur <zash@zash.se> parents: 11271 diff changeset	411 -- Set of ids
f225070e2b8a mod_storage_sql: Support query for set of IDs Kim Alvefur <zash@zash.se> parents: 11271 diff changeset	412 if query.ids then
f225070e2b8a mod_storage_sql: Support query for set of IDs Kim Alvefur <zash@zash.se> parents: 11271 diff changeset	413 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	414 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	415 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	416 args[nargs+i] = id;
f225070e2b8a mod_storage_sql: Support query for set of IDs Kim Alvefur <zash@zash.se> parents: 11271 diff changeset	417 end
f225070e2b8a mod_storage_sql: Support query for set of IDs Kim Alvefur <zash@zash.se> parents: 11271 diff changeset	418 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	419 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	420 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	421 -- 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	422 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	423 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	424 FROM "prosodyarchive"
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325) Kim Alvefur <zash@zash.se> parents: 10017 diff changeset	425 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	426 LIMIT 1;
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325) Kim Alvefur <zash@zash.se> parents: 10017 diff changeset	427 ]];
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	428 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	429 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	430 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	431 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	432 end
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325) Kim Alvefur <zash@zash.se> parents: 10017 diff changeset	433 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	434 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	435 end
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325) Kim Alvefur <zash@zash.se> parents: 10017 diff changeset	436 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	437 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	438 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	439 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	440 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	441 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	442 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	443 end
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325) Kim Alvefur <zash@zash.se> parents: 10017 diff changeset	444 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	445 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	446 end
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325) Kim Alvefur <zash@zash.se> parents: 10017 diff changeset	447 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	448 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	449 end
10020 deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325) Kim Alvefur <zash@zash.se> parents: 10017 diff changeset	450 return true;
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	451 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	452
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	453 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	454 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	455 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	456 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	457 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	458 (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	459 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	460 -- 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	461 -- 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	462 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	463 else
0b350909da24 mod_storage_sql: Return cached total where it makes sense Kim Alvefur <zash@zash.se> parents: 11970 diff changeset	464 -- 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	465 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	466 end
10031 17c175ad65f9 mod_storage_sql: Correctly return item-not-found error Kim Alvefur <zash@zash.se> parents: 10020 diff changeset	467 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	468 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	469 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	470 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	471 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	472 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	473 ]];
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	474 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	475 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	476
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	477 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	478
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	479 -- Total matching
11971 0b350909da24 mod_storage_sql: Return cached total where it makes sense Kim Alvefur <zash@zash.se> parents: 11970 diff changeset	480 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	481
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	482 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	483 .. 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	484 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	485 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	486 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	487 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	488 end
11970 f0e78fa8f24c mod_storage_sql: Adjust handling of 'total' for multi-id queries Kim Alvefur <zash@zash.se> parents: 11969 diff changeset	489 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	490 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	491 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	492 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	493 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	494 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	495 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	496
10020 deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325) Kim Alvefur <zash@zash.se> parents: 10017 diff changeset	497 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	498 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	499
8033 6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck] Kim Alvefur <zash@zash.se> parents: 8032 diff changeset	500 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	501 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	502 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	503 end);
10031 17c175ad65f9 mod_storage_sql: Correctly return item-not-found error Kim Alvefur <zash@zash.se> parents: 10020 diff changeset	504 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	505 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	506 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	507 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	508 if row ~= nil then
8537 d7f31badd359 mod_storage_sql: Catch and report value deserialization errors Kim Alvefur <zash@zash.se> parents: 8480 diff changeset	509 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	510 assert(value ~= nil, err);
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors Kim Alvefur <zash@zash.se> parents: 8480 diff changeset	511 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	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, total;
4007 062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback. Waqas Hussain <waqas20@gmail.com> parents: 4004 diff changeset	514 end
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback. Waqas Hussain <waqas20@gmail.com> parents: 4004 diff changeset	515
11354 10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	516 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	517 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	518 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	519 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	520 return stanza, when, with;
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	521 end
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	522 return nil, "item-not-found";
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	523 end
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	524
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	525 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	526 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	527 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	528
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	529 local update_query = [[
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	530 UPDATE "prosodyarchive"
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	531 SET %s
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	532 WHERE %s
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	533 ]];
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	534 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	535 local setf = {};
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	536 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	537
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	538 if new_value then
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	539 table.insert(setf, '"type" = ?')
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	540 table.insert(setf, '"value" = ?')
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	541 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	542 table.insert(args, 1, t);
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	543 table.insert(args, 2, value);
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	544 end
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	545
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	546 if new_when then
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	547 table.insert(setf, 1, '"when" = ?')
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	548 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	549 end
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	550
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	551 if new_with then
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	552 table.insert(setf, 1, '"with" = ?')
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	553 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	554 end
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 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	557 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	558 end);
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	559 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	560 return result:affected() == 1;
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	561 end
10fba62332c5 mod_storage_sql: Implement map-like API for archives Kim Alvefur <zash@zash.se> parents: 11283 diff changeset	562
9904 bf061f5512f7 mod_storage_sql: Implement archive summary API Kim Alvefur <zash@zash.se> parents: 9896 diff changeset	563 function archive_store:summary(username, query)
bf061f5512f7 mod_storage_sql: Implement archive summary API Kim Alvefur <zash@zash.se> parents: 9896 diff changeset	564 query = query or {};
bf061f5512f7 mod_storage_sql: Implement archive summary API Kim Alvefur <zash@zash.se> parents: 9896 diff changeset	565 local user,store = username,self.store;
bf061f5512f7 mod_storage_sql: Implement archive summary API Kim Alvefur <zash@zash.se> parents: 9896 diff changeset	566 local ok, result = engine:transaction(function()
bf061f5512f7 mod_storage_sql: Implement archive summary API Kim Alvefur <zash@zash.se> parents: 9896 diff changeset	567 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	568 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	569 FROM "prosodyarchive"
bf061f5512f7 mod_storage_sql: Implement archive summary API Kim Alvefur <zash@zash.se> parents: 9896 diff changeset	570 WHERE %s
12599 5aafcf6a0730 mod_storage_sql: Fix summary API with Postgres (fixes #1766) Kim Alvefur <zash@zash.se> parents: 12592 diff changeset	571 GROUP BY "with";
9904 bf061f5512f7 mod_storage_sql: Implement archive summary API Kim Alvefur <zash@zash.se> parents: 9896 diff changeset	572 ]];
bf061f5512f7 mod_storage_sql: Implement archive summary API Kim Alvefur <zash@zash.se> parents: 9896 diff changeset	573 local args = { host, user or "", store, };
bf061f5512f7 mod_storage_sql: Implement archive summary API Kim Alvefur <zash@zash.se> parents: 9896 diff changeset	574 local where = { "\"host\" = ?", "\"user\" = ?", "\"store\" = ?", };
bf061f5512f7 mod_storage_sql: Implement archive summary API Kim Alvefur <zash@zash.se> parents: 9896 diff changeset	575
bf061f5512f7 mod_storage_sql: Implement archive summary API Kim Alvefur <zash@zash.se> parents: 9896 diff changeset	576 archive_where(query, args, where);
bf061f5512f7 mod_storage_sql: Implement archive summary API Kim Alvefur <zash@zash.se> parents: 9896 diff changeset	577
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	578 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	579
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	580 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	581 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	582 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	583
12599 5aafcf6a0730 mod_storage_sql: Fix summary API with Postgres (fixes #1766) Kim Alvefur <zash@zash.se> parents: 12592 diff changeset	584 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	585 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	586 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	587 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	588 local counts = {};
10222 51f145094648 mod_storage_: Also include timestmap of first message in :summary API Kim Alvefur <zash@zash.se>* parents: 10221 diff changeset	589 local earliest, latest = {}, {};
9904 bf061f5512f7 mod_storage_sql: Implement archive summary API Kim Alvefur <zash@zash.se> parents: 9896 diff changeset	590 for row in result do
bf061f5512f7 mod_storage_sql: Implement archive summary API Kim Alvefur <zash@zash.se> parents: 9896 diff changeset	591 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	592 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	593 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	594 latest[with] = row[4];
9904 bf061f5512f7 mod_storage_sql: Implement archive summary API Kim Alvefur <zash@zash.se> parents: 9896 diff changeset	595 end
10220 1e2b444acb72 mod_storage_: Tweak :summary API to allow future expansion with more fields Kim Alvefur <zash@zash.se>* parents: 10033 diff changeset	596 return {
1e2b444acb72 mod_storage_: Tweak :summary API to allow future expansion with more fields Kim Alvefur <zash@zash.se>* parents: 10033 diff changeset	597 counts = counts;
10222 51f145094648 mod_storage_: Also include timestmap of first message in :summary API Kim Alvefur <zash@zash.se>* parents: 10221 diff changeset	598 earliest = earliest;
10221 068692cb9e78 mod_storage_: Include timestamp of latest message in :summary API Kim Alvefur <zash@zash.se>* parents: 10220 diff changeset	599 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	600 };
4007 062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback. Waqas Hussain <waqas20@gmail.com> parents: 4004 diff changeset	601 end
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback. Waqas Hussain <waqas20@gmail.com> parents: 4004 diff changeset	602
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 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	604 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	605 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	606 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	607 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	608 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	609 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	610 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	611 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	612 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	613 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	614 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	615 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	616 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	617 if query.truncate == nil then
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion Kim Alvefur <zash@zash.se> parents: 8294 diff changeset	618 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	619 elseif engine.params.driver == "MySQL" then
fba795dd99c8 mod_storage_sql: Fix #1639 Kim Alvefur <zash@zash.se> parents: 12823 diff changeset	620 sql_query = [[
fba795dd99c8 mod_storage_sql: Fix #1639 Kim Alvefur <zash@zash.se> parents: 12823 diff changeset	621 DELETE result FROM prosodyarchive AS result JOIN (
fba795dd99c8 mod_storage_sql: Fix #1639 Kim Alvefur <zash@zash.se> parents: 12823 diff changeset	622 SELECT sort_id FROM prosodyarchive
fba795dd99c8 mod_storage_sql: Fix #1639 Kim Alvefur <zash@zash.se> parents: 12823 diff changeset	623 WHERE %s
fba795dd99c8 mod_storage_sql: Fix #1639 Kim Alvefur <zash@zash.se> parents: 12823 diff changeset	624 ORDER BY "sort_id" %s
fba795dd99c8 mod_storage_sql: Fix #1639 Kim Alvefur <zash@zash.se> parents: 12823 diff changeset	625 LIMIT 18446744073709551615 OFFSET %s
fba795dd99c8 mod_storage_sql: Fix #1639 Kim Alvefur <zash@zash.se> parents: 12823 diff changeset	626 ) AS limiter on result.sort_id = limiter.sort_id;]];
fba795dd99c8 mod_storage_sql: Fix #1639 Kim Alvefur <zash@zash.se> parents: 12823 diff changeset	627
fba795dd99c8 mod_storage_sql: Fix #1639 Kim Alvefur <zash@zash.se> parents: 12823 diff changeset	628 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	629 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	630 else
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion Kim Alvefur <zash@zash.se> parents: 8294 diff changeset	631 args[#args+1] = query.truncate;
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion Kim Alvefur <zash@zash.se> parents: 8294 diff changeset	632 local unlimited = "ALL";
10011 2408e6362c15 mod_storage_sql: Move code out of if-else chain Kim Alvefur <zash@zash.se> parents: 9628 diff changeset	633 sql_query = [[
2408e6362c15 mod_storage_sql: Move code out of if-else chain Kim Alvefur <zash@zash.se> parents: 9628 diff changeset	634 DELETE FROM "prosodyarchive"
2408e6362c15 mod_storage_sql: Move code out of if-else chain Kim Alvefur <zash@zash.se> parents: 9628 diff changeset	635 WHERE "sort_id" IN (
2408e6362c15 mod_storage_sql: Move code out of if-else chain Kim Alvefur <zash@zash.se> parents: 9628 diff changeset	636 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	637 WHERE %s
2408e6362c15 mod_storage_sql: Move code out of if-else chain Kim Alvefur <zash@zash.se> parents: 9628 diff changeset	638 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	639 LIMIT %s OFFSET ?
2408e6362c15 mod_storage_sql: Move code out of if-else chain Kim Alvefur <zash@zash.se> parents: 9628 diff changeset	640 );]];
8395 fbb9a1c2120e mod_storage_sql: Add support for truncating deletion Kim Alvefur <zash@zash.se> parents: 8294 diff changeset	641 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	642 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	643 sql_query = [[
acf4a7bfb6aa mod_storage_sql: Handle SQLite DELETE with LIMIT being optional (fixes #1359) Kim Alvefur <zash@zash.se> parents: 10011 diff changeset	644 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	645 WHERE %s
acf4a7bfb6aa mod_storage_sql: Handle SQLite DELETE with LIMIT being optional (fixes #1359) Kim Alvefur <zash@zash.se> parents: 10011 diff changeset	646 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	647 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	648 ]];
acf4a7bfb6aa mod_storage_sql: Handle SQLite DELETE with LIMIT being optional (fixes #1359) Kim Alvefur <zash@zash.se> parents: 10011 diff changeset	649 end
8395 fbb9a1c2120e mod_storage_sql: Add support for truncating deletion Kim Alvefur <zash@zash.se> parents: 8294 diff changeset	650 unlimited = "-1";
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion Kim Alvefur <zash@zash.se> parents: 8294 diff changeset	651 end
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion Kim Alvefur <zash@zash.se> parents: 8294 diff changeset	652 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	653 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	654 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	655 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	656 end);
11969 395dce95508d mod_storage_sql: Fix traceback on multi-user deletion Kim Alvefur <zash@zash.se> parents: 11967 diff changeset	657 if username == true then
395dce95508d mod_storage_sql: Fix traceback on multi-user deletion Kim Alvefur <zash@zash.se> parents: 11967 diff changeset	658 archive_item_count_cache:clear();
395dce95508d mod_storage_sql: Fix traceback on multi-user deletion Kim Alvefur <zash@zash.se> parents: 11967 diff changeset	659 else
395dce95508d mod_storage_sql: Fix traceback on multi-user deletion Kim Alvefur <zash@zash.se> parents: 11967 diff changeset	660 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	661 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	662 -- 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	663 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	664 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	665 -- 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	666 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	667 end
11969 395dce95508d mod_storage_sql: Fix traceback on multi-user deletion Kim Alvefur <zash@zash.se> parents: 11967 diff changeset	668 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	669 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	670 end
5776 bd0ff8ae98a8 Remove all trailing whitespace Florian Zeitz <florob@babelmonkeys.de> parents: 5209 diff changeset	671
10002 b6b5b9d7417d mod_storage_sql: Add support for iterating over users in archive stores Kim Alvefur <zash@zash.se> parents: 9904 diff changeset	672 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	673 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	674 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	675 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	676 FROM "prosodyarchive"
b6b5b9d7417d mod_storage_sql: Add support for iterating over users in archive stores Kim Alvefur <zash@zash.se> parents: 9904 diff changeset	677 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	678 ]];
b6b5b9d7417d mod_storage_sql: Add support for iterating over users in archive stores Kim Alvefur <zash@zash.se> parents: 9904 diff changeset	679 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	680 end);
b6b5b9d7417d mod_storage_sql: Add support for iterating over users in archive stores Kim Alvefur <zash@zash.se> parents: 9904 diff changeset	681 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	682 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	683 end
b6b5b9d7417d mod_storage_sql: Add support for iterating over users in archive stores Kim Alvefur <zash@zash.se> parents: 9904 diff changeset	684
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	685 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	686 __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	687 -- 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	688 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	689 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	690 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	691 -- 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	692 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	693 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	694 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	695 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	696 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	697 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	698 };
033ea8b46d6a mod_storage_sql: Compose a keyval+ store out of keyval and map store methods Kim Alvefur <zash@zash.se> parents: 13143 diff changeset	699 }
033ea8b46d6a mod_storage_sql: Compose a keyval+ store out of keyval and map store methods Kim Alvefur <zash@zash.se> parents: 13143 diff changeset	700
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	701 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	702 keyval = keyval_store;
6954 400badaf1fc7 mod_storage_sql: Add map store (backported from trunk) Matthew Wild <mwild1@gmail.com> parents: 6953 diff changeset	703 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	704 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	705 ["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	706 };
5776 bd0ff8ae98a8 Remove all trailing whitespace Florian Zeitz <florob@babelmonkeys.de> parents: 5209 diff changeset	707
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	708 --- Implement storage driver API
5776 bd0ff8ae98a8 Remove all trailing whitespace Florian Zeitz <florob@babelmonkeys.de> parents: 5209 diff changeset	709
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	710 -- 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	711
5122 b41c33dc7c36 mod_storage_: Don't explicitly set driver name, to ease copying/renaming modules. Waqas Hussain <waqas20@gmail.com>* parents: 5121 diff changeset	712 local driver = {};
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	713
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	714 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	715 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	716 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	717 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	718 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	719 return nil, "unsupported-store";
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	720 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	721
5132 ce6546f867f9 mod_storage_sql: Remove obsolete comment Matthew Wild <mwild1@gmail.com> parents: 5130 diff changeset	722 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	723 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	724 (username == true and "!=?" or "=?");
5035 874cab7b4b3e mod_storage_sql: Add method for listing stores Kim Alvefur <zash@zash.se> parents: 5034 diff changeset	725 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	726 username = "";
874cab7b4b3e mod_storage_sql: Add method for listing stores Kim Alvefur <zash@zash.se> parents: 5034 diff changeset	727 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	728 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	729 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	730 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	731 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	732 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	733 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	734
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_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 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	736 return engine:transaction(function()
8292 2fc8b83dd736 mod_storage_sql: Remove return values from purge method Kim Alvefur <zash@zash.se> parents: 8138 diff changeset	737 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	738 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	739 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	740 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	741
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	742 --- 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	743
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	744
9459 6c279302fff4 mod_storage_sql: Remove unused function argument [luacheck] Kim Alvefur <zash@zash.se> parents: 8537 diff changeset	745 local function create_table(engine) -- luacheck: ignore 431/engine
12846 1638991caed2 mod_storage_sql: Support SQLite3 without LuaDBI Kim Alvefur <zash@zash.se> parents: 12830 diff changeset	746 local sql = engine.params.driver == "SQLite3" and sqlite or dbisql;
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	747 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	748
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_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 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	750 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	751 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	752 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	753 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	754 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	755 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	756 Column { name="value", type="MEDIUMTEXT", nullable=false };
13143 3ec48555b773 mod_storage_sql: Add UPSERT support Kim Alvefur <zash@zash.se> parents: 12977 diff changeset	757 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	758 };
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_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 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	760 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	761 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	762
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	763 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	764 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	765 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	766 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	767 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	768 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	769 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	770 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	771 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	772 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	773 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	774 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	775 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	776 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	777 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	778 };
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_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 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	780 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	781 end);
5035 874cab7b4b3e mod_storage_sql: Add method for listing stores Kim Alvefur <zash@zash.se> parents: 5034 diff changeset	782 end
874cab7b4b3e mod_storage_sql: Add method for listing stores Kim Alvefur <zash@zash.se> parents: 5034 diff changeset	783
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	784 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	785 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	786 if params.driver == "MySQL" then
12846 1638991caed2 mod_storage_sql: Support SQLite3 without LuaDBI Kim Alvefur <zash@zash.se> parents: 12830 diff changeset	787 local sql = dbisql;
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	788 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	789 do
9628 2fcf517b811e mod_storage_sql: Catch errors during schema upgrade (thanks Nothing4You) Matthew Wild <mwild1@gmail.com> parents: 9537 diff changeset	790 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	791 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	792 changes = true;
55b40f3fa659 mod_storage_sql: Change prosodyarchive_index to be non-unique (fixes #1087) Matthew Wild <mwild1@gmail.com> parents: 9492 diff changeset	793 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	794 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	795 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	796 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	797 end
55b40f3fa659 mod_storage_sql: Change prosodyarchive_index to be non-unique (fixes #1087) Matthew Wild <mwild1@gmail.com> parents: 9492 diff changeset	798 end
55b40f3fa659 mod_storage_sql: Change prosodyarchive_index to be non-unique (fixes #1087) Matthew Wild <mwild1@gmail.com> parents: 9492 diff changeset	799 end
55b40f3fa659 mod_storage_sql: Change prosodyarchive_index to be non-unique (fixes #1087) Matthew Wild <mwild1@gmail.com> parents: 9492 diff changeset	800
55b40f3fa659 mod_storage_sql: Change prosodyarchive_index to be non-unique (fixes #1087) Matthew Wild <mwild1@gmail.com> parents: 9492 diff changeset	801 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	802 -- 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	803 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	804 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	805 changes = true;
55b40f3fa659 mod_storage_sql: Change prosodyarchive_index to be non-unique (fixes #1087) Matthew Wild <mwild1@gmail.com> parents: 9492 diff changeset	806 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	807 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	808 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	809 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	810 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	811 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	812 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	813 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	814 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	815 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	816 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	817 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	818 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	819 .."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	820 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	821 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	822 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	823
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	824 -- 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	825 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	826 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	827 FROM "information_schema"."columns"
8081 a201f315de55 mod_storage_sql: Break long lines Kim Alvefur <zash@zash.se> parents: 8080 diff changeset	828 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	829 AND "TABLE_SCHEMA" = ?
8081 a201f315de55 mod_storage_sql: Break long lines Kim Alvefur <zash@zash.se> parents: 8080 diff changeset	830 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	831 ]];
8038 cd6cef579e82 mod_storage_sql: Remove unused return values (should those actually be used?) Kim Alvefur <zash@zash.se> parents: 8037 diff changeset	832 -- 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	833 engine:transaction(function()
8082 8ca11201bfe7 mod_storage_sql: Limit encoding check to current database Kim Alvefur <zash@zash.se> parents: 8081 diff changeset	834 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	835 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	836 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	837 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	838 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	839 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	840 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	841 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	842 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	843 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	844 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	845 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	846 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	847 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	848 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	849 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	850 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	851 end);
8081 a201f315de55 mod_storage_sql: Break long lines Kim Alvefur <zash@zash.se> parents: 8080 diff changeset	852 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	853 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	854 engine.charset, engine.charset.."_bin");
13143 3ec48555b773 mod_storage_sql: Add UPSERT support Kim Alvefur <zash@zash.se> parents: 12977 diff changeset	855 end);
3ec48555b773 mod_storage_sql: Add UPSERT support Kim Alvefur <zash@zash.se> parents: 12977 diff changeset	856 if not success then
3ec48555b773 mod_storage_sql: Add UPSERT support Kim Alvefur <zash@zash.se> parents: 12977 diff changeset	857 module:log("error", "Failed to check/upgrade database encoding: %s", err or "unknown error");
3ec48555b773 mod_storage_sql: Add UPSERT support Kim Alvefur <zash@zash.se> parents: 12977 diff changeset	858 return false;
3ec48555b773 mod_storage_sql: Add UPSERT support Kim Alvefur <zash@zash.se> parents: 12977 diff changeset	859 end
3ec48555b773 mod_storage_sql: Add UPSERT support Kim Alvefur <zash@zash.se> parents: 12977 diff changeset	860 else
3ec48555b773 mod_storage_sql: Add UPSERT support Kim Alvefur <zash@zash.se> parents: 12977 diff changeset	861 local indices = {};
3ec48555b773 mod_storage_sql: Add UPSERT support Kim Alvefur <zash@zash.se> parents: 12977 diff changeset	862 engine:transaction(function ()
3ec48555b773 mod_storage_sql: Add UPSERT support Kim Alvefur <zash@zash.se> parents: 12977 diff changeset	863 if params.driver == "SQLite3" then
13156 019cd1db9bec mod_storage_sql: Be more specific when checking for old index in SQLite3 Kim Alvefur <zash@zash.se> parents: 13155 diff changeset	864 for row in engine:select [[SELECT "name" FROM "sqlite_schema" WHERE "type"='index' AND "tbl_name"='prosody' AND "name"='prosody_index';]] do
13143 3ec48555b773 mod_storage_sql: Add UPSERT support Kim Alvefur <zash@zash.se> parents: 12977 diff changeset	865 indices[row[1]] = true;
3ec48555b773 mod_storage_sql: Add UPSERT support Kim Alvefur <zash@zash.se> parents: 12977 diff changeset	866 end
3ec48555b773 mod_storage_sql: Add UPSERT support Kim Alvefur <zash@zash.se> parents: 12977 diff changeset	867 elseif params.driver == "PostgreSQL" then
13159 706f7d1affc2 mod_storage_sql: Fix column name in index check for PostgreSQL Kim Alvefur <zash@zash.se> parents: 13158 diff changeset	868 for row in engine:select [[SELECT "indexname" FROM "pg_indexes" WHERE "tablename"='prosody' AND "indexname"='prosody_index';]] do
13143 3ec48555b773 mod_storage_sql: Add UPSERT support Kim Alvefur <zash@zash.se> parents: 12977 diff changeset	869 indices[row[1]] = true;
3ec48555b773 mod_storage_sql: Add UPSERT support Kim Alvefur <zash@zash.se> parents: 12977 diff changeset	870 end
3ec48555b773 mod_storage_sql: Add UPSERT support Kim Alvefur <zash@zash.se> parents: 12977 diff changeset	871 end
3ec48555b773 mod_storage_sql: Add UPSERT support Kim Alvefur <zash@zash.se> parents: 12977 diff changeset	872 end)
13157 841276d0cc25 mod_storage_sql: Only remove old index if it exists Kim Alvefur <zash@zash.se> parents: 13156 diff changeset	873 if indices["prosody_index"] then
841276d0cc25 mod_storage_sql: Only remove old index if it exists Kim Alvefur <zash@zash.se> parents: 13156 diff changeset	874 if apply_changes then
841276d0cc25 mod_storage_sql: Only remove old index if it exists Kim Alvefur <zash@zash.se> parents: 13156 diff changeset	875 local success = engine:transaction(function ()
841276d0cc25 mod_storage_sql: Only remove old index if it exists Kim Alvefur <zash@zash.se> parents: 13156 diff changeset	876 return assert(engine:execute([[DROP INDEX "prosody_index";]]));
841276d0cc25 mod_storage_sql: Only remove old index if it exists Kim Alvefur <zash@zash.se> parents: 13156 diff changeset	877 end);
841276d0cc25 mod_storage_sql: Only remove old index if it exists Kim Alvefur <zash@zash.se> parents: 13156 diff changeset	878 if not success then
841276d0cc25 mod_storage_sql: Only remove old index if it exists Kim Alvefur <zash@zash.se> parents: 13156 diff changeset	879 module:log("error", "Failed to delete obsolete index \"prosody_index\"");
841276d0cc25 mod_storage_sql: Only remove old index if it exists Kim Alvefur <zash@zash.se> parents: 13156 diff changeset	880 return false;
841276d0cc25 mod_storage_sql: Only remove old index if it exists Kim Alvefur <zash@zash.se> parents: 13156 diff changeset	881 end
841276d0cc25 mod_storage_sql: Only remove old index if it exists Kim Alvefur <zash@zash.se> parents: 13156 diff changeset	882 else
841276d0cc25 mod_storage_sql: Only remove old index if it exists Kim Alvefur <zash@zash.se> parents: 13156 diff changeset	883 changes = true;
13143 3ec48555b773 mod_storage_sql: Add UPSERT support Kim Alvefur <zash@zash.se> parents: 12977 diff changeset	884 end
3ec48555b773 mod_storage_sql: Add UPSERT support Kim Alvefur <zash@zash.se> parents: 12977 diff changeset	885 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	886 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	887 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	888 end
685403a6fee1 mod_storage_sql: Add method for removing all data belonging to a user Kim Alvefur <zash@zash.se> parents: 5035 diff changeset	889
8035 58d6c2ab3d16 mod_storage_sql: Ignore name clash [luacheck] Kim Alvefur <zash@zash.se> parents: 8034 diff changeset	890 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	891 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	892 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	893 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	894 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	895 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	896
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	897 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	898 return {
8036 7ebaefc88b3d mod_storage_sql: Split long lines [luacheck] Kim Alvefur <zash@zash.se> parents: 8035 diff changeset	899 driver = assert(params.driver,
7ebaefc88b3d mod_storage_sql: Split long lines [luacheck] Kim Alvefur <zash@zash.se> parents: 8035 diff changeset	900 "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	901 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	902 "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	903 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	904 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	905 host = params.host;
7753 c276d72d4e17 mod_storage_sql: Include missing parameter table key 'port' Kim Alvefur <zash@zash.se> parents: 7752 diff changeset	906 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	907 };
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	908 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	909
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	910 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	911 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	912 local params = normalize_params(module:get_option("sql", default_params));
12846 1638991caed2 mod_storage_sql: Support SQLite3 without LuaDBI Kim Alvefur <zash@zash.se> parents: 12830 diff changeset	913 local sql = params.driver == "SQLite3" and sqlite or dbisql;
10793 9ce67da45e54 mod_storage_sql: Log database connection parameters when creating engine Kim Alvefur <zash@zash.se> parents: 10701 diff changeset	914 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	915 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	916 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	917 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	918 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	919 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	920 -- 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	921 -- 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	922 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	923 -- 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	924 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	925 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	926 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	927 end
10652 0c00274528a4 mod_storage_sql: Fix check for deletion limits (fixes #1494) Kim Alvefur <zash@zash.se> parents: 10012 diff changeset	928 end
10655 ee6c12264420 mod_storage_sql: Move update limit check back one step, fixes traceback Kim Alvefur <zash@zash.se> parents: 10652 diff changeset	929 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	930 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	931 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	932 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	933 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	934 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	935 end
13148 c6f46f33cecf mod_storage_sql: Record all SQLite3 compile options for potential use Kim Alvefur <zash@zash.se> parents: 13144 diff changeset	936 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	937
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	938 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	939 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	940 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	941 end
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	942
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	943 -- 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	944 -- 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	945 -- will not work there.
13222 cb6a17eaa0e6 mod_storage_sql: Use config enum for 'sqlite_tune' Kim Alvefur <zash@zash.se> parents: 13221 diff changeset	946 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	947 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	948 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	949 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	950 end
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	951 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	952 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	953 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	954 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	955 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	956 end
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	957 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	958 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	959 end
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	960 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	961 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	962 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	963 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	964 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	965 end
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	966 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	967 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	968 end
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	969
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	970 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	971 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	972 end
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	973
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety Kim Alvefur <zash@zash.se> parents: 13148 diff changeset	974 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	975 end
12872 a20923f7d5fd mod_storage_sql: Record connection to database as module status Kim Alvefur <zash@zash.se> parents: 12846 diff changeset	976 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	977 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	978 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	979 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	980 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	981 else
a20923f7d5fd mod_storage_sql: Record connection to database as module status Kim Alvefur <zash@zash.se> parents: 12846 diff changeset	982 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	983 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	984
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	985 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	986 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	987
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2 Matthew Wild <mwild1@gmail.com> parents: 6283 diff changeset	988 function module.command(arg)
12977 74b9e05af71e plugins: Prefix module imports with prosody namespace Kim Alvefur <zash@zash.se> parents: 12875 diff changeset	989 local config = require "prosody.core.configmanager";
74b9e05af71e plugins: Prefix module imports with prosody namespace Kim Alvefur <zash@zash.se> parents: 12875 diff changeset	990 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	991 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	992 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	993 -- 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	994 local uris = {};
8037 5a802653d50d mod_storage_sql: Ignore name clash [luacheck] Kim Alvefur <zash@zash.se> parents: 8036 diff changeset	995 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	996 local params = normalize_params(config.get(host, "sql") or default_params);
12846 1638991caed2 mod_storage_sql: Support SQLite3 without LuaDBI Kim Alvefur <zash@zash.se> parents: 12830 diff changeset	997 local sql = engine.params.driver == "SQLite3" and sqlite or dbisql;
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	998 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	999 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	1000 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	1001 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	1002 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	1003 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	1004 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	1005 print("Ensure you have working backups of the above databases before continuing! ");
11625 04abe65b8067 mod_storage_sql: Fix for move of yes/no function (Thanks Kasim) Kim Alvefur <zash@zash.se> parents: 11354 diff changeset	1006 if not 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	1007 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	1008 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	1009 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	1010 -- 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	1011 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	1012 print("Checking "..params.database.."...");
12846 1638991caed2 mod_storage_sql: Support SQLite3 without LuaDBI Kim Alvefur <zash@zash.se> parents: 12830 diff changeset	1013 local sql = params.driver == "SQLite3" and sqlite or dbisql;
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	1014 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	1015 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	1016 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	1017 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	1018 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	1019 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	1020 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	1021 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	1022 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	1023 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	1024 end

prosody

84c7779618b6

plugins/mod_storage_sql.lua

Software / code / prosody

Annotate

plugins/mod_storage_sql.lua @ 13252:84c7779618b6

Software `/` code `/` prosody