From be5313fa0092688cea04f7f1f115574765847c22 Mon Sep 17 00:00:00 2001 From: Arda Serdar Pektezol <1669855+pektezol@users.noreply.github.com> Date: Sat, 19 Oct 2024 16:28:00 +0300 Subject: backend: fix run ranks --- backend/go.mod | 1 + backend/go.sum | 2 + backend/handlers/user.go | 40 +++++++----- backend/main.go | 28 ++++---- frontend/package-lock.json | 6 ++ frontend/package.json | 1 + frontend/src/components/UploadRunDialog.tsx | 99 +++++++++++++++++------------ 7 files changed, 106 insertions(+), 71 deletions(-) diff --git a/backend/go.mod b/backend/go.mod index f6eef48..ae50685 100644 --- a/backend/go.mod +++ b/backend/go.mod @@ -8,6 +8,7 @@ require ( ) require ( + github.com/gin-contrib/cors v1.7.2 github.com/golang-jwt/jwt/v4 v4.5.0 github.com/google/uuid v1.6.0 github.com/pektezol/steam_go v1.1.2 diff --git a/backend/go.sum b/backend/go.sum index 10504e4..f117b31 100644 --- a/backend/go.sum +++ b/backend/go.sum @@ -31,6 +31,8 @@ github.com/felixge/httpsnoop v1.0.4 h1:NFTV2Zj1bL4mc9sqWACXbQFVBBg2W3GPvqp8/ESS2 github.com/felixge/httpsnoop v1.0.4/go.mod h1:m8KPJKqk1gH5J9DgRY2ASl2lWCfGKXixSwevea8zH2U= github.com/gabriel-vasile/mimetype v1.4.5 h1:J7wGKdGu33ocBOhGy0z653k/lFKLFDPJMG8Gql0kxn4= github.com/gabriel-vasile/mimetype v1.4.5/go.mod h1:ibHel+/kbxn9x2407k1izTA1S81ku1z/DlgOW2QE0M4= +github.com/gin-contrib/cors v1.7.2 h1:oLDHxdg8W/XDoN/8zamqk/Drgt4oVZDvaV0YmvVICQw= +github.com/gin-contrib/cors v1.7.2/go.mod h1:SUJVARKgQ40dmrzgXEVxj2m7Ig1v1qIboQkPDTQ9t2E= github.com/gin-contrib/gzip v0.0.6 h1:NjcunTcGAj5CO1gn4N8jHOSIeRFHIbn51z6K+xaN4d4= github.com/gin-contrib/gzip v0.0.6/go.mod h1:QOJlmV2xmayAjkNS2Y8NQsMneuRShOU/kjovCXNuzzk= github.com/gin-contrib/sse v0.1.0 h1:Y/yl/+YNO8GZSjAhjMsSuLt29uWRFHdHYUb5lYOV9qE= diff --git a/backend/handlers/user.go b/backend/handlers/user.go index 908063f..17a7819 100644 --- a/backend/handlers/user.go +++ b/backend/handlers/user.go @@ -301,10 +301,12 @@ func Profile(c *gin.Context) { c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) return } - sql = `WITH best_scores AS (SELECT sp.user_id, sp.map_id, MIN(sp.score_count) AS best_score_count, MIN(sp.score_time) AS best_score_time - FROM records_sp sp WHERE sp.is_deleted = false GROUP BY sp.user_id, sp.map_id) - SELECT (SELECT COUNT(*) + 1 FROM best_scores AS inner_scores WHERE inner_scores.map_id = bs.map_id AND (inner_scores.best_score_count < bs.best_score_count OR (inner_scores.best_score_count = bs.best_score_count AND inner_scores.best_score_time < bs.best_score_time))) AS placement - FROM best_scores AS bs WHERE bs.user_id = $1 ORDER BY map_id, placement` + sql = `WITH best_scores AS (WITH RankedScores AS (SELECT sp.user_id, sp.map_id, sp.score_count, sp.score_time, ROW_NUMBER() OVER ( + PARTITION BY sp.user_id, sp.map_id ORDER BY sp.score_count ASC, sp.score_time ASC) AS rank FROM records_sp sp WHERE sp.is_deleted = false) + SELECT user_id, map_id, score_count AS best_score_count, score_time AS best_score_time FROM RankedScores WHERE rank = 1) + SELECT (SELECT COUNT(*) + 1 FROM best_scores AS inner_scores WHERE inner_scores.map_id = bs.map_id + AND (inner_scores.best_score_count < bs.best_score_count OR (inner_scores.best_score_count = bs.best_score_count + AND inner_scores.best_score_time < bs.best_score_time))) AS placement FROM best_scores AS bs WHERE bs.user_id = $1 ORDER BY map_id, placement` placementsRows, err := database.DB.Query(sql, user.(models.User).SteamID) if err != nil { c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) @@ -355,10 +357,12 @@ func Profile(c *gin.Context) { c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) return } - sql = `WITH best_scores AS (SELECT mp.host_id, mp.partner_id, mp.map_id, MIN(mp.score_count) AS best_score_count, MIN(mp.score_time) AS best_score_time - FROM records_mp mp WHERE mp.is_deleted = false GROUP BY mp.host_id, mp.partner_id, mp.map_id) - SELECT (SELECT COUNT(*) + 1 FROM best_scores AS inner_scores WHERE inner_scores.map_id = bs.map_id AND (inner_scores.best_score_count < bs.best_score_count OR (inner_scores.best_score_count = bs.best_score_count AND inner_scores.best_score_time < bs.best_score_time))) AS placement - FROM best_scores AS bs WHERE bs.host_id = $1 or bs.partner_id = $1 ORDER BY map_id, placement` + sql = `WITH best_scores AS (WITH RankedScores AS (SELECT mp.host_id, mp.partner_id, mp.map_id, mp.score_count, mp.score_time, ROW_NUMBER() OVER ( + PARTITION BY mp.host_id, mp.partner_id, mp.map_id ORDER BY mp.score_count ASC, mp.score_time ASC) AS rank FROM records_mp mp WHERE mp.is_deleted = false) + SELECT host_id, partner_id, map_id, score_count AS best_score_count, score_time AS best_score_time FROM RankedScores WHERE rank = 1) + SELECT (SELECT COUNT(*) + 1 FROM best_scores AS inner_scores WHERE inner_scores.map_id = bs.map_id + AND (inner_scores.best_score_count < bs.best_score_count OR (inner_scores.best_score_count = bs.best_score_count + AND inner_scores.best_score_time < bs.best_score_time))) AS placement FROM best_scores AS bs WHERE bs.host_id = $1 or bs.partner_id = $1 ORDER BY map_id, placement` placementsRows, err = database.DB.Query(sql, user.(models.User).SteamID) if err != nil { c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) @@ -675,10 +679,12 @@ func FetchUser(c *gin.Context) { c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) return } - sql = `WITH best_scores AS (SELECT sp.user_id, sp.map_id, MIN(sp.score_count) AS best_score_count, MIN(sp.score_time) AS best_score_time - FROM records_sp sp WHERE sp.is_deleted = false GROUP BY sp.user_id, sp.map_id) - SELECT (SELECT COUNT(*) + 1 FROM best_scores AS inner_scores WHERE inner_scores.map_id = bs.map_id AND (inner_scores.best_score_count < bs.best_score_count OR (inner_scores.best_score_count = bs.best_score_count AND inner_scores.best_score_time < bs.best_score_time))) AS placement - FROM best_scores AS bs WHERE bs.user_id = $1 ORDER BY map_id, placement` + sql = `WITH best_scores AS (WITH RankedScores AS (SELECT sp.user_id, sp.map_id, sp.score_count, sp.score_time, ROW_NUMBER() OVER ( + PARTITION BY sp.user_id, sp.map_id ORDER BY sp.score_count ASC, sp.score_time ASC) AS rank FROM records_sp sp WHERE sp.is_deleted = false) + SELECT user_id, map_id, score_count AS best_score_count, score_time AS best_score_time FROM RankedScores WHERE rank = 1) + SELECT (SELECT COUNT(*) + 1 FROM best_scores AS inner_scores WHERE inner_scores.map_id = bs.map_id + AND (inner_scores.best_score_count < bs.best_score_count OR (inner_scores.best_score_count = bs.best_score_count + AND inner_scores.best_score_time < bs.best_score_time))) AS placement FROM best_scores AS bs WHERE bs.user_id = $1 ORDER BY map_id, placement` placementsRows, err := database.DB.Query(sql, user.SteamID) if err != nil { c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) @@ -729,10 +735,12 @@ func FetchUser(c *gin.Context) { c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) return } - sql = `WITH best_scores AS (SELECT mp.host_id, mp.partner_id, mp.map_id, MIN(mp.score_count) AS best_score_count, MIN(mp.score_time) AS best_score_time - FROM records_mp mp WHERE mp.is_deleted = false GROUP BY mp.host_id, mp.partner_id, mp.map_id) - SELECT (SELECT COUNT(*) + 1 FROM best_scores AS inner_scores WHERE inner_scores.map_id = bs.map_id AND (inner_scores.best_score_count < bs.best_score_count OR (inner_scores.best_score_count = bs.best_score_count AND inner_scores.best_score_time < bs.best_score_time))) AS placement - FROM best_scores AS bs WHERE bs.host_id = $1 or bs.partner_id = $1 ORDER BY map_id, placement` + sql = `WITH best_scores AS (WITH RankedScores AS (SELECT mp.host_id, mp.partner_id, mp.map_id, mp.score_count, mp.score_time, ROW_NUMBER() OVER ( + PARTITION BY mp.host_id, mp.partner_id, mp.map_id ORDER BY mp.score_count ASC, mp.score_time ASC) AS rank FROM records_mp mp WHERE mp.is_deleted = false) + SELECT host_id, partner_id, map_id, score_count AS best_score_count, score_time AS best_score_time FROM RankedScores WHERE rank = 1) + SELECT (SELECT COUNT(*) + 1 FROM best_scores AS inner_scores WHERE inner_scores.map_id = bs.map_id + AND (inner_scores.best_score_count < bs.best_score_count OR (inner_scores.best_score_count = bs.best_score_count + AND inner_scores.best_score_time < bs.best_score_time))) AS placement FROM best_scores AS bs WHERE bs.host_id = $1 or bs.partner_id = $1 ORDER BY map_id, placement` placementsRows, err = database.DB.Query(sql, user.SteamID) if err != nil { c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) diff --git a/backend/main.go b/backend/main.go index a50be7e..6499b51 100644 --- a/backend/main.go +++ b/backend/main.go @@ -4,11 +4,13 @@ import ( "fmt" "log" "os" + "time" "lphub/api" "lphub/database" _ "lphub/docs" + "github.com/gin-contrib/cors" "github.com/gin-gonic/gin" "github.com/joho/godotenv" ) @@ -34,19 +36,19 @@ func main() { database.ConnectDB() api.InitRoutes(router) // for debugging - // router.Use(cors.New(cors.Config{ - // AllowOrigins: []string{"*"}, - // AllowMethods: []string{"GET", "POST", "DELETE", "PUT", "PATCH"}, - // AllowHeaders: []string{"Origin"}, - // ExposeHeaders: []string{"Content-Length"}, - // AllowCredentials: true, - // MaxAge: 12 * time.Hour, - // })) - // router.Static("/static", "../frontend/build/static") - // router.StaticFile("/", "../frontend/build/index.html") - // router.NoRoute(func(c *gin.Context) { - // c.File("../frontend/build/index.html") - // }) + router.Use(cors.New(cors.Config{ + AllowOrigins: []string{"*"}, + AllowMethods: []string{"GET", "POST", "DELETE", "PUT", "PATCH"}, + AllowHeaders: []string{"Origin"}, + ExposeHeaders: []string{"Content-Length"}, + AllowCredentials: true, + MaxAge: 12 * time.Hour, + })) + router.Static("/static", "../frontend/build/static") + router.StaticFile("/", "../frontend/build/index.html") + router.NoRoute(func(c *gin.Context) { + c.File("../frontend/build/index.html") + }) router.MaxMultipartMemory = 200 << 20 // 200 mb limit for demos router.Run(fmt.Sprintf(":%s", os.Getenv("PORT"))) } diff --git a/frontend/package-lock.json b/frontend/package-lock.json index 8b901ad..a77d244 100644 --- a/frontend/package-lock.json +++ b/frontend/package-lock.json @@ -8,6 +8,7 @@ "name": "frontend", "version": "0.1.0", "dependencies": { + "@nekz/sdp": "^0.9.0", "@testing-library/jest-dom": "^5.17.0", "@testing-library/react": "^13.4.0", "@testing-library/user-event": "^13.5.0", @@ -3174,6 +3175,11 @@ "resolved": "https://registry.npmjs.org/@leichtgewicht/ip-codec/-/ip-codec-2.0.5.tgz", "integrity": "sha512-Vo+PSpZG2/fmgmiNzYK9qWRh8h/CHrwD0mo1h1DzL4yzHNSfWYujGTYsWGreD000gcgmZ7K4Ys6Tx9TxtsKdDw==" }, + "node_modules/@nekz/sdp": { + "version": "0.9.0", + "resolved": "https://registry.npmjs.org/@nekz/sdp/-/sdp-0.9.0.tgz", + "integrity": "sha512-ndg3Ze/RZxsyX0QRhZ0HnkHUIth7/3jpTcABhzQzsXV0Y4MzpoZ0dj6KRqiBz+O3acZdk0/9gr40Smqc7S4ikg==" + }, "node_modules/@nicolo-ribaudo/eslint-scope-5-internals": { "version": "5.1.1-v1", "resolved": "https://registry.npmjs.org/@nicolo-ribaudo/eslint-scope-5-internals/-/eslint-scope-5-internals-5.1.1-v1.tgz", diff --git a/frontend/package.json b/frontend/package.json index 6c3022d..57c92a7 100644 --- a/frontend/package.json +++ b/frontend/package.json @@ -3,6 +3,7 @@ "version": "0.1.0", "private": true, "dependencies": { + "@nekz/sdp": "^0.9.0", "@testing-library/jest-dom": "^5.17.0", "@testing-library/react": "^13.4.0", "@testing-library/user-event": "^13.5.0", diff --git a/frontend/src/components/UploadRunDialog.tsx b/frontend/src/components/UploadRunDialog.tsx index d42ffe7..9f417b5 100644 --- a/frontend/src/components/UploadRunDialog.tsx +++ b/frontend/src/components/UploadRunDialog.tsx @@ -1,11 +1,16 @@ import React from 'react'; import { UploadRunContent } from '../types/Content'; +import { DemoMessages, ScoreboardTempUpdate, SourceDemoParser, UserMessage } from '@nekz/sdp'; +import fs from 'fs'; + + import '../css/UploadRunDialog.css'; import { Game } from '../types/Game'; import { Map } from '../types/Map'; import { API } from '../api/Api'; import { useNavigate } from 'react-router-dom'; +import { SvcUserMessage } from '@nekz/sdp/script/src/types/NetMessages'; interface UploadRunDialogProps { token?: string; @@ -103,6 +108,16 @@ const UploadRunDialog: React.FC = ({ token, open, onClose, return } } + const demo = SourceDemoParser.default() + .setOptions({ packets: true }) + .parse(await uploadRunContent.host_demo.arrayBuffer()); + + const scoreboardPacket = demo.findPacket(ScoreboardTempUpdate) + if (scoreboardPacket) { + console.log(scoreboardPacket) + } else { + console.log("couldnt find scoreboard packet") + } if (window.confirm("Are you sure you want to submit this run to LPHUB?")) { const message = await API.post_record(token, uploadRunContent); alert(message); @@ -114,7 +129,7 @@ const UploadRunDialog: React.FC = ({ token, open, onClose, React.useEffect(() => { if (open) { - _handle_game_select("1", "Portal 2 - Singleplayer"); // a different approach?. + _handle_game_select("1", "Portal 2 - Singleplayer"); // a different approach?. } }, [open]); @@ -125,15 +140,15 @@ const UploadRunDialog: React.FC = ({ token, open, onClose,
-
-

Select Game

-
_handle_dropdowns(1)} style={{display: "flex", alignItems: "center", cursor: "pointer", justifyContent: "space-between"}}> +
+

