prosody
7f4f834fae79
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 @ 12873:7f4f834fae79

mod_storage_sql: Silence luacheck warning
author Kim Alvefur <zash@zash.se>
date Mon, 30 Jan 2023 00:40:47 +0100
parent 12872:a20923f7d5fd
child 12875:a28f77018476
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
12846
1638991caed2 mod_storage_sql: Support SQLite3 without LuaDBI
Kim Alvefur <zash@zash.se>
parents: 12830
diff changeset
4 local deps = require "util.dependencies";
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
5 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
6 local json = require "util.json";
12846
1638991caed2 mod_storage_sql: Support SQLite3 without LuaDBI
Kim Alvefur <zash@zash.se>
parents: 12830
diff changeset
7 local sqlite = deps.softreq "util.sqlite3";
1638991caed2 mod_storage_sql: Support SQLite3 without LuaDBI
Kim Alvefur <zash@zash.se>
parents: 12830
diff changeset
8 local dbisql = (sqlite and deps.softreq or require) "util.sql";
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
9 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
10 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
11 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
12 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
13
7751
168919a947ed mod_storage_sql: Use is_stanza() from util.stanza
Kim Alvefur <zash@zash.se>
parents: 7305
diff changeset
14 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
15 local t_concat = table.concat;
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
16
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
17 local noop = function() end
12589
39ae08180c81 compat: Remove handling of Lua 5.1 location of 'unpack' function
Kim Alvefur <zash@zash.se>
parents: 12574
diff changeset
18 local unpack = table.unpack;
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
19 local function iterator(result)
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
20 return function(result_)
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
21 local row = result_();
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
22 if row ~= nil then
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
23 return unpack(row);
4105
08560575762f mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents: 4101
diff changeset
24 end
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
25 end, result, nil;
4105
08560575762f mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents: 4101
diff changeset
26 end
08560575762f mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents: 4101
diff changeset
27
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
28 local default_params = { driver = "SQLite3" };
5776
bd0ff8ae98a8 Remove all trailing whitespace
Florian Zeitz <florob@babelmonkeys.de>
parents: 5209
diff changeset
29
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
30 local engine;
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
31
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
32 local function serialize(value)
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
33 local t = type(value);
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
34 if t == "string" or t == "boolean" or t == "number" then
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
35 return t, tostring(value);
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
36 elseif is_stanza(value) then
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
37 return "xml", tostring(value);
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
38 elseif t == "table" then
8032
aa9f198cb3c9 mod_storage_sql: Rename variables to avoid name clashes [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8031
diff changeset
39 local encoded,err = json.encode(value);
8138
cb605fb60e32 mod_storage_sql: Fix logic error introduced by variable rename in aa9f198cb3c9 (thanks waqas)
Matthew Wild <mwild1@gmail.com>
parents: 8082
diff changeset
40 if encoded then return "json", encoded; end
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
41 return nil, err;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
42 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
43 return nil, "Unhandled value type: "..t;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
44 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
45 local function deserialize(t, value)
3743
5adfb8d0444d mod_storage_sql: Fixed the deserialization of string-typed values.
Waqas Hussain <waqas20@gmail.com>
parents: 3732
diff changeset
46 if t == "string" then return value;
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
47 elseif t == "boolean" then
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
48 if value == "true" then return true;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
49 elseif value == "false" then return false; end
8537
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors
Kim Alvefur <zash@zash.se>
parents: 8480
diff changeset
50 return nil, "invalid-boolean";
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors
Kim Alvefur <zash@zash.se>
parents: 8480
diff changeset
51 elseif t == "number" then
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors
Kim Alvefur <zash@zash.se>
parents: 8480
diff changeset
52 value = tonumber(value);
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors
Kim Alvefur <zash@zash.se>
parents: 8480
diff changeset
53 if value then return value; end
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors
Kim Alvefur <zash@zash.se>
parents: 8480
diff changeset
54 return nil, "invalid-number";
3772
e1f6fe098404 mod_storage_sql: Fix a couple of bugs in "JSON" decoding
Matthew Wild <mwild1@gmail.com>
parents: 3744
diff changeset
55 elseif t == "json" then
3980
6b2fac6602b3 mod_storage_sql: Use util.json instead of util.serialization.
Waqas Hussain <waqas20@gmail.com>
parents: 3978
diff changeset
56 return json.decode(value);
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
57 elseif t == "xml" then
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
58 return xml_parse(value);
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
59 end
8537
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors
Kim Alvefur <zash@zash.se>
parents: 8480
diff changeset
60 return nil, "Unhandled value type: "..t;
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
61 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
62
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
63 local host = module.host;
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
64 local user, store;
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
65
4007
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
66 local function keyval_store_get()
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
67 local haveany;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
68 local result = {};
8033
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
69 local select_sql = [[
8073
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
Kim Alvefur <zash@zash.se>
parents: 8050
diff changeset
70 SELECT "key","type","value"
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
Kim Alvefur <zash@zash.se>
parents: 8050
diff changeset
71 FROM "prosody"
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
Kim Alvefur <zash@zash.se>
parents: 8050
diff changeset
72 WHERE "host"=? AND "user"=? AND "store"=?;
8033
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
73 ]]
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
74 for row in engine:select(select_sql, host, user or "", store) do
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
75 haveany = true;
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
76 local k = row[1];
8537
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors
Kim Alvefur <zash@zash.se>
parents: 8480
diff changeset
77 local v, e = deserialize(row[2], row[3]);
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors
Kim Alvefur <zash@zash.se>
parents: 8480
diff changeset
78 assert(v ~= nil, e);
3977
6724853adb80 mod_storage_sql: Remove the subkey column from the Prosody table, and make the map store compatible with the key-value store.
Waqas Hussain <waqas20@gmail.com>
parents: 3976
diff changeset
79 if k and v then
6724853adb80 mod_storage_sql: Remove the subkey column from the Prosody table, and make the map store compatible with the key-value store.
Waqas Hussain <waqas20@gmail.com>
parents: 3976
diff changeset
80 if k ~= "" then result[k] = v; elseif type(v) == "table" then
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
81 for a,b in pairs(v) do
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
82 result[a] = b;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
83 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
84 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
85 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
86 end
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
87 if haveany then
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
88 return result;
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
89 end
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
90 end
4007
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
91 local function keyval_store_set(data)
8033
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
92 local delete_sql = [[
8073
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
Kim Alvefur <zash@zash.se>
parents: 8050
diff changeset
93 DELETE FROM "prosody"
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
Kim Alvefur <zash@zash.se>
parents: 8050
diff changeset
94 WHERE "host"=? AND "user"=? AND "store"=?
8033
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
95 ]];
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
96 engine:delete(delete_sql, host, user or "", store);
5776
bd0ff8ae98a8 Remove all trailing whitespace
Florian Zeitz <florob@babelmonkeys.de>
parents: 5209
diff changeset
97
8033
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
98 local insert_sql = [[
8073
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
Kim Alvefur <zash@zash.se>
parents: 8050
diff changeset
99 INSERT INTO "prosody"
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
Kim Alvefur <zash@zash.se>
parents: 8050
diff changeset
100 ("host","user","store","key","type","value")
8033
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
101 VALUES (?,?,?,?,?,?);
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
102 ]]
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
103 if data and next(data) ~= nil then
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
104 local extradata = {};
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
105 for key, value in pairs(data) do
3974
af40a7ce4f77 mod_storage_sql: Quote identifiers in SQL with backquotes, and use the empty string for NULL, and '=' instead of 'IS' for comparison, to work with MySQL's limitations...
Waqas Hussain <waqas20@gmail.com>
parents: 3772
diff changeset
106 if type(key) == "string" and key ~= "" then
8032
aa9f198cb3c9 mod_storage_sql: Rename variables to avoid name clashes [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8031
diff changeset
107 local t, encoded_value = assert(serialize(value));
8050
c0f81dea4662 mod_storage_sql: Fix to use correct SQL query (thanks carlos)
Kim Alvefur <zash@zash.se>
parents: 8038
diff changeset
108 engine:insert(insert_sql, host, user or "", store, key, t, encoded_value);
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
109 else
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
110 extradata[key] = value;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
111 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
112 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
113 if next(extradata) ~= nil then
8032
aa9f198cb3c9 mod_storage_sql: Rename variables to avoid name clashes [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8031
diff changeset
114 local t, encoded_extradata = assert(serialize(extradata));
8033
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
115 engine:insert(insert_sql, host, user or "", store, "", t, encoded_extradata);
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
116 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
117 end
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
118 return true;
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
119 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
120
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
121 --- Key/value store API (default store type)
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
122
4007
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
123 local keyval_store = {};
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
124 keyval_store.__index = keyval_store;
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
125 function keyval_store:get(username)
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
126 user, store = username, self.store;
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
127 local ok, result = engine:transaction(keyval_store_get);
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
128 if not ok then
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
129 module:log("error", "Unable to read from database %s store for %s: %s", store, username or "<host>", result);
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
130 return nil, result;
4105
08560575762f mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents: 4101
diff changeset
131 end
6953
b9276d677e76 mod_storage_sql: Whitespace fix (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6951
diff changeset
132 return result;
4007
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
133 end
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
134 function keyval_store:set(username, data)
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
135 user,store = username,self.store;
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
136 return engine:transaction(function()
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
137 return keyval_store_set(data);
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
138 end);
4007
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
139 end
5154
429c8eeea035 mod_storage_sql: Add support for iterating over users with data in a store
Kim Alvefur <zash@zash.se>
parents: 5133
diff changeset
140 function keyval_store:users()
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
141 local ok, result = engine:transaction(function()
8033
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
142 local select_sql = [[
8073
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
Kim Alvefur <zash@zash.se>
parents: 8050
diff changeset
143 SELECT DISTINCT "user"
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
Kim Alvefur <zash@zash.se>
parents: 8050
diff changeset
144 FROM "prosody"
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
Kim Alvefur <zash@zash.se>
parents: 8050
diff changeset
145 WHERE "host"=? AND "store"=?;
8033
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
146 ]];
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
147 return engine:select(select_sql, host, self.store);
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
148 end);
9492
c03c60a2dede mod_storage_sql: Have :users() throw an error on failure instead of returning non-iterator values
Matthew Wild <mwild1@gmail.com>
parents: 9479
diff changeset
149 if not ok then error(result); end
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
150 return iterator(result);
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
151 end
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
152
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
153 --- Archive store API
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
154
9896
dafe1b544822 mod_storage_sql: No archive item limit by default
Kim Alvefur <zash@zash.se>
parents: 9893
diff changeset
155 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
156 local archive_item_count_cache = cache.new(module:get_option("storage_archive_item_limit_cache_size", 1000));
f76bd399267c mod_storage_internal,_sql: Add limit to number of items in an archive store (fixes #733)
Matthew Wild <mwild1@gmail.com>
parents: 9691
diff changeset
157
10993
b5e7f4d533e2 mod_storage_sql: Measure hits/misses on the item count cache
Kim Alvefur <zash@zash.se>
parents: 10925
diff changeset
158 local item_count_cache_hit = module:measure("item_count_cache_hit", "rate");
b5e7f4d533e2 mod_storage_sql: Measure hits/misses on the item count cache
Kim Alvefur <zash@zash.se>
parents: 10925
diff changeset
159 local item_count_cache_miss = module:measure("item_count_cache_miss", "rate")
b5e7f4d533e2 mod_storage_sql: Measure hits/misses on the item count cache
Kim Alvefur <zash@zash.se>
parents: 10925
diff changeset
160
10033
ca8333d1a7fe mod_storage_sql: Ignore shadowed error variable [luacheck]
Kim Alvefur <zash@zash.se>
parents: 10032
diff changeset
161 -- luacheck: ignore 512 431/user 431/store 431/err
6954
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
162 local map_store = {};
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
163 map_store.__index = map_store;
7153
89fa66d4e502 mod_storage_sql: Implement map:set_keys, allowing multiple keys to be set in the same transaction
Kim Alvefur <zash@zash.se>
parents: 7149
diff changeset
164 map_store.remove = {};
6954
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
165 function map_store:get(username, key)
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
166 local ok, result = engine:transaction(function()
8033
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
167 local query = [[
8073
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
Kim Alvefur <zash@zash.se>
parents: 8050
diff changeset
168 SELECT "type", "value"
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
Kim Alvefur <zash@zash.se>
parents: 8050
diff changeset
169 FROM "prosody"
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
Kim Alvefur <zash@zash.se>
parents: 8050
diff changeset
170 WHERE "host"=? AND "user"=? AND "store"=? AND "key"=?
8033
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
171 LIMIT 1
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
172 ]];
8537
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors
Kim Alvefur <zash@zash.se>
parents: 8480
diff changeset
173 local data, err;
6954
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
174 if type(key) == "string" and key ~= "" then
8033
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
175 for row in engine:select(query, host, username or "", self.store, key) do
8537
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors
Kim Alvefur <zash@zash.se>
parents: 8480
diff changeset
176 data, err = deserialize(row[1], row[2]);
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors
Kim Alvefur <zash@zash.se>
parents: 8480
diff changeset
177 assert(data ~= nil, err);
6954
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
178 end
7274
e0727512bb99 mod_storage_sql: Allow loops over results to end on their own
Kim Alvefur <zash@zash.se>
parents: 7273
diff changeset
179 return data;
6954
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
180 else
8033
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
181 for row in engine:select(query, host, username or "", self.store, "") do
8537
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors
Kim Alvefur <zash@zash.se>
parents: 8480
diff changeset
182 data, err = deserialize(row[1], row[2]);
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors
Kim Alvefur <zash@zash.se>
parents: 8480
diff changeset
183 assert(data ~= nil, err);
7149
bb0fd02ae70f mod_storage_sql: Support non-string keys in map store
Kim Alvefur <zash@zash.se>
parents: 7008
diff changeset
184 end
7274
e0727512bb99 mod_storage_sql: Allow loops over results to end on their own
Kim Alvefur <zash@zash.se>
parents: 7273
diff changeset
185 return data and data[key] or nil;
6954
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
186 end
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
187 end);
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
188 if not ok then return nil, result; end
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
189 return result;
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
190 end
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
191 function map_store:set(username, key, data)
7169
d00d8cfcc9a8 mod_storage_sql: Fix setting value to false in map store
Kim Alvefur <zash@zash.se>
parents: 7156
diff changeset
192 if data == nil then data = self.remove; end
d00d8cfcc9a8 mod_storage_sql: Fix setting value to false in map store
Kim Alvefur <zash@zash.se>
parents: 7156
diff changeset
193 return self:set_keys(username, { [key] = data });
7153
89fa66d4e502 mod_storage_sql: Implement map:set_keys, allowing multiple keys to be set in the same transaction
Kim Alvefur <zash@zash.se>
parents: 7149
diff changeset
194 end
89fa66d4e502 mod_storage_sql: Implement map:set_keys, allowing multiple keys to be set in the same transaction
Kim Alvefur <zash@zash.se>
parents: 7149
diff changeset
195 function map_store:set_keys(username, keydatas)
6954
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
196 local ok, result = engine:transaction(function()
8033
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
197 local delete_sql = [[
8073
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
Kim Alvefur <zash@zash.se>
parents: 8050
diff changeset
198 DELETE FROM "prosody"
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
Kim Alvefur <zash@zash.se>
parents: 8050
diff changeset
199 WHERE "host"=? AND "user"=? AND "store"=? AND "key"=?;
8033
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
200 ]];
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
201 local insert_sql = [[
8073
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
Kim Alvefur <zash@zash.se>
parents: 8050
diff changeset
202 INSERT INTO "prosody"
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
Kim Alvefur <zash@zash.se>
parents: 8050
diff changeset
203 ("host","user","store","key","type","value")
8033
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
204 VALUES (?,?,?,?,?,?);
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
205 ]];
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
206 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
207 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
208 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
209 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
210 LIMIT 1;
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
211 ]];
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
212 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
213 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
214 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
215 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
216 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
217 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
218 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
219 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
220 else
8537
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors
Kim Alvefur <zash@zash.se>
parents: 8480
diff changeset
221 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
222 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
223 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
224 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
225 end
8033
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
226 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
227 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
228 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
229 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
230 end
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 return true;
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
233 end);
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
234 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
235 return result;
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
236 end
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
237
10680
19692fc5c106 storagemanager, mod_storage_sql: Rename methods to :get_all() and :delete_all()
Matthew Wild <mwild1@gmail.com>
parents: 10677
diff changeset
238 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
239 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
240 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
241 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
242 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
243 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
244 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
245 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
246 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
247 ]];
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
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 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
250 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
251 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
252 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
253 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
254 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 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
257 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
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 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
260
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 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
262 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
263 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
264 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
265
10680
19692fc5c106 storagemanager, mod_storage_sql: Rename methods to :get_all() and :delete_all()
Matthew Wild <mwild1@gmail.com>
parents: 10677
diff changeset
266 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
267 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
268 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
269 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
270 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
271 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
272 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
273 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
274 ]];
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 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
276 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
277 end);
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests)
Matthew Wild <mwild1@gmail.com>
parents: 10656
diff changeset
278 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
279 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
280 end
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests)
Matthew Wild <mwild1@gmail.com>
parents: 10656
diff changeset
281
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_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 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
283 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
284 total = true;
9884
9751c17f5281 mod_storage_internal,_sql: Expose archive capabilities feature set
Kim Alvefur <zash@zash.se>
parents: 9883
diff changeset
285 quota = archive_item_limit;
9751c17f5281 mod_storage_internal,_sql: Expose archive capabilities feature set
Kim Alvefur <zash@zash.se>
parents: 9883
diff changeset
286 truncate = true;
11271
41a962b72a6e mod_storage_sql: Advertise support for id range query
Kim Alvefur <zash@zash.se>
parents: 10993
diff changeset
287 full_id_range = true;
11279
f225070e2b8a mod_storage_sql: Support query for set of IDs
Kim Alvefur <zash@zash.se>
parents: 11271
diff changeset
288 ids = true;
11967
0f2b5182e80b mod_storage_sql: Expose multi-user deletion capability
Kim Alvefur <zash@zash.se>
parents: 11625
diff changeset
289 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
290 };
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_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 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
292 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
293 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
294 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
295 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
296 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
297 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
298 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
299 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
300 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
301 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
302 ]];
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 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
304 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
305 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
306 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
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 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
309 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
310 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
311 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
312 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
313 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
314 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
315 else
b5e7f4d533e2 mod_storage_sql: Measure hits/misses on the item count cache
Kim Alvefur <zash@zash.se>
parents: 10925
diff changeset
316 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
317 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
318
9896
dafe1b544822 mod_storage_sql: No archive item limit by default
Kim Alvefur <zash@zash.se>
parents: 9893
diff changeset
319 if archive_item_limit then
dafe1b544822 mod_storage_sql: No archive item limit by default
Kim Alvefur <zash@zash.se>
parents: 9893
diff changeset
320 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
321 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
322 return nil, "quota-limit";
dafe1b544822 mod_storage_sql: No archive item limit by default
Kim Alvefur <zash@zash.se>
parents: 9893
diff changeset
323 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
324 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
325
12631
9524bb7f3944 mod_storage_sql: Drop archive timestamp precision pending schema update
Kim Alvefur <zash@zash.se>
parents: 12600
diff changeset
326 -- FIXME update the schema to allow precision timestamps
9524bb7f3944 mod_storage_sql: Drop archive timestamp precision pending schema update
Kim Alvefur <zash@zash.se>
parents: 12600
diff changeset
327 when = when and math.floor(when) or os.time();
8031
ef838b7f8f53 mod_storage_sql: Sensible defaults for 'when' and 'with' arguments
Kim Alvefur <zash@zash.se>
parents: 8029
diff changeset
328 with = with or "";
8032
aa9f198cb3c9 mod_storage_sql: Rename variables to avoid name clashes [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8031
diff changeset
329 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
330 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
331 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
332 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
333 ]];
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
334 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
335 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
336 ("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
337 VALUES (?,?,?,?,?,?,?,?);
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
338 ]];
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
339 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
340 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
341 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
342 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
343 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
344 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
345 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
346 end
8032
aa9f198cb3c9 mod_storage_sql: Rename variables to avoid name clashes [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8031
diff changeset
347 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
348 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
349 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
350 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
351 end);
8032
aa9f198cb3c9 mod_storage_sql: Rename variables to avoid name clashes [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8031
diff changeset
352 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
353 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
354 end
4007
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
355
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_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 -- 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
357 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
358 -- 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
359 if query.start then
12723
4cfd09343947 mod_storage_sql: Strip timestamp precision in queries to fix error (thanks muppeth)
Kim Alvefur <zash@zash.se>
parents: 12631
diff changeset
360 args[#args+1] = math.floor(query.start);
8073
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
Kim Alvefur <zash@zash.se>
parents: 8050
diff changeset
361 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
362 end
5776
bd0ff8ae98a8 Remove all trailing whitespace
Florian Zeitz <florob@babelmonkeys.de>
parents: 5209
diff changeset
363
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
364 if query["end"] then
12723
4cfd09343947 mod_storage_sql: Strip timestamp precision in queries to fix error (thanks muppeth)
Kim Alvefur <zash@zash.se>
parents: 12631
diff changeset
365 args[#args+1] = math.floor(query["end"]);
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
366 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
367 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
368 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
369 where[#where+1] = "\"when\" <= ?"
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
370 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
371 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
372
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_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 -- 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
374 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
375 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
376 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
377 end
5776
bd0ff8ae98a8 Remove all trailing whitespace
Florian Zeitz <florob@babelmonkeys.de>
parents: 5209
diff changeset
378
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_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 -- 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
380 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
381 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
382 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
383 end
11279
f225070e2b8a mod_storage_sql: Support query for set of IDs
Kim Alvefur <zash@zash.se>
parents: 11271
diff changeset
384
f225070e2b8a mod_storage_sql: Support query for set of IDs
Kim Alvefur <zash@zash.se>
parents: 11271
diff changeset
385 -- Set of ids
f225070e2b8a mod_storage_sql: Support query for set of IDs
Kim Alvefur <zash@zash.se>
parents: 11271
diff changeset
386 if query.ids then
f225070e2b8a mod_storage_sql: Support query for set of IDs
Kim Alvefur <zash@zash.se>
parents: 11271
diff changeset
387 local nids, nargs = #query.ids, #args;
12574
18d33668c5fa mod_storage_sql: Remove Lua 5.1 compatibility hack
Kim Alvefur <zash@zash.se>
parents: 11971
diff changeset
388 where[#where + 1] = "\"key\" IN (" .. string.rep("?", nids, ",") .. ")";
11279
f225070e2b8a mod_storage_sql: Support query for set of IDs
Kim Alvefur <zash@zash.se>
parents: 11271
diff changeset
389 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
390 args[nargs+i] = id;
f225070e2b8a mod_storage_sql: Support query for set of IDs
Kim Alvefur <zash@zash.se>
parents: 11271
diff changeset
391 end
f225070e2b8a mod_storage_sql: Support query for set of IDs
Kim Alvefur <zash@zash.se>
parents: 11271
diff changeset
392 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
393 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
394 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
395 -- 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
396 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
397 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
398 FROM "prosodyarchive"
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325)
Kim Alvefur <zash@zash.se>
parents: 10017
diff changeset
399 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
400 LIMIT 1;
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325)
Kim Alvefur <zash@zash.se>
parents: 10017
diff changeset
401 ]];
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
402 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
403 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
404 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
405 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
406 end
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325)
Kim Alvefur <zash@zash.se>
parents: 10017
diff changeset
407 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
408 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
409 end
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325)
Kim Alvefur <zash@zash.se>
parents: 10017
diff changeset
410 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
411 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
412 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
413 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
414 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
415 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
416 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
417 end
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325)
Kim Alvefur <zash@zash.se>
parents: 10017
diff changeset
418 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
419 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
420 end
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325)
Kim Alvefur <zash@zash.se>
parents: 10017
diff changeset
421 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
422 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
423 end
10020
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325)
Kim Alvefur <zash@zash.se>
parents: 10017
diff changeset
424 return true;
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
425 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
426
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_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 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
428 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
429 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
430 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
431 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
432 (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
433 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
434 -- 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
435 -- 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
436 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
437 else
0b350909da24 mod_storage_sql: Return cached total where it makes sense
Kim Alvefur <zash@zash.se>
parents: 11970
diff changeset
438 -- 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
439 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
440 end
10031
17c175ad65f9 mod_storage_sql: Correctly return item-not-found error
Kim Alvefur <zash@zash.se>
parents: 10020
diff changeset
441 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
442 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
443 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
444 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
445 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
446 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
447 ]];
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_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 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
449 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
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 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
452
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
453 -- Total matching
11971
0b350909da24 mod_storage_sql: Return cached total where it makes sense
Kim Alvefur <zash@zash.se>
parents: 11970
diff changeset
454 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
455
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
456 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
457 .. 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
458 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
459 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
460 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
461 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
462 end
11970
f0e78fa8f24c mod_storage_sql: Adjust handling of 'total' for multi-id queries
Kim Alvefur <zash@zash.se>
parents: 11969
diff changeset
463 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
464 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
465 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
466 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
467 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
468 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
469 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
470
10020
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325)
Kim Alvefur <zash@zash.se>
parents: 10017
diff changeset
471 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
472 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
473
8033
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
474 sql_query = sql_query:format(t_concat(where, " AND "), query.reverse
12829
fba795dd99c8 mod_storage_sql: Fix #1639
Kim Alvefur <zash@zash.se>
parents: 12823
diff changeset
475 and "DESC" or "ASC", query.limit and " LIMIT " .. query.limit or "");
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
476 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
477 end);
10031
17c175ad65f9 mod_storage_sql: Correctly return item-not-found error
Kim Alvefur <zash@zash.se>
parents: 10020
diff changeset
478 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
479 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
480 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
481 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
482 if row ~= nil then
8537
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors
Kim Alvefur <zash@zash.se>
parents: 8480
diff changeset
483 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
484 assert(value ~= nil, err);
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors
Kim Alvefur <zash@zash.se>
parents: 8480
diff changeset
485 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
486 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
487 end, total;
4007
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
488 end
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
489
11354
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
490 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
491 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
492 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
493 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
494 return stanza, when, with;
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
495 end
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
496 return nil, "item-not-found";
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
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
499 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
500 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
501 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
502
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
503 local update_query = [[
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
504 UPDATE "prosodyarchive"
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
505 SET %s
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
506 WHERE %s
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
507 ]];
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
508 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
509 local setf = {};
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
510 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
511
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
512 if new_value then
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
513 table.insert(setf, '"type" = ?')
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
514 table.insert(setf, '"value" = ?')
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
515 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
516 table.insert(args, 1, t);
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
517 table.insert(args, 2, value);
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
518 end
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
519
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
520 if new_when then
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
521 table.insert(setf, 1, '"when" = ?')
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
522 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
523 end
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
524
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
525 if new_with then
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
526 table.insert(setf, 1, '"with" = ?')
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
527 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
528 end
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
529
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
530 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
531 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
532 end);
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
533 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
534 return result:affected() == 1;
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
535 end
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
536
9904
bf061f5512f7 mod_storage_sql: Implement archive summary API
Kim Alvefur <zash@zash.se>
parents: 9896
diff changeset
537 function archive_store:summary(username, query)
bf061f5512f7 mod_storage_sql: Implement archive summary API
Kim Alvefur <zash@zash.se>
parents: 9896
diff changeset
538 query = query or {};
bf061f5512f7 mod_storage_sql: Implement archive summary API
Kim Alvefur <zash@zash.se>
parents: 9896
diff changeset
539 local user,store = username,self.store;
bf061f5512f7 mod_storage_sql: Implement archive summary API
Kim Alvefur <zash@zash.se>
parents: 9896
diff changeset
540 local ok, result = engine:transaction(function()
bf061f5512f7 mod_storage_sql: Implement archive summary API
Kim Alvefur <zash@zash.se>
parents: 9896
diff changeset
541 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
542 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
543 FROM "prosodyarchive"
bf061f5512f7 mod_storage_sql: Implement archive summary API
Kim Alvefur <zash@zash.se>
parents: 9896
diff changeset
544 WHERE %s
12599
5aafcf6a0730 mod_storage_sql: Fix summary API with Postgres (fixes #1766)
Kim Alvefur <zash@zash.se>
parents: 12592
diff changeset
545 GROUP BY "with";
9904
bf061f5512f7 mod_storage_sql: Implement archive summary API
Kim Alvefur <zash@zash.se>
parents: 9896
diff changeset
546 ]];
bf061f5512f7 mod_storage_sql: Implement archive summary API
Kim Alvefur <zash@zash.se>
parents: 9896
diff changeset
547 local args = { host, user or "", store, };
bf061f5512f7 mod_storage_sql: Implement archive summary API
Kim Alvefur <zash@zash.se>
parents: 9896
diff changeset
548 local where = { "\"host\" = ?", "\"user\" = ?", "\"store\" = ?", };
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 archive_where(query, args, where);
bf061f5512f7 mod_storage_sql: Implement archive summary API
Kim Alvefur <zash@zash.se>
parents: 9896
diff changeset
551
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
552 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
553
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
554 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
555 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
556 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
557
12599
5aafcf6a0730 mod_storage_sql: Fix summary API with Postgres (fixes #1766)
Kim Alvefur <zash@zash.se>
parents: 12592
diff changeset
558 sql_query = sql_query:format(t_concat(where, " AND "));
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
559 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
560 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
561 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
562 local counts = {};
10222
51f145094648 mod_storage_*: Also include timestmap of first message in :summary API
Kim Alvefur <zash@zash.se>
parents: 10221
diff changeset
563 local earliest, latest = {}, {};
9904
bf061f5512f7 mod_storage_sql: Implement archive summary API
Kim Alvefur <zash@zash.se>
parents: 9896
diff changeset
564 for row in result do
bf061f5512f7 mod_storage_sql: Implement archive summary API
Kim Alvefur <zash@zash.se>
parents: 9896
diff changeset
565 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
566 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
567 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
568 latest[with] = row[4];
9904
bf061f5512f7 mod_storage_sql: Implement archive summary API
Kim Alvefur <zash@zash.se>
parents: 9896
diff changeset
569 end
10220
1e2b444acb72 mod_storage_*: Tweak :summary API to allow future expansion with more fields
Kim Alvefur <zash@zash.se>
parents: 10033
diff changeset
570 return {
1e2b444acb72 mod_storage_*: Tweak :summary API to allow future expansion with more fields
Kim Alvefur <zash@zash.se>
parents: 10033
diff changeset
571 counts = counts;
10222
51f145094648 mod_storage_*: Also include timestmap of first message in :summary API
Kim Alvefur <zash@zash.se>
parents: 10221
diff changeset
572 earliest = earliest;
10221
068692cb9e78 mod_storage_*: Include timestamp of latest message in :summary API
Kim Alvefur <zash@zash.se>
parents: 10220
diff changeset
573 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
574 };
4007
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
575 end
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
576
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
577 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
578 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
579 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
580 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
581 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
582 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
583 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
584 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
585 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
586 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
587 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
588 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
589 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
590 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
591 if query.truncate == nil then
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
592 sql_query = sql_query:format(t_concat(where, " AND "));
12829
fba795dd99c8 mod_storage_sql: Fix #1639
Kim Alvefur <zash@zash.se>
parents: 12823
diff changeset
593 elseif engine.params.driver == "MySQL" then
fba795dd99c8 mod_storage_sql: Fix #1639
Kim Alvefur <zash@zash.se>
parents: 12823
diff changeset
594 sql_query = [[
fba795dd99c8 mod_storage_sql: Fix #1639
Kim Alvefur <zash@zash.se>
parents: 12823
diff changeset
595 DELETE result FROM prosodyarchive AS result JOIN (
fba795dd99c8 mod_storage_sql: Fix #1639
Kim Alvefur <zash@zash.se>
parents: 12823
diff changeset
596 SELECT sort_id FROM prosodyarchive
fba795dd99c8 mod_storage_sql: Fix #1639
Kim Alvefur <zash@zash.se>
parents: 12823
diff changeset
597 WHERE %s
fba795dd99c8 mod_storage_sql: Fix #1639
Kim Alvefur <zash@zash.se>
parents: 12823
diff changeset
598 ORDER BY "sort_id" %s
fba795dd99c8 mod_storage_sql: Fix #1639
Kim Alvefur <zash@zash.se>
parents: 12823
diff changeset
599 LIMIT 18446744073709551615 OFFSET %s
fba795dd99c8 mod_storage_sql: Fix #1639
Kim Alvefur <zash@zash.se>
parents: 12823
diff changeset
600 ) AS limiter on result.sort_id = limiter.sort_id;]];
fba795dd99c8 mod_storage_sql: Fix #1639
Kim Alvefur <zash@zash.se>
parents: 12823
diff changeset
601
fba795dd99c8 mod_storage_sql: Fix #1639
Kim Alvefur <zash@zash.se>
parents: 12823
diff changeset
602 sql_query = string.format(sql_query, t_concat(where, " AND "),
fba795dd99c8 mod_storage_sql: Fix #1639
Kim Alvefur <zash@zash.se>
parents: 12823
diff changeset
603 query.reverse and "ASC" or "DESC", query.truncate);
8395
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
604 else
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
605 args[#args+1] = query.truncate;
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
606 local unlimited = "ALL";
10011
2408e6362c15 mod_storage_sql: Move code out of if-else chain
Kim Alvefur <zash@zash.se>
parents: 9628
diff changeset
607 sql_query = [[
2408e6362c15 mod_storage_sql: Move code out of if-else chain
Kim Alvefur <zash@zash.se>
parents: 9628
diff changeset
608 DELETE FROM "prosodyarchive"
2408e6362c15 mod_storage_sql: Move code out of if-else chain
Kim Alvefur <zash@zash.se>
parents: 9628
diff changeset
609 WHERE "sort_id" IN (
2408e6362c15 mod_storage_sql: Move code out of if-else chain
Kim Alvefur <zash@zash.se>
parents: 9628
diff changeset
610 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
611 WHERE %s
2408e6362c15 mod_storage_sql: Move code out of if-else chain
Kim Alvefur <zash@zash.se>
parents: 9628
diff changeset
612 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
613 LIMIT %s OFFSET ?
2408e6362c15 mod_storage_sql: Move code out of if-else chain
Kim Alvefur <zash@zash.se>
parents: 9628
diff changeset
614 );]];
8395
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
615 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
616 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
617 sql_query = [[
acf4a7bfb6aa mod_storage_sql: Handle SQLite DELETE with LIMIT being optional (fixes #1359)
Kim Alvefur <zash@zash.se>
parents: 10011
diff changeset
618 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
619 WHERE %s
acf4a7bfb6aa mod_storage_sql: Handle SQLite DELETE with LIMIT being optional (fixes #1359)
Kim Alvefur <zash@zash.se>
parents: 10011
diff changeset
620 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
621 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
622 ]];
acf4a7bfb6aa mod_storage_sql: Handle SQLite DELETE with LIMIT being optional (fixes #1359)
Kim Alvefur <zash@zash.se>
parents: 10011
diff changeset
623 end
8395
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
624 unlimited = "-1";
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
625 end
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
626 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
627 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
628 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
629 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
630 end);
11969
395dce95508d mod_storage_sql: Fix traceback on multi-user deletion
Kim Alvefur <zash@zash.se>
parents: 11967
diff changeset
631 if username == true then
395dce95508d mod_storage_sql: Fix traceback on multi-user deletion
Kim Alvefur <zash@zash.se>
parents: 11967
diff changeset
632 archive_item_count_cache:clear();
395dce95508d mod_storage_sql: Fix traceback on multi-user deletion
Kim Alvefur <zash@zash.se>
parents: 11967
diff changeset
633 else
395dce95508d mod_storage_sql: Fix traceback on multi-user deletion
Kim Alvefur <zash@zash.se>
parents: 11967
diff changeset
634 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
635 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
636 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
637 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
638 end
5776
bd0ff8ae98a8 Remove all trailing whitespace
Florian Zeitz <florob@babelmonkeys.de>
parents: 5209
diff changeset
639
10002
b6b5b9d7417d mod_storage_sql: Add support for iterating over users in archive stores
Kim Alvefur <zash@zash.se>
parents: 9904
diff changeset
640 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
641 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
642 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
643 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
644 FROM "prosodyarchive"
b6b5b9d7417d mod_storage_sql: Add support for iterating over users in archive stores
Kim Alvefur <zash@zash.se>
parents: 9904
diff changeset
645 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
646 ]];
b6b5b9d7417d mod_storage_sql: Add support for iterating over users in archive stores
Kim Alvefur <zash@zash.se>
parents: 9904
diff changeset
647 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
648 end);
b6b5b9d7417d mod_storage_sql: Add support for iterating over users in archive stores
Kim Alvefur <zash@zash.se>
parents: 9904
diff changeset
649 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
650 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
651 end
b6b5b9d7417d mod_storage_sql: Add support for iterating over users in archive stores
Kim Alvefur <zash@zash.se>
parents: 9904
diff changeset
652
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
653 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
654 keyval = keyval_store;
6954
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
655 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
656 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
657 };
5776
bd0ff8ae98a8 Remove all trailing whitespace
Florian Zeitz <florob@babelmonkeys.de>
parents: 5209
diff changeset
658
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_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 --- Implement storage driver API
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 -- 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
662
5122
b41c33dc7c36 mod_storage_*: Don't explicitly set driver name, to ease copying/renaming modules.
Waqas Hussain <waqas20@gmail.com>
parents: 5121
diff changeset
663 local driver = {};
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
664
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
665 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
666 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
667 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
668 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
669 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
670 return nil, "unsupported-store";
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
671 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
672
5132
ce6546f867f9 mod_storage_sql: Remove obsolete comment
Matthew Wild <mwild1@gmail.com>
parents: 5130
diff changeset
673 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
674 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
675 (username == true and "!=?" or "=?");
5035
874cab7b4b3e mod_storage_sql: Add method for listing stores
Kim Alvefur <zash@zash.se>
parents: 5034
diff changeset
676 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
677 username = "";
874cab7b4b3e mod_storage_sql: Add method for listing stores
Kim Alvefur <zash@zash.se>
parents: 5034
diff changeset
678 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
679 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
680 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
681 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
682 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
683 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
684 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
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_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 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
687 return engine:transaction(function()
8292
2fc8b83dd736 mod_storage_sql: Remove return values from purge method
Kim Alvefur <zash@zash.se>
parents: 8138
diff changeset
688 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
689 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
690 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
691 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
692
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_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 --- 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
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
9459
6c279302fff4 mod_storage_sql: Remove unused function argument [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8537
diff changeset
696 local function create_table(engine) -- luacheck: ignore 431/engine
12846
1638991caed2 mod_storage_sql: Support SQLite3 without LuaDBI
Kim Alvefur <zash@zash.se>
parents: 12830
diff changeset
697 local sql = engine.params.driver == "SQLite3" and sqlite or dbisql;
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
698 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
699
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_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 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
701 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
702 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
703 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
704 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
705 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
706 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
707 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
708 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
709 };
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_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 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
711 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
712 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
713
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_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 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
715 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
716 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
717 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
718 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
719 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
720 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
721 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
722 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
723 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
724 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
725 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
726 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
727 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
728 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
729 };
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
730 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
731 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
732 end);
5035
874cab7b4b3e mod_storage_sql: Add method for listing stores
Kim Alvefur <zash@zash.se>
parents: 5034
diff changeset
733 end
874cab7b4b3e mod_storage_sql: Add method for listing stores
Kim Alvefur <zash@zash.se>
parents: 5034
diff changeset
734
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
735 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
736 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
737 if params.driver == "MySQL" then
12846
1638991caed2 mod_storage_sql: Support SQLite3 without LuaDBI
Kim Alvefur <zash@zash.se>
parents: 12830
diff changeset
738 local sql = dbisql;
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
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()
7170
fb37aece3252 mod_storage_sql: Share SQL connections with same parameters across VirtualHosts (fixes #576)
Kim Alvefur <zash@zash.se>
parents: 7169
diff changeset
836 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
837 local params = normalize_params(module:get_option("sql", default_params));
12846
1638991caed2 mod_storage_sql: Support SQLite3 without LuaDBI
Kim Alvefur <zash@zash.se>
parents: 12830
diff changeset
838 local sql = params.driver == "SQLite3" and sqlite or dbisql;
10793
9ce67da45e54 mod_storage_sql: Log database connection parameters when creating engine
Kim Alvefur <zash@zash.se>
parents: 10701
diff changeset
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);
12872
a20923f7d5fd mod_storage_sql: Record connection to database as module status
Kim Alvefur <zash@zash.se>
parents: 12846
diff changeset
843 module:log_status("debug", "Creating new engine for "..params.driver);
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
844 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
845 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
846 -- 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
847 -- 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
848 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
849 -- 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
850 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
851 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
852 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
853 end
10652
0c00274528a4 mod_storage_sql: Fix check for deletion limits (fixes #1494)
Kim Alvefur <zash@zash.se>
parents: 10012
diff changeset
854 end
10655
ee6c12264420 mod_storage_sql: Move update limit check back one step, fixes traceback
Kim Alvefur <zash@zash.se>
parents: 10652
diff changeset
855 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
856 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
857 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
858 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
859 end
10012
acf4a7bfb6aa mod_storage_sql: Handle SQLite DELETE with LIMIT being optional (fixes #1359)
Kim Alvefur <zash@zash.se>
parents: 10011
diff changeset
860 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
861 end
12872
a20923f7d5fd mod_storage_sql: Record connection to database as module status
Kim Alvefur <zash@zash.se>
parents: 12846
diff changeset
862 module:set_status("info", "Connected to " .. engine.params.driver);
12873
7f4f834fae79 mod_storage_sql: Silence luacheck warning
Kim Alvefur <zash@zash.se>
parents: 12872
diff changeset
863 end, function (engine) -- luacheck: ignore 431/engine
12872
a20923f7d5fd mod_storage_sql: Record connection to database as module status
Kim Alvefur <zash@zash.se>
parents: 12846
diff changeset
864 module:set_status("error", "Disconnected from " .. engine.params.driver);
10655
ee6c12264420 mod_storage_sql: Move update limit check back one step, fixes traceback
Kim Alvefur <zash@zash.se>
parents: 10652
diff changeset
865 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
866 engines[sql.db2uri(params)] = engine;
12872
a20923f7d5fd mod_storage_sql: Record connection to database as module status
Kim Alvefur <zash@zash.se>
parents: 12846
diff changeset
867 else
a20923f7d5fd mod_storage_sql: Record connection to database as module status
Kim Alvefur <zash@zash.se>
parents: 12846
diff changeset
868 module:set_status("info", "Using existing engine");
7170
fb37aece3252 mod_storage_sql: Share SQL connections with same parameters across VirtualHosts (fixes #576)
Kim Alvefur <zash@zash.se>
parents: 7169
diff changeset
869 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
870
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_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 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
872 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
873
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_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 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
875 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
876 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
877 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
878 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
879 -- 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
880 local uris = {};
8037
5a802653d50d mod_storage_sql: Ignore name clash [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8036
diff changeset
881 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
882 local params = normalize_params(config.get(host, "sql") or default_params);
12846
1638991caed2 mod_storage_sql: Support SQLite3 without LuaDBI
Kim Alvefur <zash@zash.se>
parents: 12830
diff changeset
883 local sql = engine.params.driver == "SQLite3" and sqlite or dbisql;
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
884 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
885 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
886 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
887 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
888 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
889 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
890 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
891 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
892 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
893 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
894 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
895 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
896 -- 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
897 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
898 print("Checking "..params.database.."...");
12846
1638991caed2 mod_storage_sql: Support SQLite3 without LuaDBI
Kim Alvefur <zash@zash.se>
parents: 12830
diff changeset
899 local sql = params.driver == "SQLite3" and sqlite or dbisql;
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
900 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
901 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
902 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
903 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
904 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
905 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
906 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
907 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
908 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
909 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
910 end