Mercurial > libervia-backend

comparison src/memory/sqlite.py @ 432:31e8c48b5f5d

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
core: - memory refactoring (moved memory.py and sqlite.py from tools to memory) - private values are now stored in storage (currently sqlite database) and splitted in normal/binary storage. Normal storage is for string key/values, binary storage is for string key and any python data for value - PersistentDict and PersistentBinaryDict are sugar classes to manage the data in storage thought a python dictionary like class.
author Goffi <goffi@goffi.org>
date Sun, 20 Nov 2011 15:34:37 +0100
parents src/tools/sqlite.py@e4e9187e3b5b
children 5e9d28ca5109
comparison
equal deleted inserted replaced
431:482b9bcf0ca4 432:31e8c48b5f5d
1 #!/usr/bin/python
2 # -*- coding: utf-8 -*-
3
4 """
5 SAT: a jabber client
6 Copyright (C) 2009, 2010, 2011 Jérôme Poisson (goffi@goffi.org)
7
8 This program is free software: you can redistribute it and/or modify
9 it under the terms of the GNU General Public License as published by
10 the Free Software Foundation, either version 3 of the License, or
11 (at your option) any later version.
12
13 This program is distributed in the hope that it will be useful,
14 but WITHOUT ANY WARRANTY; without even the implied warranty of
15 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
16 GNU General Public License for more details.
17
18 You should have received a copy of the GNU General Public License
19 along with this program. If not, see <http://www.gnu.org/licenses/>.
20 """
21
22
23 from logging import debug, info, warning, error
24 from twisted.enterprise import adbapi
25 from twisted.internet import defer
26 import os.path
27 import time
28 import cPickle as pickle
29
30 class SqliteStorage():
31 """This class manage storage with Sqlite database"""
32
33
34 def __init__(self, db_filename):
35 """Connect to the given database
36 @param db_filename: full path to the Sqlite database"""
37 self.initialized = defer.Deferred() #triggered when memory is fully initialised and ready
38 init_defers = [] #list of deferred we have to wait to before initialisation is complete
39 self.profiles={} #we keep cache for the profiles (key: profile name, value: profile id)
40
41 info(_("Connecting database"))
42 new_base = not os.path.exists(db_filename) #do we have to create the database ?
43 self.dbpool = adbapi.ConnectionPool("sqlite3", db_filename, check_same_thread=False)
44 init_defers.append(self.dbpool.runOperation("PRAGMA foreign_keys = ON").addErrback(lambda x: error(_("Can't activate foreign keys"))))
45 if new_base:
46 info(_("The database is new, creating the tables"))
47 database_creation = [
48 "CREATE TABLE profiles (id INTEGER PRIMARY KEY ASC, name TEXT, UNIQUE (name))",
49 "CREATE TABLE history (id INTEGER PRIMARY KEY ASC, profile_id INTEGER, source TEXT, dest TEXT, source_res TEXT, dest_res TEXT, timestamp DATETIME, message TEXT, FOREIGN KEY(profile_id) REFERENCES profiles(id))",
50 "CREATE TABLE param_gen (category TEXT, name TEXT, value TEXT, PRIMARY KEY (category,name))",
51 "CREATE TABLE param_ind (category TEXT, name TEXT, profile_id INTEGER, value TEXT, PRIMARY KEY (category,name,profile_id), FOREIGN KEY(profile_id) REFERENCES profiles(id))",
52 "CREATE TABLE private_gen (namespace TEXT, key TEXT, value TEXT, PRIMARY KEY (namespace, key))",
53 "CREATE TABLE private_ind (namespace TEXT, key TEXT, profile_id INTEGER, value TEXT, PRIMARY KEY (namespace, key, profile_id), FOREIGN KEY(profile_id) REFERENCES profiles(id))",
54 "CREATE TABLE private_gen_bin (namespace TEXT, key TEXT, value BLOB, PRIMARY KEY (namespace, key))",
55 "CREATE TABLE private_ind_bin (namespace TEXT, key TEXT, profile_id INTEGER, value BLOB, PRIMARY KEY (namespace, key, profile_id), FOREIGN KEY(profile_id) REFERENCES profiles(id))",
56 ]
57 for op in database_creation:
58 d = self.dbpool.runOperation(op)
59 d.addErrback(lambda x: error(_("Error while creating tables in database [QUERY: %s]") % op ))
60 init_defers.append(d)
61
62 def fillProfileCache(ignore):
63 d = self.dbpool.runQuery("SELECT name,id FROM profiles").addCallback(self._profilesCache)
64 d.chainDeferred(self.initialized)
65
66 defer.DeferredList(init_defers).addCallback(fillProfileCache)
67
68 #Profiles
69 def _profilesCache(self, profiles_result):
70 """Fill the profiles cache
71 @param profiles_result: result of the sql profiles query"""
72 for profile in profiles_result:
73 name, id = profile
74 self.profiles[name] = id
75
76 def getProfilesList(self):
77 """"Return list of all registered profiles"""
78 return self.profiles.keys()
79
80 def hasProfile(self, profile_name):
81 """return True if profile_name exists
82 @param profile_name: name of the profile to check"""
83 return self.profiles.has_key(profile_name)
84
85 def createProfile(self, name):
86 """Create a new profile
87 @param name: name of the profile
88 @return: deferred triggered once profile is actually created"""
89 def getProfileId(ignore):
90 return self.dbpool.runQuery("SELECT (id) FROM profiles WHERE name = ?", (name,))
91
92 def profile_created(profile_id):
93 _id = profile_id[0][0]
94 self.profiles[name] = _id #we synchronise the cache
95
96 d = self.dbpool.runQuery("INSERT INTO profiles(name) VALUES (?)", (name,))
97 d.addCallback(getProfileId)
98 d.addCallback(profile_created)
99 return d
100
101 def deleteProfile(self, name):
102 """Delete profile
103 @param name: name of the profile
104 @return: deferred triggered once profile is actually deleted"""
105 def deletionError(failure):
106 error(_("Can't delete profile [%s]") % name)
107 return failure
108 del self.profiles[name]
109 d = self.dbpool.runQuery("DELETE FROM profiles WHERE name = ?", (name,))
110 d.addCallback(lambda ignore: info(_("Profile [%s] deleted") % name))
111 return d
112
113
114 #Params
115 def loadGenParams(self, params_gen):
116 """Load general parameters
117 @param params_gen: dictionary to fill
118 @return: deferred"""
119 def fillParams(result):
120 for param in result:
121 category,name,value = param
122 params_gen[(category, name)] = value
123 debug(_("loading general parameters from database"))
124 return self.dbpool.runQuery("SELECT category,name,value FROM param_gen").addCallback(fillParams)
125
126 def loadIndParams(self, params_ind, profile):
127 """Load individual parameters
128 @param params_ind: dictionary to fill
129 @param profile: a profile which *must* exist
130 @return: deferred"""
131 def fillParams(result):
132 for param in result:
133 category,name,value = param
134 params_ind[(category, name)] = value
135 debug(_("loading individual parameters from database"))
136 d = self.dbpool.runQuery("SELECT category,name,value FROM param_ind WHERE profile_id=?", (self.profiles[profile],))
137 d.addCallback(fillParams)
138 return d
139
140 def getIndParam(self, category, name, profile):
141 """Ask database for the value of one specific individual parameter
142 @param category: category of the parameter
143 @param name: name of the parameter
144 @param profile: %(doc_profile)s
145 @return: deferred"""
146 d = self.dbpool.runQuery("SELECT value FROM param_ind WHERE category=? AND name=? AND profile_id=?", (category,name,self.profiles[profile]))
147 d.addCallback(self.__getFirstResult)
148 return d
149
150 def setGenParam(self, category, name, value):
151 """Save the general parameters in database
152 @param category: category of the parameter
153 @param name: name of the parameter
154 @param value: value to set
155 @return: deferred"""
156 d = self.dbpool.runQuery("REPLACE INTO param_gen(category,name,value) VALUES (?,?,?)", (category, name, value))
157 d.addErrback(lambda ignore: error(_("Can't set general parameter (%(category)s/%(name)s) in database" % {"category":category, "name":name})))
158 return d
159
160 def setIndParam(self, category, name, value, profile):
161 """Save the individual parameters in database
162 @param category: category of the parameter
163 @param name: name of the parameter
164 @param value: value to set
165 @param profile: a profile which *must* exist
166 @return: deferred"""
167 d = self.dbpool.runQuery("REPLACE INTO param_ind(category,name,profile_id,value) VALUES (?,?,?,?)", (category, name, self.profiles[profile], value))
168 d.addErrback(lambda ignore: error(_("Can't set individual parameter (%(category)s/%(name)s) for [%(profile)s] in database" % {"category":category, "name":name, "profile":profile})))
169 return d
170
171 #History
172 def addToHistory(self, from_jid, to_jid, message, timestamp=None, profile=None):
173 """Store a new message in history
174 @param from_jid: full source JID
175 @param to_jid: full dest JID
176 @param message: message
177 @param timestamp: timestamp in seconds since epoch, or None to use current time
178 """
179 assert(profile!=None)
180 d = self.dbpool.runQuery("INSERT INTO history(source, source_res, dest, dest_res, timestamp, message, profile_id) VALUES (?,?,?,?,?,?,?)",
181 (from_jid.userhost(), from_jid.resource, to_jid.userhost(), to_jid.resource, timestamp or int(time.time()),
182 message, self.profiles[profile]))
183 d.addErrback(lambda ignore: error(_("Can't save following message in history: from [%(from_jid)s] to [%(to_jid)s] ==> [%(message)s]" %
184 {"from_jid":from_jid.full(), "to_jid":to_jid.full(), "message":message})))
185 return d
186
187 def getHistory(self, from_jid, to_jid, limit=0, between=True):
188 """Store a new message in history
189 @param from_jid: source JID (full, or bare for catchall
190 @param to_jid: dest JID (full, or bare for catchall
191 @param size: maximum number of messages to get, or 0 for unlimited
192 """
193 def sqliteToDict(result):
194 result_dict = {}
195 for row in result:
196 timestamp, source, source_res, dest, dest_res, message = row
197 result_dict[timestamp] = ("%s/%s" % (source, source_res) if source_res else source,
198 "%s/%s" % (dest, dest_res) if dest_res else dest,
199 message)
200 return result_dict
201
202 query_parts = ["SELECT timestamp, source, source_res, dest, dest_res, message FROM history WHERE"]
203 values = []
204
205 if between:
206 query_parts.append("(source=? OR source=?) AND (dest=? or dest=?)")
207 values.extend([from_jid.userhost(), to_jid.userhost(), to_jid.userhost(), from_jid.userhost()])
208 else:
209 query_parts.append("source=? AND dest=?")
210 values.extend([from_jid.userhost(), to_jid.userhost()])
211 if from_jid.resource:
212 query_parts.append("AND source_res=?")
213 values.append(from_jid.resource)
214 if to_jid.resource:
215 query_parts.append("AND dest_res=?")
216 values.append(to_jid.resource)
217 if limit:
218 query_parts.append("LIMIT ?")
219 values.append(limit)
220 d = self.dbpool.runQuery(" ".join(query_parts), values)
221 return d.addCallback(sqliteToDict)
222
223 #Private values
224 def loadGenPrivates(self, private_gen, namespace):
225 """Load general private values
226 @param private_gen: dictionary to fill
227 @param namespace: namespace of the values
228 @return: deferred"""
229 def fillPrivates(result):
230 for private in result:
231 key,value = private
232 private_gen[key] = value
233 debug(_("loading general private values [namespace: %s] from database") % (namespace,))
234 d = self.dbpool.runQuery("SELECT key,value FROM private_gen WHERE namespace=?", namespace).addCallback(fillPrivates)
235 return d.addErrback(lambda x: debug(_("No data present in database for namespace %s") % namespace))
236
237 def loadIndPrivates(self, private_ind, namespace, profile):
238 """Load individual private values
239 @param privates_ind: dictionary to fill
240 @param namespace: namespace of the values
241 @param profile: a profile which *must* exist
242 @return: deferred"""
243 def fillPrivates(result):
244 for private in result:
245 key,value = private
246 private_ind[key] = value
247 debug(_("loading individual private values [namespace: %s] from database") % (namespace,))
248 d = self.dbpool.runQuery("SELECT key,value FROM private_ind WHERE namespace=? AND profile_id=?", (namespace, self.profiles[profile]))
249 d.addCallback(fillPrivates)
250 return d.addErrback(lambda x: debug(_("No data present in database for namespace %s") % namespace))
251
252 def setGenPrivate(self, namespace, key, value):
253 """Save the general private value in database
254 @param category: category of the privateeter
255 @param key: key of the private value
256 @param value: value to set
257 @return: deferred"""
258 d = self.dbpool.runQuery("REPLACE INTO private_gen(namespace,key,value) VALUES (?,?,?)", (namespace,key,value))
259 d.addErrback(lambda ignore: error(_("Can't set general private value (%(key)s) [namespace:%(namespace)s] in database" %
260 {"namespace":namespace, "key":key})))
261 return d
262
263 def setIndPrivate(self, namespace, key, value, profile):
264 """Save the individual private value in database
265 @param namespace: namespace of the value
266 @param key: key of the private value
267 @param value: value to set
268 @param profile: a profile which *must* exist
269 @return: deferred"""
270 d = self.dbpool.runQuery("REPLACE INTO private_ind(namespace,key,profile_id,value) VALUES (?,?,?,?)", (namespace, key, self.profiles[profile], value))
271 d.addErrback(lambda ignore: error(_("Can't set individual private value (%(key)s) [namespace: %(namespace)s] for [%(profile)s] in database" %
272 {"namespace":namespace, "key":key, "profile":profile})))
273 return d
274
275 def delGenPrivate(self, namespace, key):
276 """Delete the general private value from database
277 @param category: category of the privateeter
278 @param key: key of the private value
279 @return: deferred"""
280 d = self.dbpool.runQuery("DELETE FROM private_gen WHERE namespace=? AND key=?", (namespace,key))
281 d.addErrback(lambda ignore: error(_("Can't delete general private value (%(key)s) [namespace:%(namespace)s] in database" %
282 {"namespace":namespace, "key":key})))
283 return d
284
285 def delIndPrivate(self, namespace, key, profile):
286 """Delete the individual private value from database
287 @param namespace: namespace of the value
288 @param key: key of the private value
289 @param profile: a profile which *must* exist
290 @return: deferred"""
291 d = self.dbpool.runQuery("DELETE FROM private_ind WHERE namespace=? AND key=? AND profile=?)", (namespace, key, self.profiles[profile]))
292 d.addErrback(lambda ignore: error(_("Can't delete individual private value (%(key)s) [namespace: %(namespace)s] for [%(profile)s] in database" %
293 {"namespace":namespace, "key":key, "profile":profile})))
294 return d
295
296
297 def loadGenPrivatesBinary(self, private_gen, namespace):
298 """Load general private binary values
299 @param private_gen: dictionary to fill
300 @param namespace: namespace of the values
301 @return: deferred"""
302 def fillPrivates(result):
303 for private in result:
304 key,value = private
305 private_gen[key] = pickle.loads(value,0)
306 debug(_("loading general private binary values [namespace: %s] from database") % (namespace,))
307 d = self.dbpool.runQuery("SELECT key,value FROM private_gen_bin WHERE namespace=?", namespace).addCallback(fillPrivates)
308 return d.addErrback(lambda x: debug(_("No binary data present in database for namespace %s") % namespace))
309
310 def loadIndPrivatesBinary(self, private_ind, namespace, profile):
311 """Load individual private binary values
312 @param privates_ind: dictionary to fill
313 @param namespace: namespace of the values
314 @param profile: a profile which *must* exist
315 @return: deferred"""
316 def fillPrivates(result):
317 for private in result:
318 key,value = private
319 private_ind[key] = pickle.loads(value,0)
320 debug(_("loading individual private binary values [namespace: %s] from database") % (namespace,))
321 d = self.dbpool.runQuery("SELECT key,value FROM private_ind_bin WHERE namespace=? AND profile_id=?", (namespace, self.profiles[profile]))
322 d.addCallback(fillPrivates)
323 return d.addErrback(lambda x: debug(_("No binary data present in database for namespace %s") % namespace))
324
325 def setGenPrivateBinary(self, namespace, key, value):
326 """Save the general private binary value in database
327 @param category: category of the privateeter
328 @param key: key of the private value
329 @param value: value to set
330 @return: deferred"""
331 d = self.dbpool.runQuery("REPLACE INTO private_gen_bin(namespace,key,value) VALUES (?,?,?)", (namespace,key,pickle.dumps(value,0)))
332 d.addErrback(lambda ignore: error(_("Can't set general private binary value (%(key)s) [namespace:%(namespace)s] in database" %
333 {"namespace":namespace, "key":key})))
334 return d
335
336 def setIndPrivateBinary(self, namespace, key, value, profile):
337 """Save the individual private binary value in database
338 @param namespace: namespace of the value
339 @param key: key of the private value
340 @param value: value to set
341 @param profile: a profile which *must* exist
342 @return: deferred"""
343 d = self.dbpool.runQuery("REPLACE INTO private_ind_bin(namespace,key,profile_id,value) VALUES (?,?,?,?)", (namespace, key, self.profiles[profile], pickle.dumps(value,0)))
344 d.addErrback(lambda ignore: error(_("Can't set individual binary private value (%(key)s) [namespace: %(namespace)s] for [%(profile)s] in database" %
345 {"namespace":namespace, "key":key, "profile":profile})))
346 return d
347
348 def delGenPrivateBinary(self, namespace, key):
349 """Delete the general private binary value from database
350 @param category: category of the privateeter
351 @param key: key of the private value
352 @return: deferred"""
353 d = self.dbpool.runQuery("DELETE FROM private_gen_bin WHERE namespace=? AND key=?", (namespace,key))
354 d.addErrback(lambda ignore: error(_("Can't delete general private binary value (%(key)s) [namespace:%(namespace)s] in database" %
355 {"namespace":namespace, "key":key})))
356 return d
357
358 def delIndPrivateBinary(self, namespace, key, profile):
359 """Delete the individual private binary value from database
360 @param namespace: namespace of the value
361 @param key: key of the private value
362 @param profile: a profile which *must* exist
363 @return: deferred"""
364 d = self.dbpool.runQuery("DELETE FROM private_ind_bin WHERE namespace=? AND key=? AND profile=?)", (namespace, key, self.profiles[profile]))
365 d.addErrback(lambda ignore: error(_("Can't delete individual private binary value (%(key)s) [namespace: %(namespace)s] for [%(profile)s] in database" %
366 {"namespace":namespace, "key":key, "profile":profile})))
367 return d
368 ##Helper methods##
369
370 def __getFirstResult(self, result):
371 """Return the first result of a database query
372 Useful when we are looking for one specific value"""
373 return None if not result else result[0][0]