121 lines
4.3 KiB
Go
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
|
|
}
|