Mercurial > lxmppd-hg-xmllex / file comparison
comparison: plugins/mod_storage_sql.lua
plugins/mod_storage_sql.lua
- changeset 3977
- 6724853adb80
- parent 3976
- 16170a66e140
- child 3978
- 13ee740b1f89
equal
deleted
inserted
replaced
| 3976:16170a66e140 | 3977:6724853adb80 |
|---|---|
| 1 | 1 |
| 2 --[[ | 2 --[[ |
| 3 | 3 |
| 4 DB Tables: | 4 DB Tables: |
| 5 Prosody - key-value, map | 5 Prosody - key-value, map |
| 6 | host | user | store | key | subkey | type | value | | 6 | host | user | store | key | type | value | |
| 7 ProsodyArchive - list | 7 ProsodyArchive - list |
| 8 | host | user | store | key | time | stanzatype | jsonvalue | | 8 | host | user | store | key | time | stanzatype | jsonvalue | |
| 9 | 9 |
| 10 Mapping: | 10 Mapping: |
| 11 Roster - Prosody | 11 Roster - Prosody |
| 12 | host | user | "roster" | "contactjid" | item-subkey | type | value | | 12 | host | user | "roster" | "contactjid" | type | value | |
| 13 | host | user | "roster" | NULL | NULL | "json" | roster[false] data | | 13 | host | user | "roster" | NULL | "json" | roster[false] data | |
| 14 Account - Prosody | 14 Account - Prosody |
| 15 | host | user | "accounts" | "username" | NULL | type | value | | 15 | host | user | "accounts" | "username" | type | value | |
| 16 | 16 |
| 17 Offline - ProsodyArchive | 17 Offline - ProsodyArchive |
| 18 | host | user | "offline" | "contactjid" | time | "message" | json|XML | | 18 | host | user | "offline" | "contactjid" | time | "message" | json|XML | |
| 19 | 19 |
| 20 ]] | 20 ]] |
| 52 if params.driver == "SQLite3" then -- auto initialize | 52 if params.driver == "SQLite3" then -- auto initialize |
| 53 local stmt = assert(connection:prepare("SELECT COUNT(*) FROM `sqlite_master` WHERE `type`='table' AND `name`='Prosody';")); | 53 local stmt = assert(connection:prepare("SELECT COUNT(*) FROM `sqlite_master` WHERE `type`='table' AND `name`='Prosody';")); |
| 54 local ok = assert(stmt:execute()); | 54 local ok = assert(stmt:execute()); |
| 55 local count = stmt:fetch()[1]; | 55 local count = stmt:fetch()[1]; |
| 56 if count == 0 then | 56 if count == 0 then |
| 57 local stmt = assert(connection:prepare("CREATE TABLE `Prosody` (`host` TEXT, `user` TEXT, `store` TEXT, `key` TEXT, `subkey` TEXT, `type` TEXT, `value` TEXT);")); | 57 local stmt = assert(connection:prepare("CREATE TABLE `Prosody` (`host` TEXT, `user` TEXT, `store` TEXT, `key` TEXT, `type` TEXT, `value` TEXT);")); |
| 58 assert(stmt:execute()); | 58 assert(stmt:execute()); |
| 59 module:log("debug", "Initialized new SQLite3 database"); | 59 module:log("debug", "Initialized new SQLite3 database"); |
| 60 end | 60 end |
| 61 assert(connection:commit()); | 61 assert(connection:commit()); |
| 62 --print("===", json.stringify()) | 62 --print("===", json.stringify()) |
| 117 | 117 |
| 118 local keyval_store = {}; | 118 local keyval_store = {}; |
| 119 keyval_store.__index = keyval_store; | 119 keyval_store.__index = keyval_store; |
| 120 function keyval_store:get(username) | 120 function keyval_store:get(username) |
| 121 user,store = username,self.store; | 121 user,store = username,self.store; |
| 122 local stmt, err = getsql("SELECT * FROM `Prosody` WHERE `host`=? AND `user`=? AND `store`=? AND `subkey`=''"); | 122 local stmt, err = getsql("SELECT * FROM `Prosody` WHERE `host`=? AND `user`=? AND `store`=?"); |
| 123 if not stmt then return nil, err; end | 123 if not stmt then return nil, err; end |
| 124 | 124 |
| 125 local haveany; | 125 local haveany; |
| 126 local result = {}; | 126 local result = {}; |
| 127 for row in stmt:rows(true) do | 127 for row in stmt:rows(true) do |
| 128 haveany = true; | 128 haveany = true; |
| 129 local k = row.key; | 129 local k = row.key; |
| 130 local v = deserialize(row.type, row.value); | 130 local v = deserialize(row.type, row.value); |
| 131 if v then | 131 if k and v then |
| 132 if k then result[k] = v; elseif type(v) == "table" then | 132 if k ~= "" then result[k] = v; elseif type(v) == "table" then |
| 133 for a,b in pairs(v) do | 133 for a,b in pairs(v) do |
| 134 result[a] = b; | 134 result[a] = b; |
| 135 end | 135 end |
| 136 end | 136 end |
| 137 end | 137 end |
| 139 return commit(haveany and result or nil); | 139 return commit(haveany and result or nil); |
| 140 end | 140 end |
| 141 function keyval_store:set(username, data) | 141 function keyval_store:set(username, data) |
| 142 user,store = username,self.store; | 142 user,store = username,self.store; |
| 143 -- start transaction | 143 -- start transaction |
| 144 local affected, err = setsql("DELETE FROM `Prosody` WHERE `host`=? AND `user`=? AND `store`=? AND `subkey`=''"); | 144 local affected, err = setsql("DELETE FROM `Prosody` WHERE `host`=? AND `user`=? AND `store`=?"); |
| 145 | 145 |
| 146 if data and next(data) ~= nil then | 146 if data and next(data) ~= nil then |
| 147 local extradata = {}; | 147 local extradata = {}; |
| 148 for key, value in pairs(data) do | 148 for key, value in pairs(data) do |
| 149 if type(key) == "string" and key ~= "" then | 149 if type(key) == "string" and key ~= "" then |
| 150 local t, value = serialize(value); | 150 local t, value = serialize(value); |
| 151 if not t then return rollback(t, value); end | 151 if not t then return rollback(t, value); end |
| 152 local ok, err = setsql("INSERT INTO `Prosody` (`host`,`user`,`store`,`key`,`type`,`value`,`subkey`) VALUES (?,?,?,?,?,?,'')", key or "", t, value); | 152 local ok, err = setsql("INSERT INTO `Prosody` (`host`,`user`,`store`,`key`,`type`,`value`) VALUES (?,?,?,?,?,?)", key, t, value); |
| 153 if not ok then return rollback(ok, err); end | 153 if not ok then return rollback(ok, err); end |
| 154 else | 154 else |
| 155 extradata[key] = value; | 155 extradata[key] = value; |
| 156 end | 156 end |
| 157 end | 157 end |
| 158 if next(extradata) ~= nil then | 158 if next(extradata) ~= nil then |
| 159 local t, extradata = serialize(extradata); | 159 local t, extradata = serialize(extradata); |
| 160 if not t then return rollback(t, extradata); end | 160 if not t then return rollback(t, extradata); end |
| 161 local ok, err = setsql("INSERT INTO `Prosody` (`host`,`user`,`store`,`key`,`type`,`value`,`subkey`) VALUES (?,?,?,?,?,?,'')", "", t, extradata); | 161 local ok, err = setsql("INSERT INTO `Prosody` (`host`,`user`,`store`,`key`,`type`,`value`) VALUES (?,?,?,?,?,?)", "", t, extradata); |
| 162 if not ok then return rollback(ok, err); end | 162 if not ok then return rollback(ok, err); end |
| 163 end | 163 end |
| 164 end | 164 end |
| 165 return commit(true); | 165 return commit(true); |
| 166 end | 166 end |
| 174 | 174 |
| 175 local haveany; | 175 local haveany; |
| 176 local result = {}; | 176 local result = {}; |
| 177 for row in stmt:rows(true) do | 177 for row in stmt:rows(true) do |
| 178 haveany = true; | 178 haveany = true; |
| 179 local k = row.subkey; | 179 local k = row.key; |
| 180 local v = deserialize(row.type, row.value); | 180 local v = deserialize(row.type, row.value); |
| 181 if v then | 181 if k and v then |
| 182 if k then result[k] = v; elseif type(v) == "table" then | 182 if k ~= "" then result[k] = v; elseif type(v) == "table" then |
| 183 for a,b in pairs(v) do | 183 for a,b in pairs(v) do |
| 184 result[a] = b; | 184 result[a] = b; |
| 185 end | 185 end |
| 186 end | 186 end |
| 187 end | 187 end |
| 188 end | 188 end |
| 189 return commit(haveany and result or nil); | 189 return commit(haveany and result[key] or nil); |
| 190 end | 190 end |
| 191 function map_store:set(username, key, data) | 191 function map_store:set(username, key, data) |
| 192 user,store = username,self.store; | 192 user,store = username,self.store; |
| 193 -- start transaction | 193 -- start transaction |
| 194 local affected, err = setsql("DELETE FROM `Prosody` WHERE `host`=? AND `user`=? AND `store`=? AND `key`=?", key or ""); | 194 local affected, err = setsql("DELETE FROM `Prosody` WHERE `host`=? AND `user`=? AND `store`=? AND `key`=?", key or ""); |
| 195 | 195 |
| 196 if data and next(data) ~= nil then | 196 if data and next(data) ~= nil then |
| 197 local extradata = {}; | 197 if type(key) == "string" and key ~= "" then |
| 198 for subkey, value in pairs(data) do | 198 local t, value = serialize(data); |
| 199 if type(subkey) == "string" and key ~= "" then | 199 if not t then return rollback(t, value); end |
| 200 local t, value = serialize(value); | 200 local ok, err = setsql("INSERT INTO `Prosody` (`host`,`user`,`store`,`key`,`type`,`value`) VALUES (?,?,?,?,?,?)", key, t, value); |
| 201 if not t then return rollback(t, value); end | |
| 202 local ok, err = setsql("INSERT INTO `Prosody` (`host`,`user`,`store`,`key`,`subkey`,`type`,`value`) VALUES (?,?,?,?,?,?,?)", key or "", subkey or "", t, value); | |
| 203 if not ok then return rollback(ok, err); end | |
| 204 else | |
| 205 extradata[subkey] = value; | |
| 206 end | |
| 207 end | |
| 208 if next(extradata) ~= nil then | |
| 209 local t, extradata = serialize(extradata); | |
| 210 if not t then return rollback(t, extradata); end | |
| 211 local ok, err = setsql("INSERT INTO `Prosody` (`host`,`user`,`store`,`key`,`subkey`,`type`,`value`) VALUES (?,?,?,?,?,?,?)", key or "", "", t, extradata); | |
| 212 if not ok then return rollback(ok, err); end | 201 if not ok then return rollback(ok, err); end |
| 202 else | |
| 203 -- TODO non-string keys | |
| 213 end | 204 end |
| 214 end | 205 end |
| 215 return commit(true); | 206 return commit(true); |
| 216 end | 207 end |
| 217 | 208 |
