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)
22 create table if not exists coffees (
23 num integer primary key,
24 id varchar(24) references users(id), -- id may be unknown
25 flavor varchar(255) not null references flavors(name),
26 time datetime default current_timestamp,
27 UNIQUE (id, flavor, time)
31 create table if not exists days (
32 num integer primary key not null
35 insert or ignore into days values
36 (0),(1),(2),(3),(4),(5),(6)
39 CREATE TABLE if not exists identifiers (
40 `userid` varchar ( 24 ) NOT NULL,
41 `id` varchar ( 24 ) PRIMARY KEY NOT NULL,
42 `name` varchar ( 24 ),
43 `active` INTEGER NOT NULL DEFAULT 1,
44 FOREIGN KEY(`userid`) REFERENCES `users`(`id`)