From 968481312058aa58261d41cf3460b45109fec240 Mon Sep 17 00:00:00 2001 From: Zach Berwaldt Date: Sat, 16 Mar 2024 10:48:52 -0400 Subject: clean up database scripts, start readmes. --- db/sql/tables.sql | 39 +++++++++++++++++++++++++++++++++++++++ 1 file changed, 39 insertions(+) create mode 100644 db/sql/tables.sql (limited to 'db/sql/tables.sql') diff --git a/db/sql/tables.sql b/db/sql/tables.sql new file mode 100644 index 0000000..0fbfef8 --- /dev/null +++ b/db/sql/tables.sql @@ -0,0 +1,39 @@ +-- user table for users. +CREATE TABLE IF NOT EXISTS Users ( + id INTEGER PRIMARY KEY, + password TEXT UNIQUE NOT NULL, + uuid TEXT UNIQUE NOT NULL, + name TEXT UNIQUE NOT NULL +); + +-- statistics table for users to log their consumption +CREATE TABLE IF NOT EXISTS Statistics ( + id INTEGER PRIMARY KEY, + date DATETIME NOT NULL, + user_id INT NOT NULL, + quantity INT +); + +-- preferences table for a user. +CREATE TABLE IF NOT EXISTS Preferences ( + id INTEGER PRIMARY KEY, + color TEXT NOT NULL DEFAULT "#000000", + user_id INT UNIQUE NOT NULL, + size_id INT NOT NULL DEFAULT 1, + FOREIGN KEY(user_id) REFERENCES Users(id) ON DELETE CASCADE, + FOREIGN KEY(size_id) REFERENCES Sizes(id) +); + +-- lookup table for sizes. +CREATE TABLE IF NOT EXISTS Sizes ( + id INTEGER PRIMARY KEY, + size INT NOT NULL, + unit TEXT DEFAULT "oz" +); + +CREATE TABLE IF NOT EXISTS APIToken ( + id INTEGER PRIMARY KEY, + token TEXT NOT NULL, + user_id INTEGER NOT NULL, + FOREIGN KEY(user_id) REFERENCES Users(id) ON DELETE CASCADE +); \ No newline at end of file -- cgit v1.1