1// SPDX-FileCopyrightText: Chris Waldon <christopher.waldon.dev@gmail.com>
2//
3// SPDX-License-Identifier: Apache-2.0
4
5package db
6
7import (
8 "context"
9 "database/sql"
10 _ "embed"
11 "fmt"
12)
13
14type migration struct {
15 upQuery string
16 downQuery string
17 postHook func(*sql.Tx) error
18}
19
20var (
21 //go:embed sql/1_add_project_ids.up.sql
22 migration1Up string
23 //go:embed sql/1_add_project_ids.down.sql
24 migration1Down string
25 //go:embed sql/2_swap_project_url_for_id.up.sql
26 migration2Up string
27 //go:embed sql/2_swap_project_url_for_id.down.sql
28 migration2Down string
29)
30
31var migrations = [...]migration{
32 0: {
33 upQuery: `CREATE TABLE schema_migrations (version uint64, dirty bool);
34 INSERT INTO schema_migrations (version, dirty) VALUES (0, 0);`,
35 downQuery: `DROP TABLE schema_migrations;`,
36 },
37 1: {
38 upQuery: migration1Up,
39 downQuery: migration1Down,
40 postHook: generateAndInsertProjectIDs,
41 },
42 2: {
43 upQuery: migration2Up,
44 downQuery: migration2Down,
45 },
46 3: {
47 postHook: correctProjectIDs,
48 },
49}
50
51// Migrate runs all pending migrations
52func Migrate(db *sql.DB) error {
53 version := getSchemaVersion(db)
54 for nextMigration := version + 1; nextMigration < len(migrations); nextMigration++ {
55 if err := runMigration(db, nextMigration); err != nil {
56 return fmt.Errorf("migrations failed: %w", err)
57 }
58 if version := getSchemaVersion(db); version != nextMigration {
59 return fmt.Errorf("migration did not update version (expected %d, got %d)", nextMigration, version)
60 }
61 }
62 return nil
63}
64
65// runMigration runs a single migration inside a transaction, updates the schema
66// version and commits the transaction if successful, and rolls back the
67// transaction if unsuccessful.
68func runMigration(db *sql.DB, migrationIdx int) (err error) {
69 current := migrations[migrationIdx]
70 tx, err := db.BeginTx(context.Background(), &sql.TxOptions{})
71 if err != nil {
72 return fmt.Errorf("failed opening transaction for migration %d: %w", migrationIdx, err)
73 }
74 defer func() {
75 if err == nil {
76 err = tx.Commit()
77 }
78 if err != nil {
79 if rbErr := tx.Rollback(); rbErr != nil {
80 err = fmt.Errorf("failed rolling back: %w due to: %w", rbErr, err)
81 }
82 }
83 }()
84 if len(current.upQuery) > 0 {
85 if _, err := tx.Exec(current.upQuery); err != nil {
86 return fmt.Errorf("failed running migration %d: %w", migrationIdx, err)
87 }
88 }
89 if current.postHook != nil {
90 if err := current.postHook(tx); err != nil {
91 return fmt.Errorf("failed running posthook for migration %d: %w", migrationIdx, err)
92 }
93 }
94 return updateSchemaVersion(tx, migrationIdx)
95}
96
97// undoMigration rolls the single most recent migration back inside a
98// transaction, updates the schema version and commits the transaction if
99// successful, and rolls back the transaction if unsuccessful.
100//
101//lint:ignore U1000 Will be used when #34 is implemented (https://todo.sr.ht/~amolith/willow/34)
102func undoMigration(db *sql.DB, migrationIdx int) (err error) {
103 current := migrations[migrationIdx]
104 tx, err := db.BeginTx(context.Background(), &sql.TxOptions{})
105 if err != nil {
106 return fmt.Errorf("failed opening undo transaction for migration %d: %w", migrationIdx, err)
107 }
108 defer func() {
109 if err == nil {
110 err = tx.Commit()
111 }
112 if err != nil {
113 if rbErr := tx.Rollback(); rbErr != nil {
114 err = fmt.Errorf("failed rolling back: %w due to: %w", rbErr, err)
115 }
116 }
117 }()
118 if len(current.downQuery) > 0 {
119 if _, err := tx.Exec(current.downQuery); err != nil {
120 return fmt.Errorf("failed undoing migration %d: %w", migrationIdx, err)
121 }
122 }
123 return updateSchemaVersion(tx, migrationIdx-1)
124}
125
126// getSchemaVersion returns the schema version from the database
127func getSchemaVersion(db *sql.DB) int {
128 row := db.QueryRowContext(context.Background(), `SELECT version FROM schema_migrations LIMIT 1;`)
129 var version int
130 if err := row.Scan(&version); err != nil {
131 version = -1
132 }
133 return version
134}
135
136// updateSchemaVersion sets the version to the provided int
137func updateSchemaVersion(tx *sql.Tx, version int) error {
138 if version < 0 {
139 // Do not try to use the schema_migrations table in a schema version where it doesn't exist
140 return nil
141 }
142 _, err := tx.Exec(`UPDATE schema_migrations SET version = @version;`, sql.Named("version", version))
143 return err
144}