From 8dcaac35b7fb5bcd845b82be6d3d4bf186286a19 Mon Sep 17 00:00:00 2001 From: Greg Burri Date: Tue, 18 Mar 2025 19:55:52 +0100 Subject: [PATCH] Doc --- README.md | 128 ++++++----- backend/sql/version_1.sql | 471 +++++++++++++++++++------------------- 2 files changed, 301 insertions(+), 298 deletions(-) diff --git a/README.md b/README.md index 4c7fb44..590a690 100644 --- a/README.md +++ b/README.md @@ -1,63 +1,65 @@ -# Technical - -## Backend - -### Launch Axum - -In directory '/backend' type: - $> cargo run - -Then browse http://127.0.0.1:8082 (You need to compile the wasm file first, see section 'Frontend') - -At first launch the configuration file '/backend/conf.ron' is created. It contains the port the server will listen to and information about the SMTP server which will be used to send email when a user sign up or change its password. - -### Autoreload - -First install cargo watch: - $> cargo install cargo-watch - -In directory '/backend' type: - $> cargo watch -x run - - -## Frontend - -### Tools needed - -nushell: https://www.nushell.sh/ -trunk: https://trunkrs.dev - -### Compilation - -In directory '/frontend' type: - $> nu deploy.nu - -It will create the '/frontend/pkg' directory and copy the wasm file into '/backend/static'. -You can now refresh your browser to reload the wasm file. - -# How-to - -## How to install service on a Linux server - -As root: - -1. Copy '/doc/recipes.service' to '/lib/systemd/system/' -2. Enabled it: #> systemctl enable recipes -3. Launch it: #> systemctl start recipes - -## Cross compile for Raspberry PI on Windows - -* $> https://gnutoolchains.com/raspberry/ -* https://gnutoolchains.com/raspberry64/ - -# Useful URLs - -* Rust patterns : https://github.com/rust-unofficial/patterns -* Rust cheat Sheet: https://cheats.rs/ - - -# Useful tools - -Benchmarking: https://crates.io/crates/oha -HTTP API tool: https://www.usebruno.com/ -GUI Database client: https://dbeaver.io/ +# Technical + +## Backend + +### Launch Axum + +In directory '/backend' type: + $> cargo run + +Then browse http://127.0.0.1:8082 (You need to compile the wasm file first, see section 'Frontend') + +At first launch the configuration file '/backend/conf.ron' is created. It contains the port the server will listen to and information about the SMTP server which will be used to send email when a user sign up or change its password. + +### Autoreload + +First install cargo watch: + $> cargo install cargo-watch + +In directory '/backend' type: + $> cargo watch -x run + + +## Frontend + +### Tools needed + +nushell: https://www.nushell.sh/ +trunk: https://trunkrs.dev + +### Compilation + +In directory '/frontend' type: + $> nu deploy.nu + +It will create the '/frontend/pkg' directory and copy the wasm file into '/backend/static'. +You can now refresh your browser to reload the wasm file. + +# How-to + +## How to install service on a Linux server + +As root: + +1. Copy '/doc/recipes.service' to '/lib/systemd/system/' +2. Enabled it: #> systemctl enable recipes +3. Launch it: #> systemctl start recipes + +## Cross compile for Raspberry PI on Windows + +* $> https://gnutoolchains.com/raspberry/ +* https://gnutoolchains.com/raspberry64/ + +# Useful URLs + +* Rust patterns: https://github.com/rust-unofficial/patterns +* Rust cheat Sheet: https://cheats.rs/ +* HTML Validation Service: https://validator.w3.org/ +* Site analysis: https://pagespeed.web.dev/ +* Open-Source UI: https://uiverse.io/ + +# Useful tools + +HTTP benchmarking and stress tool: https://crates.io/crates/oha +HTTP API tool: https://www.usebruno.com/ +GUI Database client: https://dbeaver.io/ diff --git a/backend/sql/version_1.sql b/backend/sql/version_1.sql index c3edc21..35a841d 100644 --- a/backend/sql/version_1.sql +++ b/backend/sql/version_1.sql @@ -1,235 +1,236 @@ --- Datetimes are stored as 'ISO 8601' text format. --- For example: '2025-01-07T10:41:05.697884837+00:00'. - --- Version 1 is the initial structure. -CREATE TABLE [Version] ( - [id] INTEGER PRIMARY KEY, - [version] INTEGER NOT NULL UNIQUE, - [datetime] TEXT -) STRICT; - -CREATE TABLE [User] ( - [id] INTEGER PRIMARY KEY, - - [email] TEXT NOT NULL, - [name] TEXT NOT NULL DEFAULT '', - [creation_datetime] TEXT NOT NULL, - - [default_servings] INTEGER DEFAULT 4, - [lang] TEXT NOT NULL DEFAULT 'en', - - [password] TEXT NOT NULL, -- argon2(password_plain, salt). - - [validation_token_datetime] TEXT NOT NULL, -- Updated when the validation email is sent. - [validation_token] TEXT, -- If not null then the user has not validated his account yet. - - [password_reset_token] TEXT, -- If not null then the user can reset its password. - -- The time when the reset token has been created. - -- Password can only be reset during a certain duration after this time. - [password_reset_datetime] TEXT, - - [is_admin] INTEGER NOT NULL DEFAULT FALSE -) STRICT; - -CREATE INDEX [validation_token_index] ON [User]([validation_token]); -CREATE UNIQUE INDEX [User_email_index] ON [User]([email]); - -CREATE TABLE [UserLoginToken] ( - [id] INTEGER PRIMARY KEY, - [user_id] INTEGER NOT NULL, - [last_login_datetime] TEXT, - - -- 24 alphanumeric character token. - -- Can be stored in a cookie to be able to authenticate without a password. - [token] TEXT NOT NULL, - - [ip] TEXT, -- Can be ipv4 or ipv6 - [user_agent] TEXT, - - FOREIGN KEY([user_id]) REFERENCES [User]([id]) ON DELETE CASCADE -) STRICT; - -CREATE INDEX [UserLoginToken_token_index] ON [UserLoginToken]([token]); - -CREATE TABLE [Recipe] ( - [id] INTEGER PRIMARY KEY, - [user_id] INTEGER, -- Can be null if a user is deleted. - [title] TEXT NOT NULL, - -- https://en.wikipedia.org/wiki/List_of_ISO_639_language_codes - [lang] TEXT NOT NULL DEFAULT 'en', - [estimated_time] INTEGER, -- in [s]. - [description] TEXT NOT NULL DEFAULT '', - -- 0: Unknown, 1: Easy, 2: Medium, 4: Hard. - [difficulty] INTEGER NOT NULL DEFAULT 0, - [servings] INTEGER DEFAULT 4, - [is_published] INTEGER NOT NULL DEFAULT FALSE, - [creation_datetime] TEXT NOT NULL, - - FOREIGN KEY([user_id]) REFERENCES [User]([id]) ON DELETE SET NULL -) STRICT; - -CREATE TRIGGER [Recipe_trigger_update_difficulty] -BEFORE UPDATE OF [difficulty] -ON [Recipe] -BEGIN - SELECT - CASE - WHEN NEW.[difficulty] < 0 OR NEW.[difficulty] > 3 THEN - RAISE (ABORT, 'Invalid [difficulty] value') - END; -END; - -CREATE TRIGGER [Recipe_trigger_insert_difficulty] -BEFORE INSERT -ON [Recipe] -BEGIN - SELECT - CASE - WHEN NEW.[difficulty] < 0 OR NEW.[difficulty] > 3 THEN - RAISE (ABORT, 'Invalid [difficulty] value') - END; -END; - -CREATE TABLE [Image] ( - [Id] INTEGER PRIMARY KEY, - [recipe_id] INTEGER NOT NULL, - [name] TEXT NOT NULL DEFAULT '', - [description] TEXT NOT NULL DEFAULT '', - [image] BLOB NOT NULL, - - FOREIGN KEY([recipe_id]) REFERENCES [Recipe]([id]) ON DELETE CASCADE -) STRICT; - -CREATE TABLE [RecipeTag] ( - [id] INTEGER PRIMARY KEY, - - [recipe_id] INTEGER NOT NULL, - [tag_id] INTEGER NOT NULL, - - UNIQUE([recipe_id], [tag_id]), - - FOREIGN KEY([recipe_id]) REFERENCES [Recipe]([id]) ON DELETE CASCADE, - FOREIGN KEY([tag_id]) REFERENCES [Tag]([id]) ON DELETE CASCADE -) STRICT; - -CREATE TABLE [Tag] ( - [id] INTEGER PRIMARY KEY, - [name] TEXT NOT NULL - -- https://en.wikipedia.org/wiki/List_of_ISO_639_language_codes -) STRICT; - -CREATE UNIQUE INDEX [Tag_name_lang_index] ON [Tag]([name]); - -CREATE TABLE [Group] ( - [id] INTEGER PRIMARY KEY, - - [order] INTEGER NOT NULL DEFAULT 0, - [recipe_id] INTEGER NOT NULL, - - [name] TEXT NOT NULL DEFAULT '', - [comment] TEXT NOT NULL DEFAULT '', - - FOREIGN KEY([recipe_id]) REFERENCES [Recipe]([id]) ON DELETE CASCADE -) STRICT; - -CREATE INDEX [Group_order_index] ON [Group]([order]); - -CREATE TABLE [Step] ( - [id] INTEGER PRIMARY KEY, - - [order] INTEGER NOT NULL DEFAULT 0, - [group_id] INTEGER NOT NULL, - - [action] TEXT NOT NULL DEFAULT '', - - FOREIGN KEY(group_id) REFERENCES [Group](id) ON DELETE CASCADE -) STRICT; - -CREATE INDEX [Step_order_index] ON [Group]([order]); - -CREATE TABLE [Ingredient] ( - [id] INTEGER PRIMARY KEY, - - [order] INTEGER NOT NULL DEFAULT 0, - [step_id] INTEGER NOT NULL, - - [name] TEXT NOT NULL DEFAULT '', - [quantity_value] REAL, - [quantity_unit] TEXT NOT NULL DEFAULT '', - - [comment] TEXT NOT NULL DEFAULT '', - - FOREIGN KEY([step_id]) REFERENCES [Step]([id]) ON DELETE CASCADE -) STRICT; - -CREATE INDEX [Ingredient_order_index] ON [Ingredient]([order]); - -CREATE TABLE [RecipeScheduled] ( - [id] INTEGER PRIMARY KEY, - [user_id] INTEGER NOT NULL, - [recipe_id] INTEGER NOT NULL, - [date] TEXT NOT NULL, -- In form of 'YYYY-MM-DD'. - [servings] INTEGER, -- If NULL use [recipe].[servings]. - - UNIQUE([user_id], [recipe_id], [date]), - - FOREIGN KEY([user_id]) REFERENCES [User]([id]) ON DELETE CASCADE, - FOREIGN KEY([recipe_id]) REFERENCES [Recipe]([id]) ON DELETE CASCADE -); - -CREATE INDEX [RecipeScheduled_user_id_index] ON [RecipeScheduled]([user_id]); -CREATE INDEX [RecipeScheduled_date_index] ON [RecipeScheduled]([date]); - -CREATE TABLE [ShoppingEntry] ( - [id] INTEGER PRIMARY KEY, - [user_id] INTEGER NOT NULL, - -- The linked ingredient can be deleted or a custom entry can be manually added. - -- In both cases [name], [quantity_value] and [quantity_unit] are used to display - -- the entry instead of [Ingredient] data. - [ingredient_id] INTEGER, - [recipe_scheduled_id] INTEGER, -- Can be null when manually added. - - [is_checked] INTEGER NOT NULL DEFAULT FALSE, - - [name] TEXT NOT NULL DEFAULT '', - [quantity_value] REAL, - [quantity_unit] TEXT NOT NULL DEFAULT '', - [servings] INTEGER, - - FOREIGN KEY([user_id]) REFERENCES [User]([id]) ON DELETE CASCADE, - FOREIGN KEY([ingredient_id]) REFERENCES [Ingredient]([id]) ON DELETE SET NULL, - FOREIGN KEY([recipe_scheduled_id]) REFERENCES [RecipeScheduled]([id]) ON DELETE SET NULL -); - -CREATE INDEX [ShoppingEntry_user_id_index] ON [ShoppingEntry]([user_id]); - --- When an ingredient is deleted, its values are copied to any shopping entry --- that referenced it. -CREATE TRIGGER [Ingredient_trigger_delete] -BEFORE DELETE -ON [Ingredient] -BEGIN - UPDATE [ShoppingEntry] - SET - [name] = OLD.[name], - [quantity_value] = OLD.[quantity_value], - [quantity_unit] = OLD.[quantity_unit], - [servings] = ( - SELECT [servings] - FROM [Recipe] - INNER JOIN [Group] ON [Group].[recipe_id] = [Recipe].[id] - INNER JOIN [Step] ON [Step].[group_id] = [Group].[id] - WHERE [Step].[id] = OLD.[step_id] - ) - WHERE [ingredient_id] = OLD.[id]; -END; - -CREATE TABLE [Settings] ( - [name] TEXT NOT NULL PRIMARY KEY, - - -- Value can by anything that can be read from a text by - -- implementing the trait 'std::str::FromStr'. - [value] TEXT NOT NULL -) STRICT; - -INSERT INTO [Settings] ([name], [value]) VALUES ('new_user_registration_enabled', 'true'); +-- Datetimes are stored as 'ISO 8601' text format. +-- For example: '2025-01-07T10:41:05.697884837+00:00'. + +-- Version 1 is the initial structure. +CREATE TABLE [Version] ( + [id] INTEGER PRIMARY KEY, + [version] INTEGER NOT NULL UNIQUE, + [datetime] TEXT +) STRICT; + +CREATE TABLE [User] ( + [id] INTEGER PRIMARY KEY, + + [email] TEXT NOT NULL, + [name] TEXT NOT NULL DEFAULT '', + [creation_datetime] TEXT NOT NULL, + + [default_servings] INTEGER DEFAULT 4, + [lang] TEXT NOT NULL DEFAULT 'en', + + [password] TEXT NOT NULL, -- argon2(password_plain, salt). + + [validation_token_datetime] TEXT NOT NULL, -- Updated when the validation email is sent. + [validation_token] TEXT, -- If not null then the user has not validated his account yet. + + [password_reset_token] TEXT, -- If not null then the user can reset its password. + -- The time when the reset token has been created. + -- Password can only be reset during a certain duration after this time. + [password_reset_datetime] TEXT, + + [is_admin] INTEGER NOT NULL DEFAULT FALSE +) STRICT; + +CREATE INDEX [validation_token_index] ON [User]([validation_token]); +CREATE UNIQUE INDEX [User_email_index] ON [User]([email]); + +CREATE TABLE [UserLoginToken] ( + [id] INTEGER PRIMARY KEY, + [user_id] INTEGER NOT NULL, + [last_login_datetime] TEXT, + + -- 24 alphanumeric character token. + -- Can be stored in a cookie to be able to authenticate without a password. + [token] TEXT NOT NULL, + + [ip] TEXT, -- Can be ipv4 or ipv6 + [user_agent] TEXT, + + FOREIGN KEY([user_id]) REFERENCES [User]([id]) ON DELETE CASCADE +) STRICT; + +CREATE INDEX [UserLoginToken_token_index] ON [UserLoginToken]([token]); + +CREATE TABLE [Recipe] ( + [id] INTEGER PRIMARY KEY, + [user_id] INTEGER, -- Can be null if a user is deleted. + [title] TEXT NOT NULL, + -- https://en.wikipedia.org/wiki/List_of_ISO_639_language_codes + [lang] TEXT NOT NULL DEFAULT 'en', + [estimated_time] INTEGER, -- in [s]. + [description] TEXT NOT NULL DEFAULT '', + -- 0: Unknown, 1: Easy, 2: Medium, 4: Hard. + [difficulty] INTEGER NOT NULL DEFAULT 0, + [servings] INTEGER DEFAULT 4, + [is_published] INTEGER NOT NULL DEFAULT FALSE, + [creation_datetime] TEXT NOT NULL, + + FOREIGN KEY([user_id]) REFERENCES [User]([id]) ON DELETE SET NULL +) STRICT; + +CREATE TRIGGER [Recipe_trigger_update_difficulty] +BEFORE UPDATE OF [difficulty] +ON [Recipe] +BEGIN + SELECT + CASE + WHEN NEW.[difficulty] < 0 OR NEW.[difficulty] > 3 THEN + RAISE (ABORT, 'Invalid [difficulty] value') + END; +END; + +CREATE TRIGGER [Recipe_trigger_insert_difficulty] +BEFORE INSERT +ON [Recipe] +BEGIN + SELECT + CASE + WHEN NEW.[difficulty] < 0 OR NEW.[difficulty] > 3 THEN + RAISE (ABORT, 'Invalid [difficulty] value') + END; +END; + +CREATE TABLE [Image] ( + [Id] INTEGER PRIMARY KEY, + [recipe_id] INTEGER NOT NULL, + [name] TEXT NOT NULL DEFAULT '', + [description] TEXT NOT NULL DEFAULT '', + [image] BLOB NOT NULL, + + FOREIGN KEY([recipe_id]) REFERENCES [Recipe]([id]) ON DELETE CASCADE +) STRICT; + +CREATE TABLE [RecipeTag] ( + [id] INTEGER PRIMARY KEY, + + [recipe_id] INTEGER NOT NULL, + [tag_id] INTEGER NOT NULL, + + UNIQUE([recipe_id], [tag_id]), + + FOREIGN KEY([recipe_id]) REFERENCES [Recipe]([id]) ON DELETE CASCADE, + FOREIGN KEY([tag_id]) REFERENCES [Tag]([id]) ON DELETE CASCADE +) STRICT; + +CREATE TABLE [Tag] ( + [id] INTEGER PRIMARY KEY, + [name] TEXT NOT NULL + -- https://en.wikipedia.org/wiki/List_of_ISO_639_language_codes +) STRICT; + +CREATE UNIQUE INDEX [Tag_name_lang_index] ON [Tag]([name]); + +CREATE TABLE [Group] ( + [id] INTEGER PRIMARY KEY, + + [order] INTEGER NOT NULL DEFAULT 0, + [recipe_id] INTEGER NOT NULL, + + [name] TEXT NOT NULL DEFAULT '', + [comment] TEXT NOT NULL DEFAULT '', + + FOREIGN KEY([recipe_id]) REFERENCES [Recipe]([id]) ON DELETE CASCADE +) STRICT; + +CREATE INDEX [Group_order_index] ON [Group]([order]); + +CREATE TABLE [Step] ( + [id] INTEGER PRIMARY KEY, + + [order] INTEGER NOT NULL DEFAULT 0, + [group_id] INTEGER NOT NULL, + + [action] TEXT NOT NULL DEFAULT '', + + FOREIGN KEY(group_id) REFERENCES [Group](id) ON DELETE CASCADE +) STRICT; + +CREATE INDEX [Step_order_index] ON [Group]([order]); + +CREATE TABLE [Ingredient] ( + [id] INTEGER PRIMARY KEY, + + [order] INTEGER NOT NULL DEFAULT 0, + [step_id] INTEGER NOT NULL, + + [name] TEXT NOT NULL DEFAULT '', + [quantity_value] REAL, + [quantity_unit] TEXT NOT NULL DEFAULT '', + + [comment] TEXT NOT NULL DEFAULT '', + + FOREIGN KEY([step_id]) REFERENCES [Step]([id]) ON DELETE CASCADE +) STRICT; + +CREATE INDEX [Ingredient_order_index] ON [Ingredient]([order]); + +CREATE TABLE [RecipeScheduled] ( + [id] INTEGER PRIMARY KEY, + [user_id] INTEGER NOT NULL, + [recipe_id] INTEGER NOT NULL, + [date] TEXT NOT NULL, -- In form of 'YYYY-MM-DD'. + [servings] INTEGER, -- If NULL use [recipe].[servings]. + + UNIQUE([user_id], [recipe_id], [date]), + + FOREIGN KEY([user_id]) REFERENCES [User]([id]) ON DELETE CASCADE, + FOREIGN KEY([recipe_id]) REFERENCES [Recipe]([id]) ON DELETE CASCADE +); + +CREATE INDEX [RecipeScheduled_user_id_index] ON [RecipeScheduled]([user_id]); +CREATE INDEX [RecipeScheduled_date_index] ON [RecipeScheduled]([date]); + +CREATE TABLE [ShoppingEntry] ( + [id] INTEGER PRIMARY KEY, + [user_id] INTEGER NOT NULL, + -- The linked ingredient can be deleted or a custom entry can be manually added. + -- In both cases [name], [quantity_value], [quantity_unit] and [Servings] are used to display + -- the entry instead of [Ingredient] data. + [ingredient_id] INTEGER, + [recipe_scheduled_id] INTEGER, -- Can be null when manually added. + + [is_checked] INTEGER NOT NULL DEFAULT FALSE, + + -- The following four fields contain data only if [recipe_scheduled_id] is NULL. + [name] TEXT NOT NULL DEFAULT '', + [quantity_value] REAL, + [quantity_unit] TEXT NOT NULL DEFAULT '', + [servings] INTEGER, + + FOREIGN KEY([user_id]) REFERENCES [User]([id]) ON DELETE CASCADE, + FOREIGN KEY([ingredient_id]) REFERENCES [Ingredient]([id]) ON DELETE SET NULL, + FOREIGN KEY([recipe_scheduled_id]) REFERENCES [RecipeScheduled]([id]) ON DELETE SET NULL +); + +CREATE INDEX [ShoppingEntry_user_id_index] ON [ShoppingEntry]([user_id]); + +-- When an ingredient is deleted, its values are copied to any shopping entry +-- that referenced it. +CREATE TRIGGER [Ingredient_trigger_delete] +BEFORE DELETE +ON [Ingredient] +BEGIN + UPDATE [ShoppingEntry] + SET + [name] = OLD.[name], + [quantity_value] = OLD.[quantity_value], + [quantity_unit] = OLD.[quantity_unit], + [servings] = ( + SELECT [servings] + FROM [Recipe] + INNER JOIN [Group] ON [Group].[recipe_id] = [Recipe].[id] + INNER JOIN [Step] ON [Step].[group_id] = [Group].[id] + WHERE [Step].[id] = OLD.[step_id] + ) + WHERE [ingredient_id] = OLD.[id]; +END; + +CREATE TABLE [Settings] ( + [name] TEXT NOT NULL PRIMARY KEY, + + -- Value can by anything that can be read from a text by + -- implementing the trait 'std::str::FromStr'. + [value] TEXT NOT NULL +) STRICT; + +INSERT INTO [Settings] ([name], [value]) VALUES ('new_user_registration_enabled', 'true'); -- 2.49.0