diff options
author | Zach Berwaldt <zberwaldt@tutamail.com> | 2024-03-06 21:53:07 -0500 |
---|---|---|
committer | Zach Berwaldt <zberwaldt@tutamail.com> | 2024-03-06 21:53:07 -0500 |
commit | 5fa57845052655883120ba4d19a85d8756fb8d8c (patch) | |
tree | d2d5ad1dd3fd8d9acaca9ced09612b50218f06b0 /db | |
parent | cf2113e77edabf8e3a632c7b76c769752039ba88 (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.sql | 27 |
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 ( | |||
18 | CREATE TABLE IF NOT EXISTS Preferences ( | 18 | CREATE 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 | -- |
71 | CREATE VIEW IF NOT EXISTS aggregated_stats AS | 71 | CREATE 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 | |||
74 | CREATE VIEW IF NOT EXISTS `DailyUserStatistics` AS | ||
75 | SELECT users.name, IFNULL(SUM(statistics.quantity), 0) as total, preferences.color as color | ||
76 | FROM users | ||
77 | LEFT JOIN statistics ON users.id = statistics.user_id AND DATE(statistics.date) = DATE('now', '-1 day') | ||
78 | LEFT JOIN preferences ON users.id = preferences.user_id | ||
79 | GROUP BY users.name; | ||
80 | |||
81 | |||
82 | CREATE 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 | ) | ||
91 | SELECT DateSequence.Dates as 'date', | ||
92 | IFNULL(SUM(statistics.quantity), 0) AS 'total' | ||
93 | FROM DateSequence | ||
94 | LEFT JOIN statistics | ||
95 | ON Date(statistics.date) = DateSequence.Dates | ||
96 | GROUP BY DateSequence.Dates | ||
97 | ORDER BY DateSequence.Dates; \ No newline at end of file | ||