itTransfer ranking request as database logic - scoreboard - Interactive scoreboard for CTF-like games	Err	z3bra.org	70
hgit clone git://git.z3bra.org/scoreboard.git	URL:git://git.z3bra.org/scoreboard.git	z3bra.org	70
1Log	/scm/scoreboard/log.gph	z3bra.org	70
1Files	/scm/scoreboard/files.gph	z3bra.org	70
1Refs	/scm/scoreboard/refs.gph	z3bra.org	70
i---	Err	z3bra.org	70
1commit e64e15888e10934f5d3ee1c75facb402562a0549	/scm/scoreboard/commit/e64e15888e10934f5d3ee1c75facb402562a0549.gph	z3bra.org	70
1parent 61269ee4372b67a9d212fc9227c2c46acbb58f39	/scm/scoreboard/commit/61269ee4372b67a9d212fc9227c2c46acbb58f39.gph	z3bra.org	70
hAuthor: Willy Goiffon <contact@z3bra.org>	URL:mailto:contact@z3bra.org	z3bra.org	70
iDate:   Tue,  1 Oct 2024 00:40:02 +0200	Err	z3bra.org	70
i	Err	z3bra.org	70
iTransfer ranking request as database logic	Err	z3bra.org	70
i	Err	z3bra.org	70
iDiffstat:	Err	z3bra.org	70
i  M db.go                               |      56 ++++++++++++++++++++++---------	Err	z3bra.org	70
i  M main.go                             |      15 ++++++++-------	Err	z3bra.org	70
i  M player.go                           |      71 +++++++++++++++++--------------	Err	z3bra.org	70
i  M playerbox.go                        |       2 +-	Err	z3bra.org	70
i  M ui.go                               |       2 +-	Err	z3bra.org	70
i	Err	z3bra.org	70
i5 files changed, 88 insertions(+), 58 deletions(-)	Err	z3bra.org	70
i---	Err	z3bra.org	70
1diff --git a/db.go b/db.go	/scm/scoreboard/file/db.go.gph	z3bra.org	70
it@@ -42,7 +42,6 @@ const (	Err	z3bra.org	70
i         `	Err	z3bra.org	70
i )	Err	z3bra.org	70
i 	Err	z3bra.org	70
i-	Err	z3bra.org	70
i func db_init(file string) (*sql.DB, error) {	Err	z3bra.org	70
i         var err error	Err	z3bra.org	70
i         var db *sql.DB	Err	z3bra.org	70
it@@ -72,21 +71,6 @@ func db_count_players(db *sql.DB) (int, error) {	Err	z3bra.org	70
i         return count, err	Err	z3bra.org	70
i }	Err	z3bra.org	70
i 	Err	z3bra.org	70
i-func db_get_user_score(db *sql.DB, name string) (int, error) {	Err	z3bra.org	70
i-        var count int	Err	z3bra.org	70
i-	Err	z3bra.org	70
i-        query := `SELECT	Err	z3bra.org	70
i-          IFNULL(SUM(flag.score),0)	Err	z3bra.org	70
i-          FROM flag	Err	z3bra.org	70
i-          INNER JOIN score ON score.flag = flag.value	Err	z3bra.org	70
i-          WHERE score.name = ?;`	Err	z3bra.org	70
i-	Err	z3bra.org	70
i-        row := db.QueryRow(query, name)	Err	z3bra.org	70
i-        err := row.Scan(&count)	Err	z3bra.org	70
i-	Err	z3bra.org	70
i-        return count, err	Err	z3bra.org	70
i-}	Err	z3bra.org	70
i-	Err	z3bra.org	70
i func db_get_user_flags(db *sql.DB, name string) ([]Flag, error) {	Err	z3bra.org	70
i         var flags []Flag	Err	z3bra.org	70
i 	Err	z3bra.org	70
it@@ -113,6 +97,46 @@ func db_get_user_flags(db *sql.DB, name string) ([]Flag, error) {	Err	z3bra.org	70
i         return flags, err	Err	z3bra.org	70
i }	Err	z3bra.org	70
i 	Err	z3bra.org	70
i+func db_get_user_score(db *sql.DB, name string) (int, error) {	Err	z3bra.org	70
i+        var count int	Err	z3bra.org	70
i+	Err	z3bra.org	70
i+        query := `SELECT	Err	z3bra.org	70
i+          IFNULL(SUM(flag.score),0)	Err	z3bra.org	70
i+          FROM flag	Err	z3bra.org	70
i+          INNER JOIN score ON score.flag = flag.value	Err	z3bra.org	70
i+          WHERE score.name = ?;`	Err	z3bra.org	70
i+	Err	z3bra.org	70
i+        row := db.QueryRow(query, name)	Err	z3bra.org	70
i+        err := row.Scan(&count)	Err	z3bra.org	70
i+	Err	z3bra.org	70
i+        return count, err	Err	z3bra.org	70
i+}	Err	z3bra.org	70
i+	Err	z3bra.org	70
i+func db_get_user_rank(db *sql.DB, name string) (int, error) {	Err	z3bra.org	70
i+        var rank int	Err	z3bra.org	70
i+	Err	z3bra.org	70
i+        score, err := db_get_user_score(db, name)	Err	z3bra.org	70
i+        if err != nil {	Err	z3bra.org	70
i+                return -1, err	Err	z3bra.org	70
i+        }	Err	z3bra.org	70
i+	Err	z3bra.org	70
i+        query := `SELECT COUNT(*) FROM (	Err	z3bra.org	70
i+          SELECT	Err	z3bra.org	70
i+            user.name, user.ts as ts, SUM(flag.score) as score	Err	z3bra.org	70
i+            FROM score	Err	z3bra.org	70
i+            INNER JOIN user ON user.name = score.name	Err	z3bra.org	70
i+            INNER JOIN flag ON flag.value = score.flag	Err	z3bra.org	70
i+            WHERE user.name != ?	Err	z3bra.org	70
i+            GROUP BY user.name	Err	z3bra.org	70
i+          ) WHERE score > ? OR (score == ? AND ts < (SELECT ts FROM user WHERE name = ?))	Err	z3bra.org	70
i+          ;`	Err	z3bra.org	70
i+	Err	z3bra.org	70
i+        row := db.QueryRow(query, name, score, score)	Err	z3bra.org	70
i+        row.Scan(&rank)	Err	z3bra.org	70
i+	Err	z3bra.org	70
i+        return rank, err	Err	z3bra.org	70
i+}	Err	z3bra.org	70
i+	Err	z3bra.org	70
i func db_get_flag(db *sql.DB, flag string) (Flag, error) {	Err	z3bra.org	70
i         var res Flag	Err	z3bra.org	70
i 	Err	z3bra.org	70
1diff --git a/main.go b/main.go	/scm/scoreboard/file/main.go.gph	z3bra.org	70
it@@ -15,6 +15,7 @@	Err	z3bra.org	70
i package main	Err	z3bra.org	70
i 	Err	z3bra.org	70
i import (	Err	z3bra.org	70
i+        "errors"	Err	z3bra.org	70
i         "flag"	Err	z3bra.org	70
i         "fmt"	Err	z3bra.org	70
i         "os"	Err	z3bra.org	70
it@@ -68,13 +69,13 @@ func usage() {	Err	z3bra.org	70
i }	Err	z3bra.org	70
i 	Err	z3bra.org	70
i 	Err	z3bra.org	70
i-func flagid(hash string) int {	Err	z3bra.org	70
i-        for i := 0; i<len(scoreboard.flag_ref); i++ {	Err	z3bra.org	70
i-                if strings.ToUpper(hash) == scoreboard.flag_ref[i].value {	Err	z3bra.org	70
i-                        return scoreboard.flag_ref[i].id	Err	z3bra.org	70
i+func checkflag(ref []Flag, flag string) (Flag, error) {	Err	z3bra.org	70
i+        for _, f := range ref {	Err	z3bra.org	70
i+                if strings.ToUpper(flag) == f.value {	Err	z3bra.org	70
i+                        return f, nil	Err	z3bra.org	70
i                 }	Err	z3bra.org	70
i         }	Err	z3bra.org	70
i-        return -1	Err	z3bra.org	70
i+        return Flag{}, errors.New("Unknown flag")	Err	z3bra.org	70
i }	Err	z3bra.org	70
i 	Err	z3bra.org	70
i func pageBoard() tview.Primitive {	Err	z3bra.org	70
it@@ -162,7 +163,7 @@ func main() {	Err	z3bra.org	70
i 	Err	z3bra.org	70
i                 /* anything not a command is treated as a flag */	Err	z3bra.org	70
i                 default:	Err	z3bra.org	70
i-                        scoreboard.flag, err = db_get_flag(scoreboard.db, args[0])	Err	z3bra.org	70
i+                        scoreboard.flag, err = checkflag(scoreboard.flag_ref, args[0])	Err	z3bra.org	70
i                         if err != nil {	Err	z3bra.org	70
i                                 fmt.Println("Incorrect flag")	Err	z3bra.org	70
i                                 return	Err	z3bra.org	70
it@@ -174,7 +175,7 @@ func main() {	Err	z3bra.org	70
i                                         scoreboard.Fatal(err)	Err	z3bra.org	70
i                                         return	Err	z3bra.org	70
i                                 }	Err	z3bra.org	70
i-                                scoreboard.HighlightBoard(scoreboard.player.ScoreRank() + 1)	Err	z3bra.org	70
i+                                scoreboard.HighlightBoard(scoreboard.player.Rank() + 1)	Err	z3bra.org	70
i                                 scoreboard.pages.RemovePage("token")	Err	z3bra.org	70
i                                 scoreboard.GenerateHTML()	Err	z3bra.org	70
i                         })	Err	z3bra.org	70
1diff --git a/player.go b/player.go	/scm/scoreboard/file/player.go.gph	z3bra.org	70
it@@ -49,14 +49,13 @@ func randbuf(length int64) []byte {	Err	z3bra.org	70
i }	Err	z3bra.org	70
i 	Err	z3bra.org	70
i /* Generate a random base32 token using the provided input as a salt */	Err	z3bra.org	70
i-func mktoken(input string) (string, string, error) {	Err	z3bra.org	70
i-        key := randbuf(12)	Err	z3bra.org	70
i-        salt := base32.StdEncoding.EncodeToString([]byte(input))	Err	z3bra.org	70
i+func mktoken(key []byte, salt string) (string, string, error) {	Err	z3bra.org	70
i+        salt32 := base32.StdEncoding.EncodeToString([]byte(salt))	Err	z3bra.org	70
i 	Err	z3bra.org	70
i         token := key	Err	z3bra.org	70
i-        token = append(token, []byte(input)...)	Err	z3bra.org	70
i+        token = append(token, []byte(salt)...)	Err	z3bra.org	70
i 	Err	z3bra.org	70
i-        dk, err := scrypt.Key(key, []byte(salt), 1<<15, 8, 1, 32)	Err	z3bra.org	70
i+        dk, err := scrypt.Key(key, []byte(salt32), 1<<15, 8, 1, 32)	Err	z3bra.org	70
i         if err != nil {	Err	z3bra.org	70
i                 return "", "", err	Err	z3bra.org	70
i         }	Err	z3bra.org	70
it@@ -66,18 +65,40 @@ func mktoken(input string) (string, string, error) {	Err	z3bra.org	70
i         return token32, hash32, nil	Err	z3bra.org	70
i }	Err	z3bra.org	70
i 	Err	z3bra.org	70
i+func hashtoken(token string) (string, error) {	Err	z3bra.org	70
i+        var err error	Err	z3bra.org	70
i+	Err	z3bra.org	70
i+        blob, err := base32.StdEncoding.DecodeString(token)	Err	z3bra.org	70
i+        if err != nil {	Err	z3bra.org	70
i+                return "", err	Err	z3bra.org	70
i+        }	Err	z3bra.org	70
i+	Err	z3bra.org	70
i+        key := blob[:12]	Err	z3bra.org	70
i+        name := string(blob[12:])	Err	z3bra.org	70
i+	Err	z3bra.org	70
i+        salt := base32.StdEncoding.EncodeToString([]byte(name))	Err	z3bra.org	70
i+	Err	z3bra.org	70
i+        dk, err := scrypt.Key(key, []byte(salt), 1<<15, 8, 1, 32)	Err	z3bra.org	70
i+        if err != nil {	Err	z3bra.org	70
i+                return "", err	Err	z3bra.org	70
i+        }	Err	z3bra.org	70
i+        hash := base32.StdEncoding.EncodeToString(dk)	Err	z3bra.org	70
i+	Err	z3bra.org	70
i+        return hash, nil	Err	z3bra.org	70
i+}	Err	z3bra.org	70
i+	Err	z3bra.org	70
i /* Register a user in the database */	Err	z3bra.org	70
i func (p *Player) Register() error {	Err	z3bra.org	70
i         var hash string	Err	z3bra.org	70
i         var err error	Err	z3bra.org	70
i 	Err	z3bra.org	70
i         p.ts = time.Now().Unix()	Err	z3bra.org	70
i-        p.token, hash, err = mktoken(p.name)	Err	z3bra.org	70
i+        p.token, hash, err = mktoken(randbuf(12), p.name)	Err	z3bra.org	70
i         if err != nil {	Err	z3bra.org	70
i                 return err	Err	z3bra.org	70
i         }	Err	z3bra.org	70
i 	Err	z3bra.org	70
i-        query := `INSERT INTO user(name,hash,score,flag,ts) VALUES(?,?,0,0,?);`	Err	z3bra.org	70
i+        query := `INSERT INTO user(name,hash,ts) VALUES(?,?,?);`	Err	z3bra.org	70
i         _, err = p.db.Exec(query, p.name, hash, p.ts)	Err	z3bra.org	70
i         if err != nil {	Err	z3bra.org	70
i                 return err	Err	z3bra.org	70
it@@ -121,7 +142,7 @@ func (p *Player) Refresh(ts int64) error {	Err	z3bra.org	70
i                 return err	Err	z3bra.org	70
i         }	Err	z3bra.org	70
i 	Err	z3bra.org	70
i-        query := `UPDATE user ts = ? WHERE name = ?;`	Err	z3bra.org	70
i+        query := `UPDATE user SET ts = ? WHERE name = ?;`	Err	z3bra.org	70
i         _, err = p.db.Exec(query, ts, p.name)	Err	z3bra.org	70
i         if err != nil {	Err	z3bra.org	70
i                 return err	Err	z3bra.org	70
it@@ -132,18 +153,13 @@ func (p *Player) Refresh(ts int64) error {	Err	z3bra.org	70
i         return nil	Err	z3bra.org	70
i }	Err	z3bra.org	70
i 	Err	z3bra.org	70
i-func (p *Player) ScoreRank() int {	Err	z3bra.org	70
i-        var count int	Err	z3bra.org	70
i-        query := `SELECT	Err	z3bra.org	70
i-          count(*)	Err	z3bra.org	70
i-          FROM user	Err	z3bra.org	70
i-          WHERE	Err	z3bra.org	70
i-            name != ? AND (score > ? OR (score == ? AND ts <= ?))	Err	z3bra.org	70
i-        ;`	Err	z3bra.org	70
i+func (p *Player) Rank() int {	Err	z3bra.org	70
i+        rank, err := db_get_user_rank(p.db, p.name)	Err	z3bra.org	70
i+        if err != nil {	Err	z3bra.org	70
i+                return -1	Err	z3bra.org	70
i+        }	Err	z3bra.org	70
i 	Err	z3bra.org	70
i-        row := p.db.QueryRow(query, p.name, p.score, p.score, p.ts)	Err	z3bra.org	70
i-        row.Scan(&count)	Err	z3bra.org	70
i-        return count	Err	z3bra.org	70
i+        return rank	Err	z3bra.org	70
i }	Err	z3bra.org	70
i 	Err	z3bra.org	70
i func (p *Player) FlagStr() string {	Err	z3bra.org	70
it@@ -221,7 +237,7 @@ func (p *Player) BadgeStr() string {	Err	z3bra.org	70
i }	Err	z3bra.org	70
i 	Err	z3bra.org	70
i func (p *Player) RankStr() string {	Err	z3bra.org	70
i-        return humanize.Ordinal(p.ScoreRank() + 1)	Err	z3bra.org	70
i+        return humanize.Ordinal(p.Rank() + 1)	Err	z3bra.org	70
i }	Err	z3bra.org	70
i 	Err	z3bra.org	70
i func (p *Player) Exists() bool {	Err	z3bra.org	70
it@@ -279,23 +295,12 @@ func (p *Player) Submit(flag Flag) error {	Err	z3bra.org	70
i /* Retrieve username from given token */	Err	z3bra.org	70
i func (p *Player) FromToken(token string) error {	Err	z3bra.org	70
i         var err error	Err	z3bra.org	70
i-        blob, err := base32.StdEncoding.DecodeString(token)	Err	z3bra.org	70
i+        hash, err := hashtoken(token)	Err	z3bra.org	70
i         if err != nil {	Err	z3bra.org	70
i                 return err	Err	z3bra.org	70
i         }	Err	z3bra.org	70
i 	Err	z3bra.org	70
i-        key := blob[:12]	Err	z3bra.org	70
i-        p.name = string(blob[12:])	Err	z3bra.org	70
i-	Err	z3bra.org	70
i-        salt := base32.StdEncoding.EncodeToString([]byte(p.name))	Err	z3bra.org	70
i-	Err	z3bra.org	70
i-        // use player id as salt	Err	z3bra.org	70
i-        dk, err := scrypt.Key(key, []byte(salt), 1<<15, 8, 1, 32)	Err	z3bra.org	70
i-        if err != nil {	Err	z3bra.org	70
i-                return err	Err	z3bra.org	70
i-        }	Err	z3bra.org	70
i-        hash := base32.StdEncoding.EncodeToString(dk)	Err	z3bra.org	70
i-        query := `SELECT name,flag,score,ts FROM user WHERE hash = ?`	Err	z3bra.org	70
i+        query := `SELECT name,ts FROM user WHERE hash = ?`	Err	z3bra.org	70
i 	Err	z3bra.org	70
i         row := p.db.QueryRow(query, hash)	Err	z3bra.org	70
i         err = row.Scan(&p.name, &p.ts)	Err	z3bra.org	70
1diff --git a/playerbox.go b/playerbox.go	/scm/scoreboard/file/playerbox.go.gph	z3bra.org	70
it@@ -90,7 +90,7 @@ func PlayerBoxName(p *Player) *tview.TextView {	Err	z3bra.org	70
i                                         if err != nil {	Err	z3bra.org	70
i                                                 scoreboard.Fatal(err)	Err	z3bra.org	70
i                                         }	Err	z3bra.org	70
i-                                        scoreboard.HighlightBoard(p.ScoreRank())	Err	z3bra.org	70
i+                                        scoreboard.HighlightBoard(p.Rank())	Err	z3bra.org	70
i                                         scoreboard.GenerateHTML()	Err	z3bra.org	70
i                                         scoreboard.Popup("CONGRATULATIONS", fmt.Sprintf(TOKEN_WELCOME, p.name, p.token));	Err	z3bra.org	70
i                                 } else {	Err	z3bra.org	70
1diff --git a/ui.go b/ui.go	/scm/scoreboard/file/ui.go.gph	z3bra.org	70
it@@ -135,7 +135,7 @@ func (a *Application) DrawBoard() {	Err	z3bra.org	70
i                 if event.Rune() == 'l' && a.player.token == "" {	Err	z3bra.org	70
i                         page := a.Token(func () {	Err	z3bra.org	70
i                                 a.pages.RemovePage("token")	Err	z3bra.org	70
i-                                a.HighlightBoard(a.player.ScoreRank() + 1)	Err	z3bra.org	70
i+                                a.HighlightBoard(a.player.Rank() + 1)	Err	z3bra.org	70
i                         })	Err	z3bra.org	70
i                         a.pages.AddAndSwitchToPage("token", page, true)	Err	z3bra.org	70
i                 }	Err	z3bra.org	70
.