imigrate_test.go

package imigrate

import (
	"database/sql"
	"fmt"
	"log"
	"os"
	"path"
	"strings"
	"testing"
	"testing/fstest"

	"github.com/bvinc/go-sqlite-lite/sqlite3"
)

type DB struct {
	*sqlite3.Conn
}

func check(err error) {
	if err != nil {
		log.Panic(err)
	}
}

func NewDB() *DB {
	conn, err := sqlite3.Open(":memory:")
	check(err)
	return &DB{Conn: conn}
}

type DBResult struct {
	lastInsertID int64
	rowsAffected int64
	err          error
}

func (o DBResult) LastInsertId() (int64, error) {
	return o.lastInsertID, o.err
}

func (o DBResult) RowsAffected() (int64, error) {
	return o.rowsAffected, o.err
}

func (o DB) Exec(query string, args ...interface{}) (sql.Result, error) {
	err := o.Conn.Exec(query, args...)
	res := DBResult{
		lastInsertID: o.LastInsertRowID(),
		rowsAffected: int64(o.Changes()),
		err:          err,
	}
	return res, err
}

func (o DB) Get(dst []interface{}, query string, args ...interface{}) (err error) {
	stmt, err := o.Conn.Prepare(query, args...)
	if err != nil {
		return
	}
	defer stmt.Close()
	var hasRow bool
	for {
		hasRow, err = stmt.Step()
		if !hasRow {
			break
		}
		if err != nil {
			return
		}
		err = stmt.Scan(dst...)
		if err != nil {
			return
		}
	}
	return
}

func (o DB) GetVersions(query string, args ...interface{}) (versions []int64, err error) {
	stmt, err := o.Conn.Prepare(query, args...)
	if err != nil {
		return
	}
	defer stmt.Close()
	var hasRow bool
	for {
		hasRow, err = stmt.Step()
		if !hasRow {
			break
		}
		if err != nil {
			return
		}
		var version int64
		err = stmt.Scan(&version)
		if err != nil {
			return
		}
		versions = append(versions, version)
	}
	return
}

func testFS(files map[string]string) fstest.MapFS {
	m := make(fstest.MapFS)
	for name, content := range files {
		m["migrations/"+name] = &fstest.MapFile{Data: []byte(content)}
	}
	return m
}

var migrationFiles = map[string]string{
	"1111110001-mig1.sql": `
-- ==== UP ====
create table foo (id integer primary key);
-- ==== DOWN ====
drop table foo;
`,
	"1111110002-mig2.sql": `
-- ==== UP ====
create table bar (id integer primary key);
-- ==== DOWN ====
drop table bar;
`,
	"1111110003-mig3.sql": `
-- ==== UP ====
drop table bar;
create table baz (id integer primary key);
-- ==== DOWN ====
create table bar (id integer primary key);
drop table baz;
`,
	"1111110004-mig4.sql": `
-- ==== UP ====
create table bux (id integer primary key);
-- ==== DOWN ====
drop table bux;
`,
}

func TestIMigrateUpDown(t *testing.T) {
	db := NewDB()
	defer db.Close()
	fs := testFS(map[string]string{"1111110001-mig1.sql": migrationFiles["1111110001-mig1.sql"]})
	mig := NewIMigrator(db, fs)

	// UP
	check(mig.Up(-1, 0))
	var tableName string

	check(db.Get([]interface{}{&tableName}, "select name from sqlite_master where name='foo'"))
	if tableName != "foo" {
		t.Fatalf("expected %s to equal foo", tableName)
	}

	tableName = ""
	check(db.Get([]interface{}{&tableName}, "select name from sqlite_master where name=?", mig.TableName))
	if tableName != mig.TableName {
		t.Fatalf("expected %s to equal %s", tableName, mig.TableName)
	}

	// DOWN
	tableName = ""
	check(mig.Down(-1, 0))
	check(db.Get([]interface{}{&tableName}, "select name from sqlite_master where name='foo'"))
	if tableName != "" {
		t.Fatalf("expected %s to equal \"\"", tableName)
	}
}

