diff options
Diffstat (limited to 'crates/rocie-server/src/storage/migrate/sql/0-1.sql')
| -rw-r--r-- | crates/rocie-server/src/storage/migrate/sql/0-1.sql | 199 |
1 files changed, 199 insertions, 0 deletions
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 <benedikt.peetz@b-peetz.de> +-- 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 <https://www.gnu.org/licenses/gpl-3.0.txt>. + +-- 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); |