diff options
| author | Eugene Triguba <eugenetriguba@gmail.com> | 2024-08-18 16:06:54 -0400 |
|---|---|---|
| committer | GitHub <noreply@github.com> | 2024-08-18 16:06:54 -0400 |
| commit | 3ad5a6ab1ef53330f18014b710534d0ed0cd55b0 (patch) | |
| tree | 5b5d1ed42e59314e65a4ddb8bff2dd7c059fedd9 /internal | |
| parent | Add a couple helper scripts (diff) | |
| download | cli-3ad5a6ab1ef53330f18014b710534d0ed0cd55b0.zip | |
Fix using a schema with a custom migration table name (#7)
* Fix using a schema with a custom migration table name
* Fix test
Diffstat (limited to 'internal')
| -rw-r--r-- | internal/repositories/migration_db_repo_mssql_test.go | 38 | ||||
| -rw-r--r-- | internal/repositories/migration_db_repo_postgresql_test.go | 36 | ||||
| -rw-r--r-- | internal/storage/mssql.go | 18 | ||||
| -rw-r--r-- | internal/storage/postgresql.go | 20 | ||||
| -rw-r--r-- | internal/storage/postgresql_test.go | 28 |
5 files changed, 128 insertions, 12 deletions
diff --git a/internal/repositories/migration_db_repo_mssql_test.go b/internal/repositories/migration_db_repo_mssql_test.go new file mode 100644 index 0000000..5c79168 --- /dev/null +++ b/internal/repositories/migration_db_repo_mssql_test.go @@ -0,0 +1,38 @@ +//go:build mssql + +package repositories_test + +import ( + "testing" + + "github.com/eugenetriguba/bolt/internal/bolttest" + "github.com/eugenetriguba/bolt/internal/repositories" + "github.com/eugenetriguba/checkmate/assert" +) + +func TestNewMigrationDBRepo_CreatesTableInSchema_MSSQL(t *testing.T) { + testdb := bolttest.NewTestDB(t) + schemaName := "custom_schema" + tableName := schemaName + ".bolt_migrations" + + t.Cleanup(func() { + _, err := testdb.Exec("DROP TABLE IF EXISTS " + tableName) + assert.Nil(t, err) + _, err = testdb.Exec("DROP SCHEMA IF EXISTS " + schemaName) + assert.Nil(t, err) + }) + + _, err := testdb.Exec("CREATE SCHEMA " + schemaName) + assert.Nil(t, err) + + exists, err := testdb.TableExists(tableName) + assert.Nil(t, err) + assert.False(t, exists) + + _, err = repositories.NewMigrationDBRepo(tableName, testdb) + assert.Nil(t, err) + + exists, err = testdb.TableExists(tableName) + assert.Nil(t, err) + assert.True(t, exists) +} diff --git a/internal/repositories/migration_db_repo_postgresql_test.go b/internal/repositories/migration_db_repo_postgresql_test.go new file mode 100644 index 0000000..0df9c1d --- /dev/null +++ b/internal/repositories/migration_db_repo_postgresql_test.go @@ -0,0 +1,36 @@ +//go:build postgresql + +package repositories_test + +import ( + "testing" + + "github.com/eugenetriguba/bolt/internal/bolttest" + "github.com/eugenetriguba/bolt/internal/repositories" + "github.com/eugenetriguba/checkmate/assert" +) + +func TestNewMigrationDBRepo_CreatesTableInSchema_Postgresql(t *testing.T) { + testdb := bolttest.NewTestDB(t) + schemaName := "custom_schema" + tableName := schemaName + ".bolt_migrations" + + t.Cleanup(func() { + _, err := testdb.Exec("DROP SCHEMA IF EXISTS " + schemaName + " CASCADE;") + assert.Nil(t, err) + }) + + _, err := testdb.Exec("CREATE SCHEMA IF NOT EXISTS " + schemaName) + assert.Nil(t, err) + + exists, err := testdb.TableExists(tableName) + assert.Nil(t, err) + assert.False(t, exists) + + _, err = repositories.NewMigrationDBRepo(tableName, testdb) + assert.Nil(t, err) + + exists, err = testdb.TableExists(tableName) + assert.Nil(t, err) + assert.True(t, exists) +} diff --git a/internal/storage/mssql.go b/internal/storage/mssql.go index a7fbead..4a084ca 100644 --- a/internal/storage/mssql.go +++ b/internal/storage/mssql.go @@ -31,17 +31,27 @@ func (m MSSQLAdapter) TableExists( tableName string, ) (bool, error) { var exists bool + + schemaName := "dbo" + parts := strings.Split(tableName, ".") + if len(parts) == 2 { + schemaName = parts[0] + tableName = parts[1] + } else { + tableName = parts[0] + } + err := executor.QueryRow(` SELECT CASE WHEN EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLES - WHERE TABLE_SCHEMA = 'dbo' - AND TABLE_NAME = @p1 + WHERE TABLE_SCHEMA = @p1 + AND TABLE_NAME = @p2 ) THEN 1 ELSE 0 END - `, tableName).Scan(&exists) + `, schemaName, tableName).Scan(&exists) if err != nil { return false, fmt.Errorf( - "unable to check if dbo.%s exists: %w", + "unable to check if %s exists: %w", tableName, err, ) diff --git a/internal/storage/postgresql.go b/internal/storage/postgresql.go index dee0288..d93d014 100644 --- a/internal/storage/postgresql.go +++ b/internal/storage/postgresql.go @@ -29,19 +29,25 @@ func (p PostgresqlAdapter) TableExists( tableName string, ) (bool, error) { var exists bool - // Assumption: Anytime we check if a table exists, it will be - // for the 'public' schema. If someone wants to have bolt_migrations - // table outside of the 'public' schema on postgresql, this would be - // an issue. + + schemaName := "public" + parts := strings.Split(tableName, ".") + if len(parts) == 2 { + schemaName = parts[0] + tableName = parts[1] + } else { + tableName = parts[0] + } + err := executor.QueryRow(` SELECT EXISTS ( SELECT FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace - WHERE n.nspname = 'public' - AND c.relname = $1 + WHERE n.nspname = $1 + AND c.relname = $2 AND c.relkind = 'r' -- Only tables ); - `, tableName).Scan(&exists) + `, schemaName, tableName).Scan(&exists) if err != nil { return false, fmt.Errorf( "unable to check if %s exists: %w", diff --git a/internal/storage/postgresql_test.go b/internal/storage/postgresql_test.go index ef89cff..d244646 100644 --- a/internal/storage/postgresql_test.go +++ b/internal/storage/postgresql_test.go @@ -44,7 +44,7 @@ func TestPostgresql_ConvertGenericPlaceholders(t *testing.T) { } } -func TestPostgresql_TableExists(t *testing.T) { +func TestPostgresql_TableExistsDefaultSchema(t *testing.T) { cfg := bolttest.NewTestConnectionConfig() adapter := storage.PostgresqlAdapter{} db, err := sql.Open("pgx", adapter.CreateDSN(cfg)) @@ -67,6 +67,32 @@ func TestPostgresql_TableExists(t *testing.T) { assert.True(t, exists) } +func TestPostgresql_TableExistsCustomSchema(t *testing.T) { + cfg := bolttest.NewTestConnectionConfig() + adapter := storage.PostgresqlAdapter{} + db, err := sql.Open("pgx", adapter.CreateDSN(cfg)) + assert.Nil(t, err) + t.Cleanup(func() { + _, err = db.Exec("DROP SCHEMA IF EXISTS custom_schema CASCADE;") + assert.Nil(t, err) + assert.Nil(t, db.Close()) + }) + + exists, err := adapter.TableExists(db, "custom_table.tmp") + assert.Nil(t, err) + assert.False(t, exists) + + _, err = db.Exec("CREATE SCHEMA custom_schema;") + assert.Nil(t, err) + + _, err = db.Exec("CREATE TABLE custom_schema.tmp(id INT PRIMARY KEY);") + assert.Nil(t, err) + + exists, err = adapter.TableExists(db, "custom_schema.tmp") + assert.Nil(t, err) + assert.True(t, exists) +} + func TestPostgresql_DatabaseName(t *testing.T) { cfg := bolttest.NewTestConnectionConfig() adapter := storage.PostgresqlAdapter{} |