Mercurial > prosody-hg

comparison plugins/mod_storage_sql.lua @ 4101:06778bc27d53

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
mod_storage_sql: Create table automatically for all databases now, not just SQLite. Also rename table from Prosody -> prosody.
author Matthew Wild <mwild1@gmail.com>
date Sat, 08 Jan 2011 23:09:21 +0000
parents 3b991ceb228e
children 08560575762f
comparison
equal deleted inserted replaced
4096:3b991ceb228e 4101:06778bc27d53
55 assert(dbh, err); 55 assert(dbh, err);
56 56
57 dbh:autocommit(false); -- don't commit automatically 57 dbh:autocommit(false); -- don't commit automatically
58 connection = dbh; 58 connection = dbh;
59 59
60 if params.driver == "SQLite3" then -- auto initialize 60 -- Automatically create table, ignore failure (table probably already exists)
61 local stmt = assert(connection:prepare("SELECT COUNT(*) FROM `sqlite_master` WHERE `type`='table' AND `name`='Prosody';")); 61 local create_sql = "CREATE TABLE `prosody` (`host` TEXT, `user` TEXT, `store` TEXT, `key` TEXT, `type` TEXT, `value` TEXT);";
62 local ok = assert(stmt:execute()); 62 if params.driver == "PostgreSQL" then
63 local count = stmt:fetch()[1]; 63 create_sql = create_sql:gsub("`", "\"");
64 if count == 0 then 64 end
65 local stmt = assert(connection:prepare("CREATE TABLE `Prosody` (`host` TEXT, `user` TEXT, `store` TEXT, `key` TEXT, `type` TEXT, `value` TEXT);")); 65
66 assert(stmt:execute()); 66 local stmt = connection:prepare(create_sql);
67 module:log("debug", "Initialized new SQLite3 database"); 67 if stmt then
68 end 68 local ok = stmt:execute();
69 assert(connection:commit()); 69 local commit_ok = connection:commit();
70 --print("===", json.encode()) 70 if ok and commit_ok then
71 module:log("info", "Initialized new %s database with prosody table", params.driver);
72 end
71 end 73 end
72 end 74 end
73 75
74 local function serialize(value) 76 local function serialize(value)
75 local t = type(value); 77 local t = type(value);
122 if not connection:commit() then return nil, "SQL commit failed"; end 124 if not connection:commit() then return nil, "SQL commit failed"; end
123 return ...; 125 return ...;
124 end 126 end
125 127
126 local function keyval_store_get() 128 local function keyval_store_get()
127 local stmt, err = getsql("SELECT * FROM `Prosody` WHERE `host`=? AND `user`=? AND `store`=?"); 129 local stmt, err = getsql("SELECT * FROM `prosody` WHERE `host`=? AND `user`=? AND `store`=?");
128 if not stmt then return nil, err; end 130 if not stmt then return nil, err; end
129 131
130 local haveany; 132 local haveany;
131 local result = {}; 133 local result = {};
132 for row in stmt:rows(true) do 134 for row in stmt:rows(true) do
142 end 144 end
143 end 145 end
144 return commit(haveany and result or nil); 146 return commit(haveany and result or nil);
145 end 147 end
146 local function keyval_store_set(data) 148 local function keyval_store_set(data)
147 local affected, err = setsql("DELETE FROM `Prosody` WHERE `host`=? AND `user`=? AND `store`=?"); 149 local affected, err = setsql("DELETE FROM `prosody` WHERE `host`=? AND `user`=? AND `store`=?");
148 150
149 if data and next(data) ~= nil then 151 if data and next(data) ~= nil then
150 local extradata = {}; 152 local extradata = {};
151 for key, value in pairs(data) do 153 for key, value in pairs(data) do
152 if type(key) == "string" and key ~= "" then 154 if type(key) == "string" and key ~= "" then
153 local t, value = serialize(value); 155 local t, value = serialize(value);
154 if not t then return rollback(t, value); end 156 if not t then return rollback(t, value); end
155 local ok, err = setsql("INSERT INTO `Prosody` (`host`,`user`,`store`,`key`,`type`,`value`) VALUES (?,?,?,?,?,?)", key, t, value); 157 local ok, err = setsql("INSERT INTO `prosody` (`host`,`user`,`store`,`key`,`type`,`value`) VALUES (?,?,?,?,?,?)", key, t, value);
156 if not ok then return rollback(ok, err); end 158 if not ok then return rollback(ok, err); end
157 else 159 else
158 extradata[key] = value; 160 extradata[key] = value;
159 end 161 end
160 end 162 end
161 if next(extradata) ~= nil then 163 if next(extradata) ~= nil then
162 local t, extradata = serialize(extradata); 164 local t, extradata = serialize(extradata);
163 if not t then return rollback(t, extradata); end 165 if not t then return rollback(t, extradata); end
164 local ok, err = setsql("INSERT INTO `Prosody` (`host`,`user`,`store`,`key`,`type`,`value`) VALUES (?,?,?,?,?,?)", "", t, extradata); 166 local ok, err = setsql("INSERT INTO `prosody` (`host`,`user`,`store`,`key`,`type`,`value`) VALUES (?,?,?,?,?,?)", "", t, extradata);
165 if not ok then return rollback(ok, err); end 167 if not ok then return rollback(ok, err); end
166 end 168 end
167 end 169 end
168 return commit(true); 170 return commit(true);
169 end 171 end
180 local success, ret, err = xpcall(function() return keyval_store_set(data); end, debug.traceback); 182 local success, ret, err = xpcall(function() return keyval_store_set(data); end, debug.traceback);
181 if success then return ret, err; else return rollback(nil, ret); end 183 if success then return ret, err; else return rollback(nil, ret); end
182 end 184 end
183 185
184 local function map_store_get(key) 186 local function map_store_get(key)
185 local stmt, err = getsql("SELECT * FROM `Prosody` WHERE `host`=? AND `user`=? AND `store`=? AND `key`=?", key or ""); 187 local stmt, err = getsql("SELECT * FROM `prosody` WHERE `host`=? AND `user`=? AND `store`=? AND `key`=?", key or "");
186 if not stmt then return nil, err; end 188 if not stmt then return nil, err; end
187 189
188 local haveany; 190 local haveany;
189 local result = {}; 191 local result = {};
190 for row in stmt:rows(true) do 192 for row in stmt:rows(true) do
200 end 202 end
201 end 203 end
202 return commit(haveany and result[key] or nil); 204 return commit(haveany and result[key] or nil);
203 end 205 end
204 local function map_store_set(key, data) 206 local function map_store_set(key, data)
205 local affected, err = setsql("DELETE FROM `Prosody` WHERE `host`=? AND `user`=? AND `store`=? AND `key`=?", key or ""); 207 local affected, err = setsql("DELETE FROM `prosody` WHERE `host`=? AND `user`=? AND `store`=? AND `key`=?", key or "");
206 208
207 if data and next(data) ~= nil then 209 if data and next(data) ~= nil then
208 if type(key) == "string" and key ~= "" then 210 if type(key) == "string" and key ~= "" then
209 local t, value = serialize(data); 211 local t, value = serialize(data);
210 if not t then return rollback(t, value); end 212 if not t then return rollback(t, value); end
211 local ok, err = setsql("INSERT INTO `Prosody` (`host`,`user`,`store`,`key`,`type`,`value`) VALUES (?,?,?,?,?,?)", key, t, value); 213 local ok, err = setsql("INSERT INTO `prosody` (`host`,`user`,`store`,`key`,`type`,`value`) VALUES (?,?,?,?,?,?)", key, t, value);
212 if not ok then return rollback(ok, err); end 214 if not ok then return rollback(ok, err); end
213 else 215 else
214 -- TODO non-string keys 216 -- TODO non-string keys
215 end 217 end
216 end 218 end
236 user,store = username,self.store; 238 user,store = username,self.store;
237 239
238 local cols = {"from", "to", "jid", "typ"}; 240 local cols = {"from", "to", "jid", "typ"};
239 local vals = { from , to , jid , typ }; 241 local vals = { from , to , jid , typ };
240 local stmt, err; 242 local stmt, err;
241 local query = "SELECT * FROM `ProsodyArchive` WHERE `host`=? AND `user`=? AND `store`=?"; 243 local query = "SELECT * FROM `prosodyarchive` WHERE `host`=? AND `user`=? AND `store`=?";
242 244
243 query = query.." ORDER BY time"; 245 query = query.." ORDER BY time";
244 --local stmt, err = getsql("SELECT * FROM `Prosody` WHERE `host`=? AND `user`=? AND `store`=? AND `key`=?", key or ""); 246 --local stmt, err = getsql("SELECT * FROM `prosody` WHERE `host`=? AND `user`=? AND `store`=? AND `key`=?", key or "");
245 247
246 return nil, "not-implemented" 248 return nil, "not-implemented"
247 end 249 end
248 250
249 local driver = { name = "sql" }; 251 local driver = { name = "sql" };