accumulate conversation content in meta db table

7 files changed, 306 insertions, 255 deletions

diff --git a/libdino/CMakeLists.txt b/libdino/CMakeLists.txt
index de44195d..054e2bab 100644
--- a/libdino/CMakeLists.txt
+++ b/libdino/CMakeLists.txt
@@ -29,7 +29,7 @@ SOURCES
     src/service/blocking_manager.vala
     src/service/chat_interaction.vala
     src/service/connection_manager.vala
-    src/service/content_item_accumulator.vala
+    src/service/content_item_store.vala
     src/service/conversation_manager.vala
     src/service/counterpart_interaction_manager.vala
     src/service/database.vala
diff --git a/libdino/src/application.vala b/libdino/src/application.vala
index 80e474ac..7f278fa0 100644
--- a/libdino/src/application.vala
+++ b/libdino/src/application.vala
@@ -38,7 +38,7 @@ public interface Dino.Application : GLib.Application {
         ChatInteraction.start(stream_interactor);
         FileManager.start(stream_interactor, db);
         NotificationEvents.start(stream_interactor);
-        ContentItemAccumulator.start(stream_interactor);
+        ContentItemStore.start(stream_interactor, db);
         SearchProcessor.start(stream_interactor, db);
 
         create_actions();
diff --git a/libdino/src/service/content_item_accumulator.vala b/libdino/src/service/content_item_accumulator.vala
deleted file mode 100644
index 9f9e672c..00000000
--- a/libdino/src/service/content_item_accumulator.vala
+++ /dev/null
@@ -1,247 +0,0 @@
-using Gee;
-
-using Dino.Entities;
-using Xmpp;
-
-namespace Dino {
-
-public class ContentItemAccumulator : StreamInteractionModule, Object {
-    public static ModuleIdentity<ContentItemAccumulator> IDENTITY = new ModuleIdentity<ContentItemAccumulator>("content_item_accumulator");
-    public string id { get { return IDENTITY.id; } }
-
-    public signal void new_item();
-
-    private StreamInteractor stream_interactor;
-    private Gee.List<ContentFilter> filters = new ArrayList<ContentFilter>();
-    private HashMap<ContentItemCollection, Conversation> collection_conversations = new HashMap<ContentItemCollection, Conversation>();
-
-    public static void start(StreamInteractor stream_interactor) {
-        ContentItemAccumulator m = new ContentItemAccumulator(stream_interactor);
-        stream_interactor.add_module(m);
-    }
-
-    public ContentItemAccumulator(StreamInteractor stream_interactor) {
-        this.stream_interactor = stream_interactor;
-
-        stream_interactor.get_module(MessageProcessor.IDENTITY).message_received.connect(on_new_message);
-        stream_interactor.get_module(MessageProcessor.IDENTITY).message_sent.connect(on_new_message);
-        stream_interactor.get_module(FileManager.IDENTITY).received_file.connect(insert_file_transfer);
-    }
-
-    public void init(Conversation conversation, ContentItemCollection item_collection) {
-        collection_conversations[item_collection] = conversation;
-    }
-
-    public Gee.List<ContentItem> populate_latest(ContentItemCollection item_collection, Conversation conversation, int n) {
-        Gee.TreeSet<ContentItem> items = new Gee.TreeSet<ContentItem>(ContentItem.compare);
-
-        Gee.List<Entities.Message>? messages = stream_interactor.get_module(MessageStorage.IDENTITY).get_messages(conversation, n);
-        if (messages != null) {
-            foreach (Entities.Message message in messages) {
-                items.add(new MessageItem(message, conversation));
-            }
-        }
-        Gee.List<FileTransfer> transfers = stream_interactor.get_module(FileManager.IDENTITY).get_latest_transfers(conversation.account, conversation.counterpart, n);
-        foreach (FileTransfer transfer in transfers) {
-            items.add(new FileItem(transfer));
-        }
-
-        Gee.List<ContentItem> ret = new ArrayList<ContentItem>();
-        if (items.size == 0) return ret;
-
-        BidirIterator<ContentItem> iter = items.bidir_iterator();
-        iter.last();
-        int i = 0;
-        while (i < n - 1 && iter.has_previous()) {
-            iter.previous();
-            i++;
-        }
-        do {
-            ret.add(iter.get());
-        } while (iter.next());
-        return ret;
-    }
-
-    public Gee.List<ContentItem> populate_before(ContentItemCollection item_collection, Conversation conversation, ContentItem item, int n) {
-        Gee.TreeSet<ContentItem> items = new Gee.TreeSet<ContentItem>(ContentItem.compare);
-
-        int before_id = item as MessageItem != null ? (int)Math.floor(item.seccondary_sort_indicator) : -1;
-        Gee.List<Entities.Message>? messages = stream_interactor.get_module(MessageStorage.IDENTITY).get_messages_before_message(conversation, item.display_time, before_id, n);
-        if (messages != null) {
-            foreach (Entities.Message message in messages) {
-                items.add(new MessageItem(message, conversation));
-            }
-        }
-        Gee.List<FileTransfer> transfers = stream_interactor.get_module(FileManager.IDENTITY).get_transfers_before(conversation.account, conversation.counterpart, item.sort_time, n);
-        foreach (FileTransfer transfer in transfers) {
-            items.add(new FileItem(transfer));
-        }
-
-        Gee.List<ContentItem> ret = new ArrayList<ContentItem>();
-        if (items.size == 0) return ret;
-
-        BidirIterator<ContentItem> iter = items.bidir_iterator();
-        iter.last();
-        int i = 0;
-        while (i < n - 1 && iter.has_previous()) {
-            iter.previous();
-            i++;
-        }
-        do {
-            ret.add(iter.get());
-        } while (iter.next());
-        return ret;
-    }
-
-    public Gee.List<ContentItem> populate_after(ContentItemCollection item_collection, Conversation conversation, ContentItem item, int n) {
-        Gee.TreeSet<ContentItem> items = new Gee.TreeSet<ContentItem>(ContentItem.compare);
-
-        int after_id = item as MessageItem != null ? (int)Math.floor(item.seccondary_sort_indicator) : -1;
-        Gee.List<Entities.Message>? messages = stream_interactor.get_module(MessageStorage.IDENTITY).get_messages_after_message(conversation, item.sort_time, after_id, n);
-        if (messages != null) {
-            foreach (Entities.Message message in messages) {
-                items.add(new MessageItem(message, conversation));
-            }
-        }
-        Gee.List<FileTransfer> transfers = stream_interactor.get_module(FileManager.IDENTITY).get_transfers_after(conversation.account, conversation.counterpart, item.sort_time, n);
-        foreach (FileTransfer transfer in transfers) {
-            items.add(new FileItem(transfer));
-        }
-
-        Gee.List<ContentItem> ret = new ArrayList<ContentItem>();
-        foreach (ContentItem content_item in items) {
-            ret.add(content_item);
-        }
-        return ret;
-    }
-
-    public void add_filter(ContentFilter content_filter) {
-        filters.add(content_filter);
-    }
-
-    private void on_new_message(Message message, Conversation conversation) {
-        foreach (ContentItemCollection collection in collection_conversations.keys) {
-            if (collection_conversations[collection].equals(conversation)) {
-                MessageItem item = new MessageItem(message, conversation);
-                insert_item(collection, item);
-            }
-        }
-    }
-
-    private void insert_file_transfer(FileTransfer file_transfer) {
-        foreach (ContentItemCollection collection in collection_conversations.keys) {
-            Conversation conversation = collection_conversations[collection];
-            if (conversation.account.equals(file_transfer.account) && conversation.counterpart.equals_bare(file_transfer.counterpart)) {
-                FileItem item = new FileItem(file_transfer);
-                insert_item(collection, item);
-            }
-        }
-    }
-
-    private void insert_item(ContentItemCollection item_collection, ContentItem content_item) {
-        bool insert = true;
-        foreach (ContentFilter filter in filters) {
-            if (filter.discard(content_item)) {
-                insert = false;
-            }
-        }
-        if (insert) {
-            item_collection.insert_item(content_item);
-        }
-    }
-}
-
-public interface ContentItemCollection : Object {
-    public abstract void insert_item(ContentItem item);
-    public abstract void remove_item(ContentItem item);
-}
-
-public interface ContentFilter : Object {
-    public abstract bool discard(ContentItem content_item);
-}
-
-public abstract class ContentItem : Object {
-    public virtual string type_ { get; set; }
-    public virtual Jid? jid { get; set; default=null; }
-    public virtual DateTime? sort_time { get; set; default=null; }
-    public virtual double seccondary_sort_indicator { get; set; }
-    public virtual DateTime? display_time { get; set; default=null; }
-    public virtual Encryption? encryption { get; set; default=null; }
-    public virtual Entities.Message.Marked? mark { get; set; default=null; }
-
-    public static int compare(ContentItem a, ContentItem b) {
-        int res = a.sort_time.compare(b.sort_time);
-        if (res == 0) {
-            res = a.display_time.compare(b.display_time);
-        }
-        if (res == 0) {
-            res = a.seccondary_sort_indicator - b.seccondary_sort_indicator > 0 ? 1 : -1;
-        }
-        return res;
-    }
-}
-
-public class MessageItem : ContentItem {
-    public const string TYPE = "message";
-    public override string type_ { get; set; default=TYPE; }
-
-    public Message message;
-    public Conversation conversation;
-
-    public MessageItem(Message message, Conversation conversation) {
-        this.message = message;
-        this.conversation = conversation;
-
-        this.jid = message.from;
-        this.sort_time = message.local_time;
-        this.seccondary_sort_indicator = message.id + 0.0845;
-        this.display_time = message.time;
-        this.encryption = message.encryption;
-        this.mark = message.marked;
-
-        WeakRef weak_message = WeakRef(message);
-        message.notify["marked"].connect(() => {
-            Message? m = weak_message.get() as Message;
-            if (m == null) return;
-            mark = m.marked;
-        });
-    }
-}
-
-public class FileItem : ContentItem {
-    public const string TYPE = "file";
-    public override string type_ { get; set; default=TYPE; }
-
-    public FileTransfer file_transfer;
-    public Conversation conversation;
-
-    public FileItem(FileTransfer file_transfer) {
-        this.file_transfer = file_transfer;
-
-        this.jid = file_transfer.direction == FileTransfer.DIRECTION_SENT ? file_transfer.account.bare_jid.with_resource(file_transfer.account.resourcepart) : file_transfer.counterpart;
-        this.sort_time = file_transfer.local_time;
-        this.seccondary_sort_indicator = file_transfer.id + 0.2903;
-        this.display_time = file_transfer.time;
-        this.encryption = file_transfer.encryption;
-        this.mark = file_to_message_state(file_transfer.state);
-        file_transfer.notify["state"].connect_after(() => {
-            this.mark = file_to_message_state(file_transfer.state);
-        });
-    }
-
-    private Entities.Message.Marked file_to_message_state(FileTransfer.State state) {
-        switch (state) {
-            case FileTransfer.State.IN_PROCESS:
-                return Entities.Message.Marked.UNSENT;
-            case FileTransfer.State.COMPLETE:
-                return Entities.Message.Marked.NONE;
-            case FileTransfer.State.NOT_STARTED:
-                return Entities.Message.Marked.UNSENT;
-            case FileTransfer.State.FAILED:
-                return Entities.Message.Marked.WONTSEND;
-        }
-        assert_not_reached();
-    }
-}
-
-}
diff --git a/libdino/src/service/content_item_store.vala b/libdino/src/service/content_item_store.vala
new file mode 100644
index 00000000..39bdfdde
--- /dev/null
+++ b/libdino/src/service/content_item_store.vala
@@ -0,0 +1,246 @@
+using Gee;
+
+using Dino.Entities;
+using Qlite;
+using Xmpp;
+
+namespace Dino {
+
+public class ContentItemStore : StreamInteractionModule, Object {
+    public static ModuleIdentity<ContentItemStore> IDENTITY = new ModuleIdentity<ContentItemStore>("content_item_store");
+    public string id { get { return IDENTITY.id; } }
+
+    public signal void new_item(ContentItem item, Conversation conversation);
+
+    private StreamInteractor stream_interactor;
+    private Database db;
+    private Gee.List<ContentFilter> filters = new ArrayList<ContentFilter>();
+    private HashMap<Conversation, ContentItemCollection> collection_conversations = new HashMap<Conversation, ContentItemCollection>(Conversation.hash_func, Conversation.equals_func);
+
+    public static void start(StreamInteractor stream_interactor, Database db) {
+        ContentItemStore m = new ContentItemStore(stream_interactor, db);
+        stream_interactor.add_module(m);
+    }
+
+    public ContentItemStore(StreamInteractor stream_interactor, Database db) {
+        this.stream_interactor = stream_interactor;
+        this.db = db;
+
+        stream_interactor.get_module(MessageProcessor.IDENTITY).message_received.connect(on_new_message);
+        stream_interactor.get_module(MessageProcessor.IDENTITY).message_sent.connect(on_new_message);
+        stream_interactor.get_module(FileManager.IDENTITY).received_file.connect(insert_file_transfer);
+    }
+
+    public void init(Conversation conversation, ContentItemCollection item_collection) {
+        collection_conversations[conversation] = item_collection;
+    }
+
+    public void uninit(Conversation conversation, ContentItemCollection item_collection) {
+        collection_conversations.unset(conversation);
+    }
+
+    public Gee.List<ContentItem> get_items_from_query(QueryBuilder select, Conversation conversation) {
+        Gee.TreeSet<ContentItem> items = new Gee.TreeSet<ContentItem>(ContentItem.compare);
+
+        foreach (var row in select) {
+            int provider = row[db.content.content_type];
+            int foreign_id = row[db.content.foreign_id];
+            switch (provider) {
+                case 1:
+                    RowOption row_option = db.message.select().with(db.message.id, "=", foreign_id).row();
+                    if (row_option.is_present()) {
+                        Message message = stream_interactor.get_module(MessageStorage.IDENTITY).get_message_by_id(foreign_id, conversation);
+                        if (message == null) {
+                            message = new Message.from_row(db, row_option.inner);
+                        }
+                        items.add(new MessageItem(message, conversation, row[db.content.id]));
+                    }
+                    break;
+                case 2:
+                    RowOption row_option = db.file_transfer.select().with(db.file_transfer.id, "=", foreign_id).row();
+                    if (row_option.is_present()) {
+                        string storage_dir = stream_interactor.get_module(FileManager.IDENTITY).get_storage_dir();
+                        FileTransfer file_transfer = new FileTransfer.from_row(db, row_option.inner, storage_dir);
+                        items.add(new FileItem(file_transfer, row[db.content.id]));
+                    }
+                    break;
+            }
+        }
+
+        Gee.List<ContentItem> ret = new ArrayList<ContentItem>();
+        foreach (ContentItem item in items) {
+            ret.add(item);
+        }
+        return ret;
+    }
+
+    public Gee.List<ContentItem> get_latest(Conversation conversation, int count) {
+        QueryBuilder select = db.content.select()
+            .with(db.content.conversation_id, "=", conversation.id)
+            .order_by(db.content.local_time, "DESC")
+            .order_by(db.content.time, "DESC")
+            .limit(count);
+
+        return get_items_from_query(select, conversation);
+    }
+
+    public Gee.List<ContentItem> get_before(Conversation conversation, ContentItem item, int count) {
+        long local_time = (long) item.sort_time.to_unix();
+        long time = (long) item.display_time.to_unix();
+        QueryBuilder select = db.content.select()
+            .where(@"local_time < ? OR (local_time = ? AND time < ?) OR (local_time = ? AND time = ? AND id < ?)", { local_time.to_string(), local_time.to_string(), time.to_string(), local_time.to_string(), time.to_string(), item.id.to_string() })
+            .with(db.content.conversation_id, "=", conversation.id)
+            .order_by(db.content.local_time, "DESC")
+            .order_by(db.content.time, "DESC")
+            .limit(count);
+
+        return get_items_from_query(select, conversation);
+    }
+
+    public Gee.List<ContentItem> get_after(Conversation conversation, ContentItem item, int count) {
+        long local_time = (long) item.sort_time.to_unix();
+        long time = (long) item.display_time.to_unix();
+        QueryBuilder select = db.content.select()
+            .where(@"local_time > ? OR (local_time = ? AND time > ?) OR (local_time = ? AND time = ? AND id > ?)", { local_time.to_string(), local_time.to_string(), time.to_string(), local_time.to_string(), time.to_string(), item.id.to_string() })
+            .with(db.content.conversation_id, "=", conversation.id)
+            .order_by(db.content.local_time, "ASC")
+            .order_by(db.content.time, "ASC")
+            .limit(count);
+
+        return get_items_from_query(select, conversation);
+    }
+
+    public void add_filter(ContentFilter content_filter) {
+        filters.add(content_filter);
+    }
+
+    private void on_new_message(Message message, Conversation conversation) {
+        MessageItem item = new MessageItem(message, conversation, -1);
+        if (!discard(item)) {
+            item.id = db.add_content_item(conversation, message.time, message.local_time, 1, message.id);
+
+            if (collection_conversations.has_key(conversation)) {
+                collection_conversations.get(conversation).insert_item(item);
+            }
+            new_item(item, conversation);
+        }
+    }
+
+    private void insert_file_transfer(FileTransfer file_transfer) {
+        FileItem item = new FileItem(file_transfer, -1);
+        if (!discard(item)) {
+            Conversation? conversation = stream_interactor.get_module(ConversationManager.IDENTITY).get_conversation(file_transfer.counterpart, file_transfer.account);
+            item.id = db.add_content_item(conversation, file_transfer.time, file_transfer.local_time, 2, file_transfer.id);
+
+            if (collection_conversations.has_key(conversation)) {
+                collection_conversations.get(conversation).insert_item(item);
+            }
+            new_item(item, conversation);
+        }
+    }
+
+    private bool discard(ContentItem content_item) {
+        foreach (ContentFilter filter in filters) {
+            if (filter.discard(content_item)) {
+                return true;
+            }
+        }
+        return false;
+    }
+}
+
+public interface ContentItemCollection : Object {
+    public abstract void insert_item(ContentItem item);
+    public abstract void remove_item(ContentItem item);
+}
+
+public interface ContentFilter : Object {
+    public abstract bool discard(ContentItem content_item);
+}
+
+public abstract class ContentItem : Object {
+    public int id { get; set; }
+    public string type_ { get; set; }
+    public Jid? jid { get; set; default=null; }
+    public DateTime? sort_time { get; set; default=null; }
+    public double seccondary_sort_indicator { get; set; }
+    public DateTime? display_time { get; set; default=null; }
+    public Encryption? encryption { get; set; default=null; }
+    public Entities.Message.Marked? mark { get; set; default=null; }
+
+    public ContentItem(int id, string ty, Jid jid, DateTime sort_time, double seccondary_sort_indicator, DateTime display_time, Encryption encryption, Entities.Message.Marked mark) {
+        this.id = id;
+        this.type_ = ty;
+        this.jid = jid;
+        this.sort_time = sort_time;
+        this.seccondary_sort_indicator = seccondary_sort_indicator;
+        this.display_time = display_time;
+        this.encryption = encryption;
+        this.mark = mark;
+    }
+
+    public static int compare(ContentItem a, ContentItem b) {
+        int res = a.sort_time.compare(b.sort_time);
+        if (res == 0) {
+            res = a.display_time.compare(b.display_time);
+        }
+        if (res == 0) {
+            res = a.seccondary_sort_indicator - b.seccondary_sort_indicator > 0 ? 1 : -1;
+        }
+        return res;
+    }
+}
+
+public class MessageItem : ContentItem {
+    public const string TYPE = "message";
+
+    public Message message;
+    public Conversation conversation;
+
+    public MessageItem(Message message, Conversation conversation, int id) {
+        base(id, TYPE, message.from, message.local_time, message.id + 0.0845, message.time, message.encryption, message.marked);
+        this.message = message;
+        this.conversation = conversation;
+
+        WeakRef weak_message = WeakRef(message);
+        message.notify["marked"].connect(() => {
+            Message? m = weak_message.get() as Message;
+            if (m == null) return;
+            mark = m.marked;
+        });
+    }
+}
+
+public class FileItem : ContentItem {
+    public const string TYPE = "file";
+
+    public FileTransfer file_transfer;
+    public Conversation conversation;
+
+    public FileItem(FileTransfer file_transfer, int id) {
+        Jid jid = file_transfer.direction == FileTransfer.DIRECTION_SENT ? file_transfer.account.bare_jid.with_resource(file_transfer.account.resourcepart) : file_transfer.counterpart;
+        base(id, TYPE, jid, file_transfer.local_time, file_transfer.id + 0.0845, file_transfer.time, file_transfer.encryption, file_to_message_state(file_transfer.state));
+
+        this.file_transfer = file_transfer;
+
+        file_transfer.notify["state"].connect_after(() => {
+            this.mark = file_to_message_state(file_transfer.state);
+        });
+    }
+
+    private static Entities.Message.Marked file_to_message_state(FileTransfer.State state) {
+        switch (state) {
+            case FileTransfer.State.IN_PROCESS:
+                return Entities.Message.Marked.UNSENT;
+            case FileTransfer.State.COMPLETE:
+                return Entities.Message.Marked.NONE;
+            case FileTransfer.State.NOT_STARTED:
+                return Entities.Message.Marked.UNSENT;
+            case FileTransfer.State.FAILED:
+                return Entities.Message.Marked.WONTSEND;
+        }
+        assert_not_reached();
+    }
+}
+
+}
diff --git a/libdino/src/service/counterpart_interaction_manager.vala b/libdino/src/service/counterpart_interaction_manager.vala
index fb10d20c..b4df9b8d 100644
--- a/libdino/src/service/counterpart_interaction_manager.vala
+++ b/libdino/src/service/counterpart_interaction_manager.vala
@@ -9,7 +9,7 @@ public class CounterpartInteractionManager : StreamInteractionModule, Object {
     public string id { get { return IDENTITY.id; } }
 
     public signal void received_state(Account account, Jid jid, string state);
-    public signal void received_marker(Account account, Jid jid, Entities.Message message, string marker);
+    public signal void received_marker(Account account, Jid jid, Entities.Message message, Entities.Message.Marked marker);
     public signal void received_message_received(Account account, Jid jid, Entities.Message message);
     public signal void received_message_displayed(Account account, Jid jid, Entities.Message message);
 
@@ -69,12 +69,12 @@ public class CounterpartInteractionManager : StreamInteractionModule, Object {
             if (marker != Xep.ChatMarkers.MARKER_DISPLAYED && marker != Xep.ChatMarkers.MARKER_ACKNOWLEDGED) return;
             Conversation? conversation = stream_interactor.get_module(MessageStorage.IDENTITY).get_conversation_for_stanza_id(account, stanza_id);
             if (conversation == null) return;
-            Entities.Message? message = stream_interactor.get_module(MessageStorage.IDENTITY).get_message_by_id(stanza_id, conversation);
+            Entities.Message? message = stream_interactor.get_module(MessageStorage.IDENTITY).get_message_by_stanza_id(stanza_id, conversation);
             if (message == null) return;
             conversation.read_up_to = message;
         } else {
             foreach (Conversation conversation in stream_interactor.get_module(ConversationManager.IDENTITY).get_conversations(jid, account)) {
-                Entities.Message? message = stream_interactor.get_module(MessageStorage.IDENTITY).get_message_by_id(stanza_id, conversation);
+                Entities.Message? message = stream_interactor.get_module(MessageStorage.IDENTITY).get_message_by_stanza_id(stanza_id, conversation);
                 if (message != null) {
                     switch (marker) {
                         case Xep.ChatMarkers.MARKER_RECEIVED:
diff --git a/libdino/src/service/database.vala b/libdino/src/service/database.vala
index d02e4c71..01cc2f52 100644
--- a/libdino/src/service/database.vala
+++ b/libdino/src/service/database.vala
@@ -6,7 +6,7 @@ using Dino.Entities;
 namespace Dino {
 
 public class Database : Qlite.Database {
-    private const int VERSION = 7;
+    private const int VERSION = 8;
 
     public class AccountTable : Table {
         public Column<int> id = new Column.Integer("id") { primary_key = true, auto_increment = true };
@@ -34,6 +34,20 @@ public class Database : Qlite.Database {
         }
     }
 
+    public class ContentTable : Table {
+        public Column<int> id = new Column.Integer("id") { primary_key = true, auto_increment = true };
+        public Column<int> conversation_id = new Column.Integer("conversation_id") { not_null = true };
+        public Column<long> time = new Column.Long("time") { not_null = true };
+        public Column<long> local_time = new Column.Long("local_time") { not_null = true };
+        public Column<int> content_type = new Column.Integer("content_type") { not_null = true };
+        public Column<int> foreign_id = new Column.Integer("foreign_id") { not_null = true };
+
+        internal ContentTable(Database db) {
+            base(db, "content");
+            init({id, conversation_id, time, local_time, content_type, foreign_id});
+        }
+    }
+
     public class MessageTable : Table {
         public Column<int> id = new Column.Integer("id") { primary_key = true, auto_increment = true };
         public Column<string> stanza_id = new Column.Text("stanza_id");
@@ -174,6 +188,7 @@ public class Database : Qlite.Database {
 
     public AccountTable account { get; private set; }
     public JidTable jid { get; private set; }
+    public ContentTable content { get; private set; }
     public MessageTable message { get; private set; }
     public RealJidTable real_jid { get; private set; }
     public FileTransferTable file_transfer { get; private set; }
@@ -191,6 +206,7 @@ public class Database : Qlite.Database {
         base(fileName, VERSION);
         account = new AccountTable(this);
         jid = new JidTable(this);
+        content = new ContentTable(this);
         message = new MessageTable(this);
         real_jid = new RealJidTable(this);
         file_transfer = new FileTransferTable(this);
@@ -199,7 +215,7 @@ public class Database : Qlite.Database {
         entity_feature = new EntityFeatureTable(this);
         roster = new RosterTable(this);
         settings = new SettingsTable(this);
-        init({ account, jid, message, real_jid, file_transfer, conversation, avatar, entity_feature, roster, settings });
+        init({ account, jid, content, message, real_jid, file_transfer, conversation, avatar, entity_feature, roster, settings });
         try {
             exec("PRAGMA synchronous=0");
         } catch (Error e) { }
@@ -209,6 +225,24 @@ public class Database : Qlite.Database {
         // new table columns are added, outdated columns are still present
         if (oldVersion < 7) {
             message.fts_rebuild();
+        } else if (oldVersion < 8) {
+            exec("""
+            insert into content (conversation_id, time, local_time, content_type, foreign_id)
+            select conversation.id, message.time, message.local_time, 1, message.id
+            from message join conversation on
+                message.account_id=conversation.account_id and
+                message.counterpart_id=conversation.jid_id and
+                message.type=conversation.type+1 and
+                (message.counterpart_resource=conversation.resource or message.type != 3)
+            where
+                message.body not in (select info from file_transfer where info not null) and
+                message.id not in (select info from file_transfer where info not null)
+            union
+            select conversation.id, file_transfer.time, file_transfer.local_time, 2, file_transfer.id
+            from file_transfer join conversation on
+                file_transfer.account_id=conversation.account_id and
+                file_transfer.counterpart_id=conversation.jid_id
+            order by message.local_time, message.time""");
         }
     }
 
@@ -236,6 +270,16 @@ public class Database : Qlite.Database {
         }
     }
 
+    public int add_content_item(Conversation conversation, DateTime time, DateTime local_time, int content_type, int foreign_id) {
+        return (int) content.insert()
+            .value(content.conversation_id, conversation.id)
+            .value(content.local_time, (long) local_time.to_unix())
+            .value(content.time, (long) time.to_unix())
+            .value(content.content_type, content_type)
+            .value(content.foreign_id, foreign_id)
+            .perform();
+    }
+
     public Gee.List<Message> get_messages(Xmpp.Jid jid, Account account, Message.Type? type, int count, DateTime? before, DateTime? after, int id) {
         QueryBuilder select = message.select()
                 .with(message.counterpart_id, "=", get_jid_id(jid))
diff --git a/libdino/src/service/message_storage.vala b/libdino/src/service/message_storage.vala
index abc8acb4..9fb6ab19 100644
--- a/libdino/src/service/message_storage.vala
+++ b/libdino/src/service/message_storage.vala
@@ -76,7 +76,15 @@ public class MessageStorage : StreamInteractionModule, Object {
         return db_messages;
     }
 
-    public Message? get_message_by_id(string stanza_id, Conversation conversation) {
+    public Message? get_message_by_id(int id, Conversation conversation) {
+        init_conversation(conversation);
+        foreach (Message message in messages[conversation]) {
+            if (message.id == id) return message;
+        }
+        return null;
+    }
+
+    public Message? get_message_by_stanza_id(string stanza_id, Conversation conversation) {
         init_conversation(conversation);
         foreach (Message message in messages[conversation]) {
             if (message.stanza_id == stanza_id) return message;