coffee_db.sql

   1 pragma foreign_keys = ON;
   2
   3 create table if not exists users (
   4     id varchar(24) primary key not null,
   5     name varchar(255) default "human"
   6 );
   7
   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 ""
  12 );
  13
  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"),
  21     ("tea", 7, "tea")
  22 ;
  23
  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)
  30 );
  31
  32
  33 create table if not exists days (
  34     num integer primary key not null
  35 );
  36
  37 insert or ignore into days values
  38     (0),(1),(2),(3),(4),(5),(6)
  39 ;
  40
  41 create table if not exists event_types (
  42     id integer primary key,
  43     name varchar(255) not null
  44 );
  45
  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
  52 ;
  53
  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)
  60 );
  61 create index if not exists idx_events on events (event_type, time);
  62
  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`)
  69 );