From 6f28f28ecd899fd7f90df42528ef178a94f6677d Mon Sep 17 00:00:00 2001 From: Arda Serdar Pektezol <1669855+pektezol@users.noreply.github.com> Date: Sun, 24 Sep 2023 12:13:15 +0300 Subject: feat: removing (by flag) records (#56) Former-commit-id: cc1bed84ee7ff9133192e1278c8315afee73d23a --- backend/api/routes.go | 5 +++ backend/database/init.sql | 2 ++ backend/handlers/home.go | 8 ++--- backend/handlers/logs.go | 6 ++-- backend/handlers/map.go | 8 ++--- backend/handlers/record.go | 79 ++++++++++++++++++++++++++++++++++++++++++++++ 6 files changed, 97 insertions(+), 11 deletions(-) diff --git a/backend/api/routes.go b/backend/api/routes.go index 339dc73..9e703f6 100644 --- a/backend/api/routes.go +++ b/backend/api/routes.go @@ -21,6 +21,7 @@ const ( mapImagePath string = "/maps/:mapid/image" mapLeaderboardsPath string = "/maps/:mapid/leaderboards" mapRecordPath string = "/maps/:mapid/record" + mapRecordIDPath string = "/maps/:mapid/record/:recordid" mapDiscussionsPath string = "/maps/:mapid/discussions" mapDiscussionIDPath string = "/maps/:mapid/discussions/:discussionid" rankingsPath string = "/rankings" @@ -51,14 +52,18 @@ func InitRoutes(router *gin.Engine) { v1.POST(profilePath, CheckAuth, handlers.UpdateUser) v1.GET(usersPath, CheckAuth, handlers.FetchUser) // Maps + // - Summary v1.GET(mapSummaryPath, handlers.FetchMapSummary) v1.POST(mapSummaryPath, CheckAuth, handlers.CreateMapSummary) v1.PUT(mapSummaryPath, CheckAuth, handlers.EditMapSummary) v1.DELETE(mapSummaryPath, CheckAuth, handlers.DeleteMapSummary) v1.PUT(mapImagePath, CheckAuth, handlers.EditMapImage) + // - Leaderboards v1.GET(mapLeaderboardsPath, handlers.FetchMapLeaderboards) v1.POST(mapRecordPath, CheckAuth, handlers.CreateRecordWithDemo) + v1.DELETE(mapRecordIDPath, CheckAuth, handlers.DeleteRecord) v1.GET(demosPath, handlers.DownloadDemoWithID) + // - Discussions v1.GET(mapDiscussionsPath, handlers.FetchMapDiscussions) v1.GET(mapDiscussionIDPath, handlers.FetchMapDiscussion) v1.POST(mapDiscussionsPath, CheckAuth, handlers.CreateMapDiscussion) diff --git a/backend/database/init.sql b/backend/database/init.sql index 0f8196b..9d7b68c 100644 --- a/backend/database/init.sql +++ b/backend/database/init.sql @@ -130,6 +130,7 @@ CREATE TABLE records_sp ( score_time INTEGER NOT NULL, demo_id UUID NOT NULL, record_date TIMESTAMP NOT NULL DEFAULT now(), + is_deleted BOOLEAN NOT NULL DEFAULT false, PRIMARY KEY (id), FOREIGN KEY (map_id) REFERENCES maps(id), FOREIGN KEY (user_id) REFERENCES users(steam_id), @@ -146,6 +147,7 @@ CREATE TABLE records_mp ( host_demo_id UUID NOT NULL, partner_demo_id UUID NOT NULL, record_date TIMESTAMP NOT NULL DEFAULT now(), + is_deleted BOOLEAN NOT NULL DEFAULT false, PRIMARY KEY (id), FOREIGN KEY (map_id) REFERENCES maps(id), FOREIGN KEY (host_id) REFERENCES users(steam_id), diff --git a/backend/handlers/home.go b/backend/handlers/home.go index c9742f2..5863218 100644 --- a/backend/handlers/home.go +++ b/backend/handlers/home.go @@ -49,11 +49,11 @@ func Rankings(c *gin.Context) { SELECT user_id, MIN(score_count) AS min_score_count - FROM records_sp + FROM records_sp WHERE is_deleted = false GROUP BY user_id, map_id ) AS subquery WHERE user_id = u.steam_id) - FROM records_sp sp JOIN users u ON u.steam_id = sp.user_id GROUP BY u.steam_id, u.user_name` + FROM records_sp sp JOIN users u ON u.steam_id = sp.user_id WHERE is_deleted = false GROUP BY u.steam_id, u.user_name` rows, err := database.DB.Query(sql) if err != nil { c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) @@ -81,11 +81,11 @@ func Rankings(c *gin.Context) { host_id, partner_id, MIN(score_count) AS min_score_count - FROM records_mp + FROM records_mp WHERE is_deleted = false GROUP BY host_id, partner_id, map_id ) AS subquery WHERE host_id = u.steam_id OR partner_id = u.steam_id) - FROM records_mp mp JOIN users u ON u.steam_id = mp.host_id OR u.steam_id = mp.partner_id GROUP BY u.steam_id, u.user_name` + FROM records_mp mp JOIN users u ON u.steam_id = mp.host_id OR u.steam_id = mp.partner_id WHERE is_deleted = false GROUP BY u.steam_id, u.user_name` rows, err = database.DB.Query(sql) if err != nil { c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) diff --git a/backend/handlers/logs.go b/backend/handlers/logs.go index 6f59238..4c7b415 100644 --- a/backend/handlers/logs.go +++ b/backend/handlers/logs.go @@ -140,17 +140,17 @@ func ScoreLogs(c *gin.Context) { rs.record_date FROM ( SELECT id, map_id, user_id, score_count, score_time, demo_id, record_date - FROM records_sp + FROM records_sp WHERE is_deleted = false UNION ALL SELECT id, map_id, host_id AS user_id, score_count, score_time, host_demo_id AS demo_id, record_date - FROM records_mp + FROM records_mp WHERE is_deleted = false UNION ALL SELECT id, map_id, partner_id AS user_id, score_count, score_time, partner_demo_id AS demo_id, record_date - FROM records_mp + FROM records_mp WHERE is_deleted = false ) AS rs JOIN users u ON rs.user_id = u.steam_id JOIN maps m ON rs.map_id = m.id diff --git a/backend/handlers/map.go b/backend/handlers/map.go index 3bf14cd..faccee4 100644 --- a/backend/handlers/map.go +++ b/backend/handlers/map.go @@ -104,7 +104,7 @@ func FetchMapSummary(c *gin.Context) { if response.Map.IsCoop { sql = `SELECT count(*) FROM ( SELECT host_id, partner_id, score_count, score_time, ROW_NUMBER() OVER (PARTITION BY host_id, partner_id ORDER BY score_count, score_time) AS rn - FROM records_mp WHERE map_id = $1 + FROM records_mp WHERE map_id = $1 AND is_deleted = false ) sub WHERE sub.rn = 1 AND score_count = $2` err = database.DB.QueryRow(sql, response.Map.ID, route.History.ScoreCount).Scan(&route.CompletionCount) if err != nil { @@ -114,7 +114,7 @@ func FetchMapSummary(c *gin.Context) { } else { sql = `SELECT count(*) FROM ( SELECT user_id, score_count, score_time, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY score_count, score_time) AS rn - FROM records_sp WHERE map_id = $1 + FROM records_sp WHERE map_id = $1 AND is_deleted = false ) sub WHERE rn = 1 AND score_count = $2` err = database.DB.QueryRow(sql, response.Map.ID, route.History.ScoreCount).Scan(&route.CompletionCount) if err != nil { @@ -204,7 +204,7 @@ func FetchMapLeaderboards(c *gin.Context) { record_date, ROW_NUMBER() OVER (PARTITION BY host_id, partner_id ORDER BY score_count, score_time) AS rn FROM records_mp - WHERE map_id = $1 + WHERE map_id = $1 AND is_deleted = false ) sub JOIN users AS host ON sub.host_id = host.steam_id JOIN users AS partner ON sub.partner_id = partner.steam_id @@ -255,7 +255,7 @@ func FetchMapLeaderboards(c *gin.Context) { SELECT id, user_id, score_count, score_time, demo_id, record_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY score_count, score_time) AS rn FROM records_sp - WHERE map_id = $1 + WHERE map_id = $1 AND is_deleted = false ) sub INNER JOIN users ON user_id = users.steam_id WHERE rn = 1 diff --git a/backend/handlers/record.go b/backend/handlers/record.go index 4787422..70095bf 100644 --- a/backend/handlers/record.go +++ b/backend/handlers/record.go @@ -8,6 +8,7 @@ import ( "mime/multipart" "net/http" "os" + "strconv" "github.com/gin-gonic/gin" "github.com/google/uuid" @@ -209,6 +210,84 @@ func CreateRecordWithDemo(c *gin.Context) { }) } +func DeleteRecord(c *gin.Context) { + mapID, err := strconv.Atoi(c.Param("mapid")) + if err != nil { + c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) + return + } + recordID, err := strconv.Atoi(c.Param("recordid")) + if err != nil { + c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) + return + } + user, exists := c.Get("user") + if !exists { + c.JSON(http.StatusOK, models.ErrorResponse("User not logged in.")) + return + } + // Validate map + var validateMapID int + var isCoop bool + sql := `SELECT m.id, g.is_coop FROM maps m INNER JOIN games g ON m.game_id = g.id + INNER JOIN chapters c ON m.chapter_id = c.id WHERE m.id = $1` + err = database.DB.QueryRow(sql, mapID).Scan(&validateMapID, &isCoop) + if err != nil { + c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) + return + } + if mapID != validateMapID { + c.JSON(http.StatusOK, models.ErrorResponse("Selected map does not exist.")) + return + } + if isCoop { + // Validate if cooperative record does exist + var validateRecordID int + sql = `SELECT mp.id FROM records_mp mp WHERE mp.id = $1 AND mp.map_id = $2 AND (mp.host_id = $3 OR mp.partner_id = $3) AND is_deleted = false` + err = database.DB.QueryRow(sql, recordID, mapID, user.(models.User).SteamID).Scan(&validateRecordID) + if err != nil { + c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) + return + } + if recordID != validateRecordID { + c.JSON(http.StatusOK, models.ErrorResponse("Selected record does not exist.")) + return + } + // Remove record + sql = `UPDATE records_mp SET is_deleted = true WHERE id = $1` + _, err = database.DB.Exec(sql, recordID) + if err != nil { + c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) + return + } + } else { + // Validate if singleplayer record does exist + var validateRecordID int + sql = `SELECT sp.id FROM records_sp sp WHERE sp.id = $1 AND sp.map_id = $2 AND sp.user_id = $3 AND is_deleted = false` + err = database.DB.QueryRow(sql, recordID, mapID, user.(models.User).SteamID).Scan(&validateRecordID) + if err != nil { + c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) + return + } + if recordID != validateRecordID { + c.JSON(http.StatusOK, models.ErrorResponse("Selected record does not exist.")) + return + } + // Remove record + sql = `UPDATE records_sp SET is_deleted = true WHERE id = $1` + _, err = database.DB.Exec(sql, recordID) + if err != nil { + c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) + return + } + } + c.JSON(http.StatusOK, models.Response{ + Success: true, + Message: "Successfully deleted record.", + Data: nil, + }) +} + // GET Demo // // @Description Get demo with specified demo uuid. -- cgit v1.2.3