prosody
706f7d1affc2
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 @ 13159:706f7d1affc2

mod_storage_sql: Fix column name in index check for PostgreSQL Forgot to change the column name in 9a7523ea45cb
author Kim Alvefur <zash@zash.se>
date Sat, 17 Jun 2023 13:37:33 +0200
parent 13158:cf2a907ac130
child 13190:9dc6de46dae3
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
12977
74b9e05af71e plugins: Prefix module imports with prosody namespace
Kim Alvefur <zash@zash.se>
parents: 12875
diff changeset
4 local deps = require "prosody.util.dependencies";
74b9e05af71e plugins: Prefix module imports with prosody namespace
Kim Alvefur <zash@zash.se>
parents: 12875
diff changeset
5 local cache = require "prosody.util.cache";
74b9e05af71e plugins: Prefix module imports with prosody namespace
Kim Alvefur <zash@zash.se>
parents: 12875
diff changeset
6 local json = require "prosody.util.json";
74b9e05af71e plugins: Prefix module imports with prosody namespace
Kim Alvefur <zash@zash.se>
parents: 12875
diff changeset
7 local sqlite = deps.softreq "prosody.util.sqlite3";
74b9e05af71e plugins: Prefix module imports with prosody namespace
Kim Alvefur <zash@zash.se>
parents: 12875
diff changeset
8 local dbisql = (sqlite and deps.softreq or require) "prosody.util.sql";
74b9e05af71e plugins: Prefix module imports with prosody namespace
Kim Alvefur <zash@zash.se>
parents: 12875
diff changeset
9 local xml_parse = require "prosody.util.xml".parse;
74b9e05af71e plugins: Prefix module imports with prosody namespace
Kim Alvefur <zash@zash.se>
parents: 12875
diff changeset
10 local uuid = require "prosody.util.uuid";
74b9e05af71e plugins: Prefix module imports with prosody namespace
Kim Alvefur <zash@zash.se>
parents: 12875
diff changeset
11 local resolve_relative_path = require "prosody.util.paths".resolve_relative_path;
74b9e05af71e plugins: Prefix module imports with prosody namespace
Kim Alvefur <zash@zash.se>
parents: 12875
diff changeset
12 local jid_join = require "prosody.util.jid".join;
4096
3b991ceb228e mod_storage_sql: Make the 'database' value for the SQLite3 driver relative to the data path
Matthew Wild <mwild1@gmail.com>
parents: 4007
diff changeset
13
12977
74b9e05af71e plugins: Prefix module imports with prosody namespace
Kim Alvefur <zash@zash.se>
parents: 12875
diff changeset
14 local is_stanza = require"prosody.util.stanza".is_stanza;
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
15 local t_concat = table.concat;
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
16
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
17 local noop = function() end
12589
39ae08180c81 compat: Remove handling of Lua 5.1 location of 'unpack' function
Kim Alvefur <zash@zash.se>
parents: 12574
diff changeset
18 local unpack = table.unpack;
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
19 local function iterator(result)
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
20 return function(result_)
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
21 local row = result_();
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
22 if row ~= nil then
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
23 return unpack(row);
4105
08560575762f mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents: 4101
diff changeset
24 end
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
25 end, result, nil;
4105
08560575762f mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents: 4101
diff changeset
26 end
08560575762f mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents: 4101
diff changeset
27
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
28 local default_params = { driver = "SQLite3" };
5776
bd0ff8ae98a8 Remove all trailing whitespace
Florian Zeitz <florob@babelmonkeys.de>
parents: 5209
diff changeset
29
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
30 local engine;
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
31
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
32 local function serialize(value)
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
33 local t = type(value);
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
34 if t == "string" or t == "boolean" or t == "number" then
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
35 return t, tostring(value);
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
36 elseif is_stanza(value) then
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
37 return "xml", tostring(value);
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
38 elseif t == "table" then
8032
aa9f198cb3c9 mod_storage_sql: Rename variables to avoid name clashes [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8031
diff changeset
39 local encoded,err = json.encode(value);
8138
cb605fb60e32 mod_storage_sql: Fix logic error introduced by variable rename in aa9f198cb3c9 (thanks waqas)
Matthew Wild <mwild1@gmail.com>
parents: 8082
diff changeset
40 if encoded then return "json", encoded; end
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
41 return nil, err;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
42 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
43 return nil, "Unhandled value type: "..t;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
44 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
45 local function deserialize(t, value)
3743
5adfb8d0444d mod_storage_sql: Fixed the deserialization of string-typed values.
Waqas Hussain <waqas20@gmail.com>
parents: 3732
diff changeset
46 if t == "string" then return value;
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
47 elseif t == "boolean" then
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
48 if value == "true" then return true;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
49 elseif value == "false" then return false; end
8537
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors
Kim Alvefur <zash@zash.se>
parents: 8480
diff changeset
50 return nil, "invalid-boolean";
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors
Kim Alvefur <zash@zash.se>
parents: 8480
diff changeset
51 elseif t == "number" then
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors
Kim Alvefur <zash@zash.se>
parents: 8480
diff changeset
52 value = tonumber(value);
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors
Kim Alvefur <zash@zash.se>
parents: 8480
diff changeset
53 if value then return value; end
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors
Kim Alvefur <zash@zash.se>
parents: 8480
diff changeset
54 return nil, "invalid-number";
3772
e1f6fe098404 mod_storage_sql: Fix a couple of bugs in "JSON" decoding
Matthew Wild <mwild1@gmail.com>
parents: 3744
diff changeset
55 elseif t == "json" then
3980
6b2fac6602b3 mod_storage_sql: Use util.json instead of util.serialization.
Waqas Hussain <waqas20@gmail.com>
parents: 3978
diff changeset
56 return json.decode(value);
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
57 elseif t == "xml" then
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
58 return xml_parse(value);
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
59 end
8537
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors
Kim Alvefur <zash@zash.se>
parents: 8480
diff changeset
60 return nil, "Unhandled value type: "..t;
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
61 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
62
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
63 local host = module.host;
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
64 local user, store;
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
65
4007
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
66 local function keyval_store_get()
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
67 local haveany;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
68 local result = {};
8033
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
69 local select_sql = [[
8073
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
Kim Alvefur <zash@zash.se>
parents: 8050
diff changeset
70 SELECT "key","type","value"
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
Kim Alvefur <zash@zash.se>
parents: 8050
diff changeset
71 FROM "prosody"
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
Kim Alvefur <zash@zash.se>
parents: 8050
diff changeset
72 WHERE "host"=? AND "user"=? AND "store"=?;
8033
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
73 ]]
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
74 for row in engine:select(select_sql, host, user or "", store) do
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
75 haveany = true;
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
76 local k = row[1];
8537
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors
Kim Alvefur <zash@zash.se>
parents: 8480
diff changeset
77 local v, e = deserialize(row[2], row[3]);
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors
Kim Alvefur <zash@zash.se>
parents: 8480
diff changeset
78 assert(v ~= nil, e);
3977
6724853adb80 mod_storage_sql: Remove the subkey column from the Prosody table, and make the map store compatible with the key-value store.
Waqas Hussain <waqas20@gmail.com>
parents: 3976
diff changeset
79 if k and v then
6724853adb80 mod_storage_sql: Remove the subkey column from the Prosody table, and make the map store compatible with the key-value store.
Waqas Hussain <waqas20@gmail.com>
parents: 3976
diff changeset
80 if k ~= "" then result[k] = v; elseif type(v) == "table" then
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
81 for a,b in pairs(v) do
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
82 result[a] = b;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
83 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
84 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
85 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
86 end
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
87 if haveany then
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
88 return result;
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
89 end
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
90 end
4007
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
91 local function keyval_store_set(data)
8033
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
92 local delete_sql = [[
8073
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
Kim Alvefur <zash@zash.se>
parents: 8050
diff changeset
93 DELETE FROM "prosody"
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
Kim Alvefur <zash@zash.se>
parents: 8050
diff changeset
94 WHERE "host"=? AND "user"=? AND "store"=?
8033
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
95 ]];
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
96 engine:delete(delete_sql, host, user or "", store);
5776
bd0ff8ae98a8 Remove all trailing whitespace
Florian Zeitz <florob@babelmonkeys.de>
parents: 5209
diff changeset
97
8033
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
98 local insert_sql = [[
8073
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
Kim Alvefur <zash@zash.se>
parents: 8050
diff changeset
99 INSERT INTO "prosody"
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
Kim Alvefur <zash@zash.se>
parents: 8050
diff changeset
100 ("host","user","store","key","type","value")
8033
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
101 VALUES (?,?,?,?,?,?);
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
102 ]]
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
103 if data and next(data) ~= nil then
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
104 local extradata = {};
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
105 for key, value in pairs(data) do
3974
af40a7ce4f77 mod_storage_sql: Quote identifiers in SQL with backquotes, and use the empty string for NULL, and '=' instead of 'IS' for comparison, to work with MySQL's limitations...
Waqas Hussain <waqas20@gmail.com>
parents: 3772
diff changeset
106 if type(key) == "string" and key ~= "" then
8032
aa9f198cb3c9 mod_storage_sql: Rename variables to avoid name clashes [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8031
diff changeset
107 local t, encoded_value = assert(serialize(value));
8050
c0f81dea4662 mod_storage_sql: Fix to use correct SQL query (thanks carlos)
Kim Alvefur <zash@zash.se>
parents: 8038
diff changeset
108 engine:insert(insert_sql, host, user or "", store, key, t, encoded_value);
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
109 else
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
110 extradata[key] = value;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
111 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
112 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
113 if next(extradata) ~= nil then
8032
aa9f198cb3c9 mod_storage_sql: Rename variables to avoid name clashes [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8031
diff changeset
114 local t, encoded_extradata = assert(serialize(extradata));
8033
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
115 engine:insert(insert_sql, host, user or "", store, "", t, encoded_extradata);
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
116 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
117 end
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
118 return true;
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
119 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
120
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
121 --- Key/value store API (default store type)
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
122
4007
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
123 local keyval_store = {};
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
124 keyval_store.__index = keyval_store;
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
125 function keyval_store:get(username)
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
126 user, store = username, self.store;
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
127 local ok, result = engine:transaction(keyval_store_get);
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
128 if not ok then
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
129 module:log("error", "Unable to read from database %s store for %s: %s", store, username or "<host>", result);
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
130 return nil, result;
4105
08560575762f mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents: 4101
diff changeset
131 end
6953
b9276d677e76 mod_storage_sql: Whitespace fix (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6951
diff changeset
132 return result;
4007
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
133 end
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
134 function keyval_store:set(username, data)
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
135 user,store = username,self.store;
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
136 return engine:transaction(function()
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
137 return keyval_store_set(data);
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
138 end);
4007
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
139 end
5154
429c8eeea035 mod_storage_sql: Add support for iterating over users with data in a store
Kim Alvefur <zash@zash.se>
parents: 5133
diff changeset
140 function keyval_store:users()
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
141 local ok, result = engine:transaction(function()
8033
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
142 local select_sql = [[
8073
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
Kim Alvefur <zash@zash.se>
parents: 8050
diff changeset
143 SELECT DISTINCT "user"
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
Kim Alvefur <zash@zash.se>
parents: 8050
diff changeset
144 FROM "prosody"
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
Kim Alvefur <zash@zash.se>
parents: 8050
diff changeset
145 WHERE "host"=? AND "store"=?;
8033
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
146 ]];
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
147 return engine:select(select_sql, host, self.store);
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
148 end);
9492
c03c60a2dede mod_storage_sql: Have :users() throw an error on failure instead of returning non-iterator values
Matthew Wild <mwild1@gmail.com>
parents: 9479
diff changeset
149 if not ok then error(result); end
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
150 return iterator(result);
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
151 end
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
152
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
153 --- Archive store API
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
154
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 ]];
13143
3ec48555b773 mod_storage_sql: Add UPSERT support
Kim Alvefur <zash@zash.se>
parents: 12977
diff changeset
206 local upsert_sql = [[
3ec48555b773 mod_storage_sql: Add UPSERT support
Kim Alvefur <zash@zash.se>
parents: 12977
diff changeset
207 INSERT INTO "prosody"
3ec48555b773 mod_storage_sql: Add UPSERT support
Kim Alvefur <zash@zash.se>
parents: 12977
diff changeset
208 ("host","user","store","key","type","value")
3ec48555b773 mod_storage_sql: Add UPSERT support
Kim Alvefur <zash@zash.se>
parents: 12977
diff changeset
209 VALUES (?,?,?,?,?,?)
3ec48555b773 mod_storage_sql: Add UPSERT support
Kim Alvefur <zash@zash.se>
parents: 12977
diff changeset
210 ON CONFLICT ("host", "user","store", "key")
3ec48555b773 mod_storage_sql: Add UPSERT support
Kim Alvefur <zash@zash.se>
parents: 12977
diff changeset
211 DO UPDATE SET "type"=?, "value"=?;
3ec48555b773 mod_storage_sql: Add UPSERT support
Kim Alvefur <zash@zash.se>
parents: 12977
diff changeset
212 ]];
8033
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
213 local select_extradata_sql = [[
8073
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
Kim Alvefur <zash@zash.se>
parents: 8050
diff changeset
214 SELECT "type", "value"
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
Kim Alvefur <zash@zash.se>
parents: 8050
diff changeset
215 FROM "prosody"
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
Kim Alvefur <zash@zash.se>
parents: 8050
diff changeset
216 WHERE "host"=? AND "user"=? AND "store"=? AND "key"=?
8033
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
217 LIMIT 1;
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
218 ]];
7153
89fa66d4e502 mod_storage_sql: Implement map:set_keys, allowing multiple keys to be set in the same transaction
Kim Alvefur <zash@zash.se>
parents: 7149
diff changeset
219 for key, data in pairs(keydatas) do
13151
7ebb3d6afcd1 mod_storage_sql: Add some TODO comments for future UPSERT work
Kim Alvefur <zash@zash.se>
parents: 13150
diff changeset
220 -- TODO Test UPSERT in PostgreSQL before enabling it.
13154
6cf5c215825d mod_storage_sql: Enable UPSERT with PostgreSQL
Kim Alvefur <zash@zash.se>
parents: 13151
diff changeset
221 if type(key) == "string" and key ~= "" and engine.params.driver ~= "MySQL" and data ~= self.remove then
13143
3ec48555b773 mod_storage_sql: Add UPSERT support
Kim Alvefur <zash@zash.se>
parents: 12977
diff changeset
222 local t, value = assert(serialize(data));
3ec48555b773 mod_storage_sql: Add UPSERT support
Kim Alvefur <zash@zash.se>
parents: 12977
diff changeset
223 engine:insert(upsert_sql, host, username or "", self.store, key, t, value, t, value);
3ec48555b773 mod_storage_sql: Add UPSERT support
Kim Alvefur <zash@zash.se>
parents: 12977
diff changeset
224 elseif type(key) == "string" and key ~= "" then
8033
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
225 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
226 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
227 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
228 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
229 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
230 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
231 else
8537
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors
Kim Alvefur <zash@zash.se>
parents: 8480
diff changeset
232 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
233 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
234 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
235 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
236 end
8033
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
237 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
238 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
239 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
240 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
241 end
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
242 end
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
243 return true;
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
244 end);
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
245 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
246 return result;
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
247 end
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
248
10680
19692fc5c106 storagemanager, mod_storage_sql: Rename methods to :get_all() and :delete_all()
Matthew Wild <mwild1@gmail.com>
parents: 10677
diff changeset
249 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
250 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
251 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
252 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
253 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
254 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
255 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
256 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
257 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
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
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests)
Matthew Wild <mwild1@gmail.com>
parents: 10656
diff changeset
260 local 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
261 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
262 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
263 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
264 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
265 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
266 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
267 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
268 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
269
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 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
271
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 end);
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests)
Matthew Wild <mwild1@gmail.com>
parents: 10656
diff changeset
273 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
274 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
275 end
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests)
Matthew Wild <mwild1@gmail.com>
parents: 10656
diff changeset
276
10680
19692fc5c106 storagemanager, mod_storage_sql: Rename methods to :get_all() and :delete_all()
Matthew Wild <mwild1@gmail.com>
parents: 10677
diff changeset
277 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
278 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
279 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
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 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
282 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
283 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
284 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
285 ]];
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests)
Matthew Wild <mwild1@gmail.com>
parents: 10656
diff changeset
286 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
287 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
288 end);
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests)
Matthew Wild <mwild1@gmail.com>
parents: 10656
diff changeset
289 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
290 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
291 end
0054aec3e8c5 mod_storage_sql: Add map_store:find_key() and map_store:delete_key() (+ tests)
Matthew Wild <mwild1@gmail.com>
parents: 10656
diff changeset
292
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_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 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
294 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
295 total = true;
9884
9751c17f5281 mod_storage_internal,_sql: Expose archive capabilities feature set
Kim Alvefur <zash@zash.se>
parents: 9883
diff changeset
296 quota = archive_item_limit;
9751c17f5281 mod_storage_internal,_sql: Expose archive capabilities feature set
Kim Alvefur <zash@zash.se>
parents: 9883
diff changeset
297 truncate = true;
11271
41a962b72a6e mod_storage_sql: Advertise support for id range query
Kim Alvefur <zash@zash.se>
parents: 10993
diff changeset
298 full_id_range = true;
11279
f225070e2b8a mod_storage_sql: Support query for set of IDs
Kim Alvefur <zash@zash.se>
parents: 11271
diff changeset
299 ids = true;
11967
0f2b5182e80b mod_storage_sql: Expose multi-user deletion capability
Kim Alvefur <zash@zash.se>
parents: 11625
diff changeset
300 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
301 };
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
302 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
303 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
304 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
305 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
306 local item_count = archive_item_count_cache:get(cache_key);
13150
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set
Kim Alvefur <zash@zash.se>
parents: 13149
diff changeset
307
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set
Kim Alvefur <zash@zash.se>
parents: 13149
diff changeset
308 if archive_item_limit then
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set
Kim Alvefur <zash@zash.se>
parents: 13149
diff changeset
309 if not item_count then
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set
Kim Alvefur <zash@zash.se>
parents: 13149
diff changeset
310 item_count_cache_miss();
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set
Kim Alvefur <zash@zash.se>
parents: 13149
diff changeset
311 local ok, ret = engine:transaction(function()
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set
Kim Alvefur <zash@zash.se>
parents: 13149
diff changeset
312 local count_sql = [[
13158
cf2a907ac130 mod_storage_sql: Adjust indentation to align with surrounding code
Kim Alvefur <zash@zash.se>
parents: 13157
diff changeset
313 SELECT COUNT(*) FROM "prosodyarchive"
cf2a907ac130 mod_storage_sql: Adjust indentation to align with surrounding code
Kim Alvefur <zash@zash.se>
parents: 13157
diff changeset
314 WHERE "host"=? AND "user"=? AND "store"=?;
cf2a907ac130 mod_storage_sql: Adjust indentation to align with surrounding code
Kim Alvefur <zash@zash.se>
parents: 13157
diff changeset
315 ]];
13150
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set
Kim Alvefur <zash@zash.se>
parents: 13149
diff changeset
316 local result = engine:select(count_sql, host, user, store);
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set
Kim Alvefur <zash@zash.se>
parents: 13149
diff changeset
317 if result then
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set
Kim Alvefur <zash@zash.se>
parents: 13149
diff changeset
318 for row in result do
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set
Kim Alvefur <zash@zash.se>
parents: 13149
diff changeset
319 item_count = row[1];
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set
Kim Alvefur <zash@zash.se>
parents: 13149
diff changeset
320 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
321 end
13150
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set
Kim Alvefur <zash@zash.se>
parents: 13149
diff changeset
322 end);
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set
Kim Alvefur <zash@zash.se>
parents: 13149
diff changeset
323 if not ok or not item_count then
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set
Kim Alvefur <zash@zash.se>
parents: 13149
diff changeset
324 module:log("error", "Failed while checking quota for %s: %s", username, ret);
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set
Kim Alvefur <zash@zash.se>
parents: 13149
diff changeset
325 return nil, "Failure while checking quota";
9883
f76bd399267c mod_storage_internal,_sql: Add limit to number of items in an archive store (fixes #733)
Matthew Wild <mwild1@gmail.com>
parents: 9691
diff changeset
326 end
13150
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set
Kim Alvefur <zash@zash.se>
parents: 13149
diff changeset
327 archive_item_count_cache:set(cache_key, item_count);
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set
Kim Alvefur <zash@zash.se>
parents: 13149
diff changeset
328 else
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set
Kim Alvefur <zash@zash.se>
parents: 13149
diff changeset
329 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
330 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
331
9896
dafe1b544822 mod_storage_sql: No archive item limit by default
Kim Alvefur <zash@zash.se>
parents: 9893
diff changeset
332 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
333 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
334 return nil, "quota-limit";
dafe1b544822 mod_storage_sql: No archive item limit by default
Kim Alvefur <zash@zash.se>
parents: 9893
diff changeset
335 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
336 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
337
12631
9524bb7f3944 mod_storage_sql: Drop archive timestamp precision pending schema update
Kim Alvefur <zash@zash.se>
parents: 12600
diff changeset
338 -- 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
339 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
340 with = with or "";
8032
aa9f198cb3c9 mod_storage_sql: Rename variables to avoid name clashes [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8031
diff changeset
341 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
342 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
343 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
344 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
345 ]];
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
346 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
347 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
348 ("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
349 VALUES (?,?,?,?,?,?,?,?);
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
350 ]];
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
351 if key then
13151
7ebb3d6afcd1 mod_storage_sql: Add some TODO comments for future UPSERT work
Kim Alvefur <zash@zash.se>
parents: 13150
diff changeset
352 -- TODO use UPSERT like map store
10564
3098eac31139 mod_storage_sql: Remove unused and not actually returned return value [luacheck]
Kim Alvefur <zash@zash.se>
parents: 10222
diff changeset
353 local result = engine:delete(delete_sql, host, user or "", store, key);
13150
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set
Kim Alvefur <zash@zash.se>
parents: 13149
diff changeset
354 if result and item_count then
9883
f76bd399267c mod_storage_internal,_sql: Add limit to number of items in an archive store (fixes #733)
Matthew Wild <mwild1@gmail.com>
parents: 9691
diff changeset
355 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
356 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
357 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
358 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
359 end
8032
aa9f198cb3c9 mod_storage_sql: Rename variables to avoid name clashes [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8031
diff changeset
360 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
361 engine:insert(insert_sql, host, user or "", store, when, with, key, t, encoded_value);
13150
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set
Kim Alvefur <zash@zash.se>
parents: 13149
diff changeset
362 if item_count then
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set
Kim Alvefur <zash@zash.se>
parents: 13149
diff changeset
363 archive_item_count_cache:set(cache_key, item_count+1);
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set
Kim Alvefur <zash@zash.se>
parents: 13149
diff changeset
364 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
365 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
366 end);
8032
aa9f198cb3c9 mod_storage_sql: Rename variables to avoid name clashes [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8031
diff changeset
367 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
368 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
369 end
4007
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
370
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
371 -- 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
372 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
373 -- 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
374 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
375 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
376 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
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 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
380 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
381 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
382 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
383 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
384 where[#where+1] = "\"when\" <= ?"
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
385 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
386 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
387
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
388 -- 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
389 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
390 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
391 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
392 end
5776
bd0ff8ae98a8 Remove all trailing whitespace
Florian Zeitz <florob@babelmonkeys.de>
parents: 5209
diff changeset
393
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_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 -- 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
395 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
396 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
397 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
398 end
11279
f225070e2b8a mod_storage_sql: Support query for set of IDs
Kim Alvefur <zash@zash.se>
parents: 11271
diff changeset
399
f225070e2b8a mod_storage_sql: Support query for set of IDs
Kim Alvefur <zash@zash.se>
parents: 11271
diff changeset
400 -- Set of ids
f225070e2b8a mod_storage_sql: Support query for set of IDs
Kim Alvefur <zash@zash.se>
parents: 11271
diff changeset
401 if query.ids then
f225070e2b8a mod_storage_sql: Support query for set of IDs
Kim Alvefur <zash@zash.se>
parents: 11271
diff changeset
402 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
403 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
404 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
405 args[nargs+i] = id;
f225070e2b8a mod_storage_sql: Support query for set of IDs
Kim Alvefur <zash@zash.se>
parents: 11271
diff changeset
406 end
f225070e2b8a mod_storage_sql: Support query for set of IDs
Kim Alvefur <zash@zash.se>
parents: 11271
diff changeset
407 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
408 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
409 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
410 -- 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
411 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
412 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
413 FROM "prosodyarchive"
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325)
Kim Alvefur <zash@zash.se>
parents: 10017
diff changeset
414 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
415 LIMIT 1;
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325)
Kim Alvefur <zash@zash.se>
parents: 10017
diff changeset
416 ]];
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
417 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
418 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
419 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
420 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
421 end
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325)
Kim Alvefur <zash@zash.se>
parents: 10017
diff changeset
422 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
423 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
424 end
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325)
Kim Alvefur <zash@zash.se>
parents: 10017
diff changeset
425 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
426 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
427 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
428 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
429 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
430 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
431 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
432 end
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325)
Kim Alvefur <zash@zash.se>
parents: 10017
diff changeset
433 if not 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
434 return nil, "item-not-found";
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325)
Kim Alvefur <zash@zash.se>
parents: 10017
diff changeset
435 end
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325)
Kim Alvefur <zash@zash.se>
parents: 10017
diff changeset
436 where[#where+1] = '"sort_id" < ?';
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325)
Kim Alvefur <zash@zash.se>
parents: 10017
diff changeset
437 args[#args+1] = 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
438 end
10020
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325)
Kim Alvefur <zash@zash.se>
parents: 10017
diff changeset
439 return true;
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
440 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
441
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
442 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
443 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
444 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
445 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
446 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
447 (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
448 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
449 -- 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
450 -- 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
451 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
452 else
0b350909da24 mod_storage_sql: Return cached total where it makes sense
Kim Alvefur <zash@zash.se>
parents: 11970
diff changeset
453 -- 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
454 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
455 end
10031
17c175ad65f9 mod_storage_sql: Correctly return item-not-found error
Kim Alvefur <zash@zash.se>
parents: 10020
diff changeset
456 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
457 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
458 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
459 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
460 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
461 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
462 ]];
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
463 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
464 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
465
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_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 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
467
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_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 -- Total matching
11971
0b350909da24 mod_storage_sql: Return cached total where it makes sense
Kim Alvefur <zash@zash.se>
parents: 11970
diff changeset
469 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
470
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
471 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
472 .. 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
473 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
474 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
475 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
476 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
477 end
11970
f0e78fa8f24c mod_storage_sql: Adjust handling of 'total' for multi-id queries
Kim Alvefur <zash@zash.se>
parents: 11969
diff changeset
478 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
479 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
480 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
481 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
482 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
483 end
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
484 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
485
10020
deb68066c7aa mod_storage_sql: Look up archive IDs in separate queries (fixes #1325)
Kim Alvefur <zash@zash.se>
parents: 10017
diff changeset
486 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
487 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
488
8033
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
489 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
490 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
491 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
492 end);
10031
17c175ad65f9 mod_storage_sql: Correctly return item-not-found error
Kim Alvefur <zash@zash.se>
parents: 10020
diff changeset
493 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
494 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
495 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
496 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
497 if row ~= nil then
8537
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors
Kim Alvefur <zash@zash.se>
parents: 8480
diff changeset
498 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
499 assert(value ~= nil, err);
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors
Kim Alvefur <zash@zash.se>
parents: 8480
diff changeset
500 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
501 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
502 end, total;
4007
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
503 end
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
504
11354
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
505 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
506 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
507 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
508 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
509 return stanza, when, with;
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
510 end
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
511 return nil, "item-not-found";
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
512 end
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
513
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
514 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
515 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
516 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
517
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
518 local update_query = [[
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
519 UPDATE "prosodyarchive"
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
520 SET %s
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
521 WHERE %s
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
522 ]];
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
523 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
524 local setf = {};
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
525 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
526
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
527 if new_value then
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
528 table.insert(setf, '"type" = ?')
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
529 table.insert(setf, '"value" = ?')
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
530 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
531 table.insert(args, 1, t);
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
532 table.insert(args, 2, value);
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
533 end
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
534
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
535 if new_when then
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
536 table.insert(setf, 1, '"when" = ?')
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
537 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
538 end
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
539
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
540 if new_with then
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
541 table.insert(setf, 1, '"with" = ?')
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
542 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
543 end
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
544
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
545 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
546 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
547 end);
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
548 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
549 return result:affected() == 1;
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
550 end
10fba62332c5 mod_storage_sql: Implement map-like API for archives
Kim Alvefur <zash@zash.se>
parents: 11283
diff changeset
551
9904
bf061f5512f7 mod_storage_sql: Implement archive summary API
Kim Alvefur <zash@zash.se>
parents: 9896
diff changeset
552 function archive_store:summary(username, query)
bf061f5512f7 mod_storage_sql: Implement archive summary API
Kim Alvefur <zash@zash.se>
parents: 9896
diff changeset
553 query = query or {};
bf061f5512f7 mod_storage_sql: Implement archive summary API
Kim Alvefur <zash@zash.se>
parents: 9896
diff changeset
554 local user,store = username,self.store;
bf061f5512f7 mod_storage_sql: Implement archive summary API
Kim Alvefur <zash@zash.se>
parents: 9896
diff changeset
555 local ok, result = engine:transaction(function()
bf061f5512f7 mod_storage_sql: Implement archive summary API
Kim Alvefur <zash@zash.se>
parents: 9896
diff changeset
556 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
557 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
558 FROM "prosodyarchive"
bf061f5512f7 mod_storage_sql: Implement archive summary API
Kim Alvefur <zash@zash.se>
parents: 9896
diff changeset
559 WHERE %s
12599
5aafcf6a0730 mod_storage_sql: Fix summary API with Postgres (fixes #1766)
Kim Alvefur <zash@zash.se>
parents: 12592
diff changeset
560 GROUP BY "with";
9904
bf061f5512f7 mod_storage_sql: Implement archive summary API
Kim Alvefur <zash@zash.se>
parents: 9896
diff changeset
561 ]];
bf061f5512f7 mod_storage_sql: Implement archive summary API
Kim Alvefur <zash@zash.se>
parents: 9896
diff changeset
562 local args = { host, user or "", store, };
bf061f5512f7 mod_storage_sql: Implement archive summary API
Kim Alvefur <zash@zash.se>
parents: 9896
diff changeset
563 local where = { "\"host\" = ?", "\"user\" = ?", "\"store\" = ?", };
bf061f5512f7 mod_storage_sql: Implement archive summary API
Kim Alvefur <zash@zash.se>
parents: 9896
diff changeset
564
bf061f5512f7 mod_storage_sql: Implement archive summary API
Kim Alvefur <zash@zash.se>
parents: 9896
diff changeset
565 archive_where(query, args, where);
bf061f5512f7 mod_storage_sql: Implement archive summary API
Kim Alvefur <zash@zash.se>
parents: 9896
diff changeset
566
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
567 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
568
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
569 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
570 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
571 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
572
12599
5aafcf6a0730 mod_storage_sql: Fix summary API with Postgres (fixes #1766)
Kim Alvefur <zash@zash.se>
parents: 12592
diff changeset
573 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
574 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
575 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
576 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
577 local counts = {};
10222
51f145094648 mod_storage_*: Also include timestmap of first message in :summary API
Kim Alvefur <zash@zash.se>
parents: 10221
diff changeset
578 local earliest, latest = {}, {};
9904
bf061f5512f7 mod_storage_sql: Implement archive summary API
Kim Alvefur <zash@zash.se>
parents: 9896
diff changeset
579 for row in result do
bf061f5512f7 mod_storage_sql: Implement archive summary API
Kim Alvefur <zash@zash.se>
parents: 9896
diff changeset
580 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
581 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
582 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
583 latest[with] = row[4];
9904
bf061f5512f7 mod_storage_sql: Implement archive summary API
Kim Alvefur <zash@zash.se>
parents: 9896
diff changeset
584 end
10220
1e2b444acb72 mod_storage_*: Tweak :summary API to allow future expansion with more fields
Kim Alvefur <zash@zash.se>
parents: 10033
diff changeset
585 return {
1e2b444acb72 mod_storage_*: Tweak :summary API to allow future expansion with more fields
Kim Alvefur <zash@zash.se>
parents: 10033
diff changeset
586 counts = counts;
10222
51f145094648 mod_storage_*: Also include timestmap of first message in :summary API
Kim Alvefur <zash@zash.se>
parents: 10221
diff changeset
587 earliest = earliest;
10221
068692cb9e78 mod_storage_*: Include timestamp of latest message in :summary API
Kim Alvefur <zash@zash.se>
parents: 10220
diff changeset
588 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
589 };
4007
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
590 end
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
591
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
592 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
593 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
594 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
595 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
596 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
597 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
598 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
599 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
600 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
601 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
602 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
603 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
604 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
605 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
606 if query.truncate == nil then
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
607 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
608 elseif engine.params.driver == "MySQL" then
fba795dd99c8 mod_storage_sql: Fix #1639
Kim Alvefur <zash@zash.se>
parents: 12823
diff changeset
609 sql_query = [[
fba795dd99c8 mod_storage_sql: Fix #1639
Kim Alvefur <zash@zash.se>
parents: 12823
diff changeset
610 DELETE result FROM prosodyarchive AS result JOIN (
fba795dd99c8 mod_storage_sql: Fix #1639
Kim Alvefur <zash@zash.se>
parents: 12823
diff changeset
611 SELECT sort_id FROM prosodyarchive
fba795dd99c8 mod_storage_sql: Fix #1639
Kim Alvefur <zash@zash.se>
parents: 12823
diff changeset
612 WHERE %s
fba795dd99c8 mod_storage_sql: Fix #1639
Kim Alvefur <zash@zash.se>
parents: 12823
diff changeset
613 ORDER BY "sort_id" %s
fba795dd99c8 mod_storage_sql: Fix #1639
Kim Alvefur <zash@zash.se>
parents: 12823
diff changeset
614 LIMIT 18446744073709551615 OFFSET %s
fba795dd99c8 mod_storage_sql: Fix #1639
Kim Alvefur <zash@zash.se>
parents: 12823
diff changeset
615 ) AS limiter on result.sort_id = limiter.sort_id;]];
fba795dd99c8 mod_storage_sql: Fix #1639
Kim Alvefur <zash@zash.se>
parents: 12823
diff changeset
616
fba795dd99c8 mod_storage_sql: Fix #1639
Kim Alvefur <zash@zash.se>
parents: 12823
diff changeset
617 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
618 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
619 else
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
620 args[#args+1] = query.truncate;
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
621 local unlimited = "ALL";
10011
2408e6362c15 mod_storage_sql: Move code out of if-else chain
Kim Alvefur <zash@zash.se>
parents: 9628
diff changeset
622 sql_query = [[
2408e6362c15 mod_storage_sql: Move code out of if-else chain
Kim Alvefur <zash@zash.se>
parents: 9628
diff changeset
623 DELETE FROM "prosodyarchive"
2408e6362c15 mod_storage_sql: Move code out of if-else chain
Kim Alvefur <zash@zash.se>
parents: 9628
diff changeset
624 WHERE "sort_id" IN (
2408e6362c15 mod_storage_sql: Move code out of if-else chain
Kim Alvefur <zash@zash.se>
parents: 9628
diff changeset
625 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
626 WHERE %s
2408e6362c15 mod_storage_sql: Move code out of if-else chain
Kim Alvefur <zash@zash.se>
parents: 9628
diff changeset
627 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
628 LIMIT %s OFFSET ?
2408e6362c15 mod_storage_sql: Move code out of if-else chain
Kim Alvefur <zash@zash.se>
parents: 9628
diff changeset
629 );]];
8395
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
630 if engine.params.driver == "SQLite3" then
13148
c6f46f33cecf mod_storage_sql: Record all SQLite3 compile options for potential use
Kim Alvefur <zash@zash.se>
parents: 13144
diff changeset
631 if engine.sqlite_compile_options.enable_update_delete_limit then
10012
acf4a7bfb6aa mod_storage_sql: Handle SQLite DELETE with LIMIT being optional (fixes #1359)
Kim Alvefur <zash@zash.se>
parents: 10011
diff changeset
632 sql_query = [[
acf4a7bfb6aa mod_storage_sql: Handle SQLite DELETE with LIMIT being optional (fixes #1359)
Kim Alvefur <zash@zash.se>
parents: 10011
diff changeset
633 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
634 WHERE %s
acf4a7bfb6aa mod_storage_sql: Handle SQLite DELETE with LIMIT being optional (fixes #1359)
Kim Alvefur <zash@zash.se>
parents: 10011
diff changeset
635 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
636 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
637 ]];
acf4a7bfb6aa mod_storage_sql: Handle SQLite DELETE with LIMIT being optional (fixes #1359)
Kim Alvefur <zash@zash.se>
parents: 10011
diff changeset
638 end
8395
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
639 unlimited = "-1";
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
640 end
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
641 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
642 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
643 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
644 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
645 end);
11969
395dce95508d mod_storage_sql: Fix traceback on multi-user deletion
Kim Alvefur <zash@zash.se>
parents: 11967
diff changeset
646 if username == true then
395dce95508d mod_storage_sql: Fix traceback on multi-user deletion
Kim Alvefur <zash@zash.se>
parents: 11967
diff changeset
647 archive_item_count_cache:clear();
395dce95508d mod_storage_sql: Fix traceback on multi-user deletion
Kim Alvefur <zash@zash.se>
parents: 11967
diff changeset
648 else
395dce95508d mod_storage_sql: Fix traceback on multi-user deletion
Kim Alvefur <zash@zash.se>
parents: 11967
diff changeset
649 local cache_key = jid_join(username, host, self.store);
13150
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set
Kim Alvefur <zash@zash.se>
parents: 13149
diff changeset
650 if query.start == nil and query.with == nil and query["end"] == nil and query.key == nil and query.ids == nil and query.truncate == nil then
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set
Kim Alvefur <zash@zash.se>
parents: 13149
diff changeset
651 -- All items deleted, count should be zero.
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set
Kim Alvefur <zash@zash.se>
parents: 13149
diff changeset
652 archive_item_count_cache:set(cache_key, 0);
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set
Kim Alvefur <zash@zash.se>
parents: 13149
diff changeset
653 else
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set
Kim Alvefur <zash@zash.se>
parents: 13149
diff changeset
654 -- Not sure how many items left
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set
Kim Alvefur <zash@zash.se>
parents: 13149
diff changeset
655 archive_item_count_cache:set(cache_key, nil);
9e6ede86d35d mod_storage_sql: Do not keep track of quota when no quota is set
Kim Alvefur <zash@zash.se>
parents: 13149
diff changeset
656 end
11969
395dce95508d mod_storage_sql: Fix traceback on multi-user deletion
Kim Alvefur <zash@zash.se>
parents: 11967
diff changeset
657 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
658 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
659 end
5776
bd0ff8ae98a8 Remove all trailing whitespace
Florian Zeitz <florob@babelmonkeys.de>
parents: 5209
diff changeset
660
10002
b6b5b9d7417d mod_storage_sql: Add support for iterating over users in archive stores
Kim Alvefur <zash@zash.se>
parents: 9904
diff changeset
661 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
662 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
663 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
664 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
665 FROM "prosodyarchive"
b6b5b9d7417d mod_storage_sql: Add support for iterating over users in archive stores
Kim Alvefur <zash@zash.se>
parents: 9904
diff changeset
666 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
667 ]];
b6b5b9d7417d mod_storage_sql: Add support for iterating over users in archive stores
Kim Alvefur <zash@zash.se>
parents: 9904
diff changeset
668 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
669 end);
b6b5b9d7417d mod_storage_sql: Add support for iterating over users in archive stores
Kim Alvefur <zash@zash.se>
parents: 9904
diff changeset
670 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
671 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
672 end
b6b5b9d7417d mod_storage_sql: Add support for iterating over users in archive stores
Kim Alvefur <zash@zash.se>
parents: 9904
diff changeset
673
13144
033ea8b46d6a mod_storage_sql: Compose a keyval+ store out of keyval and map store methods
Kim Alvefur <zash@zash.se>
parents: 13143
diff changeset
674 local keyvalplus = {
033ea8b46d6a mod_storage_sql: Compose a keyval+ store out of keyval and map store methods
Kim Alvefur <zash@zash.se>
parents: 13143
diff changeset
675 __index = {
033ea8b46d6a mod_storage_sql: Compose a keyval+ store out of keyval and map store methods
Kim Alvefur <zash@zash.se>
parents: 13143
diff changeset
676 -- keyval
033ea8b46d6a mod_storage_sql: Compose a keyval+ store out of keyval and map store methods
Kim Alvefur <zash@zash.se>
parents: 13143
diff changeset
677 get = keyval_store.get;
033ea8b46d6a mod_storage_sql: Compose a keyval+ store out of keyval and map store methods
Kim Alvefur <zash@zash.se>
parents: 13143
diff changeset
678 set = keyval_store.set;
033ea8b46d6a mod_storage_sql: Compose a keyval+ store out of keyval and map store methods
Kim Alvefur <zash@zash.se>
parents: 13143
diff changeset
679 items = keyval_store.users;
033ea8b46d6a mod_storage_sql: Compose a keyval+ store out of keyval and map store methods
Kim Alvefur <zash@zash.se>
parents: 13143
diff changeset
680 -- map
033ea8b46d6a mod_storage_sql: Compose a keyval+ store out of keyval and map store methods
Kim Alvefur <zash@zash.se>
parents: 13143
diff changeset
681 get_key = map_store.get;
033ea8b46d6a mod_storage_sql: Compose a keyval+ store out of keyval and map store methods
Kim Alvefur <zash@zash.se>
parents: 13143
diff changeset
682 set_key = map_store.set;
033ea8b46d6a mod_storage_sql: Compose a keyval+ store out of keyval and map store methods
Kim Alvefur <zash@zash.se>
parents: 13143
diff changeset
683 remove = map_store.remove;
033ea8b46d6a mod_storage_sql: Compose a keyval+ store out of keyval and map store methods
Kim Alvefur <zash@zash.se>
parents: 13143
diff changeset
684 set_keys = map_store.set_keys;
033ea8b46d6a mod_storage_sql: Compose a keyval+ store out of keyval and map store methods
Kim Alvefur <zash@zash.se>
parents: 13143
diff changeset
685 get_key_from_all = map_store.get_all;
033ea8b46d6a mod_storage_sql: Compose a keyval+ store out of keyval and map store methods
Kim Alvefur <zash@zash.se>
parents: 13143
diff changeset
686 delete_key_from_all = map_store.delete_all;
033ea8b46d6a mod_storage_sql: Compose a keyval+ store out of keyval and map store methods
Kim Alvefur <zash@zash.se>
parents: 13143
diff changeset
687 };
033ea8b46d6a mod_storage_sql: Compose a keyval+ store out of keyval and map store methods
Kim Alvefur <zash@zash.se>
parents: 13143
diff changeset
688 }
033ea8b46d6a mod_storage_sql: Compose a keyval+ store out of keyval and map store methods
Kim Alvefur <zash@zash.se>
parents: 13143
diff changeset
689
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_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 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
691 keyval = keyval_store;
6954
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
692 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
693 archive = archive_store;
13144
033ea8b46d6a mod_storage_sql: Compose a keyval+ store out of keyval and map store methods
Kim Alvefur <zash@zash.se>
parents: 13143
diff changeset
694 ["keyval+"] = keyvalplus;
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
695 };
5776
bd0ff8ae98a8 Remove all trailing whitespace
Florian Zeitz <florob@babelmonkeys.de>
parents: 5209
diff changeset
696
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
697 --- Implement storage driver API
5776
bd0ff8ae98a8 Remove all trailing whitespace
Florian Zeitz <florob@babelmonkeys.de>
parents: 5209
diff changeset
698
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_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 -- 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
700
5122
b41c33dc7c36 mod_storage_*: Don't explicitly set driver name, to ease copying/renaming modules.
Waqas Hussain <waqas20@gmail.com>
parents: 5121
diff changeset
701 local driver = {};
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
702
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
703 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
704 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
705 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
706 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
707 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
708 return nil, "unsupported-store";
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
709 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
710
5132
ce6546f867f9 mod_storage_sql: Remove obsolete comment
Matthew Wild <mwild1@gmail.com>
parents: 5130
diff changeset
711 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
712 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
713 (username == true and "!=?" or "=?");
5035
874cab7b4b3e mod_storage_sql: Add method for listing stores
Kim Alvefur <zash@zash.se>
parents: 5034
diff changeset
714 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
715 username = "";
874cab7b4b3e mod_storage_sql: Add method for listing stores
Kim Alvefur <zash@zash.se>
parents: 5034
diff changeset
716 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
717 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
718 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
719 end);
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
720 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
721 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
722 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
723
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_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 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
725 return engine:transaction(function()
8292
2fc8b83dd736 mod_storage_sql: Remove return values from purge method
Kim Alvefur <zash@zash.se>
parents: 8138
diff changeset
726 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
727 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
728 end);
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
729 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
730
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
731 --- 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
732
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
733
9459
6c279302fff4 mod_storage_sql: Remove unused function argument [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8537
diff changeset
734 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
735 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
736 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
737
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
738 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
739 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
740 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
741 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
742 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
743 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
744 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
745 Column { name="value", type="MEDIUMTEXT", nullable=false };
13143
3ec48555b773 mod_storage_sql: Add UPSERT support
Kim Alvefur <zash@zash.se>
parents: 12977
diff changeset
746 Index { name = "prosody_unique_index"; unique = engine.params.driver ~= "MySQL"; "host"; "user"; "store"; "key" };
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
747 };
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
748 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
749 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
750 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
751
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
752 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
753 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
754 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
755 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
756 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
757 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
758 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
759 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
760 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
761 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
762 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
763 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
764 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
765 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
766 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
767 };
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_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 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
769 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
770 end);
5035
874cab7b4b3e mod_storage_sql: Add method for listing stores
Kim Alvefur <zash@zash.se>
parents: 5034
diff changeset
771 end
874cab7b4b3e mod_storage_sql: Add method for listing stores
Kim Alvefur <zash@zash.se>
parents: 5034
diff changeset
772
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
773 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
774 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
775 if params.driver == "MySQL" then
12846
1638991caed2 mod_storage_sql: Support SQLite3 without LuaDBI
Kim Alvefur <zash@zash.se>
parents: 12830
diff changeset
776 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
777 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
778 do
9628
2fcf517b811e mod_storage_sql: Catch errors during schema upgrade (thanks Nothing4You)
Matthew Wild <mwild1@gmail.com>
parents: 9537
diff changeset
779 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
780 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
781 changes = true;
55b40f3fa659 mod_storage_sql: Change prosodyarchive_index to be non-unique (fixes #1087)
Matthew Wild <mwild1@gmail.com>
parents: 9492
diff changeset
782 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
783 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
784 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
785 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
786 end
55b40f3fa659 mod_storage_sql: Change prosodyarchive_index to be non-unique (fixes #1087)
Matthew Wild <mwild1@gmail.com>
parents: 9492
diff changeset
787 end
55b40f3fa659 mod_storage_sql: Change prosodyarchive_index to be non-unique (fixes #1087)
Matthew Wild <mwild1@gmail.com>
parents: 9492
diff changeset
788 end
55b40f3fa659 mod_storage_sql: Change prosodyarchive_index to be non-unique (fixes #1087)
Matthew Wild <mwild1@gmail.com>
parents: 9492
diff changeset
789
55b40f3fa659 mod_storage_sql: Change prosodyarchive_index to be non-unique (fixes #1087)
Matthew Wild <mwild1@gmail.com>
parents: 9492
diff changeset
790 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
791 -- 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
792 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
793 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
794 changes = true;
55b40f3fa659 mod_storage_sql: Change prosodyarchive_index to be non-unique (fixes #1087)
Matthew Wild <mwild1@gmail.com>
parents: 9492
diff changeset
795 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
796 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
797 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
798 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
799 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
800 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
801 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
802 end
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
803 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
804 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
805 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
806 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
807 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
808 .."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
809 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
810 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
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
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
813 -- 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
814 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
815 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
816 FROM "information_schema"."columns"
8081
a201f315de55 mod_storage_sql: Break long lines
Kim Alvefur <zash@zash.se>
parents: 8080
diff changeset
817 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
818 AND "TABLE_SCHEMA" = ?
8081
a201f315de55 mod_storage_sql: Break long lines
Kim Alvefur <zash@zash.se>
parents: 8080
diff changeset
819 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
820 ]];
8038
cd6cef579e82 mod_storage_sql: Remove unused return values (should those actually be used?)
Kim Alvefur <zash@zash.se>
parents: 8037
diff changeset
821 -- 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
822 engine:transaction(function()
8082
8ca11201bfe7 mod_storage_sql: Limit encoding check to current database
Kim Alvefur <zash@zash.se>
parents: 8081
diff changeset
823 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
824 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
825 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
826 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
827 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
828 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
829 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
830 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
831 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
832 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
833 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
834 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
835 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
836 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
837 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
838 end
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
839 end
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
840 end);
8081
a201f315de55 mod_storage_sql: Break long lines
Kim Alvefur <zash@zash.se>
parents: 8080
diff changeset
841 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
842 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
843 engine.charset, engine.charset.."_bin");
13143
3ec48555b773 mod_storage_sql: Add UPSERT support
Kim Alvefur <zash@zash.se>
parents: 12977
diff changeset
844 end);
3ec48555b773 mod_storage_sql: Add UPSERT support
Kim Alvefur <zash@zash.se>
parents: 12977
diff changeset
845 if not success then
3ec48555b773 mod_storage_sql: Add UPSERT support
Kim Alvefur <zash@zash.se>
parents: 12977
diff changeset
846 module:log("error", "Failed to check/upgrade database encoding: %s", err or "unknown error");
3ec48555b773 mod_storage_sql: Add UPSERT support
Kim Alvefur <zash@zash.se>
parents: 12977
diff changeset
847 return false;
3ec48555b773 mod_storage_sql: Add UPSERT support
Kim Alvefur <zash@zash.se>
parents: 12977
diff changeset
848 end
3ec48555b773 mod_storage_sql: Add UPSERT support
Kim Alvefur <zash@zash.se>
parents: 12977
diff changeset
849 else
3ec48555b773 mod_storage_sql: Add UPSERT support
Kim Alvefur <zash@zash.se>
parents: 12977
diff changeset
850 local indices = {};
3ec48555b773 mod_storage_sql: Add UPSERT support
Kim Alvefur <zash@zash.se>
parents: 12977
diff changeset
851 engine:transaction(function ()
3ec48555b773 mod_storage_sql: Add UPSERT support
Kim Alvefur <zash@zash.se>
parents: 12977
diff changeset
852 if params.driver == "SQLite3" then
13156
019cd1db9bec mod_storage_sql: Be more specific when checking for old index in SQLite3
Kim Alvefur <zash@zash.se>
parents: 13155
diff changeset
853 for row in engine:select [[SELECT "name" FROM "sqlite_schema" WHERE "type"='index' AND "tbl_name"='prosody' AND "name"='prosody_index';]] do
13143
3ec48555b773 mod_storage_sql: Add UPSERT support
Kim Alvefur <zash@zash.se>
parents: 12977
diff changeset
854 indices[row[1]] = true;
3ec48555b773 mod_storage_sql: Add UPSERT support
Kim Alvefur <zash@zash.se>
parents: 12977
diff changeset
855 end
3ec48555b773 mod_storage_sql: Add UPSERT support
Kim Alvefur <zash@zash.se>
parents: 12977
diff changeset
856 elseif params.driver == "PostgreSQL" then
13159
706f7d1affc2 mod_storage_sql: Fix column name in index check for PostgreSQL
Kim Alvefur <zash@zash.se>
parents: 13158
diff changeset
857 for row in engine:select [[SELECT "indexname" FROM "pg_indexes" WHERE "tablename"='prosody' AND "indexname"='prosody_index';]] do
13143
3ec48555b773 mod_storage_sql: Add UPSERT support
Kim Alvefur <zash@zash.se>
parents: 12977
diff changeset
858 indices[row[1]] = true;
3ec48555b773 mod_storage_sql: Add UPSERT support
Kim Alvefur <zash@zash.se>
parents: 12977
diff changeset
859 end
3ec48555b773 mod_storage_sql: Add UPSERT support
Kim Alvefur <zash@zash.se>
parents: 12977
diff changeset
860 end
3ec48555b773 mod_storage_sql: Add UPSERT support
Kim Alvefur <zash@zash.se>
parents: 12977
diff changeset
861 end)
13157
841276d0cc25 mod_storage_sql: Only remove old index if it exists
Kim Alvefur <zash@zash.se>
parents: 13156
diff changeset
862 if indices["prosody_index"] then
841276d0cc25 mod_storage_sql: Only remove old index if it exists
Kim Alvefur <zash@zash.se>
parents: 13156
diff changeset
863 if apply_changes then
841276d0cc25 mod_storage_sql: Only remove old index if it exists
Kim Alvefur <zash@zash.se>
parents: 13156
diff changeset
864 local success = engine:transaction(function ()
841276d0cc25 mod_storage_sql: Only remove old index if it exists
Kim Alvefur <zash@zash.se>
parents: 13156
diff changeset
865 return assert(engine:execute([[DROP INDEX "prosody_index";]]));
841276d0cc25 mod_storage_sql: Only remove old index if it exists
Kim Alvefur <zash@zash.se>
parents: 13156
diff changeset
866 end);
841276d0cc25 mod_storage_sql: Only remove old index if it exists
Kim Alvefur <zash@zash.se>
parents: 13156
diff changeset
867 if not success then
841276d0cc25 mod_storage_sql: Only remove old index if it exists
Kim Alvefur <zash@zash.se>
parents: 13156
diff changeset
868 module:log("error", "Failed to delete obsolete index \"prosody_index\"");
841276d0cc25 mod_storage_sql: Only remove old index if it exists
Kim Alvefur <zash@zash.se>
parents: 13156
diff changeset
869 return false;
841276d0cc25 mod_storage_sql: Only remove old index if it exists
Kim Alvefur <zash@zash.se>
parents: 13156
diff changeset
870 end
841276d0cc25 mod_storage_sql: Only remove old index if it exists
Kim Alvefur <zash@zash.se>
parents: 13156
diff changeset
871 else
841276d0cc25 mod_storage_sql: Only remove old index if it exists
Kim Alvefur <zash@zash.se>
parents: 13156
diff changeset
872 changes = true;
13143
3ec48555b773 mod_storage_sql: Add UPSERT support
Kim Alvefur <zash@zash.se>
parents: 12977
diff changeset
873 end
3ec48555b773 mod_storage_sql: Add UPSERT support
Kim Alvefur <zash@zash.se>
parents: 12977
diff changeset
874 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
875 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
876 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
877 end
685403a6fee1 mod_storage_sql: Add method for removing all data belonging to a user
Kim Alvefur <zash@zash.se>
parents: 5035
diff changeset
878
8035
58d6c2ab3d16 mod_storage_sql: Ignore name clash [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8034
diff changeset
879 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
880 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
881 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
882 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
883 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
884 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
885
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_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 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
887 return {
8036
7ebaefc88b3d mod_storage_sql: Split long lines [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8035
diff changeset
888 driver = assert(params.driver,
7ebaefc88b3d mod_storage_sql: Split long lines [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8035
diff changeset
889 "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
890 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
891 "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
892 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
893 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
894 host = params.host;
7753
c276d72d4e17 mod_storage_sql: Include missing parameter table key 'port'
Kim Alvefur <zash@zash.se>
parents: 7752
diff changeset
895 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
896 };
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
897 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
898
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
899 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
900 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
901 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
902 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
903 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
904 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
905 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
906 module:log("debug", "Creating new engine %s", db_uri);
8034
149553feb04e mod_storage_sql: Pass SQL engine as an argument to upgrade and table creator functions
Kim Alvefur <zash@zash.se>
parents: 8033
diff changeset
907 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
908 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
909 -- 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
910 -- 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
911 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
912 -- 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
913 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
914 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
915 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
916 end
10652
0c00274528a4 mod_storage_sql: Fix check for deletion limits (fixes #1494)
Kim Alvefur <zash@zash.se>
parents: 10012
diff changeset
917 end
10655
ee6c12264420 mod_storage_sql: Move update limit check back one step, fixes traceback
Kim Alvefur <zash@zash.se>
parents: 10652
diff changeset
918 if engine.params.driver == "SQLite3" then
13148
c6f46f33cecf mod_storage_sql: Record all SQLite3 compile options for potential use
Kim Alvefur <zash@zash.se>
parents: 13144
diff changeset
919 local compile_options = {}
10655
ee6c12264420 mod_storage_sql: Move update limit check back one step, fixes traceback
Kim Alvefur <zash@zash.se>
parents: 10652
diff changeset
920 for row in engine:select("PRAGMA compile_options") do
13148
c6f46f33cecf mod_storage_sql: Record all SQLite3 compile options for potential use
Kim Alvefur <zash@zash.se>
parents: 13144
diff changeset
921 local option = row[1]:lower();
c6f46f33cecf mod_storage_sql: Record all SQLite3 compile options for potential use
Kim Alvefur <zash@zash.se>
parents: 13144
diff changeset
922 local opt, val = option:match("^([^=]+)=(.*)$");
c6f46f33cecf mod_storage_sql: Record all SQLite3 compile options for potential use
Kim Alvefur <zash@zash.se>
parents: 13144
diff changeset
923 compile_options[opt or option] = tonumber(val) or val or true;
10012
acf4a7bfb6aa mod_storage_sql: Handle SQLite DELETE with LIMIT being optional (fixes #1359)
Kim Alvefur <zash@zash.se>
parents: 10011
diff changeset
924 end
13148
c6f46f33cecf mod_storage_sql: Record all SQLite3 compile options for potential use
Kim Alvefur <zash@zash.se>
parents: 13144
diff changeset
925 engine.sqlite_compile_options = compile_options;
13149
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety
Kim Alvefur <zash@zash.se>
parents: 13148
diff changeset
926
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety
Kim Alvefur <zash@zash.se>
parents: 13148
diff changeset
927 local journal_mode = "delete";
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety
Kim Alvefur <zash@zash.se>
parents: 13148
diff changeset
928 for row in engine:select[[PRAGMA journal_mode;]] do
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety
Kim Alvefur <zash@zash.se>
parents: 13148
diff changeset
929 journal_mode = row[1];
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety
Kim Alvefur <zash@zash.se>
parents: 13148
diff changeset
930 end
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety
Kim Alvefur <zash@zash.se>
parents: 13148
diff changeset
931
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety
Kim Alvefur <zash@zash.se>
parents: 13148
diff changeset
932 -- Note: These things can't be changed with in a transaction. LuaDBI
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety
Kim Alvefur <zash@zash.se>
parents: 13148
diff changeset
933 -- opens a transaction automatically for every statement(?), so this
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety
Kim Alvefur <zash@zash.se>
parents: 13148
diff changeset
934 -- will not work there.
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety
Kim Alvefur <zash@zash.se>
parents: 13148
diff changeset
935 local tune = module:get_option_string("sqlite_tune", "default");
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety
Kim Alvefur <zash@zash.se>
parents: 13148
diff changeset
936 if tune == "normal" then
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety
Kim Alvefur <zash@zash.se>
parents: 13148
diff changeset
937 if journal_mode ~= "wal" then
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety
Kim Alvefur <zash@zash.se>
parents: 13148
diff changeset
938 engine:execute("PRAGMA journal_mode=WAL;");
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety
Kim Alvefur <zash@zash.se>
parents: 13148
diff changeset
939 end
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety
Kim Alvefur <zash@zash.se>
parents: 13148
diff changeset
940 engine:execute("PRAGMA auto_vacuum=FULL;");
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety
Kim Alvefur <zash@zash.se>
parents: 13148
diff changeset
941 engine:execute("PRAGMA synchronous=NORMAL;")
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety
Kim Alvefur <zash@zash.se>
parents: 13148
diff changeset
942 elseif tune == "fast" then
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety
Kim Alvefur <zash@zash.se>
parents: 13148
diff changeset
943 if journal_mode ~= "wal" then
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety
Kim Alvefur <zash@zash.se>
parents: 13148
diff changeset
944 engine:execute("PRAGMA journal_mode=WAL;");
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety
Kim Alvefur <zash@zash.se>
parents: 13148
diff changeset
945 end
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety
Kim Alvefur <zash@zash.se>
parents: 13148
diff changeset
946 if compile_options.secure_delete then
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety
Kim Alvefur <zash@zash.se>
parents: 13148
diff changeset
947 engine:execute("PRAGMA secure_delete=FAST;");
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety
Kim Alvefur <zash@zash.se>
parents: 13148
diff changeset
948 end
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety
Kim Alvefur <zash@zash.se>
parents: 13148
diff changeset
949 engine:execute("PRAGMA synchronous=OFF;")
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety
Kim Alvefur <zash@zash.se>
parents: 13148
diff changeset
950 engine:execute("PRAGMA fullfsync=0;")
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety
Kim Alvefur <zash@zash.se>
parents: 13148
diff changeset
951 elseif tune == "safe" then
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety
Kim Alvefur <zash@zash.se>
parents: 13148
diff changeset
952 if journal_mode ~= "delete" then
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety
Kim Alvefur <zash@zash.se>
parents: 13148
diff changeset
953 engine:execute("PRAGMA journal_mode=DELETE;");
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety
Kim Alvefur <zash@zash.se>
parents: 13148
diff changeset
954 end
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety
Kim Alvefur <zash@zash.se>
parents: 13148
diff changeset
955 engine:execute("PRAGMA synchronous=EXTRA;")
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety
Kim Alvefur <zash@zash.se>
parents: 13148
diff changeset
956 engine:execute("PRAGMA fullfsync=1;")
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety
Kim Alvefur <zash@zash.se>
parents: 13148
diff changeset
957 end
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety
Kim Alvefur <zash@zash.se>
parents: 13148
diff changeset
958
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety
Kim Alvefur <zash@zash.se>
parents: 13148
diff changeset
959 for row in engine:select[[PRAGMA journal_mode;]] do
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety
Kim Alvefur <zash@zash.se>
parents: 13148
diff changeset
960 journal_mode = row[1];
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety
Kim Alvefur <zash@zash.se>
parents: 13148
diff changeset
961 end
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety
Kim Alvefur <zash@zash.se>
parents: 13148
diff changeset
962
0aaf67f70015 mod_storage_sql: Add setting to tune SQLite3 performance vs safety
Kim Alvefur <zash@zash.se>
parents: 13148
diff changeset
963 module:log("debug", "SQLite3 database %q operating with journal_mode=%s", engine.params.database, journal_mode);
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
964 end
12872
a20923f7d5fd mod_storage_sql: Record connection to database as module status
Kim Alvefur <zash@zash.se>
parents: 12846
diff changeset
965 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
966 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
967 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
968 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
969 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
970 else
a20923f7d5fd mod_storage_sql: Record connection to database as module status
Kim Alvefur <zash@zash.se>
parents: 12846
diff changeset
971 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
972 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
973
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
974 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
975 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
976
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
977 function module.command(arg)
12977
74b9e05af71e plugins: Prefix module imports with prosody namespace
Kim Alvefur <zash@zash.se>
parents: 12875
diff changeset
978 local config = require "prosody.core.configmanager";
74b9e05af71e plugins: Prefix module imports with prosody namespace
Kim Alvefur <zash@zash.se>
parents: 12875
diff changeset
979 local hi = require "prosody.util.human.io";
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
980 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
981 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
982 -- 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
983 local uris = {};
8037
5a802653d50d mod_storage_sql: Ignore name clash [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8036
diff changeset
984 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
985 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
986 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
987 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
988 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
989 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
990 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
991 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
992 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
993 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
994 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
995 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
996 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
997 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
998 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
999 -- 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
1000 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
1001 print("Checking "..params.database.."...");
12846
1638991caed2 mod_storage_sql: Support SQLite3 without LuaDBI
Kim Alvefur <zash@zash.se>
parents: 12830
diff changeset
1002 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
1003 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
1004 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
1005 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
1006 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
1007 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
1008 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
1009 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
1010 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
1011 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
1012 end
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
1013 end