4ef37fe26553dd4ae8cca8e2dc8c55a63a2af273
[recipes.git] / backend / src / db.rs
1 use std::{fmt::Display, fs::{self, File}, path::Path, io::Read};
2
3 use itertools::Itertools;
4 use chrono::{prelude::*, Duration};
5 use rusqlite::{params, Params, OptionalExtension};
6 use r2d2::Pool;
7 use r2d2_sqlite::SqliteConnectionManager;
8 use rand::distributions::{Alphanumeric, DistString};
9
10 use crate::consts;
11 use crate::hash::hash;
12 use crate::model;
13
14 const CURRENT_DB_VERSION: u32 = 1;
15
16 #[derive(Debug)]
17 pub enum DBError {
18 SqliteError(rusqlite::Error),
19 R2d2Error(r2d2::Error),
20 UnsupportedVersion(u32),
21 Other(String),
22 }
23
24 impl From<rusqlite::Error> for DBError {
25 fn from(error: rusqlite::Error) -> Self {
26 DBError::SqliteError(error)
27 }
28 }
29
30 impl From<r2d2::Error> for DBError {
31 fn from(error: r2d2::Error) -> Self {
32 DBError::R2d2Error(error)
33 }
34 }
35
36 // TODO: Is there a better solution?
37 impl DBError {
38 fn from_dyn_error(error: Box<dyn std::error::Error>) -> Self {
39 DBError::Other(error.to_string())
40 }
41 }
42
43 type Result<T> = std::result::Result<T, DBError>;
44
45 #[derive(Debug)]
46 pub enum SignUpResult {
47 UserAlreadyExists,
48 UserCreatedWaitingForValidation(String), // Validation token.
49 }
50
51 #[derive(Debug)]
52 pub enum ValidationResult {
53 ValidationExpired,
54 OK,
55 }
56
57 #[derive(Debug)]
58 pub enum SignInResult {
59 NotValidToken,
60 OK,
61 }
62
63 #[derive(Debug)]
64 pub enum AuthenticationResult {
65 NotValidToken,
66 OK,
67 }
68
69 #[derive(Clone)]
70 pub struct Connection {
71 //con: rusqlite::Connection
72 pool: Pool<SqliteConnectionManager>
73 }
74
75 impl Connection {
76 pub fn new() -> Result<Connection> {
77 let path = Path::new(consts::DB_DIRECTORY).join(consts::DB_FILENAME);
78 Self::new_from_file(path)
79 }
80
81 pub fn new_in_memory() -> Result<Connection> {
82 Self::create_connection(SqliteConnectionManager::memory())
83 }
84
85 pub fn new_from_file<P: AsRef<Path>>(file: P) -> Result<Connection> {
86 if let Some(data_dir) = file.as_ref().parent() {
87 if !data_dir.exists() {
88 fs::DirBuilder::new().create(data_dir).unwrap();
89 }
90 }
91
92 Self::create_connection(SqliteConnectionManager::file(file))
93 }
94
95 /// Called after the connection has been established for creating or updating the database.
96 /// The 'Version' table tracks the current state of the database.
97 fn create_or_update(&self) -> Result<()> {
98 // Check the Database version.
99 let mut con = self.pool.get()?;
100 let tx = con.transaction()?;
101
102 // Version 0 corresponds to an empty database.
103 let mut version = {
104 match tx.query_row(
105 "SELECT [name] FROM [sqlite_master] WHERE [type] = 'table' AND [name] = 'Version'",
106 [],
107 |row| row.get::<usize, String>(0)
108 ) {
109 Ok(_) => tx.query_row("SELECT [version] FROM [Version] ORDER BY [id] DESC", [], |row| row.get(0)).unwrap_or_default(),
110 Err(_) => 0
111 }
112 };
113
114 while Connection::update_to_next_version(version, &tx)? {
115 version += 1;
116 }
117
118 tx.commit()?;
119
120 Ok(())
121 }
122
123 fn create_connection(manager: SqliteConnectionManager) -> Result<Connection> {;
124 let pool = r2d2::Pool::new(manager).unwrap();
125 let connection = Connection { pool };
126 connection.create_or_update()?;
127 Ok(connection)
128 }
129
130 fn update_to_next_version(current_version: u32, tx: &rusqlite::Transaction) -> Result<bool> {
131 let next_version = current_version + 1;
132
133 if next_version <= CURRENT_DB_VERSION {
134 println!("Update to version {}...", next_version);
135 }
136
137 fn update_version(to_version: u32, tx: &rusqlite::Transaction) -> Result<()> {
138 tx.execute("INSERT INTO [Version] ([version], [datetime]) VALUES (?1, datetime('now'))", [to_version]).map(|_| ()).map_err(DBError::from)
139 }
140
141 fn ok(updated: bool) -> Result<bool> {
142 if updated {
143 println!("Version updated");
144 }
145 Ok(updated)
146 }
147
148 match next_version {
149 1 => {
150 let sql_file = consts::SQL_FILENAME.replace("{VERSION}", &next_version.to_string());
151 tx.execute_batch(&load_sql_file(&sql_file)?)?;
152 update_version(next_version, tx)?;
153
154 ok(true)
155 }
156
157 // Version 1 doesn't exist yet.
158 2 =>
159 ok(false),
160
161 v =>
162 Err(DBError::UnsupportedVersion(v)),
163 }
164 }
165
166 pub fn get_all_recipe_titles(&self) -> Result<Vec<(i32, String)>> {
167 let con = self.pool.get()?;
168 let mut stmt = con.prepare("SELECT [id], [title] FROM [Recipe] ORDER BY [title]")?;
169 let titles =
170 stmt.query_map([], |row| {
171 Ok((row.get(0)?, row.get(1)?))
172 })?.map(|r| r.unwrap()).collect_vec(); // TODO: remove unwrap.
173 Ok(titles)
174 }
175
176 /* Not used for the moment.
177 pub fn get_all_recipes(&self) -> Result<Vec<model::Recipe>> {
178 let con = self.pool.get()?;
179 let mut stmt = con.prepare("SELECT [id], [title] FROM [Recipe] ORDER BY [title]")?;
180 let recipes =
181 stmt.query_map([], |row| {
182 Ok(model::Recipe::new(row.get(0)?, row.get(1)?))
183 })?.map(|r| r.unwrap()).collect_vec(); // TODO: remove unwrap.
184 Ok(recipes)
185 } */
186
187 pub fn get_recipe(&self, id: i32) -> Result<model::Recipe> {
188 let con = self.pool.get()?;
189 con.query_row("SELECT [id], [title] FROM [Recipe] WHERE [id] = ?1", [id], |row| {
190 Ok(model::Recipe::new(row.get(0)?, row.get(1)?))
191 }).map_err(DBError::from)
192 }
193
194 ///
195 pub fn sign_up(&self, password: &str, email: &str) -> Result<SignUpResult> {
196 self.sign_up_with_given_time(password, email, Utc::now())
197 }
198
199 fn sign_up_with_given_time(&self, password: &str, email: &str, datetime: DateTime<Utc>) -> Result<SignUpResult> {
200 let mut con = self.pool.get()?;
201 let tx = con.transaction()?;
202 let token =
203 match tx.query_row("SELECT [id], [validation_token] FROM [User] WHERE [email] = ?1", [email], |r| {
204 Ok((r.get::<&str, i32>("id")?, r.get::<&str, Option<String>>("validation_token")?))
205 }).optional()? {
206 Some((id, validation_token)) => {
207 if validation_token.is_none() {
208 return Ok(SignUpResult::UserAlreadyExists)
209 }
210 let token = generate_token();
211 let hashed_password = hash(password).map_err(|e| DBError::from_dyn_error(e))?;
212 tx.execute("UPDATE [User] SET [validation_token] = ?2, [creation_datetime] = ?3, [password] = ?4 WHERE [id] = ?1", params![id, token, datetime, hashed_password])?;
213 token
214 },
215 None => {
216 let token = generate_token();
217 let hashed_password = hash(password).map_err(|e| DBError::from_dyn_error(e))?;
218 tx.execute("INSERT INTO [User] ([email], [validation_token], [creation_datetime], [password]) VALUES (?1, ?2, ?3, ?4)", params![email, token, datetime, hashed_password])?;
219 token
220 },
221 };
222 tx.commit()?;
223 Ok(SignUpResult::UserCreatedWaitingForValidation(token))
224 }
225
226 pub fn validation(&self, token: &str, validation_time: Duration) -> Result<ValidationResult> {
227 todo!()
228 }
229
230 pub fn sign_in(&self, password: &str, email: String) -> Result<SignInResult> {
231 todo!()
232 }
233
234 pub fn authentication(&self, token: &str) -> Result<AuthenticationResult> {
235 todo!()
236 }
237
238 pub fn logout(&self, token: &str) -> Result<()> {
239 todo!()
240 }
241
242 /// Execute a given SQL file.
243 pub fn execute_file<P: AsRef<Path> + Display>(&self, file: P) -> Result<()> {
244 let con = self.pool.get()?;
245 let sql = load_sql_file(file)?;
246 con.execute_batch(&sql).map_err(DBError::from)
247 }
248
249 /// Execute any SQL statement.
250 /// Mainly used for testing.
251 pub fn execute_sql<P: Params>(&self, sql: &str, params: P) -> Result<usize> {
252 let con = self.pool.get()?;
253 con.execute(sql, params).map_err(DBError::from)
254 }
255 }
256
257 fn load_sql_file<P: AsRef<Path> + Display>(sql_file: P) -> Result<String> {
258 let mut file = File::open(&sql_file).map_err(|err| DBError::Other(format!("Cannot open SQL file ({}): {}", &sql_file, err.to_string())))?;
259 let mut sql = String::new();
260 file.read_to_string(&mut sql).map_err(|err| DBError::Other(format!("Cannot read SQL file ({}) : {}", &sql_file, err.to_string())))?;
261 Ok(sql)
262 }
263
264 fn generate_token() -> String {
265 Alphanumeric.sample_string(&mut rand::thread_rng(), 24)
266 }
267
268 #[cfg(test)]
269 mod tests {
270 use super::*;
271
272 #[test]
273 fn sign_up() -> Result<()> {
274 let connection = Connection::new_in_memory()?;
275 match connection.sign_up("12345", "paul@test.org")? {
276 SignUpResult::UserCreatedWaitingForValidation(_) => (), // Nominal case.
277 other => panic!("{:?}", other),
278 }
279 Ok(())
280 }
281
282 #[test]
283 fn sign_up_to_an_already_existing_user() -> Result<()> {
284 let connection = Connection::new_in_memory()?;
285 connection.execute_sql("
286 INSERT INTO [User] ([id], [email], [name], [password], [creation_datetime], [validation_token])
287 VALUES (
288 1,
289 'paul@test.org',
290 'paul',
291 '$argon2id$v=19$m=4096,t=3,p=1$1vtXcacYjUHZxMrN6b2Xng$wW8Z59MIoMcsIljnjHmxn3EBcc5ymEySZPUVXHlRxcY',
292 0,
293 NULL
294 );", [])?;
295 match connection.sign_up("12345", "paul@test.org")? {
296 SignUpResult::UserAlreadyExists => (), // Nominal case.
297 other => panic!("{:?}", other),
298 }
299 Ok(())
300 }
301
302 #[test]
303 fn sign_up_to_an_unvalidated_already_existing_user() -> Result<()> {
304 todo!()
305 }
306
307 fn sign_up_then_send_validation_at_time() -> Result<()> {
308 todo!()
309 }
310
311 fn sign_up_then_send_validation_too_late() -> Result<()> {
312 todo!()
313 }
314
315 //fn sign_up_then_send_validation_then_sign_in()
316 }