add product category

2 files changed, 3 insertions, 2 deletions

diff --git a/sql/tables.sql b/sql/tables.sql
index ac9a8c2..211cda3 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, deprecated BOOLEAN NOT NULL DEFAULT 0);
+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 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);
@@ -8,5 +8,6 @@ CREATE TABLE IF NOT EXISTS authentication(user INTEGER PRIMARY KEY NOT NULL REFE
 CREATE TABLE IF NOT EXISTS supplier(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, city TEXT, postal_code TEXT, street TEXT, phone TEXT, website TEXT);
 CREATE TABLE IF NOT EXISTS cashbox_diff(id INTEGER PRIMARY KEY AUTOINCREMENT, user INTEGER NOT NULL REFERENCES users, amount INTEGER NOT NULL, timestamp INTEGER NOT NULL DEFAULT 0);
 CREATE TABLE IF NOT EXISTS ean_aliases (id INTEGER PRIMARY KEY NOT NULL, real_ean INTEGER NOT NULL REFERENCES products);
+CREATE TABLE IF NOT EXISTS categories (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT);
 CREATE INDEX IF NOT EXISTS invoiceindex ON sales (user ASC, timestamp DESC);
 COMMIT;
diff --git a/sql/views.sql b/sql/views.sql
index 4c0a56b..94cbabd 100644
--- a/sql/views.sql
+++ b/sql/views.sql
@@ -1,5 +1,5 @@
 BEGIN TRANSACTION;
-CREATE VIEW IF NOT EXISTS stock AS SELECT id, name, amount FROM products WHERE deprecated = 0 OR amount != 0;
+CREATE VIEW IF NOT EXISTS stock AS SELECT id, name, category, amount FROM products WHERE deprecated = 0 OR amount != 0;
 CREATE VIEW IF NOT EXISTS purchaseprices AS SELECT product, SUM(price * amount) / SUM(amount) AS price FROM restock GROUP BY product;
 CREATE VIEW IF NOT EXISTS invoice AS 
 	SELECT user, timestamp, id AS productid, name AS productname,