From d28ce2593ddb1e17ade4dabcd11455f36b5f57f4 Mon Sep 17 00:00:00 2001 From: Mroik Date: Fri, 3 Apr 2026 07:13:38 +0200 Subject: Add subscription table interaction We need to track which user subscribed to which list. Unlike User and List, we don't need to make a Subscription model, this is because this is a relationship and not an entity of its own. Implement database interaction with subscription. Signed-off-by: Mroik --- src/database.rs | 1 + src/list.rs | 409 ++++++++++++++++++++++++++++++++++++++++++++++++++++++-- 2 files changed, 399 insertions(+), 11 deletions(-) (limited to 'src') diff --git a/src/database.rs b/src/database.rs index de77f95..10a5843 100644 --- a/src/database.rs +++ b/src/database.rs @@ -80,5 +80,6 @@ pub trait DBExecutable { #[derive(Debug)] pub enum QueryResult { Empty, + Single(T), Vec(Vec), } diff --git a/src/list.rs b/src/list.rs index a015da5..1a20093 100644 --- a/src/list.rs +++ b/src/list.rs @@ -33,6 +33,18 @@ impl User { fn query_all<'a>() -> UserQuery<'a> { UserQuery::QueryAll } + + fn subscribe<'a>(&'a self, list: &'a List) -> SubscriptionQuery<'a> { + SubscriptionQuery::Insert(&self.email, &list.name) + } + + fn unsubscribe<'a>(&'a self, list: &'a List) -> SubscriptionQuery<'a> { + SubscriptionQuery::Delete(&self.email, &list.name) + } + + fn subscribed_to(&self) -> SubscriptionQuery<'_> { + SubscriptionQuery::Lists(&self.email) + } } enum UserQuery<'a> { @@ -124,29 +136,33 @@ impl UserQuery<'_> { #[derive(Debug, PartialEq)] struct List { - address: String, + name: String, desc: Option, } impl List { fn new(name: &str) -> Self { List { - address: String::from(name), + name: String::from(name), desc: None, } } fn insert(&self) -> ListQuery<'_> { - ListQuery::Insert(&self.address, self.desc.as_deref()) + ListQuery::Insert(&self.name, self.desc.as_deref()) } fn delete(&self) -> ListQuery<'_> { - ListQuery::Delete(&self.address) + ListQuery::Delete(&self.name) } fn query_all<'a>() -> ListQuery<'a> { ListQuery::QueryAll } + + fn subscribers(&self) -> SubscriptionQuery<'_> { + SubscriptionQuery::Subscribers(&self.name) + } } enum ListQuery<'a> { @@ -212,7 +228,7 @@ impl ListQuery<'_> { .query([name])? .map(|row| { Ok(List { - address: row.get(1).unwrap(), + name: row.get(1).unwrap(), desc: row.get(2).unwrap(), }) }) @@ -227,7 +243,7 @@ impl ListQuery<'_> { .query(())? .map(|row| { Ok(List { - address: row.get(1).unwrap(), + name: row.get(1).unwrap(), desc: row.get(2).unwrap(), }) }) @@ -236,13 +252,139 @@ impl ListQuery<'_> { } } +enum SubscriptionResult { + List(List), + Subscriber(User), + Count(i64), +} + +enum SubscriptionQuery<'a> { + Insert(&'a str, &'a str), + Delete(&'a str, &'a str), + /// This is just for debug + Count, + Lists(&'a str), + Subscribers(&'a str), +} + +impl DBExecutable for SubscriptionQuery<'_> { + type T = SubscriptionResult; + + fn execute(&self, tx: &rusqlite::Transaction) -> Result> { + match self { + SubscriptionQuery::Insert(_, _) => self.db_insert(tx), + SubscriptionQuery::Count => self.db_query_all(tx), + SubscriptionQuery::Delete(_, _) => self.db_delete(tx), + SubscriptionQuery::Lists(_) => self.db_lists(tx), + SubscriptionQuery::Subscribers(_) => self.db_subscribers(tx), + } + } +} + +impl SubscriptionQuery<'_> { + fn db_insert(&self, tx: &rusqlite::Transaction) -> Result> { + let (email, list_name) = if let SubscriptionQuery::Insert(a, b) = self { + (a, b) + } else { + unreachable!("this should only be called by SubscriptionQuery::Insert") + }; + + let q = "INSERT INTO subscription + SELECT user.id, list.id FROM user CROSS JOIN list + WHERE user.email = ? AND list.name = ?"; + tx.execute(q, [email, list_name])?; + + Ok(QueryResult::Empty) + } + + fn db_query_all(&self, tx: &rusqlite::Transaction) -> Result> { + match self { + SubscriptionQuery::Count => (), + _ => unreachable!("this should only be called by SubscriptionQuery::Count"), + }; + + let q = "SELECT COUNT(*) FROM subscription"; + let ris = tx.query_one(q, (), |row| Ok(row.get(0).unwrap()))?; + Ok(QueryResult::Single(SubscriptionResult::Count(ris))) + } + + fn db_delete(&self, tx: &rusqlite::Transaction) -> Result> { + let (email, name) = if let SubscriptionQuery::Delete(e, n) = self { + (e, n) + } else { + unreachable!("this should only be called by SubscriptionQuery::Delete") + }; + + let q = "WITH helper AS (SELECT user.id AS user_id, list.id AS list_id FROM user CROSS JOIN list + WHERE user.email = ? AND list.name = ?) + DELETE FROM subscription WHERE EXISTS ( + SELECT 1 FROM helper + WHERE helper.user_id = subscription.user_id AND helper.list_id = subscription.list_id)"; + tx.execute(q, [email, name])?; + Ok(QueryResult::Empty) + } + + fn db_lists(&self, tx: &rusqlite::Transaction) -> Result> { + let email = if let SubscriptionQuery::Lists(email) = self { + email + } else { + unreachable!("this should only be called by SubscriptionQuery::Lists") + }; + + let q = "SELECT list.name, list.description + FROM subscription JOIN list ON list.id = subscription.list_id + JOIN user ON user.id = subscription.user_id + WHERE user.email = ?"; + let ris = tx + .prepare(q)? + .query([email])? + .map(|row| { + Ok(SubscriptionResult::List(List { + name: row.get(0).unwrap(), + desc: row.get(1).unwrap(), + })) + }) + .collect()?; + + Ok(QueryResult::Vec(ris)) + } + + fn db_subscribers( + &self, + tx: &rusqlite::Transaction, + ) -> Result> { + let name = if let SubscriptionQuery::Subscribers(name) = self { + name + } else { + unreachable!("this should only be called by SubscriptionQuery::Subscribers") + }; + + let q = "SELECT user.name, user.email + FROM subscription JOIN list ON list.id = subscription.list_id + JOIN user ON user.id = subscription.user_id + WHERE list.name = ?"; + let ris = tx + .prepare(q)? + .query([name])? + .map(|row| { + Ok(SubscriptionResult::Subscriber(User { + name: row.get(0).unwrap(), + email: row.get(1).unwrap(), + })) + }) + .collect()?; + + Ok(QueryResult::Vec(ris)) + } +} + #[cfg(test)] mod tests { use anyhow::Result; use crate::{ database::{DB_NAME, Database}, - list::{List, User}, + list::{List, SubscriptionQuery, User}, }; fn setup() -> Result { @@ -273,6 +415,7 @@ mod tests { assert_eq!(items.len(), 1); assert_eq!(items[0], user); } + crate::database::QueryResult::Single(_) => assert!(false), } drop(database); @@ -297,6 +440,7 @@ mod tests { assert_eq!(items.len(), 1); assert_eq!(items[0], user); } + crate::database::QueryResult::Single(_) => assert!(false), } drop(database); @@ -336,6 +480,7 @@ mod tests { match ris { crate::database::QueryResult::Empty => assert!(false), crate::database::QueryResult::Vec(items) => assert!(items.is_empty()), + crate::database::QueryResult::Single(_) => assert!(false), } drop(database); @@ -347,7 +492,7 @@ mod tests { let mut database = setup().expect("failed setup"); let list = List { - address: String::from("poul"), + name: String::from("poul"), desc: None, }; database.execute(list.insert()).expect("failed insert"); @@ -358,6 +503,7 @@ mod tests { assert_eq!(items.len(), 1); assert_eq!(items[0], list); } + crate::database::QueryResult::Single(_) => assert!(false), } cleanup().expect("failed cleanup"); @@ -368,7 +514,7 @@ mod tests { let mut database = setup().expect("failed setup"); let list = List { - address: String::from("poul"), + name: String::from("poul"), desc: Some(String::from("The mailing list of the POuL")), }; database.execute(list.insert()).expect("failed insert"); @@ -379,6 +525,7 @@ mod tests { assert_eq!(items.len(), 1); assert_eq!(items[0], list); } + crate::database::QueryResult::Single(_) => assert!(false), } drop(database); @@ -390,7 +537,7 @@ mod tests { let mut database = setup().expect("failed setup"); let list = List { - address: String::from("poul"), + name: String::from("poul"), desc: Some(String::from("The mailing list of the POuL")), }; database.execute(list.insert()).expect("failed insert"); @@ -405,7 +552,7 @@ mod tests { let mut database = setup().expect("failed setup"); let list = List { - address: String::from("poul"), + name: String::from("poul"), desc: Some(String::from("The mailing list of the POuL")), }; database.execute(list.insert()).expect("failed insert"); @@ -417,6 +564,246 @@ mod tests { match ris { crate::database::QueryResult::Empty => assert!(false), crate::database::QueryResult::Vec(items) => assert!(items.is_empty()), + crate::database::QueryResult::Single(_) => assert!(false), + } + + drop(database); + cleanup().expect("failed cleanup"); + } + + #[test] + fn subscription_insert() { + let mut database = setup().expect("failed setup"); + + let list = List { + name: String::from("poul"), + desc: None, + }; + let list2 = List { + name: String::from("dev"), + desc: None, + }; + + database.execute(list.insert()).expect("failed insert"); + database.execute(list2.insert()).expect("failed insert"); + + let user = User { + name: None, + email: String::from("mroik@delayed.space"), + }; + let user2 = User { + name: None, + email: String::from("mroik@poul.org"), + }; + + database.execute(user.insert()).expect("failed insert"); + database.execute(user2.insert()).expect("failed insert"); + + database + .execute(user.subscribe(&list)) + .expect("failed insert"); + + let ris = database + .execute(SubscriptionQuery::Count) + .expect("failed query"); + match ris { + crate::database::QueryResult::Single(crate::list::SubscriptionResult::Count(c)) => { + assert_eq!(c, 1) + } + _ => assert!(false), + } + + drop(database); + cleanup().expect("failed cleanup"); + } + + #[test] + fn subscription_delete() { + let mut database = setup().expect("failed setup"); + + let list = List { + name: String::from("poul"), + desc: None, + }; + let list2 = List { + name: String::from("dev"), + desc: None, + }; + + database.execute(list.insert()).expect("failed insert"); + database.execute(list2.insert()).expect("failed insert"); + + let user = User { + name: None, + email: String::from("mroik@delayed.space"), + }; + let user2 = User { + name: None, + email: String::from("mroik@poul.org"), + }; + + database.execute(user.insert()).expect("failed insert"); + database.execute(user2.insert()).expect("failed insert"); + + database + .execute(user.subscribe(&list)) + .expect("failed insert"); + database + .execute(user.subscribe(&list2)) + .expect("failed insert"); + database + .execute(user2.subscribe(&list)) + .expect("failed insert"); + database + .execute(user2.subscribe(&list2)) + .expect("failed insert"); + + let mut ris = database + .execute(SubscriptionQuery::Count) + .expect("failed query"); + match ris { + crate::database::QueryResult::Single(crate::list::SubscriptionResult::Count(c)) => { + assert_eq!(c, 4) + } + _ => assert!(false), + } + + database + .execute(user.unsubscribe(&list)) + .expect("failed delete"); + + ris = database + .execute(SubscriptionQuery::Count) + .expect("failed query"); + match ris { + crate::database::QueryResult::Single(crate::list::SubscriptionResult::Count(c)) => { + assert_eq!(c, 3) + } + _ => assert!(false), + } + + drop(database); + cleanup().expect("failed cleanup"); + } + + #[test] + fn subscription_lists() { + let mut database = setup().expect("failed setup"); + + let list = List { + name: String::from("poul"), + desc: None, + }; + let list2 = List { + name: String::from("dev"), + desc: None, + }; + + database.execute(list.insert()).expect("failed insert"); + database.execute(list2.insert()).expect("failed insert"); + + let user = User { + name: None, + email: String::from("mroik@delayed.space"), + }; + let user2 = User { + name: None, + email: String::from("mroik@poul.org"), + }; + + database.execute(user.insert()).expect("failed insert"); + database.execute(user2.insert()).expect("failed insert"); + + database + .execute(user.subscribe(&list)) + .expect("failed insert"); + database + .execute(user.subscribe(&list2)) + .expect("failed insert"); + database + .execute(user2.subscribe(&list)) + .expect("failed insert"); + database + .execute(user2.subscribe(&list2)) + .expect("failed insert"); + + let mut ris = database + .execute(SubscriptionQuery::Count) + .expect("failed query"); + match ris { + crate::database::QueryResult::Single(crate::list::SubscriptionResult::Count(c)) => { + assert_eq!(c, 4) + } + _ => assert!(false), + } + + ris = database + .execute(user.subscribed_to()) + .expect("failed query"); + match ris { + crate::database::QueryResult::Vec(items) => assert_eq!(items.len(), 2), + _ => assert!(false), + } + + drop(database); + cleanup().expect("failed cleanup"); + } + + #[test] + fn subscription_subscribers() { + let mut database = setup().expect("failed setup"); + + let list = List { + name: String::from("poul"), + desc: None, + }; + let list2 = List { + name: String::from("dev"), + desc: None, + }; + + database.execute(list.insert()).expect("failed insert"); + database.execute(list2.insert()).expect("failed insert"); + + let user = User { + name: None, + email: String::from("mroik@delayed.space"), + }; + let user2 = User { + name: None, + email: String::from("mroik@poul.org"), + }; + + database.execute(user.insert()).expect("failed insert"); + database.execute(user2.insert()).expect("failed insert"); + + database + .execute(user.subscribe(&list)) + .expect("failed insert"); + database + .execute(user.subscribe(&list2)) + .expect("failed insert"); + database + .execute(user2.subscribe(&list)) + .expect("failed insert"); + database + .execute(user2.subscribe(&list2)) + .expect("failed insert"); + + let mut ris = database + .execute(SubscriptionQuery::Count) + .expect("failed query"); + match ris { + crate::database::QueryResult::Single(crate::list::SubscriptionResult::Count(c)) => { + assert_eq!(c, 4) + } + _ => assert!(false), + } + + ris = database.execute(list.subscribers()).expect("failed query"); + match ris { + crate::database::QueryResult::Vec(items) => assert_eq!(items.len(), 2), + _ => assert!(false), } drop(database); -- cgit v1.3