1package store23import (4 "database/sql"5 "fmt"6)78const currentSchemaVersion = 3910type 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}2122type gameRowMeta struct {23 ID int6424 Name string25 GameType string26 Mode string27 GameID string28 ModeID string29 RunKind string30}3132func runMigrations(db *sql.DB) error {33 if err := ensureSchemaMigrationsTable(db); err != nil {34 return err35 }3637 version, err := schemaVersion(db)38 if err != nil {39 return err40 }41 if version == 0 {42 version, err = detectSchemaVersion(db)43 if err != nil {44 return err45 }46 if err := setSchemaVersion(db, version); err != nil {47 return err48 }49 }5051 for _, m := range schemaMigrations {52 if m.version <= version {53 continue54 }55 if err := m.apply(db); err != nil {56 return fmt.Errorf("applying migration %d (%s): %w", m.version, m.name, err)57 }58 if err := setSchemaVersion(db, m.version); err != nil {59 return err60 }61 }6263 return nil64}6566func ensureSchemaMigrationsTable(db *sql.DB) error {67 _, err := db.Exec(`68CREATE TABLE IF NOT EXISTS schema_migrations (69 id INTEGER PRIMARY KEY CHECK(id = 1),70 version INTEGER NOT NULL,71 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP72);`)73 if err != nil {74 return fmt.Errorf("creating schema_migrations table: %w", err)75 }76 return nil77}7879func schemaVersion(db *sql.DB) (int, error) {80 var version int81 err := db.QueryRow(`SELECT version FROM schema_migrations WHERE id = 1`).Scan(&version)82 if err == sql.ErrNoRows {83 return 0, nil84 }85 if err != nil {86 return 0, fmt.Errorf("reading schema version: %w", err)87 }88 return version, nil89}9091func setSchemaVersion(db *sql.DB, version int) error {92 _, err := db.Exec(`93INSERT INTO schema_migrations (id, version)94VALUES (1, ?)95ON CONFLICT(id) DO UPDATE96SET version = excluded.version,97 updated_at = CURRENT_TIMESTAMP98`, version)99 if err != nil {100 return fmt.Errorf("writing schema version %d: %w", version, err)101 }102 return nil103}104105func detectSchemaVersion(db *sql.DB) (int, error) {106 exists, err := tableExists(db, "games")107 if err != nil {108 return 0, err109 }110 if !exists {111 return 0, nil112 }113114 columns, err := tableColumns(db, "games")115 if err != nil {116 return 0, err117 }118119 required := []string{120 "game_id",121 "mode_id",122 "run_kind",123 "run_date",124 "week_year",125 "week_number",126 "week_index",127 "seed_text",128 }129 for _, column := range required {130 if !columns[column] {131 return 1, nil132 }133 }134135 return 2, nil136}137138func createGamesSchema(db *sql.DB) error {139 if _, err := db.Exec(createTableSQL); err != nil {140 return fmt.Errorf("creating games table: %w", err)141 }142 return nil143}144145func migrateGameMetadata(db *sql.DB) error {146 if err := ensureGameColumns(db); err != nil {147 return err148 }149 if err := backfillGameMetadata(db); err != nil {150 return err151 }152 return nil153}154155func refreshStatsViews(db *sql.DB) error {156 if _, err := db.Exec(`DROP VIEW IF EXISTS category_stats`); err != nil {157 return fmt.Errorf("dropping category_stats view: %w", err)158 }159 if _, err := db.Exec(`DROP VIEW IF EXISTS mode_stats`); err != nil {160 return fmt.Errorf("dropping mode_stats view: %w", err)161 }162 if _, err := db.Exec(createCategoryStatsViewSQL); err != nil {163 return fmt.Errorf("creating category_stats view: %w", err)164 }165 if _, err := db.Exec(createModeStatsViewSQL); err != nil {166 return fmt.Errorf("creating mode_stats view: %w", err)167 }168 return nil169}170171func ensureGameColumns(db *sql.DB) error {172 columns := []struct {173 name string174 definition string175 }{176 {name: "game_id", definition: "TEXT NOT NULL DEFAULT ''"},177 {name: "mode_id", definition: "TEXT NOT NULL DEFAULT ''"},178 {name: "run_kind", definition: "TEXT NOT NULL DEFAULT 'normal'"},179 {name: "run_date", definition: "DATE"},180 {name: "week_year", definition: "INTEGER"},181 {name: "week_number", definition: "INTEGER"},182 {name: "week_index", definition: "INTEGER"},183 {name: "seed_text", definition: "TEXT"},184 }185186 existing, err := tableColumns(db, "games")187 if err != nil {188 return err189 }190 for _, column := range columns {191 if existing[column.name] {192 continue193 }194 if _, err := db.Exec(fmt.Sprintf("ALTER TABLE games ADD COLUMN %s %s", column.name, column.definition)); err != nil {195 return fmt.Errorf("adding games.%s: %w", column.name, err)196 }197 }198 return nil199}200201func tableColumns(db *sql.DB, table string) (map[string]bool, error) {202 rows, err := db.Query(fmt.Sprintf("PRAGMA table_info(%s)", table))203 if err != nil {204 return nil, fmt.Errorf("querying %s columns: %w", table, err)205 }206 defer rows.Close()207208 columns := map[string]bool{}209 for rows.Next() {210 var (211 cid int212 name string213 typ string214 notNull int215 defaultV any216 primaryKey int217 )218 if err := rows.Scan(&cid, &name, &typ, ¬Null, &defaultV, &primaryKey); err != nil {219 return nil, fmt.Errorf("scanning %s column: %w", table, err)220 }221 columns[name] = true222 }223 return columns, rows.Err()224}225226func tableExists(db *sql.DB, table string) (bool, error) {227 var name string228 err := db.QueryRow(`SELECT name FROM sqlite_master WHERE type = 'table' AND name = ?`, table).Scan(&name)229 if err == sql.ErrNoRows {230 return false, nil231 }232 if err != nil {233 return false, fmt.Errorf("checking %s existence: %w", table, err)234 }235 return true, nil236}237238func backfillGameMetadata(db *sql.DB) error {239 rows, err := db.Query(`SELECT id, name, game_type, mode, game_id, mode_id, run_kind FROM games`)240 if err != nil {241 return fmt.Errorf("querying game metadata for backfill: %w", err)242 }243 defer rows.Close()244245 var metas []gameRowMeta246 for rows.Next() {247 var meta gameRowMeta248 if err := rows.Scan(&meta.ID, &meta.Name, &meta.GameType, &meta.Mode, &meta.GameID, &meta.ModeID, &meta.RunKind); err != nil {249 return fmt.Errorf("scanning game metadata for backfill: %w", err)250 }251 metas = append(metas, meta)252 }253 if err := rows.Err(); err != nil {254 return fmt.Errorf("reading game metadata for backfill: %w", err)255 }256257 for _, meta := range metas {258 gameID := meta.GameID259 if gameID == "" {260 gameID = CanonicalGameID(meta.GameType)261 }262 modeID := meta.ModeID263 if modeID == "" {264 modeID = CanonicalModeID(meta.Mode)265 }266 runKind := RunKind(meta.RunKind)267 if runKind == "" || runKind == RunKindNormal {268 runKind = RunKindForName(meta.Name)269 }270 runDate := RunDateForName(meta.Name)271 seedText := SeedTextForName(meta.Name)272 year, week, index, ok := WeeklyIdentityForName(meta.Name)273 if _, err := db.Exec(274 `UPDATE games275 SET game_id = ?, mode_id = ?, run_kind = ?, run_date = ?,276 week_year = ?, week_number = ?, week_index = ?, seed_text = ?277 WHERE id = ?`,278 gameID, modeID, string(runKind), runDate,279 nullableIntIf(ok, year), nullableIntIf(ok, week), nullableIntIf(ok, index),280 nullableString(seedText), meta.ID,281 ); err != nil {282 return fmt.Errorf("backfilling game metadata for %d: %w", meta.ID, err)283 }284 }285 return nil286}287288func nullableInt(value int) any {289 if value == 0 {290 return nil291 }292 return value293}294295func nullableIntIf(ok bool, value int) any {296 if !ok || value == 0 {297 return nil298 }299 return value300}301302func nullableString(value string) any {303 if value == "" {304 return nil305 }306 return value307}