libervia-backend: src/memory/sqlite.py comparison

comparison src/memory/sqlite.py @ 669:ffb716804580

core, bridge: extra parameter is saved in history: - added extra data in getHistory return value - extra data is saved in database /!\ WARNING: DATABASE SCHEMA HAS CHANGED /!\ to update sqlite database: ALTER TABLE history ADD COLUMN extra BLOB;

author	Goffi <goffi@goffi.org>
date	Thu, 07 Nov 2013 15:29:39 +0100
parents	70988f08d0ad
children	66633443bcd7

comparison

equal deleted inserted replaced

-:7bb50096d225
+:ffb716804580
 "INSERT INTO message_types VALUES ('chat')",
 "INSERT INTO message_types VALUES ('error')",
 "INSERT INTO message_types VALUES ('groupchat')",
 "INSERT INTO message_types VALUES ('headline')",
 "INSERT INTO message_types VALUES ('normal')",
-"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) ON DELETE CASCADE, FOREIGN KEY(type) REFERENCES message_types(type))",
+"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, extra BLOB, FOREIGN KEY(profile_id) REFERENCES profiles(id) ON DELETE CASCADE, FOREIGN KEY(type) REFERENCES message_types(type))",
 "CREATE TABLE param_gen (category TEXT, name TEXT, value TEXT, PRIMARY KEY (category,name))",
 "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) ON DELETE CASCADE)",
 "CREATE TABLE private_gen (namespace TEXT, key TEXT, value TEXT, PRIMARY KEY (namespace, key))",
 "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) ON DELETE CASCADE)",
 "CREATE TABLE private_gen_bin (namespace TEXT, key TEXT, value BLOB, PRIMARY KEY (namespace, key))",
 d = self.dbpool.runQuery("REPLACE INTO param_ind(category,name,profile_id,value) VALUES (?,?,?,?)", (category, name, self.profiles[profile], value))
 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})))
 return d
 #History
-def addToHistory(self, from_jid, to_jid, message, _type='chat', timestamp=None, profile=None):
+def addToHistory(self, from_jid, to_jid, message, _type='chat', extra=None, timestamp=None, profile=None):
 """Store a new message in history
 @param from_jid: full source JID
 @param to_jid: full dest JID
 @param message: message
 @param _type: message type (see RFC 6121 §5.2.2)
+@param extra: dictionary (keys and values are unicode) of extra message data
 @param timestamp: timestamp in seconds since epoch, or None to use current time
 """
 assert(profile)
-d = self.dbpool.runQuery("INSERT INTO history(source, source_res, dest, dest_res, timestamp, message, type, profile_id) VALUES (?,?,?,?,?,?,?,?)",
+if extra is None:
+extra = {}
+d = self.dbpool.runQuery("INSERT INTO history(source, source_res, dest, dest_res, timestamp, message, type, extra, profile_id) VALUES (?,?,?,?,?,?,?,?,?)",
 (from_jid.userhost(), from_jid.resource, to_jid.userhost(), to_jid.resource, timestamp or time(),
-message, _type, self.profiles[profile]))
+message, _type, pickle.dumps(extra, 0), self.profiles[profile]))
 d.addErrback(lambda ignore: error(_("Can't save following message in history: from [%(from_jid)s] to [%(to_jid)s] ==> [%(message)s]" %
 {"from_jid": from_jid.full(), "to_jid": to_jid.full(), "message": message})))
 return d
 def getHistory(self, from_jid, to_jid, limit=0, between=True, profile=None):
 @param to_jid: dest JID (full, or bare for catchall
 @param size: maximum number of messages to get, or 0 for unlimited
 """
 assert(profile)
-def sqliteToDict(query_result):
+def sqliteToList(query_result):
 query_result.reverse()
 result = []
 for row in query_result:
-timestamp, source, source_res, dest, dest_res, message, _type = row
+timestamp, source, source_res, dest, dest_res, message, type_, extra_raw = row
+try:
+extra = pickle.loads(str(extra_raw or ""))
+except EOFError:
+extra = {}
 result.append((timestamp, "%s/%s" % (source, source_res) if source_res else source,
 "%s/%s" % (dest, dest_res) if dest_res else dest,
-message, _type))
+message, type_, extra))
 return result
-query_parts = ["SELECT timestamp, source, source_res, dest, dest_res, message, type FROM history WHERE profile_id=? AND"]
+query_parts = ["SELECT timestamp, source, source_res, dest, dest_res, message, type, extra FROM history WHERE profile_id=? AND"]
 values = [self.profiles[profile]]
-def test_jid(_type, _jid):
+def test_jid(type_, _jid):
 values.append(_jid.userhost())
 if _jid.resource:
 values.append(_jid.resource)
-return '(%s=? AND %s_res=?)' % (_type, _type)
+return '(%s=? AND %s_res=?)' % (type_, type_)
-return '%s=?' % (_type, )
+return '%s=?' % (type_, )
 if between:
 query_parts.append("(%s OR %s) AND (%s or %s)" % (test_jid('source', from_jid),
 test_jid('source', to_jid),
 test_jid('dest', to_jid),
 if limit:
 query_parts.append("LIMIT ?")
 values.append(limit)
 d = self.dbpool.runQuery(" ".join(query_parts), values)
-return d.addCallback(sqliteToDict)
+return d.addCallback(sqliteToList)
 #Private values
 def loadGenPrivates(self, private_gen, namespace):
 """Load general private values
 @param private_gen: dictionary to fill

Mercurial > libervia-backend

comparison src/memory/sqlite.py @ 669:ffb716804580