From 3eafb413a48cde60dea8a7355ee621c6acca952f Mon Sep 17 00:00:00 2001 From: Doog <157747121+doogongithub@users.noreply.github.com> Date: Wed, 21 Feb 2024 22:07:27 -0500 Subject: first commit --- db/scripts/water_init.sql | 42 ++++++++++++++++++++++++++++++++++++++++++ db/water.sqlite3 | Bin 0 -> 40960 bytes 2 files changed, 42 insertions(+) create mode 100644 db/scripts/water_init.sql create mode 100644 db/water.sqlite3 (limited to 'db') diff --git a/db/scripts/water_init.sql b/db/scripts/water_init.sql new file mode 100644 index 0000000..d7b912a --- /dev/null +++ b/db/scripts/water_init.sql @@ -0,0 +1,42 @@ +-- user table for users. +CREATE TABLE IF NOT EXISTS Users ( + id INT PRIMARY KEY, + name TEXT NOT NULL, + UNIQUE(name) +); + +-- statistics table for users to log their consumption +CREATE TABLE IF NOT EXISTS Statistics ( + id INT 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 INT PRIMARY KEY, + color TEXT NOT NULL DEFAULT "#000000", + user_id INT 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 INT PRIMARY KEY, + size INT NOT NULL + unit TEXT DEFAULT "oz" +); + +-- 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 users. +INSERT OR IGNORE INTO Users (id, name) VALUES (1, 'Parker'), (2, 'Zach'); + +-- create default preferences. +INSERT OR IGNORE INTO Preferences (id, user_id) VALUES (1, 1), (2, 2); + + diff --git a/db/water.sqlite3 b/db/water.sqlite3 new file mode 100644 index 0000000..97f9214 Binary files /dev/null and b/db/water.sqlite3 differ -- cgit v1.1 From e37c73e33a4aaf7fb8d25b5af03627f20bcda19f Mon Sep 17 00:00:00 2001 From: Doog <157747121+doogongithub@users.noreply.github.com> Date: Sat, 24 Feb 2024 20:08:35 -0500 Subject: add gitignore --- db/scripts/water_init.sql | 10 +++++----- db/water.sqlite3 | Bin 40960 -> 24576 bytes 2 files changed, 5 insertions(+), 5 deletions(-) (limited to 'db') diff --git a/db/scripts/water_init.sql b/db/scripts/water_init.sql index d7b912a..0751c41 100644 --- a/db/scripts/water_init.sql +++ b/db/scripts/water_init.sql @@ -1,13 +1,13 @@ -- user table for users. CREATE TABLE IF NOT EXISTS Users ( - id INT PRIMARY KEY, + id INTEGER PRIMARY KEY, name TEXT NOT NULL, UNIQUE(name) ); -- statistics table for users to log their consumption CREATE TABLE IF NOT EXISTS Statistics ( - id INT PRIMARY KEY, + id INTEGER PRIMARY KEY, date DATETIME NOT NULL, user_id INT NOT NULL, quantity INT @@ -15,7 +15,7 @@ CREATE TABLE IF NOT EXISTS Statistics ( -- preferences table for a user. CREATE TABLE IF NOT EXISTS Preferences ( - id INT PRIMARY KEY, + id INTEGER PRIMARY KEY, color TEXT NOT NULL DEFAULT "#000000", user_id INT NOT NULL, size_id INT NOT NULL DEFAULT 1, @@ -25,8 +25,8 @@ CREATE TABLE IF NOT EXISTS Preferences ( -- lookup table for sizes. CREATE TABLE IF NOT EXISTS Sizes ( - id INT PRIMARY KEY, - size INT NOT NULL + id INTEGER PRIMARY KEY, + size INT NOT NULL, unit TEXT DEFAULT "oz" ); diff --git a/db/water.sqlite3 b/db/water.sqlite3 index 97f9214..c800708 100644 Binary files a/db/water.sqlite3 and b/db/water.sqlite3 differ -- cgit v1.1 From 9f9a33cbf55d38987a66b709284d2bb4ffea0fe9 Mon Sep 17 00:00:00 2001 From: Doog <157747121+doogongithub@users.noreply.github.com> Date: Thu, 29 Feb 2024 20:13:48 -0500 Subject: modify api, build additional FE components, add types --- db/scripts/water_init.sql | 40 +++++++++++++++++++++++++++++++++++----- db/water.sqlite3 | Bin 24576 -> 36864 bytes 2 files changed, 35 insertions(+), 5 deletions(-) (limited to 'db') diff --git a/db/scripts/water_init.sql b/db/scripts/water_init.sql index 0751c41..6a4de24 100644 --- a/db/scripts/water_init.sql +++ b/db/scripts/water_init.sql @@ -1,8 +1,9 @@ -- user table for users. CREATE TABLE IF NOT EXISTS Users ( id INTEGER PRIMARY KEY, - name TEXT NOT NULL, - UNIQUE(name) + password TEXT UNIQUE NOT NULL, + uuid TEXT UNIQUE NOT NULL, + name TEXT UNIQUE NOT NULL ); -- statistics table for users to log their consumption @@ -19,7 +20,7 @@ CREATE TABLE IF NOT EXISTS Preferences ( color TEXT NOT NULL DEFAULT "#000000", user_id INT NOT NULL, size_id INT NOT NULL DEFAULT 1, - FOREIGN KEY(user_id) REFERENCES Users(id) ON DELETE CASCADE + FOREIGN KEY(user_id) REFERENCES Users(id) ON DELETE CASCADE, FOREIGN KEY(size_id) REFERENCES Sizes(id) ); @@ -30,13 +31,42 @@ CREATE TABLE IF NOT EXISTS Sizes ( 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 users. -INSERT OR IGNORE INTO Users (id, name) VALUES (1, 'Parker'), (2, 'Zach'); +INSERT OR IGNORE INTO Users (name, password, uuid) VALUES ( + 'parker', + '$2y$10$2UlKrQJQV5cQOo/8VcFlq.ai3MWf7mA4//knEs2xVnHTeB.RnfN.m', + '1aa668f3-7527-4a67-9c24-fdf307542eeb' +), ( + 'zach', + '$2y$10$35UJnLpBj8ulhqN/3G4qKe0GYBOa/YunXit11n7ET6zknZpNeKpRS', + 'be3fd6b7-cf55-4eb8-92d8-1b745b439f34' +); -- create default preferences. -INSERT OR IGNORE INTO Preferences (id, user_id) VALUES (1, 1), (2, 2); +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; diff --git a/db/water.sqlite3 b/db/water.sqlite3 index c800708..716c5a4 100644 Binary files a/db/water.sqlite3 and b/db/water.sqlite3 differ -- cgit v1.1 From afeffe31bd7d0f8333627a972e1d32e64a325b5b Mon Sep 17 00:00:00 2001 From: Zach Berwaldt Date: Fri, 1 Mar 2024 18:17:42 -0500 Subject: reformat fe --- db/water.sqlite3 | Bin 36864 -> 0 bytes 1 file changed, 0 insertions(+), 0 deletions(-) delete mode 100644 db/water.sqlite3 (limited to 'db') diff --git a/db/water.sqlite3 b/db/water.sqlite3 deleted file mode 100644 index 716c5a4..0000000 Binary files a/db/water.sqlite3 and /dev/null differ -- cgit v1.1 From 5fa57845052655883120ba4d19a85d8756fb8d8c Mon Sep 17 00:00:00 2001 From: Zach Berwaldt Date: Wed, 6 Mar 2024 21:53:07 -0500 Subject: [FEAT] Refactor API main file and models This commit refactors the `main.go` file in the API directory, as well as the related models in the `models.go` file. The changes include: - Reordering imports and removing unnecessary imports - Fixing error messages to be more descriptive - Handling database connections more efficiently with deferred closures - Handling errors and returning appropriate error responses - Adding proper JSON bindings for POST requests - Adding new views in the database scripts for aggregated statistics and daily user statistics No changes were made to imports and requires. --- db/scripts/water_init.sql | 27 ++++++++++++++++++++++++++- 1 file changed, 26 insertions(+), 1 deletion(-) (limited to 'db') diff --git a/db/scripts/water_init.sql b/db/scripts/water_init.sql index 6a4de24..3b79ed5 100644 --- a/db/scripts/water_init.sql +++ b/db/scripts/water_init.sql @@ -18,7 +18,7 @@ CREATE TABLE IF NOT EXISTS Statistics ( CREATE TABLE IF NOT EXISTS Preferences ( id INTEGER PRIMARY KEY, color TEXT NOT NULL DEFAULT "#000000", - user_id INT NOT NULL, + 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) @@ -70,3 +70,28 @@ 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 -- cgit v1.1