func TestIMigrateUp(t *testing.T) {
	db := NewDB()
	defer db.Close()
	fs := testFS(migrationFiles)
	mig := NewIMigrator(db, fs)

	check(mig.Up(-1, 0))
	expectedTables := []string{
		"foo", "baz", "bux", mig.TableName,
	}
	for _, tbl := range expectedTables {
		var sqlTbl string
		check(db.Get([]interface{}{&sqlTbl}, "select name from sqlite_master where name=?", tbl))
		if sqlTbl != tbl {
			t.Fatalf("expected %s to exist, got %s", tbl, sqlTbl)
		}
	}
	versions := []int{1111110001, 1111110002, 1111110003, 1111110004}
	for _, v := range versions {
		var versionMigrated int
		sql := fmt.Sprintf("select %s from %s where %s=?", mig.VersionColumn, mig.TableName, mig.VersionColumn)
		check(db.Get([]interface{}{&versionMigrated}, sql, v))
		if v != versionMigrated {
			t.Fatalf("Expected version %d to exist, but got %d", v, versionMigrated)
		}
	}

	check(mig.Down(-1, 0))
	var count int
	check(db.Get([]interface{}{&count}, "select count(*) from sqlite_master where type='table'"))
	if count != 1 {
		t.Fatalf("expected one table to exist, not %d", count)
	}
	count = 1111
	check(db.Get([]interface{}{&count}, fmt.Sprintf("select count(*) from %s", mig.TableName)))
	if count != 0 {
		t.Fatalf("Expected 0 migrations to exist after Down, got %d", count)
	}
	var migrationTable string
	check(db.Get([]interface{}{&migrationTable}, "select name from sqlite_master where type='table'"))

	if migrationTable != mig.TableName {
		t.Fatalf("Expected table %s to exist, got %s", mig.TableName, migrationTable)
	}

	check(mig.Up(-1, 1111110004))
	check(db.Get([]interface{}{&migrationTable}, "select name from sqlite_master where name=?", "bux"))
	if migrationTable != "bux" {
		t.Fatalf("expected table bux to exist on Up with version, but got %s", migrationTable)
	}

	check(mig.Down(-1, 0))
	check(mig.Up(2, 0))
	expectedTables = []string{
		"foo", "bar", mig.TableName,
	}
	for _, tbl := range expectedTables {
		var sqlTbl string
		check(db.Get([]interface{}{&sqlTbl}, "select name from sqlite_master where name=?", tbl))
		if sqlTbl != tbl {
			t.Fatalf("expected %s to exist, got %s", tbl, sqlTbl)
		}
	}
}

func TestIMigrateDown(t *testing.T) {
	db := NewDB()
	defer db.Close()
	fs := testFS(map[string]string{
		"1111110001-mig1.sql": migrationFiles["1111110001-mig1.sql"],
		"1111110002-mig2.sql": migrationFiles["1111110002-mig2.sql"],
	})
	mig := NewIMigrator(db, fs)

	// Up all, then down with steps=1
	check(mig.Up(-1, 0))
	check(mig.Down(1, 0))

	var count int
	check(db.Get([]interface{}{&count}, fmt.Sprintf("select count(*) from %s", mig.TableName)))
	if count != 1 {
		t.Fatalf("expected 1 version after down 1, got %d", count)
	}

	// Down specific version
	check(mig.Up(-1, 0))
	check(mig.Down(-1, 1111110001))

	var v int
	check(db.Get([]interface{}{&v}, fmt.Sprintf("select count(*) from %s where %s = 1111110001", mig.TableName, mig.VersionColumn)))
	if v != 0 {
		t.Fatalf("expected version 1111110001 removed, got count %d", v)
	}
	check(db.Get([]interface{}{&v}, fmt.Sprintf("select count(*) from %s where %s = 1111110002", mig.TableName, mig.VersionColumn)))
	if v != 1 {
		t.Fatalf("expected version 1111110002 still present, got count %d", v)
	}
}

func TestIMigrateRedo(t *testing.T) {
	db := NewDB()
	defer db.Close()
	fs := testFS(map[string]string{"1111110001-mig1.sql": migrationFiles["1111110001-mig1.sql"]})
	mig := NewIMigrator(db, fs)

	check(mig.Up(-1, 0))
	check(mig.Redo(1, 0))

	var tableName string
	check(db.Get([]interface{}{&tableName}, "select name from sqlite_master where name='foo'"))
	if tableName != "foo" {
		t.Fatalf("expected foo table to exist after redo, got %s", tableName)
	}

	var count int
	check(db.Get([]interface{}{&count}, fmt.Sprintf("select count(*) from %s", mig.TableName)))
	if count != 1 {
		t.Fatalf("expected 1 version after redo, got %d", count)
	}
}

