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, 0 insertions, 199 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 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 <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); |