--- 1/2 Úprava databáze coffee_db.sql | 26 ++++++++++++++++++++++++++ 1 file changed, 26 insertions(+) diff --git a/coffee_db.sql b/coffee_db.sql index 51f83aa..5248d4c 100644 --- a/coffee_db.sql +++ b/coffee_db.sql @@ -38,6 +38,32 @@ insert or ignore into days values (0),(1),(2),(3),(4),(5),(6) ; +create table if not exists event_types ( + id integer primary key, + name varchar(255) not null, -- name of the event + status varchar(32) not null, -- `status` x days ago (required when `display`=1) + action varchar(32) not null, -- label of button to register event + display integer default 1, -- 1 for showing the latest occurence on the main page + trigger integer references event_types(id) default NULL + -- When set, registering this event will also count for `trigger` event +); + +insert or ignore into event_types values + (0, "coffee pack", "opened", "open", 1, NULL), + (1, "coffee pack", "", "open last", 0, 0), + (2, "coffee machine", "cleaned", "clean", 1, NULL), + (3, "milk container", "cleaned", "clean", 1, NULL), + (4, "milk container", "pill cleaned", "pill clean", 0, 3) -- e.g. pill cleaning implies cleaning of the container +; + +create table if not exists events ( + id integer primary key, + event_id integer references event_types(id), + user_id varchar(24) references users(id), + time datetime default current_timestamp, + UNIQUE (event_id, time, user_id) +); + CREATE TABLE if not exists identifiers ( `userid` varchar ( 24 ) NOT NULL, `id` varchar ( 24 ) PRIMARY KEY NOT NULL, -- 2.7.4