func TestIMigrateRollback(t *testing.T) {
	db := NewDB()
	defer db.Close()
	fs := testFS(map[string]string{
		"1111110001-mig1.sql": migrationFiles["1111110001-mig1.sql"],
		"1111110002-mig2.sql": migrationFiles["1111110002-mig2.sql"],
	})
	mig := NewIMigrator(db, fs)

	check(mig.Up(-1, 0))
	check(mig.Rollback(1))

	var count int
	check(db.Get([]interface{}{&count}, fmt.Sprintf("select count(*) from %s", mig.TableName)))
	if count != 1 {
		t.Fatalf("expected 1 version after rollback, got %d", count)
	}

	var tableName string
	check(db.Get([]interface{}{&tableName}, "select name from sqlite_master where name='bar'"))
	if tableName != "" {
		t.Fatalf("expected bar table to be dropped after rollback, got %s", tableName)
	}
	check(db.Get([]interface{}{&tableName}, "select name from sqlite_master where name='foo'"))
	if tableName != "foo" {
		t.Fatalf("expected foo table to still exist, got %s", tableName)
	}
}

func TestIMigrateCreate(t *testing.T) {
	db := NewDB()
	defer db.Close()
	fs := fstest.MapFS{}
	mig := NewIMigrator(db, fs)

	dir := t.TempDir()
	mig.WriteDirname = dir

	err := mig.Create("test_migration")
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}

	entries, err := os.ReadDir(dir)
	if err != nil {
		t.Fatalf("unexpected error reading dir: %v", err)
	}
	if len(entries) != 1 {
		t.Fatalf("expected 1 file, got %d", len(entries))
	}
	if !strings.Contains(entries[0].Name(), "test_migration") {
		t.Fatalf("expected filename to contain test_migration, got %s", entries[0].Name())
	}
	if !strings.HasSuffix(entries[0].Name(), ".sql") {
		t.Fatalf("expected .sql extension, got %s", entries[0].Name())
	}

	content, err := os.ReadFile(path.Join(dir, entries[0].Name()))
	if err != nil {
		t.Fatalf("unexpected error reading file: %v", err)
	}
	s := string(content)
	if !strings.Contains(s, "==== UP ====") {
		t.Fatal("expected UP marker in file")
	}
	if !strings.Contains(s, "==== DOWN ====") {
		t.Fatal("expected DOWN marker in file")
	}
}

func TestIMigrateStatus(t *testing.T) {
	db := NewDB()
	defer db.Close()
	fs := testFS(map[string]string{
		"1111110001-mig1.sql": migrationFiles["1111110001-mig1.sql"],
		"1111110002-mig2.sql": migrationFiles["1111110002-mig2.sql"],
	})
	mig := NewIMigrator(db, fs)
	mig.Logger = DiscardLogger

	err := mig.Status()
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}

	check(mig.Up(1, 0))
	err = mig.Status()
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
}

func TestIMigrateUpReturnsError(t *testing.T) {
	db := NewDB()
	defer db.Close()

	fs := testFS(map[string]string{
		"1111110001-bad.sql": `
-- ==== UP ====
CREATE TABLE foo (id integer primary key);
CREATE TABLE foo (id integer primary key);
-- ==== DOWN ====
DROP TABLE foo;
`,
	})
	mig := NewIMigrator(db, fs)
	mig.Logger = DiscardLogger

	err := mig.Up(-1, 0)
	if err == nil {
		t.Fatal("expected error from duplicate CREATE TABLE")
	}
}

func TestIMigrateSetupErrorBadDir(t *testing.T) {
	db := NewDB()
	defer db.Close()

	fs := fstest.MapFS{}
	mig := NewIMigrator(db, fs)
	mig.Dirname = "does_not_exist"
	mig.Logger = DiscardLogger

	err := mig.Up(-1, 0)
	if err == nil {
		t.Fatal("expected error from bad directory")
	}
}

