From 6e187ccc973d55cd73de4eeb8498936d41a5a904 Mon Sep 17 00:00:00 2001 From: Zach Berwaldt Date: Sat, 16 Mar 2024 09:44:50 -0400 Subject: remove unnessary logs. remove logger and recovery, default has these --- api/internal/middleware/middleware.go | 2 -- api/internal/router/router.go | 6 +----- 2 files changed, 1 insertion(+), 7 deletions(-) diff --git a/api/internal/middleware/middleware.go b/api/internal/middleware/middleware.go index aa27fb8..2333457 100644 --- a/api/internal/middleware/middleware.go +++ b/api/internal/middleware/middleware.go @@ -2,7 +2,6 @@ package middleware import ( "errors" - "log" "net/http" "strings" @@ -31,7 +30,6 @@ func CORSMiddleware() gin.HandlerFunc { c.Writer.Header().Set("Access-Control-Allow-Methods", "POST, OPTIONS, GET, PUT, PATCH") if c.Request.Method == "OPTIONS" { - log.Println(c.Request.Header) c.AbortWithStatus(http.StatusNoContent) return } diff --git a/api/internal/router/router.go b/api/internal/router/router.go index a71c3e6..1f2d914 100644 --- a/api/internal/router/router.go +++ b/api/internal/router/router.go @@ -7,12 +7,8 @@ import ( ) func SetupRouter() *gin.Engine { - // Disable Console Color - // gin.DisableConsoleColor() r := gin.Default() r.Use(middleware.CORSMiddleware()) - r.Use(gin.Logger()) - r.Use(gin.Recovery()) api := r.Group("api/v1") @@ -40,4 +36,4 @@ func SetupRouter() *gin.Engine { } return r -} \ No newline at end of file +} -- cgit v1.1 From 947bbd510ea104d3a631b3200da9ed239cfd6e80 Mon Sep 17 00:00:00 2001 From: Zach Berwaldt Date: Sat, 16 Mar 2024 09:55:02 -0400 Subject: Remove default users --- db/scripts/water_init.sql | 11 ----------- 1 file changed, 11 deletions(-) diff --git a/db/scripts/water_init.sql b/db/scripts/water_init.sql index 3b79ed5..1099d09 100644 --- a/db/scripts/water_init.sql +++ b/db/scripts/water_init.sql @@ -41,17 +41,6 @@ CREATE TABLE IF NOT EXISTS APIToken ( -- 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 (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 (user_id) VALUES (1), (2); -- cgit v1.1 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. --- 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 From 436fb1531a0876d76ce8baf6ace3e8d0286e399b Mon Sep 17 00:00:00 2001 From: Zach Berwaldt Date: Sat, 16 Mar 2024 11:23:03 -0400 Subject: write init script for database. --- db/scripts/init.sh | 84 +++++++++++++++++++++++++++++++++++++++++++++++++++--- 1 file changed, 80 insertions(+), 4 deletions(-) diff --git a/db/scripts/init.sh b/db/scripts/init.sh index e55292b..1a8bbde 100644 --- a/db/scripts/init.sh +++ b/db/scripts/init.sh @@ -1,9 +1,85 @@ -sqlite3 $DB_PATH < ../sql/tables.sql +PROJECT_DIR=$(pwd) + +DB_PATH="$PROJECT_DIR/db/test.sqlite3" + +SQL_DIR="$PROJECT_DIR/db/sql" + + insert_user() { -read -p "Enter a username: " username -read -sp - } + read -p "Enter a username: " username + read -sp "Enter a password: " password + hash=$(mkpasswd -m bcrypt "$password") + + if [ $? -ne 0 ]; then + echo "Error: Failed to hash password." + exit 1 + fi + + uuid=$(uuidgen) + + sqlite3 $DB_PATH "INSERT INTO users (name, password, uuid) VALUES ('$username', '$hash', '$uuid');" + + if [ $? -ne 0 ]; then + echo "Error: Failed to insert user into the database." + exit 1 + fi +} + +sqlite3 $DB_PATH < "$SQL_DIR/tables.sql" + +if [ $? -ne 0 ]; then + echo "Error: Failed to create tables in the database." + exit 1 +fi echo "Before continuing you must create users. The reset of the schema depends on them" +while true; do + insert_user + echo -e "\nDo you want to add anoter user? (y/n):" + read choice + if [ "$choice" != "y" ]; then + break + fi +done + +echo -e '\nUsers inserted, now setting up the rest of the database.' + +echo -e "\nAdding views:" +sqlite3 $DB_PATH < "$SQL_DIR/views.sql" + +if [ $? -ne 0 ]; then + echo "Error: Failed to add views." + exit 1 +fi + +echo -e "\nAdding triggers:" +sqlite3 $DB_PATH < "$SQL_DIR/triggers.sql" + +if [ $? -ne 0 ]; then + echo "Error: Failed to add triggers." + exit 1 +fi + +echo -e "\nSeeding the database:" +sqlite3 $DB_PATH < "$SQL_DIR/seed.sql" + +if [ $? -ne 0 ]; then + echo "Error: Failed to seed the database." + exit 1 +fi + +echo -e "\nThe database schema" +sqlite3 $DB_PATH .schema + +if [ $? -ne 0 ]; then + echo "Error: Failed to print the database schema." + exit 1 +fi + +echo -e "\n+------------------------------------+" + +echo -e "| Database initialization completed. |" + +echo -e "+------------------------------------+\n" \ No newline at end of file -- cgit v1.1