tRemove redundant flag count/score from user table - scoreboard - Interactive scoreboard for CTF-like games
(HTM) git clone git://git.z3bra.org/scoreboard.git
(DIR) Log
(DIR) Files
(DIR) Refs
---
(DIR) commit dc2f40166091506f6da7f28f9c85c086f6b225a5
(DIR) parent 9ae93c35af4c5f5f17da95e9405c72788d3f0707
(HTM) Author: Willy Goiffon <contact@z3bra.org>
Date: Sat, 28 Sep 2024 11:42:48 +0200
Remove redundant flag count/score from user table
Diffstat:
M db.go | 51 ++++++++++++++++++++++---------
M main.go | 2 +-
M player.go | 61 ++++++++++++-------------------
3 files changed, 61 insertions(+), 53 deletions(-)
---
(DIR) diff --git a/db.go b/db.go
t@@ -26,8 +26,6 @@ const (
user(
name TEXT PRIMARY KEY,
hash TEXT NOT NULL UNIQUE,
- score INT,
- flag INT,
ts INT
);
CREATE TABLE IF NOT EXISTS
t@@ -74,10 +72,14 @@ func db_count_players(db *sql.DB) (int, error) {
return count, err
}
-func db_count_user_flags(db *sql.DB, name string) (int, error) {
+func db_get_user_score(db *sql.DB, name string) (int, error) {
var count int
- query := `SELECT count(*) FROM score WHERE name = ?;`
+ query := `SELECT
+ IFNULL(SUM(flag.score),0)
+ FROM flag
+ INNER JOIN score ON score.flag = flag.value
+ WHERE score.name = ?;`
row := db.QueryRow(query, name)
err := row.Scan(&count)
t@@ -85,19 +87,30 @@ func db_count_user_flags(db *sql.DB, name string) (int, error) {
return count, err
}
-func db_calculate_user_score(db *sql.DB, name string) (int, error) {
- var count int
+func db_get_user_flags(db *sql.DB, name string) ([]Flag, error) {
+ var flags []Flag
query := `SELECT
- sum(flag.score)
+ flag.rowid,flag.value,flag.badge,flag.score
FROM flag
INNER JOIN score ON score.flag = flag.value
WHERE score.name = ?;`
- row := db.QueryRow(query, name)
- err := row.Scan(&count)
+ rows, err := db.Query(query, name)
+ if err != nil {
+ return nil, err
+ }
- return count, err
+ for rows.Next() {
+ var f Flag
+ err := rows.Scan(&f.id, &f.value, &f.badge, &f.score)
+ if err != nil {
+ return nil, err
+ }
+ flags = append(flags, f)
+ }
+
+ return flags, err
}
func db_get_flag(db *sql.DB, flag string) (Flag, error) {
t@@ -135,12 +148,17 @@ func db_get_flags(db *sql.DB) ([]Flag, error) {
func db_ranked_players(db *sql.DB, offset, limit int) ([]Player, error) {
query := `SELECT
- name,flag,score,ts
+ user.name,
+ user.ts,
+ IFNULL(SUM(flag.score),0) as score
FROM user
+ FULL OUTER JOIN score ON user.name = score.name
+ FULL OUTER JOIN flag ON flag.value = score.flag
+ WHERE user.name IS NOT NULL
+ GROUP BY user.name
ORDER BY
score DESC,
- flag DESC,
- ts ASC
+ user.ts ASC
LIMIT ?
OFFSET ?
;`
t@@ -153,7 +171,12 @@ func db_ranked_players(db *sql.DB, offset, limit int) ([]Player, error) {
players := make([]Player, 0)
for rows.Next() {
var p Player
- err := rows.Scan(&p.name, &p.flag, &p.score, &p.ts)
+ p.db = db;
+ err := rows.Scan(&p.name, &p.ts, &p.score)
+ if err != nil {
+ return nil, err
+ }
+ err = p.Fetch()
if err != nil {
return nil, err
}
(DIR) diff --git a/main.go b/main.go
t@@ -126,7 +126,7 @@ func main() {
scoreboard.board = tview.NewFlex()
scoreboard.player = &Player{
db: scoreboard.db,
- flag: 0,
+ flags: make([]Flag,0),
score: 0,
ts: time.Now().Unix(),
token: "",
(DIR) diff --git a/player.go b/player.go
t@@ -33,7 +33,7 @@ type Player struct {
id int
token string
name string
- flag int
+ flags []Flag
score int
ts int64
}
t@@ -87,20 +87,23 @@ func (p *Player) Register() error {
}
func (p *Player) Fetch() error {
+ var err error
+
/* Fill player struct with basic info */
query := `SELECT rowid,ts FROM user WHERE name = ?;`
row := p.db.QueryRow(query, p.name)
row.Scan(&p.id, &p.ts)
/* Calculate score based on submitted flags */
- query = `SELECT
- COUNT(flag.score), SUM(flag.score)
- FROM flag
- INNER JOIN score ON score.flag = flag.value
- WHERE score.name = ?;`
+ p.flags, err = db_get_user_flags(p.db, p.name)
+ if err != nil {
+ return err
+ }
- row = p.db.QueryRow(query, p.name)
- row.Scan(&p.flag, &p.score)
+ p.score, err = db_get_user_score(p.db, p.name)
+ if err != nil {
+ return err
+ }
return nil
}
t@@ -108,18 +111,18 @@ func (p *Player) Fetch() error {
func (p *Player) Refresh(ts int64) error {
var err error
- p.flag, err = db_count_user_flags(p.db, p.name)
+ p.flags, err = db_get_user_flags(p.db, p.name)
if err != nil {
return err
}
- p.score, err = db_calculate_user_score(p.db, p.name)
+ p.score, err = db_get_user_score(p.db, p.name)
if err != nil {
return err
}
- query := `UPDATE user SET score = ?, flag = ?, ts = ? WHERE name = ?;`
- _, err = p.db.Exec(query, p.score, p.flag, ts, p.name)
+ query := `UPDATE user ts = ? WHERE name = ?;`
+ _, err = p.db.Exec(query, ts, p.name)
if err != nil {
return err
}
t@@ -144,32 +147,13 @@ func (p *Player) ScoreRank() int {
}
func (p *Player) FlagStr() string {
- return fmt.Sprintf("%2d/%d", p.flag, len(scoreboard.flag_ref))
+ return fmt.Sprintf("%2d/%d", len(p.flags), len(scoreboard.flag_ref))
}
func (p *Player) FlagsStr() string {
var flaglist strings.Builder
- var flags []Flag
-
- query := `SELECT
- flag.badge, flag.value, flag.score
- FROM flag
- INNER JOIN score ON score.flag = flag.value
- WHERE score.name = ?;`
-
- rows, err := p.db.Query(query, p.name)
- if err != nil {
- return ""
- }
-
- for rows.Next() {
- var f Flag
- err := rows.Scan(&f.badge, &f.value, &f.score)
- if err != nil {
- return ""
- }
- flags = append(flags, f)
+ for _, f := range p.flags {
flaglist.WriteString(fmt.Sprintf("%s %s\n", f.badge, f.value))
}
t@@ -183,7 +167,7 @@ func (p *Player) FlagsStr() string {
}
flaglist.WriteString("[::-][::d]")
for _, r := range scoreboard.flag_ref {
- if !hasflag(flags, r) {
+ if !hasflag(p.flags, r) {
flaglist.WriteString(fmt.Sprintf("[::d]%s ................................................................[::-]\n", r.badge))
}
}
t@@ -224,12 +208,12 @@ func (p *Player) BadgeStr() string {
}
return false
}
- badges.WriteString("[::-]")
+ badges.WriteString("[-::-]")
for _, r := range scoreboard.flag_ref {
if hasflag(flags, r) {
- badges.WriteString(fmt.Sprintf("[::b]%s[::-]", r.badge))
+ badges.WriteString(fmt.Sprintf("%s", r.badge))
} else {
- badges.WriteString(fmt.Sprintf("[::d]%s[::-]", r.badge))
+ badges.WriteString(fmt.Sprintf("[black::d]%s[-::-]", r.badge))
}
}
t@@ -314,10 +298,11 @@ func (p *Player) FromToken(token string) error {
query := `SELECT name,flag,score,ts FROM user WHERE hash = ?`
row := p.db.QueryRow(query, hash)
- err = row.Scan(&p.name, &p.flag, &p.score, &p.ts)
+ err = row.Scan(&p.name, &p.ts)
if err == sql.ErrNoRows {
return errors.New("Unknown token")
}
+ p.Fetch()
return nil
}