1 pragma foreign_keys = ON;
3 create table if not exists users (
4 id varchar(24) primary key not null,
5 name varchar(255) default "human"
8 create table if not exists flavors (
9 name varchar(255) primary key not null,
10 ord integer not null default 999,
11 type varchar(24) not null default ""
14 insert or ignore into flavors values
15 ("espresso", 2, "coffee"),
16 ("espresso lungo", 3, "coffee"),
17 ("cappuccino", 1, "coffee"),
18 ("latte macchiato", 4, "coffee"),
19 ("Club-Mate 0,5 l", 5, "Club-Mate"),
20 ("Club-Mate 0,33 l", 6, "Club-Mate"),
24 create table if not exists coffees (
25 num integer primary key,
26 id varchar(24) references users(id), -- id may be unknown
27 flavor varchar(255) not null references flavors(name),
28 time datetime default current_timestamp,
29 UNIQUE (id, flavor, time)
33 create table if not exists days (
34 num integer primary key not null
37 insert or ignore into days values
38 (0),(1),(2),(3),(4),(5),(6)
41 create table if not exists event_types (
42 id integer primary key,
43 name varchar(255) not null
46 insert or ignore into event_types values
47 (0, 'COFFEE_PACK_OPENED'),
48 (1, 'LAST_COFFEE_PACK_OPENED'), -- prepared for later use
49 (2, 'COFFEE_MACHINE_CLEANED'),
50 (3, 'MILK_CONTAINER_CLEANED'),
51 (4, 'MILK_CONTAINER_CLEANED_WITH_TABLET') -- cleaning with tablets implies cleaning of the container
54 create table if not exists events (
55 id integer primary key,
56 event_type integer not null,
57 user_id varchar(24) references users(id),
58 time datetime default current_timestamp not null,
59 foreign key(event_type) references event_types(id)
61 create index if not exists idx_events on events (event_type, time);
63 CREATE TABLE if not exists identifiers (
64 `userid` varchar ( 24 ) NOT NULL,
65 `id` varchar ( 24 ) PRIMARY KEY NOT NULL,
66 `name` varchar ( 24 ),
67 `active` INTEGER NOT NULL DEFAULT 1,
68 FOREIGN KEY(`userid`) REFERENCES `users`(`id`)