X-Git-Url: http://git.euphorik.ch/?a=blobdiff_plain;f=backend%2Fsrc%2Fdb.rs;h=4ef37fe26553dd4ae8cca8e2dc8c55a63a2af273;hb=5e4e0862477f46a6ea477a56f01fd84e720a9546;hp=ac97f1b14639db17377415e1e736c773810a4788;hpb=a080d19cb9076780db9e86325b2ab617886e2a5f;p=recipes.git

diff --git a/backend/src/db.rs b/backend/src/db.rs
index ac97f1b..4ef37fe 100644
--- a/backend/src/db.rs
+++ b/backend/src/db.rs
@@ -1,86 +1,112 @@
-use std::path::Path;
-use std::fs;
+use std::{fmt::Display, fs::{self, File}, path::Path, io::Read};
 
-//use rusqlite::types::ToSql;
-//use rusqlite::{Connection, Result, NO_PARAMS};
+use itertools::Itertools;
+use chrono::{prelude::*, Duration};
+use rusqlite::{params, Params, OptionalExtension};
+use r2d2::Pool;
+use r2d2_sqlite::SqliteConnectionManager;
+use rand::distributions::{Alphanumeric, DistString};
 
-//extern crate r2d2;
-//extern crate r2d2_sqlite;
-//extern crate rusqlite;
+use crate::consts;
+use crate::hash::hash;
+use crate::model;
 
-use r2d2_sqlite::SqliteConnectionManager;
-use r2d2::Pool;
+const CURRENT_DB_VERSION: u32 = 1;
 
 #[derive(Debug)]
