From 5fa57845052655883120ba4d19a85d8756fb8d8c Mon Sep 17 00:00:00 2001 From: Zach Berwaldt Date: Wed, 6 Mar 2024 21:53:07 -0500 Subject: [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. --- api/lib/models.go | 41 -------- api/main.go | 215 ++++++++++++++++++++++------------------ api/models.go | 57 +++++++++++ db/scripts/water_init.sql | 27 ++++- fe/src/lib/DataView.svelte | 130 ++++++++++++------------ fe/src/lib/forms/AddForm.svelte | 13 ++- 6 files changed, 281 insertions(+), 202 deletions(-) delete mode 100644 api/lib/models.go create mode 100644 api/models.go diff --git a/api/lib/models.go b/api/lib/models.go deleted file mode 100644 index f959519..0000000 --- a/api/lib/models.go +++ /dev/null @@ -1,41 +0,0 @@ -package models - -import ( - "time" - "github.com/google/uuid" -) - -type Statistic struct { - ID int64 `json:"-"` - Date time.Time `json:"date"` - User User `json:"user"` - Quantity int `json:"quantity"` -} - -type User struct { - ID int64 `json:"-"` - Name string `json:"name"` - UUID uuid.UUID `json:"uuid"` - Password string `json:"-"` -} - -type Token struct { - 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 57feb09..17a3c3a 100644 --- a/api/main.go +++ b/api/main.go @@ -1,18 +1,17 @@ package main import ( - "net/http" "crypto/rand" - "encoding/base64" "database/sql" - "strings" + "encoding/base64" "errors" "log" + "net/http" + "strings" "github.com/gin-gonic/gin" _ "github.com/mattn/go-sqlite3" "golang.org/x/crypto/bcrypt" - "water/api/lib" ) func CORSMiddleware() gin.HandlerFunc { @@ -22,15 +21,12 @@ func CORSMiddleware() gin.HandlerFunc { c.Writer.Header().Set("Access-Control-Allow-Headers", "Content-Type, Content-Length, Accept-Encoding, X-CSRF-Token, Authorization, accept, origin, Cache-Control, X-Requested-With") c.Writer.Header().Set("Access-Control-Allow-Methods", "POST, OPTIONS, GET, PUT") - log.Println("I am here") - if c.Request.Method == "OPTIONS" { log.Println(c.Request.Header) - c.AbortWithStatus(204) + c.AbortWithStatus(http.StatusNoContent) return } - log.Println(c.Request.Header) c.Next() } } @@ -38,7 +34,10 @@ func CORSMiddleware() gin.HandlerFunc { // generatToken will g func generateToken() string { token := make([]byte, 32) - rand.Read(token) + _, err := rand.Read(token) + if err != nil { + return "" + } return base64.StdEncoding.EncodeToString(token) } @@ -53,13 +52,13 @@ func establishDBConnection() *sql.DB { func checkForTokenInContext(c *gin.Context) (string, error) { authorizationHeader := c.GetHeader("Authorization") if authorizationHeader == "" { - return "", errors.New("Authorization header is missing") + return "", errors.New("authorization header is missing") } parts := strings.Split(authorizationHeader, " ") if len(parts) != 2 || parts[0] != "Bearer" { - return "", errors.New("Invalid Authorization header format") + return "", errors.New("invalid Authorization header format") } return parts[1], nil @@ -98,15 +97,21 @@ func setupRouter() *gin.Engine { } db := establishDBConnection() - defer db.Close() + defer func(db *sql.DB) { + err := db.Close() + if err != nil { + c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()}) + return + } + }(db) - var user models.User - var preference models.Preference - var size models.Size + var user User + var preference Preference + var size 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 { + if errors.Is(err, sql.ErrNoRows) { c.AbortWithStatus(http.StatusUnauthorized) return } @@ -129,128 +134,147 @@ func setupRouter() *gin.Engine { { stats.GET("/", func(c *gin.Context) { db := establishDBConnection() - defer db.Close() + defer func(db *sql.DB) { + err := db.Close() + if err != nil { + c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()}) + return + } + }(db) 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()}) + c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()}) return } - defer rows.Close() + defer func(rows *sql.Rows) { + err := rows.Close() + if err != nil { + c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()}) + return + } + }(rows) - var data []models.Statistic + var data []Statistic for rows.Next() { - var stat models.Statistic - var user models.User + var stat Statistic + var user User if err := rows.Scan(&stat.Date, &stat.Quantity, &user.UUID, &user.Name); err != nil { - c.JSON(500, gin.H{"error": err.Error()}) + c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()}) return } stat.User = user data = append(data, stat) } + c.JSON(http.StatusOK, data) + }) - // TODO: return to this and figure out how to best collect the data you are looking for for each user (zach and parker) - rows, err = db.Query("SELECT date(s.date), SUM(s.quantity) as total FROM Statistics s WHERE s.date >= date('now', '-7 days') GROUP BY DATE(s.date)") - if err != nil { - c.JSON(500, gin.H{"error": err.Error()}) + stats.POST("/", func(c *gin.Context) { + var stat StatisticPost + + if err := c.BindJSON(&stat); err != nil { + c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()}) return } - defer rows.Close() - var dailySummaries []models.DailySummary - for rows.Next() { - var summary models.DailySummary - if err := rows.Scan(&summary.Date, &summary.Total); err != nil { - c.JSON(500, gin.H{"error": err.Error()}) + db := establishDBConnection() + defer func(db *sql.DB) { + err := db.Close() + if err != nil { + c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()}) return } - dailySummaries = append(dailySummaries, summary) - } + }(db) - c.JSON(http.StatusOK, gin.H{"stats": data, "totals": dailySummaries}) - rows, err = db.Query("SELECT s.date, SUM(s.quantity) as total, u.uuid, u.name FROM Statistics s INNER JOIN Users u ON u.id = s.user_id WHERE s.date >= date('now', '-7 days') GROUP BY s.date, s.user_id") + result, err := db.Exec("INSERT INTO statistics (date, user_id, quantity) values (?, ?, ?)", stat.Date, stat.UserID, stat.Quantity) if err != nil { - c.JSON(500, gin.H{"error": err.Error()}) - return + c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()}) } - defer rows.Close() - var totals []interface{} - for rows.Next() { - var stat models.Statistic - 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 - totals = append(totals, stat) + id, err := result.LastInsertId() + if err != nil { + c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()}) } - c.JSON(http.StatusOK, gin.H{"stats": data, "totals": totals}) + c.JSON(http.StatusCreated, gin.H{"status": "created", "id": id}) }) - stats.POST("/", func(c *gin.Context) { - var stat models.Statistic + stats.GET("weekly/", func(c *gin.Context) { + db := establishDBConnection() + defer func(db *sql.DB) { + err := db.Close() + if err != nil { + c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()}) + return + } + }(db) - if err := c.BindJSON(&stat); err != nil { - c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()}) + rows, err := db.Query("SELECT date, total FROM `WeeklyStatisticsView`") + if err != nil { + c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()}) return } + defer func(rows *sql.Rows) { + err := rows.Close() + if err != nil { + c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()}) + return + } + }(rows) + + var data []WeeklyStatistic + for rows.Next() { + var weeklyStat WeeklyStatistic + if err := rows.Scan(&weeklyStat.Date, &weeklyStat.Total); err != nil { + c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()}) + } + data = append(data, weeklyStat) + } + + c.JSON(http.StatusOK, data) + }) + stats.GET("totals/", func(c *gin.Context) { db := establishDBConnection() - defer db.Close() + defer func(db *sql.DB) { + err := db.Close() + if err != nil { + c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()}) + return + } + }(db) - result, err := db.Exec("INSERT INTO statistics (date, user_id, quantity) values (?, ?, ?)", stat.Date, 1, stat.Quantity) + rows, err := db.Query("SELECT name, total FROM DailyUserStatistics") if err != nil { - c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()}) + c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()}) + return } + defer func(rows *sql.Rows) { + err := rows.Close() + if err != nil { + c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()}) + return + } + }(rows) - id, err := result.LastInsertId() - if err != nil { - c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()}) + var data []DailyUserTotals + for rows.Next() { + var stat DailyUserTotals + if err := rows.Scan(&stat.Name, &stat.Total); err != nil { + c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()}) + return + } + data = append(data, stat) } - c.JSON(http.StatusCreated, gin.H{"status": "created", "id": id}) - }) + c.JSON(http.StatusOK, data) - stats.GET("/totals/", func(c *gin.Context) { - c.JSON(http.StatusOK, gin.H{"status": "ok"}) }) - // stats.GET("/totals/", func(c *gin.Context) { - // db := establishDBConnection() - // defer db.Close() - // - // rows, err := db.Query("SELECT s.date, SUM(s.quantity) as total, u.uuid, u.name FROM Statistics s INNER JOIN Users u ON u.id = s.user_id WHERE s.date >= date('now', '-7 days') GROUP BY s.date, s.user_id") - // - // if err != nil { - // c.JSON(500, gin.H{"error": err.Error()}) - // return - // } - // defer rows.Close() - // - // var data []models.Statistic - // for rows.Next() { - // var stat models.Statistic - // 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) - // } - // - // c.JSON(http.StatusOK, data) - // - // }) - stats.GET("user/:uuid", func(c *gin.Context) { c.JSON(http.StatusOK, gin.H{"status": "ok", "uuid": c.Param("uuid")}) }) @@ -270,5 +294,8 @@ func setupRouter() *gin.Engine { func main() { r := setupRouter() // Listen and Server in 0.0.0.0:8080 - r.Run(":8080") + err := r.Run(":8080") + if err != nil { + return + } } diff --git a/api/models.go b/api/models.go new file mode 100644 index 0000000..0845d1d --- /dev/null +++ b/api/models.go @@ -0,0 +1,57 @@ +package main + +import ( + "time" + "github.com/google/uuid" +) + +type Statistic struct { + ID int64 `json:"-"` + Date time.Time `json:"date"` + User User `json:"user"` + Quantity int `json:"quantity"` +} + +type StatisticPost struct { + Date time.Time `json:"date"` + Quantity int64 `json:"quantity"` + UserID int64 `json:"user_id"` +} + +type User struct { + ID int64 `json:"-"` + Name string `json:"name"` + UUID uuid.UUID `json:"uuid"` + Password string `json:"-"` +} + +type Token struct { + 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"` +} + +type WeeklyStatistic struct { + Date string `json:"date"` + Total int64 `json:"total"` +} + +type DailyUserTotals struct { + Name string `json:"name"` + Total int64 `json:"total"` +} \ No newline at end of file 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 ( CREATE TABLE IF NOT EXISTS Preferences ( id INTEGER PRIMARY KEY, color TEXT NOT NULL DEFAULT "#000000", - user_id INT NOT NULL, + 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) @@ -70,3 +70,28 @@ 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/fe/src/lib/DataView.svelte b/fe/src/lib/DataView.svelte index 2b1b8b9..7d62a43 100644 --- a/fe/src/lib/DataView.svelte +++ b/fe/src/lib/DataView.svelte @@ -9,8 +9,6 @@ import AddForm from "./forms/AddForm.svelte"; let json: Promise; - let totals: Promise; - let userStats: Promise; let barCanvasRef: HTMLCanvasElement; let lineCanvasRef: HTMLCanvasElement; @@ -18,6 +16,10 @@ let lineChart: any; let lastSevenDays: string[]; + let lastSevenDaysData: number[]; + + let userTotalsLabels: string[]; + let userTotalsData: number[]; async function fetchData() { const res = await fetch("http://localhost:8080/api/v1/stats/", { @@ -33,24 +35,27 @@ } } - async function fetchTotals() { + async function fetchDailyUserStatistics() { const res = await fetch("http://localhost:8080/api/v1/stats/totals/", { - method: 'GET', - mode: 'no-cors', + method: "GET", headers: { Authorization: `Bearer ${$token}` } }); if (res.ok) { - totals = res.json(); + const json = await res.json(); + let labels = json.map(d => d.name); + let data = json.map(d => d.total); + return [labels, data]; } else { throw new Error("There was a problem with your request"); } + } - async function fetchStatsForUser() { - const res = await fetch("http://localhost:8080/api/v1/stats/user/1aa668f3-7527-4a67-9c24-fdf307542eeb", { + async function fetchWeeklyTotals() { + const res = await fetch("http://localhost:8080/api/v1/stats/weekly/", { method: "GET", headers: { Authorization: `Bearer ${$token}` @@ -58,22 +63,15 @@ }); if (res.ok) { - userStats = res.json(); + const json = await res.json(); + let labels = json.map(d => d.date); + let data = json.map(d => d.total); + return [labels, data]; } else { throw new Error("There was a problem with your request"); } } - function getLastSevenDays() { - const result = []; - for (let i = 0; i < 7; i++) { - let d = new Date(); - d.setDate(d.getDate() - i); - result.push(d.toISOString().substring(0, 10)); - } - return result; - } - function closeDialog() { addFormOpen.set(false); } @@ -81,61 +79,79 @@ function onStatisticAdd() { closeDialog(); fetchData(); + fetchWeeklyTotals().then(updateWeeklyTotalsChart).catch(err => console.error(err)); + fetchDailyUserStatistics().then(updateDailyUserTotalsChart).catch(err => console.error(err)); } - onMount(() => { - fetchData(); -// fetchTotals(); - fetchStatsForUser(); - lastSevenDays = getLastSevenDays(); - barChart = new Chart(barCanvasRef, { - type: "bar", + function setupWeeklyTotalsChart(result) { + [lastSevenDays, lastSevenDaysData] = result; + lineChart = new Chart(lineCanvasRef, { + type: "line", data: { labels: lastSevenDays, datasets: [ { - label: "Zach", - data: [1, 2, 8, 2, 5, 5, 1], - backgroundColor: "rgba(255, 192, 192, 0.2)", - borderColor: "rgba(75, 192, 192, 1)", - borderWidth: 1 - }, { - label: "Parker", - data: [6, 1, 1, 4, 3, 5, 1], - backgroundColor: "rgba(75, 192, 192, 0.2)", - borderColor: "rgba(75, 192, 192, 1)", - borderWidth: 1 + label: "Totals", + data: lastSevenDaysData, + backgroundColor: "rgba(255, 192, 192, 0.2)" } ] }, options: { - responsive: true + responsive: true, + plugins: { + legend: { + display: false + } + } } }); - lineChart = new Chart(lineCanvasRef, { - type: "line", + } + + function setupDailyUserTotalsChart(result) { + [userTotalsLabels, userTotalsData] = result; + + barChart = new Chart(barCanvasRef, { + type: "bar", data: { - labels: lastSevenDays, + labels: userTotalsLabels, datasets: [ { - label: "Zach", - data: [1, 2, 8, 2, 5, 5, 1], - backgroundColor: "rgba(255, 192, 192, 0.2)", - borderColor: "rgba(75, 192, 192, 1)", - borderWidth: 1 - }, { - label: "Parker", - data: [6, 1, 1, 4, 3, 5, 1], - backgroundColor: "rgba(75, 192, 192, 0.2)", - borderColor: "rgba(75, 192, 192, 1)", - borderWidth: 1 + data: userTotalsData, + backgroundColor: [ + "#330000", + "rgba(100, 200, 192, 0.2)" + ] } ] }, options: { - responsive: true + responsive: true, + plugins: { + legend: { + display: false + } + } } }); + } + + function updateWeeklyTotalsChart(result) { + [,lastSevenDaysData] = result; + lineChart.data.datasets[0].data = lastSevenDaysData; + lineChart.update(); + } + + function updateDailyUserTotalsChart(result) { + [,userTotalsData] = result; + barChart.data.datasets[0].data = userTotalsData; + barChart.update(); + } + + onMount(() => { + fetchData(); + fetchWeeklyTotals().then(setupWeeklyTotalsChart); + fetchDailyUserStatistics().then(setupDailyUserTotalsChart); }); onDestroy(() => { @@ -164,14 +180,6 @@

{error}

{/await} - - - {#await totals then data} - {JSON.stringify(data)} - {:catch error} -

{error}

- {/await} -
diff --git a/fe/src/lib/forms/AddForm.svelte b/fe/src/lib/forms/AddForm.svelte index f22e5f4..4520b1b 100644 --- a/fe/src/lib/forms/AddForm.svelte +++ b/fe/src/lib/forms/AddForm.svelte @@ -34,20 +34,23 @@ dispatch("close"); } - async function handleSubmitStat() { - const response = await fetch("http://localhost:8080/api/v1/stats/", { + async function handleSubmitStat() + { + const { date, quantity } = statistic; + await fetch("http://localhost:8080/api/v1/stats/", { method: "POST", headers: { Authorization: `Bearer ${$token}` }, body: JSON.stringify({ - date: new Date(), - user_id: 1, - quantity: 3 + date: new Date(date), + user_id: 2, + quantity }) }); dispatch("submit"); } + -- cgit v1.1