prosody
b351fa47a743
plugins/mod_storage_sql.lua

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

Software / code / prosody

Comparison

plugins/mod_storage_sql.lua @ 3729:b351fa47a743

mod_storage_sql: Initial commit of new SQL data driver.
author Waqas Hussain <waqas20@gmail.com>
date Sun, 12 Dec 2010 05:18:36 +0500
child 3730:5aa586d1366e
comparison
equal deleted inserted replaced
3728:b1b8fe846d68 3729:b351fa47a743
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);