tMove all database queries in db.go - scoreboard - Interactive scoreboard for CTF-like games
HTML git clone git://git.z3bra.org/scoreboard.git
DIR Log
DIR Files
DIR Refs
---
DIR commit f94f68e67b93faabec57041a45ffaaf1343c3003
DIR parent 352b4c819d43e2300a993307af95ca065138c504
HTML Author: Willy Goiffon <contact@z3bra.org>
Date: Fri, 4 Oct 2024 01:08:07 +0200
Move all database queries in db.go
Diffstat:
M db.go | 43 ++++++++++++++++++++++++++++++-
M player.go | 65 +++++++++----------------------
2 files changed, 61 insertions(+), 47 deletions(-)
---
DIR diff --git a/db.go b/db.go
t@@ -60,6 +60,23 @@ func db_init(file string) (*sql.DB, error) {
return db, nil
}
+func db_add_player(db *sql.DB, player Player, hash string) error {
+ query := `INSERT INTO user(name,hash,ts) VALUES(?,?,?);`
+ _, err := player.db.Exec(query, player.name, hash, player.ts)
+
+ return err
+}
+
+func db_add_player_flag(db *sql.DB, player Player, flag Flag) error {
+ query := `
+ INSERT INTO score(name,flag) VALUES(?,?);
+ UPDATE user SET ts = ? WHERE name = ?;
+ `
+ _, err := player.db.Exec(query, player.name, flag.value, player.ts, player.name)
+
+ return err
+}
+
func db_count_players(db *sql.DB) (int, error) {
var count int
t@@ -71,6 +88,29 @@ func db_count_players(db *sql.DB) (int, error) {
return count, err
}
+func db_get_user(db *sql.DB, p *Player, hash string) (*Player, error) {
+
+ query := `SELECT rowid,name,ts FROM user WHERE hash = ? OR name = ?;`
+ row := db.QueryRow(query, hash, p.name)
+ err := row.Scan(&p.id, &p.name, &p.ts)
+ if err != nil {
+ return p, err
+ }
+
+ p.flags, err = db_get_user_flags(db, p.name)
+ if err != nil {
+ return p, err
+ }
+
+ p.score, err = db_get_user_score(db, p.name)
+ if err != nil {
+ return p, err
+ }
+
+ return p, nil
+}
+
+
func db_get_user_flags(db *sql.DB, name string) ([]Flag, error) {
var flags []Flag
t@@ -172,6 +212,7 @@ func db_get_flags(db *sql.DB) ([]Flag, error) {
func db_ranked_players(db *sql.DB, offset, limit int) ([]Player, error) {
query := `SELECT
+ user.rowid,
user.name,
user.ts,
IFNULL(SUM(flag.score),0) as score
t@@ -196,7 +237,7 @@ func db_ranked_players(db *sql.DB, offset, limit int) ([]Player, error) {
for rows.Next() {
var p Player
p.db = db;
- err := rows.Scan(&p.name, &p.ts, &p.score)
+ err := rows.Scan(&p.id, &p.name, &p.ts, &p.score)
if err != nil {
return nil, err
}
DIR diff --git a/player.go b/player.go
t@@ -65,12 +65,12 @@ func mktoken(key []byte, salt string) (string, string, error) {
return token32, hash32, nil
}
-func hashtoken(token string) (string, string, error) {
+func hashtoken(token string) (string, error) {
var err error
blob, err := base32.StdEncoding.DecodeString(token)
if err != nil {
- return "", "", err
+ return "", err
}
key := blob[:12]
t@@ -80,11 +80,11 @@ func hashtoken(token string) (string, string, error) {
dk, err := scrypt.Key(key, []byte(salt), 1<<15, 8, 1, 32)
if err != nil {
- return "", "", err
+ return "", err
}
hash := base32.StdEncoding.EncodeToString(dk)
- return name, hash, nil
+ return hash, nil
}
/* Register a user in the database */
t@@ -98,13 +98,7 @@ func (p *Player) Register() error {
return err
}
- query := `INSERT INTO user(name,hash,ts) VALUES(?,?,?);`
- _, err = p.db.Exec(query, p.name, hash, p.ts)
- if err != nil {
- return err
- }
-
- return nil
+ return db_add_player(p.db, *p, hash)
}
func (p *Player) Fetch() error {
t@@ -112,23 +106,13 @@ func (p *Player) Fetch() error {
var hash string
if (p.token != "") {
- p.name, hash, err = hashtoken(p.token)
+ hash, err = hashtoken(p.token)
}
/* Fill player struct with basic info */
- query := `SELECT rowid,ts FROM user WHERE hash = ? OR name = ?;`
- row := p.db.QueryRow(query, hash, p.name)
- row.Scan(&p.id, &p.ts)
-
- /* Calculate score based on submitted flags */
- p.flags, err = db_get_user_flags(p.db, p.name)
- if err != nil {
- return err
- }
-
- p.score, err = db_get_user_score(p.db, p.name)
- if err != nil {
- return err
+ p, err = db_get_user(p.db, p, hash)
+ if errors.Is(err, sql.ErrNoRows) {
+ return errors.New("Player not found")
}
return nil
t@@ -198,25 +182,18 @@ func (p *Player) Exists() bool {
}
}
- var count int
- query := `SELECT
- count(*)
- FROM score
- WHERE
- name = ?
- ;`
-
- row := p.db.QueryRow(query, p.name)
- row.Scan(&count)
- return (count > 0)
+ _, err := db_get_user(p.db, p, "")
+
+ return !errors.Is(err, sql.ErrNoRows)
}
func (p *Player) HasFlag(flag Flag) bool {
- var count int
- query := `SELECT count(*) FROM score WHERE name = ? AND flag = ?;`
- row := p.db.QueryRow(query, p.name, flag.value)
- row.Scan(&count)
- return (count > 0)
+ for _, f := range p.flags {
+ if f.value == flag.value {
+ return true
+ }
+ }
+ return false
}
func (p *Player) Submit(flag Flag) error {
t@@ -226,11 +203,7 @@ func (p *Player) Submit(flag Flag) error {
return errors.New("Flag already submitted")
} else {
p.ts = time.Now().Unix()
- query := `
- INSERT INTO score(name,flag) VALUES(?,?);
- UPDATE user SET ts = ? WHERE name = ?;
- `
- _, err = p.db.Exec(query, p.name, flag.value, p.ts, p.name)
+ err = db_add_player_flag(p.db, *p, flag)
if err != nil {
return err
}