tdb.go - scoreboard - Interactive scoreboard for CTF-like games
(HTM) git clone git://git.z3bra.org/scoreboard.git
(DIR) Log
(DIR) Files
(DIR) Refs
---
tdb.go (5317B)
---
1 // Copyright 2016 The Tcell Authors
2 //
3 // Licensed under the Apache License, Version 2.0 (the "License");
4 // you may not use file except in compliance with the License.
5 // You may obtain a copy of the license at
6 //
7 // http://www.apache.org/licenses/LICENSE-2.0
8 //
9 // Unless required by applicable law or agreed to in writing, software
10 // distributed under the License is distributed on an "AS IS" BASIS,
11 // WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
12 // See the License for the specific language governing permissions and
13 // limitations under the License.
14
15 package main
16
17 import (
18 "database/sql"
19 _ "modernc.org/sqlite"
20 )
21
22 const (
23 // DB queries
24 DB_CREATE string = `
25 CREATE TABLE IF NOT EXISTS
26 user(
27 name TEXT PRIMARY KEY,
28 hash TEXT NOT NULL UNIQUE,
29 ts INT
30 );
31 CREATE TABLE IF NOT EXISTS
32 flag(
33 value TEXT PRIMARY KEY,
34 badge TEXT,
35 score INT
36 );
37 CREATE TABLE IF NOT EXISTS
38 score(
39 name TEXT,
40 flag TEXT
41 );
42 `
43 )
44
45 func db_init(file string) (*sql.DB, error) {
46 var err error
47 var db *sql.DB
48
49 // open database
50 db, err = sql.Open("sqlite", file)
51 if err != nil {
52 return nil, err
53 }
54
55 // create schema if needed
56 _, err = db.Exec(DB_CREATE)
57 if err != nil {
58 return nil, err
59 }
60 return db, nil
61 }
62
63 func db_add_player(db *sql.DB, player Player, hash string) error {
64 query := `INSERT INTO user(name,hash,ts) VALUES(?,?,?);`
65 _, err := player.db.Exec(query, player.name, hash, player.ts)
66
67 return err
68 }
69
70 func db_add_player_flag(db *sql.DB, player Player, flag Flag) error {
71 query := `
72 INSERT INTO score(name,flag) VALUES(?,?);
73 UPDATE user SET ts = ? WHERE name = ?;
74 `
75 _, err := player.db.Exec(query, player.name, flag.value, player.ts, player.name)
76
77 return err
78 }
79
80 func db_count_players(db *sql.DB) (int, error) {
81 var count int
82
83 query := `SELECT count(*) FROM user;`
84
85 row := db.QueryRow(query)
86 err := row.Scan(&count)
87
88 return count, err
89 }
90
91 func db_get_user(db *sql.DB, p *Player, hash string) (*Player, error) {
92
93 query := `SELECT rowid,name,ts FROM user WHERE hash = ? OR name = ?;`
94 row := db.QueryRow(query, hash, p.name)
95 err := row.Scan(&p.id, &p.name, &p.ts)
96 if err != nil {
97 return p, err
98 }
99
100 p.flags, err = db_get_user_flags(db, p.name)
101 if err != nil {
102 return p, err
103 }
104
105 p.score, err = db_get_user_score(db, p.name)
106 if err != nil {
107 return p, err
108 }
109
110 return p, nil
111 }
112
113
114 func db_get_user_flags(db *sql.DB, name string) ([]Flag, error) {
115 var flags []Flag
116
117 query := `SELECT
118 flag.rowid,flag.value,flag.badge,flag.score
119 FROM flag
120 INNER JOIN score ON score.flag = flag.value
121 WHERE score.name = ?;`
122
123 rows, err := db.Query(query, name)
124 if err != nil {
125 return nil, err
126 }
127
128 for rows.Next() {
129 var f Flag
130 err := rows.Scan(&f.id, &f.value, &f.badge, &f.score)
131 if err != nil {
132 return nil, err
133 }
134 flags = append(flags, f)
135 }
136
137 return flags, err
138 }
139
140 func db_get_user_score(db *sql.DB, name string) (int, error) {
141 var count int
142
143 query := `SELECT
144 IFNULL(SUM(flag.score),0)
145 FROM flag
146 INNER JOIN score ON score.flag = flag.value
147 WHERE score.name = ?;`
148
149 row := db.QueryRow(query, name)
150 err := row.Scan(&count)
151
152 return count, err
153 }
154
155 func db_get_user_rank(db *sql.DB, name string) (int, error) {
156 var rank int
157
158 score, err := db_get_user_score(db, name)
159 if err != nil {
160 return -1, err
161 }
162
163 query := `SELECT COUNT(*) FROM (
164 SELECT
165 user.name, user.ts as ts, SUM(flag.score) as score
166 FROM score
167 INNER JOIN user ON user.name = score.name
168 INNER JOIN flag ON flag.value = score.flag
169 WHERE user.name != ?
170 GROUP BY user.name
171 ) WHERE score > ? OR (score == ? AND ts < (SELECT ts FROM user WHERE name = ?))
172 ;`
173
174 row := db.QueryRow(query, name, score, score, name)
175 row.Scan(&rank)
176
177 return rank, err
178 }
179
180 func db_get_flag(db *sql.DB, flag string) (Flag, error) {
181 var res Flag
182
183 query := `SELECT rowid,value,badge,score FROM flag WHERE value = ?;`
184
185 row := db.QueryRow(query, flag)
186 err := row.Scan(&res.id, &res.value, &res.badge, &res.score)
187
188 return res, err
189 }
190
191
192 func db_get_flags(db *sql.DB) ([]Flag, error) {
193 query := `SELECT rowid,value,badge,score FROM flag ORDER BY score;`
194
195 rows, err := db.Query(query)
196 if err != nil {
197 return nil, err
198 }
199
200 flags := make([]Flag, 0)
201 for rows.Next() {
202 var flag Flag
203 err := rows.Scan(&flag.id, &flag.value, &flag.badge, &flag.score)
204 if err != nil {
205 return nil, err
206 }
207 flags = append(flags, flag)
208 }
209
210 return flags, nil
211 }
212
213 func db_ranked_players(db *sql.DB, offset, limit int) ([]Player, error) {
214 query := `SELECT
215 user.rowid,
216 user.name,
217 user.ts,
218 IFNULL(SUM(flag.score),0) as score
219 FROM user
220 FULL OUTER JOIN score ON user.name = score.name
221 FULL OUTER JOIN flag ON flag.value = score.flag
222 WHERE user.name IS NOT NULL
223 GROUP BY user.name
224 ORDER BY
225 score DESC,
226 user.ts ASC
227 LIMIT ?
228 OFFSET ?
229 ;`
230
231 rows, err := db.Query(query, limit, offset)
232 if err != nil {
233 return nil, err
234 }
235
236 players := make([]Player, 0)
237 for rows.Next() {
238 var p Player
239 p.db = db;
240 err := rows.Scan(&p.id, &p.name, &p.ts, &p.score)
241 if err != nil {
242 return nil, err
243 }
244 err = p.Fetch()
245 if err != nil {
246 return nil, err
247 }
248 players = append(players, p)
249 }
250
251 return players, nil
252 }