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
13 insert or ignore into flavors values
15 ("espresso lungo", 3),
17 ("latte macchiato", 4),
18 ("Club-Mate 0,5 l", 5),
19 ("Club-Mate 0,33 l", 6),
23 create table if not exists coffees (
24 num integer primary key,
25 id varchar(24) references users(id), -- id may be unknown
26 flavor varchar(255) not null references flavors(name),
27 time datetime default current_timestamp,
28 UNIQUE (id, flavor, time)
32 create table if not exists days (
33 num integer primary key not null
36 insert or ignore into days values
37 (0),(1),(2),(3),(4),(5),(6)
40 CREATE TABLE if not exists identifiers (
41 `userid` varchar ( 24 ) NOT NULL,
42 `id` varchar ( 24 ) PRIMARY KEY NOT NULL,
43 `name` varchar ( 24 ),
44 `active` INTEGER NOT NULL DEFAULT 1,
45 FOREIGN KEY(`userid`) REFERENCES `users`(`id`)