Software `/` code `/` prosody

Annotate

plugins/mod_storage_sql.lua @ 13213:50324f66ca2a

plugins: Use integer config API with interval specification where sensible Many of these fall into a few categories: - util.cache size, must be >= 1 - byte or item counts that logically can't be negative - port numbers that should be in 1..0xffff

author	Kim Alvefur <zash@zash.se>
date	Mon, 17 Jul 2023 01:38:54 +0200
parent	13190:9dc6de46dae3
child	13221:2a9ce229efac

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

prosody

50324f66ca2a

plugins/mod_storage_sql.lua

Software / code / prosody

Annotate

plugins/mod_storage_sql.lua @ 13213:50324f66ca2a

Software `/` code `/` prosody