Files
pages/internal/db/postgres_migration.go
2026-11-15 05:47:14 +09:00

121 lines
4.3 KiB
Go

package db
import (
"context"
"crypto/sha256"
"database/sql"
"encoding/hex"
"encoding/json"
"fmt"
"strings"
"time"
)
// PostgreSQLMigration contains native PostgreSQL SQL ($1 parameters, BIGINT
// timestamps). SQLite migration definitions must not be translated implicitly.
// Definitions are append-only after release. Assertions return no rows on success.
type PostgreSQLMigration struct {
Version int
Name string
Statements []string
Assertions []string `json:",omitempty"`
}
func postgresMigrationChecksum(step PostgreSQLMigration) string {
encoded, _ := json.Marshal(step)
sum := sha256.Sum256(encoded)
return hex.EncodeToString(sum[:])
}
// ApplyPostgreSQLMigrations uses one transaction-scoped advisory lock for the
// migration namespace, including ledger creation. Component-specific locks
// would race concurrent first creation of the shared ledger. READ COMMITTED
// ensures a waiter sees the previous owner's committed migration history.
// Callers must use the same search_path and lock convention for a database.
func ApplyPostgreSQLMigrations(ctx context.Context, database *sql.DB, component string, steps []PostgreSQLMigration) error {
if strings.TrimSpace(component) == "" {
return fmt.Errorf("migration component is required")
}
for i, step := range steps {
if step.Version != i+1 || strings.TrimSpace(step.Name) == "" || len(step.Statements)+len(step.Assertions) == 0 {
return fmt.Errorf("invalid PostgreSQL migration sequence for %s", component)
}
}
tx, err := database.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelReadCommitted})
if err != nil {
return postgresError("migration begin", err)
}
defer tx.Rollback()
// Stable application-specific key, independent of Go hashes and versions.
if _, err := tx.ExecContext(ctx, `SELECT pg_advisory_xact_lock(1885431653, 1835624306)`); err != nil {
return postgresError("migration lock", err)
}
if _, err := tx.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 BIGINT NOT NULL,
PRIMARY KEY(component,version))`); err != nil {
return postgresError("migration ledger", err)
}
rows, err := tx.QueryContext(ctx, `SELECT version,name,checksum FROM schema_migration_steps WHERE component=$1 ORDER BY version`, component)
if err != nil {
return postgresError("migration history", 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 postgresError("migration history", err)
}
if version != applied+1 || version > len(steps) || steps[version-1].Name != name || postgresMigrationChecksum(steps[version-1]) != checksum {
rows.Close()
return fmt.Errorf("PostgreSQL migration history mismatch: component=%s version=%d", component, version)
}
applied++
}
err = rows.Err()
closeErr := rows.Close()
if err != nil {
return postgresError("migration history", err)
}
if closeErr != nil {
return postgresError("migration history close", closeErr)
}
for _, step := range steps[applied:] {
for i, statement := range step.Statements {
if _, err := tx.ExecContext(ctx, statement); err != nil {
return postgresError(fmt.Sprintf("migration %s/%d statement %d", component, step.Version, i+1), err)
}
}
if _, err := tx.ExecContext(ctx, `INSERT INTO schema_migration_steps(component,version,name,checksum,applied_at_ms) VALUES($1,$2,$3,$4,$5)`, component, step.Version, step.Name, postgresMigrationChecksum(step), time.Now().UnixMilli()); err != nil {
return postgresError("migration record", err)
}
}
if len(steps) > 0 {
step := steps[len(steps)-1]
for i, assertion := range step.Assertions {
rows, err := tx.QueryContext(ctx, assertion)
if err != nil {
return postgresError("schema assertion", err)
}
violated := rows.Next()
err = rows.Err()
closeErr := rows.Close()
if err != nil {
return postgresError("schema assertion", err)
}
if closeErr != nil {
return postgresError("schema assertion close", closeErr)
}
if violated {
return fmt.Errorf("PostgreSQL schema contract %s/%d assertion %d failed", component, step.Version, i+1)
}
}
}
if err := tx.Commit(); err != nil {
return postgresError("migration commit", err)
}
return nil
}