From 15dfb099b13ad68b4f286fa9def77f083f79e1b2 Mon Sep 17 00:00:00 2001
From: Benedikt Peetz <benedikt.peetz@b-peetz.de>
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.
---
 .../rocie-server/src/storage/migrate/sql/0-1.sql   | 199 +++++++++++++++++++++
 1 file changed, 199 insertions(+)
 create mode 100644 crates/rocie-server/src/storage/migrate/sql/0-1.sql

(limited to 'crates/rocie-server/src/storage/migrate/sql/0-1.sql')

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);
-- 
cgit 1.4.1

