prosody
feaef6215bb8
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 @ 9307:feaef6215bb8

util.stanza: Don't automatically generate ids for iq stanzas Users of this API should provide their own id attribute that meets their uniqueness requirements. The current implementation leaks information (i.e. how many iq stanzas have been sent by the server to other JIDs). Providing any strong guarantees of randomness here would need to pull in additional dependencies that we don't want in this simple library.
author Matthew Wild <mwild1@gmail.com>
date Thu, 13 Sep 2018 16:35:48 +0100
parent 8537:d7f31badd359
child 9459:6c279302fff4
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
3980
6b2fac6602b3 mod_storage_sql: Use util.json instead of util.serialization.
Waqas Hussain <waqas20@gmail.com>
parents: 3978
diff changeset
4 local json = require "util.json";
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
5 local sql = require "util.sql";
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
6 local xml_parse = require "util.xml".parse;
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
7 local uuid = require "util.uuid";
6165
6a184b16b717 core.certmanager, core.moduleapi, mod_storage_sql, mod_storage_sql2: Import from util.paths
Kim Alvefur <zash@zash.se>
parents: 5776
diff changeset
8 local resolve_relative_path = require "util.paths".resolve_relative_path;
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
9
7751
168919a947ed mod_storage_sql: Use is_stanza() from util.stanza
Kim Alvefur <zash@zash.se>
parents: 7305
diff changeset
10 local is_stanza = require"util.stanza".is_stanza;
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
11 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
12
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
13 local noop = function() 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
14 local unpack = unpack
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_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 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
16 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
17 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
18 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
19 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
20 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
21 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
22 end
08560575762f mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents: 4101
diff changeset
23
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
24 local default_params = { driver = "SQLite3" };
5776
bd0ff8ae98a8 Remove all trailing whitespace
Florian Zeitz <florob@babelmonkeys.de>
parents: 5209
diff changeset
25
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
26 local engine;
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
27
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
28 local function serialize(value)
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
29 local t = type(value);
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
30 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
31 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
32 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
33 return "xml", tostring(value);
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
34 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
35 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
36 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
37 return nil, err;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
38 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
39 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
40 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
41 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
42 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
43 elseif t == "boolean" then
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
44 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
45 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
46 return nil, "invalid-boolean";
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors
Kim Alvefur <zash@zash.se>
parents: 8480
diff changeset
47 elseif t == "number" then
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors
Kim Alvefur <zash@zash.se>
parents: 8480
diff changeset
48 value = tonumber(value);
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors
Kim Alvefur <zash@zash.se>
parents: 8480
diff changeset
49 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
50 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
51 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
52 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
53 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
54 return xml_parse(value);
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
55 end
8537
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors
Kim Alvefur <zash@zash.se>
parents: 8480
diff changeset
56 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
57 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
58
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
59 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
60 local user, store;
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
61
4007
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
62 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
63 local haveany;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
64 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
65 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
66 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
67 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
68 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
69 ]]
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
70 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
71 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
72 local k = row[1];
8537
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors
Kim Alvefur <zash@zash.se>
parents: 8480
diff changeset
73 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
74 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
75 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
76 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
77 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
78 result[a] = b;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
79 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
80 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
81 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
82 end
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
83 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
84 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
85 end
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
86 end
4007
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
87 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
88 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
89 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
90 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
91 ]];
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
92 engine:delete(delete_sql, host, user or "", store);
5776
bd0ff8ae98a8 Remove all trailing whitespace
Florian Zeitz <florob@babelmonkeys.de>
parents: 5209
diff changeset
93
8033
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
94 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
95 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
96 ("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
97 VALUES (?,?,?,?,?,?);
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
98 ]]
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
99 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
100 local extradata = {};
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
101 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
102 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
103 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
104 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
105 else
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
106 extradata[key] = value;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
107 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
108 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
109 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
110 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
111 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
112 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
113 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
114 return true;
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
115 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
116
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
117 --- 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
118
4007
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
119 local keyval_store = {};
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
120 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
121 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
122 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
123 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
124 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
125 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
126 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
127 end
6953
b9276d677e76 mod_storage_sql: Whitespace fix (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6951
diff changeset
128 return result;
4007
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
129 end
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
130 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
131 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
132 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
133 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
134 end);
4007
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
135 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
136 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
137 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
138 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
139 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
140 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
141 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
142 ]];
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
143 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
144 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
145 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
146 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
147 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
148
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
149 --- 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
150
7156
18b7ffd08d56 mod_storage_sql: Silence some harmless luacheck warnings
Kim Alvefur <zash@zash.se>
parents: 7153
diff changeset
151 -- luacheck: ignore 512 431/user 431/store
6954
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
152 local map_store = {};
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
153 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
154 map_store.remove = {};
6954
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
155 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
156 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
157 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
158 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
159 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
160 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
161 LIMIT 1
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
162 ]];
8537
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors
Kim Alvefur <zash@zash.se>
parents: 8480
diff changeset
163 local data, err;
6954
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
164 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
165 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
166 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
167 assert(data ~= nil, err);
6954
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
168 end
7274
e0727512bb99 mod_storage_sql: Allow loops over results to end on their own
Kim Alvefur <zash@zash.se>
parents: 7273
diff changeset
169 return data;
6954
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
170 else
8033
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
171 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
172 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
173 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
174 end
7274
e0727512bb99 mod_storage_sql: Allow loops over results to end on their own
Kim Alvefur <zash@zash.se>
parents: 7273
diff changeset
175 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
176 end
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
177 end);
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
178 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
179 return result;
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
180 end
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
181 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
182 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
183 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
184 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
185 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
186 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
187 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
188 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
189 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
190 ]];
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
191 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
192 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
193 ("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
194 VALUES (?,?,?,?,?,?);
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
195 ]];
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
196 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
197 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
198 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 LIMIT 1;
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
201 ]];
7153
89fa66d4e502 mod_storage_sql: Implement map:set_keys, allowing multiple keys to be set in the same transaction
Kim Alvefur <zash@zash.se>
parents: 7149
diff changeset
202 for key, data in pairs(keydatas) do
89fa66d4e502 mod_storage_sql: Implement map:set_keys, allowing multiple keys to be set in the same transaction
Kim Alvefur <zash@zash.se>
parents: 7149
diff changeset
203 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
204 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
205 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
206 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
207 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
208 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
209 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
210 else
8537
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors
Kim Alvefur <zash@zash.se>
parents: 8480
diff changeset
211 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
212 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
213 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
214 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
215 end
8033
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
216 engine: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
217 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
218 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
219 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
220 end
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
221 end
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
222 return true;
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
223 end);
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
224 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
225 return result;
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
226 end
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
227
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
228 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
229 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
230 total = 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
231 };
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
232 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
233 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
234 local user,store = username,self.store;
8031
ef838b7f8f53 mod_storage_sql: Sensible defaults for 'when' and 'with' arguments
Kim Alvefur <zash@zash.se>
parents: 8029
diff changeset
235 when = when or os.time();
ef838b7f8f53 mod_storage_sql: Sensible defaults for 'when' and 'with' arguments
Kim Alvefur <zash@zash.se>
parents: 8029
diff changeset
236 with = with or "";
8032
aa9f198cb3c9 mod_storage_sql: Rename variables to avoid name clashes [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8031
diff changeset
237 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
238 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
239 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
240 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
241 ]];
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
242 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
243 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
244 ("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
245 VALUES (?,?,?,?,?,?,?,?);
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
246 ]];
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
247 if 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
248 engine:delete(delete_sql, host, user or "", 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
249 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
250 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
251 end
8032
aa9f198cb3c9 mod_storage_sql: Rename variables to avoid name clashes [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8031
diff changeset
252 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
253 engine:insert(insert_sql, host, user or "", store, when, with, key, t, encoded_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
254 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
255 end);
8032
aa9f198cb3c9 mod_storage_sql: Rename variables to avoid name clashes [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8031
diff changeset
256 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
257 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
258 end
4007
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
259
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
260 -- 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
261 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
262 -- 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
263 if query.start then
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
264 args[#args+1] = query.start
8073
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
Kim Alvefur <zash@zash.se>
parents: 8050
diff changeset
265 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
266 end
5776
bd0ff8ae98a8 Remove all trailing whitespace
Florian Zeitz <florob@babelmonkeys.de>
parents: 5209
diff changeset
267
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
268 if query["end"] then
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
269 args[#args+1] = query["end"];
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
270 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
271 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
272 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
273 where[#where+1] = "\"when\" <= ?"
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
274 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
275 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
276
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
277 -- 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
278 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
279 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
280 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
281 end
5776
bd0ff8ae98a8 Remove all trailing whitespace
Florian Zeitz <florob@babelmonkeys.de>
parents: 5209
diff changeset
282
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
283 -- 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
284 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
285 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
286 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
287 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
288 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
289 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
290 local args_len = #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
291 -- Before or after specific item, exclusive
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
292 if query.after then -- keys better be unique!
8033
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
293 where[#where+1] = [[
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
294 "sort_id" > COALESCE(
8033
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
295 (
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
296 SELECT "sort_id"
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
297 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
298 WHERE "key" = ? AND "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
299 LIMIT 1
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
300 ), 0)
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
301 ]];
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_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 args[args_len+1], args[args_len+2], args[args_len+3], args[args_len+4] = query.after, args[1], args[2], args[3];
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_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 args_len = args_len + 4
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_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 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
305 if query.before then
8033
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
306 where[#where+1] = [[
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
307 "sort_id" < COALESCE(
8033
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
308 (
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
309 SELECT "sort_id"
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
310 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
311 WHERE "key" = ? AND "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
312 LIMIT 1
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
313 ),
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
314 (
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
315 SELECT MAX("sort_id")+1
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
316 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
317 )
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
318 )
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
319 ]]
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
320 args[args_len+1], args[args_len+2], args[args_len+3], args[args_len+4] = query.before, args[1], args[2], args[3];
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
321 end
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
322 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
323
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
324 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
325 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
326 local 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
327 local 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
328 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
329 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
330 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
331 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
332 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
333 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
334 ]];
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
335 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
336 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
337
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
338 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
339
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
340 -- Total matching
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
341 if query.total 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
342 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
343 .. 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
344 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
345 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
346 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
347 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
348 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
349 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
350 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
351 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
352 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
353
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
354 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
355
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
356 if query.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
357 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
358 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
359
8033
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
360 sql_query = sql_query:format(t_concat(where, " AND "), query.reverse
6c3cae9b96cb mod_storage_sql: Move SQL queries into multiline strings for readability [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8032
diff changeset
361 and "DESC" or "ASC", query.limit and " LIMIT ?" or "");
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
362 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
363 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
364 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
365 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
366 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
367 if row ~= nil then
8537
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors
Kim Alvefur <zash@zash.se>
parents: 8480
diff changeset
368 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
369 assert(value ~= nil, err);
d7f31badd359 mod_storage_sql: Catch and report value deserialization errors
Kim Alvefur <zash@zash.se>
parents: 8480
diff changeset
370 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
371 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
372 end, total;
4007
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
373 end
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
374
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
375 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
376 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
377 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
378 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
379 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
380 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
381 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
382 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
383 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
384 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
385 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
386 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
387 archive_where_id_range(query, args, where);
8395
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
388 if query.truncate == nil then
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
389 sql_query = sql_query:format(t_concat(where, " AND "));
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
390 else
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
391 args[#args+1] = query.truncate;
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
392 local unlimited = "ALL";
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
393 if engine.params.driver == "SQLite3" then
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
394 sql_query = [[
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
395 DELETE FROM "prosodyarchive"
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
396 WHERE %s
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
397 ORDER BY "sort_id" %s
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
398 LIMIT %s OFFSET ?;
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
399 ]];
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
400 unlimited = "-1";
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
401 else
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
402 sql_query = [[
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
403 DELETE FROM "prosodyarchive"
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
404 WHERE "sort_id" IN (
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
405 SELECT "sort_id" FROM "prosodyarchive"
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
406 WHERE %s
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
407 ORDER BY "sort_id" %s
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
408 LIMIT %s OFFSET ?
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
409 );]];
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
410 if engine.params.driver == "MySQL" then
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
411 unlimited = "18446744073709551615";
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
412 end
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
413 end
fbb9a1c2120e mod_storage_sql: Add support for truncating deletion
Kim Alvefur <zash@zash.se>
parents: 8294
diff changeset
414 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
415 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
416 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
417 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
418 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
419 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
420 end
5776
bd0ff8ae98a8 Remove all trailing whitespace
Florian Zeitz <florob@babelmonkeys.de>
parents: 5209
diff changeset
421
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
422 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
423 keyval = keyval_store;
6954
400badaf1fc7 mod_storage_sql: Add map store (backported from trunk)
Matthew Wild <mwild1@gmail.com>
parents: 6953
diff changeset
424 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
425 archive = archive_store;
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
426 };
5776
bd0ff8ae98a8 Remove all trailing whitespace
Florian Zeitz <florob@babelmonkeys.de>
parents: 5209
diff changeset
427
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
428 --- Implement storage driver API
5776
bd0ff8ae98a8 Remove all trailing whitespace
Florian Zeitz <florob@babelmonkeys.de>
parents: 5209
diff changeset
429
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
430 -- 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
431
5122
b41c33dc7c36 mod_storage_*: Don't explicitly set driver name, to ease copying/renaming modules.
Waqas Hussain <waqas20@gmail.com>
parents: 5121
diff changeset
432 local driver = {};
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
433
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
434 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
435 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
436 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
437 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
438 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
439 return nil, "unsupported-store";
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
5132
ce6546f867f9 mod_storage_sql: Remove obsolete comment
Matthew Wild <mwild1@gmail.com>
parents: 5130
diff changeset
442 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
443 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
444 (username == true and "!=?" or "=?");
5035
874cab7b4b3e mod_storage_sql: Add method for listing stores
Kim Alvefur <zash@zash.se>
parents: 5034
diff changeset
445 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
446 username = "";
874cab7b4b3e mod_storage_sql: Add method for listing stores
Kim Alvefur <zash@zash.se>
parents: 5034
diff changeset
447 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
448 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
449 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
450 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
451 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
452 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
453 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
454
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
455 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
456 return engine:transaction(function()
8292
2fc8b83dd736 mod_storage_sql: Remove return values from purge method
Kim Alvefur <zash@zash.se>
parents: 8138
diff changeset
457 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
458 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
459 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
460 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
461
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
462 --- 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
463
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
464
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
465 local function create_table(engine, name) -- 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
466 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
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 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
469 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
470 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
471 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
472 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
473 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
474 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
475 Column { name="value", type="MEDIUMTEXT", nullable=false };
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
476 Index { name="prosody_index", "host", "user", "store", "key" };
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
477 };
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
478 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
479 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
480 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
481
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_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 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
483 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
484 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
485 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
486 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
487 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
488 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
489 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
490 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
491 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
492 Column { name="value", type="MEDIUMTEXT", nullable=false };
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
493 Index { name="prosodyarchive_index", unique = true, "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
494 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
495 Index { name="prosodyarchive_when", "host", "user", "store", "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
496 };
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_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 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
498 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
499 end);
5035
874cab7b4b3e mod_storage_sql: Add method for listing stores
Kim Alvefur <zash@zash.se>
parents: 5034
diff changeset
500 end
874cab7b4b3e mod_storage_sql: Add method for listing stores
Kim Alvefur <zash@zash.se>
parents: 5034
diff changeset
501
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
502 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
503 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
504 if params.driver == "MySQL" then
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
505 local success,err = engine:transaction(function()
8079
b058fc4a4243 mod_storage_sql: Quote some field names
Kim Alvefur <zash@zash.se>
parents: 8075
diff changeset
506 local result = engine:execute("SHOW COLUMNS FROM \"prosody\" WHERE \"Field\"='value' and \"Type\"='text'");
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
507 if result:rowcount() > 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
508 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
509 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
510 module:log("info", "Upgrading database schema...");
8079
b058fc4a4243 mod_storage_sql: Quote some field names
Kim Alvefur <zash@zash.se>
parents: 8075
diff changeset
511 engine:execute("ALTER TABLE \"prosody\" MODIFY COLUMN \"value\" MEDIUMTEXT");
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
512 module:log("info", "Database table automatically upgraded");
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
513 end
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
514 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
515 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
516 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
517 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
518 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
519 .."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
520 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
521 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
522 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
523
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
524 -- 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
525 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
526 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
527 FROM "information_schema"."columns"
8081
a201f315de55 mod_storage_sql: Break long lines
Kim Alvefur <zash@zash.se>
parents: 8080
diff changeset
528 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
529 AND "TABLE_SCHEMA" = ?
8081
a201f315de55 mod_storage_sql: Break long lines
Kim Alvefur <zash@zash.se>
parents: 8080
diff changeset
530 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
531 ]];
8038
cd6cef579e82 mod_storage_sql: Remove unused return values (should those actually be used?)
Kim Alvefur <zash@zash.se>
parents: 8037
diff changeset
532 -- 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
533 engine:transaction(function()
8082
8ca11201bfe7 mod_storage_sql: Limit encoding check to current database
Kim Alvefur <zash@zash.se>
parents: 8081
diff changeset
534 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
535 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
536 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
537 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
538 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
539 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
540 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
541 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
542 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
543 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
544 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
545 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
546 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
547 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
548 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
549 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
550 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
551 end);
8081
a201f315de55 mod_storage_sql: Break long lines
Kim Alvefur <zash@zash.se>
parents: 8080
diff changeset
552 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
553 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
554 engine.charset, engine.charset.."_bin");
8081
a201f315de55 mod_storage_sql: Break long lines
Kim Alvefur <zash@zash.se>
parents: 8080
diff changeset
555 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
556 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
557 module:log("error", "Failed to check/upgrade database encoding: %s", err or "unknown error");
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
558 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
559 end
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
560 end
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
561 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
562 end
685403a6fee1 mod_storage_sql: Add method for removing all data belonging to a user
Kim Alvefur <zash@zash.se>
parents: 5035
diff changeset
563
8035
58d6c2ab3d16 mod_storage_sql: Ignore name clash [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8034
diff changeset
564 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
565 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
566 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
567 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
568 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
569 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
570
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_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 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
572 return {
8036
7ebaefc88b3d mod_storage_sql: Split long lines [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8035
diff changeset
573 driver = assert(params.driver,
7ebaefc88b3d mod_storage_sql: Split long lines [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8035
diff changeset
574 "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
575 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
576 "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
577 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
578 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
579 host = params.host;
7753
c276d72d4e17 mod_storage_sql: Include missing parameter table key 'port'
Kim Alvefur <zash@zash.se>
parents: 7752
diff changeset
580 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
581 };
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
582 end
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
583
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
584 function module.load()
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
585 if prosody.prosodyctl then return; end
7170
fb37aece3252 mod_storage_sql: Share SQL connections with same parameters across VirtualHosts (fixes #576)
Kim Alvefur <zash@zash.se>
parents: 7169
diff changeset
586 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
587 local params = normalize_params(module:get_option("sql", default_params));
7170
fb37aece3252 mod_storage_sql: Share SQL connections with same parameters across VirtualHosts (fixes #576)
Kim Alvefur <zash@zash.se>
parents: 7169
diff changeset
588 engine = engines[sql.db2uri(params)];
fb37aece3252 mod_storage_sql: Share SQL connections with same parameters across VirtualHosts (fixes #576)
Kim Alvefur <zash@zash.se>
parents: 7169
diff changeset
589 if not engine then
7173
d8c060615aa1 mod_storage_sql: Lower message about new engine creation to debug level
Kim Alvefur <zash@zash.se>
parents: 7170
diff changeset
590 module:log("debug", "Creating new engine");
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
591 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
592 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
593 -- 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
594 -- 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
595 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
596 -- 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
597 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
598 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
599 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
600 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
601 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
602 end);
fb37aece3252 mod_storage_sql: Share SQL connections with same parameters across VirtualHosts (fixes #576)
Kim Alvefur <zash@zash.se>
parents: 7169
diff changeset
603 engines[sql.db2uri(params)] = engine;
fb37aece3252 mod_storage_sql: Share SQL connections with same parameters across VirtualHosts (fixes #576)
Kim Alvefur <zash@zash.se>
parents: 7169
diff changeset
604 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
605
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
606 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
607 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
608
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
609 function module.command(arg)
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
610 local config = require "core.configmanager";
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
611 local prosodyctl = require "util.prosodyctl";
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
612 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
613 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
614 -- 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
615 local uris = {};
8037
5a802653d50d mod_storage_sql: Ignore name clash [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8036
diff changeset
616 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
617 local params = normalize_params(config.get(host, "sql") or default_params);
6951
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
618 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
619 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
620 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
621 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
622 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
623 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
624 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
625 print("Ensure you have working backups of the above databases before continuing! ");
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
626 if not prosodyctl.show_yesno("Continue with the database upgrade? [yN]") 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
627 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
628 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
629 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
630 -- 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
631 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
632 print("Checking "..params.database.."...");
99de8f30d99e storagemanager, mod_storage_sql, mod_storage_sql2: Rename mod_storage_sql2 -> mod_storage_sql, mod_storage_sql -> mod_storage_sql1 (temporarily), and emit warning for configs using sql2
Matthew Wild <mwild1@gmail.com>
parents: 6283
diff changeset
633 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
634 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
635 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
636 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
637 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
638 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
639 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
640 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
641 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
642 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
643 end