From 79b7424746c7636cd23a7869169431afa6941b06 Mon Sep 17 00:00:00 2001 From: Sebastian Reichel Date: Sat, 8 Dec 2012 23:35:33 +0100 Subject: rename table sells -> sales --- sql/tables.sql | 2 +- sql/trigger.sql | 6 +++--- sql/views.sql | 2 +- 3 files changed, 5 insertions(+), 5 deletions(-) (limited to 'sql') diff --git a/sql/tables.sql b/sql/tables.sql index a208467..21f007b 100644 --- a/sql/tables.sql +++ b/sql/tables.sql @@ -1,6 +1,6 @@ BEGIN TRANSACTION; CREATE TABLE IF NOT EXISTS products (id INTEGER PRIMARY KEY NOT NULL, name TEXT, amount INTEGER NOT NULL DEFAULT 0); -CREATE TABLE IF NOT EXISTS sells (user INTEGER NOT NULL REFERENCES users, product INTEGER NOT NULL REFERENCES products, timestamp INTEGER 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); 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); CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY NOT NULL, email TEXT, firstname TEXT NOT NULL, lastname TEXT NOT NULL, gender TEXT, street TEXT, plz INTEGER, city TEXT); diff --git a/sql/trigger.sql b/sql/trigger.sql index 8a9bede..d26a39d 100644 --- a/sql/trigger.sql +++ b/sql/trigger.sql @@ -12,15 +12,15 @@ CREATE TRIGGER IF NOT EXISTS update_product_amount_on_restock_update AFTER UPDAT UPDATE products SET amount = products.amount + NEW.amount WHERE products.id = NEW.product; END; -CREATE TRIGGER IF NOT EXISTS update_product_amount_on_sells_insert AFTER INSERT ON sells BEGIN +CREATE TRIGGER IF NOT EXISTS update_product_amount_on_sales_insert AFTER INSERT ON sales BEGIN UPDATE products SET amount = products.amount - 1 WHERE products.id = NEW.product; END; -CREATE TRIGGER IF NOT EXISTS update_product_amount_on_sells_delete AFTER DELETE ON sells BEGIN +CREATE TRIGGER IF NOT EXISTS update_product_amount_on_sales_delete AFTER DELETE ON sales BEGIN UPDATE products SET amount = products.amount + 1 WHERE products.id = OLD.product; END; -CREATE TRIGGER IF NOT EXISTS update_product_amount_on_sells_update AFTER UPDATE ON sells BEGIN +CREATE TRIGGER IF NOT EXISTS update_product_amount_on_sales_update AFTER UPDATE ON sales BEGIN UPDATE products SET amount = products.amount + 1 WHERE products.id = OLD.product; UPDATE products SET amount = products.amount - 1 WHERE products.id = NEW.product; END; diff --git a/sql/views.sql b/sql/views.sql index 6ab5ef2..8edfa9f 100644 --- a/sql/views.sql +++ b/sql/views.sql @@ -17,6 +17,6 @@ CREATE VIEW IF NOT EXISTS invoice AS WHERE product = id AND valid_from <= timestamp ORDER BY valid_from DESC LIMIT 1) END AS price - FROM sells INNER JOIN products ON sells.product = products.id + FROM sales INNER JOIN products ON sales.product = products.id ORDER BY timestamp; COMMIT; -- cgit v1.2.3