diff options
Diffstat (limited to 'crates/rocie-server/src/storage')
13 files changed, 435 insertions, 23 deletions
diff --git a/crates/rocie-server/src/storage/migrate/mod.rs b/crates/rocie-server/src/storage/migrate/mod.rs index 3fdc400..ae0732b 100644 --- a/crates/rocie-server/src/storage/migrate/mod.rs +++ b/crates/rocie-server/src/storage/migrate/mod.rs @@ -198,7 +198,7 @@ pub(crate) mod db_version_parse { } /// Returns the current data as UNIX time stamp. -fn get_current_date() -> i64 { +pub(crate) fn get_current_date() -> i64 { let start = SystemTime::now(); let seconds_since_epoch: TimeDelta = TimeDelta::from_std( start diff --git a/crates/rocie-server/src/storage/migrate/sql/0->1.sql b/crates/rocie-server/src/storage/migrate/sql/0->1.sql index 5aa497d..fd48c68 100644 --- a/crates/rocie-server/src/storage/migrate/sql/0->1.sql +++ b/crates/rocie-server/src/storage/migrate/sql/0->1.sql @@ -19,13 +19,45 @@ CREATE TABLE version ( -- TODO: Fix the possibility for cyclic parent-ship entries <2025-09-05> CREATE TABLE parents ( id TEXT UNIQUE NOT NULL PRIMARY KEY, - parent TEXT DEFAULT NULL CHECK (id IS NOT parent), + parent TEXT DEFAULT NULL CHECK ( + id IS NOT parent + ), FOREIGN KEY(parent) REFERENCES parents(id) ) STRICT; +-- Record with barcodes were bought, and how much of this buy is already used up. +CREATE TABLE buys ( + buy_id TEXT UNIQUE NOT NULL PRIMARY KEY, + barcode_id INTEGER NOT NULL, + used_amount INTEGER DEFAULT NULL CHECK (used_amount > 0), + timestamp INTEGER NOT NULL, + FOREIGN KEY(barcode_id) REFERENCES barcodes(id) +) STRICT; + +CREATE TRIGGER used_amount_lower_or_equal_than_actual_amount_update +BEFORE UPDATE OF used_amount ON buys +FOR EACH ROW +BEGIN + SELECT RAISE(FAIL, "Used amount higher than actual amount") + FROM barcodes + WHERE NEW.barcode_id = barcodes.id + AND NEW.used_amount > barcodes.amount; +END; + +CREATE TRIGGER used_amount_lower_or_equal_than_actual_amount_insert +BEFORE INSERT ON buys +FOR EACH ROW +BEGIN + SELECT RAISE(FAIL, "Used amount higher than actual amount") + FROM barcodes + WHERE NEW.barcode_id = barcodes.id + AND NEW.used_amount > barcodes.amount; +END; + + CREATE TABLE products ( id TEXT UNIQUE NOT NULL PRIMARY KEY, - name TEXT NOT NULL, + name TEXT NOT NULL, description TEXT, parent TEXT DEFAULT NULL, FOREIGN KEY(parent) REFERENCES parents(id) diff --git a/crates/rocie-server/src/storage/sql/barcode.rs b/crates/rocie-server/src/storage/sql/barcode.rs new file mode 100644 index 0000000..239ed8c --- /dev/null +++ b/crates/rocie-server/src/storage/sql/barcode.rs @@ -0,0 +1,30 @@ +use serde::{Deserialize, Serialize}; +use utoipa::ToSchema; + +use crate::storage::sql::unit::UnitId; + +#[derive(ToSchema, Debug, Clone, Serialize, Deserialize)] +pub(crate) struct Barcode { + #[schema(format = Int64, minimum = 0)] + pub(crate) id: BarcodeId, + pub(crate) amount: UnitAmount, +} + +#[derive(ToSchema, Debug, Clone, Copy, Serialize, Deserialize)] +pub(crate) struct BarcodeId(u32); + +impl BarcodeId { + pub(crate) fn to_db(self) -> i64 { + i64::from(self.0) + } + pub(crate) fn from_db(val: i64) -> Self { + Self(u32::try_from(val).expect("Should be strictly positive")) + } +} + +#[derive(ToSchema, Debug, Clone, Copy, Serialize, Deserialize)] +pub(crate) struct UnitAmount { + #[schema(format = Int64, minimum = 0)] + pub(crate) value: u32, + pub(crate) unit: UnitId, +} diff --git a/crates/rocie-server/src/storage/sql/get/barcode/mod.rs b/crates/rocie-server/src/storage/sql/get/barcode/mod.rs new file mode 100644 index 0000000..7b656b1 --- /dev/null +++ b/crates/rocie-server/src/storage/sql/get/barcode/mod.rs @@ -0,0 +1,50 @@ +use crate::{ + app::App, + storage::sql::{ + barcode::{Barcode, BarcodeId, UnitAmount}, + unit::UnitId, + }, +}; + +use sqlx::query; + +impl Barcode { + pub(crate) async fn from_id(app: &App, id: BarcodeId) -> Result<Option<Self>, from_id::Error> { + let db_id = id.to_db(); + + let record = query!( + " + SELECT product_id, amount, unit + FROM barcodes + WHERE id = ? +", + db_id + ) + .fetch_optional(&app.db) + .await?; + + if let Some(record) = record { + Ok(Some(Self { + id, + amount: UnitAmount { + value: u32::try_from(record.amount).expect("Is strictly positive"), + unit: UnitId::from_db(&record.unit), + }, + })) + } else { + Ok(None) + } + } +} + +pub(crate) mod from_id { + use actix_web::ResponseError; + + #[derive(thiserror::Error, Debug)] + pub(crate) enum Error { + #[error("Failed to execute the sql query")] + SqlError(#[from] sqlx::Error), + } + + impl ResponseError for Error {} +} diff --git a/crates/rocie-server/src/storage/sql/get/mod.rs b/crates/rocie-server/src/storage/sql/get/mod.rs index fa22f81..048cb3d 100644 --- a/crates/rocie-server/src/storage/sql/get/mod.rs +++ b/crates/rocie-server/src/storage/sql/get/mod.rs @@ -1,2 +1,4 @@ pub(crate) mod product; +pub(crate) mod product_amount; pub(crate) mod unit; +pub(crate) mod barcode; diff --git a/crates/rocie-server/src/storage/sql/get/product/mod.rs b/crates/rocie-server/src/storage/sql/get/product/mod.rs index d23297a..541f388 100644 --- a/crates/rocie-server/src/storage/sql/get/product/mod.rs +++ b/crates/rocie-server/src/storage/sql/get/product/mod.rs @@ -1,7 +1,8 @@ use crate::{ app::App, storage::sql::{ - product::{Barcode, Product, ProductId, UnitAmount}, + barcode::{Barcode, BarcodeId, UnitAmount}, + product::{Product, ProductId}, unit::UnitId, }, }; @@ -63,7 +64,7 @@ impl Product { associated_bar_codes: barcodes .into_iter() .map(|record| Barcode { - id: u32::try_from(record.id).expect("Should be strictly positive"), + id: BarcodeId::from_db(record.id), amount: UnitAmount { value: u32::try_from(record.amount) .expect("Should be strictly positve"), diff --git a/crates/rocie-server/src/storage/sql/get/product_amount/mod.rs b/crates/rocie-server/src/storage/sql/get/product_amount/mod.rs new file mode 100644 index 0000000..7700274 --- /dev/null +++ b/crates/rocie-server/src/storage/sql/get/product_amount/mod.rs @@ -0,0 +1,56 @@ +use crate::{ + app::App, + storage::sql::{ + barcode::UnitAmount, product::ProductId, product_amount::ProductAmount, unit::UnitId, + }, +}; + +use sqlx::query; + +impl ProductAmount { + pub(crate) async fn from_id( + app: &App, + product_id: ProductId, + ) -> Result<Option<Self>, from_id::Error> { + let record = query!( + r#" +SELECT + SUM(barcodes.amount - IFNULL(buys.used_amount,0)) "available_amount: u32", + barcodes.unit +FROM barcodes + JOIN products ON products.id = ? + JOIN buys ON buys.barcode_id = barcodes.id +GROUP BY barcodes.unit; +"#, + product_id + ) + .fetch_all(&app.db) + .await?; + + assert!(record.len() <= 1, "We currently only support one unit"); + + if let Some(record) = record.first() { + Ok(Some(Self { + product_id, + amount: UnitAmount { + value: record.available_amount.expect("Should always be set"), + unit: UnitId::from_db(&record.unit), + }, + })) + } else { + Ok(None) + } + } +} + +pub(crate) mod from_id { + use actix_web::ResponseError; + + #[derive(thiserror::Error, Debug)] + pub(crate) enum Error { + #[error("Failed to execute the sql query")] + SqlError(#[from] sqlx::Error), + } + + impl ResponseError for Error {} +} diff --git a/crates/rocie-server/src/storage/sql/insert/barcode/mod.rs b/crates/rocie-server/src/storage/sql/insert/barcode/mod.rs new file mode 100644 index 0000000..62a2e11 --- /dev/null +++ b/crates/rocie-server/src/storage/sql/insert/barcode/mod.rs @@ -0,0 +1,241 @@ +use std::str::FromStr; + +use serde::{Deserialize, Serialize}; +use sqlx::query; +use uuid::Uuid; + +use crate::{ + app::App, + storage::{ + migrate::get_current_date, + sql::{ + barcode::{Barcode, BarcodeId, UnitAmount}, + insert::{Operations, Transactionable}, + unit::Unit, + }, + }, +}; + +#[derive(Debug, Deserialize, Serialize)] +pub(crate) enum Operation { + Buy { + buy_id: Uuid, + id: BarcodeId, + }, + Consume { + buy_id: Uuid, + id: BarcodeId, + amount: UnitAmount, + }, +} + +impl Transactionable for Operation { + type ApplyError = apply::Error; + type UndoError = undo::Error; + + async fn apply(self, txn: &mut sqlx::SqliteConnection) -> Result<(), apply::Error> { + match self { + Operation::Buy { buy_id, id } => { + let id = id.to_db(); + let buy_id = buy_id.to_string(); + let timestamp = get_current_date(); + + query!( + " + INSERT INTO buys (buy_id, barcode_id, timestamp) + VALUES (?, ?, ?) +", + buy_id, + id, + timestamp, + ) + .execute(txn) + .await?; + } + Operation::Consume { buy_id, id, amount } => { + let id = id.to_db(); + let buy_id = buy_id.to_string(); + + let old_amount = { + let record = query!( + " + SELECT used_amount + FROM buys + WHERE buy_id = ?; +", + buy_id + ) + .fetch_one(&mut *txn) + .await?; + + u32::try_from(record.used_amount.unwrap_or(0)) + .expect("Should be strictly positive") + }; + + // TODO: Check, that this does not overflow the maximum amount. <2025-09-21> + let new_amount = amount.value + old_amount; + + // TODO(@bpeetz): We need to add the amount. <2025-09-09> + query!( + " + UPDATE buys + SET used_amount = ? + WHERE barcode_id = ? AND buy_id = ? +", + new_amount, + id, + buy_id + ) + .execute(txn) + .await?; + } + } + Ok(()) + } + + async fn undo(self, txn: &mut sqlx::SqliteConnection) -> Result<(), undo::Error> { + match self { + Operation::Buy { buy_id, id } => { + let id = id.to_db(); + let buy_id = buy_id.to_string(); + + query!( + " + DELETE FROM buys + WHERE barcode_id = ? AND buy_id = ? +", + id, + buy_id + ) + .execute(txn) + .await?; + } + Operation::Consume { buy_id, id, amount } => { + todo!("We would need to subtract the amount."); + } + } + Ok(()) + } +} + +pub(crate) mod undo { + #[derive(thiserror::Error, Debug)] + pub(crate) enum Error { + #[error("Failed to execute undo sql statments: {0}")] + SqlError(#[from] sqlx::Error), + } +} +pub(crate) mod apply { + #[derive(thiserror::Error, Debug)] + pub(crate) enum Error { + #[error("Failed to execute apply sql statments: {0}")] + SqlError(#[from] sqlx::Error), + } +} + +impl Barcode { + pub(crate) fn buy(&self, ops: &mut Operations<Operation>) { + let id = Uuid::new_v4(); + ops.push(Operation::Buy { + buy_id: id, + id: self.id, + }); + } + + pub(crate) async fn consume( + &self, + app: &App, + amount: UnitAmount, + ops: &mut Operations<Operation>, + ) -> Result<(), consume::Error> { + assert_eq!( + self.amount.unit, amount.unit, + "We currently do not support unit conversions yet" + ); + + if amount.value > self.amount.value { + let foreign_amount_unit = Unit::from_id(app, amount.unit).await?; + + if let Some(foreign_amount_unit) = foreign_amount_unit { + let self_amount_unit = Unit::from_id(app, self.amount.unit) + .await? + .expect("This unit id should always exist"); + + return Err(consume::Error::ConsumedMoreThanAvailable { + consumed: Box::new((amount, foreign_amount_unit)), + available: Box::new((self.amount, self_amount_unit)), + }); + } + + return Err(consume::Error::UnitIdDoesNotExist(amount.unit)); + } + + let barcode_id = self.id.to_db(); + let buy_id = { + let record = query!( + " + SELECT buy_id + FROM buys + WHERE barcode_id = ? AND (used_amount IS NULL OR used_amount < ?) + ORDER BY timestamp DESC + LIMIT 1; +", + barcode_id, + self.amount.value + ) + .fetch_optional(&app.db) + .await?; + + if let Some(found) = record { + Uuid::from_str(&found.buy_id).expect("Was a uuid, should still be one") + } else { + return Err(consume::Error::NoMoreAvailable); + } + }; + + ops.push(Operation::Consume { + id: self.id, + amount, + buy_id, + }); + + Ok(()) + } +} + +pub(crate) mod consume { + use actix_web::ResponseError; + + use crate::storage::{ + self, + sql::{ + barcode::UnitAmount, + unit::{Unit, UnitId}, + }, + }; + + #[derive(thiserror::Error, Debug)] + pub(crate) enum Error { + #[error("Failed to execute apply sql statments: {0}")] + Sql(#[from] sqlx::Error), + + #[error("Failed to fetch an unit from a specified amount id value")] + UnitGet(#[from] storage::sql::get::unit::from_id::Error), + + #[error("The specified unit-id does not exist: {0}")] + UnitIdDoesNotExist(UnitId), + + #[error("No more of this product is available, buy more. ")] + NoMoreAvailable, + + #[error( + "Consumed more than available: consumed {} {}, but available: {} {}", consumed.0.value, consumed.1.short_name, available.0.value, available.1.short_name, + )] + ConsumedMoreThanAvailable { + consumed: Box<(UnitAmount, Unit)>, + available: Box<(UnitAmount, Unit)>, + }, + } + + impl ResponseError for Error {} +} diff --git a/crates/rocie-server/src/storage/sql/insert/mod.rs b/crates/rocie-server/src/storage/sql/insert/mod.rs index eec6ad2..e6728d9 100644 --- a/crates/rocie-server/src/storage/sql/insert/mod.rs +++ b/crates/rocie-server/src/storage/sql/insert/mod.rs @@ -7,6 +7,7 @@ use log::{debug, trace}; use serde::{Serialize, de::DeserializeOwned}; use sqlx::{SqliteConnection, query}; +pub(crate) mod barcode; pub(crate) mod product; pub(crate) mod unit; diff --git a/crates/rocie-server/src/storage/sql/insert/product/mod.rs b/crates/rocie-server/src/storage/sql/insert/product/mod.rs index b6dd604..e14d3f4 100644 --- a/crates/rocie-server/src/storage/sql/insert/product/mod.rs +++ b/crates/rocie-server/src/storage/sql/insert/product/mod.rs @@ -3,8 +3,9 @@ use sqlx::query; use uuid::Uuid; use crate::storage::sql::{ + barcode::Barcode, insert::{Operations, Transactionable}, - product::{Barcode, Product, ProductId}, + product::{Product, ProductId}, }; #[derive(Debug, Deserialize, Serialize)] @@ -47,7 +48,7 @@ impl Transactionable for Operation { .await?; } Operation::AssociateBarcode { id, barcode } => { - let barcode_id = i64::from(barcode.id); + let barcode_id = barcode.id.to_db(); let barcode_amount_value = i64::from(barcode.amount.value); let barcode_amount_unit = barcode.amount.unit; @@ -90,7 +91,7 @@ impl Transactionable for Operation { .await?; } Operation::AssociateBarcode { id, barcode } => { - let barcode_id = i64::from(barcode.id); + let barcode_id = barcode.id.to_db(); let barcode_amount_value = i64::from(barcode.amount.value); let barcode_amount_unit = barcode.amount.unit; diff --git a/crates/rocie-server/src/storage/sql/mod.rs b/crates/rocie-server/src/storage/sql/mod.rs index f5ad88a..edce187 100644 --- a/crates/rocie-server/src/storage/sql/mod.rs +++ b/crates/rocie-server/src/storage/sql/mod.rs @@ -3,4 +3,6 @@ pub(crate) mod insert; // Types pub(crate) mod product; +pub(crate) mod product_amount; pub(crate) mod unit; +pub(crate) mod barcode; diff --git a/crates/rocie-server/src/storage/sql/product.rs b/crates/rocie-server/src/storage/sql/product.rs index e2a4f0d..93cc6a0 100644 --- a/crates/rocie-server/src/storage/sql/product.rs +++ b/crates/rocie-server/src/storage/sql/product.rs @@ -5,7 +5,7 @@ use sqlx::{Database, Encode, Type}; use utoipa::ToSchema; use uuid::Uuid; -use crate::storage::sql::unit::{Unit, UnitId}; +use crate::storage::sql::barcode::Barcode; #[derive(Clone, ToSchema, Serialize, Deserialize)] pub(crate) struct Product { @@ -58,17 +58,3 @@ where <String as Type<DB>>::type_info() } } - -#[derive(ToSchema, Debug, Clone, Serialize, Deserialize)] -pub(crate) struct Barcode { - #[schema(format = Int64, minimum = 0)] - pub(crate) id: u32, - pub(crate) amount: UnitAmount, -} - -#[derive(ToSchema, Debug, Clone, Serialize, Deserialize)] -pub(crate) struct UnitAmount { - #[schema(format = Int64, minimum = 0)] - pub(crate) value: u32, - pub(crate) unit: UnitId, -} diff --git a/crates/rocie-server/src/storage/sql/product_amount.rs b/crates/rocie-server/src/storage/sql/product_amount.rs new file mode 100644 index 0000000..232c5db --- /dev/null +++ b/crates/rocie-server/src/storage/sql/product_amount.rs @@ -0,0 +1,10 @@ +use serde::{Deserialize, Serialize}; +use utoipa::ToSchema; + +use crate::storage::sql::{barcode::UnitAmount, product::ProductId}; + +#[derive(Clone, ToSchema, Serialize, Deserialize)] +pub(crate) struct ProductAmount { + pub(crate) product_id: ProductId, + pub(crate) amount: UnitAmount, +} |