From cdb883c3c4ccbb82774ecfbfad059f3392e75432 Mon Sep 17 00:00:00 2001 From: Greg Burri Date: Sat, 19 Nov 2022 00:16:07 +0100 Subject: [PATCH] Add some data access methods to Connection --- backend/sql/version_1.sql | 95 +++++++++++++++------------ backend/src/db.rs | 78 ++++++++++++++-------- backend/src/main.rs | 56 +++++++++++----- backend/src/model.rs | 59 ++++++++++------- backend/templates/base_with_list.html | 4 +- backend/templates/home.html | 2 +- 6 files changed, 180 insertions(+), 114 deletions(-) diff --git a/backend/sql/version_1.sql b/backend/sql/version_1.sql index d68982d..9673d25 100644 --- a/backend/sql/version_1.sql +++ b/backend/sql/version_1.sql @@ -1,64 +1,73 @@ -- Version 1 is the initial structure. -CREATE TABLE Version ( - id INTEGER PRIMARY KEY, - version INTEGER NOT NULL UNIQUE, - datetime DATETIME +CREATE TABLE [Version] ( + [id] INTEGER PRIMARY KEY, + [version] INTEGER NOT NULL UNIQUE, + [datetime] DATETIME ); -CREATE TABLE User ( - id INTEGER PRIMARY KEY, - email TEXT NOT NULL, - password TEXT NOT NULL, -- Hashed and salted. - name TEXT NOT NULL +CREATE TABLE [User] ( + [id] INTEGER PRIMARY KEY, + [email] TEXT NOT NULL, + [password] TEXT NOT NULL, -- Hashed and salted. + [name] TEXT NOT NULL ); -CREATE TABLE Recipe ( - id INTEGER PRIMARY KEY, - user_id INTEGER NOT NULL, - title TEXT NOT NULL, - estimate_time INTEGER, - description DATETIME, +CREATE TABLE [Recipe] ( + [id] INTEGER PRIMARY KEY, + [user_id] INTEGER NOT NULL, + [title] TEXT NOT NULL, + [estimate_time] INTEGER, + [description] TEXT, - FOREIGN KEY(user_id) REFERENCES User(id) + FOREIGN KEY([user_id]) REFERENCES [User]([id]) ); -CREATE TABLE Quantity ( - id INTEGER PRIMARY KEY, - value REAL, - unit TEXT +CREATE TABLE [Quantity] ( + [id] INTEGER PRIMARY KEY, + [value] REAL, + [unit] TEXT ); -CREATE TABLE Ingredient ( - id INTEGER PRIMARY KEY, - name TEXT NOT NULL, - quantity_id INTEGER, - input_step_id INTEGER NOT NULL, +CREATE TABLE [Ingredient] ( + [id] INTEGER PRIMARY KEY, + [name] TEXT NOT NULL, + [quantity_id] INTEGER, + [input_step_id] INTEGER NOT NULL, - FOREIGN KEY(quantity_id) REFERENCES Quantity(id), - FOREIGN KEY(input_step_id) REFERENCES Step(id) + FOREIGN KEY([quantity_id]) REFERENCES Quantity([id]), + FOREIGN KEY([input_step_id]) REFERENCES Step([id]) ); CREATE TABLE [Group] ( - id INTEGER PRIMARY KEY, - name TEXT + [id] INTEGER PRIMARY KEY, + [order] INTEGER NOT NULL DEFAULT 0, + [recipe_id] INTEGER, + name TEXT, + + FOREIGN KEY([recipe_id]) REFERENCES [Recipe]([id]) ); -CREATE TABLE Step ( - id INTEGER PRIMARY KEY, - action TEXT NOT NULL, - group_id INTEGER NOT NULL, +CREATE INDEX [Group_order_index] ON [Group] ([order]); + +CREATE TABLE [Step] ( + [id] INTEGER PRIMARY KEY, + [order] INTEGER NOT NULL DEFAULT 0, + [action] TEXT NOT NULL, + [group_id] INTEGER NOT NULL, FOREIGN KEY(group_id) REFERENCES [Group](id) ); -CREATE TABLE IntermediateSubstance ( - id INTEGER PRIMARY KEY, - name TEXT NOT NULL, - quantity_id INTEGER, - output_step_id INTEGER NOT NULL, - input_step_id INTEGER NOT NULL, +CREATE INDEX [Step_order_index] ON [Group] ([order]); - FOREIGN KEY(quantity_id) REFERENCES Quantity(id), - FOREIGN KEY(output_step_id) REFERENCES Step(id), - FOREIGN KEY(input_step_id) REFERENCES Step(id) -); \ No newline at end of file +CREATE TABLE [IntermediateSubstance] ( + [id] INTEGER PRIMARY KEY, + [name] TEXT NOT NULL, + [quantity_id] INTEGER, + [output_step_id] INTEGER NOT NULL, + [input_step_id] INTEGER NOT NULL, + + FOREIGN KEY([quantity_id]) REFERENCES [Quantity]([id]), + FOREIGN KEY([output_step_id]) REFERENCES [Step]([id]), + FOREIGN KEY([input_step_id]) REFERENCES [Step]([id]) +); diff --git a/backend/src/db.rs b/backend/src/db.rs index 1d38a45..2bf383b 100644 --- a/backend/src/db.rs +++ b/backend/src/db.rs @@ -1,14 +1,14 @@ -use crate::consts::SQL_FILENAME; - -use super::consts; - use std::{fs::{self, File}, path::Path, io::Read}; +use itertools::Itertools; //use rusqlite::types::ToSql; //use rusqlite::{Connection, Result, NO_PARAMS}; use r2d2::Pool; use r2d2_sqlite::SqliteConnectionManager; +use crate::consts; +use crate::model; + const CURRENT_DB_VERSION: u32 = 1; #[derive(Debug)] @@ -19,30 +19,28 @@ pub enum DBError { Other(String), } -pub struct Connection { - //con: rusqlite::Connection - pool: Pool -} - -pub struct Recipe { - pub title: String, - pub id: i32, -} - -impl std::convert::From for DBError { +impl From for DBError { fn from(error: rusqlite::Error) -> Self { DBError::SqliteError(error) } } -impl std::convert::From for DBError { +impl From for DBError { fn from(error: r2d2::Error) -> Self { DBError::R2d2Error(error) } } +type Result = std::result::Result; + +#[derive(Clone)] +pub struct Connection { + //con: rusqlite::Connection + pool: Pool +} + impl Connection { - pub fn new() -> Result { + pub fn new() -> Result { let data_dir = Path::new(consts::DB_DIRECTORY); @@ -62,11 +60,7 @@ impl Connection { * 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(); - + fn create_or_update(&self) -> Result<()> { // Check the Database version. let mut con = self.pool.get()?; let tx = con.transaction()?; @@ -78,7 +72,7 @@ impl Connection { [], |row| row.get::(0) ) { - Ok(_) => tx.query_row("SELECT [version] FROM [Version]", [], |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 } }; @@ -92,14 +86,18 @@ impl Connection { Ok(()) } - fn update_to_next_version(current_version: u32, tx: &rusqlite::Transaction) -> Result { + fn update_to_next_version(current_version: u32, tx: &rusqlite::Transaction) -> Result { let next_version = current_version + 1; if next_version <= CURRENT_DB_VERSION { println!("Update to version {}...", next_version); } - fn ok(updated: bool) -> Result { + 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 { if updated { println!("Version updated"); } @@ -109,6 +107,7 @@ impl Connection { match next_version { 1 => { tx.execute_batch(&load_sql_file(next_version)?)?; + update_version(next_version, tx)?; ok(true) } @@ -122,13 +121,36 @@ impl Connection { } } - pub fn get_all_recipes() { + pub fn get_all_recipe_titles(&self) -> Result> { + 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) + } + + pub fn get_all_recipes(&self) -> Result> { + 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 { + 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) } } -fn load_sql_file(version: u32) -> Result { - let sql_file = SQL_FILENAME.replace("{VERSION}", &version.to_string()); +fn load_sql_file(version: u32) -> Result { + let sql_file = consts::SQL_FILENAME.replace("{VERSION}", &version.to_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())))?; diff --git a/backend/src/main.rs b/backend/src/main.rs index b483ce2..5c6de4c 100644 --- a/backend/src/main.rs +++ b/backend/src/main.rs @@ -1,15 +1,13 @@ -use std::io::prelude::*; -use std::{fs::File, env::args}; +use std::fs::File; +use std::sync::Mutex; use actix_files as fs; -use actix_web::{get, web, Responder, middleware, App, HttpServer, HttpResponse, HttpRequest, web::Query}; - +use actix_web::{get, web, Responder, middleware, App, HttpServer, HttpRequest}; use askama_actix::Template; +use clap::Parser; use ron::de::from_reader; use serde::Deserialize; -use itertools::Itertools; - mod consts; mod model; mod db; @@ -17,14 +15,14 @@ mod db; #[derive(Template)] #[template(path = "home.html")] struct HomeTemplate { - recipes: Vec + recipes: Vec<(i32, String)>, } #[derive(Template)] #[template(path = "view_recipe.html")] struct ViewRecipeTemplate { - recipes: Vec, - current_recipe: db::Recipe + recipes: Vec<(i32, String)>, + current_recipe: model::Recipe, } #[derive(Deserialize)] @@ -33,13 +31,16 @@ pub struct Request { } #[get("/")] -async fn home_page(req: HttpRequest) -> impl Responder { - HomeTemplate { recipes: vec![ db::Recipe { title: String::from("Saumon en croûte feuilletée"), id: 1 }, db::Recipe { title: String::from("Croissant au jambon"), id: 2 } ] } +async fn home_page(req: HttpRequest, connection: web::Data) -> impl Responder { + HomeTemplate { recipes: connection.get_all_recipe_titles().unwrap() } // TODO: unwrap. } #[get("/recipe/view/{id}")] -async fn view_page(req: HttpRequest, path: web::Path<(i32,)>) -> impl Responder { - ViewRecipeTemplate { recipes: vec![ db::Recipe { title: String::from("Saumon en croûte feuilletée"), id: 1 }, db::Recipe { title: String::from("Croissant au jambon"), id: 2 } ], current_recipe: db::Recipe { title: String::from("Saumon en croûte feuilletée"), id: 1 } } +async fn view_recipe(req: HttpRequest, path: web::Path<(i32,)>, connection: web::Data) -> impl Responder { + ViewRecipeTemplate { + recipes: connection.get_all_recipe_titles().unwrap(), + current_recipe: connection.get_recipe(path.0).unwrap(), + } } #[derive(Debug, Deserialize)] @@ -72,18 +73,19 @@ async fn main() -> std::io::Result<()> { println!("Configuration: {:?}", config); - // let database_connection = db::create_or_update(); + let db_connection = web::Data::new(db::Connection::new().unwrap()); // TODO: remove unwrap. std::env::set_var("RUST_LOG", "actix_web=info"); let mut server = HttpServer::new( - || { + move || { App::new() .wrap(middleware::Logger::default()) .wrap(middleware::Compress::default()) + .app_data(db_connection.clone()) .service(home_page) - .service(view_page) + .service(view_recipe) .service(fs::Files::new("/static", "static").show_files_listing()) } ); @@ -93,7 +95,27 @@ async fn main() -> std::io::Result<()> { server.run().await } +#[derive(Parser, Debug)] +struct Args { + #[arg(long)] + test: bool +} + fn process_args() -> bool { + let args = Args::parse(); + + if args.test { + if let Err(error) = db::Connection::new() { + println!("Error: {:?}", error) + } + return true; + } + + false + + /* + + fn print_usage() { println!("Usage:"); println!(" {} [--help] [--test]", get_exe_name()); @@ -111,6 +133,6 @@ fn process_args() -> bool { } return true } - false + */ } diff --git a/backend/src/model.rs b/backend/src/model.rs index d012bc9..4984cdd 100644 --- a/backend/src/model.rs +++ b/backend/src/model.rs @@ -1,44 +1,57 @@ -struct Recipe { - title: String, - estimate_time: Option, // [min]. - difficulty: Option, +pub struct Recipe { + pub id: i32, + pub title: String, + pub estimate_time: Option, // [min]. + pub difficulty: Option, //ingredients: Vec, // For four people. - process: Vec, + pub process: Vec, } -struct Ingredient { - quantity: Option, - name: String, +impl Recipe { + pub fn new(id: i32, title: String) -> Recipe { + Recipe { + id, + title, + estimate_time: None, + difficulty: None, + process: Vec::new(), + } + } } -struct Quantity { - value: f32, - unit: String, +pub struct Ingredient { + pub quantity: Option, + pub name: String, } -struct Group { - name: Option, - steps: Vec, +pub struct Quantity { + pub value: f32, + pub unit: String, } -struct Step { - action: String, - input: Vec, - output: Vec, +pub struct Group { + pub name: Option, + pub steps: Vec, } -struct IntermediateSubstance { - name: String, - quantity: Option, +pub struct Step { + pub action: String, + pub input: Vec, + pub output: Vec, } -enum StepInput { +pub struct IntermediateSubstance { + pub name: String, + pub quantity: Option, +} + +pub enum StepInput { Ingredient(Ingredient), IntermediateSubstance(IntermediateSubstance), } -enum Difficulty { +pub enum Difficulty { Unknown, Easy, Medium, diff --git a/backend/templates/base_with_list.html b/backend/templates/base_with_list.html index d3f20ee..fb008aa 100644 --- a/backend/templates/base_with_list.html +++ b/backend/templates/base_with_list.html @@ -3,8 +3,8 @@ {% block main_container %}
diff --git a/backend/templates/home.html b/backend/templates/home.html index b828dc7..ada913f 100644 --- a/backend/templates/home.html +++ b/backend/templates/home.html @@ -2,6 +2,6 @@ {% block content %} -*** HOME - PUT SOMETHING HERE *** +HOME: TODO {% endblock %} \ No newline at end of file -- 2.45.2