|
1 |
|
2 --[[ |
|
3 |
|
4 DB Tables: |
|
5 Prosody - key-value, map |
|
6 | host | user | store | key | subkey | type | value | |
|
7 ProsodyArchive - list |
|
8 | host | user | store | key | time | stanzatype | jsonvalue | |
|
9 |
|
10 Mapping: |
|
11 Roster - Prosody |
|
12 | host | user | "roster" | "contactjid" | item-subkey | type | value | |
|
13 | host | user | "roster" | NULL | NULL | "json" | roster[false] data | |
|
14 Account - Prosody |
|
15 | host | user | "accounts" | "username" | NULL | type | value | |
|
16 |
|
17 Offline - ProsodyArchive |
|
18 | host | user | "offline" | "contactjid" | time | "message" | json|XML | |
|
19 |
|
20 ]] |
|
21 |
|
22 local type = type; |
|
23 local tostring = tostring; |
|
24 local tonumber = tonumber; |
|
25 local pairs = pairs; |
|
26 local next = next; |
|
27 local setmetatable = setmetatable; |
|
28 local json = { stringify = function(s) return require"util.serialzation".serialize(s) end, parse = require"util.serialization".deserialze }; |
|
29 |
|
30 local connection = ...; |
|
31 local host,user,store = module.host; |
|
32 |
|
33 do -- process options to get a db connection |
|
34 local DBI = require "DBI"; |
|
35 |
|
36 local params = module:get_option("sql"); |
|
37 assert(params and params.driver and params.database, "invalid params"); |
|
38 |
|
39 prosody.unlock_globals(); |
|
40 local dbh, err = DBI.Connect( |
|
41 params.driver, params.database, |
|
42 params.username, params.password, |
|
43 params.host, params.port |
|
44 ); |
|
45 prosody.lock_globals(); |
|
46 assert(dbh, err); |
|
47 |
|
48 dbh:autocommit(false); -- don't commit automatically |
|
49 connection = dbh; |
|
50 end |
|
51 |
|
52 local function serialize(value) |
|
53 local t = type(value); |
|
54 if t == "string" or t == "boolean" or t == "number" then |
|
55 return t, tostring(value); |
|
56 elseif t == "table" then |
|
57 local value,err = json.stringify(value); |
|
58 if value then return "json", value; end |
|
59 return nil, err; |
|
60 end |
|
61 return nil, "Unhandled value type: "..t; |
|
62 end |
|
63 local function deserialize(t, value) |
|
64 if t == "string" then return t; |
|
65 elseif t == "boolean" then |
|
66 if value == "true" then return true; |
|
67 elseif value == "false" then return false; end |
|
68 elseif t == "number" then return tonumber(value); |
|
69 elseif value == "json" then |
|
70 return json.parse(value); |
|
71 end |
|
72 end |
|
73 |
|
74 local function getsql(sql, ...) |
|
75 -- do prepared statement stuff |
|
76 local stmt, err = connection:prepare(sql); |
|
77 if not stmt then return nil, err; end |
|
78 -- run query |
|
79 local ok, err = stmt:execute(host, user, store, ...); |
|
80 if not ok then return nil, err; end |
|
81 |
|
82 return stmt; |
|
83 end |
|
84 local function setsql(sql, ...) |
|
85 local stmt, err = getsql(sql, ...); |
|
86 if not stmt then return stmt, err; end |
|
87 return stmt:affected(); |
|
88 end |
|
89 local function transact(...) |
|
90 -- ... |
|
91 end |
|
92 local function rollback(...) |
|
93 connection:rollback(); -- FIXME check for rollback error? |
|
94 return ...; |
|
95 end |
|
96 local function commit(...) |
|
97 if not connection:commit() then return nil, "SQL commit failed"; end |
|
98 return ...; |
|
99 end |
|
100 |
|
101 local keyval_store = {}; |
|
102 keyval_store.__index = keyval_store; |
|
103 function keyval_store:get(username) |
|
104 user,store = username,self.store; |
|
105 local stmt, err = getsql("SELECT * FROM Prosody WHERE host=? AND user=? AND store=? AND subkey=NULL"); |
|
106 if not stmt then return nil, err; end |
|
107 |
|
108 local haveany; |
|
109 local result = {}; |
|
110 for row in stmt:rows(true) do |
|
111 haveany = true; |
|
112 local k = row.key; |
|
113 local v = deserialize(row.type, row.value); |
|
114 if v then |
|
115 if k then result[k] = v; elseif type(v) == "table" then |
|
116 for a,b in pairs(v) do |
|
117 result[a] = b; |
|
118 end |
|
119 end |
|
120 end |
|
121 end |
|
122 return haveany and result or nil; |
|
123 end |
|
124 function keyval_store:set(username, data) |
|
125 user,store = username,self.store; |
|
126 -- start transaction |
|
127 local affected, err = setsql("DELETE FROM Prosody WHERE host=? AND user=? AND store=? AND subkey=NULL"); |
|
128 |
|
129 if data and next(data) ~= nil then |
|
130 local extradata = {}; |
|
131 for key, value in pairs(data) do |
|
132 if type(key) == "string" then |
|
133 local t, value = serialize(value); |
|
134 if not t then return rollback(t, value); end |
|
135 local ok, err = setsql("INSERT INTO Prosody (host,user,store,key,type,value) VALUES (?,?,?,?,?,?)", key, t, value); |
|
136 if not ok then return rollback(ok, err); end |
|
137 else |
|
138 extradata[key] = value; |
|
139 end |
|
140 end |
|
141 if next(extradata) ~= nil then |
|
142 local t, extradata = serialize(extradata); |
|
143 if not t then return rollback(t, extradata); end |
|
144 local ok, err = setsql("INSERT INTO Prosody (host,user,store,key,type,value) VALUES (?,?,?,?,?,?)", nil, t, extradata); |
|
145 if not ok then return rollback(ok, err); end |
|
146 end |
|
147 end |
|
148 return commit(true); |
|
149 end |
|
150 |
|
151 local map_store = {}; |
|
152 map_store.__index = map_store; |
|
153 function map_store:get(username, key) |
|
154 user,store = username,self.store; |
|
155 local stmt, err = getsql("SELECT * FROM Prosody WHERE host=? AND user=? AND store=? AND key=?", key); |
|
156 if not stmt then return nil, err; end |
|
157 |
|
158 local haveany; |
|
159 local result = {}; |
|
160 for row in stmt:rows(true) do |
|
161 haveany = true; |
|
162 local k = row.subkey; |
|
163 local v = deserialize(row.type, row.value); |
|
164 if v then |
|
165 if k then result[k] = v; elseif type(v) == "table" then |
|
166 for a,b in pairs(v) do |
|
167 result[a] = b; |
|
168 end |
|
169 end |
|
170 end |
|
171 end |
|
172 return haveany and result or nil; |
|
173 end |
|
174 function map_store:set(username, key, data) |
|
175 user,store = username,self.store; |
|
176 -- start transaction |
|
177 local affected, err = setsql("DELETE FROM Prosody WHERE host=? AND user=? AND store=? AND key=?", key); |
|
178 |
|
179 if data and next(data) ~= nil then |
|
180 local extradata = {}; |
|
181 for subkey, value in pairs(data) do |
|
182 if type(subkey) == "string" then |
|
183 local t, value = serialize(value); |
|
184 if not t then return rollback(t, value); end |
|
185 local ok, err = setsql("INSERT INTO Prosody (host,user,store,key,subkey,type,value) VALUES (?,?,?,?,?,?)", key, subkey, t, value); |
|
186 if not ok then return rollback(ok, err); end |
|
187 else |
|
188 extradata[subkey] = value; |
|
189 end |
|
190 end |
|
191 if next(extradata) ~= nil then |
|
192 local t, extradata = serialize(extradata); |
|
193 if not t then return rollback(t, extradata); end |
|
194 local ok, err = setsql("INSERT INTO Prosody (host,user,store,key,subkey,type,value) VALUES (?,?,?,?,?,?)", key, nil, t, extradata); |
|
195 if not ok then return rollback(ok, err); end |
|
196 end |
|
197 end |
|
198 return commit(true); |
|
199 end |
|
200 |
|
201 local list_store = {}; |
|
202 list_store.__index = list_store; |
|
203 function list_store:scan(username, from, to, jid, typ) |
|
204 user,store = username,self.store; |
|
205 |
|
206 local cols = {"from", "to", "jid", "typ"}; |
|
207 local vals = { from , to , jid , typ }; |
|
208 local stmt, err; |
|
209 local query = "SELECT * FROM ProsodyArchive WHERE host=? AND user=? AND store=?"; |
|
210 |
|
211 query = query.." ORDER BY time"; |
|
212 --local stmt, err = getsql("SELECT * FROM Prosody WHERE host=? AND user=? AND store=? AND key=?", key); |
|
213 |
|
214 return nil, "not-implemented" |
|
215 end |
|
216 |
|
217 local driver = { name = "sql" }; |
|
218 |
|
219 function driver:open(store, typ) |
|
220 if not typ then -- default key-value store |
|
221 return setmetatable({ store = store }, keyval_store); |
|
222 end |
|
223 return nil, "unsupported-store"; |
|
224 end |
|
225 |
|
226 module:add_item("data-driver", driver); |