Mercurial > prosody-hg

comparison plugins/mod_storage_sql2.lua @ 6054:7a5ddbaf758d

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Merge 0.9->0.10
author Matthew Wild <mwild1@gmail.com>
date Wed, 02 Apr 2014 17:41:38 +0100
parents 918ab89cb68d
children de4c83feb064
comparison
equal deleted inserted replaced
6053:2f93a04564b2 6054:7a5ddbaf758d
1
2 local json = require "util.json";
3 local xml_parse = require "util.xml".parse;
4 local uuid = require "util.uuid";
5 local resolve_relative_path = require "core.configmanager".resolve_relative_path;
6
7 local stanza_mt = require"util.stanza".stanza_mt;
8 local getmetatable = getmetatable;
9 local t_concat = table.concat;
10 local function is_stanza(x) return getmetatable(x) == stanza_mt; end
11
12 local noop = function() end
13 local unpack = unpack
14 local function iterator(result)
15 return function(result)
16 local row = result();
17 if row ~= nil then
18 return unpack(row);
19 end
20 end, result, nil;
21 end
22
23 local mod_sql = module:require("sql");
24 local params = module:get_option("sql");
25
26 local engine; -- TODO create engine
27
28 local function create_table()
29 local Table,Column,Index = mod_sql.Table,mod_sql.Column,mod_sql.Index;
30
31 local ProsodyTable = Table {
32 name="prosody";
33 Column { name="host", type="TEXT", nullable=false };
34 Column { name="user", type="TEXT", nullable=false };
35 Column { name="store", type="TEXT", nullable=false };
36 Column { name="key", type="TEXT", nullable=false };
37 Column { name="type", type="TEXT", nullable=false };
38 Column { name="value", type="MEDIUMTEXT", nullable=false };
39 Index { name="prosody_index", "host", "user", "store", "key" };
40 };
41 engine:transaction(function()
42 ProsodyTable:create(engine);
43 end);
44
45 local ProsodyArchiveTable = Table {
46 name="prosodyarchive";
47 Column { name="sort_id", type="INTEGER", primary_key=true, auto_increment=true };
48 Column { name="host", type="TEXT", nullable=false };
49 Column { name="user", type="TEXT", nullable=false };
50 Column { name="store", type="TEXT", nullable=false };
51 Column { name="key", type="TEXT", nullable=false }; -- item id
52 Column { name="when", type="INTEGER", nullable=false }; -- timestamp
53 Column { name="with", type="TEXT", nullable=false }; -- related id
54 Column { name="type", type="TEXT", nullable=false };
55 Column { name="value", type="MEDIUMTEXT", nullable=false };
56 Index { name="prosodyarchive_index", unique = true, "host", "user", "store", "key" };
57 };
58 engine:transaction(function()
59 ProsodyArchiveTable:create(engine);
60 end);
61 end
62
63 local function upgrade_table()
64 if params.driver == "MySQL" then
65 local success,err = engine:transaction(function()
66 local result = engine:execute("SHOW COLUMNS FROM prosody WHERE Field='value' and Type='text'");
67 if result:rowcount() > 0 then
68 module:log("info", "Upgrading database schema...");
69 engine:execute("ALTER TABLE prosody MODIFY COLUMN `value` MEDIUMTEXT");
70 module:log("info", "Database table automatically upgraded");
71 end
72 return true;
73 end);
74 if not success then
75 module:log("error", "Failed to check/upgrade database schema (%s), please see "
76 .."http://prosody.im/doc/mysql for help",
77 err or "unknown error");
78 return false;
79 end
80 -- COMPAT w/pre-0.9: Upgrade tables to UTF-8 if not already
81 local check_encoding_query = "SELECT `COLUMN_NAME`,`COLUMN_TYPE` FROM `information_schema`.`columns` WHERE `TABLE_NAME`='prosody' AND ( `CHARACTER_SET_NAME`!='utf8' OR `COLLATION_NAME`!='utf8_bin' );";
82 success,err = engine:transaction(function()
83 local result = engine:execute(check_encoding_query);
84 local n_bad_columns = result:rowcount();
85 if n_bad_columns > 0 then
86 module:log("warn", "Found %d columns in prosody table requiring encoding change, updating now...", n_bad_columns);
87 local fix_column_query1 = "ALTER TABLE `prosody` CHANGE `%s` `%s` BLOB;";
88 local fix_column_query2 = "ALTER TABLE `prosody` CHANGE `%s` `%s` %s CHARACTER SET 'utf8' COLLATE 'utf8_bin';";
89 for row in result:rows() do
90 local column_name, column_type = unpack(row);
91 engine:execute(fix_column_query1:format(column_name, column_name));
92 engine:execute(fix_column_query2:format(column_name, column_name, column_type));
93 end
94 module:log("info", "Database encoding upgrade complete!");
95 end
96 end);
97 success,err = engine:transaction(function() return engine:execute(check_encoding_query); end);
98 if not success then
99 module:log("error", "Failed to check/upgrade database encoding: %s", err or "unknown error");
100 end
101 end
102 end
103
104 do -- process options to get a db connection
105 params = params or { driver = "SQLite3" };
106
107 if params.driver == "SQLite3" then
108 params.database = resolve_relative_path(prosody.paths.data or ".", params.database or "prosody.sqlite");
109 end
110
111 assert(params.driver and params.database, "Both the SQL driver and the database need to be specified");
112
113 --local dburi = db2uri(params);
114 engine = mod_sql:create_engine(params);
115
116 engine:set_encoding();
117
118 if module:get_option("sql_manage_tables", true) then
119 -- Automatically create table, ignore failure (table probably already exists)
120 create_table();
121 -- Encoding mess
122 upgrade_table();
123 end
124 end
125
126 local function serialize(value)
127 local t = type(value);
128 if t == "string" or t == "boolean" or t == "number" then
129 return t, tostring(value);
130 elseif is_stanza(value) then
131 return "xml", tostring(value);
132 elseif t == "table" then
133 local value,err = json.encode(value);
134 if value then return "json", value; end
135 return nil, err;
136 end
137 return nil, "Unhandled value type: "..t;
138 end
139 local function deserialize(t, value)
140 if t == "string" then return value;
141 elseif t == "boolean" then
142 if value == "true" then return true;
143 elseif value == "false" then return false; end
144 elseif t == "number" then return tonumber(value);
145 elseif t == "json" then
146 return json.decode(value);
147 elseif t == "xml" then
148 return xml_parse(value);
149 end
150 end
151
152 local host = module.host;
153 local user, store;
154
155 local function keyval_store_get()
156 local haveany;
157 local result = {};
158 for row in engine:select("SELECT `key`,`type`,`value` FROM `prosody` WHERE `host`=? AND `user`=? AND `store`=?", host, user or "", store) do
159 haveany = true;
160 local k = row[1];
161 local v = deserialize(row[2], row[3]);
162 if k and v then
163 if k ~= "" then result[k] = v; elseif type(v) == "table" then
164 for a,b in pairs(v) do
165 result[a] = b;
166 end
167 end
168 end
169 end
170 if haveany then
171 return result;
172 end
173 end
174 local function keyval_store_set(data)
175 engine:delete("DELETE FROM `prosody` WHERE `host`=? AND `user`=? AND `store`=?", host, user or "", store);
176
177 if data and next(data) ~= nil then
178 local extradata = {};
179 for key, value in pairs(data) do
180 if type(key) == "string" and key ~= "" then
181 local t, value = serialize(value);
182 assert(t, value);
183 engine:insert("INSERT INTO `prosody` (`host`,`user`,`store`,`key`,`type`,`value`) VALUES (?,?,?,?,?,?)", host, user or "", store, key, t, value);
184 else
185 extradata[key] = value;
186 end
187 end
188 if next(extradata) ~= nil then
189 local t, extradata = serialize(extradata);
190 assert(t, extradata);
191 engine:insert("INSERT INTO `prosody` (`host`,`user`,`store`,`key`,`type`,`value`) VALUES (?,?,?,?,?,?)", host, user or "", store, "", t, extradata);
192 end
193 end
194 return true;
195 end
196
197 local keyval_store = {};
198 keyval_store.__index = keyval_store;
199 function keyval_store:get(username)
200 user,store = username,self.store;
201 return select(2, engine:transaction(keyval_store_get));
202 end
203 function keyval_store:set(username, data)
204 user,store = username,self.store;
205 return engine:transaction(function()
206 return keyval_store_set(data);
207 end);
208 end
209 function keyval_store:users()
210 local ok, result = engine:transaction(function()
211 return engine:select("SELECT DISTINCT `user` FROM `prosody` WHERE `host`=? AND `store`=?", host, self.store);
212 end);
213 if not ok then return ok, result end
214 return iterator(result);
215 end
216
217 local archive_store = {}
218 archive_store.__index = archive_store
219 function archive_store:append(username, key, when, with, value)
220 if value == nil then -- COMPAT early versions
221 when, with, value, key = key, when, with, value
222 end
223 local user,store = username,self.store;
224 return engine:transaction(function()
225 if key then
226 engine:delete("DELETE FROM `prosodyarchive` WHERE `host`=? AND `user`=? AND `store`=? AND `key`=?", host, user or "", store, key);
227 else
228 key = uuid.generate();
229 end
230 local t, value = serialize(value);
231 engine:insert("INSERT INTO `prosodyarchive` (`host`, `user`, `store`, `when`, `with`, `key`, `type`, `value`) VALUES (?,?,?,?,?,?,?,?)", host, user or "", store, when, with, key, t, value);
232 return key;
233 end);
234 end
235
236 -- Helpers for building the WHERE clause
237 local function archive_where(query, args, where)
238 -- Time range, inclusive
239 if query.start then
240 args[#args+1] = query.start
241 where[#where+1] = "`when` >= ?"
242 end
243
244 if query["end"] then
245 args[#args+1] = query["end"];
246 if query.start then
247 where[#where] = "`when` BETWEEN ? AND ?" -- is this inclusive?
248 else
249 where[#where+1] = "`when` <= ?"
250 end
251 end
252
253 -- Related name
254 if query.with then
255 where[#where+1] = "`with` = ?";
256 args[#args+1] = query.with
257 end
258
259 -- Unique id
260 if query.key then
261 where[#where+1] = "`key` = ?";
262 args[#args+1] = query.key
263 end
264 end
265 local function archive_where_id_range(query, args, where)
266 local args_len = #args
267 -- Before or after specific item, exclusive
268 if query.after then -- keys better be unique!
269 where[#where+1] = "`sort_id` > (SELECT `sort_id` FROM `prosodyarchive` WHERE `key` = ? AND `host` = ? AND `user` = ? AND `store` = ? LIMIT 1)"
270 args[args_len+1], args[args_len+2], args[args_len+3], args[args_len+4] = query.after, args[1], args[2], args[3];
271 args_len = args_len + 4
272 end
273 if query.before then
274 where[#where+1] = "`sort_id` < (SELECT `sort_id` FROM `prosodyarchive` WHERE `key` = ? AND `host` = ? AND `user` = ? AND `store` = ? LIMIT 1)"
275 args[args_len+1], args[args_len+2], args[args_len+3], args[args_len+4] = query.before, args[1], args[2], args[3];
276 end
277 end
278
279 function archive_store:find(username, query)
280 query = query or {};
281 local user,store = username,self.store;
282 local total;
283 local ok, result = engine:transaction(function()
284 local sql_query = "SELECT `key`, `type`, `value`, `when` FROM `prosodyarchive` WHERE %s ORDER BY `sort_id` %s%s;";
285 local args = { host, user or "", store, };
286 local where = { "`host` = ?", "`user` = ?", "`store` = ?", };
287
288 archive_where(query, args, where);
289
290 -- Total matching
291 if query.total then
292 local stats = engine:select(sql_query:gsub("^(SELECT).-(FROM)", "%1 COUNT(*) %2"):format(t_concat(where, " AND "), "DESC", ""), unpack(args));
293 if stats then
294 local _total = stats()
295 total = _total and _total[1];
296 end
297 if query.limit == 0 then -- Skip the real query
298 return noop, total;
299 end
300 end
301
302 archive_where_id_range(query, args, where);
303
304 if query.limit then
305 args[#args+1] = query.limit;
306 end
307
308 sql_query = sql_query:format(t_concat(where, " AND "), query.reverse and "DESC" or "ASC", query.limit and " LIMIT ?" or "");
309 module:log("debug", sql_query);
310 return engine:select(sql_query, unpack(args));
311 end);
312 if not ok then return ok, result end
313 return function()
314 local row = result();
315 if row ~= nil then
316 return row[1], deserialize(row[2], row[3]), row[4];
317 end
318 end, total;
319 end
320
321 function archive_store:delete(username, query)
322 query = query or {};
323 local user,store = username,self.store;
324 return engine:transaction(function()
325 local sql_query = "DELETE FROM `prosodyarchive` WHERE %s;";
326 local args = { host, user or "", store, };
327 local where = { "`host` = ?", "`user` = ?", "`store` = ?", };
328 if user == true then
329 table.remove(args, 2);
330 table.remove(where, 2);
331 end
332 archive_where(query, args, where);
333 archive_where_id_range(query, args, where);
334 sql_query = sql_query:format(t_concat(where, " AND "));
335 module:log("debug", sql_query);
336 return engine:delete(sql_query, unpack(args));
337 end);
338 end
339
340 local stores = {
341 keyval = keyval_store;
342 archive = archive_store;
343 };
344
345 local driver = {};
346
347 function driver:open(store, typ)
348 local store_mt = stores[typ or "keyval"];
349 if store_mt then
350 return setmetatable({ store = store }, store_mt);
351 end
352 return nil, "unsupported-store";
353 end
354
355 function driver:stores(username)
356 local sql = "SELECT DISTINCT `store` FROM `prosody` WHERE `host`=? AND `user`" ..
357 (username == true and "!=?" or "=?");
358 if username == true or not username then
359 username = "";
360 end
361 local ok, result = engine:transaction(function()
362 return engine:select(sql, host, username);
363 end);
364 if not ok then return ok, result end
365 return iterator(result);
366 end
367
368 function driver:purge(username)
369 return engine:transaction(function()
370 local stmt,err = engine:delete("DELETE FROM `prosody` WHERE `host`=? AND `user`=?", host, username);
371 return true,err;
372 end);
373 end
374
375 module:provides("storage", driver);
376
377