4 files changed, 154 insertions, 0 deletions
diff --git a/pkg/database/migrations.go b/pkg/database/migrations.go
index 6a3d923..e977960 100644
--- a/pkg/database/migrations.go
+++ b/pkg/database/migrations.go
@@ -23,6 +23,10 @@ var allMigrations = []func(tx *sql.Tx) error{
 				user_id INTEGER NOT NULL,
 				created_at DATE DEFAULT (datetime('now')),
 				FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
+			);
+			CREATE TABLE train_stops (
+				id TEXT PRIMARY KEY,
+				name TEXT NOT NULL UNIQUE
 			);`
 		_, err = tx.Exec(sql)
 		return err
diff --git a/pkg/database/train_stop.go b/pkg/database/train_stop.go
new file mode 100644
index 0000000..27fb910
--- /dev/null
+++ b/pkg/database/train_stop.go
@@ -0,0 +1,36 @@
+package database
+
+import "git.adyxax.org/adyxax/trains/pkg/model"
+
+func (env *DBEnv) ReplaceAndImportTrainStops(trainStops []model.TrainStop) error {
+	pre_query := `DELETE FROM train_stops;`
+	query := `
+		INSERT INTO train_stops
+			(id, name)
+		VALUES
+			($1, $2);`
+	tx, err := env.db.Begin()
+	if err != nil {
+		return newTransactionError("Could not Begin()", err)
+	}
+	_, err = tx.Exec(pre_query)
+	if err != nil {
+		tx.Rollback()
+		return newQueryError("Could not run database query: most likely the schema is corrupted", err)
+	}
+	for i := 0; i < len(trainStops); i++ {
+		_, err = tx.Exec(
+			query,
+			trainStops[i].Id,
+			trainStops[i].Name,
+		)
+		if err != nil {
+			tx.Rollback()
+			return newQueryError("Could not run database query: ", err)
+		}
+	}
+	if err := tx.Commit(); err != nil {
+		return newTransactionError("Could not commit transaction", err)
+	}
+	return nil
+}
diff --git a/pkg/database/train_stop_test.go b/pkg/database/train_stop_test.go
new file mode 100644
index 0000000..9ee9c03
--- /dev/null
+++ b/pkg/database/train_stop_test.go
@@ -0,0 +1,108 @@
+package database
+
+import (
+	"reflect"
+	"testing"
+
+	"git.adyxax.org/adyxax/trains/pkg/model"
+	"github.com/DATA-DOG/go-sqlmock"
+	"github.com/stretchr/testify/assert"
+	"github.com/stretchr/testify/require"
+)
+
+func TestReplaceAndImportTrainStops(t *testing.T) {
+	// test db setup
+	db, err := InitDB("sqlite3", "file::memory:?_foreign_keys=on")
+	require.NoError(t, err)
+	err = db.Migrate()
+	require.NoError(t, err)
+	// datasets
+	data1 := []model.TrainStop{
+		model.TrainStop{Id: "first", Name: "firstName"},
+		model.TrainStop{Id: "second", Name: "secondName"},
+	}
+	data2 := []model.TrainStop{
+		model.TrainStop{Id: "first", Name: "firstTest"},
+		model.TrainStop{Id: "secondTest", Name: "secondTest"},
+		model.TrainStop{Id: "thirdTest", Name: "thirdTest"},
+	}
+	testCases := []struct {
+		name          string
+		input         []model.TrainStop
+		expectedError interface{}
+	}{
+		{"Normal insert", data1, nil},
+		{"Normal insert overwrite", data2, nil},
+	}
+	for _, tc := range testCases {
+		t.Run(tc.name, func(t *testing.T) {
+			err := db.ReplaceAndImportTrainStops(tc.input)
+			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 TestReplaceAndImportTrainStopsWithSQLMock(t *testing.T) {
+	// datasets
+	data1 := []model.TrainStop{
+		model.TrainStop{Id: "first", Name: "firstName"},
+		model.TrainStop{Id: "second", Name: "secondName"},
+	}
+	// 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()
+	// Query error cannot delete from
+	dbCannotDeleteFrom, mockCannotDeleteFrom, err := sqlmock.New()
+	if err != nil {
+		t.Fatalf("an error '%s' was not expected when opening a stub database connection", err)
+	}
+	defer dbCannotDeleteFrom.Close()
+	mockCannotDeleteFrom.ExpectBegin()
+	// Transaction commit error
+	dbCannotInsertError, mockCannotInsertError, err := sqlmock.New()
+	if err != nil {
+		t.Fatalf("an error '%s' was not expected when opening a stub database connection", err)
+	}
+	defer dbCannotInsertError.Close()
+	mockCannotInsertError.ExpectBegin()
+	mockCannotInsertError.ExpectExec(`DELETE FROM`)
+	// 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`)
+	mockCommitError.ExpectExec(`INSERT INTO`)
+	// Test cases
+	testCases := []struct {
+		name          string
+		db            *DBEnv
+		expectedError interface{}
+	}{
+		{"begin transaction error", &DBEnv{db: dbBeginError}, &TransactionError{}},
+		{"query error cannot delete from", &DBEnv{db: dbCannotDeleteFrom}, &QueryError{}},
+		{"query error cannot insert into", &DBEnv{db: dbCannotInsertError}, &QueryError{}},
+		// TODO FIX THIS TEST {"commit transaction error", &DBEnv{db: dbCommitError}, &TransactionError{}},
+	}
+	for _, tc := range testCases {
+		t.Run(tc.name, func(t *testing.T) {
+			err := tc.db.ReplaceAndImportTrainStops(data1)
+			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/model/train_stop.go b/pkg/model/train_stop.go
new file mode 100644
index 0000000..e5b1f6e
--- /dev/null
+++ b/pkg/model/train_stop.go
@@ -0,0 +1,6 @@
+package model
+
+type TrainStop struct {
+	Id   string
+	Name string
+}