itMove all database queries in db.go - 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 f94f68e67b93faabec57041a45ffaaf1343c3003	/scm/scoreboard/commit/f94f68e67b93faabec57041a45ffaaf1343c3003.gph	z3bra.org	70
1parent 352b4c819d43e2300a993307af95ca065138c504	/scm/scoreboard/commit/352b4c819d43e2300a993307af95ca065138c504.gph	z3bra.org	70
hAuthor: Willy Goiffon <contact@z3bra.org>	URL:mailto:contact@z3bra.org	z3bra.org	70
iDate:   Fri,  4 Oct 2024 01:08:07 +0200	Err	z3bra.org	70
i	Err	z3bra.org	70
iMove all database queries in db.go	Err	z3bra.org	70
i	Err	z3bra.org	70
iDiffstat:	Err	z3bra.org	70
i  M db.go                               |      43 ++++++++++++++++++++++++++++++-	Err	z3bra.org	70
i  M player.go                           |      65 +++++++++----------------------	Err	z3bra.org	70
i	Err	z3bra.org	70
i2 files changed, 61 insertions(+), 47 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@@ -60,6 +60,23 @@ func db_init(file string) (*sql.DB, error) {	Err	z3bra.org	70
i         return db, nil	Err	z3bra.org	70
i }	Err	z3bra.org	70
i 	Err	z3bra.org	70
i+func db_add_player(db *sql.DB, player Player, hash string) error {	Err	z3bra.org	70
i+        query := `INSERT INTO user(name,hash,ts) VALUES(?,?,?);`	Err	z3bra.org	70
i+        _, err := player.db.Exec(query, player.name, hash, player.ts)	Err	z3bra.org	70
i+	Err	z3bra.org	70
i+        return err	Err	z3bra.org	70
i+}	Err	z3bra.org	70
i+	Err	z3bra.org	70
i+func db_add_player_flag(db *sql.DB, player Player, flag Flag) error {	Err	z3bra.org	70
i+        query := `	Err	z3bra.org	70
i+          INSERT INTO score(name,flag) VALUES(?,?);	Err	z3bra.org	70
i+          UPDATE user SET ts = ? WHERE name = ?;	Err	z3bra.org	70
i+        `	Err	z3bra.org	70
i+        _, err := player.db.Exec(query, player.name, flag.value, player.ts, player.name)	Err	z3bra.org	70
i+	Err	z3bra.org	70
i+        return err	Err	z3bra.org	70
i+}	Err	z3bra.org	70
i+	Err	z3bra.org	70
i func db_count_players(db *sql.DB) (int, error) {	Err	z3bra.org	70
i         var count int	Err	z3bra.org	70
i 	Err	z3bra.org	70
it@@ -71,6 +88,29 @@ 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(db *sql.DB, p *Player, hash string) (*Player, error) {	Err	z3bra.org	70
i+	Err	z3bra.org	70
i+        query := `SELECT rowid,name,ts FROM user WHERE hash = ? OR name = ?;`	Err	z3bra.org	70
i+        row := db.QueryRow(query, hash, p.name)	Err	z3bra.org	70
i+        err := row.Scan(&p.id, &p.name, &p.ts)	Err	z3bra.org	70
i+        if err != nil {	Err	z3bra.org	70
i+                return p, err	Err	z3bra.org	70
i+        }	Err	z3bra.org	70
i+	Err	z3bra.org	70
i+        p.flags, err = db_get_user_flags(db, p.name)	Err	z3bra.org	70
i+        if err != nil {	Err	z3bra.org	70
i+                return p, err	Err	z3bra.org	70
i+        }	Err	z3bra.org	70
i+	Err	z3bra.org	70
i+        p.score, err = db_get_user_score(db, p.name)	Err	z3bra.org	70
i+        if err != nil {	Err	z3bra.org	70
i+                return p, err	Err	z3bra.org	70
i+        }	Err	z3bra.org	70
i+	Err	z3bra.org	70
i+        return p, nil	Err	z3bra.org	70
i+}	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@@ -172,6 +212,7 @@ func db_get_flags(db *sql.DB) ([]Flag, error) {	Err	z3bra.org	70
i 	Err	z3bra.org	70
i func db_ranked_players(db *sql.DB, offset, limit int) ([]Player, error) {	Err	z3bra.org	70
i         query := `SELECT	Err	z3bra.org	70
i+            user.rowid,	Err	z3bra.org	70
i             user.name,	Err	z3bra.org	70
i             user.ts,	Err	z3bra.org	70
i             IFNULL(SUM(flag.score),0) as score	Err	z3bra.org	70
it@@ -196,7 +237,7 @@ func db_ranked_players(db *sql.DB, offset, limit int) ([]Player, error) {	Err	z3bra.org	70
i         for rows.Next() {	Err	z3bra.org	70
i                 var p Player	Err	z3bra.org	70
i                 p.db = db;	Err	z3bra.org	70
i-                err := rows.Scan(&p.name, &p.ts, &p.score)	Err	z3bra.org	70
i+                err := rows.Scan(&p.id, &p.name, &p.ts, &p.score)	Err	z3bra.org	70
i                 if err != nil {	Err	z3bra.org	70
i                         return nil, err	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@@ -65,12 +65,12 @@ func mktoken(key []byte, salt 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, string, error) {	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+                return "", err	Err	z3bra.org	70
i         }	Err	z3bra.org	70
i 	Err	z3bra.org	70
i         key := blob[:12]	Err	z3bra.org	70
it@@ -80,11 +80,11 @@ func hashtoken(token string) (string, string, error) {	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+                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 name, hash, nil	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
it@@ -98,13 +98,7 @@ func (p *Player) Register() error {	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,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
i-        }	Err	z3bra.org	70
i-	Err	z3bra.org	70
i-        return nil	Err	z3bra.org	70
i+        return db_add_player(p.db, *p, hash)	Err	z3bra.org	70
i }	Err	z3bra.org	70
i 	Err	z3bra.org	70
i func (p *Player) Fetch() error {	Err	z3bra.org	70
it@@ -112,23 +106,13 @@ func (p *Player) Fetch() error {	Err	z3bra.org	70
i         var hash string	Err	z3bra.org	70
i 	Err	z3bra.org	70
i         if (p.token != "") {	Err	z3bra.org	70
i-                p.name, hash, err = hashtoken(p.token)	Err	z3bra.org	70
i+                hash, err = hashtoken(p.token)	Err	z3bra.org	70
i         }	Err	z3bra.org	70
i 	Err	z3bra.org	70
i         /* Fill player struct with basic info */	Err	z3bra.org	70
i-        query := `SELECT rowid,ts FROM user WHERE hash = ? OR name = ?;`	Err	z3bra.org	70
i-        row := p.db.QueryRow(query, hash, p.name)	Err	z3bra.org	70
i-        row.Scan(&p.id, &p.ts)	Err	z3bra.org	70
i-	Err	z3bra.org	70
i-        /* Calculate score based on submitted flags */	Err	z3bra.org	70
i-        p.flags, err = db_get_user_flags(p.db, 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-        p.score, err = db_get_user_score(p.db, p.name)	Err	z3bra.org	70
i-        if err != nil {	Err	z3bra.org	70
i-                return err	Err	z3bra.org	70
i+        p, err = db_get_user(p.db, p, hash)	Err	z3bra.org	70
i+        if errors.Is(err, sql.ErrNoRows) {	Err	z3bra.org	70
i+                return errors.New("Player not found")	Err	z3bra.org	70
i         }	Err	z3bra.org	70
i 	Err	z3bra.org	70
i         return nil	Err	z3bra.org	70
it@@ -198,25 +182,18 @@ func (p *Player) Exists() bool {	Err	z3bra.org	70
i                 }	Err	z3bra.org	70
i         }	Err	z3bra.org	70
i 	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 score	Err	z3bra.org	70
i-          WHERE	Err	z3bra.org	70
i-            name = ?	Err	z3bra.org	70
i-        ;`	Err	z3bra.org	70
i-	Err	z3bra.org	70
i-        row := p.db.QueryRow(query, p.name)	Err	z3bra.org	70
i-        row.Scan(&count)	Err	z3bra.org	70
i-        return (count > 0)	Err	z3bra.org	70
i+        _, err := db_get_user(p.db, p, "")	Err	z3bra.org	70
i+	Err	z3bra.org	70
i+        return !errors.Is(err, sql.ErrNoRows)	Err	z3bra.org	70
i }	Err	z3bra.org	70
i 	Err	z3bra.org	70
i func (p *Player) HasFlag(flag Flag) bool {	Err	z3bra.org	70
i-        var count int	Err	z3bra.org	70
i-        query := `SELECT count(*) FROM score WHERE name = ? AND flag = ?;`	Err	z3bra.org	70
i-        row := p.db.QueryRow(query, p.name, flag.value)	Err	z3bra.org	70
i-        row.Scan(&count)	Err	z3bra.org	70
i-        return (count > 0)	Err	z3bra.org	70
i+        for _, f := range p.flags {	Err	z3bra.org	70
i+                if f.value == flag.value {	Err	z3bra.org	70
i+                        return true	Err	z3bra.org	70
i+                }	Err	z3bra.org	70
i+        }	Err	z3bra.org	70
i+        return false	Err	z3bra.org	70
i }	Err	z3bra.org	70
i 	Err	z3bra.org	70
i func (p *Player) Submit(flag Flag) error {	Err	z3bra.org	70
it@@ -226,11 +203,7 @@ func (p *Player) Submit(flag Flag) error {	Err	z3bra.org	70
i                 return errors.New("Flag already submitted")	Err	z3bra.org	70
i         } else {	Err	z3bra.org	70
i                 p.ts = time.Now().Unix()	Err	z3bra.org	70
i-                query := `	Err	z3bra.org	70
i-                  INSERT INTO score(name,flag) VALUES(?,?);	Err	z3bra.org	70
i-                  UPDATE user SET ts = ? WHERE name = ?;	Err	z3bra.org	70
i-                `	Err	z3bra.org	70
i-                _, err = p.db.Exec(query, p.name, flag.value, p.ts, p.name)	Err	z3bra.org	70
i+                err = db_add_player_flag(p.db, *p, flag)	Err	z3bra.org	70
i                 if err != nil {	Err	z3bra.org	70
i                         return err	Err	z3bra.org	70
i                 }	Err	z3bra.org	70
.