aboutsummaryrefslogtreecommitdiffstats
path: root/internal
diff options
context:
space:
mode:
authorEugene Triguba <eugenetriguba@gmail.com>2024-03-11 18:56:21 -0400
committerEugene Triguba <eugenetriguba@gmail.com>2024-03-11 18:56:21 -0400
commit58cc6cdbe0a0553a8e4319de12132e82d8bb85df (patch)
treea17b8cdbaf74797dc4f52e006635e4f5c9a210c2 /internal
parentTweak tests (diff)
downloadcli-58cc6cdbe0a0553a8e4319de12132e82d8bb85df.zip
Add support for sql server
Diffstat (limited to 'internal')
-rw-r--r--internal/repositories/migration_db_repo.go22
-rw-r--r--internal/repositories/migration_db_repo_test.go1
-rw-r--r--internal/storage/db.go5
-rw-r--r--internal/storage/mssql.go78
-rw-r--r--internal/storage/mssql_test.go123
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)
+}