Select Game

+
_handle_dropdowns(1)} style={{ display: "flex", alignItems: "center", cursor: "pointer", justifyContent: "space-between" }}>
{selectedGameName}
- +
{games.map((game) => ( -
{_handle_game_select(game.id.toString(), game.name); _handle_dropdowns(1)}} key={game.id}>{game.name}
+
{ _handle_game_select(game.id.toString(), game.name); _handle_dropdowns(1) }} key={game.id}>{game.name}
))}
@@ -141,45 +156,45 @@ const UploadRunDialog: React.FC = ({ token, open, onClose, !loading && ( <> -
-
-

Select Map

-
_handle_dropdowns(2)} style={{display: "flex", alignItems: "center", cursor: "pointer", justifyContent: "space-between"}}> - {currentMap} - +
+
+

Select Map

+
_handle_dropdowns(2)} style={{ display: "flex", alignItems: "center", cursor: "pointer", justifyContent: "space-between" }}> + {currentMap} + +
-
-
- - Host Demo - _handle_file_change(e, true)} /> - { - games[selectedGameID].is_coop && - ( - <> - Partner Demo - _handle_file_change(e, false)} /> - Partner ID - setUploadRunContent({ - ...uploadRunContent, - partner_id: e.target.value, - })} /> - - ) - } -
- -
-
- - -
-
) } -- cgit v1.2.3