Mercurial > libervia-backend

comparison src/memory/sqlite.py @ 512:862c0d6ab974

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
core, bridge, quick_frontend: MUC private messages history management: - history now store message type - sqlite3 storage: fixed resource management in getHistory - bridge: added message type in getHistory return value - quick_chats: (ugly) hacks to manage private history filtering/nickname printing
author Goffi <goffi@goffi.org>
date Wed, 17 Oct 2012 00:35:48 +0200
parents 2a072735e459
children 2c4016921403
comparison
equal deleted inserted replaced
511:62f7f2403093 512:862c0d6ab974
44 init_defers.append(self.dbpool.runOperation("PRAGMA foreign_keys = ON").addErrback(lambda x: error(_("Can't activate foreign keys")))) 44 init_defers.append(self.dbpool.runOperation("PRAGMA foreign_keys = ON").addErrback(lambda x: error(_("Can't activate foreign keys"))))
45 if new_base: 45 if new_base:
46 info(_("The database is new, creating the tables")) 46 info(_("The database is new, creating the tables"))
47 database_creation = [ 47 database_creation = [
48 "CREATE TABLE profiles (id INTEGER PRIMARY KEY ASC, name TEXT, UNIQUE (name))", 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))", 49 "CREATE TABLE message_types (type TEXT PRIMARY KEY)",
50 "INSERT INTO message_types VALUES ('chat')",
51 "INSERT INTO message_types VALUES ('error')",
52 "INSERT INTO message_types VALUES ('groupchat')",
53 "INSERT INTO message_types VALUES ('headline')",
54 "INSERT INTO message_types VALUES ('normal')",
55 "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, type TEXT, FOREIGN KEY(profile_id) REFERENCES profiles(id), FOREIGN KEY(type) REFERENCES message_types(type))",
50 "CREATE TABLE param_gen (category TEXT, name TEXT, value TEXT, PRIMARY KEY (category,name))", 56 "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))", 57 "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))", 58 "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))", 59 "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))", 60 "CREATE TABLE private_gen_bin (namespace TEXT, key TEXT, value BLOB, PRIMARY KEY (namespace, key))",
167 d = self.dbpool.runQuery("REPLACE INTO param_ind(category,name,profile_id,value) VALUES (?,?,?,?)", (category, name, self.profiles[profile], value)) 173 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}))) 174 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 175 return d
170 176
171 #History 177 #History
172 def addToHistory(self, from_jid, to_jid, message, timestamp=None, profile=None): 178 def addToHistory(self, from_jid, to_jid, message, _type='chat', timestamp=None, profile=None):
173 """Store a new message in history 179 """Store a new message in history
174 @param from_jid: full source JID 180 @param from_jid: full source JID
175 @param to_jid: full dest JID 181 @param to_jid: full dest JID
176 @param message: message 182 @param message: message
183 @param _type: message type (see RFC 6121 ยง5.2.2)
177 @param timestamp: timestamp in seconds since epoch, or None to use current time 184 @param timestamp: timestamp in seconds since epoch, or None to use current time
178 """ 185 """
179 assert(profile!=None) 186 assert(profile!=None)
180 d = self.dbpool.runQuery("INSERT INTO history(source, source_res, dest, dest_res, timestamp, message, profile_id) VALUES (?,?,?,?,?,?,?)", 187 d = self.dbpool.runQuery("INSERT INTO history(source, source_res, dest, dest_res, timestamp, message, type, profile_id) VALUES (?,?,?,?,?,?,?,?)",
181 (from_jid.userhost(), from_jid.resource, to_jid.userhost(), to_jid.resource, timestamp or time.time(), 188 (from_jid.userhost(), from_jid.resource, to_jid.userhost(), to_jid.resource, timestamp or time.time(),
182 message, self.profiles[profile])) 189 message, _type, 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]" % 190 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}))) 191 {"from_jid":from_jid.full(), "to_jid":to_jid.full(), "message":message})))
185 return d 192 return d
186 193
187 def getHistory(self, from_jid, to_jid, limit=0, between=True): 194 def getHistory(self, from_jid, to_jid, limit=0, between=True):
192 """ 199 """
193 def sqliteToDict(query_result): 200 def sqliteToDict(query_result):
194 query_result.reverse() 201 query_result.reverse()
195 result = [] 202 result = []
196 for row in query_result: 203 for row in query_result:
197 timestamp, source, source_res, dest, dest_res, message = row 204 timestamp, source, source_res, dest, dest_res, message, _type= row
198 result.append((timestamp, "%s/%s" % (source, source_res) if source_res else source, 205 result.append((timestamp, "%s/%s" % (source, source_res) if source_res else source,
199 "%s/%s" % (dest, dest_res) if dest_res else dest, 206 "%s/%s" % (dest, dest_res) if dest_res else dest,
200 message)) 207 message, _type))
201 return result 208 return result
209
210
211 query_parts = ["SELECT timestamp, source, source_res, dest, dest_res, message, type FROM history WHERE"]
212 values = []
213
214 def test_jid(_type,_jid):
215 values.append(_jid.userhost())
216 if _jid.resource:
217 values.append(_jid.resource)
218 return '(%s=? AND %s_res=?)' % (_type, _type)
219 return '%s=?' % (_type,)
202 220
203 query_parts = ["SELECT timestamp, source, source_res, dest, dest_res, message FROM history WHERE"]
204 values = []
205
206 if between: 221 if between:
207 query_parts.append("(source=? OR source=?) AND (dest=? or dest=?)") 222 query_parts.append("(%s OR %s) AND (%s or %s)" % (test_jid('source', from_jid),
208 values.extend([from_jid.userhost(), to_jid.userhost(), to_jid.userhost(), from_jid.userhost()]) 223 test_jid('source', to_jid),
224 test_jid('dest', to_jid),
225 test_jid('dest', from_jid)))
209 else: 226 else:
210 query_parts.append("source=? AND dest=?") 227 query_parts.append("%s AND %s") % (test_jid('source', from_jid),
211 values.extend([from_jid.userhost(), to_jid.userhost()]) 228 test_jid('dest', to_jid))
212 if from_jid.resource:
213 query_parts.append("AND source_res=?")
214 values.append(from_jid.resource)
215 if to_jid.resource:
216 query_parts.append("AND dest_res=?")
217 values.append(to_jid.resource)
218 229
219 query_parts.append("ORDER BY timestamp DESC") 230 query_parts.append("ORDER BY timestamp DESC")
220 231
221 if limit: 232 if limit:
222 query_parts.append("LIMIT ?") 233 query_parts.append("LIMIT ?")
223 values.append(limit) 234 values.append(limit)
235
224 d = self.dbpool.runQuery(" ".join(query_parts), values) 236 d = self.dbpool.runQuery(" ".join(query_parts), values)
225 return d.addCallback(sqliteToDict) 237 return d.addCallback(sqliteToDict)
226 238
227 #Private values 239 #Private values
228 def loadGenPrivates(self, private_gen, namespace): 240 def loadGenPrivates(self, private_gen, namespace):