From cf2e52f7e6bcc4dc87315ec0d9aed010b1ceb4d6 Mon Sep 17 00:00:00 2001 From: Sebastian Reichel Date: Thu, 21 Jan 2016 17:56:46 +0100 Subject: add sql constraint allowing only valid EAN numbers --- sql/tables.sql | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/sql/tables.sql b/sql/tables.sql index 211cda3..c82e473 100644 --- a/sql/tables.sql +++ b/sql/tables.sql @@ -1,5 +1,5 @@ BEGIN TRANSACTION; -CREATE TABLE IF NOT EXISTS products (id INTEGER PRIMARY KEY NOT NULL, name TEXT, amount INTEGER NOT NULL DEFAULT 0, category INTEGER REFERENCES categories, deprecated BOOLEAN NOT NULL DEFAULT 0); +CREATE TABLE IF NOT EXISTS products (id INTEGER PRIMARY KEY NOT NULL CHECK (id < 10000000000000 and (10 - (((id / 1000000000000 % 10) + (id / 100000000000 % 10) * 3 + (id / 10000000000 % 10) + (id / 1000000000 % 10) * 3 + (id / 100000000 % 10) + (id / 10000000 % 10) * 3 + (id / 1000000 % 10) + (id / 100000 % 10) * 3 + (id / 10000 % 10) + (id / 1000 % 10) * 3 + (id / 100 % 10) + (id / 10 % 10) * 3) % 10)) % 10 == (id / 1 % 10)), name TEXT, amount INTEGER NOT NULL DEFAULT 0, category INTEGER REFERENCES categories, deprecated BOOLEAN NOT NULL DEFAULT 0); CREATE TABLE IF NOT EXISTS sales (user INTEGER NOT NULL REFERENCES users, product INTEGER NOT NULL REFERENCES products, timestamp INTEGER NOT NULL DEFAULT 0); CREATE TABLE IF NOT EXISTS restock (user INTEGER NOT NULL REFERENCES users, product INTEGER NOT NULL REFERENCES products, amount INTEGER NOT NULL DEFAULT 0, timestamp INTEGER NOT NULL DEFAULT 0, price INTEGER NOT NULL DEFAULT 0, supplier INTEGER, best_before_date INTEGER); CREATE TABLE IF NOT EXISTS prices (product INTEGER NOT NULL REFERENCES products, valid_from INTEGER NOT NULL DEFAULT 0, memberprice INTEGER NOT NULL DEFAULT 0, guestprice INTEGER NOT NULL DEFAULT 0); -- cgit v1.2.3