prosody
08560575762f
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 @ 4105:08560575762f

mod_storage_sql: Reconnect in all cases, and rollback on error.
author Waqas Hussain <waqas20@gmail.com>
date Mon, 10 Jan 2011 21:07:59 +0500
parent 4101:06778bc27d53
child 4109:d26db1f936f8
comparison
equal deleted inserted replaced
4103:c994524ef953 4105:08560575762f
26 local next = next; 26 local next = next;
27 local setmetatable = setmetatable; 27 local setmetatable = setmetatable;
28 local xpcall = xpcall; 28 local xpcall = xpcall;
29 local json = require "util.json"; 29 local json = require "util.json";
30 30
31 local DBI;
31 local connection; 32 local connection;
32 local host,user,store = module.host; 33 local host,user,store = module.host;
33 local params = module:get_option("sql"); 34 local params = module:get_option("sql");
34 35
35 local resolve_relative_path = require "core.configmanager".resolve_relative_path; 36 local resolve_relative_path = require "core.configmanager".resolve_relative_path;
36 37
38 local function test_connection()
39 if not connection then return nil; end
40 if connection:ping() then
41 return true;
42 else
43 module:log("debug", "Database connection closed");
44 connection = nil;
45 end
46 end
47 local function connect()
48 if not test_connection() then
49 prosody.unlock_globals();
50 local dbh, err = DBI.Connect(
51 params.driver, params.database,
52 params.username, params.password,
53 params.host, params.port
54 );
55 prosody.lock_globals();
56 if not dbh then
57 module:log("debug", "Database connection failed: %s", tostring(err));
58 return nil, err;
59 end
60 module:log("debug", "Successfully connected to database");
61 dbh:autocommit(false); -- don't commit automatically
62 connection = dbh;
63 return connection;
64 end
65 end
66
37 do -- process options to get a db connection 67 do -- process options to get a db connection
38 local DBI = require "DBI"; 68 DBI = require "DBI";
39 69
40 params = params or { driver = "SQLite3" }; 70 params = params or { driver = "SQLite3" };
41 71
42 if params.driver == "SQLite3" then 72 if params.driver == "SQLite3" then
43 params.database = resolve_relative_path(prosody.paths.data or ".", params.database or "prosody.sqlite"); 73 params.database = resolve_relative_path(prosody.paths.data or ".", params.database or "prosody.sqlite");
44 end 74 end
45 75
46 assert(params.driver and params.database, "Both the SQL driver and the database need to be specified"); 76 assert(params.driver and params.database, "Both the SQL driver and the database need to be specified");
47 77
48 prosody.unlock_globals(); 78 assert(connect());
49 local dbh, err = DBI.Connect(
50 params.driver, params.database,
51 params.username, params.password,
52 params.host, params.port
53 );
54 prosody.lock_globals();
55 assert(dbh, err);
56
57 dbh:autocommit(false); -- don't commit automatically
58 connection = dbh;
59 79
60 -- Automatically create table, ignore failure (table probably already exists) 80 -- Automatically create table, ignore failure (table probably already exists)
61 local create_sql = "CREATE TABLE `prosody` (`host` TEXT, `user` TEXT, `store` TEXT, `key` TEXT, `type` TEXT, `value` TEXT);"; 81 local create_sql = "CREATE TABLE `prosody` (`host` TEXT, `user` TEXT, `store` TEXT, `key` TEXT, `type` TEXT, `value` TEXT);";
62 if params.driver == "PostgreSQL" then 82 if params.driver == "PostgreSQL" then
63 create_sql = create_sql:gsub("`", "\""); 83 create_sql = create_sql:gsub("`", "\"");
99 if params.driver == "PostgreSQL" then 119 if params.driver == "PostgreSQL" then
100 sql = sql:gsub("`", "\""); 120 sql = sql:gsub("`", "\"");
101 end 121 end
102 -- do prepared statement stuff 122 -- do prepared statement stuff
103 local stmt, err = connection:prepare(sql); 123 local stmt, err = connection:prepare(sql);
124 if not stmt and not test_connection() then error("connection failed"); end
104 if not stmt then module:log("error", "QUERY FAILED: %s %s", err, debug.traceback()); return nil, err; end 125 if not stmt then module:log("error", "QUERY FAILED: %s %s", err, debug.traceback()); return nil, err; end
105 -- run query 126 -- run query
106 local ok, err = stmt:execute(host or "", user or "", store or "", ...); 127 local ok, err = stmt:execute(host or "", user or "", store or "", ...);
128 if not ok and not test_connection() then error("connection failed"); end
107 if not ok then return nil, err; end 129 if not ok then return nil, err; end
108 130
109 return stmt; 131 return stmt;
110 end 132 end
111 local function setsql(sql, ...) 133 local function setsql(sql, ...)
115 end 137 end
116 local function transact(...) 138 local function transact(...)
117 -- ... 139 -- ...
118 end 140 end
119 local function rollback(...) 141 local function rollback(...)
120 connection:rollback(); -- FIXME check for rollback error? 142 if connection then connection:rollback(); end -- FIXME check for rollback error?
121 return ...; 143 return ...;
122 end 144 end
123 local function commit(...) 145 local function commit(...)
124 if not connection:commit() then return nil, "SQL commit failed"; end 146 if not connection:commit() then return nil, "SQL commit failed"; end
125 return ...; 147 return ...;
126 end 148 end
127 149
128 local function keyval_store_get() 150 local function keyval_store_get()
129 local stmt, err = getsql("SELECT * FROM `prosody` WHERE `host`=? AND `user`=? AND `store`=?"); 151 local stmt, err = getsql("SELECT * FROM `prosody` WHERE `host`=? AND `user`=? AND `store`=?");
130 if not stmt then return nil, err; end 152 if not stmt then return rollback(nil, err); end
131 153
132 local haveany; 154 local haveany;
133 local result = {}; 155 local result = {};
134 for row in stmt:rows(true) do 156 for row in stmt:rows(true) do
135 haveany = true; 157 haveany = true;
145 end 167 end
146 return commit(haveany and result or nil); 168 return commit(haveany and result or nil);
147 end 169 end
148 local function keyval_store_set(data) 170 local function keyval_store_set(data)
149 local affected, err = setsql("DELETE FROM `prosody` WHERE `host`=? AND `user`=? AND `store`=?"); 171 local affected, err = setsql("DELETE FROM `prosody` WHERE `host`=? AND `user`=? AND `store`=?");
172 if not affected then return rollback(affected, err); end
150 173
151 if data and next(data) ~= nil then 174 if data and next(data) ~= nil then
152 local extradata = {}; 175 local extradata = {};
153 for key, value in pairs(data) do 176 for key, value in pairs(data) do
154 if type(key) == "string" and key ~= "" then 177 if type(key) == "string" and key ~= "" then
172 195
173 local keyval_store = {}; 196 local keyval_store = {};
174 keyval_store.__index = keyval_store; 197 keyval_store.__index = keyval_store;
175 function keyval_store:get(username) 198 function keyval_store:get(username)
176 user,store = username,self.store; 199 user,store = username,self.store;
200 if not connection and not connect() then return nil, "Unable to connect to database"; end
177 local success, ret, err = xpcall(keyval_store_get, debug.traceback); 201 local success, ret, err = xpcall(keyval_store_get, debug.traceback);
202 if not connection and connect() then
203 success, ret, err = xpcall(keyval_store_get, debug.traceback);
204 end
178 if success then return ret, err; else return rollback(nil, ret); end 205 if success then return ret, err; else return rollback(nil, ret); end
179 end 206 end
180 function keyval_store:set(username, data) 207 function keyval_store:set(username, data)
181 user,store = username,self.store; 208 user,store = username,self.store;
209 if not connection and not connect() then return nil, "Unable to connect to database"; end
182 local success, ret, err = xpcall(function() return keyval_store_set(data); end, debug.traceback); 210 local success, ret, err = xpcall(function() return keyval_store_set(data); end, debug.traceback);
211 if not connection and connect() then
212 success, ret, err = xpcall(function() return keyval_store_set(data); end, debug.traceback);
213 end
183 if success then return ret, err; else return rollback(nil, ret); end 214 if success then return ret, err; else return rollback(nil, ret); end
184 end 215 end
185 216
186 local function map_store_get(key) 217 local function map_store_get(key)
187 local stmt, err = getsql("SELECT * FROM `prosody` WHERE `host`=? AND `user`=? AND `store`=? AND `key`=?", key or ""); 218 local stmt, err = getsql("SELECT * FROM `prosody` WHERE `host`=? AND `user`=? AND `store`=? AND `key`=?", key or "");
188 if not stmt then return nil, err; end 219 if not stmt then return rollback(nil, err); end
189 220
190 local haveany; 221 local haveany;
191 local result = {}; 222 local result = {};
192 for row in stmt:rows(true) do 223 for row in stmt:rows(true) do
193 haveany = true; 224 haveany = true;
203 end 234 end
204 return commit(haveany and result[key] or nil); 235 return commit(haveany and result[key] or nil);
205 end 236 end
206 local function map_store_set(key, data) 237 local function map_store_set(key, data)
207 local affected, err = setsql("DELETE FROM `prosody` WHERE `host`=? AND `user`=? AND `store`=? AND `key`=?", key or ""); 238 local affected, err = setsql("DELETE FROM `prosody` WHERE `host`=? AND `user`=? AND `store`=? AND `key`=?", key or "");
239 if not affected then return rollback(affected, err); end
208 240
209 if data and next(data) ~= nil then 241 if data and next(data) ~= nil then
210 if type(key) == "string" and key ~= "" then 242 if type(key) == "string" and key ~= "" then
211 local t, value = serialize(data); 243 local t, value = serialize(data);
212 if not t then return rollback(t, value); end 244 if not t then return rollback(t, value); end