package repositories_test import ( "database/sql" "errors" "testing" "git.eugenetriguba.com/bolt/cli/internal/bolttest" "git.eugenetriguba.com/bolt/cli/internal/models" "git.eugenetriguba.com/bolt/cli/internal/repositories" "git.eugenetriguba.com/checkmate/assert" ) func TestNewMigrationDBRepo_CreatesTable(t *testing.T) { testdb := bolttest.NewTestDB(t) exists, err := testdb.TableExists("bolt_migrations") assert.Nil(t, err) assert.False(t, exists) _, err = repositories.NewMigrationDBRepo("bolt_migrations", testdb) assert.Nil(t, err) exists, err = testdb.TableExists("bolt_migrations") assert.Nil(t, err) assert.True(t, exists) } func TestNewMigrationDBRepo_CreateTableExecError(t *testing.T) { mockDB := &bolttest.MockDB{ TableExistsFunc: func(tableName string) (bool, error) { return false, nil }, ExecFunc: func(query string, args ...interface{}) (sql.Result, error) { return nil, errors.New("exec error") }, } _, err := repositories.NewMigrationDBRepo("bolt_migrations", mockDB) assert.NotNil(t, err) assert.ErrorContains(t, err, "unable to create 'bolt_migrations' database table: exec error") } func TestNewMigrationDBRepo_TableExistsError(t *testing.T) { mockDB := &bolttest.MockDB{ TableExistsFunc: func(tableName string) (bool, error) { return false, errors.New("table exists failed") }, } _, err := repositories.NewMigrationDBRepo("bolt_migrations", mockDB) assert.NotNil(t, err) assert.ErrorContains(t, err, "unable to confirm 'bolt_migrations' database table exists: table exists failed") } func TestNewMigrationDBRepo_TableAlreadyExists(t *testing.T) { testdb := bolttest.NewTestDB(t) _, err := testdb.Exec(`CREATE TABLE bolt_migrations(id INT NOT NULL PRIMARY KEY)`) assert.Nil(t, err) _, err = testdb.Exec(`INSERT INTO bolt_migrations(id) VALUES (1);`) assert.Nil(t, err) _, err = repositories.NewMigrationDBRepo("bolt_migrations", testdb) assert.Nil(t, err) var count int err = testdb.QueryRow("SELECT count(*) FROM bolt_migrations;").Scan(&count) assert.Nil(t, err) assert.Equal(t, count, 1) var scanResult int err = testdb.QueryRow("SELECT id FROM bolt_migrations;").Scan(&scanResult) assert.Nil(t, err) assert.Equal(t, scanResult, 1) } func TestList_EmptyTable(t *testing.T) { db := bolttest.NewTestDB(t) repo, err := repositories.NewMigrationDBRepo("bolt_migrations", db) assert.Nil(t, err) migrations, err := repo.List() assert.Nil(t, err) assert.Equal(t, len(migrations), 0) } func TestList_QueryError(t *testing.T) { mockDB := &bolttest.MockDB{ TableExistsFunc: func(tableName string) (bool, error) { return true, nil }, QueryFunc: func(query string, args ...interface{}) (*sql.Rows, error) { return nil, errors.New("query error") }, } repo, err := repositories.NewMigrationDBRepo("bolt_migrations", mockDB) assert.Nil(t, err) _, err = repo.List() assert.NotNil(t, err) assert.ErrorContains(t, err, "unable to execute query to select versions from 'bolt_migrations' database table: query error") } func TestList_SingleResult(t *testing.T) { db := bolttest.NewTestDB(t) repo, err := repositories.NewMigrationDBRepo("bolt_migrations", db) assert.Nil(t, err) version := "20230101000000" _, err = db.Exec("INSERT INTO bolt_migrations(version) VALUES(?)", version) assert.Nil(t, err) migrations, err := repo.List() assert.Nil(t, err) assert.Equal(t, len(migrations), 1) assert.DeepEqual( t, migrations[version], &models.Migration{Version: version, Message: "", Applied: true}, ) } func TestList_ShortVersion(t *testing.T) { db := bolttest.NewTestDB(t) repo, err := repositories.NewMigrationDBRepo("bolt_migrations", db) assert.Nil(t, err) version := "20230101" _, err = db.Exec("INSERT INTO bolt_migrations(version) VALUES(?)", version) assert.Nil(t, err) migrations, err := repo.List() assert.Nil(t, err) assert.Equal(t, len(migrations), 1) assert.DeepEqual( t, migrations[version], &models.Migration{Version: version, Message: "", Applied: true}, ) } func TestIsApplied_WithNotApplied(t *testing.T) { db := bolttest.NewTestDB(t) repo, err := repositories.NewMigrationDBRepo("bolt_migrations", db) assert.Nil(t, err) version := "20230101010101" applied, err := repo.IsApplied(version) assert.Nil(t, err) assert.Equal(t, applied, false) } func TestIsApplied_WithApplied(t *testing.T) { db := bolttest.NewTestDB(t) repo, err := repositories.NewMigrationDBRepo("bolt_migrations", db) assert.Nil(t, err) version := "20230101010101" _, err = db.Exec("INSERT INTO bolt_migrations(version) VALUES(?)", version) assert.Nil(t, err) applied, err := repo.IsApplied(version) assert.Nil(t, err) assert.Equal(t, applied, true) } func TestIsApplied_SQLInjectionAttempt(t *testing.T) { db := bolttest.NewTestDB(t) repo, err := repositories.NewMigrationDBRepo("bolt_migrations", db) assert.Nil(t, err) version := "20230101010101'; DROP TABLE bolt_migrations; --" applied, err := repo.IsApplied(version) assert.Nil(t, err) assert.Equal(t, applied, false) exists, err := db.TableExists("bolt_migrations") assert.Nil(t, err) assert.True(t, exists) } func TestApply(t *testing.T) { testdb := bolttest.NewTestDB(t) repo, err := repositories.NewMigrationDBRepo("bolt_migrations", testdb) assert.Nil(t, err) migration := models.NewMigration(1, "test") err = repo.Apply(`CREATE TABLE tmp(id INT NOT NULL PRIMARY KEY)`, migration) assert.Nil(t, err) assert.Equal(t, migration.Applied, true) exists, err := testdb.TableExists("tmp") assert.Nil(t, err) assert.True(t, exists) applied, err := repo.IsApplied(migration.Version) assert.Nil(t, err) assert.Equal(t, applied, true) } func TestApply_MalformedSql(t *testing.T) { db := bolttest.NewTestDB(t) repo, err := repositories.NewMigrationDBRepo("bolt_migrations", db) assert.Nil(t, err) migration := models.NewMigration(1, "test") err = repo.Apply("this is not SQL", migration) assert.NotNil(t, err) assert.Equal(t, migration.Applied, false) } func TestApplyWithTx_ExecErr(t *testing.T) { db := bolttest.NewTestDB(t) repo, err := repositories.NewMigrationDBRepo("bolt_migrations", db) assert.Nil(t, err) migration := models.NewMigration(1, "test") err = repo.ApplyWithTx("SELECT 1 FROM abc123donotexist;", migration) assert.ErrorContains(t, err, `unable to execute upgrade script`) } func TestApplyWithTx_SuccessfullyApplied(t *testing.T) { testdb := bolttest.NewTestDB(t) repo, err := repositories.NewMigrationDBRepo("bolt_migrations", testdb) assert.Nil(t, err) migration := models.NewMigration(1, "test") err = repo.ApplyWithTx(`CREATE TABLE tmp(id INT NOT NULL PRIMARY KEY)`, migration) assert.Nil(t, err) assert.Equal(t, migration.Applied, true) exists, err := testdb.TableExists("tmp") assert.Nil(t, err) assert.True(t, exists) applied, err := repo.IsApplied(migration.Version) assert.Nil(t, err) assert.Equal(t, applied, true) } func TestRevert(t *testing.T) { testdb := bolttest.NewTestDB(t) repo, err := repositories.NewMigrationDBRepo("bolt_migrations", testdb) assert.Nil(t, err) _, err = testdb.Exec(`CREATE TABLE tmp(id INT NOT NULL PRIMARY KEY)`) assert.Nil(t, err) migration := models.NewMigration(1, "test") _, err = testdb.Exec( "INSERT INTO bolt_migrations(version) VALUES(?)", migration.Version, ) assert.Nil(t, err) migration.Applied = true err = repo.Revert(`DROP TABLE tmp;`, migration) assert.Nil(t, err) assert.Equal(t, migration.Applied, false) exists, err := testdb.TableExists("tmp") assert.Nil(t, err) assert.False(t, exists) var count int err = testdb.QueryRow("SELECT count(*) FROM bolt_migrations;").Scan(&count) assert.Nil(t, err) assert.Equal(t, count, 0) } func TestRevert_MalformedSql(t *testing.T) { db := bolttest.NewTestDB(t) repo, err := repositories.NewMigrationDBRepo("bolt_migrations", db) assert.Nil(t, err) migration := models.NewMigration(1, "test") migration.Applied = true err = repo.Revert("this is not SQL", migration) assert.ErrorContains(t, err, "unable to execute downgrade script") assert.Equal(t, migration.Applied, true) } func TestRevertWithTx_ExecErr(t *testing.T) { db := bolttest.NewTestDB(t) repo, err := repositories.NewMigrationDBRepo("bolt_migrations", db) assert.Nil(t, err) migration := models.NewMigration(1, "test") err = repo.RevertWithTx("DROP TABLE abc123donotexist;", migration) assert.ErrorContains(t, err, `unable to execute downgrade script`) } func TestRevertWithTx_SuccessfullyReverted(t *testing.T) { testdb := bolttest.NewTestDB(t) repo, err := repositories.NewMigrationDBRepo("bolt_migrations", testdb) assert.Nil(t, err) _, err = testdb.Exec(`CREATE TABLE tmp(id INT NOT NULL PRIMARY KEY)`) assert.Nil(t, err) migration := models.NewMigration(1, "test") _, err = testdb.Exec( "INSERT INTO bolt_migrations(version) VALUES(?)", migration.Version, ) assert.Nil(t, err) migration.Applied = true err = repo.RevertWithTx(`DROP TABLE tmp;`, migration) assert.Nil(t, err) assert.Equal(t, migration.Applied, false) exists, err := testdb.TableExists("tmp") assert.Nil(t, err) assert.False(t, exists) var count int err = testdb.QueryRow("SELECT count(*) FROM bolt_migrations;").Scan(&count) assert.Nil(t, err) assert.Equal(t, count, 0) } func TestNewMigrationDBRepo_InvalidTableName(t *testing.T) { db := bolttest.NewTestDB(t) invalidTableNames := []string{ "bolt_migrations; DROP TABLE users; --", // SQL injection attempt "bolt_migrations`", // Backtick is not allowed "bolt_migrations'", // Single quote is not allowed "bolt_migrations\"", // Double quote is not allowed "bolt_migrations;", // Semicolon could terminate the SQL statement "bolt_migrations\\", // Backslash is not allowed "myschema..migrations_table", // Double dot, which is invalid for schema.table format "myschema.migrations_table.", // Trailing dot is not allowed "myschema.migrations_table;", // Semicolon in schema-qualified name is not allowed ".migrations_table", // Leading dot is not allowed "myschema.migrations_table$", // Dollar sign is not allowed } for _, tableName := range invalidTableNames { _, err := repositories.NewMigrationDBRepo(tableName, db) assert.NotNil(t, err) assert.ErrorContains(t, err, "invalid migration table name") } }