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 identifiers (
42 `userid` varchar ( 24 ) NOT NULL,
43 `id` varchar ( 24 ) PRIMARY KEY NOT NULL,
44 `name` varchar ( 24 ),
45 `active` INTEGER NOT NULL DEFAULT 1,
46 FOREIGN KEY(`userid`) REFERENCES `users`(`id`)