diff options
| author | Eugene Triguba <eugenetriguba@gmail.com> | 2024-03-11 18:56:21 -0400 |
|---|---|---|
| committer | Eugene Triguba <eugenetriguba@gmail.com> | 2024-03-11 18:56:21 -0400 |
| commit | 58cc6cdbe0a0553a8e4319de12132e82d8bb85df (patch) | |
| tree | a17b8cdbaf74797dc4f52e006635e4f5c9a210c2 /internal | |
| parent | Tweak tests (diff) | |
| download | cli-58cc6cdbe0a0553a8e4319de12132e82d8bb85df.zip | |
Add support for sql server
Diffstat (limited to 'internal')
| -rw-r--r-- | internal/repositories/migration_db_repo.go | 22 | ||||
| -rw-r--r-- | internal/repositories/migration_db_repo_test.go | 1 | ||||
| -rw-r--r-- | internal/storage/db.go | 5 | ||||
| -rw-r--r-- | internal/storage/mssql.go | 78 | ||||
| -rw-r--r-- | internal/storage/mssql_test.go | 123 |
5 files changed, 222 insertions, 7 deletions
diff --git a/internal/repositories/migration_db_repo.go b/internal/repositories/migration_db_repo.go index 27644ae..e97349a 100644 --- a/internal/repositories/migration_db_repo.go +++ b/internal/repositories/migration_db_repo.go @@ -28,18 +28,28 @@ type migrationDBRepo struct { // operates on exists. If it is unable to create or confirm // the table exists, an error is returned. func NewMigrationDBRepo(db storage.DB) (MigrationDBRepo, error) { - _, err := db.Exec(` - CREATE TABLE IF NOT EXISTS bolt_migrations( - version CHARACTER(14) PRIMARY KEY NOT NULL - ); - `) + migrationTableExists, err := db.TableExists("bolt_migrations") if err != nil { return nil, fmt.Errorf( - "unable to create or confirm bolt_migrations database table exists: %w", + "unable to confirm bolt_migrations database table exists: %w", err, ) } + if !migrationTableExists { + _, err := db.Exec(` + CREATE TABLE bolt_migrations( + version CHARACTER(14) PRIMARY KEY NOT NULL + ); + `) + if err != nil { + return nil, fmt.Errorf( + "unable to create bolt_migrations database table: %w", + err, + ) + } + } + return &migrationDBRepo{db: db}, nil } diff --git a/internal/repositories/migration_db_repo_test.go b/internal/repositories/migration_db_repo_test.go index 2a316c9..9c5d824 100644 --- a/internal/repositories/migration_db_repo_test.go +++ b/internal/repositories/migration_db_repo_test.go @@ -245,6 +245,7 @@ func TestRevertWithTx_SuccessfullyReverted(t *testing.T) { t.Cleanup(func() { bolttest.DropTable(t, testdb, "tmp") }) + bolttest.DropTable(t, testdb, "tmp") _, err = testdb.Exec(`CREATE TABLE tmp(id INT NOT NULL PRIMARY KEY)`) assert.Nil(t, err) diff --git a/internal/storage/db.go b/internal/storage/db.go index 70c3344..12adc4c 100644 --- a/internal/storage/db.go +++ b/internal/storage/db.go @@ -8,14 +8,17 @@ import ( "github.com/eugenetriguba/bolt/internal/configloader" _ "github.com/go-sql-driver/mysql" _ "github.com/jackc/pgx/v5/stdlib" + _ "github.com/microsoft/go-mssqldb" ) var postgresqlDriverName = "postgresql" var mysqlDriverName = "mysql" +var mssqlDriverName = "mssql" var supportedDrivers = map[string]dbDriver{ postgresqlDriverName: {name: "pgx", adapter: PostgresqlAdapter{}}, mysqlDriverName: {name: "mysql", adapter: MySQLAdapter{}}, + mssqlDriverName: {name: "sqlserver", adapter: MSSQLAdapter{}}, } type dbDriver struct { @@ -30,7 +33,7 @@ var ( ErrUnableToConnect = errors.New("unable to open connection to database") ErrUnsupportedDriver = fmt.Errorf( "unsupported driver, supported drivers are %s", - []string{postgresqlDriverName, mysqlDriverName}, + []string{postgresqlDriverName, mysqlDriverName, mssqlDriverName}, ) ) diff --git a/internal/storage/mssql.go b/internal/storage/mssql.go new file mode 100644 index 0000000..a7fbead --- /dev/null +++ b/internal/storage/mssql.go @@ -0,0 +1,78 @@ +package storage + +import ( + "fmt" + "strconv" + "strings" + + "github.com/eugenetriguba/bolt/internal/configloader" + "github.com/microsoft/go-mssqldb/msdsn" +) + +type MSSQLAdapter struct{} + +func (m MSSQLAdapter) ConvertGenericPlaceholders( + query string, + argsCount int, +) string { + for i := 0; i <= argsCount; i++ { + placeholder := m.createPlaceholder(i + 1) + query = strings.Replace(query, "?", placeholder, 1) + } + return query +} + +func (m MSSQLAdapter) createPlaceholder(index int) string { + return fmt.Sprintf("@p%d", index) +} + +func (m MSSQLAdapter) TableExists( + executor sqlExecutor, + tableName string, +) (bool, error) { + var exists bool + err := executor.QueryRow(` + SELECT CASE WHEN EXISTS ( + SELECT * + FROM INFORMATION_SCHEMA.TABLES + WHERE TABLE_SCHEMA = 'dbo' + AND TABLE_NAME = @p1 + ) THEN 1 ELSE 0 END + `, tableName).Scan(&exists) + if err != nil { + return false, fmt.Errorf( + "unable to check if dbo.%s exists: %w", + tableName, + err, + ) + } + + return exists, nil +} + +func (m MSSQLAdapter) DatabaseName(executor sqlExecutor) (string, error) { + var name string + err := executor.QueryRow("SELECT DB_NAME();").Scan(&name) + if err != nil { + return "", fmt.Errorf("unable to retrieve database name: %w", err) + } + + return name, nil +} + +func (m MSSQLAdapter) CreateDSN(cfg configloader.ConnectionConfig) string { + port, err := strconv.ParseUint(cfg.Port, 10, 64) + if err != nil { + // Use default port if we can't parse it out. + // The mssql driver requires an int port to be passed. + port = 1433 + } + dsnCfg := msdsn.Config{ + Host: cfg.Host, + Port: port, + User: cfg.User, + Password: cfg.Password, + Database: cfg.DBName, + } + return dsnCfg.URL().String() +} diff --git a/internal/storage/mssql_test.go b/internal/storage/mssql_test.go new file mode 100644 index 0000000..400b210 --- /dev/null +++ b/internal/storage/mssql_test.go @@ -0,0 +1,123 @@ +//go:build mssql + +package storage_test + +import ( + "database/sql" + "fmt" + "testing" + + "github.com/eugenetriguba/bolt/internal/bolttest" + "github.com/eugenetriguba/bolt/internal/configloader" + "github.com/eugenetriguba/bolt/internal/storage" + "github.com/eugenetriguba/checkmate/assert" +) + +func TestConvertGenericPlaceholders(t *testing.T) { + type test struct { + query string + argCount int + expectedQuery string + } + + testCases := []test{ + { + query: "SELECT id, name FROM some_table;", + argCount: 0, + expectedQuery: "SELECT id, name FROM some_table;", + }, + { + query: "SELECT id, name FROM some_table WHERE id = ?;", + argCount: 1, + expectedQuery: "SELECT id, name FROM some_table WHERE id = @p1;", + }, + { + query: "SELECT id, name FROM some_table WHERE id = ? AND name = ?;", + argCount: 2, + expectedQuery: "SELECT id, name FROM some_table WHERE id = @p1 AND name = @p2;", + }, + } + for _, tc := range testCases { + adapter := storage.MSSQLAdapter{} + newQuery := adapter.ConvertGenericPlaceholders(tc.query, tc.argCount) + assert.Equal(t, newQuery, tc.expectedQuery) + } +} + +func TestTableExists(t *testing.T) { + cfg := bolttest.NewTestConnectionConfig() + adapter := storage.MSSQLAdapter{} + db, err := sql.Open("sqlserver", adapter.CreateDSN(cfg)) + assert.Nil(t, err) + t.Cleanup(func() { + _, err = db.Exec("DROP TABLE IF EXISTS tmp;") + assert.Nil(t, err) + assert.Nil(t, db.Close()) + }) + + exists, err := adapter.TableExists(db, "tmp") + assert.Nil(t, err) + assert.False(t, exists) + + _, err = db.Exec("CREATE TABLE tmp(id INT PRIMARY KEY);") + assert.Nil(t, err) + + exists, err = adapter.TableExists(db, "tmp") + assert.Nil(t, err) + assert.True(t, exists) +} + +func TestDatabaseName(t *testing.T) { + cfg := bolttest.NewTestConnectionConfig() + adapter := storage.MSSQLAdapter{} + db, err := sql.Open("sqlserver", adapter.CreateDSN(cfg)) + assert.Nil(t, err) + t.Cleanup(func() { + assert.Nil(t, db.Close()) + }) + + name, err := adapter.DatabaseName(db) + assert.Nil(t, err) + + assert.Equal(t, name, cfg.DBName) +} + +func TestCreateDSN(t *testing.T) { + cfg := configloader.ConnectionConfig{ + Driver: "mssql", + Host: "db1", + Port: "5432", + User: "testuser", + Password: "supersecretpassword", + DBName: "testdb", + } + adapter := storage.MSSQLAdapter{} + + cs := adapter.CreateDSN(cfg) + + expectedConnectionString := fmt.Sprintf( + "sqlserver://%s:%s@%s:%s?database=%s&dial+timeout=0&disableretry=false", + cfg.User, cfg.Password, cfg.Host, cfg.Port, cfg.DBName, + ) + assert.Equal(t, cs, expectedConnectionString) +} + +func TestCreateDSN_UnparsablePort(t *testing.T) { + cfg := configloader.ConnectionConfig{ + Driver: "mssql", + Host: "db1", + Port: "abc123", + User: "testuser", + Password: "supersecretpassword", + DBName: "testdb", + } + adapter := storage.MSSQLAdapter{} + + cs := adapter.CreateDSN(cfg) + + expectedConnectionString := fmt.Sprintf( + "sqlserver://%s:%s@%s:%s?database=%s&dial+timeout=0&disableretry=false", + cfg.User, cfg.Password, cfg.Host, "1433", cfg.DBName, + ) + assert.Equal(t, cs, expectedConnectionString) +} |