From 15dfb099b13ad68b4f286fa9def77f083f79e1b2 Mon Sep 17 00:00:00 2001 From: Benedikt Peetz Date: Thu, 19 Mar 2026 05:58:30 +0100 Subject: chore(crates/rocie-server/storage/migrate/sql): Remove special `>` from path Cargo does not allow that for publishing. --- crates/rocie-server/src/storage/migrate/mod.rs | 2 +- .../rocie-server/src/storage/migrate/sql/0-1.sql | 199 +++++++++++++++++++++ .../rocie-server/src/storage/migrate/sql/0->1.sql | 199 --------------------- 3 files changed, 200 insertions(+), 200 deletions(-) create mode 100644 crates/rocie-server/src/storage/migrate/sql/0-1.sql delete mode 100644 crates/rocie-server/src/storage/migrate/sql/0->1.sql diff --git a/crates/rocie-server/src/storage/migrate/mod.rs b/crates/rocie-server/src/storage/migrate/mod.rs index b39b58b..1dbacab 100644 --- a/crates/rocie-server/src/storage/migrate/mod.rs +++ b/crates/rocie-server/src/storage/migrate/mod.rs @@ -151,7 +151,7 @@ impl DbVersion { async fn update(self, app: &App) -> Result<(), update::Error> { match self { Self::Empty => { - make_upgrade! {app, Self::Empty, Self::One, "./sql/0->1.sql"}?; + make_upgrade! {app, Self::Empty, Self::One, "./sql/0-1.sql"}?; Ok(()) } diff --git a/crates/rocie-server/src/storage/migrate/sql/0-1.sql b/crates/rocie-server/src/storage/migrate/sql/0-1.sql new file mode 100644 index 0000000..e8bcd95 --- /dev/null +++ b/crates/rocie-server/src/storage/migrate/sql/0-1.sql @@ -0,0 +1,199 @@ +-- rocie - An enterprise grocery management system +-- +-- Copyright (C) 2026 Benedikt Peetz +-- SPDX-License-Identifier: GPL-3.0-or-later +-- +-- This file is part of Rocie. +-- +-- You should have received a copy of the License along with this program. +-- If not, see . + +-- All tables should be declared STRICT, as I actually like to have types checking (and a +-- db that doesn't lie to me). + +CREATE TABLE version ( + -- The `namespace` is only useful, if other tools ever build on this database + namespace TEXT NOT NULL, + + -- The version. + number INTEGER UNIQUE NOT NULL PRIMARY KEY, + + -- The validity of this version as UNIX time stamp + valid_from INTEGER NOT NULL CHECK (valid_from < valid_to), + -- If set to `NULL`, represents the current version + valid_to INTEGER UNIQUE CHECK (valid_to > valid_from) +) STRICT; + +-- Encodes the tree structure of the product parents. +-- A product parent cannot be a product, but can have parents on it's own. +-- TODO: Fix the possibility for cyclic parent-ship entries <2025-09-05> +CREATE TABLE product_parents ( + id TEXT UNIQUE NOT NULL PRIMARY KEY, + parent TEXT DEFAULT NULL CHECK ( + id IS NOT parent + ), + name TEXT UNIQUE NOT NULL, + description TEXT, + FOREIGN KEY(parent) REFERENCES product_parents(id) +) STRICT; + +-- Encodes the tree structure of the recipe parents. +-- TODO: Fix the possibility for cyclic parent-ship entries <2025-09-05> +CREATE TABLE recipe_parents ( + id TEXT UNIQUE NOT NULL PRIMARY KEY, + parent TEXT DEFAULT NULL CHECK ( + id IS NOT parent + ), + name TEXT UNIQUE NOT NULL, + description TEXT, + FOREIGN KEY(parent) REFERENCES recipe_parents(id) +) STRICT; + +-- Stores the registered users. +CREATE TABLE users ( + id TEXT UNIQUE NOT NULL PRIMARY KEY, + name TEXT UNIQUE NOT NULL, + password_hash TEXT NOT NULL, + description TEXT +) 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 UNIQUE NOT NULL, + description TEXT, + parent TEXT DEFAULT NULL, + unit_property TEXT NOT NULL, + FOREIGN KEY(parent) REFERENCES product_parents(id), + FOREIGN KEY(unit_property) REFERENCES unit_properties(id) +) STRICT; + +CREATE TABLE barcodes ( + id INTEGER UNIQUE NOT NULL PRIMARY KEY, + product_id TEXT NOT NULL, + amount INTEGER NOT NULL, + unit TEXT NOT NULL, + FOREIGN KEY(product_id) REFERENCES products(id), + FOREIGN KEY(unit) REFERENCES units(id) +) STRICT; +CREATE TRIGGER unit_has_property_of_product_update +BEFORE UPDATE OF product_id,unit ON barcodes +FOR EACH ROW +BEGIN + SELECT RAISE(FAIL, "Used different unit_property for product_id and unit") + FROM products, units + WHERE NEW.product_id = products.id + AND NEW.unit = units.id + AND units.unit_property != products.unit_property; +END; + +CREATE TRIGGER unit_has_property_of_product_insert +BEFORE INSERT ON barcodes +FOR EACH ROW +BEGIN + SELECT RAISE(FAIL, "Used different unit_property for product_id and unit") + FROM products, units + WHERE NEW.product_id = products.id + AND NEW.unit = units.id + AND units.unit_property != products.unit_property; +END; + +CREATE TABLE units ( + id TEXT UNIQUE NOT NULL PRIMARY KEY, + unit_property TEXT NOT NULL, + full_name_singular TEXT UNIQUE NOT NULL, + full_name_plural TEXT UNIQUE NOT NULL, + short_name TEXT UNIQUE NOT NULL, + description TEXT, + FOREIGN KEY(unit_property) REFERENCES unit_properties(id) +) STRICT; + +CREATE TABLE unit_properties ( + id TEXT UNIQUE NOT NULL PRIMARY KEY, + name TEXT UNIQUE NOT NULL, + description TEXT +) STRICT; + +CREATE TABLE recipies ( + id TEXT UNIQUE NOT NULL PRIMARY KEY, + name TEXT UNIQUE NOT NULL, + parent TEXT, + content TEXT NOT NULL, + FOREIGN KEY(parent) REFERENCES recipe_parents(id) +) STRICT; + +-- Encodes unit conversions: +-- {factor} {from_unit} = 1 {to_unit} +-- E.g.: 1000 g = 1 kg +CREATE TABLE unit_conversions ( + from_unit TEXT NOT NULL, + to_unit TEXT NOT NULL, + factor REAL NOT NULL, + FOREIGN KEY(from_unit) REFERENCES units(id), + FOREIGN KEY(to_unit) REFERENCES units(id) +) STRICT; +CREATE TRIGGER both_units_have_same_unit_property_update +BEFORE UPDATE OF from_unit,to_unit ON unit_conversions +FOR EACH ROW +BEGIN + SELECT RAISE(FAIL, "Used not the same unit_property") + FROM units, units AS units2 + WHERE NEW.from_unit = units.id + AND NEW.to_unit = units2.id + AND units.unit_property != units2.unit_property; +END; + +CREATE TRIGGER both_units_have_same_unit_property_insert +BEFORE INSERT ON unit_conversions +FOR EACH ROW +BEGIN + SELECT RAISE(FAIL, "Used not the same unit_property") + FROM units, units AS units2 + WHERE NEW.from_unit = units.id + AND NEW.to_unit = units2.id + AND units.unit_property != units2.unit_property; +END; + +-- Log of all the applied operations to this db. +CREATE TABLE txn_log ( + timestamp INTEGER NOT NULL, + operation TEXT NOT NULL +) STRICT; + +CREATE TABLE rocie_config ( + -- Make it impossible to insert more than one value here. + id INTEGER PRIMARY KEY NOT NULL CHECK (id = 0), + use_defaults INTEGER NOT NULL CHECK (use_defaults = 1 OR use_defaults = 0) +) STRICT; + +INSERT INTO rocie_config (id, use_defaults) +VALUES (0, 0); diff --git a/crates/rocie-server/src/storage/migrate/sql/0->1.sql b/crates/rocie-server/src/storage/migrate/sql/0->1.sql deleted file mode 100644 index e8bcd95..0000000 --- a/crates/rocie-server/src/storage/migrate/sql/0->1.sql +++ /dev/null @@ -1,199 +0,0 @@ --- rocie - An enterprise grocery management system --- --- Copyright (C) 2026 Benedikt Peetz --- SPDX-License-Identifier: GPL-3.0-or-later --- --- This file is part of Rocie. --- --- You should have received a copy of the License along with this program. --- If not, see . - --- All tables should be declared STRICT, as I actually like to have types checking (and a --- db that doesn't lie to me). - -CREATE TABLE version ( - -- The `namespace` is only useful, if other tools ever build on this database - namespace TEXT NOT NULL, - - -- The version. - number INTEGER UNIQUE NOT NULL PRIMARY KEY, - - -- The validity of this version as UNIX time stamp - valid_from INTEGER NOT NULL CHECK (valid_from < valid_to), - -- If set to `NULL`, represents the current version - valid_to INTEGER UNIQUE CHECK (valid_to > valid_from) -) STRICT; - --- Encodes the tree structure of the product parents. --- A product parent cannot be a product, but can have parents on it's own. --- TODO: Fix the possibility for cyclic parent-ship entries <2025-09-05> -CREATE TABLE product_parents ( - id TEXT UNIQUE NOT NULL PRIMARY KEY, - parent TEXT DEFAULT NULL CHECK ( - id IS NOT parent - ), - name TEXT UNIQUE NOT NULL, - description TEXT, - FOREIGN KEY(parent) REFERENCES product_parents(id) -) STRICT; - --- Encodes the tree structure of the recipe parents. --- TODO: Fix the possibility for cyclic parent-ship entries <2025-09-05> -CREATE TABLE recipe_parents ( - id TEXT UNIQUE NOT NULL PRIMARY KEY, - parent TEXT DEFAULT NULL CHECK ( - id IS NOT parent - ), - name TEXT UNIQUE NOT NULL, - description TEXT, - FOREIGN KEY(parent) REFERENCES recipe_parents(id) -) STRICT; - --- Stores the registered users. -CREATE TABLE users ( - id TEXT UNIQUE NOT NULL PRIMARY KEY, - name TEXT UNIQUE NOT NULL, - password_hash TEXT NOT NULL, - description TEXT -) 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 UNIQUE NOT NULL, - description TEXT, - parent TEXT DEFAULT NULL, - unit_property TEXT NOT NULL, - FOREIGN KEY(parent) REFERENCES product_parents(id), - FOREIGN KEY(unit_property) REFERENCES unit_properties(id) -) STRICT; - -CREATE TABLE barcodes ( - id INTEGER UNIQUE NOT NULL PRIMARY KEY, - product_id TEXT NOT NULL, - amount INTEGER NOT NULL, - unit TEXT NOT NULL, - FOREIGN KEY(product_id) REFERENCES products(id), - FOREIGN KEY(unit) REFERENCES units(id) -) STRICT; -CREATE TRIGGER unit_has_property_of_product_update -BEFORE UPDATE OF product_id,unit ON barcodes -FOR EACH ROW -BEGIN - SELECT RAISE(FAIL, "Used different unit_property for product_id and unit") - FROM products, units - WHERE NEW.product_id = products.id - AND NEW.unit = units.id - AND units.unit_property != products.unit_property; -END; - -CREATE TRIGGER unit_has_property_of_product_insert -BEFORE INSERT ON barcodes -FOR EACH ROW -BEGIN - SELECT RAISE(FAIL, "Used different unit_property for product_id and unit") - FROM products, units - WHERE NEW.product_id = products.id - AND NEW.unit = units.id - AND units.unit_property != products.unit_property; -END; - -CREATE TABLE units ( - id TEXT UNIQUE NOT NULL PRIMARY KEY, - unit_property TEXT NOT NULL, - full_name_singular TEXT UNIQUE NOT NULL, - full_name_plural TEXT UNIQUE NOT NULL, - short_name TEXT UNIQUE NOT NULL, - description TEXT, - FOREIGN KEY(unit_property) REFERENCES unit_properties(id) -) STRICT; - -CREATE TABLE unit_properties ( - id TEXT UNIQUE NOT NULL PRIMARY KEY, - name TEXT UNIQUE NOT NULL, - description TEXT -) STRICT; - -CREATE TABLE recipies ( - id TEXT UNIQUE NOT NULL PRIMARY KEY, - name TEXT UNIQUE NOT NULL, - parent TEXT, - content TEXT NOT NULL, - FOREIGN KEY(parent) REFERENCES recipe_parents(id) -) STRICT; - --- Encodes unit conversions: --- {factor} {from_unit} = 1 {to_unit} --- E.g.: 1000 g = 1 kg -CREATE TABLE unit_conversions ( - from_unit TEXT NOT NULL, - to_unit TEXT NOT NULL, - factor REAL NOT NULL, - FOREIGN KEY(from_unit) REFERENCES units(id), - FOREIGN KEY(to_unit) REFERENCES units(id) -) STRICT; -CREATE TRIGGER both_units_have_same_unit_property_update -BEFORE UPDATE OF from_unit,to_unit ON unit_conversions -FOR EACH ROW -BEGIN - SELECT RAISE(FAIL, "Used not the same unit_property") - FROM units, units AS units2 - WHERE NEW.from_unit = units.id - AND NEW.to_unit = units2.id - AND units.unit_property != units2.unit_property; -END; - -CREATE TRIGGER both_units_have_same_unit_property_insert -BEFORE INSERT ON unit_conversions -FOR EACH ROW -BEGIN - SELECT RAISE(FAIL, "Used not the same unit_property") - FROM units, units AS units2 - WHERE NEW.from_unit = units.id - AND NEW.to_unit = units2.id - AND units.unit_property != units2.unit_property; -END; - --- Log of all the applied operations to this db. -CREATE TABLE txn_log ( - timestamp INTEGER NOT NULL, - operation TEXT NOT NULL -) STRICT; - -CREATE TABLE rocie_config ( - -- Make it impossible to insert more than one value here. - id INTEGER PRIMARY KEY NOT NULL CHECK (id = 0), - use_defaults INTEGER NOT NULL CHECK (use_defaults = 1 OR use_defaults = 0) -) STRICT; - -INSERT INTO rocie_config (id, use_defaults) -VALUES (0, 0); -- cgit 1.4.1