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
};
16 hash
::{hash
, verify_password
},
21 const CURRENT_DB_VERSION
: u32 = 1;
25 SqliteError(rusqlite
::Error
),
26 R2d2Error(r2d2
::Error
),
27 UnsupportedVersion(u32),
31 impl fmt
::Display
for DBError
{
32 fn fmt(&self, f
: &mut fmt
::Formatter
) -> std
::result
::Result
<(), fmt
::Error
> {
33 write!(f
, "{:?}", self)
37 impl std
::error
::Error
for DBError {}
39 impl From
<rusqlite
::Error
> for DBError
{
40 fn from(error
: rusqlite
::Error
) -> Self {
41 DBError
::SqliteError(error
)
45 impl From
<r2d2
::Error
> for DBError
{
46 fn from(error
: r2d2
::Error
) -> Self {
47 DBError
::R2d2Error(error
)
52 fn from_dyn_error(error
: Box
<dyn std
::error
::Error
>) -> Self {
53 DBError
::Other(error
.to_string())
57 type Result
<T
> = std
::result
::Result
<T
, DBError
>;
60 pub enum SignUpResult
{
62 UserCreatedWaitingForValidation(String
), // Validation token.
66 pub enum ValidationResult
{
69 Ok(String
, i64), // Returns token and user id.
73 pub enum SignInResult
{
77 Ok(String
, i64), // Returns token and user id.
81 pub enum AuthenticationResult
{
83 Ok(i64), // Returns user id.
87 pub struct Connection
{
88 pool
: Pool
<SqliteConnectionManager
>,
92 pub fn new() -> Result
<Connection
> {
93 let path
= Path
::new(consts
::DB_DIRECTORY
).join(consts
::DB_FILENAME
);
94 Self::new_from_file(path
)
97 pub fn new_in_memory() -> Result
<Connection
> {
98 Self::create_connection(SqliteConnectionManager
::memory())
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();
108 Self::create_connection(SqliteConnectionManager
::file(file
))
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()?
;
118 fn get(&self) -> Result
<PooledConnection
<SqliteConnectionManager
>> {
119 let con
= self.pool
.get()?
;
120 con
.pragma_update(None
, "synchronous", "NORMAL")?
;
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")?
;
131 let tx
= con
.transaction()?
;
133 // Version 0 corresponds to an empty database.
136 "SELECT [name] FROM [sqlite_master] WHERE [type] = 'table' AND [name] = 'Version'",
138 |row
| row
.get
::<usize, String
>(0),
142 "SELECT [version] FROM [Version] ORDER BY [id] DESC",
146 .unwrap_or_default(),
151 while Self::update_to_next_version(version
, &tx
)?
{
160 fn update_to_next_version(current_version
: u32, tx
: &rusqlite
::Transaction
) -> Result
<bool
> {
161 let next_version
= current_version
+ 1;
163 if next_version
<= CURRENT_DB_VERSION
{
164 println!("Update to version {}...", next_version
);
167 fn update_version(to_version
: u32, tx
: &rusqlite
::Transaction
) -> Result
<()> {
169 "INSERT INTO [Version] ([version], [datetime]) VALUES (?1, datetime('now'))",
173 .map_err(DBError
::from
)
176 fn ok(updated
: bool
) -> Result
<bool
> {
178 println!("Version updated");
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
)?
;
192 // Version 1 doesn't exist yet.
195 v
=> Err(DBError
::UnsupportedVersion(v
)),
199 pub fn get_all_recipe_titles(&self) -> Result
<Vec
<(i64, String
)>> {
200 let con
= self.get()?
;
202 let mut stmt
= con
.prepare("SELECT [id], [title] FROM [Recipe] ORDER BY [title]")?
;
204 let titles
: std
::result
::Result
<Vec
<(i64, String
)>, rusqlite
::Error
> = stmt
205 .query_map([], |row
| Ok((row
.get("id")?
, row
.get("title")?
)))?
208 titles
.map_err(DBError
::from
)
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]")?;
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.
222 pub fn get_recipe(&self, id
: i64) -> Result
<model
::Recipe
> {
223 let con
= self.get()?
;
225 "SELECT [id], [user_id], [title], [description] FROM [Recipe] WHERE [id] = ?1",
228 Ok(model
::Recipe
::new(
232 row
.get("description")?
,
236 .map_err(DBError
::from
)
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")?
,
245 user_agent
: r
.get("user_agent")?
,
247 }).map_err(DBError
::from
)
250 pub fn load_user(&self, user_id
: i64) -> Result
<model
::User
> {
251 let con
= self.get()?
;
253 "SELECT [email] FROM [User] WHERE [id] = ?1",
258 email
: r
.get("email")?
,
262 .map_err(DBError
::from
)
265 pub fn sign_up(&self, email
: &str, password
: &str) -> Result
<SignUpResult
> {
266 self.sign_up_with_given_time(email
, password
, Utc
::now())
269 fn sign_up_with_given_time(
273 datetime
: DateTime
<Utc
>,
274 ) -> Result
<SignUpResult
> {
275 let mut con
= self.get()?
;
276 let tx
= con
.transaction()?
;
279 "SELECT [id], [validation_token] FROM [User] WHERE [email] = ?1",
283 r
.get
::<&str, i64>("id")?
,
284 r
.get
::<&str, Option
<String
>>("validation_token")?
,
290 Some((id
, validation_token
)) => {
291 if validation_token
.is_none() {
292 return Ok(SignUpResult
::UserAlreadyExists
);
294 let token
= generate_token();
295 let hashed_password
= hash(password
).map_err(|e
| DBError
::from_dyn_error(e
))?
;
298 SET [validation_token] = ?2, [creation_datetime] = ?3, [password] = ?4
300 params
![id
, token
, datetime
, hashed_password
],
305 let token
= generate_token();
306 let hashed_password
= hash(password
).map_err(|e
| DBError
::from_dyn_error(e
))?
;
309 ([email], [validation_token], [creation_datetime], [password])
310 VALUES (?1, ?2, ?3, ?4)",
311 params
![email
, token
, datetime
, hashed_password
],
317 Ok(SignUpResult
::UserCreatedWaitingForValidation(token
))
323 validation_time
: Duration
,
326 ) -> Result
<ValidationResult
> {
327 let mut con
= self.get()?
;
328 let tx
= con
.transaction()?
;
329 let user_id
= match tx
331 "SELECT [id], [creation_datetime] FROM [User] WHERE [validation_token] = ?1",
335 r
.get
::<&str, i64>("id")?
,
336 r
.get
::<&str, DateTime
<Utc
>>("creation_datetime")?
,
342 Some((id
, creation_datetime
)) => {
343 if Utc
::now() - creation_datetime
> validation_time
{
344 return Ok(ValidationResult
::ValidationExpired
);
347 "UPDATE [User] SET [validation_token] = NULL WHERE [id] = ?1",
352 None
=> return Ok(ValidationResult
::UnknownUser
),
354 let token
= Connection
::create_login_token(&tx
, user_id
, ip
, user_agent
)?
;
356 Ok(ValidationResult
::Ok(token
, user_id
))
365 ) -> Result
<SignInResult
> {
366 let mut con
= self.get()?
;
367 let tx
= con
.transaction()?
;
370 "SELECT [id], [password], [validation_token] FROM [User] WHERE [email] = ?1",
374 r
.get
::<&str, i64>("id")?
,
375 r
.get
::<&str, String
>("password")?
,
376 r
.get
::<&str, Option
<String
>>("validation_token")?
,
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
)?
388 let token
= Connection
::create_login_token(&tx
, id
, ip
, user_agent
)?
;
390 Ok(SignInResult
::Ok(token
, id
))
392 Ok(SignInResult
::WrongPassword
)
395 None
=> Ok(SignInResult
::UserNotFound
),
399 pub fn authentication(
404 ) -> Result
<AuthenticationResult
> {
405 let mut con
= self.get()?
;
406 let tx
= con
.transaction()?
;
409 "SELECT [id], [user_id] FROM [UserLoginToken] WHERE [token] = ?1",
411 |r
| Ok((r
.get
::<&str, i64>("id")?
, r
.get
::<&str, i64>("user_id")?
)),
415 Some((login_id
, user_id
)) => {
417 "UPDATE [UserLoginToken]
418 SET [last_login_datetime] = ?2, [ip] = ?3, [user_agent] = ?4
420 params
![login_id
, Utc
::now(), ip
, user_agent
],
423 Ok(AuthenticationResult
::Ok(user_id
))
425 None
=> Ok(AuthenticationResult
::NotValidToken
),
429 pub fn sign_out(&self, token
: &str) -> Result
<()> {
430 let mut con
= self.get()?
;
431 let tx
= con
.transaction()?
;
434 "SELECT [id] FROM [UserLoginToken] WHERE [token] = ?1",
436 |r
| Ok(r
.get
::<&str, i64>("id")?
),
442 "DELETE FROM [UserLoginToken] WHERE [id] = ?1",
452 pub fn create_recipe(&self, user_id
: i64) -> Result
<i64> {
453 let con
= self.get()?
;
455 // Verify if an empty recipe already exists. Returns its id if one exists.
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",
465 |r
| Ok(r
.get
::<&str, i64>("id")?
),
469 Some(recipe_id
) => Ok(recipe_id
),
472 "INSERT INTO [Recipe] ([user_id], [title]) VALUES (?1, '')",
475 Ok(con
.last_insert_rowid())
480 pub fn set_recipe_title(&self, recipe_id
: i64, title
: &str) -> Result
<()> {
481 let con
= self.get()?
;
483 "UPDATE [Recipe] SET [title] = ?2 WHERE [id] = ?1",
484 params
![recipe_id
, title
],
487 .map_err(DBError
::from
)
490 pub fn set_recipe_description(&self, recipe_id
: i64, description
: &str) -> Result
<()> {
491 let con
= self.get()?
;
493 "UPDATE [Recipe] SET [description] = ?2 WHERE [id] = ?1",
494 params
![recipe_id
, description
],
497 .map_err(DBError
::from
)
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
)
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
)
515 fn create_login_token(
516 tx
: &rusqlite
::Transaction
,
520 ) -> Result
<String
> {
521 let token
= generate_token();
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
],
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 ({}): {}",
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 ({}) : {}",
551 fn generate_token() -> String
{
552 Alphanumeric
.sample_string(&mut rand
::thread_rng(), consts
::AUTHENTICATION_TOKEN_SIZE
)
558 use rusqlite
::{ffi
, types
::Value
, Error
, ErrorCode
};
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
),
571 fn sign_up_to_an_already_existing_user() -> Result
<()> {
572 let connection
= Connection
::new_in_memory()?
;
573 connection
.execute_sql("
575 [User] ([id], [email], [name], [password], [creation_datetime], [validation_token])
580 '$argon2id$v=19$m=4096,t=3,p=1$1vtXcacYjUHZxMrN6b2Xng$wW8Z59MIoMcsIljnjHmxn3EBcc5ymEySZPUVXHlRxcY',
584 match connection
.sign_up("paul@atreides.com", "12345")?
{
585 SignUpResult
::UserAlreadyExists
=> (), // Nominal case.
586 other
=> panic!("{:?}", other
),
592 fn sign_up_and_sign_in_without_validation() -> Result
<()> {
593 let connection
= Connection
::new_in_memory()?
;
595 let email
= "paul@atreides.com";
596 let password
= "12345";
598 match connection
.sign_up(email
, password
)?
{
599 SignUpResult
::UserCreatedWaitingForValidation(_
) => (), // Nominal case.
600 other
=> panic!("{:?}", other
),
603 match connection
.sign_in(email
, password
, "127.0.0.1", "Mozilla/5.0")?
{
604 SignInResult
::AccountNotValidated
=> (), // Nominal case.
605 other
=> panic!("{:?}", other
),
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("
617 [User] ([id], [email], [name], [password], [creation_datetime], [validation_token])
622 '$argon2id$v=19$m=4096,t=3,p=1$1vtXcacYjUHZxMrN6b2Xng$wW8Z59MIoMcsIljnjHmxn3EBcc5ymEySZPUVXHlRxcY',
625 );", named_params
! { ":token": token
})?
;
626 match connection
.sign_up("paul@atreides.com", "12345")?
{
627 SignUpResult
::UserCreatedWaitingForValidation(_
) => (), // Nominal case.
628 other
=> panic!("{:?}", other
),
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
),
640 match connection
.validation(
646 ValidationResult
::Ok(_
, _
) => (), // Nominal case.
647 other
=> panic!("{:?}", other
),
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(
658 Utc
::now() - Duration
::days(1),
660 SignUpResult
::UserCreatedWaitingForValidation(token
) => token
, // Nominal case.
661 other
=> panic!("{:?}", other
),
663 match connection
.validation(
669 ValidationResult
::ValidationExpired
=> (), // Nominal case.
670 other
=> panic!("{:?}", other
),
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
),
682 let random_token
= generate_token();
683 match connection
.validation(
689 ValidationResult
::UnknownUser
=> (), // Nominal case.
690 other
=> panic!("{:?}", other
),
696 fn sign_up_then_send_validation_then_sign_in() -> Result
<()> {
697 let connection
= Connection
::new_in_memory()?
;
699 let email
= "paul@atreides.com";
700 let password
= "12345";
703 let validation_token
= match connection
.sign_up(email
, password
)?
{
704 SignUpResult
::UserCreatedWaitingForValidation(token
) => token
, // Nominal case.
705 other
=> panic!("{:?}", other
),
709 match connection
.validation(
715 ValidationResult
::Ok(_
, _
) => (),
716 other
=> panic!("{:?}", other
),
720 match connection
.sign_in(email
, password
, "127.0.0.1", "Mozilla/5.0")?
{
721 SignInResult
::Ok(_
, _
) => (), // Nominal case.
722 other
=> panic!("{:?}", other
),
729 fn sign_up_then_send_validation_then_authentication() -> Result
<()> {
730 let connection
= Connection
::new_in_memory()?
;
732 let email
= "paul@atreides.com";
733 let password
= "12345";
736 let validation_token
= match connection
.sign_up(email
, password
)?
{
737 SignUpResult
::UserCreatedWaitingForValidation(token
) => token
, // Nominal case.
738 other
=> panic!("{:?}", other
),
742 let (authentication_token
, user_id
) = match connection
.validation(
748 ValidationResult
::Ok(token
, user_id
) => (token
, user_id
),
749 other
=> panic!("{:?}", other
),
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");
759 match connection
.authentication(&authentication_token
, "192.168.1.1", "Chrome")?
{
760 AuthenticationResult
::Ok(user_id
) => user_id
, // Nominal case.
761 other
=> panic!("{:?}", other
),
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");
773 fn sign_up_then_send_validation_then_sign_out_then_sign_in() -> Result
<()> {
774 let connection
= Connection
::new_in_memory()?
;
776 let email
= "paul@atreides.com";
777 let password
= "12345";
780 let validation_token
= match connection
.sign_up(email
, password
)?
{
781 SignUpResult
::UserCreatedWaitingForValidation(token
) => token
, // Nominal case.
782 other
=> panic!("{:?}", other
),
786 let (authentication_token_1
, user_id_1
) = match connection
.validation(
792 ValidationResult
::Ok(token
, user_id
) => (token
, user_id
),
793 other
=> panic!("{:?}", other
),
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");
802 connection
.sign_out(&authentication_token_1
)?
;
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
),
811 assert_eq!(user_id_1
, user_id_2
);
812 assert_ne!(authentication_token_1
, authentication_token_2
);
814 // Check user login information.
815 let user_login_info_2
= connection
.get_user_login_info(&authentication_token_2
)?
;
817 assert_eq!(user_login_info_2
.ip
, "192.168.1.1");
818 assert_eq!(user_login_info_2
.user_agent
, "Chrome");
824 fn create_a_new_recipe_then_update_its_title() -> Result
<()> {
825 let connection
= Connection
::new_in_memory()?
;
827 connection
.execute_sql(
829 ([id], [email], [name], [password], [creation_datetime], [validation_token])
830 VALUES (?1, ?2, ?3, ?4, ?5, ?6)",
835 "$argon2id$v=19$m=4096,t=3,p=1$G4fjepS05MkRbTqEImUdYg$GGziE8uVQe1L1oFHk37lBno10g4VISnVqynSkLCH3Lc",
836 "2022-11-29 22:05:04.121407300+00:00",
841 match connection
.create_recipe(2) {
842 Err(DBError
::SqliteError(Error
::SqliteFailure(
844 code
: ErrorCode
::ConstraintViolation
,
848 ))) => (), // Nominal case.
850 "Creating a recipe with an inexistant user must fail: {:?}",
855 let recipe_id
= connection
.create_recipe(1)?
;
856 assert_eq!(recipe_id
, 1);
858 connection
.set_recipe_title(recipe_id
, "Crêpe")?
;
860 let recipe
= connection
.get_recipe(recipe_id
)?
;
861 assert_eq!(recipe
.title
, "Crêpe".to_string());