Actual event logging will be implemented in the next commits. --- coffee_db.sql | 22 ++++++++++++++++++++++ 1 file changed, 22 insertions(+)
diff --git a/coffee_db.sql b/coffee_db.sql index 51f83aa..7f872d2 100644 --- a/coffee_db.sql +++ b/coffee_db.sql @@ -38,6 +38,28 @@ 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 +); + +insert or ignore into event_types values + (0, 'COFFEE_PACK_OPENED'), + (1, 'COFFEE_PACK_LAST_OPENED'), + (2, 'COFFEE_MACHINE_CLEANED'), + (3, 'MILK_CONTAINER_CLEANED'), + (4, 'MILK_CONTAINER_PILL_CLEANED') -- pill cleaning implies cleaning of the container +; + +create table if not exists events ( + id integer primary key, + event_type integer not null, + user_id varchar(24) references users(id), + time datetime default current_timestamp not null, + foreign key(event_type) references event_types(id) +); +create index if not exists idx_events on events (event_type, time); + CREATE TABLE if not exists identifiers ( `userid` varchar ( 24 ) NOT NULL, `id` varchar ( 24 ) PRIMARY KEY NOT NULL,