feat(crates/rocie-server/unit-property): Init

1 files changed, 54 insertions, 2 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
index fd48c68..7f08738 100644
--- a/crates/rocie-server/src/storage/migrate/sql/0->1.sql
+++ b/crates/rocie-server/src/storage/migrate/sql/0->1.sql
@@ -57,10 +57,12 @@ END;
 
 CREATE TABLE products (
     id                          TEXT UNIQUE NOT NULL PRIMARY KEY,
-    name                        TEXT        NOT NULL,
+    name                        TEXT UNIQUE NOT NULL,
     description                 TEXT,
     parent                      TEXT                             DEFAULT NULL,
-    FOREIGN KEY(parent) REFERENCES parents(id)
+    unit_property               TEXT        NOT NULL,
+    FOREIGN KEY(parent) REFERENCES parents(id),
+    FOREIGN KEY(unit_property) REFERENCES unit_properties(id)
 ) STRICT;
 
 CREATE TABLE barcodes (
@@ -71,12 +73,41 @@ CREATE TABLE barcodes (
     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 UNIQUE 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;
 
@@ -90,6 +121,27 @@ CREATE TABLE unit_conversions (
     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 (