Mercurial > lxmppd-hg-xmllex / file comparison
comparison: plugins/mod_storage_sql.lua
plugins/mod_storage_sql.lua
- changeset 3974
- af40a7ce4f77
- parent 3772
- e1f6fe098404
- child 3975
- aa5e93e61760
equal
deleted
inserted
replaced
| 3973:f5416372350d | 3974:af40a7ce4f77 |
|---|---|
| 47 | 47 |
| 48 dbh:autocommit(false); -- don't commit automatically | 48 dbh:autocommit(false); -- don't commit automatically |
| 49 connection = dbh; | 49 connection = dbh; |
| 50 | 50 |
| 51 if params.driver == "SQLite3" then -- auto initialize | 51 if params.driver == "SQLite3" then -- auto initialize |
| 52 local stmt = assert(connection:prepare("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='Prosody';")); | 52 local stmt = assert(connection:prepare("SELECT COUNT(*) FROM `sqlite_master` WHERE `type`='table' AND `name`='Prosody';")); |
| 53 local ok = assert(stmt:execute()); | 53 local ok = assert(stmt:execute()); |
| 54 local count = stmt:fetch()[1]; | 54 local count = stmt:fetch()[1]; |
| 55 if count == 0 then | 55 if count == 0 then |
| 56 local stmt = assert(connection:prepare("CREATE TABLE Prosody (host TEXT, user TEXT, store TEXT, key TEXT, subkey TEXT, type TEXT, value TEXT);")); | 56 local stmt = assert(connection:prepare("CREATE TABLE `Prosody` (`host` TEXT, `user` TEXT, `store` TEXT, `key` TEXT, `subkey` TEXT, `type` TEXT, `value` TEXT);")); |
| 57 assert(stmt:execute()); | 57 assert(stmt:execute()); |
| 58 assert(connection:commit()); | 58 assert(connection:commit()); |
| 59 module:log("debug", "Initialized new SQLite3 database"); | 59 module:log("debug", "Initialized new SQLite3 database"); |
| 60 end | 60 end |
| 61 --print("===", json.stringify()) | 61 --print("===", json.stringify()) |
| 87 local function getsql(sql, ...) | 87 local function getsql(sql, ...) |
| 88 -- do prepared statement stuff | 88 -- do prepared statement stuff |
| 89 local stmt, err = connection:prepare(sql); | 89 local stmt, err = connection:prepare(sql); |
| 90 if not stmt then return nil, err; end | 90 if not stmt then return nil, err; end |
| 91 -- run query | 91 -- run query |
| 92 local ok, err = stmt:execute(host, user, store, ...); | 92 local ok, err = stmt:execute(host or "", user or "", store or "", ...); |
| 93 if not ok then return nil, err; end | 93 if not ok then return nil, err; end |
| 94 | 94 |
| 95 return stmt; | 95 return stmt; |
| 96 end | 96 end |
| 97 local function setsql(sql, ...) | 97 local function setsql(sql, ...) |
| 113 | 113 |
| 114 local keyval_store = {}; | 114 local keyval_store = {}; |
| 115 keyval_store.__index = keyval_store; | 115 keyval_store.__index = keyval_store; |
| 116 function keyval_store:get(username) | 116 function keyval_store:get(username) |
| 117 user,store = username,self.store; | 117 user,store = username,self.store; |
| 118 local stmt, err = getsql("SELECT * FROM Prosody WHERE host IS ? AND user IS ? AND store IS ? AND subkey IS NULL"); | 118 local stmt, err = getsql("SELECT * FROM `Prosody` WHERE `host`=? AND `user`=? AND `store`=? AND `subkey`=''"); |
| 119 if not stmt then return nil, err; end | 119 if not stmt then return nil, err; end |
| 120 | 120 |
| 121 local haveany; | 121 local haveany; |
| 122 local result = {}; | 122 local result = {}; |
| 123 for row in stmt:rows(true) do | 123 for row in stmt:rows(true) do |
| 135 return haveany and result or nil; | 135 return haveany and result or nil; |
| 136 end | 136 end |
| 137 function keyval_store:set(username, data) | 137 function keyval_store:set(username, data) |
| 138 user,store = username,self.store; | 138 user,store = username,self.store; |
| 139 -- start transaction | 139 -- start transaction |
| 140 local affected, err = setsql("DELETE FROM Prosody WHERE host IS ? AND user IS ? AND store IS ? AND subkey IS NULL"); | 140 local affected, err = setsql("DELETE FROM `Prosody` WHERE `host`=? AND `user`=? AND `store`=? AND `subkey`=''"); |
| 141 | 141 |
| 142 if data and next(data) ~= nil then | 142 if data and next(data) ~= nil then |
| 143 local extradata = {}; | 143 local extradata = {}; |
| 144 for key, value in pairs(data) do | 144 for key, value in pairs(data) do |
| 145 if type(key) == "string" then | 145 if type(key) == "string" and key ~= "" then |
| 146 local t, value = serialize(value); | 146 local t, value = serialize(value); |
| 147 if not t then return rollback(t, value); end | 147 if not t then return rollback(t, value); end |
| 148 local ok, err = setsql("INSERT INTO Prosody (host,user,store,key,type,value) VALUES (?,?,?,?,?,?)", key, t, value); | 148 local ok, err = setsql("INSERT INTO `Prosody` (`host`,`user`,`store`,`key`,`type`,`value`,`subkey`) VALUES (?,?,?,?,?,?,'')", key or "", t, value); |
| 149 if not ok then return rollback(ok, err); end | 149 if not ok then return rollback(ok, err); end |
| 150 else | 150 else |
| 151 extradata[key] = value; | 151 extradata[key] = value; |
| 152 end | 152 end |
| 153 end | 153 end |
| 154 if next(extradata) ~= nil then | 154 if next(extradata) ~= nil then |
| 155 local t, extradata = serialize(extradata); | 155 local t, extradata = serialize(extradata); |
| 156 if not t then return rollback(t, extradata); end | 156 if not t then return rollback(t, extradata); end |
| 157 local ok, err = setsql("INSERT INTO Prosody (host,user,store,key,type,value) VALUES (?,?,?,?,?,?)", nil, t, extradata); | 157 local ok, err = setsql("INSERT INTO `Prosody` (`host`,`user`,`store`,`key`,`type`,`value`,`subkey`) VALUES (?,?,?,?,?,?,'')", "", t, extradata); |
| 158 if not ok then return rollback(ok, err); end | 158 if not ok then return rollback(ok, err); end |
| 159 end | 159 end |
| 160 end | 160 end |
| 161 return commit(true); | 161 return commit(true); |
| 162 end | 162 end |
| 163 | 163 |
| 164 local map_store = {}; | 164 local map_store = {}; |
| 165 map_store.__index = map_store; | 165 map_store.__index = map_store; |
| 166 function map_store:get(username, key) | 166 function map_store:get(username, key) |
| 167 user,store = username,self.store; | 167 user,store = username,self.store; |
| 168 local stmt, err = getsql("SELECT * FROM Prosody WHERE host IS ? AND user IS ? AND store IS ? AND key IS ?", key); | 168 local stmt, err = getsql("SELECT * FROM `Prosody` WHERE `host`=? AND `user`=? AND `store`=? AND `key`=?", key or ""); |
| 169 if not stmt then return nil, err; end | 169 if not stmt then return nil, err; end |
| 170 | 170 |
| 171 local haveany; | 171 local haveany; |
| 172 local result = {}; | 172 local result = {}; |
| 173 for row in stmt:rows(true) do | 173 for row in stmt:rows(true) do |
| 185 return haveany and result or nil; | 185 return haveany and result or nil; |
| 186 end | 186 end |
| 187 function map_store:set(username, key, data) | 187 function map_store:set(username, key, data) |
| 188 user,store = username,self.store; | 188 user,store = username,self.store; |
| 189 -- start transaction | 189 -- start transaction |
| 190 local affected, err = setsql("DELETE FROM Prosody WHERE host IS ? AND user IS ? AND store IS ? AND key IS ?", key); | 190 local affected, err = setsql("DELETE FROM `Prosody` WHERE `host`=? AND `user`=? AND `store`=? AND `key`=?", key or ""); |
| 191 | 191 |
| 192 if data and next(data) ~= nil then | 192 if data and next(data) ~= nil then |
| 193 local extradata = {}; | 193 local extradata = {}; |
| 194 for subkey, value in pairs(data) do | 194 for subkey, value in pairs(data) do |
| 195 if type(subkey) == "string" then | 195 if type(subkey) == "string" and key ~= "" then |
| 196 local t, value = serialize(value); | 196 local t, value = serialize(value); |
| 197 if not t then return rollback(t, value); end | 197 if not t then return rollback(t, value); end |
| 198 local ok, err = setsql("INSERT INTO Prosody (host,user,store,key,subkey,type,value) VALUES (?,?,?,?,?,?)", key, subkey, t, value); | 198 local ok, err = setsql("INSERT INTO `Prosody` (`host`,`user`,`store`,`key`,`subkey`,`type`,`value`) VALUES (?,?,?,?,?,?,?)", key or "", subkey or "", t, value); |
| 199 if not ok then return rollback(ok, err); end | 199 if not ok then return rollback(ok, err); end |
| 200 else | 200 else |
| 201 extradata[subkey] = value; | 201 extradata[subkey] = value; |
| 202 end | 202 end |
| 203 end | 203 end |
| 204 if next(extradata) ~= nil then | 204 if next(extradata) ~= nil then |
| 205 local t, extradata = serialize(extradata); | 205 local t, extradata = serialize(extradata); |
| 206 if not t then return rollback(t, extradata); end | 206 if not t then return rollback(t, extradata); end |
| 207 local ok, err = setsql("INSERT INTO Prosody (host,user,store,key,subkey,type,value) VALUES (?,?,?,?,?,?)", key, nil, t, extradata); | 207 local ok, err = setsql("INSERT INTO `Prosody` (`host`,`user`,`store`,`key`,`subkey`,`type`,`value`) VALUES (?,?,?,?,?,?,?)", key or "", "", t, extradata); |
| 208 if not ok then return rollback(ok, err); end | 208 if not ok then return rollback(ok, err); end |
| 209 end | 209 end |
| 210 end | 210 end |
| 211 return commit(true); | 211 return commit(true); |
| 212 end | 212 end |
| 217 user,store = username,self.store; | 217 user,store = username,self.store; |
| 218 | 218 |
| 219 local cols = {"from", "to", "jid", "typ"}; | 219 local cols = {"from", "to", "jid", "typ"}; |
| 220 local vals = { from , to , jid , typ }; | 220 local vals = { from , to , jid , typ }; |
| 221 local stmt, err; | 221 local stmt, err; |
| 222 local query = "SELECT * FROM ProsodyArchive WHERE host IS ? AND user IS ? AND store IS ?"; | 222 local query = "SELECT * FROM `ProsodyArchive` WHERE `host`=? AND `user`=? AND `store`=?"; |
| 223 | 223 |
| 224 query = query.." ORDER BY time"; | 224 query = query.." ORDER BY time"; |
| 225 --local stmt, err = getsql("SELECT * FROM Prosody WHERE host IS ? AND user IS ? AND store IS ? AND key IS ?", key); | 225 --local stmt, err = getsql("SELECT * FROM `Prosody` WHERE `host`=? AND `user`=? AND `store`=? AND `key`=?", key or ""); |
| 226 | 226 |
| 227 return nil, "not-implemented" | 227 return nil, "not-implemented" |
| 228 end | 228 end |
| 229 | 229 |
| 230 local driver = { name = "sql" }; | 230 local driver = { name = "sql" }; |
