diff options
Diffstat (limited to 'filamento/src/db.rs')
| -rw-r--r-- | filamento/src/db.rs | 281 |
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)?, |