5 Prosody - key-value, map
6 | host | user | store | key | subkey | type | value |
8 | host | user | store | key | time | stanzatype | jsonvalue |
12 | host | user | "roster" | "contactjid" | item-subkey | type | value |
13 | host | user | "roster" | NULL | NULL | "json" | roster[false] data |
15 | host | user | "accounts" | "username" | NULL | type | value |
17 Offline - ProsodyArchive
18 | host | user | "offline" | "contactjid" | time | "message" | json|XML |
23 local tostring = tostring;
24 local tonumber = tonumber;
27 local setmetatable = setmetatable;
28 local json = { stringify = function(s) return require"util.serialization".serialize(s) end, parse = require"util.serialization".deserialze };
30 local connection = ...;
31 local host,user,store = module.host;
33 do -- process options to get a db connection
34 local DBI = require "DBI";
36 local params = module:get_option("sql");
37 assert(params and params.driver and params.database, "invalid params");
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
45 prosody.lock_globals();
48 dbh:autocommit(false); -- don't commit automatically
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
61 return nil, "Unhandled value type: "..t;
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);
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
79 local ok, err = stmt:execute(host, user, store, ...);
80 if not ok then return nil, err; 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();
89 local function transact(...)
92 local function rollback(...)
93 connection:rollback(); -- FIXME check for rollback error?
96 local function commit(...)
97 if not connection:commit() then return nil, "SQL commit failed"; end
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
110 for row in stmt:rows(true) do
113 local v = deserialize(row.type, row.value);
115 if k then result[k] = v; elseif type(v) == "table" then
116 for a,b in pairs(v) do
122 return haveany and result or nil;
124 function keyval_store:set(username, data)
125 user,store = username,self.store;
127 local affected, err = setsql("DELETE FROM Prosody WHERE host=? AND user=? AND store=? AND subkey=NULL");
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
138 extradata[key] = value;
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
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
160 for row in stmt:rows(true) do
162 local k = row.subkey;
163 local v = deserialize(row.type, row.value);
165 if k then result[k] = v; elseif type(v) == "table" then
166 for a,b in pairs(v) do
172 return haveany and result or nil;
174 function map_store:set(username, key, data)
175 user,store = username,self.store;
177 local affected, err = setsql("DELETE FROM Prosody WHERE host=? AND user=? AND store=? AND key=?", key);
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
188 extradata[subkey] = value;
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
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;
206 local cols = {"from", "to", "jid", "typ"};
207 local vals = { from , to , jid , typ };
209 local query = "SELECT * FROM ProsodyArchive WHERE host=? AND user=? AND store=?";
211 query = query.." ORDER BY time";
212 --local stmt, err = getsql("SELECT * FROM Prosody WHERE host=? AND user=? AND store=? AND key=?", key);
214 return nil, "not-implemented"
217 local driver = { name = "sql" };
219 function driver:open(store, typ)
220 if not typ then -- default key-value store
221 return setmetatable({ store = store }, keyval_store);
223 return nil, "unsupported-store";
226 module:add_item("data-driver", driver);