a geicko-2 based round robin ranking system designed to test c++ battleship submissions
battleship.dunkirk.sh
dunkirk.sh
1package main
2
3import (
4 "database/sql"
5 "time"
6
7 _ "github.com/mattn/go-sqlite3"
8)
9
10var globalDB *sql.DB
11
12type LeaderboardEntry struct {
13 Username string
14 Wins int
15 Losses int
16 AvgMoves float64
17 LastPlayed time.Time
18}
19
20type Submission struct {
21 ID int
22 Username string
23 Filename string
24 UploadTime time.Time
25 Status string // pending, testing, completed, failed
26}
27
28func initDB(path string) (*sql.DB, error) {
29 db, err := sql.Open("sqlite3", path+"?parseTime=true")
30 if err != nil {
31 return nil, err
32 }
33
34 schema := `
35 CREATE TABLE IF NOT EXISTS submissions (
36 id INTEGER PRIMARY KEY AUTOINCREMENT,
37 username TEXT NOT NULL,
38 filename TEXT NOT NULL,
39 upload_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
40 status TEXT DEFAULT 'pending',
41 is_active BOOLEAN DEFAULT 1
42 );
43
44 CREATE TABLE IF NOT EXISTS matches (
45 id INTEGER PRIMARY KEY AUTOINCREMENT,
46 player1_id INTEGER,
47 player2_id INTEGER,
48 winner_id INTEGER,
49 player1_moves INTEGER,
50 player2_moves INTEGER,
51 timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
52 FOREIGN KEY (player1_id) REFERENCES submissions(id),
53 FOREIGN KEY (player2_id) REFERENCES submissions(id),
54 FOREIGN KEY (winner_id) REFERENCES submissions(id)
55 );
56
57 CREATE INDEX IF NOT EXISTS idx_matches_player1 ON matches(player1_id);
58 CREATE INDEX IF NOT EXISTS idx_matches_player2 ON matches(player2_id);
59 CREATE INDEX IF NOT EXISTS idx_submissions_username ON submissions(username);
60 CREATE INDEX IF NOT EXISTS idx_submissions_status ON submissions(status);
61 CREATE INDEX IF NOT EXISTS idx_submissions_active ON submissions(is_active);
62 `
63
64 _, err = db.Exec(schema)
65 return db, err
66}
67
68func getLeaderboard(limit int) ([]LeaderboardEntry, error) {
69 query := `
70 SELECT
71 s.username,
72 COUNT(CASE WHEN m.winner_id = s.id THEN 1 END) as wins,
73 COUNT(CASE WHEN (m.player1_id = s.id OR m.player2_id = s.id) AND m.winner_id != s.id THEN 1 END) as losses,
74 AVG(CASE WHEN m.player1_id = s.id THEN m.player1_moves ELSE m.player2_moves END) as avg_moves,
75 MAX(m.timestamp) as last_played
76 FROM submissions s
77 LEFT JOIN matches m ON (m.player1_id = s.id OR m.player2_id = s.id)
78 WHERE s.is_active = 1
79 GROUP BY s.username
80 HAVING COUNT(m.id) > 0
81 ORDER BY wins DESC, losses ASC, avg_moves ASC
82 LIMIT ?
83 `
84
85 rows, err := globalDB.Query(query, limit)
86 if err != nil {
87 return nil, err
88 }
89 defer rows.Close()
90
91 var entries []LeaderboardEntry
92 for rows.Next() {
93 var e LeaderboardEntry
94 var lastPlayed string
95 err := rows.Scan(&e.Username, &e.Wins, &e.Losses, &e.AvgMoves, &lastPlayed)
96 if err != nil {
97 return nil, err
98 }
99
100 // Parse the timestamp string
101 e.LastPlayed, _ = time.Parse("2006-01-02 15:04:05", lastPlayed)
102
103 entries = append(entries, e)
104 }
105
106 return entries, rows.Err()
107}
108
109func addSubmission(username, filename string) (int64, error) {
110 // Mark old submission as inactive
111 _, err := globalDB.Exec(
112 "UPDATE submissions SET is_active = 0 WHERE username = ?",
113 username,
114 )
115 if err != nil {
116 return 0, err
117 }
118
119 // Insert new submission
120 result, err := globalDB.Exec(
121 "INSERT INTO submissions (username, filename, is_active) VALUES (?, ?, 1)",
122 username, filename,
123 )
124 if err != nil {
125 return 0, err
126 }
127 return result.LastInsertId()
128}
129
130func addMatch(player1ID, player2ID, winnerID, player1Moves, player2Moves int) error {
131 _, err := globalDB.Exec(
132 "INSERT INTO matches (player1_id, player2_id, winner_id, player1_moves, player2_moves) VALUES (?, ?, ?, ?, ?)",
133 player1ID, player2ID, winnerID, player1Moves, player2Moves,
134 )
135 return err
136}
137
138func updateSubmissionStatus(id int, status string) error {
139 _, err := globalDB.Exec("UPDATE submissions SET status = ? WHERE id = ?", status, id)
140 return err
141}
142
143func getPendingSubmissions() ([]Submission, error) {
144 rows, err := globalDB.Query(
145 "SELECT id, username, filename, upload_time, status FROM submissions WHERE status = 'pending' AND is_active = 1 ORDER BY upload_time",
146 )
147 if err != nil {
148 return nil, err
149 }
150 defer rows.Close()
151
152 var submissions []Submission
153 for rows.Next() {
154 var s Submission
155 err := rows.Scan(&s.ID, &s.Username, &s.Filename, &s.UploadTime, &s.Status)
156 if err != nil {
157 return nil, err
158 }
159 submissions = append(submissions, s)
160 }
161
162 return submissions, rows.Err()
163}
164
165func getActiveSubmissions() ([]Submission, error) {
166 rows, err := globalDB.Query(
167 "SELECT id, username, filename, upload_time, status FROM submissions WHERE is_active = 1 AND status = 'completed' ORDER BY username",
168 )
169 if err != nil {
170 return nil, err
171 }
172 defer rows.Close()
173
174 var submissions []Submission
175 for rows.Next() {
176 var s Submission
177 err := rows.Scan(&s.ID, &s.Username, &s.Filename, &s.UploadTime, &s.Status)
178 if err != nil {
179 return nil, err
180 }
181 submissions = append(submissions, s)
182 }
183
184 return submissions, rows.Err()
185}
186
187func getUserSubmissions(username string) ([]Submission, error) {
188 rows, err := globalDB.Query(
189 "SELECT id, username, filename, upload_time, status FROM submissions WHERE username = ? ORDER BY upload_time DESC LIMIT 10",
190 username,
191 )
192 if err != nil {
193 return nil, err
194 }
195 defer rows.Close()
196
197 var submissions []Submission
198 for rows.Next() {
199 var s Submission
200 err := rows.Scan(&s.ID, &s.Username, &s.Filename, &s.UploadTime, &s.Status)
201 if err != nil {
202 return nil, err
203 }
204 submissions = append(submissions, s)
205 }
206
207 return submissions, rows.Err()
208}
209
210
211type MatchResult struct {
212 Player1Username string
213 Player2Username string
214 WinnerUsername string
215 AvgMoves int
216}
217
218func getAllMatches() ([]MatchResult, error) {
219 query := `
220 SELECT
221 s1.username as player1,
222 s2.username as player2,
223 sw.username as winner,
224 m.player1_moves as avg_moves
225 FROM matches m
226 JOIN submissions s1 ON m.player1_id = s1.id
227 JOIN submissions s2 ON m.player2_id = s2.id
228 JOIN submissions sw ON m.winner_id = sw.id
229 WHERE s1.is_active = 1 AND s2.is_active = 1
230 ORDER BY m.timestamp DESC
231 `
232
233 rows, err := globalDB.Query(query)
234 if err != nil {
235 return nil, err
236 }
237 defer rows.Close()
238
239 var matches []MatchResult
240 for rows.Next() {
241 var m MatchResult
242 err := rows.Scan(&m.Player1Username, &m.Player2Username, &m.WinnerUsername, &m.AvgMoves)
243 if err != nil {
244 return nil, err
245 }
246 matches = append(matches, m)
247 }
248
249 return matches, rows.Err()
250}