From b32419dfe996fbc9731b48ba528bae67535f4839 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. --- README.md | 17 ++++++++++ db/README.md | 3 ++ db/scripts/init.sh | 9 +++++ db/scripts/water_init.sql | 86 ----------------------------------------------- db/sql/seed.sql | 5 +++ db/sql/tables.sql | 39 +++++++++++++++++++++ db/sql/triggers.sql | 11 ++++++ db/sql/views.sql | 27 +++++++++++++++ 8 files changed, 111 insertions(+), 86 deletions(-) create mode 100644 README.md create mode 100644 db/README.md create mode 100644 db/scripts/init.sh delete mode 100644 db/scripts/water_init.sql create mode 100644 db/sql/seed.sql create mode 100644 db/sql/tables.sql create mode 100644 db/sql/triggers.sql create mode 100644 db/sql/views.sql diff --git a/README.md b/README.md new file mode 100644 index 0000000..6c0b743 --- /dev/null +++ b/README.md @@ -0,0 +1,17 @@ +# Water + +Water is a simple tracking application for the amount of water you and +others drink. Complete with simple data visualizations for the daily consumptions, +plus over the last seven days + +## Stack + +This project uses the following technologies: + +- [Svelte][fe] for the frontend +- [Gin][api] for the backend +- [Sqlite3][db] for the database + +[fe]: https://svelte.dev/ +[api]: https://gin-gonic.com/ +[db]: https://www.sqlite.org/index.html \ No newline at end of file diff --git a/db/README.md b/db/README.md new file mode 100644 index 0000000..f36e555 --- /dev/null +++ b/db/README.md @@ -0,0 +1,3 @@ +# The Database + +This document describes how to set up the database for your instance of the water application. \ No newline at end of file diff --git a/db/scripts/init.sh b/db/scripts/init.sh new file mode 100644 index 0000000..e55292b --- /dev/null +++ b/db/scripts/init.sh @@ -0,0 +1,9 @@ +sqlite3 $DB_PATH < ../sql/tables.sql + +insert_user() { +read -p "Enter a username: " username +read -sp + } + +echo "Before continuing you must create users. The reset of the schema depends on them" + diff --git a/db/scripts/water_init.sql b/db/scripts/water_init.sql deleted file mode 100644 index 1099d09..0000000 --- a/db/scripts/water_init.sql +++ /dev/null @@ -1,86 +0,0 @@ --- 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 -); - --- create default sizes for sizes lookup table. -INSERT OR IGNORE INTO Sizes (id, size) VALUES (1, 8), (2, 16), (3, 24), (4, 32), (5, 40), (6, 48); - --- create default preferences. -INSERT OR IGNORE INTO Preferences (user_id) VALUES (1), (2); - -CREATE TRIGGER IF NOT EXISTS enforce_size_id -BEFORE INSERT ON Preferences -BEGIN - SELECT - CASE - WHEN ( - SELECT COUNT(*) FROM Sizes WHERE id = new.size_id - ) = 0 - THEN RAISE(ABORT, 'Size does not exist') - END; -END; - --- -CREATE VIEW IF NOT EXISTS aggregated_stats AS - SELECT u.uuid, SUM(s.quantity * s.size) from Statistics s INNER JOIN Users u ON u.id = s.user_id INNER JOIN Preferences p ON p.user_id = u.id INNER JOIN Size s ON s.id = p.size_id; - -CREATE VIEW IF NOT EXISTS `DailyUserStatistics` AS -SELECT users.name, IFNULL(SUM(statistics.quantity), 0) as total, preferences.color as color -FROM users -LEFT JOIN statistics ON users.id = statistics.user_id AND DATE(statistics.date) = DATE('now', '-1 day') -LEFT JOIN preferences ON users.id = preferences.user_id -GROUP BY users.name; - - -CREATE VIEW IF NOT EXISTS `WeeklyStatisticsView` AS - WITH DateSequence(Dates) AS - ( - SELECT Date(CURRENT_DATE, '-7 day') - UNION ALL - SELECT Date(Dates, '+1 day') - FROM DateSequence - WHERE Date(Dates, '+1 day') < Date(CURRENT_DATE) - ) -SELECT DateSequence.Dates as 'date', - IFNULL(SUM(statistics.quantity), 0) AS 'total' -FROM DateSequence -LEFT JOIN statistics -ON Date(statistics.date) = DateSequence.Dates -GROUP BY DateSequence.Dates -ORDER BY DateSequence.Dates; \ No newline at end of file diff --git a/db/sql/seed.sql b/db/sql/seed.sql new file mode 100644 index 0000000..41d6e46 --- /dev/null +++ b/db/sql/seed.sql @@ -0,0 +1,5 @@ +-- create default sizes for sizes lookup table. +INSERT OR IGNORE INTO Sizes (id, size) VALUES (1, 8), (2, 16), (3, 24), (4, 32), (5, 40), (6, 48); + +-- create default preferences. +INSERT OR IGNORE INTO Preferences (user_id) VALUES (1), (2); 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 diff --git a/db/sql/triggers.sql b/db/sql/triggers.sql new file mode 100644 index 0000000..3827642 --- /dev/null +++ b/db/sql/triggers.sql @@ -0,0 +1,11 @@ +CREATE TRIGGER IF NOT EXISTS enforce_size_id +BEFORE INSERT ON Preferences +BEGIN + SELECT + CASE + WHEN ( + SELECT COUNT(*) FROM Sizes WHERE id = new.size_id + ) = 0 + THEN RAISE(ABORT, 'Size does not exist') + END; +END; diff --git a/db/sql/views.sql b/db/sql/views.sql new file mode 100644 index 0000000..c56286d --- /dev/null +++ b/db/sql/views.sql @@ -0,0 +1,27 @@ +CREATE VIEW IF NOT EXISTS aggregated_stats AS + SELECT u.uuid, SUM(s.quantity * s.size) from Statistics s INNER JOIN Users u ON u.id = s.user_id INNER JOIN Preferences p ON p.user_id = u.id INNER JOIN Size s ON s.id = p.size_id; + +CREATE VIEW IF NOT EXISTS `DailyUserStatistics` AS +SELECT users.name, IFNULL(SUM(statistics.quantity), 0) as total, preferences.color as color +FROM users +LEFT JOIN statistics ON users.id = statistics.user_id AND DATE(statistics.date) = DATE('now', '-1 day') +LEFT JOIN preferences ON users.id = preferences.user_id +GROUP BY users.name; + + +CREATE VIEW IF NOT EXISTS `WeeklyStatisticsView` AS + WITH DateSequence(Dates) AS + ( + SELECT Date(CURRENT_DATE, '-7 day') + UNION ALL + SELECT Date(Dates, '+1 day') + FROM DateSequence + WHERE Date(Dates, '+1 day') < Date(CURRENT_DATE) + ) +SELECT DateSequence.Dates as 'date', + IFNULL(SUM(statistics.quantity), 0) AS 'total' +FROM DateSequence +LEFT JOIN statistics +ON Date(statistics.date) = DateSequence.Dates +GROUP BY DateSequence.Dates +ORDER BY DateSequence.Dates; \ No newline at end of file -- cgit v1.1