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
|
-- Basic SQL driver
-- This driver stores data as simple key-values
local ser = require "util.serialization".serialize;
local deser = function(data)
module:log("debug", "deser: %s", tostring(data));
if not data then return nil; end
local f = loadstring("return "..data);
if not f then return nil; end
setfenv(f, {});
local s, d = pcall(f);
if not s then return nil; end
return d;
end;
local driver = {};
driver.__index = driver;
driver.item_table = "item";
driver.list_table = "list";
function driver:prepare(sql)
module:log("debug", "query: %s", sql);
local err;
if not self.sqlcache then self.sqlcache = {}; end
local r = self.sqlcache[sql];
if r then return r; end
r, err = self.connection:prepare(sql);
if not r then error("Unable to prepare SQL statement: "..err); end
self.sqlcache[sql] = r;
return r;
end
function driver:load(username, host, datastore)
local select = self:prepare("select data from "..self.item_table.." where username=? and host=? and datastore=?");
select:execute(username, host, datastore);
local row = select:fetch();
return row and deser(row[1]) or nil;
end
function driver:store(username, host, datastore, data)
if not data or next(data) == nil then
local delete = self:prepare("delete from "..self.item_table.." where username=? and host=? and datastore=?");
delete:execute(username, host, datastore);
return true;
else
local d = self:load(username, host, datastore);
if d then -- update
local update = self:prepare("update "..self.item_table.." set data=? where username=? and host=? and datastore=?");
return update:execute(ser(data), username, host, datastore);
else -- insert
local insert = self:prepare("insert into "..self.item_table.." values (?, ?, ?, ?)");
return insert:execute(username, host, datastore, ser(data));
end
end
end
function driver:list_append(username, host, datastore, data)
if not data then return; end
local insert = self:prepare("insert into "..self.list_table.." values (?, ?, ?, ?)");
return insert:execute(username, host, datastore, ser(data));
end
function driver:list_store(username, host, datastore, data)
-- remove existing data
local delete = self:prepare("delete from "..self.list_table.." where username=? and host=? and datastore=?");
delete:execute(username, host, datastore);
if data and next(data) ~= nil then
-- add data
for _, d in ipairs(data) do
self:list_append(username, host, datastore, ser(d));
end
end
return true;
end
function driver:list_load(username, host, datastore)
local select = self:prepare("select data from "..self.list_table.." where username=? and host=? and datastore=?");
select:execute(username, host, datastore);
local r = {};
for row in select:rows() do
table.insert(r, deser(row[1]));
end
return r;
end
local _M = {};
function _M.new(dbtype, dbname, ...)
local d = {};
setmetatable(d, driver);
local dbh = get_database(dbtype, dbname, ...);
--d:set_connection(dbh);
d.connection = dbh;
return d;
end
return _M;
|
