Add new beverage `tea` and make buttons larger

[coffee/coffee-flask.git] / coffee_db.sql

diff --git a/coffee_db.sql b/coffee_db.sql
index d9f7d8ea1dafff09de4a0376d3329842a87a64c1..23011af167bf7f0dabe96d6024ecfa5269f5e1c1 100644 (file)
--- a/coffee_db.sql
+++ b/coffee_db.sql
@@ -6,21 +6,26 @@ create table if not exists users (
 );
 
 create table if not exists flavors (
-    name varchar(255) primary key not null
+    name varchar(255) primary key not null,
+    ord integer not null default 999
 );
 
 insert or ignore into flavors values
-    ("espresso"),
-    ("espresso lungo"),
-    ("cappuccino"),
-    ("latte macchiato")
+    ("espresso", 2),
+    ("espresso lungo", 3),
+    ("cappuccino", 1),
+    ("latte macchiato", 4),
+    ("Club-Mate 0,5 l", 5),
+    ("Club-Mate 0,33 l", 6),
+    ("tea", 7)
 ;
 
 create table if not exists coffees (
     num integer primary key,
     id varchar(24) references users(id), -- id may be unknown
     flavor varchar(255) not null references flavors(name),
-    time datetime default current_timestamp
+    time datetime default current_timestamp,
+    UNIQUE (id, flavor, time)
 );
 
 
@@ -31,3 +36,11 @@ create table if not exists days (
 insert or ignore into days values
     (0),(1),(2),(3),(4),(5),(6)
 ;
+
+CREATE TABLE if not exists identifiers (
+    `userid` varchar ( 24 ) NOT NULL,
+    `id` varchar ( 24 ) PRIMARY KEY NOT NULL,
+    `name` varchar ( 24 ),
+    `active` INTEGER NOT NULL DEFAULT 1,
+    FOREIGN KEY(`userid`) REFERENCES `users`(`id`)
+);