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 <mroik@delayed.space>

2 files changed, 399 insertions, 11 deletions

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<T> {
     Empty,
+    Single(T),
     Vec(Vec<T>),
 }
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<String>,
 }
 
 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<QueryResult<Self::T>> {
+        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<QueryResult<SubscriptionResult>> {
+        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<QueryResult<SubscriptionResult>> {
+        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<QueryResult<SubscriptionResult>> {
+        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<QueryResult<SubscriptionResult>> {
+        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<QueryResult<SubscriptionResult>> {
+        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<Database> {
@@ -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);