--- a/src/memory/sqlite.py	Tue Oct 16 01:22:40 2012 +0200
+++ b/src/memory/sqlite.py	Wed Oct 17 00:35:48 2012 +0200
@@ -46,7 +46,13 @@
             info(_("The database is new, creating the tables"))
             database_creation = [
             "CREATE TABLE profiles (id INTEGER PRIMARY KEY ASC, name TEXT, UNIQUE (name))",
-            "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))",
+            "CREATE TABLE message_types (type TEXT PRIMARY KEY)",
+            "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), 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))",
             "CREATE TABLE private_gen (namespace TEXT, key TEXT, value TEXT, PRIMARY KEY (namespace, key))",
@@ -169,17 +175,18 @@
         return d
 
     #History
-    def addToHistory(self, from_jid, to_jid, message, timestamp=None, profile=None):
+    def addToHistory(self, from_jid, to_jid, message, _type='chat', 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 timestamp: timestamp in seconds since epoch, or None to use current time
         """
         assert(profile!=None)
-        d = self.dbpool.runQuery("INSERT INTO history(source, source_res, dest, dest_res, timestamp, message, profile_id) VALUES (?,?,?,?,?,?,?)",
+        d = self.dbpool.runQuery("INSERT INTO history(source, source_res, dest, dest_res, timestamp, message, type, profile_id) VALUES (?,?,?,?,?,?,?,?)",
                                 (from_jid.userhost(), from_jid.resource, to_jid.userhost(), to_jid.resource, timestamp or time.time(),
-                                message, self.profiles[profile]))
+                                message, _type, 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
@@ -194,33 +201,38 @@
             query_result.reverse()
             result = []
             for row in query_result:
-                timestamp, source, source_res, dest, dest_res, message = row
+                timestamp, source, source_res, dest, dest_res, message, _type= row
                 result.append((timestamp, "%s/%s" % (source, source_res) if source_res else source,
                                           "%s/%s" % (dest, dest_res) if dest_res else dest,
-                                          message))
+                                          message, _type))
             return result
-        
-        query_parts = ["SELECT timestamp, source, source_res, dest, dest_res, message FROM history WHERE"]
+
+
+        query_parts = ["SELECT timestamp, source, source_res, dest, dest_res, message, type FROM history WHERE"]
         values = []
 
+        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=?' % (_type,)
+        
         if between:
-            query_parts.append("(source=? OR source=?) AND (dest=? or dest=?)")
-            values.extend([from_jid.userhost(), to_jid.userhost(), to_jid.userhost(), from_jid.userhost()])
+            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),
+                                                               test_jid('dest', from_jid)))
         else:
-            query_parts.append("source=? AND dest=?")
-            values.extend([from_jid.userhost(), to_jid.userhost()])
-        if from_jid.resource:
-            query_parts.append("AND source_res=?")
-            values.append(from_jid.resource)
-        if to_jid.resource:
-            query_parts.append("AND dest_res=?")
-            values.append(to_jid.resource)
+            query_parts.append("%s AND %s") % (test_jid('source', from_jid),
+                                               test_jid('dest', to_jid))
 
         query_parts.append("ORDER BY timestamp DESC")
 
         if limit:
             query_parts.append("LIMIT ?")
             values.append(limit)
+        
         d = self.dbpool.runQuery(" ".join(query_parts), values)
         return d.addCallback(sqliteToDict)