aboutsummaryrefslogtreecommitdiff
path: root/db
diff options
context:
space:
mode:
authorZach Berwaldt <zberwaldt@tutamail.com>2024-03-06 21:53:07 -0500
committerZach Berwaldt <zberwaldt@tutamail.com>2024-03-06 21:53:07 -0500
commit5fa57845052655883120ba4d19a85d8756fb8d8c (patch)
treed2d5ad1dd3fd8d9acaca9ced09612b50218f06b0 /db
parentcf2113e77edabf8e3a632c7b76c769752039ba88 (diff)
[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.
Diffstat (limited to 'db')
-rw-r--r--db/scripts/water_init.sql27
1 files changed, 26 insertions, 1 deletions
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 (
18CREATE TABLE IF NOT EXISTS Preferences ( 18CREATE TABLE IF NOT EXISTS Preferences (
19 id INTEGER PRIMARY KEY, 19 id INTEGER PRIMARY KEY,
20 color TEXT NOT NULL DEFAULT "#000000", 20 color TEXT NOT NULL DEFAULT "#000000",
21 user_id INT NOT NULL, 21 user_id INT UNIQUE NOT NULL,
22 size_id INT NOT NULL DEFAULT 1, 22 size_id INT NOT NULL DEFAULT 1,
23 FOREIGN KEY(user_id) REFERENCES Users(id) ON DELETE CASCADE, 23 FOREIGN KEY(user_id) REFERENCES Users(id) ON DELETE CASCADE,
24 FOREIGN KEY(size_id) REFERENCES Sizes(id) 24 FOREIGN KEY(size_id) REFERENCES Sizes(id)
@@ -70,3 +70,28 @@ END;
70-- 70--
71CREATE VIEW IF NOT EXISTS aggregated_stats AS 71CREATE VIEW IF NOT EXISTS aggregated_stats AS
72 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; 72 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;
73
74CREATE VIEW IF NOT EXISTS `DailyUserStatistics` AS
75SELECT users.name, IFNULL(SUM(statistics.quantity), 0) as total, preferences.color as color
76FROM users
77LEFT JOIN statistics ON users.id = statistics.user_id AND DATE(statistics.date) = DATE('now', '-1 day')
78LEFT JOIN preferences ON users.id = preferences.user_id
79GROUP BY users.name;
80
81
82CREATE VIEW IF NOT EXISTS `WeeklyStatisticsView` AS
83 WITH DateSequence(Dates) AS
84 (
85 SELECT Date(CURRENT_DATE, '-7 day')
86 UNION ALL
87 SELECT Date(Dates, '+1 day')
88 FROM DateSequence
89 WHERE Date(Dates, '+1 day') < Date(CURRENT_DATE)
90 )
91SELECT DateSequence.Dates as 'date',
92 IFNULL(SUM(statistics.quantity), 0) AS 'total'
93FROM DateSequence
94LEFT JOIN statistics
95ON Date(statistics.date) = DateSequence.Dates
96GROUP BY DateSequence.Dates
97ORDER BY DateSequence.Dates; \ No newline at end of file