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 @@
+ +

Add Water

- - +
+ + +
+
+ + +
+
@@ -65,3 +112,15 @@ onMount(() => { {/await}
+ + diff --git a/fe/src/lib/LoginForm.svelte b/fe/src/lib/LoginForm.svelte index 22c0faf..499a457 100644 --- a/fe/src/lib/LoginForm.svelte +++ b/fe/src/lib/LoginForm.svelte @@ -1,8 +1,8 @@ + +

User Preferences

+
+
+ + +
+
+ + +
+ +
+ + diff --git a/fe/src/lib/Table.svelte b/fe/src/lib/Table.svelte index 5572280..4b81800 100644 --- a/fe/src/lib/Table.svelte +++ b/fe/src/lib/Table.svelte @@ -30,6 +30,11 @@ function formatDatum([key, value]: any[]) { const parsedDate = new Date(value); return formatter.format(parsedDate); } + + if (key === 'user') { + return value['name']; + } + return value; } @@ -78,9 +83,23 @@ table { padding: 16px; margin: 8px; border: solid 1px black; + border-collapse: collapse; } th { text-transform: capitalize; } + +thead tr { + background: rgba(0,0,23, 0.34); +} + +tbody tr:nth-child(odd) { + background: rgba(0,0,23,0.14); +} + +th, td { + padding: 1em; + border: 1px solid rgba(0,0,0, 1); +} diff --git a/fe/src/stores/auth.ts b/fe/src/stores/auth.ts index 7e70cda..10e6bd3 100644 --- a/fe/src/stores/auth.ts +++ b/fe/src/stores/auth.ts @@ -1,4 +1,5 @@ import type { Invalidator, Subscriber, Unsubscriber } from 'svelte/store'; +import type { Preference } from '../types'; import { writable, derived } from 'svelte/store'; type Nullable = T | null; @@ -14,6 +15,18 @@ interface TokenStore { unauthenticate: () => void } + +interface UserStore { + subscribe: (run: Subscriber>, invalidate: Invalidator>) => Unsubscriber, + setUser: (user: User) => void, + reset: () => void +} + +interface PreferenceStore { + subscribe: (run: Subscriber>, invalidate: Invalidator>) => Unsubscriber, + set: (this: void, value: Nullable) => void +} + function createTokenStore(): TokenStore { const storedToken = localStorage.getItem("token"); const { subscribe, set } = writable(storedToken); @@ -43,6 +56,48 @@ function onTokenChange ($token: Nullable): boolean { return $token ? true : false; } +function createUserStore(): UserStore { + const user = localStorage.getItem('user'); + const userObj: Nullable = user ? JSON.parse(user) : null; + const { subscribe, set } = writable(userObj); + + const setUser = (user: User) => { + localStorage.setItem('user', JSON.stringify(user)); + set(user); + } + + const reset = () => { + localStorage.removeItem('user'); + set(null); + } + + return { + subscribe, + setUser, + reset + } +} + + +function createPreferenceStore(): PreferenceStore { + const preferences = localStorage.getItem('preferences'); + const preferenceObj: Preference = preferences ? JSON.parse(preferences) : { + color: "#FF0000", + size: { + size: 16, + unit: 'oz' + } + }; + + const { subscribe, set } = writable>(preferenceObj); + + return { + subscribe, + set + } +} + export const token = createTokenStore(); export const authenticated = derived(token, onTokenChange); -export const user = writable(null); +export const user = createUserStore(); +export const preferences = createPreferenceStore(); diff --git a/fe/src/types.ts b/fe/src/types.ts new file mode 100644 index 0000000..03d613d --- /dev/null +++ b/fe/src/types.ts @@ -0,0 +1,14 @@ +export interface Size { + size: number; + unit: string; +} + +export interface Preference { + color: string; + size: Size; +} + +export interface User { + name: string; + uuid: string; +} -- cgit v1.1