From 824226ef686d604bc3b1e8675143dabfe8df6555 Mon Sep 17 00:00:00 2001 From: Julien Dessaux Date: Fri, 9 Apr 2021 23:56:16 +0200 Subject: Began implementing a database backend --- go.mod | 3 + go.sum | 7 ++- pkg/database/database.go | 58 +++++++++++++++++ pkg/database/database_test.go | 143 ++++++++++++++++++++++++++++++++++++++++++ pkg/database/error.go | 57 +++++++++++++++++ pkg/database/error_test.go | 15 +++++ pkg/database/migrations.go | 19 ++++++ 7 files changed, 301 insertions(+), 1 deletion(-) create mode 100644 pkg/database/database.go create mode 100644 pkg/database/database_test.go create mode 100644 pkg/database/error.go create mode 100644 pkg/database/error_test.go create mode 100644 pkg/database/migrations.go diff --git a/go.mod b/go.mod index 255bfa4..254fdec 100644 --- a/go.mod +++ b/go.mod @@ -3,7 +3,10 @@ module git.adyxax.org/adyxax/trains go 1.16 require ( + github.com/DATA-DOG/go-sqlmock v1.5.0 + github.com/davecgh/go-spew v1.1.1 // indirect github.com/kr/pretty v0.2.1 // indirect + github.com/mattn/go-sqlite3 v1.14.6 github.com/stretchr/testify v1.7.0 gopkg.in/check.v1 v1.0.0-20180628173108-788fd7840127 // indirect gopkg.in/yaml.v3 v3.0.0-20210107192922-496545a6307b diff --git a/go.sum b/go.sum index cd0dd5e..a268c59 100644 --- a/go.sum +++ b/go.sum @@ -1,10 +1,15 @@ -github.com/davecgh/go-spew v1.1.0 h1:ZDRjVQ15GmhC3fiQ8ni8+OwkZQO4DARzQgrnXU1Liz8= +github.com/DATA-DOG/go-sqlmock v1.5.0 h1:Shsta01QNfFxHCfpW6YH2STWB0MudeXXEWMr20OEh60= +github.com/DATA-DOG/go-sqlmock v1.5.0/go.mod h1:f/Ixk793poVmq4qj/V1dPUg2JEAKC73Q5eFN3EC/SaM= github.com/davecgh/go-spew v1.1.0/go.mod h1:J7Y8YcW2NihsgmVo/mv3lAwl/skON4iLHjSsI+c5H38= +github.com/davecgh/go-spew v1.1.1 h1:vj9j/u1bqnvCEfJOwUhtlOARqs3+rkHYY13jYWTU97c= +github.com/davecgh/go-spew v1.1.1/go.mod h1:J7Y8YcW2NihsgmVo/mv3lAwl/skON4iLHjSsI+c5H38= github.com/kr/pretty v0.2.1 h1:Fmg33tUaq4/8ym9TJN1x7sLJnHVwhP33CNkpYV/7rwI= github.com/kr/pretty v0.2.1/go.mod h1:ipq/a2n7PKx3OHsz4KJII5eveXtPO4qwEXGdVfWzfnI= github.com/kr/pty v1.1.1/go.mod h1:pFQYn66WHrOpPYNljwOMqo10TkYh1fy3cYio2l3bCsQ= github.com/kr/text v0.1.0 h1:45sCR5RtlFHMR4UwH9sdQ5TC8v0qDQCHnXt+kaKSTVE= github.com/kr/text v0.1.0/go.mod h1:4Jbv+DJW3UT/LiOwJeYQe1efqtUx/iVham/4vfdArNI= +github.com/mattn/go-sqlite3 v1.14.6 h1:dNPt6NO46WmLVt2DLNpwczCmdV5boIZ6g/tlDrlRUbg= +github.com/mattn/go-sqlite3 v1.14.6/go.mod h1:NyWgC/yNuGj7Q9rpYnZvas74GogHl5/Z4A/KQRfk6bU= github.com/pmezard/go-difflib v1.0.0 h1:4DBwDE0NGyQoBHbLQYPwSUPoCMWR5BEzIk/f1lZbAQM= github.com/pmezard/go-difflib v1.0.0/go.mod h1:iKH77koFhYxTK1pcRnkKkqfTogsbg7gZNVY4sRDYZ/4= github.com/stretchr/objx v0.1.0/go.mod h1:HFkY916IF+rwdDfMAkV7OtwuqBVzrE8GR6GFx+wExME= diff --git a/pkg/database/database.go b/pkg/database/database.go new file mode 100644 index 0000000..c89a334 --- /dev/null +++ b/pkg/database/database.go @@ -0,0 +1,58 @@ +package database + +import ( + "database/sql" + "time" + + _ "github.com/mattn/go-sqlite3" +) + +// DBEnv is the struct that holds this package together +type DBEnv struct { + db *sql.DB +} + +// InitDB initializes database access and the connection pool +func InitDB(dbType string, dsn string) (*DBEnv, error) { + db, err := sql.Open(dbType, dsn) + if err != nil { + return nil, newInitError(dsn, err) + } + db.SetMaxOpenConns(25) + db.SetMaxIdleConns(25) + db.SetConnMaxLifetime(5 * time.Minute) + + if err := db.Ping(); err != nil { + _ = db.Close() // TODO think about handling this error case? + return nil, newInitError(dsn, err) + } + + return &DBEnv{db: db}, nil +} + +// Migrate performs the migrations of the database to the latest schema_version +func (env *DBEnv) Migrate() error { + var currentVersion int + if err := env.db.QueryRow(`SELECT version FROM schema_version`).Scan(¤tVersion); err != nil { + currentVersion = 0 + } + for version := currentVersion; version < len(migrations); version++ { + newVersion := version + 1 + tx, err := env.db.Begin() + if err != nil { + return newTransactionError("Could not begin transaction", err) + } + if err := migrations[version](tx); err != nil { + tx.Rollback() + return newMigrationError(newVersion, err) + } + if _, err := tx.Exec(`DELETE FROM schema_version; INSERT INTO schema_version (version) VALUES ($1)`, newVersion); err != nil { + tx.Rollback() + return newMigrationError(newVersion, err) + } + if err := tx.Commit(); err != nil { + return newTransactionError("Could not commit transaction", err) + } + } + return nil +} diff --git a/pkg/database/database_test.go b/pkg/database/database_test.go new file mode 100644 index 0000000..aad7953 --- /dev/null +++ b/pkg/database/database_test.go @@ -0,0 +1,143 @@ +package database + +import ( + "database/sql" + "os" + "reflect" + "testing" + + "github.com/DATA-DOG/go-sqlmock" + "github.com/stretchr/testify/assert" + "github.com/stretchr/testify/require" +) + +func TestInitDB(t *testing.T) { + // Test cases + testCases := []struct { + name string + dbType string + dsn string + expectedError interface{} + }{ + {"Invalid dbType", "non-existant", "test", &InitError{}}, + {"Non existant path", "sqlite3", "/non-existant/non-existant", &InitError{}}, + {"Working DB", "sqlite3", "file::memory:?_foreign_keys=on", nil}, + } + for _, tc := range testCases { + t.Run(tc.name, func(t *testing.T) { + db, err := InitDB(tc.dbType, tc.dsn) + if tc.expectedError != nil { + require.Error(t, err) + assert.Equalf(t, reflect.TypeOf(err), reflect.TypeOf(tc.expectedError), "Invalid error type. Got %s but expected %s", reflect.TypeOf(err), reflect.TypeOf(tc.expectedError)) + require.Nil(t, db) + } else { + require.NoError(t, err) + require.NotNil(t, db) + } + }) + } +} + +func TestMigrate(t *testing.T) { + badMigration := []func(tx *sql.Tx) error{ + func(tx *sql.Tx) (err error) { + return newMigrationError(0, nil) + }, + } + noSchemaVersionMigration := []func(tx *sql.Tx) error{ + func(tx *sql.Tx) (err error) { + return nil + }, + } + onlyFirstMigration := []func(tx *sql.Tx) error{migrations[0]} + notFromScratchMigration := []func(tx *sql.Tx) error{ + func(tx *sql.Tx) (err error) { + return &MigrationError{version: 1, err: nil} + }, + func(tx *sql.Tx) (err error) { + return nil + }, + } + os.Remove("testfile_notFromScratch.db") + notFromScratchDB, err := InitDB("sqlite3", "file:testfile_notFromScratch.db?_foreign_keys=on") + if err != nil { + t.Fatalf("Failed to init testfile.db : %+v", err) + } + defer os.Remove("testfile_notFromScratch.db") + migrations = onlyFirstMigration + if err := notFromScratchDB.Migrate(); err != nil { + t.Fatalf("Failed to migrate testfile.db to first schema version : %+v", err) + } + + // Test cases + testCases := []struct { + name string + dsn string + migrs []func(tx *sql.Tx) error + expectedError interface{} + }{ + {"bad migration", "file::memory:?_foreign_keys=on", badMigration, &MigrationError{}}, + {"no schema_version migration", "file::memory:?_foreign_keys=on", noSchemaVersionMigration, &MigrationError{}}, + {"not from scratch", "file:testfile_notFromScratch.db?_foreign_keys=on", notFromScratchMigration, nil}, + {"from scratch", "file::memory:?_foreign_keys=on", allMigrations, nil}, + } + for _, tc := range testCases { + t.Run(tc.name, func(t *testing.T) { + db, err := InitDB("sqlite3", tc.dsn) + require.NoError(t, err) + migrations = tc.migrs + err = db.Migrate() + if tc.expectedError != nil { + require.Error(t, err) + assert.Equalf(t, reflect.TypeOf(err), reflect.TypeOf(tc.expectedError), "Invalid error type. Got %s but expected %s", reflect.TypeOf(err), reflect.TypeOf(tc.expectedError)) + } else { + require.NoError(t, err) + } + }) + } +} + +func TestMigrateWithSQLMock(t *testing.T) { + fakeMigration := []func(tx *sql.Tx) error{ + func(tx *sql.Tx) (err error) { + return nil + }, + } + // Transaction begin error + dbBeginError, _, err := sqlmock.New() + if err != nil { + t.Fatalf("an error '%s' was not expected when opening a stub database connection", err) + } + defer dbBeginError.Close() + // Transaction commit error + dbCommitError, mockCommitError, err := sqlmock.New() + if err != nil { + t.Fatalf("an error '%s' was not expected when opening a stub database connection", err) + } + defer dbCommitError.Close() + mockCommitError.ExpectBegin() + mockCommitError.ExpectExec(`DELETE FROM schema_version`).WillReturnResult(sqlmock.NewResult(1, 1)) + // Test cases + testCases := []struct { + name string + db *DBEnv + migrs []func(tx *sql.Tx) error + expectedError interface{} + }{ + {"begin transaction error", &DBEnv{db: dbBeginError}, fakeMigration, &TransactionError{}}, + {"commit transaction error", &DBEnv{db: dbCommitError}, fakeMigration, &TransactionError{}}, + } + for _, tc := range testCases { + t.Run(tc.name, func(t *testing.T) { + migrations = tc.migrs + err = tc.db.Migrate() + migrations = allMigrations + if tc.expectedError != nil { + require.Error(t, err) + assert.Equalf(t, reflect.TypeOf(err), reflect.TypeOf(tc.expectedError), "Invalid error type. Got %s but expected %s", reflect.TypeOf(err), reflect.TypeOf(tc.expectedError)) + } else { + require.NoError(t, err) + } + }) + } +} diff --git a/pkg/database/error.go b/pkg/database/error.go new file mode 100644 index 0000000..1681dd5 --- /dev/null +++ b/pkg/database/error.go @@ -0,0 +1,57 @@ +package database + +import "fmt" + +// database init error +type InitError struct { + path string + err error +} + +func (e *InitError) Error() string { + return fmt.Sprintf("Failed to open database : %s", e.path) +} +func (e *InitError) Unwrap() error { return e.err } + +func newInitError(path string, err error) error { + return &InitError{ + path: path, + err: err, + } +} + +// database migration error +type MigrationError struct { + version int + err error +} + +func (e *MigrationError) Error() string { + return fmt.Sprintf("Failed to migrate database to version %d : %s", e.version, e.err) +} +func (e *MigrationError) Unwrap() error { return e.err } + +func newMigrationError(version int, err error) error { + return &MigrationError{ + version: version, + err: err, + } +} + +// database transaction error +type TransactionError struct { + msg string + err error +} + +func (e *TransactionError) Error() string { + return fmt.Sprintf("Failed to perform transaction : %s", e.msg) +} +func (e *TransactionError) Unwrap() error { return e.err } + +func newTransactionError(msg string, err error) error { + return &TransactionError{ + msg: msg, + err: err, + } +} diff --git a/pkg/database/error_test.go b/pkg/database/error_test.go new file mode 100644 index 0000000..8031305 --- /dev/null +++ b/pkg/database/error_test.go @@ -0,0 +1,15 @@ +package database + +import "testing" + +func TestErrorsCoverage(t *testing.T) { + initErr := InitError{} + _ = initErr.Error() + _ = initErr.Unwrap() + migrationErr := MigrationError{} + _ = migrationErr.Error() + _ = migrationErr.Unwrap() + transactionErr := TransactionError{} + _ = transactionErr.Error() + _ = transactionErr.Unwrap() +} diff --git a/pkg/database/migrations.go b/pkg/database/migrations.go new file mode 100644 index 0000000..cec3c41 --- /dev/null +++ b/pkg/database/migrations.go @@ -0,0 +1,19 @@ +package database + +import "database/sql" + +// allMigrations is the list of migrations to perform to get an up to date database +// Order is important. Add new migrations at the end of the list. +var allMigrations = []func(tx *sql.Tx) error{ + func(tx *sql.Tx) (err error) { + sql := ` + CREATE TABLE schema_version ( + version INTEGER NOT NULL + );` + _, err = tx.Exec(sql) + return err + }, +} + +// This variable exists so that tests can override it +var migrations = allMigrations -- cgit v1.2.3