Software /
code /
prosody
Comparison
plugins/mod_storage_sql.lua @ 3977:6724853adb80
mod_storage_sql: Remove the subkey column from the Prosody table, and make the map store compatible with the key-value store.
author | Waqas Hussain <waqas20@gmail.com> |
---|---|
date | Mon, 27 Dec 2010 06:10:35 +0500 |
parent | 3976:16170a66e140 |
child | 3978:13ee740b1f89 |
comparison
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 |