prosody
82f6a0b0a425
plugins/mod_storage_sql.lua

Find changesets by keywords by author, files, the commit message, revision number or hash, or revset expression.

Software / code / prosody

Annotate

plugins/mod_storage_sql.lua @ 12037:82f6a0b0a425

tools/generate_format_spec: Apply lua-format to silence luacheck
author Kim Alvefur <zash@zash.se>
date Sat, 11 Dec 2021 20:58:09 +0100
parent 11971:0b350909da24
child 12574:18d33668c5fa
child 12592:d580e6a57cbb
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
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
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
4 local cache = require "util.cache";
3980
6b2fac6602b3 mod_storage_sql: Use util.json instead of util.serialization.
Waqas Hussain <waqas20@gmail.com>
parents: 3978
diff changeset
5 local json = require "util.json";
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
6 local sql = require "util.sql";
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
7 local xml_parse = require "util.xml".parse;
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
8 local uuid = require "util.uuid";
6165
6a184b16b717 core.certmanager, core.moduleapi, mod_storage_sql, mod_storage_sql2: Import from util.paths
Kim Alvefur <zash@zash.se>
parents: 5776
diff changeset
9 local resolve_relative_path = require "util.paths".resolve_relative_path;
9885
64e16d1e91f6 mod_storage_internal,_sql: Key item count cache on both username and store
Kim Alvefur <zash@zash.se>
parents: 9884
diff changeset
10 local jid_join = require "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
11
7751
168919a947ed mod_storage_sql: Use is_stanza() from util.stanza
Kim Alvefur <zash@zash.se>
parents: 7305
diff changeset
12 local is_stanza = require"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
13 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
14
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_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 noop = function() end
9691
e11e076f0eb8 various: Don't rely on _G.unpack existing
Kim Alvefur <zash@zash.se>
parents: 9628
diff changeset
16 local unpack = table.unpack or unpack; -- luacheck: ignore 113
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_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 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
18 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
19 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
20 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
21 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
22 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
23 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
24 end
08560575762f mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents: 4101
diff changeset
25
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
26 local default_params = { driver = "SQLite3" };
5776
bd0ff8ae98a8 Remove all trailing whitespace
Florian Zeitz <florob@babelmonkeys.de>
parents: 5209
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 engine;
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
29
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
30 local function serialize(value)
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
31 local t = type(value);
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
32 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
33 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
34 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
35 return "xml", tostring(value);
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
36 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
37 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
38 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
39 return nil, err;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
40 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
41 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
42 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
43 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
44 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
45 elseif t == "boolean" then
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
46 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
47 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
48 return nil, "invalid-boolean";
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors
Kim Alvefur <zash@zash.se>
parents: 8480
diff changeset
49 elseif t == "number" then
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors
Kim Alvefur <zash@zash.se>
parents: 8480
diff changeset
50 value = tonumber(value);
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors
Kim Alvefur <zash@zash.se>
parents: 8480
diff changeset
51 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
52 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
53 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
54 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
55 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
56 return xml_parse(value);
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
57 end
8537
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors
Kim Alvefur <zash@zash.se>
parents: 8480
diff changeset
58 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
59 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
60
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
61 local 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
62 local user, store;
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
63
4007
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
64 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
65 local haveany;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
66 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
67 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
68 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
69 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
70 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
71 ]]
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
72 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
73 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
74 local k = row[1];
8537
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors
Kim Alvefur <zash@zash.se>
parents: 8480
diff changeset
75 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
76 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
77 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
78 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
79 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
80 result[a] = b;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
81 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
82 end
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
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
85 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
86 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
87 end
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
88 end
4007
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
89 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
90 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
91 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
92 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
93 ]];
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
94 engine:delete(delete_sql, host, user or "", store);
5776
bd0ff8ae98a8 Remove all trailing whitespace
Florian Zeitz <florob@babelmonkeys.de>
parents: 5209
diff changeset
95
8033
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
96 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
97 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
98 ("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
99 VALUES (?,?,?,?,?,?);
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
100 ]]
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
101 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
102 local extradata = {};
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
103 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
104 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
105 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
106 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
107 else
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
108 extradata[key] = value;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
109 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
110 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
111 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
112 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
113 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
114 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
115 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
116 return true;
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
117 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
118
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
119 --- 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
120
4007
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
121 local keyval_store = {};
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
122 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
123 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
124 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
125 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
126 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
127 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
128 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
129 end
6953
b9276d677e76 mod_storage_sql: Whitespace fix (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6951
diff changeset
130 return result;
4007
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
131 end
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
132 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
133 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
134 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
135 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
136 end);
4007
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
137 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
138 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
139 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
140 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
141 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
142 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
143 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
144 ]];
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
145 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
146 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
147 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
148 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
149 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
150
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_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 --- 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
152
9896
dafe1b544822 mod_storage_sql: No archive item limit by default
Kim Alvefur <zash@zash.se>
parents: 9893
diff changeset
153 local archive_item_limit = module:get_option_number("storage_archive_item_limit");
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
154 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
155
10993
b5e7f4d533e2 mod_storage_sql: Measure hits/misses on the item count cache
Kim Alvefur <zash@zash.se>
parents: 10925
diff changeset
156 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
157 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
158
10033
ca8333d1a7fe mod_storage_sql: Ignore shadowed error variable [luacheck]
Kim Alvefur <zash@zash.se>
parents: 10032
diff changeset
159 -- 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
160 local map_store = {};
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
161 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
162 map_store.remove = {};
6954
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
163 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
164 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
165 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
166 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
167 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
168 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
169 LIMIT 1
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
170 ]];
8537
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors
Kim Alvefur <zash@zash.se>
parents: 8480
diff changeset
171 local data, err;
6954
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
172 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
173 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
174 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
175 assert(data ~= nil, err);
6954
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
176 end
7274
e0727512bb99 mod_storage_sql: Allow loops over results to end on their own
Kim Alvefur <zash@zash.se>
parents: 7273
diff changeset
177 return data;
6954
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
178 else
8033
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
179 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
180 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
181 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
182 end
7274
e0727512bb99 mod_storage_sql: Allow loops over results to end on their own
Kim Alvefur <zash@zash.se>
parents: 7273
diff changeset
183 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
184 end
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
185 end);
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
186 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
187 return result;
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
188 end
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
189 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
190 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
191 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
192 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
193 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
194 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
195 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
196 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
197 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
198 ]];
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
199 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
200 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
201 ("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
202 VALUES (?,?,?,?,?,?);
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
203 ]];
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
204 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
205 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
206 FROM "prosody"
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
Kim Alvefur <zash@zash.se>
parents: 8050
diff changeset
207 WHERE "host"=? AND "user"=? AND "store"=? AND "key"=?
8033
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
208 LIMIT 1;
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
209 ]];
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
210 for key, data in pairs(keydatas) do
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
211 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
212 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
213 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
214 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
215 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
216 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
217 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
218 else
8537
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors
Kim Alvefur <zash@zash.se>
parents: 8480
diff changeset
219 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
220 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
221 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
222 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
223 end
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, 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
225 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
226 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
227 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
228 end
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
229 end
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
230 return true;
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
231 end);
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
232 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
233 return result;
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
234 end
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
235
10680
19692fc5c106 storagemanager, mod_storage_sql: Rename methods to :get_all() and :delete_all()
Matthew Wild <mwild1@gmail.com>
parents: 10677
diff changeset
236 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
237 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
238 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
239 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
240 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
241 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
242 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
243 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
244 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
245 ]];
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests)
Matthew Wild <mwild1@gmail.com>
parents: 10656
diff changeset
246
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests)
Matthew Wild <mwild1@gmail.com>
parents: 10656
diff changeset
247 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
248 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
249 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
250 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
251 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
252 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
253 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
254 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
255 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
256
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 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
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 end);
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests)
Matthew Wild <mwild1@gmail.com>
parents: 10656
diff changeset
260 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
261 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
262 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
263
10680
19692fc5c106 storagemanager, mod_storage_sql: Rename methods to :get_all() and :delete_all()
Matthew Wild <mwild1@gmail.com>
parents: 10677
diff changeset
264 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
265 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
266 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
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 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
269 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
270 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
271 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
272 ]];
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 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
274 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
275 end);
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests)
Matthew Wild <mwild1@gmail.com>
parents: 10656
diff changeset
276 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
277 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
278 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
279
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
280 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
281 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
282 total = true;
9884
9751c17f5281 mod_storage_internal,_sql: Expose archive capabilities feature set
Kim Alvefur <zash@zash.se>
parents: 9883
diff changeset
283 quota = archive_item_limit;
9751c17f5281 mod_storage_internal,_sql: Expose archive capabilities feature set
Kim Alvefur <zash@zash.se>
parents: 9883
diff changeset
284 truncate = true;
11271
41a962b72a6e mod_storage_sql: Advertise support for id range query
Kim Alvefur <zash@zash.se>
parents: 10993
diff changeset
285 full_id_range = true;
11279
f225070e2b8a mod_storage_sql: Support query for set of IDs
Kim Alvefur <zash@zash.se>
parents: 11271
diff changeset
286 ids = true;
11967
0f2b5182e80b mod_storage_sql: Expose multi-user deletion capability
Kim Alvefur <zash@zash.se>
parents: 11625
diff changeset
287 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
288 };
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
289 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
290 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
291 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
292 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
293 local item_count = archive_item_count_cache:get(cache_key);
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
294 if not item_count then
10993
b5e7f4d533e2 mod_storage_sql: Measure hits/misses on the item count cache
Kim Alvefur <zash@zash.se>
parents: 10925
diff changeset
295 item_count_cache_miss();
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
296 local ok, ret = engine:transaction(function()
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
297 local count_sql = [[
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
298 SELECT COUNT(*) FROM "prosodyarchive"
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
299 WHERE "host"=? AND "user"=? AND "store"=?;
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
300 ]];
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
301 local result = engine:select(count_sql, host, user, store);
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
302 if result then
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
303 for row in result do
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
304 item_count = row[1];
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
305 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
306 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
307 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
308 if not ok or not item_count then
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
309 module:log("error", "Failed while checking quota for %s: %s", username, ret);
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
310 return nil, "Failure while checking quota";
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
311 end
9885
64e16d1e91f6 mod_storage_internal,_sql: Key item count cache on both username and store
Kim Alvefur <zash@zash.se>
parents: 9884
diff changeset
312 archive_item_count_cache:set(cache_key, item_count);
10993
b5e7f4d533e2 mod_storage_sql: Measure hits/misses on the item count cache
Kim Alvefur <zash@zash.se>
parents: 10925
diff changeset
313 else
b5e7f4d533e2 mod_storage_sql: Measure hits/misses on the item count cache
Kim Alvefur <zash@zash.se>
parents: 10925
diff changeset
314 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
315 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
316
9896
dafe1b544822 mod_storage_sql: No archive item limit by default
Kim Alvefur <zash@zash.se>
parents: 9893
diff changeset
317 if archive_item_limit then
dafe1b544822 mod_storage_sql: No archive item limit by default
Kim Alvefur <zash@zash.se>
parents: 9893
diff changeset
318 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
319 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
320 return nil, "quota-limit";
dafe1b544822 mod_storage_sql: No archive item limit by default
Kim Alvefur <zash@zash.se>
parents: 9893
diff changeset
321 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
322 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
323
8031
ef838b7f8f53 mod_storage_sql: Sensible defaults for 'when' and 'with' arguments
Kim Alvefur <zash@zash.se>
parents: 8029
diff changeset
324 when = when or os.time();
ef838b7f8f53 mod_storage_sql: Sensible defaults for 'when' and 'with' arguments
Kim Alvefur <zash@zash.se>
parents: 8029
diff changeset
325 with = with or "";
8032
aa9f198cb3c9 mod_storage_sql: Rename variables to avoid name clashes [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8031
diff changeset
326 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
327 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
328 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
329 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
330 ]];
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
331 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
332 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
333 ("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
334 VALUES (?,?,?,?,?,?,?,?);
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
335 ]];
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
336 if key then
10564
3098eac31139 mod_storage_sql: Remove unused and not actually returned return value [luacheck]
Kim Alvefur <zash@zash.se>
parents: 10222
diff changeset
337 local result = engine:delete(delete_sql, host, user or "", store, key);
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
338 if result then
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
339 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
340 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
341 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
342 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
343 end
8032
aa9f198cb3c9 mod_storage_sql: Rename variables to avoid name clashes [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8031
diff changeset
344 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
345 engine:insert(insert_sql, host, user or "", store, when, with, key, t, encoded_value);
9885
64e16d1e91f6 mod_storage_internal,_sql: Key item count cache on both username and store
Kim Alvefur <zash@zash.se>
parents: 9884
diff changeset
346 archive_item_count_cache:set(cache_key, item_count+1);
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
347 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
348 end);
8032
aa9f198cb3c9 mod_storage_sql: Rename variables to avoid name clashes [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8031
diff changeset
349 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
350 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
351 end
4007
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
352
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
353 -- 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
354 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
355 -- 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
356 if query.start 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
357 args[#args+1] = 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
358 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
359 end
5776
bd0ff8ae98a8 Remove all trailing whitespace
Florian Zeitz <florob@babelmonkeys.de>
parents: 5209
diff changeset
360
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
361 if query["end"] 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
362 args[#args+1] = query["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
363 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
364 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
365 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
366 where[#where+1] = "\"when\" <= ?"
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
367 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
368 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
369
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_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 -- 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
371 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
372 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
373 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
374 end
5776
bd0ff8ae98a8 Remove all trailing whitespace
Florian Zeitz <florob@babelmonkeys.de>
parents: 5209
diff changeset
375
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_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 -- 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
377 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
378 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
379 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
380 end
11279
f225070e2b8a mod_storage_sql: Support query for set of IDs
Kim Alvefur <zash@zash.se>
parents: 11271
diff changeset
381
f225070e2b8a mod_storage_sql: Support query for set of IDs
Kim Alvefur <zash@zash.se>
parents: 11271
diff changeset
382 -- Set of ids
f225070e2b8a mod_storage_sql: Support query for set of IDs
Kim Alvefur <zash@zash.se>
parents: 11271
diff changeset
383 if query.ids then
f225070e2b8a mod_storage_sql: Support query for set of IDs
Kim Alvefur <zash@zash.se>
parents: 11271
diff changeset
384 local nids, nargs = #query.ids, #args;
11283
f415176281fa mod_storage_sql: Fix compatibilty with Lua 5.1
Kim Alvefur <zash@zash.se>
parents: 11279
diff changeset
385 -- COMPAT Lua 5.1: No separator argument to string.rep
f415176281fa mod_storage_sql: Fix compatibilty with Lua 5.1
Kim Alvefur <zash@zash.se>
parents: 11279
diff changeset
386 where[#where + 1] = "\"key\" IN (" .. string.rep("?,", nids):sub(1,-2) .. ")";
11279
f225070e2b8a mod_storage_sql: Support query for set of IDs
Kim Alvefur <zash@zash.se>
parents: 11271
diff changeset
387 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
388 args[nargs+i] = id;
f225070e2b8a mod_storage_sql: Support query for set of IDs
Kim Alvefur <zash@zash.se>
parents: 11271
diff changeset
389 end
f225070e2b8a mod_storage_sql: Support query for set of IDs
Kim Alvefur <zash@zash.se>
parents: 11271
diff changeset
390 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
391 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
392 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
393 -- 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
394 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
395 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
396 FROM "prosodyarchive"
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325)
Kim Alvefur <zash@zash.se>
parents: 10017
diff changeset
397 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
398 LIMIT 1;
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325)
Kim Alvefur <zash@zash.se>
parents: 10017
diff changeset
399 ]];
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
400 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
401 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
402 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
403 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
404 end
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325)
Kim Alvefur <zash@zash.se>
parents: 10017
diff changeset
405 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
406 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
407 end
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325)
Kim Alvefur <zash@zash.se>
parents: 10017
diff changeset
408 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
409 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
410 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
411 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
412 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
413 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
414 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
415 end
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325)
Kim Alvefur <zash@zash.se>
parents: 10017
diff changeset
416 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
417 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
418 end
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325)
Kim Alvefur <zash@zash.se>
parents: 10017
diff changeset
419 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
420 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
421 end
10020
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325)
Kim Alvefur <zash@zash.se>
parents: 10017
diff changeset
422 return true;
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
423 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
424
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
425 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
426 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
427 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
428 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
429 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
430 (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
431 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
432 -- 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
433 -- 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
434 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
435 else
0b350909da24 mod_storage_sql: Return cached total where it makes sense
Kim Alvefur <zash@zash.se>
parents: 11970
diff changeset
436 -- 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
437 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
438 end
10031
17c175ad65f9 mod_storage_sql: Correctly return item-not-found error
Kim Alvefur <zash@zash.se>
parents: 10020
diff changeset
439 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
440 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
441 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
442 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
443 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
444 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
445 ]];
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
446 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
447 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
448
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
449 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
450
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
451 -- Total matching
11971
0b350909da24 mod_storage_sql: Return cached total where it makes sense
Kim Alvefur <zash@zash.se>
parents: 11970
diff changeset
452 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
453
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
454 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
455 .. 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
456 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
457 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
458 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
459 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
460 end
11970
f0e78fa8f24c mod_storage_sql: Adjust handling of 'total' for multi-id queries
Kim Alvefur <zash@zash.se>
parents: 11969
diff changeset
461 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
462 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
463 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
464 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
465 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
466 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
467 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
468
10020
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325)
Kim Alvefur <zash@zash.se>
parents: 10017
diff changeset
469 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
470 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
471
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_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 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
473 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
474 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
475
8033
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
476 sql_query = sql_query:format(t_concat(where, " AND "), query.reverse
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
477 and "DESC" or "ASC", query.limit and " 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
478 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
479 end);
10031
17c175ad65f9 mod_storage_sql: Correctly return item-not-found error
Kim Alvefur <zash@zash.se>
parents: 10020
diff changeset
480 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
481 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
482 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
483 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
484 if row ~= nil then
8537
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors
Kim Alvefur <zash@zash.se>
parents: 8480
diff changeset
485 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
486 assert(value ~= nil, err);
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors
Kim Alvefur <zash@zash.se>
parents: 8480
diff changeset
487 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
488 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
489 end, total;
4007
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
490 end
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
491
11354
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
492 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
493 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
494 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
495 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
496 return stanza, when, with;
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
497 end
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
498 return nil, "item-not-found";
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
499 end
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
500
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
501 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
502 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
503 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
504
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
505 local update_query = [[
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
506 UPDATE "prosodyarchive"
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
507 SET %s
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
508 WHERE %s
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
509 ]];
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
510 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
511 local setf = {};
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
512 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
513
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
514 if new_value then
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
515 table.insert(setf, '"type" = ?')
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
516 table.insert(setf, '"value" = ?')
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
517 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
518 table.insert(args, 1, t);
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
519 table.insert(args, 2, value);
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
520 end
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 if new_when then
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
523 table.insert(setf, 1, '"when" = ?')
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
524 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
525 end
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
526
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
527 if new_with then
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
528 table.insert(setf, 1, '"with" = ?')
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
529 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
530 end
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
531
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
532 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
533 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
534 end);
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
535 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
536 return result:affected() == 1;
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
9904
bf061f5512f7 mod_storage_sql: Implement archive summary API
Kim Alvefur <zash@zash.se>
parents: 9896
diff changeset
539 function archive_store:summary(username, query)
bf061f5512f7 mod_storage_sql: Implement archive summary API
Kim Alvefur <zash@zash.se>
parents: 9896
diff changeset
540 query = query or {};
bf061f5512f7 mod_storage_sql: Implement archive summary API
Kim Alvefur <zash@zash.se>
parents: 9896
diff changeset
541 local user,store = username,self.store;
bf061f5512f7 mod_storage_sql: Implement archive summary API
Kim Alvefur <zash@zash.se>
parents: 9896
diff changeset
542 local ok, result = engine:transaction(function()
bf061f5512f7 mod_storage_sql: Implement archive summary API
Kim Alvefur <zash@zash.se>
parents: 9896
diff changeset
543 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
544 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
545 FROM "prosodyarchive"
bf061f5512f7 mod_storage_sql: Implement archive summary API
Kim Alvefur <zash@zash.se>
parents: 9896
diff changeset
546 WHERE %s
bf061f5512f7 mod_storage_sql: Implement archive summary API
Kim Alvefur <zash@zash.se>
parents: 9896
diff changeset
547 GROUP BY "with"
bf061f5512f7 mod_storage_sql: Implement archive summary API
Kim Alvefur <zash@zash.se>
parents: 9896
diff changeset
548 ORDER BY "sort_id" %s%s;
bf061f5512f7 mod_storage_sql: Implement archive summary API
Kim Alvefur <zash@zash.se>
parents: 9896
diff changeset
549 ]];
bf061f5512f7 mod_storage_sql: Implement archive summary API
Kim Alvefur <zash@zash.se>
parents: 9896
diff changeset
550 local args = { host, user or "", store, };
bf061f5512f7 mod_storage_sql: Implement archive summary API
Kim Alvefur <zash@zash.se>
parents: 9896
diff changeset
551 local where = { "\"host\" = ?", "\"user\" = ?", "\"store\" = ?", };
bf061f5512f7 mod_storage_sql: Implement archive summary API
Kim Alvefur <zash@zash.se>
parents: 9896
diff changeset
552
bf061f5512f7 mod_storage_sql: Implement archive summary API
Kim Alvefur <zash@zash.se>
parents: 9896
diff changeset
553 archive_where(query, args, where);
bf061f5512f7 mod_storage_sql: Implement archive summary API
Kim Alvefur <zash@zash.se>
parents: 9896
diff changeset
554
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
555 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
556
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
557 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
558 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
559 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
560
8033
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
561 sql_query = sql_query:format(t_concat(where, " AND "), query.reverse
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
562 and "DESC" or "ASC", query.limit and " 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
563 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
564 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
565 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
566 local counts = {};
10222
51f145094648 mod_storage_*: Also include timestmap of first message in :summary API
Kim Alvefur <zash@zash.se>
parents: 10221
diff changeset
567 local earliest, latest = {}, {};
9904
bf061f5512f7 mod_storage_sql: Implement archive summary API
Kim Alvefur <zash@zash.se>
parents: 9896
diff changeset
568 for row in result do
bf061f5512f7 mod_storage_sql: Implement archive summary API
Kim Alvefur <zash@zash.se>
parents: 9896
diff changeset
569 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
570 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
571 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
572 latest[with] = row[4];
9904
bf061f5512f7 mod_storage_sql: Implement archive summary API
Kim Alvefur <zash@zash.se>
parents: 9896
diff changeset
573 end
10220
1e2b444acb72 mod_storage_*: Tweak :summary API to allow future expansion with more fields
Kim Alvefur <zash@zash.se>
parents: 10033
diff changeset
574 return {
1e2b444acb72 mod_storage_*: Tweak :summary API to allow future expansion with more fields
Kim Alvefur <zash@zash.se>
parents: 10033
diff changeset
575 counts = counts;
10222
51f145094648 mod_storage_*: Also include timestmap of first message in :summary API
Kim Alvefur <zash@zash.se>
parents: 10221
diff changeset
576 earliest = earliest;
10221
068692cb9e78 mod_storage_*: Include timestamp of latest message in :summary API
Kim Alvefur <zash@zash.se>
parents: 10220
diff changeset
577 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
578 };
4007
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
579 end
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
580
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
581 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
582 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
583 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
584 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
585 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
586 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
587 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
588 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
589 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
590 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
591 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
592 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
593 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
594 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
595 if query.truncate == nil then
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
596 sql_query = sql_query:format(t_concat(where, " AND "));
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
597 else
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
598 args[#args+1] = query.truncate;
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
599 local unlimited = "ALL";
10011
2408e6362c15 mod_storage_sql: Move code out of if-else chain
Kim Alvefur <zash@zash.se>
parents: 9628
diff changeset
600 sql_query = [[
2408e6362c15 mod_storage_sql: Move code out of if-else chain
Kim Alvefur <zash@zash.se>
parents: 9628
diff changeset
601 DELETE FROM "prosodyarchive"
2408e6362c15 mod_storage_sql: Move code out of if-else chain
Kim Alvefur <zash@zash.se>
parents: 9628
diff changeset
602 WHERE "sort_id" IN (
2408e6362c15 mod_storage_sql: Move code out of if-else chain
Kim Alvefur <zash@zash.se>
parents: 9628
diff changeset
603 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
604 WHERE %s
2408e6362c15 mod_storage_sql: Move code out of if-else chain
Kim Alvefur <zash@zash.se>
parents: 9628
diff changeset
605 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
606 LIMIT %s OFFSET ?
2408e6362c15 mod_storage_sql: Move code out of if-else chain
Kim Alvefur <zash@zash.se>
parents: 9628
diff changeset
607 );]];
8395
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
608 if engine.params.driver == "SQLite3" then
10012
acf4a7bfb6aa mod_storage_sql: Handle SQLite DELETE with LIMIT being optional (fixes #1359)
Kim Alvefur <zash@zash.se>
parents: 10011
diff changeset
609 if engine._have_delete_limit then
acf4a7bfb6aa mod_storage_sql: Handle SQLite DELETE with LIMIT being optional (fixes #1359)
Kim Alvefur <zash@zash.se>
parents: 10011
diff changeset
610 sql_query = [[
acf4a7bfb6aa mod_storage_sql: Handle SQLite DELETE with LIMIT being optional (fixes #1359)
Kim Alvefur <zash@zash.se>
parents: 10011
diff changeset
611 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
612 WHERE %s
acf4a7bfb6aa mod_storage_sql: Handle SQLite DELETE with LIMIT being optional (fixes #1359)
Kim Alvefur <zash@zash.se>
parents: 10011
diff changeset
613 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
614 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
615 ]];
acf4a7bfb6aa mod_storage_sql: Handle SQLite DELETE with LIMIT being optional (fixes #1359)
Kim Alvefur <zash@zash.se>
parents: 10011
diff changeset
616 end
8395
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
617 unlimited = "-1";
9479
3a683b2476e2 mod_storage_sql: Workaround MySQL not supporting LIMIT subquery in archive truncate operations, fixes #1200
Matthew Wild <mwild1@gmail.com>
parents: 9459
diff changeset
618 elseif engine.params.driver == "MySQL" then
3a683b2476e2 mod_storage_sql: Workaround MySQL not supporting LIMIT subquery in archive truncate operations, fixes #1200
Matthew Wild <mwild1@gmail.com>
parents: 9459
diff changeset
619 sql_query = [[
3a683b2476e2 mod_storage_sql: Workaround MySQL not supporting LIMIT subquery in archive truncate operations, fixes #1200
Matthew Wild <mwild1@gmail.com>
parents: 9459
diff changeset
620 DELETE result FROM prosodyarchive AS result JOIN (
3a683b2476e2 mod_storage_sql: Workaround MySQL not supporting LIMIT subquery in archive truncate operations, fixes #1200
Matthew Wild <mwild1@gmail.com>
parents: 9459
diff changeset
621 SELECT sort_id FROM prosodyarchive
3a683b2476e2 mod_storage_sql: Workaround MySQL not supporting LIMIT subquery in archive truncate operations, fixes #1200
Matthew Wild <mwild1@gmail.com>
parents: 9459
diff changeset
622 WHERE %s
3a683b2476e2 mod_storage_sql: Workaround MySQL not supporting LIMIT subquery in archive truncate operations, fixes #1200
Matthew Wild <mwild1@gmail.com>
parents: 9459
diff changeset
623 ORDER BY "sort_id" %s
3a683b2476e2 mod_storage_sql: Workaround MySQL not supporting LIMIT subquery in archive truncate operations, fixes #1200
Matthew Wild <mwild1@gmail.com>
parents: 9459
diff changeset
624 LIMIT %s OFFSET ?
3a683b2476e2 mod_storage_sql: Workaround MySQL not supporting LIMIT subquery in archive truncate operations, fixes #1200
Matthew Wild <mwild1@gmail.com>
parents: 9459
diff changeset
625 ) AS limiter on result.sort_id = limiter.sort_id;]];
3a683b2476e2 mod_storage_sql: Workaround MySQL not supporting LIMIT subquery in archive truncate operations, fixes #1200
Matthew Wild <mwild1@gmail.com>
parents: 9459
diff changeset
626 unlimited = "18446744073709551615";
8395
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
627 end
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
628 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
629 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
630 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
631 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
632 end);
11969
395dce95508d mod_storage_sql: Fix traceback on multi-user deletion
Kim Alvefur <zash@zash.se>
parents: 11967
diff changeset
633 if username == true then
395dce95508d mod_storage_sql: Fix traceback on multi-user deletion
Kim Alvefur <zash@zash.se>
parents: 11967
diff changeset
634 archive_item_count_cache:clear();
395dce95508d mod_storage_sql: Fix traceback on multi-user deletion
Kim Alvefur <zash@zash.se>
parents: 11967
diff changeset
635 else
395dce95508d mod_storage_sql: Fix traceback on multi-user deletion
Kim Alvefur <zash@zash.se>
parents: 11967
diff changeset
636 local cache_key = jid_join(username, host, self.store);
395dce95508d mod_storage_sql: Fix traceback on multi-user deletion
Kim Alvefur <zash@zash.se>
parents: 11967
diff changeset
637 archive_item_count_cache:set(cache_key, nil);
395dce95508d mod_storage_sql: Fix traceback on multi-user deletion
Kim Alvefur <zash@zash.se>
parents: 11967
diff changeset
638 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
639 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
640 end
5776
bd0ff8ae98a8 Remove all trailing whitespace
Florian Zeitz <florob@babelmonkeys.de>
parents: 5209
diff changeset
641
10002
b6b5b9d7417d mod_storage_sql: Add support for iterating over users in archive stores
Kim Alvefur <zash@zash.se>
parents: 9904
diff changeset
642 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
643 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
644 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
645 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
646 FROM "prosodyarchive"
b6b5b9d7417d mod_storage_sql: Add support for iterating over users in archive stores
Kim Alvefur <zash@zash.se>
parents: 9904
diff changeset
647 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
648 ]];
b6b5b9d7417d mod_storage_sql: Add support for iterating over users in archive stores
Kim Alvefur <zash@zash.se>
parents: 9904
diff changeset
649 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
650 end);
b6b5b9d7417d mod_storage_sql: Add support for iterating over users in archive stores
Kim Alvefur <zash@zash.se>
parents: 9904
diff changeset
651 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
652 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
653 end
b6b5b9d7417d mod_storage_sql: Add support for iterating over users in archive stores
Kim Alvefur <zash@zash.se>
parents: 9904
diff changeset
654
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
655 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
656 keyval = keyval_store;
6954
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
657 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
658 archive = 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
659 };
5776
bd0ff8ae98a8 Remove all trailing whitespace
Florian Zeitz <florob@babelmonkeys.de>
parents: 5209
diff changeset
660
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
661 --- Implement storage driver API
5776
bd0ff8ae98a8 Remove all trailing whitespace
Florian Zeitz <florob@babelmonkeys.de>
parents: 5209
diff changeset
662
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
663 -- 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
664
5122
b41c33dc7c36 mod_storage_*: Don't explicitly set driver name, to ease copying/renaming modules.
Waqas Hussain <waqas20@gmail.com>
parents: 5121
diff changeset
665 local driver = {};
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
666
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
667 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
668 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
669 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
670 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
671 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
672 return nil, "unsupported-store";
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
673 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
674
5132
ce6546f867f9 mod_storage_sql: Remove obsolete comment
Matthew Wild <mwild1@gmail.com>
parents: 5130
diff changeset
675 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
676 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
677 (username == true and "!=?" or "=?");
5035
874cab7b4b3e mod_storage_sql: Add method for listing stores
Kim Alvefur <zash@zash.se>
parents: 5034
diff changeset
678 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
679 username = "";
874cab7b4b3e mod_storage_sql: Add method for listing stores
Kim Alvefur <zash@zash.se>
parents: 5034
diff changeset
680 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
681 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
682 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
683 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
684 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
685 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
686 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
687
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
688 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
689 return engine:transaction(function()
8292
2fc8b83dd736 mod_storage_sql: Remove return values from purge method
Kim Alvefur <zash@zash.se>
parents: 8138
diff changeset
690 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
691 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
692 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
693 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
694
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
695 --- 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
696
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
697
9459
6c279302fff4 mod_storage_sql: Remove unused function argument [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8537
diff changeset
698 local function create_table(engine) -- 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
699 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
700
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
701 local 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
702 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
703 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
704 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
705 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
706 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
707 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
708 Column { name="value", type="MEDIUMTEXT", 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
709 Index { name="prosody_index", "host", "user", "store", "key" };
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
710 };
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
711 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
712 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
713 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
714
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
715 local 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
716 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
717 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
718 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
719 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
720 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
721 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
722 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
723 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
724 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
725 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
726 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
727 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
728 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
729 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
730 };
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_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 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
732 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
733 end);
5035
874cab7b4b3e mod_storage_sql: Add method for listing stores
Kim Alvefur <zash@zash.se>
parents: 5034
diff changeset
734 end
874cab7b4b3e mod_storage_sql: Add method for listing stores
Kim Alvefur <zash@zash.se>
parents: 5034
diff changeset
735
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
736 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
737 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
738 if params.driver == "MySQL" 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
739 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
740 do
9628
2fcf517b811e mod_storage_sql: Catch errors during schema upgrade (thanks Nothing4You)
Matthew Wild <mwild1@gmail.com>
parents: 9537
diff changeset
741 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
742 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
743 changes = true;
55b40f3fa659 mod_storage_sql: Change prosodyarchive_index to be non-unique (fixes #1087)
Matthew Wild <mwild1@gmail.com>
parents: 9492
diff changeset
744 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
745 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
746 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
747 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
748 end
55b40f3fa659 mod_storage_sql: Change prosodyarchive_index to be non-unique (fixes #1087)
Matthew Wild <mwild1@gmail.com>
parents: 9492
diff changeset
749 end
55b40f3fa659 mod_storage_sql: Change prosodyarchive_index to be non-unique (fixes #1087)
Matthew Wild <mwild1@gmail.com>
parents: 9492
diff changeset
750 end
55b40f3fa659 mod_storage_sql: Change prosodyarchive_index to be non-unique (fixes #1087)
Matthew Wild <mwild1@gmail.com>
parents: 9492
diff changeset
751
55b40f3fa659 mod_storage_sql: Change prosodyarchive_index to be non-unique (fixes #1087)
Matthew Wild <mwild1@gmail.com>
parents: 9492
diff changeset
752 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
753 -- 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
754 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
755 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
756 changes = true;
55b40f3fa659 mod_storage_sql: Change prosodyarchive_index to be non-unique (fixes #1087)
Matthew Wild <mwild1@gmail.com>
parents: 9492
diff changeset
757 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
758 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
759 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
760 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
761 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
762 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
763 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
764 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
765 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
766 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
767 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
768 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
769 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
770 .."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
771 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
772 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
773 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
774
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
775 -- 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
776 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
777 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
778 FROM "information_schema"."columns"
8081
a201f315de55 mod_storage_sql: Break long lines
Kim Alvefur <zash@zash.se>
parents: 8080
diff changeset
779 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
780 AND "TABLE_SCHEMA" = ?
8081
a201f315de55 mod_storage_sql: Break long lines
Kim Alvefur <zash@zash.se>
parents: 8080
diff changeset
781 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
782 ]];
8038
cd6cef579e82 mod_storage_sql: Remove unused return values (should those actually be used?)
Kim Alvefur <zash@zash.se>
parents: 8037
diff changeset
783 -- 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
784 engine:transaction(function()
8082
8ca11201bfe7 mod_storage_sql: Limit encoding check to current database
Kim Alvefur <zash@zash.se>
parents: 8081
diff changeset
785 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
786 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
787 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
788 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
789 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
790 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
791 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
792 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
793 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
794 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
795 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
796 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
797 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
798 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
799 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
800 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
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);
8081
a201f315de55 mod_storage_sql: Break long lines
Kim Alvefur <zash@zash.se>
parents: 8080
diff changeset
803 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
804 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
805 engine.charset, engine.charset.."_bin");
8081
a201f315de55 mod_storage_sql: Break long lines
Kim Alvefur <zash@zash.se>
parents: 8080
diff changeset
806 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
807 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
808 module:log("error", "Failed to check/upgrade database encoding: %s", 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 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
812 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
813 end
685403a6fee1 mod_storage_sql: Add method for removing all data belonging to a user
Kim Alvefur <zash@zash.se>
parents: 5035
diff changeset
814
8035
58d6c2ab3d16 mod_storage_sql: Ignore name clash [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8034
diff changeset
815 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
816 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
817 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
818 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
819 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
820 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
821
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
822 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
823 return {
8036
7ebaefc88b3d mod_storage_sql: Split long lines [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8035
diff changeset
824 driver = assert(params.driver,
7ebaefc88b3d mod_storage_sql: Split long lines [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8035
diff changeset
825 "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
826 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
827 "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
828 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
829 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
830 host = params.host;
7753
c276d72d4e17 mod_storage_sql: Include missing parameter table key 'port'
Kim Alvefur <zash@zash.se>
parents: 7752
diff changeset
831 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
832 };
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_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 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
834
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_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 function module.load()
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
836 if prosody.prosodyctl then return; 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
837 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
838 local params = normalize_params(module:get_option("sql", default_params));
10793
9ce67da45e54 mod_storage_sql: Log database connection parameters when creating engine
Kim Alvefur <zash@zash.se>
parents: 10701
diff changeset
839 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
840 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
841 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
842 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
843 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
844 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
845 -- 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
846 -- 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
847 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
848 -- 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
849 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
850 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
851 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
852 end
10652
0c00274528a4 mod_storage_sql: Fix check for deletion limits (fixes #1494)
Kim Alvefur <zash@zash.se>
parents: 10012
diff changeset
853 end
10655
ee6c12264420 mod_storage_sql: Move update limit check back one step, fixes traceback
Kim Alvefur <zash@zash.se>
parents: 10652
diff changeset
854 if engine.params.driver == "SQLite3" then
ee6c12264420 mod_storage_sql: Move update limit check back one step, fixes traceback
Kim Alvefur <zash@zash.se>
parents: 10652
diff changeset
855 for row in engine:select("PRAGMA compile_options") do
ee6c12264420 mod_storage_sql: Move update limit check back one step, fixes traceback
Kim Alvefur <zash@zash.se>
parents: 10652
diff changeset
856 if row[1] == "ENABLE_UPDATE_DELETE_LIMIT" then
ee6c12264420 mod_storage_sql: Move update limit check back one step, fixes traceback
Kim Alvefur <zash@zash.se>
parents: 10652
diff changeset
857 engine._have_delete_limit = true;
ee6c12264420 mod_storage_sql: Move update limit check back one step, fixes traceback
Kim Alvefur <zash@zash.se>
parents: 10652
diff changeset
858 end
10012
acf4a7bfb6aa mod_storage_sql: Handle SQLite DELETE with LIMIT being optional (fixes #1359)
Kim Alvefur <zash@zash.se>
parents: 10011
diff changeset
859 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
860 end
10655
ee6c12264420 mod_storage_sql: Move update limit check back one step, fixes traceback
Kim Alvefur <zash@zash.se>
parents: 10652
diff changeset
861 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
862 engines[sql.db2uri(params)] = engine;
fb37aece3252 mod_storage_sql: Share SQL connections with same parameters across VirtualHosts (fixes #576)
Kim Alvefur <zash@zash.se>
parents: 7169
diff changeset
863 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
864
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
865 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
866 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
867
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
868 function module.command(arg)
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
869 local config = require "core.configmanager";
11625
04abe65b8067 mod_storage_sql: Fix for move of yes/no function (Thanks Kasim)
Kim Alvefur <zash@zash.se>
parents: 11354
diff changeset
870 local hi = require "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
871 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
872 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
873 -- 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
874 local uris = {};
8037
5a802653d50d mod_storage_sql: Ignore name clash [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8036
diff changeset
875 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
876 local params = normalize_params(config.get(host, "sql") or default_params);
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
877 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
878 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
879 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
880 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
881 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
882 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
883 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
884 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
885 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
886 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
887 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
888 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
889 -- 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
890 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
891 print("Checking "..params.database.."...");
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
892 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
893 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
894 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
895 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
896 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
897 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
898 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
899 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
900 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
901 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
902 end