backend/src/data/db.rs

   1 use std::{
   2     fmt,
   3     fs::{self, File},
   4     io::Read,
   5     path::Path,
   6 };
   7
   8 use chrono::{prelude::*, Duration};
   9 use itertools::Itertools;
  10 use r2d2::{Pool, PooledConnection};
  11 use r2d2_sqlite::SqliteConnectionManager;
  12 use rand::distributions::{Alphanumeric, DistString};
  13 use rusqlite::{named_params, params, OptionalExtension, Params};
  14
  15 use crate::{
  16     consts,
  17     hash::{hash, verify_password},
  18     model,
  19 };
  20
  21 const CURRENT_DB_VERSION: u32 = 1;
  22
  23 #[derive(Debug)]
  24 pub enum DBError {
  25     SqliteError(rusqlite::Error),
  26     R2d2Error(r2d2::Error),
  27     UnsupportedVersion(u32),
  28     Other(String),
  29 }
  30
  31 impl fmt::Display for DBError {
  32     fn fmt(&self, f: &mut fmt::Formatter) -> std::result::Result<(), fmt::Error> {
  33         write!(f, "{:?}", self)
  34     }
  35 }
  36
  37 impl std::error::Error for DBError {}
  38
  39 impl From<rusqlite::Error> for DBError {
  40     fn from(error: rusqlite::Error) -> Self {
  41         DBError::SqliteError(error)
  42     }
  43 }
  44
  45 impl From<r2d2::Error> for DBError {
  46     fn from(error: r2d2::Error) -> Self {
  47         DBError::R2d2Error(error)
  48     }
  49 }
  50
  51 impl DBError {
  52     fn from_dyn_error(error: Box<dyn std::error::Error>) -> Self {
  53         DBError::Other(error.to_string())
  54     }
  55 }
  56
  57 type Result<T> = std::result::Result<T, DBError>;
  58
  59 #[derive(Debug)]
  60 pub enum SignUpResult {
  61     UserAlreadyExists,
  62     UserCreatedWaitingForValidation(String), // Validation token.
  63 }
  64
  65 #[derive(Debug)]
  66 pub enum ValidationResult {
  67     UnknownUser,
  68     ValidationExpired,
  69     Ok(String, i64), // Returns token and user id.
  70 }
  71
  72 #[derive(Debug)]
  73 pub enum SignInResult {
  74     UserNotFound,
  75     WrongPassword,
  76     AccountNotValidated,
  77     Ok(String, i64), // Returns token and user id.
  78 }
  79
  80 #[derive(Debug)]
  81 pub enum AuthenticationResult {
  82     NotValidToken,
  83     Ok(i64), // Returns user id.
  84 }
  85
  86 #[derive(Clone)]
  87 pub struct Connection {
  88     pool: Pool<SqliteConnectionManager>,
  89 }
  90
  91 impl Connection {
  92     pub fn new() -> Result<Connection> {
  93         let path = Path::new(consts::DB_DIRECTORY).join(consts::DB_FILENAME);
  94         Self::new_from_file(path)
  95     }
  96
  97     pub fn new_in_memory() -> Result<Connection> {
  98         Self::create_connection(SqliteConnectionManager::memory())
  99     }
 100
 101     pub fn new_from_file<P: AsRef<Path>>(file: P) -> Result<Connection> {
 102         if let Some(data_dir) = file.as_ref().parent() {
 103             if !data_dir.exists() {
 104                 fs::DirBuilder::new().create(data_dir).unwrap();
 105             }
 106         }
 107
 108         Self::create_connection(SqliteConnectionManager::file(file))
 109     }
 110
 111     fn create_connection(manager: SqliteConnectionManager) -> Result<Connection> {
 112         let pool = r2d2::Pool::new(manager).unwrap();
 113         let connection = Connection { pool };
 114         connection.create_or_update_db()?;
 115         Ok(connection)
 116     }
 117
 118     fn get(&self) -> Result<PooledConnection<SqliteConnectionManager>> {
 119         let con = self.pool.get()?;
 120         con.pragma_update(None, "synchronous", "NORMAL")?;
 121         Ok(con)
 122     }
 123
 124     /// Called after the connection has been established for creating or updating the database.
 125     /// The 'Version' table tracks the current state of the database.
 126     fn create_or_update_db(&self) -> Result<()> {
 127         // Check the Database version.
 128         let mut con = self.get()?;
 129         con.pragma_update(None, "journal_mode", "WAL")?;
 130
 131         let tx = con.transaction()?;
 132
 133         // Version 0 corresponds to an empty database.
 134         let mut version = {
 135             match tx.query_row(
 136                 "SELECT [name] FROM [sqlite_master] WHERE [type] = 'table' AND [name] = 'Version'",
 137                 [],
 138                 |row| row.get::<usize, String>(0),
 139             ) {
 140                 Ok(_) => tx
 141                     .query_row(
 142                         "SELECT [version] FROM [Version] ORDER BY [id] DESC",
 143                         [],
 144                         |row| row.get(0),
 145                     )
 146                     .unwrap_or_default(),
 147                 Err(_) => 0,
 148             }
 149         };
 150
 151         while Self::update_to_next_version(version, &tx)? {
 152             version += 1;
 153         }
 154
 155         tx.commit()?;
 156
 157         Ok(())
 158     }
 159
 160     fn update_to_next_version(current_version: u32, tx: &rusqlite::Transaction) -> Result<bool> {
 161         let next_version = current_version + 1;
 162
 163         if next_version <= CURRENT_DB_VERSION {
 164             println!("Update to version {}...", next_version);
 165         }
 166
 167         fn update_version(to_version: u32, tx: &rusqlite::Transaction) -> Result<()> {
 168             tx.execute(
 169                 "INSERT INTO [Version] ([version], [datetime]) VALUES (?1, datetime('now'))",
 170                 [to_version],
 171             )
 172             .map(|_| ())
 173             .map_err(DBError::from)
 174         }
 175
 176         fn ok(updated: bool) -> Result<bool> {
 177             if updated {
 178                 println!("Version updated");
 179             }
 180             Ok(updated)
 181         }
 182
 183         match next_version {
 184             1 => {
 185                 let sql_file = consts::SQL_FILENAME.replace("{VERSION}", &next_version.to_string());
 186                 tx.execute_batch(&load_sql_file(&sql_file)?)?;
 187                 update_version(next_version, tx)?;
 188
 189                 ok(true)
 190             }
 191
 192             // Version 1 doesn't exist yet.
 193             2 => ok(false),
 194
 195             v => Err(DBError::UnsupportedVersion(v)),
 196         }
 197     }
 198
 199     pub fn get_all_recipe_titles(&self) -> Result<Vec<(i64, String)>> {
 200         let con = self.get()?;
 201
 202         let mut stmt = con.prepare("SELECT [id], [title] FROM [Recipe] ORDER BY [title]")?;
 203
 204         let titles: std::result::Result<Vec<(i64, String)>, rusqlite::Error> = stmt
 205             .query_map([], |row| Ok((row.get("id")?, row.get("title")?)))?
 206             .collect();
 207
 208         titles.map_err(DBError::from)
 209     }
 210
 211     /* Not used for the moment.
 212     pub fn get_all_recipes(&self) -> Result<Vec<model::Recipe>> {
 213         let con = self.get()?;
 214         let mut stmt = con.prepare("SELECT [id], [title] FROM [Recipe] ORDER BY [title]")?;
 215         let recipes =
 216             stmt.query_map([], |row| {
 217                 Ok(model::Recipe::new(row.get(0)?, row.get(1)?))
 218             })?.map(|r| r.unwrap()).collect_vec(); // TODO: remove unwrap.
 219         Ok(recipes)
 220     } */
 221
 222     pub fn get_recipe(&self, id: i64) -> Result<model::Recipe> {
 223         let con = self.get()?;
 224         con.query_row(
 225             "SELECT [id], [user_id], [title], [description] FROM [Recipe] WHERE [id] = ?1",
 226             [id],
 227             |row| {
 228                 Ok(model::Recipe::new(
 229                     row.get("id")?,
 230                     row.get("user_id")?,
 231                     row.get("title")?,
 232                     row.get("description")?,
 233                 ))
 234             },
 235         )
 236         .map_err(DBError::from)
 237     }
 238
 239     pub fn get_user_login_info(&self, token: &str) -> Result<model::UserLoginInfo> {
 240         let con = self.get()?;
 241         con.query_row("SELECT [last_login_datetime], [ip], [user_agent] FROM [UserLoginToken] WHERE [token] = ?1", [token], |r| {
 242             Ok(model::UserLoginInfo {
 243                 last_login_datetime: r.get("last_login_datetime")?,
 244                 ip: r.get("ip")?,
 245                 user_agent: r.get("user_agent")?,
 246             })
 247         }).map_err(DBError::from)
 248     }
 249
 250     pub fn load_user(&self, user_id: i64) -> Result<model::User> {
 251         let con = self.get()?;
 252         con.query_row(
 253             "SELECT [email] FROM [User] WHERE [id] = ?1",
 254             [user_id],
 255             |r| {
 256                 Ok(model::User {
 257                     id: user_id,
 258                     email: r.get("email")?,
 259                 })
 260             },
 261         )
 262         .map_err(DBError::from)
 263     }
 264
 265     pub fn sign_up(&self, email: &str, password: &str) -> Result<SignUpResult> {
 266         self.sign_up_with_given_time(email, password, Utc::now())
 267     }
 268
 269     fn sign_up_with_given_time(
 270         &self,
 271         email: &str,
 272         password: &str,
 273         datetime: DateTime<Utc>,
 274     ) -> Result<SignUpResult> {
 275         let mut con = self.get()?;
 276         let tx = con.transaction()?;
 277         let token = match tx
 278             .query_row(
 279                 "SELECT [id], [validation_token] FROM [User] WHERE [email] = ?1",
 280                 [email],
 281                 |r| {
 282                     Ok((
 283                         r.get::<&str, i64>("id")?,
 284                         r.get::<&str, Option<String>>("validation_token")?,
 285                     ))
 286                 },
 287             )
 288             .optional()?
 289         {
 290             Some((id, validation_token)) => {
 291                 if validation_token.is_none() {
 292                     return Ok(SignUpResult::UserAlreadyExists);
 293                 }
 294                 let token = generate_token();
 295                 let hashed_password = hash(password).map_err(|e| DBError::from_dyn_error(e))?;
 296                 tx.execute(
 297                     "UPDATE [User]
 298                     SET [validation_token] = ?2, [creation_datetime] = ?3, [password] = ?4
 299                     WHERE [id] = ?1",
 300                     params![id, token, datetime, hashed_password],
 301                 )?;
 302                 token
 303             }
 304             None => {
 305                 let token = generate_token();
 306                 let hashed_password = hash(password).map_err(|e| DBError::from_dyn_error(e))?;
 307                 tx.execute(
 308                     "INSERT INTO [User]
 309                     ([email], [validation_token], [creation_datetime], [password])
 310                     VALUES (?1, ?2, ?3, ?4)",
 311                     params![email, token, datetime, hashed_password],
 312                 )?;
 313                 token
 314             }
 315         };
 316         tx.commit()?;
 317         Ok(SignUpResult::UserCreatedWaitingForValidation(token))
 318     }
 319
 320     pub fn validation(
 321         &self,
 322         token: &str,
 323         validation_time: Duration,
 324         ip: &str,
 325         user_agent: &str,
 326     ) -> Result<ValidationResult> {
 327         let mut con = self.get()?;
 328         let tx = con.transaction()?;
 329         let user_id = match tx
 330             .query_row(
 331                 "SELECT [id], [creation_datetime] FROM [User] WHERE [validation_token] = ?1",
 332                 [token],
 333                 |r| {
 334                     Ok((
 335                         r.get::<&str, i64>("id")?,
 336                         r.get::<&str, DateTime<Utc>>("creation_datetime")?,
 337                     ))
 338                 },
 339             )
 340             .optional()?
 341         {
 342             Some((id, creation_datetime)) => {
 343                 if Utc::now() - creation_datetime > validation_time {
 344                     return Ok(ValidationResult::ValidationExpired);
 345                 }
 346                 tx.execute(
 347                     "UPDATE [User] SET [validation_token] = NULL WHERE [id] = ?1",
 348                     [id],
 349                 )?;
 350                 id
 351             }
 352             None => return Ok(ValidationResult::UnknownUser),
 353         };
 354         let token = Connection::create_login_token(&tx, user_id, ip, user_agent)?;
 355         tx.commit()?;
 356         Ok(ValidationResult::Ok(token, user_id))
 357     }
 358
 359     pub fn sign_in(
 360         &self,
 361         email: &str,
 362         password: &str,
 363         ip: &str,
 364         user_agent: &str,
 365     ) -> Result<SignInResult> {
 366         let mut con = self.get()?;
 367         let tx = con.transaction()?;
 368         match tx
 369             .query_row(
 370                 "SELECT [id], [password], [validation_token] FROM [User] WHERE [email] = ?1",
 371                 [email],
 372                 |r| {
 373                     Ok((
 374                         r.get::<&str, i64>("id")?,
 375                         r.get::<&str, String>("password")?,
 376                         r.get::<&str, Option<String>>("validation_token")?,
 377                     ))
 378                 },
 379             )
 380             .optional()?
 381         {
 382             Some((id, stored_password, validation_token)) => {
 383                 if validation_token.is_some() {
 384                     Ok(SignInResult::AccountNotValidated)
 385                 } else if verify_password(password, &stored_password)
 386                     .map_err(DBError::from_dyn_error)?
 387                 {
 388                     let token = Connection::create_login_token(&tx, id, ip, user_agent)?;
 389                     tx.commit()?;
 390                     Ok(SignInResult::Ok(token, id))
 391                 } else {
 392                     Ok(SignInResult::WrongPassword)
 393                 }
 394             }
 395             None => Ok(SignInResult::UserNotFound),
 396         }
 397     }
 398
 399     pub fn authentication(
 400         &self,
 401         token: &str,
 402         ip: &str,
 403         user_agent: &str,
 404     ) -> Result<AuthenticationResult> {
 405         let mut con = self.get()?;
 406         let tx = con.transaction()?;
 407         match tx
 408             .query_row(
 409                 "SELECT [id], [user_id] FROM [UserLoginToken] WHERE [token] = ?1",
 410                 [token],
 411                 |r| Ok((r.get::<&str, i64>("id")?, r.get::<&str, i64>("user_id")?)),
 412             )
 413             .optional()?
 414         {
 415             Some((login_id, user_id)) => {
 416                 tx.execute(
 417                     "UPDATE [UserLoginToken]
 418                     SET [last_login_datetime] = ?2, [ip] = ?3, [user_agent] = ?4
 419                     WHERE [id] = ?1",
 420                     params![login_id, Utc::now(), ip, user_agent],
 421                 )?;
 422                 tx.commit()?;
 423                 Ok(AuthenticationResult::Ok(user_id))
 424             }
 425             None => Ok(AuthenticationResult::NotValidToken),
 426         }
 427     }
 428
 429     pub fn sign_out(&self, token: &str) -> Result<()> {
 430         let mut con = self.get()?;
 431         let tx = con.transaction()?;
 432         match tx
 433             .query_row(
 434                 "SELECT [id] FROM [UserLoginToken] WHERE [token] = ?1",
 435                 [token],
 436                 |r| Ok(r.get::<&str, i64>("id")?),
 437             )
 438             .optional()?
 439         {
 440             Some(login_id) => {
 441                 tx.execute(
 442                     "DELETE FROM [UserLoginToken] WHERE [id] = ?1",
 443                     params![login_id],
 444                 )?;
 445                 tx.commit()?
 446             }
 447             None => (),
 448         }
 449         Ok(())
 450     }
 451
 452     pub fn create_recipe(&self, user_id: i64) -> Result<i64> {
 453         let con = self.get()?;
 454
 455         // Verify if an empty recipe already exists. Returns its id if one exists.
 456         match con
 457             .query_row(
 458                 "SELECT [Recipe].[id] FROM [Recipe]
 459             INNER JOIN [Image] ON [Image].[recipe_id] = [Recipe].[id]
 460             INNER JOIN [Group] ON [Group].[recipe_id] = [Recipe].[id]
 461             WHERE [Recipe].[user_id] = ?1
 462                 AND [Recipe].[estimate_time] = NULL
 463                 AND [Recipe].[description] = NULL",
 464                 [user_id],
 465                 |r| Ok(r.get::<&str, i64>("id")?),
 466             )
 467             .optional()?
 468         {
 469             Some(recipe_id) => Ok(recipe_id),
 470             None => {
 471                 con.execute(
 472                     "INSERT INTO [Recipe] ([user_id], [title]) VALUES (?1, '')",
 473                     [user_id],
 474                 )?;
 475                 Ok(con.last_insert_rowid())
 476             }
 477         }
 478     }
 479
 480     pub fn set_recipe_title(&self, recipe_id: i64, title: &str) -> Result<()> {
 481         let con = self.get()?;
 482         con.execute(
 483             "UPDATE [Recipe] SET [title] = ?2 WHERE [id] = ?1",
 484             params![recipe_id, title],
 485         )
 486         .map(|_n| ())
 487         .map_err(DBError::from)
 488     }
 489
 490     pub fn set_recipe_description(&self, recipe_id: i64, description: &str) -> Result<()> {
 491         let con = self.get()?;
 492         con.execute(
 493             "UPDATE [Recipe] SET [description] = ?2 WHERE [id] = ?1",
 494             params![recipe_id, description],
 495         )
 496         .map(|_n| ())
 497         .map_err(DBError::from)
 498     }
 499
 500     /// Execute a given SQL file.
 501     pub fn execute_file<P: AsRef<Path> + fmt::Display>(&self, file: P) -> Result<()> {
 502         let con = self.get()?;
 503         let sql = load_sql_file(file)?;
 504         con.execute_batch(&sql).map_err(DBError::from)
 505     }
 506
 507     /// Execute any SQL statement.
 508     /// Mainly used for testing.
 509     pub fn execute_sql<P: Params>(&self, sql: &str, params: P) -> Result<usize> {
 510         let con = self.get()?;
 511         con.execute(sql, params).map_err(DBError::from)
 512     }
 513
 514     // Return the token.
 515     fn create_login_token(
 516         tx: &rusqlite::Transaction,
 517         user_id: i64,
 518         ip: &str,
 519         user_agent: &str,
 520     ) -> Result<String> {
 521         let token = generate_token();
 522         tx.execute(
 523             "INSERT INTO [UserLoginToken]
 524             ([user_id], [last_login_datetime], [token], [ip], [user_agent])
 525             VALUES (?1, ?2, ?3, ?4, ?5)",
 526             params![user_id, Utc::now(), token, ip, user_agent],
 527         )?;
 528         Ok(token)
 529     }
 530 }
 531
 532 fn load_sql_file<P: AsRef<Path> + fmt::Display>(sql_file: P) -> Result<String> {
 533     let mut file = File::open(&sql_file).map_err(|err| {
 534         DBError::Other(format!(
 535             "Cannot open SQL file ({}): {}",
 536             &sql_file,
 537             err.to_string()
 538         ))
 539     })?;
 540     let mut sql = String::new();
 541     file.read_to_string(&mut sql).map_err(|err| {
 542         DBError::Other(format!(
 543             "Cannot read SQL file ({}) : {}",
 544             &sql_file,
 545             err.to_string()
 546         ))
 547     })?;
 548     Ok(sql)
 549 }
 550
 551 fn generate_token() -> String {
 552     Alphanumeric.sample_string(&mut rand::thread_rng(), consts::AUTHENTICATION_TOKEN_SIZE)
 553 }
 554
 555 #[cfg(test)]
 556 mod tests {
 557     use super::*;
 558     use rusqlite::{ffi, types::Value, Error, ErrorCode};
 559
 560     #[test]
 561     fn sign_up() -> Result<()> {
 562         let connection = Connection::new_in_memory()?;
 563         match connection.sign_up("paul@atreides.com", "12345")? {
 564             SignUpResult::UserCreatedWaitingForValidation(_) => (), // Nominal case.
 565             other => panic!("{:?}", other),
 566         }
 567         Ok(())
 568     }
 569
 570     #[test]
 571     fn sign_up_to_an_already_existing_user() -> Result<()> {
 572         let connection = Connection::new_in_memory()?;
 573         connection.execute_sql("
 574             INSERT INTO
 575                 [User] ([id], [email], [name], [password], [creation_datetime], [validation_token])
 576                 VALUES (
 577                     1,
 578                     'paul@atreides.com',
 579                     'paul',
 580                     '$argon2id$v=19$m=4096,t=3,p=1$1vtXcacYjUHZxMrN6b2Xng$wW8Z59MIoMcsIljnjHmxn3EBcc5ymEySZPUVXHlRxcY',
 581                     0,
 582                     NULL
 583                 );", [])?;
 584         match connection.sign_up("paul@atreides.com", "12345")? {
 585             SignUpResult::UserAlreadyExists => (), // Nominal case.
 586             other => panic!("{:?}", other),
 587         }
 588         Ok(())
 589     }
 590
 591     #[test]
 592     fn sign_up_and_sign_in_without_validation() -> Result<()> {
 593         let connection = Connection::new_in_memory()?;
 594
 595         let email = "paul@atreides.com";
 596         let password = "12345";
 597
 598         match connection.sign_up(email, password)? {
 599             SignUpResult::UserCreatedWaitingForValidation(_) => (), // Nominal case.
 600             other => panic!("{:?}", other),
 601         }
 602
 603         match connection.sign_in(email, password, "127.0.0.1", "Mozilla/5.0")? {
 604             SignInResult::AccountNotValidated => (), // Nominal case.
 605             other => panic!("{:?}", other),
 606         }
 607
 608         Ok(())
 609     }
 610
 611     #[test]
 612     fn sign_up_to_an_unvalidated_already_existing_user() -> Result<()> {
 613         let connection = Connection::new_in_memory()?;
 614         let token = generate_token();
 615         connection.execute_sql("
 616             INSERT INTO
 617                 [User] ([id], [email], [name], [password], [creation_datetime], [validation_token])
 618                 VALUES (
 619                     1,
 620                     'paul@atreides.com',
 621                     'paul',
 622                     '$argon2id$v=19$m=4096,t=3,p=1$1vtXcacYjUHZxMrN6b2Xng$wW8Z59MIoMcsIljnjHmxn3EBcc5ymEySZPUVXHlRxcY',
 623                     0,
 624                     :token
 625                 );", named_params! { ":token": token })?;
 626         match connection.sign_up("paul@atreides.com", "12345")? {
 627             SignUpResult::UserCreatedWaitingForValidation(_) => (), // Nominal case.
 628             other => panic!("{:?}", other),
 629         }
 630         Ok(())
 631     }
 632
 633     #[test]
 634     fn sign_up_then_send_validation_at_time() -> Result<()> {
 635         let connection = Connection::new_in_memory()?;
 636         let validation_token = match connection.sign_up("paul@atreides.com", "12345")? {
 637             SignUpResult::UserCreatedWaitingForValidation(token) => token, // Nominal case.
 638             other => panic!("{:?}", other),
 639         };
 640         match connection.validation(
 641             &validation_token,
 642             Duration::hours(1),
 643             "127.0.0.1",
 644             "Mozilla/5.0",
 645         )? {
 646             ValidationResult::Ok(_, _) => (), // Nominal case.
 647             other => panic!("{:?}", other),
 648         }
 649         Ok(())
 650     }
 651
 652     #[test]
 653     fn sign_up_then_send_validation_too_late() -> Result<()> {
 654         let connection = Connection::new_in_memory()?;
 655         let validation_token = match connection.sign_up_with_given_time(
 656             "paul@atreides.com",
 657             "12345",
 658             Utc::now() - Duration::days(1),
 659         )? {
 660             SignUpResult::UserCreatedWaitingForValidation(token) => token, // Nominal case.
 661             other => panic!("{:?}", other),
 662         };
 663         match connection.validation(
 664             &validation_token,
 665             Duration::hours(1),
 666             "127.0.0.1",
 667             "Mozilla/5.0",
 668         )? {
 669             ValidationResult::ValidationExpired => (), // Nominal case.
 670             other => panic!("{:?}", other),
 671         }
 672         Ok(())
 673     }
 674
 675     #[test]
 676     fn sign_up_then_send_validation_with_bad_token() -> Result<()> {
 677         let connection = Connection::new_in_memory()?;
 678         let _validation_token = match connection.sign_up("paul@atreides.com", "12345")? {
 679             SignUpResult::UserCreatedWaitingForValidation(token) => token, // Nominal case.
 680             other => panic!("{:?}", other),
 681         };
 682         let random_token = generate_token();
 683         match connection.validation(
 684             &random_token,
 685             Duration::hours(1),
 686             "127.0.0.1",
 687             "Mozilla/5.0",
 688         )? {
 689             ValidationResult::UnknownUser => (), // Nominal case.
 690             other => panic!("{:?}", other),
 691         }
 692         Ok(())
 693     }
 694
 695     #[test]
 696     fn sign_up_then_send_validation_then_sign_in() -> Result<()> {
 697         let connection = Connection::new_in_memory()?;
 698
 699         let email = "paul@atreides.com";
 700         let password = "12345";
 701
 702         // Sign up.
 703         let validation_token = match connection.sign_up(email, password)? {
 704             SignUpResult::UserCreatedWaitingForValidation(token) => token, // Nominal case.
 705             other => panic!("{:?}", other),
 706         };
 707
 708         // Validation.
 709         match connection.validation(
 710             &validation_token,
 711             Duration::hours(1),
 712             "127.0.0.1",
 713             "Mozilla/5.0",
 714         )? {
 715             ValidationResult::Ok(_, _) => (),
 716             other => panic!("{:?}", other),
 717         };
 718
 719         // Sign in.
 720         match connection.sign_in(email, password, "127.0.0.1", "Mozilla/5.0")? {
 721             SignInResult::Ok(_, _) => (), // Nominal case.
 722             other => panic!("{:?}", other),
 723         }
 724
 725         Ok(())
 726     }
 727
 728     #[test]
 729     fn sign_up_then_send_validation_then_authentication() -> Result<()> {
 730         let connection = Connection::new_in_memory()?;
 731
 732         let email = "paul@atreides.com";
 733         let password = "12345";
 734
 735         // Sign up.
 736         let validation_token = match connection.sign_up(email, password)? {
 737             SignUpResult::UserCreatedWaitingForValidation(token) => token, // Nominal case.
 738             other => panic!("{:?}", other),
 739         };
 740
 741         // Validation.
 742         let (authentication_token, user_id) = match connection.validation(
 743             &validation_token,
 744             Duration::hours(1),
 745             "127.0.0.1",
 746             "Mozilla",
 747         )? {
 748             ValidationResult::Ok(token, user_id) => (token, user_id),
 749             other => panic!("{:?}", other),
 750         };
 751
 752         // Check user login information.
 753         let user_login_info_1 = connection.get_user_login_info(&authentication_token)?;
 754         assert_eq!(user_login_info_1.ip, "127.0.0.1");
 755         assert_eq!(user_login_info_1.user_agent, "Mozilla");
 756
 757         // Authentication.
 758         let _user_id =
 759             match connection.authentication(&authentication_token, "192.168.1.1", "Chrome")? {
 760                 AuthenticationResult::Ok(user_id) => user_id, // Nominal case.
 761                 other => panic!("{:?}", other),
 762             };
 763
 764         // Check user login information.
 765         let user_login_info_2 = connection.get_user_login_info(&authentication_token)?;
 766         assert_eq!(user_login_info_2.ip, "192.168.1.1");
 767         assert_eq!(user_login_info_2.user_agent, "Chrome");
 768
 769         Ok(())
 770     }
 771
 772     #[test]
 773     fn sign_up_then_send_validation_then_sign_out_then_sign_in() -> Result<()> {
 774         let connection = Connection::new_in_memory()?;
 775
 776         let email = "paul@atreides.com";
 777         let password = "12345";
 778
 779         // Sign up.
 780         let validation_token = match connection.sign_up(email, password)? {
 781             SignUpResult::UserCreatedWaitingForValidation(token) => token, // Nominal case.
 782             other => panic!("{:?}", other),
 783         };
 784
 785         // Validation.
 786         let (authentication_token_1, user_id_1) = match connection.validation(
 787             &validation_token,
 788             Duration::hours(1),
 789             "127.0.0.1",
 790             "Mozilla",
 791         )? {
 792             ValidationResult::Ok(token, user_id) => (token, user_id),
 793             other => panic!("{:?}", other),
 794         };
 795
 796         // Check user login information.
 797         let user_login_info_1 = connection.get_user_login_info(&authentication_token_1)?;
 798         assert_eq!(user_login_info_1.ip, "127.0.0.1");
 799         assert_eq!(user_login_info_1.user_agent, "Mozilla");
 800
 801         // Sign out.
 802         connection.sign_out(&authentication_token_1)?;
 803
 804         // Sign in.
 805         let (authentication_token_2, user_id_2) =
 806             match connection.sign_in(email, password, "192.168.1.1", "Chrome")? {
 807                 SignInResult::Ok(token, user_id) => (token, user_id),
 808                 other => panic!("{:?}", other),
 809             };
 810
 811         assert_eq!(user_id_1, user_id_2);
 812         assert_ne!(authentication_token_1, authentication_token_2);
 813
 814         // Check user login information.
 815         let user_login_info_2 = connection.get_user_login_info(&authentication_token_2)?;
 816
 817         assert_eq!(user_login_info_2.ip, "192.168.1.1");
 818         assert_eq!(user_login_info_2.user_agent, "Chrome");
 819
 820         Ok(())
 821     }
 822
 823     #[test]
 824     fn create_a_new_recipe_then_update_its_title() -> Result<()> {
 825         let connection = Connection::new_in_memory()?;
 826
 827         connection.execute_sql(
 828             "INSERT INTO [User]
 829             ([id], [email], [name], [password], [creation_datetime], [validation_token])
 830             VALUES (?1, ?2, ?3, ?4, ?5, ?6)",
 831             params![
 832                 1,
 833                 "paul@atreides.com",
 834                 "paul",
 835                 "$argon2id$v=19$m=4096,t=3,p=1$G4fjepS05MkRbTqEImUdYg$GGziE8uVQe1L1oFHk37lBno10g4VISnVqynSkLCH3Lc",
 836                 "2022-11-29 22:05:04.121407300+00:00",
 837                 Value::Null,
 838             ]
 839         )?;
 840
 841         match connection.create_recipe(2) {
 842             Err(DBError::SqliteError(Error::SqliteFailure(
 843                 ffi::Error {
 844                     code: ErrorCode::ConstraintViolation,
 845                     extended_code: _,
 846                 },
 847                 Some(_),
 848             ))) => (), // Nominal case.
 849             other => panic!(
 850                 "Creating a recipe with an inexistant user must fail: {:?}",
 851                 other
 852             ),
 853         }
 854
 855         let recipe_id = connection.create_recipe(1)?;
 856         assert_eq!(recipe_id, 1);
 857
 858         connection.set_recipe_title(recipe_id, "Crêpe")?;
 859
 860         let recipe = connection.get_recipe(recipe_id)?;
 861         assert_eq!(recipe.title, "Crêpe".to_string());
 862
 863         Ok(())
 864     }
 865 }