Files
QuoteForge/internal/models/sql_migration_safety_integration_test.go

228 lines
8.7 KiB
Go

//go:build integration
package models
import (
"context"
"fmt"
"os"
"os/exec"
"path/filepath"
"strings"
"testing"
"testing/fstest"
"time"
"git.mchus.pro/mchus/quoteforge/internal/mariadb"
"gorm.io/driver/mysql"
"gorm.io/gorm"
"gorm.io/gorm/logger"
)
func TestSQLMigrationSafetyMariaDB(t *testing.T) {
out, err := exec.Command("docker", "run", "--detach", "--rm", "--pull=never", "-p", "127.0.0.1::3306", "-e", "MARIADB_ALLOW_EMPTY_ROOT_PASSWORD=1", "-e", "MARIADB_ROOT_HOST=%", "mariadb:11.8", "--skip-ssl").CombinedOutput()
if err != nil {
t.Fatalf("start test MariaDB: %v: %s", err, out)
}
id := strings.TrimSpace(string(out))
t.Cleanup(func() {
if out, err := exec.Command("docker", "stop", "-t", "2", id).CombinedOutput(); err != nil {
t.Errorf("stop test MariaDB: %v: %s", err, out)
}
})
out, err = exec.Command("docker", "port", id, "3306/tcp").CombinedOutput()
if err != nil {
t.Fatalf("get test MariaDB port: %v: %s", err, out)
}
addr := strings.TrimSpace(string(out))
dsn := "root@tcp(" + addr + ")/?parseTime=true&timeout=1s&tls=false"
var admin *gorm.DB
deadline := time.Now().Add(45 * time.Second)
for {
admin, err = gorm.Open(mysql.Open(dsn), &gorm.Config{Logger: logger.Default.LogMode(logger.Silent)})
if err == nil {
break
}
if time.Now().After(deadline) {
t.Fatalf("test MariaDB unavailable: %v", err)
}
time.Sleep(150 * time.Millisecond)
}
pool, err := admin.DB()
if err != nil {
t.Fatal(err)
}
t.Cleanup(func() { pool.Close() })
newDB := func(t *testing.T) *gorm.DB {
t.Helper()
schema := fmt.Sprintf("qfs_test_%d", time.Now().UnixNano())
if err := admin.Exec("CREATE DATABASE " + schema + " COLLATE utf8mb4_unicode_ci").Error; err != nil {
t.Fatal(err)
}
db, err := gorm.Open(mysql.Open(strings.Replace(dsn, ")/?", ")/"+schema+"?", 1)), &gorm.Config{Logger: logger.Default.LogMode(logger.Silent)})
if err != nil {
t.Fatal(err)
}
p, err := db.DB()
if err != nil {
t.Fatal(err)
}
p.SetMaxOpenConns(1)
p.SetMaxIdleConns(1)
t.Cleanup(func() {
p.Close()
if err := admin.Exec("DROP DATABASE " + schema).Error; err != nil {
t.Error(err)
}
})
for _, sql := range []string{
"SET time_zone='+00:00'",
"CREATE TABLE parent (id INT PRIMARY KEY AUTO_INCREMENT, value TEXT, empty_value TEXT, nullable TEXT, raw VARBINARY(32), amount DECIMAL(20,6), stamp TIMESTAMP(6), generated INT AS (id+1) VIRTUAL) ENGINE=InnoDB",
"INSERT INTO parent (id,value,empty_value,nullable,raw,amount,stamp) VALUES (1,'quote; and ''text''','',NULL,X'0001FF',123.456789,'2026-10-26 12:34:56.123456')",
"SET @saved_test_mode=@@sql_mode",
"SET sql_mode='NO_AUTO_VALUE_ON_ZERO'",
"INSERT INTO parent (id,value,stamp) VALUES (0,'legacy','0000-00-00 00:00:00')",
"SET sql_mode=@saved_test_mode",
"CREATE TABLE child (id INT PRIMARY KEY, parent_id INT, CONSTRAINT child_parent FOREIGN KEY (parent_id) REFERENCES parent(id)) ENGINE=InnoDB",
"INSERT INTO child VALUES (1,1)",
"CREATE TABLE audit (id INT PRIMARY KEY AUTO_INCREMENT, note TEXT) ENGINE=InnoDB",
"CREATE TRIGGER insert_value BEFORE INSERT ON parent FOR EACH ROW SET NEW.value=CONCAT(NEW.value,'!')",
"CREATE TRIGGER audit_z AFTER UPDATE ON parent FOR EACH ROW INSERT INTO audit (note) VALUES ('first')",
"CREATE TRIGGER audit_a AFTER UPDATE ON parent FOR EACH ROW INSERT INTO audit (note) VALUES ('second')",
} {
if err := db.Exec(sql).Error; err != nil {
t.Fatal(err)
}
}
return db
}
migration := func(sql, checks string) *fstest.MapFile {
return &fstest.MapFile{Data: []byte("-- Tables affected: parent, child\n" + checks + "\n" + sql)}
}
for _, failure := range []string{"none", "verify", "FK"} {
t.Run(failure, func(t *testing.T) {
db, root := newDB(t), t.TempDir()
var original string
err := db.Connection(func(conn *gorm.DB) error {
var e error
original, e = mariadb.CreateSnapshot(conn, root, "original")
return e
})
if err != nil {
t.Fatal(err)
}
files := fstest.MapFS{"001_a.sql": migration("SET @example='value; quoted'; PREPARE q FROM 'SELECT 1'; EXECUTE q; DEALLOCATE PREPARE q; ALTER TABLE parent ADD COLUMN added INT; UPDATE parent SET value=@example;", "-- verify: added missing | SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='parent' AND COLUMN_NAME='added' HAVING COUNT(*)=0\n-- verify: wrong value | SELECT 1 FROM parent WHERE value<>'value; quoted'")}
if failure == "verify" {
files["002_b.sql"] = migration("CREATE TABLE new_table (id INT); UPDATE parent SET value='changed';", "-- verify: deliberate failure | SELECT 1")
}
if failure == "FK" {
files["002_b.sql"] = migration("ALTER TABLE child ADD COLUMN added INT; ALTER TABLE child ADD CONSTRAINT broken_fk FOREIGN KEY (added) REFERENCES parent(missing);", "-- verify: should never run | SELECT 1 WHERE 0")
}
applied, err := RunSQLMigrations(db, files, nil, root)
if failure == "none" {
if err != nil || len(applied) != 1 {
t.Fatalf("apply=%v error=%v", applied, err)
}
var count int64
if err := db.Model(&SQLSchemaMigration{}).Count(&count).Error; err != nil || count != 1 {
t.Fatalf("history=%d err=%v", count, err)
}
} else {
if err == nil || !strings.Contains(err.Error(), "all session changes restored") || len(applied) != 0 {
t.Fatalf("apply=%v error=%v", applied, err)
}
if err := mariadb.CheckSnapshot(db, original); err != nil {
t.Fatalf("recovery changed original state: %v", err)
}
}
markers, err := filepath.Glob(filepath.Join(root, "*.pending"))
if err != nil || len(markers) != 0 {
t.Fatalf("markers=%v error=%v", markers, err)
}
})
}
t.Run("interrupted session", func(t *testing.T) {
db, root := newDB(t), t.TempDir()
files := fstest.MapFS{"001_a.sql": migration("ALTER TABLE parent ADD COLUMN added INT", "-- verify: ok | SELECT 1 WHERE 0")}
func() {
defer func() {
if recover() == nil {
t.Error("expected interruption")
}
}()
RunSQLMigrations(db, files, func(string) { panic("interruption") }, root)
}()
if err := db.Exec("ALTER TABLE parent ADD COLUMN added INT").Error; err != nil {
t.Fatal(err)
}
if _, err := RunSQLMigrations(db, files, nil, root); err == nil || !strings.Contains(err.Error(), "interrupted migration") {
t.Fatalf("retry not refused: %v", err)
}
markers, err := filepath.Glob(filepath.Join(root, "*.pending"))
if err != nil || len(markers) != 1 {
t.Fatalf("markers=%v err=%v", markers, err)
}
data, err := os.ReadFile(markers[0])
if err != nil {
t.Fatal(err)
}
if err := RestoreSQLMigrationSession(db, strings.TrimSpace(string(data))); err != nil {
t.Fatal(err)
}
if db.Table("parent").Migrator().HasColumn(&struct{ Added int }{}, "added") {
t.Fatal("interrupted DDL not restored")
}
if _, err := RunSQLMigrations(db, files, nil, root); err != nil {
t.Fatalf("retry after restore: %v", err)
}
})
t.Run("unsupported object", func(t *testing.T) {
db := newDB(t)
if err := db.Exec("CREATE VIEW example_view AS SELECT id FROM parent").Error; err != nil {
t.Fatal(err)
}
files := fstest.MapFS{"001_a.sql": migration("ALTER TABLE parent ADD COLUMN added INT", "-- verify: ok | SELECT 1 WHERE 0")}
if _, err := RunSQLMigrations(db, files, nil, t.TempDir()); err == nil || !strings.Contains(err.Error(), "snapshot refused") {
t.Fatalf("unsupported schema accepted: %v", err)
}
if db.Table("parent").Migrator().HasColumn(&struct{ Added int }{}, "added") {
t.Fatal("unsupported schema was changed")
}
})
t.Run("other client", func(t *testing.T) {
db := newDB(t)
p, err := db.DB()
if err != nil {
t.Fatal(err)
}
p.SetMaxOpenConns(2)
other, err := p.Conn(context.Background())
if err != nil {
t.Fatal(err)
}
defer other.Close()
files := fstest.MapFS{"001_a.sql": migration("ALTER TABLE parent ADD COLUMN added INT", "-- verify: ok | SELECT 1 WHERE 0")}
if _, err := RunSQLMigrations(db, files, nil, t.TempDir()); err == nil || !strings.Contains(err.Error(), "other connections") {
t.Fatalf("active client accepted: %v", err)
}
if db.Table("parent").Migrator().HasColumn(&struct{ Added int }{}, "added") {
t.Fatal("active schema changed")
}
})
t.Run("backup failure", func(t *testing.T) {
db := newDB(t)
path := filepath.Join(t.TempDir(), "not-directory")
if err := os.WriteFile(path, []byte("example"), 0600); err != nil {
t.Fatal(err)
}
files := fstest.MapFS{"001_a.sql": migration("ALTER TABLE parent ADD COLUMN added INT", "-- verify: ok | SELECT 1 WHERE 0")}
if _, err := RunSQLMigrations(db, files, nil, path); err == nil {
t.Fatal("backup failure ignored")
}
if db.Table("parent").Migrator().HasColumn(&struct{ Added int }{}, "added") {
t.Fatal("schema changed without backup")
}
})
}