Files
pages/internal/db/migration_runner.go
Anya Tokugawa d5d21b7841
Build and publish container images / images (push) Successful in 9m34s
Release container images / release (push) Successful in 36s
Refactor architecture into eight components and consolidate reliability work
Extract Grammaratur, Dregarnuhr, Geduldh, Mestionora, Ordoschnellic, Schutzaria, Kunstzeal and Seheweit behind compatible application contracts.

Include accumulated editor resilience, navigation and theme UX, remote CLI, storage/schema and DR groundwork, tests and design/operations documentation.

Verified: uncached full Go suite; go vet ./...; race tests for all eight components; 11 targeted Chromium tests; Node suites; 19 Python CLI tests; reproducible editor build. No push, release tag or deployment in this checkpoint.
2026-11-01 15:23:31 +09:00

189 lines
6.2 KiB
Go

package db
import (
"context"
"crypto/sha256"
"database/sql"
"database/sql/driver"
"encoding/hex"
"encoding/json"
"fmt"
"strings"
"time"
)
// SQLMigration is append-only once released. Edit a new version, never an
// applied statement. This runner is SQLite-specific; other DBs need their own
// locking/DDL implementations rather than a driver-name substitution.
type SQLMigration struct {
Version int
Name string
Statements []string
// Omit empty new fields to preserve checksums of previously released steps.
AddColumns []SQLiteColumn `json:",omitempty"`
// Each assertion must execute successfully and return no rows. The latest
// version defines the whole current contract; older assertions are superseded
// to permit deliberate column removal in a later, immutable migration.
Assertions []string `json:",omitempty"`
}
type SQLiteColumn struct {
Table string
Name string
Declaration string
}
func migrationChecksum(step SQLMigration) string {
encoded, _ := json.Marshal(step)
sum := sha256.Sum256(encoded)
return hex.EncodeToString(sum[:])
}
// ApplySQLiteMigrations serializes processes on a dedicated connection with
// BEGIN IMMEDIATE. DDL, data statements and ledger updates commit together.
// The legacy schema_migrations table remains untouched for old installations.
func ApplySQLiteMigrations(ctx context.Context, database *sql.DB, component string, steps []SQLMigration) error {
if component == "" {
return fmt.Errorf("migration component is required")
}
for i, step := range steps {
if step.Version != i+1 || step.Name == "" || (len(step.Statements) == 0 && len(step.AddColumns) == 0 && len(step.Assertions) == 0) {
return fmt.Errorf("invalid migration sequence for %s", component)
}
}
conn, err := database.Conn(ctx)
if err != nil {
return fmt.Errorf("migration %s acquire connection: %w", component, err)
}
defer conn.Close()
if _, err := conn.ExecContext(ctx, "BEGIN IMMEDIATE"); err != nil {
return fmt.Errorf("migration %s begin immediate: %w", component, err)
}
committed := false
defer func() {
if committed {
return
}
cleanup, cancel := context.WithTimeout(context.Background(), 5*time.Second)
defer cancel()
if _, err := conn.ExecContext(cleanup, "ROLLBACK"); err != nil {
// Never return a connection with uncertain transaction state to
// the pool. A failed/ambiguous COMMIT also takes this path.
_ = conn.Raw(func(any) error { return driver.ErrBadConn })
}
}()
if _, err := conn.ExecContext(ctx, `CREATE TABLE IF NOT EXISTS schema_migration_steps (
component TEXT NOT NULL, version INTEGER NOT NULL, name TEXT NOT NULL,
checksum TEXT NOT NULL, applied_at_ms INTEGER NOT NULL,
PRIMARY KEY(component, version))`); err != nil {
return err
}
rows, err := conn.QueryContext(ctx, `SELECT version,name,checksum FROM schema_migration_steps WHERE component=? ORDER BY version`, component)
if err != nil {
return err
}
applied := 0
for rows.Next() {
var version int
var name, checksum string
if err := rows.Scan(&version, &name, &checksum); err != nil {
rows.Close()
return err
}
if version != applied+1 || version > len(steps) || steps[version-1].Name != name || migrationChecksum(steps[version-1]) != checksum {
rows.Close()
return fmt.Errorf("migration history mismatch: component=%s version=%d (changed, missing or newer migration)", component, version)
}
applied++
}
if err := rows.Err(); err != nil {
rows.Close()
return err
}
if err := rows.Close(); err != nil {
return err
}
for _, step := range steps[applied:] {
for i, statement := range step.Statements {
if _, err := conn.ExecContext(ctx, statement); err != nil {
return fmt.Errorf("migration %s/%d %s statement %d: %w", component, step.Version, step.Name, i+1, err)
}
}
for _, column := range step.AddColumns {
if err := addSQLiteColumn(ctx, conn, column); err != nil {
return fmt.Errorf("migration %s/%d column %s.%s: %w", component, step.Version, column.Table, column.Name, err)
}
}
if _, err := conn.ExecContext(ctx, `INSERT INTO schema_migration_steps(component,version,name,checksum,applied_at_ms) VALUES(?,?,?,?,?)`, component, step.Version, step.Name, migrationChecksum(step), time.Now().UnixMilli()); err != nil {
return err
}
}
if len(steps) > 0 {
step := steps[len(steps)-1]
for i, assertion := range step.Assertions {
rows, err := conn.QueryContext(ctx, assertion)
if err != nil {
return fmt.Errorf("schema contract %s/%d assertion %d: %w", component, step.Version, i+1, err)
}
violated := rows.Next()
err = rows.Err()
closeErr := rows.Close()
if err != nil {
return err
}
if closeErr != nil {
return closeErr
}
if violated {
return fmt.Errorf("schema contract %s/%d assertion %d failed", component, step.Version, i+1)
}
}
}
_, err = conn.ExecContext(ctx, "COMMIT")
committed = err == nil
return err
}
func addSQLiteColumn(ctx context.Context, conn *sql.Conn, column SQLiteColumn) error {
// Identifiers are trusted migration definitions, but validate before SQL
// interpolation so accidental punctuation cannot change the statement.
for _, identifier := range []string{column.Table, column.Name} {
if identifier == "" || strings.IndexFunc(identifier, func(r rune) bool {
return !(r >= 'a' && r <= 'z' || r >= 'A' && r <= 'Z' || r >= '0' && r <= '9' || r == '_')
}) >= 0 {
return fmt.Errorf("invalid schema identifier")
}
}
if strings.TrimSpace(column.Declaration) == "" {
return fmt.Errorf("column declaration is required")
}
rows, err := conn.QueryContext(ctx, `SELECT name FROM pragma_table_info(?)`, column.Table)
if err != nil {
return err
}
found, tableExists := false, false
for rows.Next() {
var name string
if err := rows.Scan(&name); err != nil {
rows.Close()
return err
}
found, tableExists = found || name == column.Name, true
}
if err := rows.Err(); err != nil {
rows.Close()
return err
}
if err := rows.Close(); err != nil {
return err
}
if !tableExists {
return fmt.Errorf("table %s is missing", column.Table)
}
if found {
return nil
}
_, err = conn.ExecContext(ctx, fmt.Sprintf("ALTER TABLE %q ADD COLUMN %q %s", column.Table, column.Name, column.Declaration))
return err
}