prosody
c5882e2e12b5
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 @ 2678:c5882e2e12b5

mod_storage, plus a bit of SQL and XML.
author Waqas Hussain <waqas20@gmail.com>
date Fri, 19 Feb 2010 22:32:28 +0500
child 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;
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
6 local deser = function(data)
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
7 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
8 if not data then return nil; end
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
9 local f = loadstring("return "..data);
c5882e2e12b5 mod_storage, plus a bit of SQL and XML.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
10 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
11 setfenv(f, {});
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;