1 package zenith
2
3 import (
4 "database/sql"
5 "fmt"
6 "strconv"
7 "strings"
8 "sync"
9 )
10
11
12 var DatabaseTables = map[string][]string{
13 "match": {
14 "`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT",
15 "`timestamp` INTEGER NOT NULL DEFAULT 0",
16 "`game` INTEGER NOT NULL DEFAULT 0",
17 "`match` INTEGER NOT NULL DEFAULT 0",
18 "`player` INTEGER NOT NULL DEFAULT 0",
19 "`outcome` INTEGER NOT NULL DEFAULT 0",
20 },
21 "matchmeta": {
22 "`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT",
23 "`game` INTEGER NOT NULL DEFAULT 0",
24 "`match` INTEGER NOT NULL DEFAULT 0",
25 "`player` INTEGER NOT NULL DEFAULT 0",
26 "`key` VARCHAR(255) NOT NULL DEFAULT ''",
27 "`value` TEXT NOT NULL DEFAULT ''",
28 },
29 "meta": {
30 "`key` VARCHAR(255) NOT NULL PRIMARY KEY",
31 "`value` TEXT NOT NULL DEFAULT ''",
32 }}
33
34 const DatabaseVersion = 1
35
36 var DatabasePrefix string
37
38 type Database struct {
39 db *sql.DB
40 funcGreatest string
41 driver string
42 sync.RWMutex
43 }
44
45 func Connect(driver string, dataSource string) (*Database, error) {
46 var err error
47 d := new(Database)
48
49 d.db, err = sql.Open(driver, dataSource)
50 if err != nil {
51 return nil, fmt.Errorf("failed to connect to database: %s", err)
52 }
53
54 d.funcGreatest = "GREATEST"
55 if driver == "sqlite3" {
56 d.funcGreatest = "MAX"
57
58 _, err = d.db.Exec(`PRAGMA encoding="UTF-8"`)
59 if err != nil {
60 return nil, fmt.Errorf("failed to send PRAGMA: %s", err)
61 }
62 }
63
64 err = d.CreateTables()
65 if err != nil {
66 _ = d.db.Close()
67 return nil, fmt.Errorf("failed to create tables: %s", err)
68 }
69
70 err = d.Migrate()
71 if err != nil {
72 _ = d.db.Close()
73 return nil, fmt.Errorf("failed to migrate database: %s", err)
74 }
75
76 return d, nil
77 }
78
79 func (d *Database) CreateTables() error {
80 var (
81 tcolumns string
82 err error
83 )
84
85 createQueryExtra := ""
86 if d.driver == "mysql" {
87 createQueryExtra = " ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_unicode_ci"
88 }
89
90 for tname, tcols := range DatabaseTables {
91 tname = DatabasePrefix + tname
92
93 tcolumns = strings.Join(tcols, ",")
94 if d.driver == "mysql" {
95 tcolumns = strings.Replace(tcolumns, "AUTOINCREMENT", "AUTO_INCREMENT", -1)
96 }
97
98 _, err = d.db.Exec(fmt.Sprintf("CREATE TABLE IF NOT EXISTS `%s` (%s)", tname, tcolumns) + createQueryExtra)
99 if err != nil {
100 return fmt.Errorf("failed to create table %s: %s", tname, err)
101 }
102 }
103
104 return nil
105 }
106
107 func (d *Database) Migrate() error {
108 rows, err := d.db.Query("SELECT `value` FROM "+DatabasePrefix+"meta WHERE `key`=?", "version")
109 if err != nil {
110 return fmt.Errorf("failed to fetch database version: %s", err)
111 }
112
113 version := 0
114 for rows.Next() {
115 v := ""
116 err = rows.Scan(&v)
117 if err != nil {
118 return fmt.Errorf("failed to scan database meta: %s", err)
119 }
120
121 version, err = strconv.Atoi(v)
122 if err != nil {
123 version = -1
124 }
125 }
126
127 if version == -1 {
128 panic("Unable to migrate database: database version unknown")
129 } else if version == 0 {
130 _, err := d.db.Exec("UPDATE "+DatabasePrefix+"meta SET `value`=? WHERE `key`=?", strconv.Itoa(DatabaseVersion), "version")
131 if err != nil {
132 return fmt.Errorf("failed to save database version: %s", err)
133 }
134 }
135
136 migrated := false
137 for version < DatabaseVersion {
138 switch version {
139 case 1:
140
141 }
142
143 version++
144 migrated = true
145 }
146
147 if migrated {
148 _, err := d.db.Exec("UPDATE "+DatabasePrefix+"meta SET `value`=? WHERE `key`=?", strconv.Itoa(DatabaseVersion), "version")
149 if err != nil {
150 return fmt.Errorf("failed to save updated database version: %s", err)
151 }
152 }
153
154 return nil
155 }
156
157 func (d *Database) Track(results ...*MatchResult) error {
158 d.Lock()
159 defer d.Unlock()
160
161 for i := range results {
162 _, err := d.db.Exec("INSERT INTO "+DatabasePrefix+"match (`timestamp`, `game`, `match`, `player`, `outcome`) VALUES (?, ?, ?, ?, ?)", results[i].Timestamp, results[i].Game, results[i].Match, results[i].Player, results[i].Outcome)
163 if err != nil {
164 return fmt.Errorf("failed to track match %+v: %s", results[i], err)
165 }
166 }
167
168 return nil
169 }
170
171
172 func (d *Database) Matches(game, player int) ([]*MatchResult, error) {
173 d.Lock()
174 defer d.Unlock()
175
176 rows, err := d.db.Query("SELECT * FROM "+DatabasePrefix+"match WHERE `game`=? AND `player`=?", game, player)
177 if err != nil {
178 return nil, fmt.Errorf("failed to fetch matches: %s", err)
179 }
180 defer rows.Close()
181
182 var matches []*MatchResult
183 for rows.Next() {
184 match, err := d.scanMatch(rows)
185 if err != nil {
186 return nil, fmt.Errorf("failed to scan matches: %s", err)
187 }
188
189 matches = append(matches, match)
190 }
191
192 return matches, nil
193 }
194
195 func (d *Database) Player(game, player int) (*Player, error) {
196 d.Lock()
197 defer d.Unlock()
198
199 p := &Player{}
200
201 rows, err := d.db.Query("SELECT COUNT(*) as c FROM "+DatabasePrefix+"match WHERE `game`=? AND `player`=? AND `outcome`=?", game, player, Won)
202 if err != nil {
203 return nil, fmt.Errorf("failed to fetch player: %s", err)
204 }
205 for rows.Next() {
206 err := rows.Scan(&p.Wins)
207 if err != nil {
208 rows.Close()
209 return nil, fmt.Errorf("failed to fetch player: %s", err)
210 }
211 }
212 rows.Close()
213
214 rows, err = d.db.Query("SELECT COUNT(*) as c FROM "+DatabasePrefix+"match WHERE `game`=? AND `player`=? AND `outcome`=?", game, player, Drew)
215 if err != nil {
216 return nil, fmt.Errorf("failed to fetch player: %s", err)
217 }
218 for rows.Next() {
219 err := rows.Scan(&p.Draws)
220 if err != nil {
221 rows.Close()
222 return nil, fmt.Errorf("failed to fetch player: %s", err)
223 }
224 }
225 rows.Close()
226
227 rows, err = d.db.Query("SELECT COUNT(*) as c FROM "+DatabasePrefix+"match WHERE `game`=? AND `player`=? AND `outcome`=?", game, player, Lost)
228 if err != nil {
229 return nil, fmt.Errorf("failed to fetch player: %s", err)
230 }
231 for rows.Next() {
232 err := rows.Scan(&p.Losses)
233 if err != nil {
234 rows.Close()
235 return nil, fmt.Errorf("failed to fetch player: %s", err)
236 }
237 }
238 rows.Close()
239
240 return p, nil
241 }
242
243 func (d *Database) Versus(game, player, versus int) (int, int, int, error) {
244 d.Lock()
245 defer d.Unlock()
246
247 playerMatches := make(map[int]bool)
248 versusMatches := make(map[int]bool)
249
250 var matchID int
251 var matchPlayer int
252
253 rows, err := d.db.Query("SELECT id, player FROM "+DatabasePrefix+"match WHERE `game`=? AND (`player`=? OR `player`=?)", game, player, versus)
254 if err != nil {
255 return 0, 0, 0, fmt.Errorf("failed to fetch player: %s", err)
256 }
257 for rows.Next() {
258 err := rows.Scan(&matchID, &matchPlayer)
259 if err != nil {
260 rows.Close()
261 return 0, 0, 0, fmt.Errorf("failed to fetch player: %s", err)
262 }
263
264 if matchPlayer == player {
265 playerMatches[matchID] = true
266 } else {
267 versusMatches[matchID] = true
268 }
269 }
270 rows.Close()
271
272 var matches []int
273 for matchID := range playerMatches {
274 if versusMatches[matchID] {
275 matches = append(matches, matchID)
276 }
277 }
278
279 matches = uniqueInts(matches)
280
281 var wins, draws, losses int
282 var addWins, addDraws, addLosses int
283
284 for match := range matches {
285 rows, err := d.db.Query("SELECT COUNT(*) as c FROM "+DatabasePrefix+"match WHERE `game`=? AND `match`=? AND `player`=? AND `outcome`=?", game, match, player, Won)
286 if err != nil {
287 return 0, 0, 0, fmt.Errorf("failed to fetch player: %s", err)
288 }
289 for rows.Next() {
290 err := rows.Scan(&addWins)
291 if err != nil {
292 rows.Close()
293 return 0, 0, 0, fmt.Errorf("failed to fetch player: %s", err)
294 }
295 wins += addWins
296 }
297 rows.Close()
298
299 rows, err = d.db.Query("SELECT COUNT(*) as c FROM "+DatabasePrefix+"match WHERE `game`=? AND `player`=? AND `outcome`=?", game, player, Drew)
300 if err != nil {
301 return 0, 0, 0, fmt.Errorf("failed to fetch player: %s", err)
302 }
303 for rows.Next() {
304 err := rows.Scan(&addDraws)
305 if err != nil {
306 rows.Close()
307 return 0, 0, 0, fmt.Errorf("failed to fetch player: %s", err)
308 }
309 draws += addDraws
310 }
311 rows.Close()
312
313 rows, err = d.db.Query("SELECT COUNT(*) as c FROM "+DatabasePrefix+"match WHERE `game`=? AND `player`=? AND `outcome`=?", game, player, Lost)
314 if err != nil {
315 return 0, 0, 0, fmt.Errorf("failed to fetch player: %s", err)
316 }
317 for rows.Next() {
318 err := rows.Scan(&addLosses)
319 if err != nil {
320 rows.Close()
321 return 0, 0, 0, fmt.Errorf("failed to fetch player: %s", err)
322 }
323 losses += addLosses
324 }
325 rows.Close()
326 }
327
328 return wins, draws, losses, nil
329 }
330
331 func (d *Database) scanMatch(rows *sql.Rows) (*MatchResult, error) {
332 m := &MatchResult{}
333 err := rows.Scan(&m.ID, &m.Timestamp, &m.Game, &m.Match, &m.Player, &m.Outcome)
334 if err != nil {
335 return nil, fmt.Errorf("failed to scan match: %s", err)
336 }
337
338 return m, nil
339 }
340
View as plain text