arimelody.space
home to your local SPACEGIRL 馃挮
arimelody.space
1package controller
2
3import (
4 "embed"
5 "fmt"
6 "os"
7 "time"
8
9 "github.com/jmoiron/sqlx"
10)
11
12const DB_VERSION int = 4
13
14func CheckDBVersionAndMigrate(db *sqlx.DB) {
15 db.MustExec("CREATE SCHEMA IF NOT EXISTS arimelody")
16 db.MustExec("SET search_path TO arimelody, public")
17 db.MustExec(
18 "CREATE TABLE IF NOT EXISTS arimelody.schema_version (" +
19 "version INTEGER PRIMARY KEY, " +
20 "applied_at TIMESTAMP DEFAULT current_timestamp)",
21 )
22
23 oldDBVersion := 0
24 schemaVersionCount := 0
25 err := db.Get(&schemaVersionCount, "SELECT COUNT(*) FROM schema_version")
26 if err != nil { panic(err) }
27 if schemaVersionCount > 0 {
28 err := db.Get(&oldDBVersion, "SELECT MAX(version) FROM schema_version")
29 if err != nil { panic(err) }
30 }
31
32 for oldDBVersion < DB_VERSION {
33 switch oldDBVersion {
34 case 0:
35 // default case; assume no database exists
36 ApplyMigration(db, "000-init")
37 oldDBVersion = DB_VERSION
38
39 case 1:
40 // the irony is i actually have to awkwardly shove schema_version
41 // into the old database in order for this to work LOL
42 ApplyMigration(db, "001-pre-versioning")
43 oldDBVersion = 2
44
45 case 2:
46 ApplyMigration(db, "002-audit-logs")
47 oldDBVersion = 3
48
49 case 3:
50 ApplyMigration(db, "003-fail-lock")
51 oldDBVersion = 4
52
53 }
54 }
55
56 fmt.Printf("Database schema up to date.\n")
57}
58
59//go:embed "schema-migration"
60var schemaFS embed.FS
61
62func ApplyMigration(db *sqlx.DB, scriptFile string) {
63 fmt.Printf("Applying schema migration %s...\n", scriptFile)
64
65 bytes, err := schemaFS.ReadFile("schema-migration/" + scriptFile + ".sql")
66 if err != nil {
67 fmt.Fprintf(os.Stderr, "FATAL: Failed to open schema file \"%s\": %v\n", scriptFile, err)
68 os.Exit(1)
69 }
70 script := string(bytes)
71
72 tx, err := db.Begin()
73 if err != nil {
74 fmt.Fprintf(os.Stderr, "FATAL: Failed to begin migration: %v\n", err)
75 os.Exit(1)
76 }
77
78 _, err = tx.Exec(script)
79 if err != nil {
80 tx.Rollback()
81 fmt.Fprintf(os.Stderr, "FATAL: Failed to apply migration: %v\n", err)
82 os.Exit(1)
83 }
84
85 _, err = tx.Exec(
86 "INSERT INTO schema_version (version, applied_at) " +
87 "VALUES ($1, $2)",
88 DB_VERSION,
89 time.Now(),
90 )
91 if err != nil {
92 tx.Rollback()
93 fmt.Fprintf(os.Stderr, "FATAL: Failed to update schema version: %v\n", err)
94 os.Exit(1)
95 }
96
97 err = tx.Commit()
98 if err != nil {
99 fmt.Fprintf(os.Stderr, "FATAL: Failed to commit transaction: %v\n", err)
100 os.Exit(1)
101 }
102}