From: Michal Sojka <michal.sojka@cvut.cz>
Date: Tue, 18 Aug 2020 17:36:57 +0000 (+0200)
Subject: Extend database schema for event logging
X-Git-Url: https://rtime.felk.cvut.cz/gitweb/coffee/coffee-flask.git/commitdiff_plain/e5c5ffd4df1886ecb028399e8dca68f2e55d379c

Extend database schema for event logging

Actual event logging will be implemented in the next commits.
---

diff --git a/coffee_db.sql b/coffee_db.sql
index 51f83aa..d9ff410 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, 'LAST_COFFEE_PACK_OPENED'), -- prepared for later use
+    (2, 'COFFEE_MACHINE_CLEANED'),
+    (3, 'MILK_CONTAINER_CLEANED'),
+    (4, 'MILK_CONTAINER_CLEANED_WITH_TABLET') -- cleaning with tablets 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,