feat: new db schema

1 files changed, 133 insertions, 148 deletions

diff --git a/filamento/src/db.rs b/filamento/src/db.rs
index 298d54a..79fdd9a 100644
--- a/filamento/src/db.rs
+++ b/filamento/src/db.rs
@@ -341,22 +341,6 @@ impl Db {
         result
     }
 
-    pub(crate) async fn update_chat_correspondent(
-        &self,
-        old_chat: Chat,
-        new_correspondent: BareJID,
-    ) -> Result<Chat, Error> {
-        let (result, recv) = oneshot::channel();
-        let command = DbCommand::UpdateChatCorrespondent {
-            old_chat,
-            new_correspondent,
-            result,
-        };
-        self.sender.send(command);
-        let result = recv.await?;
-        result
-    }
-
     // pub(crate) async fn update_chat
 
     pub(crate) async fn delete_chat(&self, chat: BareJID) -> Result<(), Error> {
@@ -416,7 +400,7 @@ impl Db {
         &self,
         message: Message,
         chat: BareJID,
-        from: FullJID,
+        from: BareJID,
     ) -> Result<(), Error> {
         let (result, recv) = oneshot::channel();
         let command = DbCommand::CreateMessage {
@@ -438,28 +422,6 @@ impl Db {
         result
     }
 
-    /// create direct message from incoming. MUST upsert chat and user
-    #[tracing::instrument]
-    pub(crate) async fn create_message_with_user_resource(
-        &self,
-        message: Message,
-        // TODO: enforce two kinds of jid. bare and full
-        chat: BareJID,
-        from: FullJID,
-    ) -> Result<(), Error> {
-        tracing::info!("MSGDEBUG create_message_with_user_resource exists");
-        let (result, recv) = oneshot::channel();
-        let command = DbCommand::CreateMessageWithUserResource {
-            message,
-            chat,
-            from,
-            result,
-        };
-        self.sender.send(command);
-        let result = recv.await?;
-        result
-    }
-
     pub(crate) async fn update_message_delivery(
         &self,
         message: Uuid,
@@ -600,11 +562,6 @@ pub enum DbCommand {
         chat: BareJID,
         result: oneshot::Sender<Result<(), Error>>,
     },
-    UpdateChatCorrespondent {
-        old_chat: Chat,
-        new_correspondent: BareJID,
-        result: oneshot::Sender<Result<Chat, Error>>,
-    },
     DeleteChat {
         chat: BareJID,
         result: oneshot::Sender<Result<(), Error>>,
@@ -632,19 +589,13 @@ pub enum DbCommand {
     CreateMessage {
         message: Message,
         chat: BareJID,
-        from: FullJID,
+        from: BareJID,
         result: oneshot::Sender<Result<(), Error>>,
     },
     UpsertChatAndUser {
         chat: BareJID,
         result: oneshot::Sender<Result<bool, Error>>,
     },
-    CreateMessageWithUserResource {
-        message: Message,
-        chat: BareJID,
-        from: FullJID,
-        result: oneshot::Sender<Result<(), Error>>,
-    },
     UpdateMessageDelivery {
         message: Uuid,
         delivery: Delivery,
@@ -710,7 +661,6 @@ impl Display for DbCommand {
             DbCommand::CreateChat { chat, result } => "CreateChat",
             DbCommand::ReadChat { chat, result } => "ReadChat",
             DbCommand::MarkChatAsChatted { chat, result } => "MarkChatAsChatted",
-            DbCommand::UpdateChatCorrespondent { old_chat, new_correspondent, result } => "UpdateChatCorrespondent",
             DbCommand::DeleteChat { chat, result } => "DeleteChat",
             DbCommand::ReadChats { result } => "ReadChats",
             DbCommand::ReadChatsOrdered { result } => "ReadChatsOrdered",
@@ -718,7 +668,6 @@ impl Display for DbCommand {
             DbCommand::ReadChatsOrderedWithLatestMessagesAndUsers { result } => "ReadChatsOrderedWithLatestMessagesAndUsers",
             DbCommand::CreateMessage { message, chat, from, result } => "CreateMessage",
             DbCommand::UpsertChatAndUser { chat, result } => "UpsertChatAndUser",
-            DbCommand::CreateMessageWithUserResource { message, chat, from, result } => "CreateMessageWithUserResource",
             DbCommand::UpdateMessageDelivery { message, delivery, result } => "UpdateMessageDelivery",
             DbCommand::DeleteMessage { message, result } => "DeleteMessage",
             DbCommand::ReadMessage { message, result } => "ReadMessage",
@@ -865,13 +814,6 @@ impl DbActor {
                 DbCommand::MarkChatAsChatted { chat, result } => {
                     result.send(self.mark_chat_as_chatted(chat));
                 }
-                DbCommand::UpdateChatCorrespondent {
-                    old_chat,
-                    new_correspondent,
-                    result,
-                } => {
-                    result.send(self.update_chat_correspondent(old_chat, new_correspondent));
-                }
                 DbCommand::DeleteChat { chat, result } => {
                     result.send(self.delete_chat(chat));
                 }
@@ -898,14 +840,6 @@ impl DbActor {
                 DbCommand::UpsertChatAndUser { chat, result } => {
                     result.send(self.upsert_chat_and_user(&chat));
                 }
-                DbCommand::CreateMessageWithUserResource {
-                    message,
-                    chat,
-                    from,
-                    result,
-                } => {
-                    result.send(self.create_message_with_user_resource(message, chat, from));
-                }
                 DbCommand::UpdateMessageDelivery {
                     message,
                     delivery,
@@ -1059,7 +993,7 @@ impl DbActor {
         }
     }
 
-    // TODO: use references everywhere
+    // TODO: use references everywhere?
     pub(crate) fn update_user(&self, user: User) -> Result<(), Error> {
         self.db.execute(
             "update users set nick = ?1, avatar = ?2 where user_jid = ?1",
@@ -1260,23 +1194,25 @@ impl DbActor {
     }
 
     pub(crate) fn create_chat(&self, chat: Chat) -> Result<(), Error> {
-        let id = Uuid::new_v4();
         let jid = chat.correspondent();
+        debug!("aick: before user identity upsert {jid}");
+        let identity = self.upsert_user_identity(jid)?;
+        debug!("aick: chat user identity: {identity}");
+        let id = Uuid::new_v4();
+        debug!("aick: chat uuid: {id}");
         self.db.execute(
             "insert into chats (id, correspondent, have_chatted) values (?1, ?2, ?3)",
-            (id, jid, chat.have_chatted),
+            (id, identity, chat.have_chatted),
         )?;
         Ok(())
     }
 
-    // TODO: what happens if a correspondent changes from a user to a contact? maybe just have correspondent be a user, then have the client make the user show up as a contact in ui if they are in the loaded roster.
-
     /// TODO: this is NOT a read
     pub(crate) fn read_chat(&self, chat: BareJID) -> Result<Chat, Error> {
         let chat_opt = self
             .db
             .query_row(
-                "select correspondent, have_chatted from chats where correspondent = ?1",
+                "select primary_jid, have_chatted from chats join identities on correspondent = identities.id where primary_jid = ?1",
                 [&chat],
                 |row| {
                     Ok(Chat {
@@ -1302,7 +1238,7 @@ impl DbActor {
     pub(crate) fn read_chat_and_user(&self, chat: BareJID) -> Result<(Chat, User), Error> {
         let user = self.read_user(chat.clone())?;
         let chat_opt = self.db.query_row(
-            "select correspondent, have_chatted, jid, nick, avatar from chats join users on correspondent = jid where correspondent = ?1",
+            "select primary_jid, have_chatted, jid, nick, avatar from chats join identities i on chats.correspondent = i.id join users on jid = primary_jid where primary_jid = ?1",
             [&chat],
             |row| {
                 Ok((
@@ -1325,7 +1261,9 @@ impl DbActor {
                     correspondent: chat,
                     have_chatted: false,
                 };
+                debug!("aick: creating chat");
                 self.create_chat(chat.clone())?;
+                debug!("aick: created chat");
                 Ok((chat, user))
             }
         }
@@ -1333,35 +1271,15 @@ impl DbActor {
 
     pub(crate) fn mark_chat_as_chatted(&self, chat: BareJID) -> Result<(), Error> {
         self.db.execute(
-            "update chats set have_chatted = true where correspondent = ?1",
+            "update chats set have_chatted = true where correspondent = (select id from identities where primary_jid = ?1)",
             [chat],
         )?;
         Ok(())
     }
 
-    pub(crate) fn update_chat_correspondent(
-        &self,
-        old_chat: Chat,
-        new_correspondent: BareJID,
-    ) -> Result<Chat, Error> {
-        let new_jid = &new_correspondent;
-        let old_jid = old_chat.correspondent();
-        let chat = self.db.query_row(
-            "update chats set correspondent = ?1 where correspondent = ?2 returning correspondent, have_chatted",
-            [new_jid, old_jid],
-            |row| Ok(Chat {
-                correspondent: row.get(0)?,
-                have_chatted: row.get(1)?,
-            })
-        )?;
-        Ok(chat)
-    }
-
-    // pub(crate) fn update_chat
-
     pub(crate) fn delete_chat(&self, chat: BareJID) -> Result<(), Error> {
         self.db
-            .execute("delete from chats where correspondent = ?1", [chat])?;
+            .execute("delete from chats where correspondent = (select id from identities where primary_jid = ?1)", [chat])?;
         Ok(())
     }
 
@@ -1369,7 +1287,7 @@ impl DbActor {
     pub(crate) fn read_chats(&self) -> Result<Vec<Chat>, Error> {
         let chats = self
             .db
-            .prepare("select correspondent, have_chatted from chats")?
+            .prepare("select primary_jid, have_chatted from chats join identities on correspondent = identities.id")?
             .query_map([], |row| {
                 Ok(Chat {
                     correspondent: row.get(0)?,
@@ -1385,7 +1303,7 @@ impl DbActor {
     pub(crate) fn read_chats_ordered(&self) -> Result<Vec<Chat>, Error> {
         let chats = self
             .db
-            .prepare("select c.correspondent, c.have_chatted, m.* from chats c join (select chat_id, max(timestamp) max_timestamp from messages group by chat_id) max_timestamps on c.id = max_timestamps.chat_id join messages m on max_timestamps.chat_id = m.chat_id and max_timestamps.max_timestamp = m.timestamp order by m.timestamp desc")?
+            .prepare("select i.primary_jid, c.have_chatted, m.* from chats c join identities i on c.correspondent = i.id join (select chat_id, max(timestamp) max_timestamp from messages group by chat_id) max_timestamps on c.id = max_timestamps.chat_id join messages m on max_timestamps.chat_id = m.chat_id and max_timestamps.max_timestamp = m.timestamp order by m.timestamp desc")?
             .query_map([], |row| {
                 Ok(Chat {
                     correspondent: row.get(0)?,
@@ -1401,9 +1319,32 @@ impl DbActor {
     pub(crate) fn read_chats_ordered_with_latest_messages(
         &self,
     ) -> Result<Vec<(Chat, Message)>, Error> {
-        let chats = self
+        let mut chats = self
             .db
-            .prepare("select c.correspondent, c.have_chatted, m.id, m.from_jid, m.delivery, m.timestamp, m.body from chats c join (select chat_id, max(timestamp) max_timestamp from messages group by chat_id) max_timestamps on c.id = max_timestamps.chat_id join messages m on max_timestamps.chat_id = m.chat_id and max_timestamps.max_timestamp = m.timestamp order by m.timestamp desc")?
+            .prepare(
+                "
+SELECT ci.primary_jid,
+       c.have_chatted,
+       m.id,
+       ui.primary_jid,
+       m.delivery,
+       m.timestamp,
+       m.body
+FROM   chats c
+       JOIN (SELECT chat_id,
+                    Max(timestamp) max_timestamp
+             FROM   messages
+             GROUP  BY chat_id) max_timestamps
+         ON c.id = max_timestamps.chat_id
+       JOIN messages m
+         ON max_timestamps.chat_id = m.chat_id
+            AND max_timestamps.max_timestamp = m.timestamp
+       JOIN identities AS ci
+         ON ci.id = c.correspondent
+       JOIN identities AS ui
+         ON ui.id = m.from_identity
+ORDER  BY m.timestamp DESC",
+            )?
             .query_map([], |row| {
                 Ok((
                     Chat {
@@ -1415,10 +1356,10 @@ impl DbActor {
                         from: row.get(3)?,
                         delivery: row.get(4)?,
                         timestamp: row.get(5)?,
-                        body: Body {
-                            body: row.get(6)?,
-                        },
-                    }
+                        body: Body { body: row.get(6)? },
+                        // TODO: query raw sources.
+                        source: Vec::new(),
+                    },
                 ))
             })?
             .collect::<Result<Vec<_>, _>>()?;
@@ -1432,7 +1373,41 @@ impl DbActor {
     ) -> Result<Vec<((Chat, User), (Message, User))>, Error> {
         let chats = self
             .db
-            .prepare("select c.id as chat_id, c.correspondent as chat_correspondent, c.have_chatted as chat_have_chatted, m.id as message_id, m.body as message_body, m.delivery as message_delivery, m.timestamp as message_timestamp, m.from_jid as message_from_jid, cu.jid as chat_user_jid, cu.nick as chat_user_nick, cu.avatar as chat_user_avatar, mu.jid as message_user_jid, mu.nick as message_user_nick, mu.avatar as message_user_avatar from chats c join (select chat_id, max(timestamp) max_timestamp from messages group by chat_id) max_timestamps on c.id = max_timestamps.chat_id join messages m on max_timestamps.chat_id = m.chat_id and max_timestamps.max_timestamp = m.timestamp join users as cu on cu.jid = c.correspondent join users as mu on mu.jid = m.from_jid order by m.timestamp desc")?
+            .prepare(
+                "
+SELECT c.id            AS chat_id,
+       ci.primary_jid  AS chat_correspondent,
+       c.have_chatted  AS chat_have_chatted,
+       m.id            AS message_id,
+       m.body          AS message_body,
+       m.delivery      AS message_delivery,
+       m.timestamp     AS message_timestamp,
+       ui.primary_jid  AS message_from_jid,
+       cu.jid          AS chat_user_jid,
+       cu.nick         AS chat_user_nick,
+       cu.avatar       AS chat_user_avatar,
+       mu.jid          AS message_user_jid,
+       mu.nick         AS message_user_nick,
+       mu.avatar       AS message_user_avatar
+FROM   chats c
+       JOIN (SELECT chat_id,
+                    Max(timestamp) max_timestamp
+             FROM   messages
+             GROUP  BY chat_id) max_timestamps
+         ON c.id = max_timestamps.chat_id
+       JOIN messages m
+         ON max_timestamps.chat_id = m.chat_id
+            AND max_timestamps.max_timestamp = m.timestamp
+       JOIN identities AS ci
+         ON ci.id = c.correspondent
+       JOIN identities AS ui
+         ON ui.id = m.from_identity
+       JOIN users AS cu
+         ON cu.jid = ci.primary_jid
+       JOIN users AS mu
+         ON mu.jid = ui.primary_jid
+ORDER  BY m.timestamp DESC",
+            )?
             .query_map([], |row| {
                 Ok((
                     (
@@ -1444,7 +1419,7 @@ impl DbActor {
                             jid: row.get("chat_user_jid")?,
                             nick: row.get("chat_user_nick")?,
                             avatar: row.get("chat_user_avatar")?,
-                        }
+                        },
                     ),
                     (
                         Message {
@@ -1455,12 +1430,14 @@ impl DbActor {
                             body: Body {
                                 body: row.get("message_body")?,
                             },
+                            // TODO: query raw sources.
+                            source: Vec::new(),
                         },
                         User {
                             jid: row.get("message_user_jid")?,
                             nick: row.get("message_user_nick")?,
                             avatar: row.get("message_user_avatar")?,
-                        }
+                        },
                     ),
                 ))
             })?
@@ -1471,18 +1448,18 @@ impl DbActor {
     #[tracing::instrument]
     fn read_chat_id(&self, chat: BareJID) -> Result<Uuid, Error> {
         let chat_id = self.db.query_row(
-            "select id from chats where correspondent = ?1",
+            "select id from chats where correspondent = (select id from identities where primary_jid = ?1)",
             [chat],
             |row| Ok(row.get(0)?),
         )?;
         Ok(chat_id)
     }
 
-    fn read_chat_id_opt(&self, chat: JID) -> Result<Option<Uuid>, Error> {
+    fn read_chat_id_opt(&self, chat: BareJID) -> Result<Option<Uuid>, Error> {
         let chat_id = self
             .db
             .query_row(
-                "select id from chats where correspondent = ?1",
+                "select id from chats where correspondent = (select id from identities where primary_jid = ?1)",
                 [chat],
                 |row| Ok(row.get(0)?),
             )
@@ -1491,57 +1468,60 @@ impl DbActor {
     }
 
     /// if the chat doesn't already exist, it must be created by calling create_chat() before running this function.
+    /// create direct message from incoming. MUST upsert user w/ identity, and chat w/identity
     #[tracing::instrument]
     pub(crate) fn create_message(
         &self,
         message: Message,
         chat: BareJID,
-        from: FullJID,
+        from: BareJID,
     ) -> Result<(), Error> {
-        let from_jid = from.as_bare();
-        let chat_id = self.read_chat_id(chat)?;
+        debug!("oomla: 1");
+        let chat_identity = self.upsert_user_identity(&chat)?;
+        debug!("oomla: upserted chat user and got identity {chat_identity}");
+        let (chat_id, _) = self.upsert_chat(chat_identity)?;
+        debug!("oomla: upserted chat and got chat id {chat_id}");
+        let from_identity = self.upsert_user_identity(&from)?;
+        debug!("oomla: upserted from user and got user identity {from_identity}");
         tracing::debug!("creating message");
-        self.db.execute("insert into messages (id, body, chat_id, from_jid, from_resource, timestamp, delivery) values (?1, ?2, ?3, ?4, ?5, ?6, ?7)", (&message.id, &message.body.body, &chat_id, &from_jid, &from.resourcepart, &message.timestamp, &message.delivery))?;
+        self.db.execute("insert into messages (id, body, chat_id, from_identity, timestamp, delivery) values (?1, ?2, ?3, ?4, ?5, ?6)", (&message.id, &message.body.body, &chat_id, &from_identity, &message.timestamp, &message.delivery))?;
         Ok(())
     }
 
-    pub(crate) fn upsert_chat_and_user(&self, chat: &BareJID) -> Result<bool, Error> {
-        let db = &self.db;
-        db.execute(
+    // returns the user identity
+    pub(crate) fn upsert_user_identity(&self, chat: &BareJID) -> Result<Uuid, Error> {
+        self.db.execute(
             "insert into users (jid) values (?1) on conflict do nothing",
             [&chat],
         )?;
-        let id = Uuid::new_v4();
-        db.execute("insert into chats (id, correspondent, have_chatted) values (?1, ?2, ?3) on conflict do nothing", (id, &chat, false))?;
-        let chat = db.query_row(
-            "select correspondent, have_chatted from chats where correspondent = ?1",
+        let identity = Uuid::new_v4();
+        self.db.execute(
+            "insert into identities (id, primary_jid) values (?1, ?2) on conflict do nothing",
+            (identity, &chat),
+        )?;
+        let identity = self.db.query_row(
+            "select id from identities where primary_jid = ?1",
             [&chat],
-            |row| {
-                Ok(Chat {
-                    correspondent: row.get(0)?,
-                    have_chatted: row.get(1)?,
-                })
-            },
+            |row| Ok(row.get(0)?),
         )?;
-        Ok(chat.have_chatted)
+        Ok(identity)
     }
 
-    /// create direct message from incoming. MUST upsert chat and user
-    #[tracing::instrument]
-    pub(crate) fn create_message_with_user_resource(
-        &self,
-        message: Message,
-        chat: BareJID,
-        from: FullJID,
-    ) -> Result<(), Error> {
-        let from_jid = from.as_bare();
-        tracing::debug!("creating resource");
-        self.db.execute(
-            "insert into resources (bare_jid, resource) values (?1, ?2) on conflict do nothing",
-            (&from_jid, &from.resourcepart),
+    pub(crate) fn upsert_chat(&self, identity: Uuid) -> Result<(Uuid, bool), Error> {
+        let chat_id = Uuid::new_v4();
+        self.db.execute("insert into chats (id, correspondent, have_chatted) values (?1, ?2, ?3) on conflict do nothing", (chat_id, &identity, false))?;
+        let (chat_id, have_chatted) = self.db.query_row(
+            "select id, have_chatted from chats where correspondent = ?1",
+            [identity],
+            |row| Ok((row.get(0)?, row.get(1)?)),
         )?;
-        self.create_message(message, chat, from)?;
-        Ok(())
+        Ok((chat_id, have_chatted))
+    }
+
+    pub(crate) fn upsert_chat_and_user(&self, chat: &BareJID) -> Result<bool, Error> {
+        let chat_identity = self.upsert_user_identity(&chat)?;
+        let (_chat_id, have_chatted) = self.upsert_chat(chat_identity)?;
+        Ok(have_chatted)
     }
 
     pub(crate) fn update_message_delivery(
@@ -1576,7 +1556,7 @@ impl DbActor {
 
     pub(crate) fn read_message(&self, message: Uuid) -> Result<Message, Error> {
         let message = self.db.query_row(
-            "select id, from_jid, delivery, timestamp, body from messages where id = ?1",
+            "select id, primary_jid, delivery, timestamp, body from messages join identities on identities.id = messages.from_identity where messages.id = ?1",
             [&message],
             |row| {
                 Ok(Message {
@@ -1586,6 +1566,8 @@ impl DbActor {
                     delivery: row.get(2)?,
                     timestamp: row.get(3)?,
                     body: Body { body: row.get(4)? },
+                    // TODO: query raw sources
+                    source: Vec::new(),
                 })
             },
         )?;
@@ -1598,16 +1580,17 @@ impl DbActor {
         let messages = self
             .db
             .prepare(
-                "select id, from_jid, delivery, timestamp, body from messages where chat_id = ?1",
+                "select id, primary_jid, delivery, timestamp, body from messages join identities on identities.id = messages.from_identity where chat_id = (select id from identities where primary_jid = ?1)",
             )?
             .query_map([chat_id], |row| {
                 Ok(Message {
                     id: row.get(0)?,
-                    // TODO: full from
                     from: row.get(1)?,
                     delivery: row.get(2)?,
                     timestamp: row.get(3)?,
                     body: Body { body: row.get(4)? },
+                    // TODO: query raw sources
+                    source: Vec::new(),
                 })
             })?
             .collect::<Result<Vec<_>, _>>()?;
@@ -1622,7 +1605,7 @@ impl DbActor {
         let messages = self
             .db
             .prepare(
-                "select id, from_jid, delivery, timestamp, body, jid, nick, avatar from messages join users on jid = from_jid where chat_id = ? order by timestamp asc",
+                "select id, primary_jid, delivery, timestamp, body, jid, nick, avatar from messages join users on jid = (select primary_jid from identities where id = from_identity) where chat_id = ? order by timestamp asc",
             )?
             .query_map([chat_id], |row| {
                 Ok((
@@ -1633,6 +1616,8 @@ impl DbActor {
                         delivery: row.get(2)?,
                         timestamp: row.get(3)?,
                         body: Body { body: row.get(4)? },
+                        // TODO: query raw sources
+                        source: Vec::new(),
                     },
                     User {
                         jid: row.get(5)?,