func TestIMigrateIdempotentUp(t *testing.T) {
	db := NewDB()
	defer db.Close()
	fs := testFS(map[string]string{"1111110001-mig1.sql": migrationFiles["1111110001-mig1.sql"]})
	mig := NewIMigrator(db, fs)

	check(mig.Up(-1, 0))
	check(mig.Up(-1, 0))

	var count int
	check(db.Get([]interface{}{&count}, fmt.Sprintf("select count(*) from %s", mig.TableName)))
	if count != 1 {
		t.Fatalf("expected 1 version after double up, got %d", count)
	}
}

func TestIMigrateSilentLogger(t *testing.T) {
	db := NewDB()
	defer db.Close()
	fs := testFS(map[string]string{"1111110001-mig1.sql": migrationFiles["1111110001-mig1.sql"]})
	mig := NewIMigrator(db, fs)
	mig.Logger = DiscardLogger

	check(mig.Up(-1, 0))
	check(mig.Down(-1, 0))
}

func TestIMigrateDuplicateVersion(t *testing.T) {
	db := NewDB()
	defer db.Close()

	fs := testFS(map[string]string{
		"1111110001-first.sql": `
-- ==== UP ====
create table foo (id integer primary key);
-- ==== DOWN ====
drop table foo;
`,
		"1111110001-second.sql": `
-- ==== UP ====
create table bar (id integer primary key);
-- ==== DOWN ====
drop table bar;
`,
	})
	mig := NewIMigrator(db, fs)
	mig.Logger = DiscardLogger

	err := mig.Up(-1, 0)
	if err == nil {
		t.Fatal("expected error from duplicate version")
	}
	if !strings.Contains(err.Error(), "duplicate migration version") {
		t.Fatalf("expected duplicate version error, got: %v", err)
	}
}

func TestIMigrateDryRun(t *testing.T) {
	db := NewDB()
	defer db.Close()
	fs := testFS(map[string]string{
		"1111110001-mig1.sql": migrationFiles["1111110001-mig1.sql"],
		"1111110002-mig2.sql": migrationFiles["1111110002-mig2.sql"],
	})
	mig := NewIMigrator(db, fs)
	mig.Logger = DiscardLogger

	// Dry run should not create any tables
	check(mig.DryRun(-1, 0))

	var count int
	check(db.Get([]interface{}{&count}, "select count(*) from sqlite_master where type='table' and name != ?", mig.TableName))
	if count != 0 {
		t.Fatalf("expected no tables after dry run, got %d", count)
	}

	// Dry run should not insert into versions table
	check(db.Get([]interface{}{&count}, fmt.Sprintf("select count(*) from %s", mig.TableName)))
	if count != 0 {
		t.Fatalf("expected no versions after dry run, got %d", count)
	}

	// After actual up, dry run should show nothing pending
	check(mig.Up(-1, 0))

	// Verify tables exist now
	check(db.Get([]interface{}{&count}, "select count(*) from sqlite_master where type='table' and name != ?", mig.TableName))
	if count != 2 {
		t.Fatalf("expected 2 tables after up, got %d", count)
	}
}

func TestIMigrateDryRunWithSteps(t *testing.T) {
	db := NewDB()
	defer db.Close()
	fs := testFS(migrationFiles)
	mig := NewIMigrator(db, fs)
	mig.Logger = DiscardLogger

	// Dry run with steps=1 should not error
	check(mig.DryRun(1, 0))

	// Nothing should be applied
	var count int
	check(db.Get([]interface{}{&count}, fmt.Sprintf("select count(*) from %s", mig.TableName)))
	if count != 0 {
		t.Fatalf("expected no versions after dry run, got %d", count)
	}
}

func TestIMigrateDryRunWithVersion(t *testing.T) {
	db := NewDB()
	defer db.Close()
	fs := testFS(migrationFiles)
	mig := NewIMigrator(db, fs)
	mig.Logger = DiscardLogger

	// Dry run specific version
	check(mig.DryRun(-1, 1111110003))

	// Nothing should be applied
	var count int
	check(db.Get([]interface{}{&count}, fmt.Sprintf("select count(*) from %s", mig.TableName)))
	if count != 0 {
		t.Fatalf("expected no versions after dry run, got %d", count)
	}
}