1package store23import (4 "database/sql"5 "fmt"6)78const currentSchemaVersion = 5910type migration struct {11 version int12 name string13 apply func(*sql.DB) error14}1516var schemaMigrations = []migration{17 {version: 1, name: "create games table", apply: createGamesSchema},18 {version: 2, name: "add game metadata columns", apply: migrateGameMetadata},19 {version: 3, name: "refresh stats views", apply: refreshStatsViews},20 {version: 4, name: "add difficulty metadata", apply: migrateDifficultyMetadata},21 {version: 5, name: "add variant metadata", apply: migrateVariantMetadata},22}2324type gameRowMeta struct {25 ID int6426 Name string27 GameType string28 Mode string29 GameID string30 ModeID string31 RunKind string32}3334func runMigrations(db *sql.DB) error {35 if err := ensureSchemaMigrationsTable(db); err != nil {36 return err37 }3839 version, err := schemaVersion(db)40 if err != nil {41 return err42 }43 if version == 0 {44 version, err = detectSchemaVersion(db)45 if err != nil {46 return err47 }48 if err := setSchemaVersion(db, version); err != nil {49 return err50 }51 }5253 for _, m := range schemaMigrations {54 if m.version <= version {55 continue56 }57 if err := m.apply(db); err != nil {58 return fmt.Errorf("applying migration %d (%s): %w", m.version, m.name, err)59 }60 if err := setSchemaVersion(db, m.version); err != nil {61 return err62 }63 }6465 return nil66}6768func ensureSchemaMigrationsTable(db *sql.DB) error {69 _, err := db.Exec(`70CREATE TABLE IF NOT EXISTS schema_migrations (71 id INTEGER PRIMARY KEY CHECK(id = 1),72 version INTEGER NOT NULL,73 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP74);`)75 if err != nil {76 return fmt.Errorf("creating schema_migrations table: %w", err)77 }78 return nil79}8081func schemaVersion(db *sql.DB) (int, error) {82 var version int83 err := db.QueryRow(`SELECT version FROM schema_migrations WHERE id = 1`).Scan(&version)84 if err == sql.ErrNoRows {85 return 0, nil86 }87 if err != nil {88 return 0, fmt.Errorf("reading schema version: %w", err)89 }90 return version, nil91}9293func setSchemaVersion(db *sql.DB, version int) error {94 _, err := db.Exec(`95INSERT INTO schema_migrations (id, version)96VALUES (1, ?)97ON CONFLICT(id) DO UPDATE98SET version = excluded.version,99 updated_at = CURRENT_TIMESTAMP100`, version)101 if err != nil {102 return fmt.Errorf("writing schema version %d: %w", version, err)103 }104 return nil105}106107func detectSchemaVersion(db *sql.DB) (int, error) {108 exists, err := tableExists(db, "games")109 if err != nil {110 return 0, err111 }112 if !exists {113 return 0, nil114 }115116 columns, err := tableColumns(db, "games")117 if err != nil {118 return 0, err119 }120121 required := []string{122 "game_id",123 "mode_id",124 "run_kind",125 "run_date",126 "week_year",127 "week_number",128 "week_index",129 "seed_text",130 }131 for _, column := range required {132 if !columns[column] {133 return 1, nil134 }135 }136 for _, column := range []string{"target_difficulty_elo", "actual_difficulty_elo", "difficulty_confidence"} {137 if !columns[column] {138 return 2, nil139 }140 }141 for _, column := range []string{"variant_id", "variant"} {142 if !columns[column] {143 return 4, nil144 }145 }146147 return 5, nil148}149150func createGamesSchema(db *sql.DB) error {151 if _, err := db.Exec(createTableSQL); err != nil {152 return fmt.Errorf("creating games table: %w", err)153 }154 return nil155}156157func migrateGameMetadata(db *sql.DB) error {158 if err := ensureGameColumns(db); err != nil {159 return err160 }161 if err := backfillGameMetadata(db); err != nil {162 return err163 }164 return nil165}166167func refreshStatsViews(db *sql.DB) error {168 if _, err := db.Exec(`DROP VIEW IF EXISTS category_stats`); err != nil {169 return fmt.Errorf("dropping category_stats view: %w", err)170 }171 if _, err := db.Exec(`DROP VIEW IF EXISTS mode_stats`); err != nil {172 return fmt.Errorf("dropping mode_stats view: %w", err)173 }174 if _, err := db.Exec(createCategoryStatsViewSQL); err != nil {175 return fmt.Errorf("creating category_stats view: %w", err)176 }177 if _, err := db.Exec(createModeStatsViewSQL); err != nil {178 return fmt.Errorf("creating mode_stats view: %w", err)179 }180 return nil181}182183func migrateDifficultyMetadata(db *sql.DB) error {184 if err := ensureDifficultyColumns(db); err != nil {185 return err186 }187 return refreshStatsViews(db)188}189190func migrateVariantMetadata(db *sql.DB) error {191 if err := ensureVariantColumns(db); err != nil {192 return err193 }194 if err := backfillVariantMetadata(db); err != nil {195 return err196 }197 return refreshStatsViews(db)198}199200func ensureGameColumns(db *sql.DB) error {201 columns := []struct {202 name string203 definition string204 }{205 {name: "game_id", definition: "TEXT NOT NULL DEFAULT ''"},206 {name: "variant_id", definition: "TEXT NOT NULL DEFAULT ''"},207 {name: "variant", definition: "TEXT NOT NULL DEFAULT ''"},208 {name: "mode_id", definition: "TEXT NOT NULL DEFAULT ''"},209 {name: "run_kind", definition: "TEXT NOT NULL DEFAULT 'normal'"},210 {name: "run_date", definition: "DATE"},211 {name: "week_year", definition: "INTEGER"},212 {name: "week_number", definition: "INTEGER"},213 {name: "week_index", definition: "INTEGER"},214 {name: "seed_text", definition: "TEXT"},215 }216217 existing, err := tableColumns(db, "games")218 if err != nil {219 return err220 }221 for _, column := range columns {222 if existing[column.name] {223 continue224 }225 if _, err := db.Exec(fmt.Sprintf("ALTER TABLE games ADD COLUMN %s %s", column.name, column.definition)); err != nil {226 return fmt.Errorf("adding games.%s: %w", column.name, err)227 }228 }229 return nil230}231232func ensureVariantColumns(db *sql.DB) error {233 columns := []struct {234 name string235 definition string236 }{237 {name: "variant_id", definition: "TEXT NOT NULL DEFAULT ''"},238 {name: "variant", definition: "TEXT NOT NULL DEFAULT ''"},239 }240241 existing, err := tableColumns(db, "games")242 if err != nil {243 return err244 }245 for _, column := range columns {246 if existing[column.name] {247 continue248 }249 if _, err := db.Exec(fmt.Sprintf("ALTER TABLE games ADD COLUMN %s %s", column.name, column.definition)); err != nil {250 return fmt.Errorf("adding games.%s: %w", column.name, err)251 }252 }253 return nil254}255256func ensureDifficultyColumns(db *sql.DB) error {257 columns := []struct {258 name string259 definition string260 }{261 {name: "target_difficulty_elo", definition: "INTEGER"},262 {name: "actual_difficulty_elo", definition: "INTEGER"},263 {name: "difficulty_confidence", definition: "TEXT"},264 }265266 existing, err := tableColumns(db, "games")267 if err != nil {268 return err269 }270 for _, column := range columns {271 if existing[column.name] {272 continue273 }274 if _, err := db.Exec(fmt.Sprintf("ALTER TABLE games ADD COLUMN %s %s", column.name, column.definition)); err != nil {275 return fmt.Errorf("adding games.%s: %w", column.name, err)276 }277 }278 return nil279}280281func backfillVariantMetadata(db *sql.DB) error {282 _, err := db.Exec(`283UPDATE games284 SET variant = CASE WHEN variant = '' THEN mode ELSE variant END,285 variant_id = CASE286 WHEN variant_id != '' THEN variant_id287 WHEN mode_id != '' THEN mode_id288 ELSE lower(trim(replace(replace(mode, '-', ' '), '_', ' ')))289 END290 WHERE variant = ''291 OR variant_id = ''`)292 if err != nil {293 return fmt.Errorf("backfilling variant metadata: %w", err)294 }295 return nil296}297298func tableColumns(db *sql.DB, table string) (map[string]bool, error) {299 rows, err := db.Query(fmt.Sprintf("PRAGMA table_info(%s)", table))300 if err != nil {301 return nil, fmt.Errorf("querying %s columns: %w", table, err)302 }303 defer rows.Close()304305 columns := map[string]bool{}306 for rows.Next() {307 var (308 cid int309 name string310 typ string311 notNull int312 defaultV any313 primaryKey int314 )315 if err := rows.Scan(&cid, &name, &typ, ¬Null, &defaultV, &primaryKey); err != nil {316 return nil, fmt.Errorf("scanning %s column: %w", table, err)317 }318 columns[name] = true319 }320 return columns, rows.Err()321}322323func tableExists(db *sql.DB, table string) (bool, error) {324 var name string325 err := db.QueryRow(`SELECT name FROM sqlite_master WHERE type = 'table' AND name = ?`, table).Scan(&name)326 if err == sql.ErrNoRows {327 return false, nil328 }329 if err != nil {330 return false, fmt.Errorf("checking %s existence: %w", table, err)331 }332 return true, nil333}334335func backfillGameMetadata(db *sql.DB) error {336 rows, err := db.Query(`SELECT id, name, game_type, mode, game_id, mode_id, run_kind FROM games`)337 if err != nil {338 return fmt.Errorf("querying game metadata for backfill: %w", err)339 }340 defer rows.Close()341342 var metas []gameRowMeta343 for rows.Next() {344 var meta gameRowMeta345 if err := rows.Scan(&meta.ID, &meta.Name, &meta.GameType, &meta.Mode, &meta.GameID, &meta.ModeID, &meta.RunKind); err != nil {346 return fmt.Errorf("scanning game metadata for backfill: %w", err)347 }348 metas = append(metas, meta)349 }350 if err := rows.Err(); err != nil {351 return fmt.Errorf("reading game metadata for backfill: %w", err)352 }353354 for _, meta := range metas {355 gameID := meta.GameID356 if gameID == "" {357 gameID = CanonicalGameID(meta.GameType)358 }359 modeID := meta.ModeID360 if modeID == "" {361 modeID = CanonicalModeID(meta.Mode)362 }363 runKind := RunKind(meta.RunKind)364 if runKind == "" || runKind == RunKindNormal {365 runKind = RunKindForName(meta.Name)366 }367 runDate := RunDateForName(meta.Name)368 seedText := SeedTextForName(meta.Name)369 year, week, index, ok := WeeklyIdentityForName(meta.Name)370 if _, err := db.Exec(371 `UPDATE games372 SET game_id = ?, mode_id = ?, run_kind = ?, run_date = ?,373 week_year = ?, week_number = ?, week_index = ?, seed_text = ?374 WHERE id = ?`,375 gameID, modeID, string(runKind), runDate,376 nullableIntIf(ok, year), nullableIntIf(ok, week), nullableIntIf(ok, index),377 nullableString(seedText), meta.ID,378 ); err != nil {379 return fmt.Errorf("backfilling game metadata for %d: %w", meta.ID, err)380 }381 }382 return nil383}384385func nullableInt(value int) any {386 if value == 0 {387 return nil388 }389 return value390}391392func nullableIntPtr(value *int) any {393 if value == nil {394 return nil395 }396 return *value397}398399func nullableIntIf(ok bool, value int) any {400 if !ok || value == 0 {401 return nil402 }403 return value404}405406func nullableString(value string) any {407 if value == "" {408 return nil409 }410 return value411}