-pub enum DbError {
+pub enum DBError {
     SqliteError(rusqlite::Error),
     R2d2Error(r2d2::Error),
-    UnsupportedVersion(i32),
+    UnsupportedVersion(u32),
+    Other(String),
 }
 
-use super::consts;
+impl From<rusqlite::Error> for DBError  {
+    fn from(error: rusqlite::Error) -> Self {
+        DBError::SqliteError(error)
+    }
+}
 
-const CURRENT_DB_VERSION: u32 = 1;
+impl From<r2d2::Error> for DBError  {
+    fn from(error: r2d2::Error) -> Self {
+        DBError::R2d2Error(error)
+    }
+}
 
-pub struct Connection {
-    //con: rusqlite::Connection
-    pool: Pool<SqliteConnectionManager>
+// TODO: Is there a better solution?
+impl DBError {
+    fn from_dyn_error(error: Box<dyn std::error::Error>) -> Self {
+        DBError::Other(error.to_string())
+    }
 }
 
-pub struct Recipe {
-    pub title: String,
-    pub id: i32,
+type Result<T> = std::result::Result<T, DBError>;
+
+#[derive(Debug)]
+pub enum SignUpResult {
+    UserAlreadyExists,
+    UserCreatedWaitingForValidation(String), // Validation token.
 }
 
-impl std::convert::From<rusqlite::Error> for DbError  {
-    fn from(error: rusqlite::Error) -> Self {
-        DbError::SqliteError(error)
-    }
+#[derive(Debug)]
+pub enum ValidationResult {
+    ValidationExpired,
+    OK,
 }
 
-impl std::convert::From<r2d2::Error> for DbError  {
-    fn from(error: r2d2::Error) -> Self {
-        DbError::R2d2Error(error)
-    }
+#[derive(Debug)]
+pub enum SignInResult {
+    NotValidToken,
+    OK,
+}
+
+#[derive(Debug)]
+pub enum AuthenticationResult {
+    NotValidToken,
+    OK,
+}
+
+#[derive(Clone)]
+pub struct Connection {
+    //con: rusqlite::Connection
+    pool: Pool<SqliteConnectionManager>
 }
 
 impl Connection {
-    pub fn new() -> Result<Connection, DbError> {
+    pub fn new() -> Result<Connection> {
+        let path = Path::new(consts::DB_DIRECTORY).join(consts::DB_FILENAME);
+        Self::new_from_file(path)
+    }
 
-        let data_dir = Path::new(consts::DB_DIRECTORY);
+    pub fn new_in_memory() -> Result<Connection> {
+        Self::create_connection(SqliteConnectionManager::memory())
+    }
 
-        if !data_dir.exists() {
-            fs::DirBuilder::new().create(data_dir).unwrap();
+    pub fn new_from_file<P: AsRef<Path>>(file: P) -> Result<Connection> {
+        if let Some(data_dir) = file.as_ref().parent() {
+            if !data_dir.exists() {
+                fs::DirBuilder::new().create(data_dir).unwrap();
+            }
         }
 
-        let manager = SqliteConnectionManager::file("file.db");
-        let pool = r2d2::Pool::new(manager).unwrap();
-
-        let connection = Connection { pool };
-        connection.create_or_update()?;
-        Ok(connection)
+        Self::create_connection(SqliteConnectionManager::file(file))
     }
 
-    /*
-     * Called after the connection has been established for creating or updating the database.
-     * The 'Version' table tracks the current state of the database.
-     */
-    fn create_or_update(self: &Self) -> Result<(), DbError> {
-        // let connection = Connection::new();
-        // let mut stmt = connection.sqlite_con.prepare("SELECT * FROM versions ORDER BY date").unwrap();
-        // let mut stmt = connection.sqlite_con.prepare("SELECT name FROM sqlite_master WHERE type='table' AND name='versions'").unwrap();
-
+    /// Called after the connection has been established for creating or updating the database.
+    /// The 'Version' table tracks the current state of the database.
+    fn create_or_update(&self) -> Result<()> {
         // Check the Database version.
         let mut con = self.pool.get()?;
         let tx = con.transaction()?;
 
+        // Version 0 corresponds to an empty database.
         let mut version = {
             match tx.query_row(
                     "SELECT [name] FROM [sqlite_master] WHERE [type] = 'table' AND [name] = 'Version'",
-                        rusqlite::NO_PARAMS,
+                        [],
                         |row| row.get::<usize, String>(0)
                     ) {
-                Ok(_) => tx.query_row("SELECT [version] FROM [Version]", rusqlite::NO_PARAMS, |row| row.get(0)).unwrap_or_default(),
+                Ok(_) => tx.query_row("SELECT [version] FROM [Version] ORDER BY [id] DESC", [], |row| row.get(0)).unwrap_or_default(),
                 Err(_) => 0
             }
         };
@@ -94,49 +120,197 @@ impl Connection {
         Ok(())
     }
 
-    fn update_to_next_version(version: i32, tx: &rusqlite::Transaction) -> Result<bool, DbError> {
-        match version {
-            0 => {
-                println!("Update to version 1...");
-
-                // Initial structure.
-                tx.execute_batch(
-                    "
-                    CREATE TABLE [Version] (
-                        [id] INTEGER PRIMARY KEY,
-                        [version] INTEGER NOT NULL UNIQUE,
-                        [datetime] INTEGER DATETIME
-                    );
-
-                    CREATE TABLE [Recipe] (
-                        [id] INTEGER PRIMARY KEY,
-                        [title] INTEGER NOT NULL,
-                        [description] INTEGER DATETIME
-                    );
-                    "
-                )?;
-
-                /*
-                tx.execute(
-                    "
-                    INSERT INTO Version
-                    ",
-                    rusqlite::NO_PARAMS
-                );*/
-
-                Ok(true)
+    fn create_connection(manager: SqliteConnectionManager) -> Result<Connection> {;
+        let pool = r2d2::Pool::new(manager).unwrap();
+        let connection = Connection { pool };
+        connection.create_or_update()?;
+        Ok(connection)
+    }
+
+    fn update_to_next_version(current_version: u32, tx: &rusqlite::Transaction) -> Result<bool> {
+        let next_version = current_version + 1;
+
+        if next_version <= CURRENT_DB_VERSION {
+            println!("Update to version {}...", next_version);
+        }
+
+        fn update_version(to_version: u32, tx: &rusqlite::Transaction) -> Result<()> {
+            tx.execute("INSERT INTO [Version] ([version], [datetime]) VALUES (?1, datetime('now'))", [to_version]).map(|_| ()).map_err(DBError::from)
+        }
+
+        fn ok(updated: bool) -> Result<bool> {
+            if updated {
+                println!("Version updated");
             }
+            Ok(updated)
+        }
 
-            // Current version.
-            1 =>
-                Ok(false),
+        match next_version {
+            1 => {
+                let sql_file = consts::SQL_FILENAME.replace("{VERSION}", &next_version.to_string());
+                tx.execute_batch(&load_sql_file(&sql_file)?)?;
+                update_version(next_version, tx)?;
+
+                ok(true)
+            }
+
+            // Version 1 doesn't exist yet.
+            2 =>
+                ok(false),
 
             v =>
-                Err(DbError::UnsupportedVersion(v)),
+                Err(DBError::UnsupportedVersion(v)),
         }
     }
 
-    pub fn get_all_recipes() {
+    pub fn get_all_recipe_titles(&self) -> Result<Vec<(i32, String)>> {
+        let con = self.pool.get()?;
+        let mut stmt = con.prepare("SELECT [id], [title] FROM [Recipe] ORDER BY [title]")?;
+        let titles =
+            stmt.query_map([], |row| {
+                Ok((row.get(0)?, row.get(1)?))
+            })?.map(|r| r.unwrap()).collect_vec(); // TODO: remove unwrap.
+        Ok(titles)
+    }
+
+    /* Not used for the moment.
+    pub fn get_all_recipes(&self) -> Result<Vec<model::Recipe>> {
+        let con = self.pool.get()?;
+        let mut stmt = con.prepare("SELECT [id], [title] FROM [Recipe] ORDER BY [title]")?;
+        let recipes =
+            stmt.query_map([], |row| {
+                Ok(model::Recipe::new(row.get(0)?, row.get(1)?))
+            })?.map(|r| r.unwrap()).collect_vec(); // TODO: remove unwrap.
+        Ok(recipes)
+    } */
+
+    pub fn get_recipe(&self, id: i32) -> Result<model::Recipe> {
+        let con = self.pool.get()?;
+        con.query_row("SELECT [id], [title] FROM [Recipe] WHERE [id] = ?1", [id], |row| {
+            Ok(model::Recipe::new(row.get(0)?, row.get(1)?))
+        }).map_err(DBError::from)
+    }
+
+    ///
+    pub fn sign_up(&self, password: &str, email: &str) -> Result<SignUpResult> {
+        self.sign_up_with_given_time(password, email, Utc::now())
+    }
+
+    fn sign_up_with_given_time(&self, password: &str, email: &str, datetime: DateTime<Utc>) -> Result<SignUpResult> {
+        let mut con = self.pool.get()?;
+        let tx = con.transaction()?;
+        let token =
+            match tx.query_row("SELECT [id], [validation_token] FROM [User] WHERE [email] = ?1", [email], |r| {
+                Ok((r.get::<&str, i32>("id")?, r.get::<&str, Option<String>>("validation_token")?))
+            }).optional()? {
+                Some((id, validation_token)) => {
+                    if validation_token.is_none() {
+                        return Ok(SignUpResult::UserAlreadyExists)
+                    }
+                    let token = generate_token();
+                    let hashed_password = hash(password).map_err(|e| DBError::from_dyn_error(e))?;
+                    tx.execute("UPDATE [User] SET [validation_token] = ?2, [creation_datetime] = ?3, [password] = ?4 WHERE [id] = ?1", params![id, token, datetime, hashed_password])?;
+                    token
+                },
+                None => {
+                    let token = generate_token();
+                    let hashed_password = hash(password).map_err(|e| DBError::from_dyn_error(e))?;
+                    tx.execute("INSERT INTO [User] ([email], [validation_token], [creation_datetime], [password]) VALUES (?1, ?2, ?3, ?4)", params![email, token, datetime, hashed_password])?;
+                    token
+                },
+            };
+        tx.commit()?;
+        Ok(SignUpResult::UserCreatedWaitingForValidation(token))
+    }
+
+    pub fn validation(&self, token: &str, validation_time: Duration) -> Result<ValidationResult> {
+        todo!()
+    }
+
+    pub fn sign_in(&self, password: &str, email: String) -> Result<SignInResult> {
+        todo!()
+    }
+
+    pub fn authentication(&self, token: &str) -> Result<AuthenticationResult> {
+        todo!()
+    }
+
+    pub fn logout(&self, token: &str) -> Result<()> {
+        todo!()
+    }
 
+    /// Execute a given SQL file.
+    pub fn execute_file<P: AsRef<Path> + Display>(&self, file: P) -> Result<()> {
+        let con = self.pool.get()?;
+        let sql = load_sql_file(file)?;
+        con.execute_batch(&sql).map_err(DBError::from)
     }
-}
\ No newline at end of file
+
+    /// Execute any SQL statement.
+    /// Mainly used for testing.
+    pub fn execute_sql<P: Params>(&self, sql: &str, params: P) -> Result<usize> {
+        let con = self.pool.get()?;
+        con.execute(sql, params).map_err(DBError::from)
+    }
+}
+
+fn load_sql_file<P: AsRef<Path> + Display>(sql_file: P) -> Result<String> {
+    let mut file = File::open(&sql_file).map_err(|err| DBError::Other(format!("Cannot open SQL file ({}): {}", &sql_file, err.to_string())))?;
+    let mut sql = String::new();
+    file.read_to_string(&mut sql).map_err(|err| DBError::Other(format!("Cannot read SQL file ({}) : {}", &sql_file, err.to_string())))?;
+    Ok(sql)
+}
+
+fn generate_token() -> String {
+    Alphanumeric.sample_string(&mut rand::thread_rng(), 24)
+}
+
+#[cfg(test)]
+mod tests {
+    use super::*;
+
+    #[test]
+    fn sign_up() -> Result<()> {
+        let connection = Connection::new_in_memory()?;
+        match connection.sign_up("12345", "paul@test.org")? {
+            SignUpResult::UserCreatedWaitingForValidation(_) => (), // Nominal case.
+            other => panic!("{:?}", other),
+        }
+        Ok(())
+    }
+
+    #[test]
+    fn sign_up_to_an_already_existing_user() -> Result<()> {
+        let connection = Connection::new_in_memory()?;
+        connection.execute_sql("
+            INSERT INTO [User] ([id], [email], [name], [password], [creation_datetime], [validation_token])
+                VALUES (
+                    1,
+                    'paul@test.org',
+                    'paul',
+                    '$argon2id$v=19$m=4096,t=3,p=1$1vtXcacYjUHZxMrN6b2Xng$wW8Z59MIoMcsIljnjHmxn3EBcc5ymEySZPUVXHlRxcY',
+                    0,
+                    NULL
+                );", [])?;
+        match connection.sign_up("12345", "paul@test.org")? {
+            SignUpResult::UserAlreadyExists => (), // Nominal case.
+            other => panic!("{:?}", other),
+        }
+        Ok(())
+    }
+
+    #[test]
+    fn sign_up_to_an_unvalidated_already_existing_user() -> Result<()> {
+        todo!()
+    }
+
+    fn sign_up_then_send_validation_at_time() -> Result<()> {
+        todo!()
+    }
+
+    fn sign_up_then_send_validation_too_late() -> Result<()> {
+        todo!()
+    }
+
+    //fn sign_up_then_send_validation_then_sign_in()
+}