backend/src/data/db.rs

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