prosody
e6fed1d80116
plugins/storage/sqlbasic.lib.lua

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

Software / code / prosody

Annotate

plugins/storage/sqlbasic.lib.lua @ 5915:e6fed1d80116

Back out 1b0ac7950129, as SSLv3 appears to still be in moderate use on the network. Also, although obsolete, SSLv3 isn't documented to have any weaknesses that TLS 1.0 (the most common version used today) doesn't also have. Get your act together clients!
author Matthew Wild <mwild1@gmail.com>
date Tue, 12 Nov 2013 02:13:01 +0000
parent 5021:85b2689dbcfe
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
2678
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
1
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
2 -- Basic SQL driver
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
3 -- This driver stores data as simple key-values
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
4
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
5 local ser = require "util.serialization".serialize;
5021
85b2689dbcfe Eliminate direct setfenv usage
Florian Zeitz <florob@babelmonkeys.de>
parents: 2678
diff changeset
6 local envload = require "util.envload".envload;
2678
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
7 local deser = function(data)
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
8 module:log("debug", "deser: %s", tostring(data));
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
9 if not data then return nil; end
5021
85b2689dbcfe Eliminate direct setfenv usage
Florian Zeitz <florob@babelmonkeys.de>
parents: 2678
diff changeset
10 local f = envload("return "..data, nil, {});
2678
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
11 if not f then return nil; end
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
12 local s, d = pcall(f);
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
13 if not s then return nil; end
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
14 return d;
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
15 end;
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
16
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
17 local driver = {};
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
18 driver.__index = driver;
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
19
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
20 driver.item_table = "item";
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
21 driver.list_table = "list";
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
22
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
23 function driver:prepare(sql)
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
24 module:log("debug", "query: %s", sql);
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
25 local err;
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
26 if not self.sqlcache then self.sqlcache = {}; end
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
27 local r = self.sqlcache[sql];
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
28 if r then return r; end
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
29 r, err = self.connection:prepare(sql);
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
30 if not r then error("Unable to prepare SQL statement: "..err); end
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
31 self.sqlcache[sql] = r;
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
32 return r;
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
33 end
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
34
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
35 function driver:load(username, host, datastore)
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
36 local select = self:prepare("select data from "..self.item_table.." where username=? and host=? and datastore=?");
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
37 select:execute(username, host, datastore);
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
38 local row = select:fetch();
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
39 return row and deser(row[1]) or nil;
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
40 end
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
41
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
42 function driver:store(username, host, datastore, data)
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
43 if not data or next(data) == nil then
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
44 local delete = self:prepare("delete from "..self.item_table.." where username=? and host=? and datastore=?");
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
45 delete:execute(username, host, datastore);
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
46 return true;
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
47 else
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
48 local d = self:load(username, host, datastore);
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
49 if d then -- update
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
50 local update = self:prepare("update "..self.item_table.." set data=? where username=? and host=? and datastore=?");
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
51 return update:execute(ser(data), username, host, datastore);
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
52 else -- insert
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
53 local insert = self:prepare("insert into "..self.item_table.." values (?, ?, ?, ?)");
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
54 return insert:execute(username, host, datastore, ser(data));
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
55 end
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
56 end
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
57 end
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
58
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
59 function driver:list_append(username, host, datastore, data)
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
60 if not data then return; end
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
61 local insert = self:prepare("insert into "..self.list_table.." values (?, ?, ?, ?)");
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
62 return insert:execute(username, host, datastore, ser(data));
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
63 end
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
64
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
65 function driver:list_store(username, host, datastore, data)
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
66 -- remove existing data
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
67 local delete = self:prepare("delete from "..self.list_table.." where username=? and host=? and datastore=?");
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
68 delete:execute(username, host, datastore);
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
69 if data and next(data) ~= nil then
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
70 -- add data
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
71 for _, d in ipairs(data) do
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
72 self:list_append(username, host, datastore, ser(d));
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
73 end
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
74 end
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
75 return true;
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
76 end
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
77
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
78 function driver:list_load(username, host, datastore)
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
79 local select = self:prepare("select data from "..self.list_table.." where username=? and host=? and datastore=?");
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
80 select:execute(username, host, datastore);
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
81 local r = {};
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
82 for row in select:rows() do
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
83 table.insert(r, deser(row[1]));
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
84 end
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
85 return r;
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
86 end
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
87
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
88 local _M = {};
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
89 function _M.new(dbtype, dbname, ...)
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
90 local d = {};
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
91 setmetatable(d, driver);
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
92 local dbh = get_database(dbtype, dbname, ...);
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
93 --d:set_connection(dbh);
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
94 d.connection = dbh;
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
95 return d;
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
96 end
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
97 return _M;