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 --- api/go.mod | 3 +- api/go.sum | 2 ++ api/lib/models.go | 38 +++++++++++++++------ api/main.go | 48 +++++++++++++++++---------- db/scripts/water_init.sql | 40 ++++++++++++++++++++--- db/water.sqlite3 | Bin 24576 -> 36864 bytes fe/src/lib/DataView.svelte | 67 +++++++++++++++++++++++++++++++++++--- fe/src/lib/LoginForm.svelte | 16 +++++---- fe/src/lib/PreferencesForm.svelte | 45 +++++++++++++++++++++++++ fe/src/lib/Table.svelte | 19 +++++++++++ fe/src/stores/auth.ts | 57 +++++++++++++++++++++++++++++++- fe/src/types.ts | 14 ++++++++ 12 files changed, 303 insertions(+), 46 deletions(-) create mode 100644 fe/src/lib/PreferencesForm.svelte create mode 100644 fe/src/types.ts diff --git a/api/go.mod b/api/go.mod index 08ad9e1..6a326bc 100644 --- a/api/go.mod +++ b/api/go.mod @@ -4,7 +4,9 @@ go 1.18 require ( github.com/gin-gonic/gin v1.9.1 + github.com/google/uuid v1.6.0 github.com/mattn/go-sqlite3 v1.14.22 + golang.org/x/crypto v0.19.0 ) require ( @@ -27,7 +29,6 @@ require ( github.com/twitchyliquid64/golang-asm v0.15.1 // indirect github.com/ugorji/go/codec v1.2.12 // indirect golang.org/x/arch v0.7.0 // indirect - golang.org/x/crypto v0.19.0 // indirect golang.org/x/net v0.21.0 // indirect golang.org/x/sys v0.17.0 // indirect golang.org/x/text v0.14.0 // indirect diff --git a/api/go.sum b/api/go.sum index 5174feb..115a832 100644 --- a/api/go.sum +++ b/api/go.sum @@ -29,6 +29,8 @@ github.com/goccy/go-json v0.10.2 h1:CrxCmQqYDkv1z7lO7Wbh2HN93uovUHgrECaO5ZrCXAU= github.com/goccy/go-json v0.10.2/go.mod h1:6MelG93GURQebXPDq3khkgXZkazVtN9CRI+MGFi0w8I= github.com/google/go-cmp v0.5.5 h1:Khx7svrCpmxxtHBq5j2mp/xVjsi8hQMfNLvJFAlrGgU= github.com/google/gofuzz v1.0.0/go.mod h1:dBl0BpW6vV/+mYPU4Po3pmUjxk6FQPldtuIdl/M65Eg= +github.com/google/uuid v1.6.0 h1:NIvaJDMOsjHA8n1jAhLSgzrAzy1Hgr+hNrb57e+94F0= +github.com/google/uuid v1.6.0/go.mod h1:TIyPZe4MgqvfeYDBFedMoGGpEw/LqOeaOT+nhxU+yHo= github.com/json-iterator/go v1.1.12 h1:PV8peI4a0ysnczrg+LtxykD8LfKY9ML6u2jnxaEnrnM= github.com/json-iterator/go v1.1.12/go.mod h1:e30LSqwooZae/UwlEbR2852Gd8hjQvJoHmT4TnhNGBo= github.com/klauspost/cpuid/v2 v2.0.9/go.mod h1:FInQzS24/EEf25PyTYn52gqo7WaD8xa0213Md/qVLRg= diff --git a/api/lib/models.go b/api/lib/models.go index 92e5703..f959519 100644 --- a/api/lib/models.go +++ b/api/lib/models.go @@ -1,23 +1,41 @@ package models -import "time" +import ( + "time" + "github.com/google/uuid" +) type Statistic struct { - ID int64 `json:"id"` + ID int64 `json:"-"` Date time.Time `json:"date"` - UserID int64 `json:"user_id"` + User User `json:"user"` Quantity int `json:"quantity"` } type User struct { - ID int64 - Name string + ID int64 `json:"-"` + Name string `json:"name"` + UUID uuid.UUID `json:"uuid"` + Password string `json:"-"` } type Token struct { - ID int64 - UserID int64 - Token string - CreatedAt time.Time - ExpiredAt time.Time + ID int64 `json:"-"` + UserID int64 `json:"user_id"` + Token string `json:"token"` + CreatedAt time.Time `json:"created_at"` + ExpiredAt time.Time `json:"expired_at"` +} + +type Preference struct { + ID int64 `json:"-"` + Color string `json:"color"` + UserID int64 `json:"-"` + Size Size `json:"size"` +} + +type Size struct { + ID int64 `json:"-"` + Size int64 `json:"size"` + Unit string `json:"unit"` } diff --git a/api/main.go b/api/main.go index 292a5f9..91b7929 100644 --- a/api/main.go +++ b/api/main.go @@ -10,6 +10,7 @@ import ( "github.com/gin-gonic/gin" _ "github.com/mattn/go-sqlite3" + "golang.org/x/crypto/bcrypt" "water/api/lib" ) @@ -29,6 +30,7 @@ func CORSMiddleware() gin.HandlerFunc { } } +// generatToken will g func generateToken() string { token := make([]byte, 32) rand.Read(token) @@ -43,6 +45,7 @@ func establishDBConnection() *sql.DB { return db } + func checkForTokenInContext(c *gin.Context) (string, error) { authorizationHeader := c.GetHeader("Authorization") if authorizationHeader == "" { @@ -54,6 +57,7 @@ func checkForTokenInContext(c *gin.Context) (string, error) { if len(parts) != 2 || parts[0] != "Bearer" { return "", errors.New("Invalid Authorization header format") } + return parts[1], nil } @@ -73,15 +77,6 @@ func TokenRequired() gin.HandlerFunc { } } -type User struct { - Username string - Password string -} - -var users = map[string]User{ - "user1": {"user1", "password1"}, -} - func setupRouter() *gin.Engine { // Disable Console Color // gin.DisableConsoleColor() @@ -100,16 +95,31 @@ func setupRouter() *gin.Engine { return } - user, exists := users[username] + db := establishDBConnection() + defer db.Close() + + var user models.User + var preference models.Preference + var size models.Size + + row := db.QueryRow("SELECT name, uuid, password, color, size, unit FROM Users u INNER JOIN Preferences p ON p.user_id = u.id INNER JOIN Sizes s ON p.size_id = s.id WHERE u.name = ?", username) + if err := row.Scan(&user.Name, &user.UUID, &user.Password, &preference.Color, &size.Size, &size.Unit); err != nil { + if err == sql.ErrNoRows { + c.AbortWithStatus(http.StatusUnauthorized) + return + } + } + + if err := bcrypt.CompareHashAndPassword([]byte(user.Password), []byte(password)); err != nil { + c.AbortWithStatus(http.StatusUnauthorized) + return + } - if !exists || user.Password != password { - c.AbortWithStatus(http.StatusUnauthorized) - return - } + preference.Size = size // Generate a simple API token apiToken := generateToken() - c.JSON(http.StatusOK, gin.H{"token": apiToken}) + c.JSON(http.StatusOK, gin.H{"token": apiToken, "user": user, "preferences": preference}) }) stats := api.Group("stats") @@ -119,7 +129,7 @@ func setupRouter() *gin.Engine { db := establishDBConnection() defer db.Close() - rows, err := db.Query("SELECT * FROM statistics"); + rows, err := db.Query("SELECT s.date, s.quantity, u.uuid, u.name FROM Statistics s INNER JOIN Users u ON u.id = s.user_id"); if err != nil { c.JSON(500, gin.H{"error": err.Error()}) return @@ -129,10 +139,12 @@ func setupRouter() *gin.Engine { var data []models.Statistic for rows.Next() { var stat models.Statistic - if err := rows.Scan(&stat.ID, &stat.Date, &stat.UserID, &stat.Quantity); err != nil { + var user models.User + if err := rows.Scan(&stat.Date, &stat.Quantity, &user.UUID, &user.Name); err != nil { c.JSON(500, gin.H{"error": err.Error()}) return } + stat.User = user data = append(data, stat) } @@ -150,7 +162,7 @@ func setupRouter() *gin.Engine { db := establishDBConnection() defer db.Close() - result, err := db.Exec("INSERT INTO statistics (date, user_id, quantity) values (?, ?, ?)", stat.Date, stat.UserID, stat.Quantity) + result, err := db.Exec("INSERT INTO statistics (date, user_id, quantity) values (?, ?, ?)", stat.Date, 1, stat.Quantity) if err != nil { c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()}) 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 diff --git a/fe/src/lib/DataView.svelte b/fe/src/lib/DataView.svelte index cd7b042..dc8acae 100644 --- a/fe/src/lib/DataView.svelte +++ b/fe/src/lib/DataView.svelte @@ -1,11 +1,24 @@