1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
|
--[[
DB Tables:
Prosody - key-value, map
| host | user | store | key | type | value |
ProsodyArchive - list
| host | user | store | key | time | stanzatype | jsonvalue |
Mapping:
Roster - Prosody
| host | user | "roster" | "contactjid" | type | value |
| host | user | "roster" | NULL | "json" | roster[false] data |
Account - Prosody
| host | user | "accounts" | "username" | type | value |
Offline - ProsodyArchive
| host | user | "offline" | "contactjid" | time | "message" | json|XML |
]]
local type = type;
local tostring = tostring;
local tonumber = tonumber;
local pairs = pairs;
local next = next;
local setmetatable = setmetatable;
local xpcall = xpcall;
local json = require "util.json";
local build_url = require"socket.url".build;
local DBI;
local connection;
local host,user,store = module.host;
local params = module:get_option("sql");
local dburi;
local connections = module:shared "/*/sql/connection-cache";
local function db2uri(params)
return build_url{
scheme = params.driver,
user = params.username,
password = params.password,
host = params.host,
port = params.port,
path = params.database,
};
end
local resolve_relative_path = require "core.configmanager".resolve_relative_path;
local function test_connection()
if not connection then return nil; end
if connection:ping() then
return true;
else
module:log("debug", "Database connection closed");
connection = nil;
connections[dburi] = nil;
end
end
local function connect()
if not test_connection() then
prosody.unlock_globals();
local dbh, err = DBI.Connect(
params.driver, params.database,
params.username, params.password,
params.host, params.port
);
prosody.lock_globals();
if not dbh then
module:log("debug", "Database connection failed: %s", tostring(err));
return nil, err;
end
module:log("debug", "Successfully connected to database");
dbh:autocommit(false); -- don't commit automatically
connection = dbh;
connections[dburi] = dbh;
end
return connection;
end
local function create_table()
if not module:get_option("sql_manage_tables", true) then
return;
end
local create_sql = "CREATE TABLE `prosody` (`host` TEXT, `user` TEXT, `store` TEXT, `key` TEXT, `type` TEXT, `value` TEXT);";
if params.driver == "PostgreSQL" then
create_sql = create_sql:gsub("`", "\"");
elseif params.driver == "MySQL" then
create_sql = create_sql:gsub("`value` TEXT", "`value` MEDIUMTEXT");
end
local stmt, err = connection:prepare(create_sql);
if stmt then
local ok = stmt:execute();
local commit_ok = connection:commit();
if ok and commit_ok then
module:log("info", "Initialized new %s database with prosody table", params.driver);
local index_sql = "CREATE INDEX `prosody_index` ON `prosody` (`host`, `user`, `store`, `key`)";
if params.driver == "PostgreSQL" then
index_sql = index_sql:gsub("`", "\"");
elseif params.driver == "MySQL" then
index_sql = index_sql:gsub("`([,)])", "`(20)%1");
end
local stmt, err = connection:prepare(index_sql);
local ok, commit_ok, commit_err;
if stmt then
ok, err = stmt:execute();
commit_ok, commit_err = connection:commit();
end
if not(ok and commit_ok) then
module:log("warn", "Failed to create index (%s), lookups may not be optimised", err or commit_err);
end
elseif params.driver == "MySQL" then -- COMPAT: Upgrade tables from 0.8.0
-- Failed to create, but check existing MySQL table here
local stmt = connection:prepare("SHOW COLUMNS FROM prosody WHERE Field='value' and Type='text'");
local ok = stmt:execute();
local commit_ok = connection:commit();
if ok and commit_ok then
if stmt:rowcount() > 0 then
module:log("info", "Upgrading database schema...");
local stmt = connection:prepare("ALTER TABLE prosody MODIFY COLUMN `value` MEDIUMTEXT");
local ok, err = stmt:execute();
local commit_ok = connection:commit();
if ok and commit_ok then
module:log("info", "Database table automatically upgraded");
else
module:log("error", "Failed to upgrade database schema (%s), please see "
.."http://prosody.im/doc/mysql for help",
err or "unknown error");
end
end
repeat until not stmt:fetch();
end
end
elseif params.driver ~= "SQLite3" then -- SQLite normally fails to prepare for existing table
module:log("warn", "Prosody was not able to automatically check/create the database table (%s), "
.."see http://prosody.im/doc/modules/mod_storage_sql#table_management for help.",
err or "unknown error");
end
end
do -- process options to get a db connection
local ok;
prosody.unlock_globals();
ok, DBI = pcall(require, "DBI");
if not ok then
package.loaded["DBI"] = {};
module:log("error", "Failed to load the LuaDBI library for accessing SQL databases: %s", DBI);
module:log("error", "More information on installing LuaDBI can be found at http://prosody.im/doc/depends#luadbi");
end
prosody.lock_globals();
if not ok or not DBI.Connect then
return; -- Halt loading of this module
end
params = params or { driver = "SQLite3" };
if params.driver == "SQLite3" then
params.database = resolve_relative_path(prosody.paths.data or ".", params.database or "prosody.sqlite");
end
assert(params.driver and params.database, "Both the SQL driver and the database need to be specified");
dburi = db2uri(params);
connection = connections[dburi];
assert(connect());
-- Automatically create table, ignore failure (table probably already exists)
create_table();
end
local function serialize(value)
local t = type(value);
if t == "string" or t == "boolean" or t == "number" then
return t, tostring(value);
elseif t == "table" then
local value,err = json.encode(value);
if value then return "json", value; end
return nil, err;
end
return nil, "Unhandled value type: "..t;
end
local function deserialize(t, value)
if t == "string" then return value;
elseif t == "boolean" then
if value == "true" then return true;
elseif value == "false" then return false; end
elseif t == "number" then return tonumber(value);
elseif t == "json" then
return json.decode(value);
end
end
local function dosql(sql, ...)
if params.driver == "PostgreSQL" then
sql = sql:gsub("`", "\"");
end
-- do prepared statement stuff
local stmt, err = connection:prepare(sql);
if not stmt and not test_connection() then error("connection failed"); end
if not stmt then module:log("error", "QUERY FAILED: %s %s", err, debug.traceback()); return nil, err; end
-- run query
local ok, err = stmt:execute(...);
if not ok and not test_connection() then error("connection failed"); end
if not ok then return nil, err; end
return stmt;
end
local function getsql(sql, ...)
return dosql(sql, host or "", user or "", store or "", ...);
end
local function setsql(sql, ...)
local stmt, err = getsql(sql, ...);
if not stmt then return stmt, err; end
return stmt:affected();
end
local function transact(...)
-- ...
end
local function rollback(...)
if connection then connection:rollback(); end -- FIXME check for rollback error?
return ...;
end
local function commit(...)
if not connection:commit() then return nil, "SQL commit failed"; end
return ...;
end
local function keyval_store_get()
local stmt, err = getsql("SELECT * FROM `prosody` WHERE `host`=? AND `user`=? AND `store`=?");
if not stmt then return rollback(nil, err); end
local haveany;
local result = {};
for row in stmt:rows(true) do
haveany = true;
local k = row.key;
local v = deserialize(row.type, row.value);
if k and v then
if k ~= "" then result[k] = v; elseif type(v) == "table" then
for a,b in pairs(v) do
result[a] = b;
end
end
end
end
return commit(haveany and result or nil);
end
local function keyval_store_set(data)
local affected, err = setsql("DELETE FROM `prosody` WHERE `host`=? AND `user`=? AND `store`=?");
if not affected then return rollback(affected, err); end
if data and next(data) ~= nil then
local extradata = {};
for key, value in pairs(data) do
if type(key) == "string" and key ~= "" then
local t, value = serialize(value);
if not t then return rollback(t, value); end
local ok, err = setsql("INSERT INTO `prosody` (`host`,`user`,`store`,`key`,`type`,`value`) VALUES (?,?,?,?,?,?)", key, t, value);
if not ok then return rollback(ok, err); end
else
extradata[key] = value;
end
end
if next(extradata) ~= nil then
local t, extradata = serialize(extradata);
if not t then return rollback(t, extradata); end
local ok, err = setsql("INSERT INTO `prosody` (`host`,`user`,`store`,`key`,`type`,`value`) VALUES (?,?,?,?,?,?)", "", t, extradata);
if not ok then return rollback(ok, err); end
end
end
return commit(true);
end
local keyval_store = {};
keyval_store.__index = keyval_store;
function keyval_store:get(username)
user,store = username,self.store;
if not connection and not connect() then return nil, "Unable to connect to database"; end
local success, ret, err = xpcall(keyval_store_get, debug.traceback);
if not connection and connect() then
success, ret, err = xpcall(keyval_store_get, debug.traceback);
end
if success then return ret, err; else return rollback(nil, ret); end
end
function keyval_store:set(username, data)
user,store = username,self.store;
if not connection and not connect() then return nil, "Unable to connect to database"; end
local success, ret, err = xpcall(function() return keyval_store_set(data); end, debug.traceback);
if not connection and connect() then
success, ret, err = xpcall(function() return keyval_store_set(data); end, debug.traceback);
end
if success then return ret, err; else return rollback(nil, ret); end
end
function keyval_store:users()
local stmt, err = dosql("SELECT DISTINCT `user` FROM `prosody` WHERE `host`=? AND `store`=?", host, self.store);
if not stmt then
return rollback(nil, err);
end
local next = stmt:rows();
return commit(function()
local row = next();
return row and row[1];
end);
end
local function map_store_get(key)
local stmt, err = getsql("SELECT * FROM `prosody` WHERE `host`=? AND `user`=? AND `store`=? AND `key`=?", key or "");
if not stmt then return rollback(nil, err); end
local haveany;
local result = {};
for row in stmt:rows(true) do
haveany = true;
local k = row.key;
local v = deserialize(row.type, row.value);
if k and v then
if k ~= "" then result[k] = v; elseif type(v) == "table" then
for a,b in pairs(v) do
result[a] = b;
end
end
end
end
return commit(haveany and result[key] or nil);
end
local function map_store_set(key, data)
local affected, err = setsql("DELETE FROM `prosody` WHERE `host`=? AND `user`=? AND `store`=? AND `key`=?", key or "");
if not affected then return rollback(affected, err); end
if data and next(data) ~= nil then
if type(key) == "string" and key ~= "" then
local t, value = serialize(data);
if not t then return rollback(t, value); end
local ok, err = setsql("INSERT INTO `prosody` (`host`,`user`,`store`,`key`,`type`,`value`) VALUES (?,?,?,?,?,?)", key, t, value);
if not ok then return rollback(ok, err); end
else
-- TODO non-string keys
end
end
return commit(true);
end
local map_store = {};
map_store.__index = map_store;
function map_store:get(username, key)
user,store = username,self.store;
local success, ret, err = xpcall(function() return map_store_get(key); end, debug.traceback);
if success then return ret, err; else return rollback(nil, ret); end
end
function map_store:set(username, key, data)
user,store = username,self.store;
local success, ret, err = xpcall(function() return map_store_set(key, data); end, debug.traceback);
if success then return ret, err; else return rollback(nil, ret); end
end
local list_store = {};
list_store.__index = list_store;
function list_store:scan(username, from, to, jid, typ)
user,store = username,self.store;
local cols = {"from", "to", "jid", "typ"};
local vals = { from , to , jid , typ };
local stmt, err;
local query = "SELECT * FROM `prosodyarchive` WHERE `host`=? AND `user`=? AND `store`=?";
query = query.." ORDER BY time";
--local stmt, err = getsql("SELECT * FROM `prosody` WHERE `host`=? AND `user`=? AND `store`=? AND `key`=?", key or "");
return nil, "not-implemented"
end
local driver = {};
function driver:open(store, typ)
if not typ then -- default key-value store
return setmetatable({ store = store }, keyval_store);
end
return nil, "unsupported-store";
end
function driver:stores(username)
local sql = "SELECT DISTINCT `store` FROM `prosody` WHERE `host`=? AND `user`" ..
(username == true and "!=?" or "=?");
if username == true or not username then
username = "";
end
local stmt, err = dosql(sql, host, username);
if not stmt then
return rollback(nil, err);
end
local next = stmt:rows();
return commit(function()
local row = next();
return row and row[1];
end);
end
function driver:purge(username)
local stmt, err = dosql("DELETE FROM `prosody` WHERE `host`=? AND `user`=?", host, username);
if not stmt then return rollback(stmt, err); end
local changed, err = stmt:affected();
if not changed then return rollback(changed, err); end
return commit(true, changed);
end
module:provides("storage", driver);
|