refactor: centralize test database setup

4 files changed, 45 insertions, 101 deletions

diff --git a/internal/dbtest/testdb.go b/internal/dbtest/testdb.go
new file mode 100644
index 0000000..3e00e04
--- /dev/null
+++ b/internal/dbtest/testdb.go
@@ -0,0 +1,38 @@
+package dbtest
+
+import (
+	"database/sql"
+	"os"
+	"path/filepath"
+	"runtime"
+	"testing"
+
+	_ "github.com/mattn/go-sqlite3"
+)
+
+func New(t testing.TB) *sql.DB {
+	t.Helper()
+	db, err := sql.Open("sqlite3", ":memory:")
+	if err != nil {
+		t.Fatalf("open test database: %v", err)
+	}
+	db.SetMaxOpenConns(1)
+	t.Cleanup(func() {
+		if err := db.Close(); err != nil {
+			t.Errorf("close test database: %v", err)
+		}
+	})
+	schema, err := os.ReadFile(schemaPath())
+	if err != nil {
+		t.Fatalf("read test database schema: %v", err)
+	}
+	if _, err := db.Exec(string(schema)); err != nil {
+		t.Fatalf("apply test database schema: %v", err)
+	}
+	return db
+}
+
+func schemaPath() string {
+	_, filename, _, _ := runtime.Caller(0)
+	return filepath.Join(filepath.Dir(filename), "..", "..", "db", "schema.sql")
+}
diff --git a/official_climb/official_climb_test.go b/official_climb/official_climb_test.go
index 553c482..848216c 100644
--- a/official_climb/official_climb_test.go
+++ b/official_climb/official_climb_test.go
@@ -1,41 +1,19 @@
 package official_climb_test
 
 import (
-	"database/sql"
 	"testing"
 	"time"
 
 	"github.com/jftuga/geodist"
+	"github.com/martinlehoux/biking_home/internal/dbtest"
 	"github.com/martinlehoux/biking_home/official_climb"
 	"github.com/martinlehoux/biking_home/ride"
-	_ "github.com/mattn/go-sqlite3"
 	"github.com/stretchr/testify/assert"
 	"github.com/stretchr/testify/require"
 )
 
-func newTestDB(t *testing.T) *sql.DB {
-	t.Helper()
-	db, err := sql.Open("sqlite3", ":memory:")
-	require.NoError(t, err)
-	t.Cleanup(func() { require.NoError(t, db.Close()) })
-	_, err = db.Exec(`
-		CREATE TABLE official_climbs (
-			id INTEGER PRIMARY KEY,
-			name TEXT NOT NULL,
-			start_latitude REAL NOT NULL,
-			start_longitude REAL NOT NULL,
-			end_latitude REAL NOT NULL,
-			end_longitude REAL NOT NULL,
-			created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
-			updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
-		)
-	`)
-	require.NoError(t, err)
-	return db
-}
-
 func TestCreateListAndGetOfficialClimb(t *testing.T) {
-	db := newTestDB(t)
+	db := dbtest.New(t)
 	created, err := official_climb.Create(db, official_climb.OfficialClimb{
 		Name:       "Col de Test",
 		StartCoord: geodist.Coord{Lat: 43.1, Lon: 5.1},
@@ -60,7 +38,7 @@ func TestCreateListAndGetOfficialClimb(t *testing.T) {
 }
 
 func TestCreateRejectsInvalidOfficialClimb(t *testing.T) {
-	db := newTestDB(t)
+	db := dbtest.New(t)
 	_, err := official_climb.Create(db, official_climb.OfficialClimb{
 		Name:       " ",
 		StartCoord: geodist.Coord{Lat: 43.1, Lon: 5.1},
diff --git a/rides/rides_test.go b/rides/rides_test.go
index eb705fa..90998cd 100644
--- a/rides/rides_test.go
+++ b/rides/rides_test.go
@@ -7,39 +7,15 @@ import (
 	"testing"
 	"time"
 
+	"github.com/martinlehoux/biking_home/internal/dbtest"
 	"github.com/martinlehoux/biking_home/ride"
 	"github.com/martinlehoux/kagamigo/kcore"
-	_ "github.com/mattn/go-sqlite3"
 	"github.com/stretchr/testify/assert"
 	"github.com/stretchr/testify/require"
 )
 
 func newTestDB(t *testing.T) *sql.DB {
-	t.Helper()
-	db, err := sql.Open("sqlite3", ":memory:")
-	require.NoError(t, err)
-	t.Cleanup(func() { db.Close() })
-	_, err = db.Exec(`
-		create table rides (
-			id integer primary key,
-			external_id text unique not null,
-			gpx_path text not null,
-			name text not null,
-			type text not null,
-			start_date text not null,
-			distance_m real not null,
-			moving_time_s integer not null,
-			elapsed_time_s integer not null,
-			total_elevation_gain_m real not null,
-			average_speed_mps real not null,
-			cotacol_score real,
-			cotacol_algo_version text,
-			created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
-			updated_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
-		)
-	`)
-	require.NoError(t, err)
-	return db
+	return dbtest.New(t)
 }
 
 func sampleRide(t *testing.T) Ride {
diff --git a/web/server_test.go b/web/server_test.go
index e8c3012..9ecda80 100644
--- a/web/server_test.go
+++ b/web/server_test.go
@@ -16,67 +16,19 @@ import (
 
 	"github.com/jftuga/geodist"
 	"github.com/martinlehoux/biking_home/config"
+	"github.com/martinlehoux/biking_home/internal/dbtest"
 	"github.com/martinlehoux/biking_home/mountain_pass"
 	"github.com/martinlehoux/biking_home/official_climb"
 	"github.com/martinlehoux/biking_home/ride"
 	"github.com/martinlehoux/biking_home/rides"
 	"github.com/martinlehoux/biking_home/strava"
-	_ "github.com/mattn/go-sqlite3"
 	"github.com/stretchr/testify/assert"
 	"github.com/stretchr/testify/require"
 )
 
 func newWebTestServer(t *testing.T) (*Server, *sql.DB) {
 	t.Helper()
-	db, err := sql.Open("sqlite3", ":memory:")
-	require.NoError(t, err)
-	t.Cleanup(func() { db.Close() })
-	_, err = db.Exec(`
-		create table rides (
-			id integer primary key,
-			external_id text unique not null,
-			gpx_path text not null,
-			name text not null,
-			type text not null,
-			start_date text not null,
-			distance_m real not null,
-			moving_time_s integer not null,
-			elapsed_time_s integer not null,
-			total_elevation_gain_m real not null,
-			average_speed_mps real not null,
-			cotacol_score real,
-			cotacol_algo_version text,
-			created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
-			updated_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
-		)
-	`)
-	require.NoError(t, err)
-	_, err = db.Exec(`
-		create table mountain_passes (
-			id integer primary key,
-			external_id text unique not null,
-			name text not null,
-			country_code text not null,
-			department_code text not null,
-			elevation integer not null,
-			latitude real,
-			longitude real
-		)
-	`)
-	require.NoError(t, err)
-	_, err = db.Exec(`
-		create table official_climbs (
-			id integer primary key,
-			name text not null,
-			start_latitude real not null,
-			start_longitude real not null,
-			end_latitude real not null,
-			end_longitude real not null,
-			created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
-			updated_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
-		)
-	`)
-	require.NoError(t, err)
+	db := dbtest.New(t)
 	configPath := filepath.Join(t.TempDir(), "config.yaml")
 	appConfig := config.Default()
 	appConfig.Strava.ClientID = "123"