aboutsummaryrefslogtreecommitdiff
path: root/ride
diff options
context:
space:
mode:
Diffstat (limited to 'ride')
-rw-r--r--ride/store.go268
-rw-r--r--ride/store_test.go303
2 files changed, 571 insertions, 0 deletions
diff --git a/ride/store.go b/ride/store.go
new file mode 100644
index 0000000..af598e8
--- /dev/null
+++ b/ride/store.go
@@ -0,0 +1,268 @@
+package ride
+
+import (
+ "database/sql"
+ "fmt"
+ "log/slog"
+ "time"
+
+ "github.com/martinlehoux/kagamigo/kcore"
+)
+
+type StoredRide struct {
+ ID int64
+ ExternalID string
+ GPXPath string
+ Name string
+ Type string
+ StartDate time.Time
+ DistanceM float64
+ ExternalMovingTimeS int64
+ movingTimeS *int64
+ movingTimeAlgoVersion string
+ ElapsedTimeS int64
+ TotalElevationGainM float64
+ AverageSpeedMps float64
+ cotacolScore *float64
+ cotacolAlgoVersion string
+ CreatedAt time.Time
+ UpdatedAt time.Time
+}
+
+type SortColumn string
+
+const (
+ SortName SortColumn = "name"
+ SortStartDate SortColumn = "started"
+ SortDistance SortColumn = "distance"
+ SortMovingTime SortColumn = "moving_time"
+ SortSpeed SortColumn = "speed"
+ SortElevation SortColumn = "elevation"
+ SortCotacol SortColumn = "cotacol"
+ SortCotacolKm SortColumn = "cotacol_100km"
+)
+
+const columns = "id, external_id, gpx_path, name, type, start_date, distance_m, external_moving_time_s, moving_time_s, moving_time_algo_version, elapsed_time_s, total_elevation_gain_m, average_speed_mps, cotacol_score, cotacol_algo_version, created_at, updated_at"
+
+func Save(db *sql.DB, r StoredRide) error {
+ var cotacolScore any
+ var cotacolAlgorithmVersion any
+ var movingTimeS any
+ var movingTimeAlgorithmVersion any
+ if r.GPXPath != "" {
+ parsed, err := ParseFile(GPXRideParser{}, r.GPXPath)
+ if err != nil {
+ return fmt.Errorf("compute ride values for %q: %w", r.ExternalID, err)
+ }
+ cotacolScore = Cotacol(parsed)
+ cotacolAlgorithmVersion = CotacolAlgorithmVersion
+ movingTimeS = int64(MovingTime(parsed) / time.Second)
+ movingTimeAlgorithmVersion = MovingTimeAlgorithmVersion
+ }
+ _, err := db.Exec(`
+ INSERT INTO rides (external_id, gpx_path, name, type, start_date, distance_m, external_moving_time_s, moving_time_s, moving_time_algo_version, elapsed_time_s, total_elevation_gain_m, average_speed_mps, cotacol_score, cotacol_algo_version)
+ VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
+ ON CONFLICT(external_id) DO UPDATE SET
+ gpx_path = excluded.gpx_path,
+ name = excluded.name,
+ type = excluded.type,
+ start_date = excluded.start_date,
+ distance_m = excluded.distance_m,
+ external_moving_time_s = excluded.external_moving_time_s,
+ moving_time_s = excluded.moving_time_s,
+ moving_time_algo_version = excluded.moving_time_algo_version,
+ elapsed_time_s = excluded.elapsed_time_s,
+ total_elevation_gain_m = excluded.total_elevation_gain_m,
+ average_speed_mps = excluded.average_speed_mps,
+ cotacol_score = excluded.cotacol_score,
+ cotacol_algo_version = excluded.cotacol_algo_version,
+ updated_at = strftime('%Y-%m-%dT%H:%M:%SZ', 'now')
+ `, r.ExternalID, r.GPXPath, r.Name, r.Type, r.StartDate.UTC().Format(time.RFC3339), r.DistanceM, r.ExternalMovingTimeS, movingTimeS, movingTimeAlgorithmVersion, r.ElapsedTimeS, r.TotalElevationGainM, r.AverageSpeedMps, cotacolScore, cotacolAlgorithmVersion)
+ return err
+}
+
+func Backfill(db *sql.DB) (int, error) {
+ rows, err := db.Query("SELECT " + columns + " FROM rides ORDER BY id")
+ if err != nil {
+ return 0, err
+ }
+ var pending []StoredRide
+ for rows.Next() {
+ item, err := scanRide(rows)
+ if err != nil {
+ rows.Close()
+ return 0, err
+ }
+ pending = append(pending, item)
+ }
+ if err := rows.Err(); err != nil {
+ rows.Close()
+ return 0, err
+ }
+ if err := rows.Close(); err != nil {
+ return 0, err
+ }
+ backfilled := 0
+ for _, item := range pending {
+ if err := Save(db, item); err != nil {
+ slog.Warn("Failed to backfill ride values", "ride", item.ExternalID, "file", item.GPXPath, "error", err)
+ continue
+ }
+ backfilled++
+ }
+ return backfilled, nil
+}
+
+func List(db *sql.DB) ([]StoredRide, error) {
+ return ListSorted(db, SortStartDate, true)
+}
+
+func ListSorted(db *sql.DB, column SortColumn, descending bool) ([]StoredRide, error) {
+ expression, ok := sortExpressions[column]
+ if !ok {
+ return nil, fmt.Errorf("invalid ride sort column %q", column)
+ }
+ direction := "ASC"
+ if descending {
+ direction = "DESC"
+ }
+ query := "SELECT " + columns + " FROM rides ORDER BY "
+ args := []any{}
+ if column == SortMovingTime {
+ args = append(args, MovingTimeAlgorithmVersion)
+ } else if isComputedSort(column) {
+ query += "(cotacol_score IS NULL OR cotacol_algo_version IS NULL OR cotacol_algo_version <> ? OR distance_m <= 0) ASC, "
+ args = append(args, CotacolAlgorithmVersion)
+ }
+ query += expression + " " + direction + ", id DESC"
+ rows, err := db.Query(query, args...)
+ if err != nil {
+ return nil, err
+ }
+ defer rows.Close()
+ var rides []StoredRide
+ for rows.Next() {
+ ride, err := scanRide(rows)
+ if err != nil {
+ return nil, err
+ }
+ rides = append(rides, ride)
+ }
+ return rides, rows.Err()
+}
+
+var sortExpressions = map[SortColumn]string{
+ SortName: "name COLLATE NOCASE",
+ SortStartDate: "start_date",
+ SortDistance: "distance_m",
+ SortMovingTime: "CASE WHEN moving_time_algo_version = ? THEN COALESCE(moving_time_s, external_moving_time_s) ELSE external_moving_time_s END",
+ SortSpeed: "average_speed_mps",
+ SortElevation: "total_elevation_gain_m",
+ SortCotacol: "cotacol_score",
+ SortCotacolKm: "cotacol_score * 100000.0 / NULLIF(distance_m, 0)",
+}
+
+func isComputedSort(column SortColumn) bool {
+ return column == SortCotacol || column == SortCotacolKm
+}
+
+func GetByExternalID(db *sql.DB, externalID string) (StoredRide, bool, error) {
+ row := db.QueryRow("SELECT "+columns+" FROM rides WHERE external_id = ?", externalID)
+ ride, err := scanRide(row)
+ if err == sql.ErrNoRows {
+ return StoredRide{}, false, nil
+ }
+ if err != nil {
+ return StoredRide{}, false, err
+ }
+ return ride, true, nil
+}
+
+func GetByID(db *sql.DB, id int64) (StoredRide, bool, error) {
+ row := db.QueryRow("SELECT "+columns+" FROM rides WHERE id = ?", id)
+ ride, err := scanRide(row)
+ if err == sql.ErrNoRows {
+ return StoredRide{}, false, nil
+ }
+ if err != nil {
+ return StoredRide{}, false, err
+ }
+ return ride, true, nil
+}
+
+type scanner interface {
+ Scan(dest ...any) error
+}
+
+func scanRide(s scanner) (StoredRide, error) {
+ var (
+ ride StoredRide
+ startDate string
+ movingTimeS sql.NullInt64
+ movingTimeAlgoVer sql.NullString
+ cotacolScore sql.NullFloat64
+ cotacolAlgoVersion sql.NullString
+ createdAt string
+ updatedAt string
+ )
+ err := s.Scan(&ride.ID, &ride.ExternalID, &ride.GPXPath, &ride.Name, &ride.Type, &startDate, &ride.DistanceM, &ride.ExternalMovingTimeS, &movingTimeS, &movingTimeAlgoVer, &ride.ElapsedTimeS, &ride.TotalElevationGainM, &ride.AverageSpeedMps, &cotacolScore, &cotacolAlgoVersion, &createdAt, &updatedAt)
+ if err != nil {
+ return StoredRide{}, err
+ }
+ if movingTimeS.Valid {
+ ride.movingTimeS = &movingTimeS.Int64
+ }
+ if movingTimeAlgoVer.Valid {
+ ride.movingTimeAlgoVersion = movingTimeAlgoVer.String
+ }
+ if cotacolScore.Valid {
+ ride.cotacolScore = &cotacolScore.Float64
+ }
+ if cotacolAlgoVersion.Valid {
+ ride.cotacolAlgoVersion = cotacolAlgoVersion.String
+ }
+ ride.StartDate, err = time.Parse(time.RFC3339, startDate)
+ if err != nil {
+ return StoredRide{}, kcore.Wrap(err, "invalid start_date in rides row")
+ }
+ ride.CreatedAt, err = time.Parse(time.RFC3339, createdAt)
+ if err != nil {
+ return StoredRide{}, kcore.Wrap(err, "invalid created_at in rides row")
+ }
+ ride.UpdatedAt, err = time.Parse(time.RFC3339, updatedAt)
+ if err != nil {
+ return StoredRide{}, kcore.Wrap(err, "invalid updated_at in rides row")
+ }
+ return ride, nil
+}
+
+func (r StoredRide) CotacolAlgorithmVersion() string {
+ return r.cotacolAlgoVersion
+}
+
+func (r StoredRide) MovingTimeS() (int64, bool) {
+ if r.movingTimeS == nil {
+ return 0, false
+ }
+ return *r.movingTimeS, true
+}
+
+func (r StoredRide) MovingTimeAlgorithmVersion() string {
+ return r.movingTimeAlgoVersion
+}
+
+// EffectiveMovingTimeS is the canonical moving time when the stored value
+// matches the current algorithm version, falling back to Strava's value.
+func (r StoredRide) EffectiveMovingTimeS() int64 {
+ if moving, ok := r.MovingTimeS(); ok && r.movingTimeAlgoVersion == MovingTimeAlgorithmVersion {
+ return moving
+ }
+ return r.ExternalMovingTimeS
+}
+
+func (r StoredRide) CotacolScore() (float64, bool) {
+ if r.cotacolScore == nil {
+ return 0, false
+ }
+ return *r.cotacolScore, true
+}
diff --git a/ride/store_test.go b/ride/store_test.go
new file mode 100644
index 0000000..877f4e5
--- /dev/null
+++ b/ride/store_test.go
@@ -0,0 +1,303 @@
+package ride
+
+import (
+ "database/sql"
+ "os"
+ "path/filepath"
+ "testing"
+ "time"
+
+ "github.com/martinlehoux/biking_home/internal/dbtest"
+ "github.com/martinlehoux/kagamigo/kcore"
+ "github.com/stretchr/testify/assert"
+ "github.com/stretchr/testify/require"
+)
+
+func newTestDB(t *testing.T) *sql.DB {
+ return dbtest.New(t)
+}
+
+func sampleRide(t *testing.T) StoredRide {
+ t.Helper()
+ gpxPath := filepath.Join(t.TempDir(), "ride.gpx")
+ require.NoError(t, os.WriteFile(gpxPath, []byte(testGPX), 0o600))
+ return StoredRide{
+ ExternalID: "strava:1234",
+ GPXPath: gpxPath,
+ Name: "Morning Ride",
+ Type: "Ride",
+ StartDate: time.Date(2026, 8, 1, 7, 30, 0, 0, time.UTC),
+ DistanceM: 42_195,
+ ExternalMovingTimeS: 7_200,
+ ElapsedTimeS: 7_800,
+ TotalElevationGainM: 850,
+ AverageSpeedMps: 5.86,
+ }
+}
+
+const testGPX = `<?xml version="1.0"?><gpx xmlns="http://www.topografix.com/GPX/1/1" version="1.1"><trk><trkseg><trkpt lat="43.0" lon="5.0"><ele>100</ele><time>2026-08-01T07:30:00Z</time></trkpt><trkpt lat="43.001" lon="5.001"><ele>200</ele><time>2026-08-01T07:31:00Z</time></trkpt></trkseg></trk></gpx>`
+
+func TestUpsertAndGet(t *testing.T) {
+ db := newTestDB(t)
+ sample := sampleRide(t)
+ err := Save(db, sample)
+ require.NoError(t, err)
+
+ got, ok, err := GetByExternalID(db, "strava:1234")
+ require.NoError(t, err)
+ require.True(t, ok)
+ assert.Equal(t, "Morning Ride", got.Name)
+ assert.Equal(t, sample.StartDate, got.StartDate)
+ assert.Equal(t, 42_195.0, got.DistanceM)
+ assert.Equal(t, sample.GPXPath, got.GPXPath)
+ score, found := got.CotacolScore()
+ require.True(t, found)
+ assert.Greater(t, score, 0.0)
+ assert.Equal(t, CotacolAlgorithmVersion, got.CotacolAlgorithmVersion())
+
+ _, ok, err = GetByExternalID(db, "strava:9999")
+ require.NoError(t, err)
+ assert.False(t, ok)
+}
+
+func TestGetByID(t *testing.T) {
+ db := newTestDB(t)
+ sample := sampleRide(t)
+ require.NoError(t, Save(db, sample))
+ stored, found, err := GetByExternalID(db, sample.ExternalID)
+ require.NoError(t, err)
+ require.True(t, found)
+
+ got, found, err := GetByID(db, stored.ID)
+ require.NoError(t, err)
+ require.True(t, found)
+ assert.Equal(t, sample.ExternalID, got.ExternalID)
+ assert.Equal(t, sample.Name, got.Name)
+
+ _, found, err = GetByID(db, 999999)
+ require.NoError(t, err)
+ assert.False(t, found)
+}
+
+func TestSaveComputesCotacol(t *testing.T) {
+ db := newTestDB(t)
+ require.NoError(t, Save(db, sampleRide(t)))
+
+ got, ok, err := GetByExternalID(db, "strava:1234")
+ require.NoError(t, err)
+ require.True(t, ok)
+ score, found := got.CotacolScore()
+ require.True(t, found)
+ assert.Greater(t, score, 0.0)
+ assert.Equal(t, CotacolAlgorithmVersion, got.CotacolAlgorithmVersion())
+}
+
+func TestSaveComputesMovingTimeAndPreservesExternal(t *testing.T) {
+ db := newTestDB(t)
+ require.NoError(t, Save(db, sampleRide(t)))
+
+ got, ok, err := GetByExternalID(db, "strava:1234")
+ require.NoError(t, err)
+ require.True(t, ok)
+ moving, found := got.MovingTimeS()
+ require.True(t, found)
+ assert.Equal(t, int64(60), moving)
+ assert.Equal(t, MovingTimeAlgorithmVersion, got.MovingTimeAlgorithmVersion())
+ assert.Equal(t, int64(7_200), got.ExternalMovingTimeS)
+}
+
+func TestSaveAllowsRideWithoutGPX(t *testing.T) {
+ db := newTestDB(t)
+ indoor := sampleRide(t)
+ indoor.ExternalID = "strava:14701658670"
+ indoor.GPXPath = ""
+
+ require.NoError(t, Save(db, indoor))
+
+ got, found, err := GetByExternalID(db, indoor.ExternalID)
+ require.NoError(t, err)
+ require.True(t, found)
+ assert.Empty(t, got.GPXPath)
+ _, ready := got.CotacolScore()
+ assert.False(t, ready)
+ assert.Empty(t, got.CotacolAlgorithmVersion())
+ _, ready = got.MovingTimeS()
+ assert.False(t, ready)
+ assert.Empty(t, got.MovingTimeAlgorithmVersion())
+}
+
+func TestEffectiveMovingTimePrefersCanonical(t *testing.T) {
+ db := newTestDB(t)
+ require.NoError(t, Save(db, sampleRide(t)))
+ got, ok, err := GetByExternalID(db, "strava:1234")
+ require.NoError(t, err)
+ require.True(t, ok)
+ assert.Equal(t, int64(60), got.EffectiveMovingTimeS())
+
+ _, err = db.Exec("UPDATE rides SET moving_time_algo_version = 'old' WHERE external_id = ?", got.ExternalID)
+ require.NoError(t, err)
+ got, ok, err = GetByExternalID(db, "strava:1234")
+ require.NoError(t, err)
+ require.True(t, ok)
+ assert.Equal(t, int64(7_200), got.EffectiveMovingTimeS())
+}
+
+func TestListSortedByMovingTimeUsesCanonicalWithExternalFallback(t *testing.T) {
+ db := newTestDB(t)
+ first := sampleRide(t)
+ first.ExternalID = "strava:550e8400-e29b-41d4-a716-446655440000"
+ second := sampleRide(t)
+ second.ExternalID = "strava:6ba7b810-9dad-41d1-80b4-00c04fd430c8"
+ require.NoError(t, Save(db, first))
+ require.NoError(t, Save(db, second))
+ _, err := db.Exec("UPDATE rides SET external_moving_time_s = 1000, moving_time_s = 500 WHERE external_id = ?", first.ExternalID)
+ require.NoError(t, err)
+ _, err = db.Exec("UPDATE rides SET external_moving_time_s = 300, moving_time_s = 500, moving_time_algo_version = 'old' WHERE external_id = ?", second.ExternalID)
+ require.NoError(t, err)
+
+ items, err := ListSorted(db, SortMovingTime, false)
+ require.NoError(t, err)
+ assert.Equal(t, second.ExternalID, items[0].ExternalID)
+ assert.Equal(t, first.ExternalID, items[1].ExternalID)
+}
+
+func TestListSortedBySpeed(t *testing.T) {
+ db := newTestDB(t)
+ slow := sampleRide(t)
+ slow.ExternalID = "strava:550e8400-e29b-41d4-a716-446655440000"
+ slow.AverageSpeedMps = 4.2
+ fast := sampleRide(t)
+ fast.ExternalID = "strava:6ba7b810-9dad-41d1-80b4-00c04fd430c8"
+ fast.AverageSpeedMps = 9.8
+ require.NoError(t, Save(db, slow))
+ require.NoError(t, Save(db, fast))
+
+ items, err := ListSorted(db, SortSpeed, false)
+ require.NoError(t, err)
+ assert.Equal(t, slow.ExternalID, items[0].ExternalID)
+ assert.Equal(t, fast.ExternalID, items[1].ExternalID)
+}
+
+func TestBackfill(t *testing.T) {
+ db := newTestDB(t)
+ require.NoError(t, Save(db, sampleRide(t)))
+
+ count, err := Backfill(db)
+ require.NoError(t, err)
+ assert.Equal(t, 1, count)
+ got, ok, err := GetByExternalID(db, "strava:1234")
+ require.NoError(t, err)
+ require.True(t, ok)
+ _, found := got.CotacolScore()
+ assert.True(t, found)
+ assert.Equal(t, CotacolAlgorithmVersion, got.CotacolAlgorithmVersion())
+
+ count, err = Backfill(db)
+ require.NoError(t, err)
+ assert.Equal(t, 1, count)
+}
+
+func TestBackfillSkipsInvalidRide(t *testing.T) {
+ db := newTestDB(t)
+ _, err := db.Exec(`
+ INSERT INTO rides (external_id, gpx_path, name, type, start_date, distance_m, external_moving_time_s, elapsed_time_s, total_elevation_gain_m, average_speed_mps)
+ VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
+ `, "strava:550e8400-e29b-41d4-a716-446655440000", "missing.gpx", "Broken Ride", "Ride", "2026-08-01T07:00:00Z", 20_000, 0, 0, 0, 0)
+ require.NoError(t, err)
+
+ count, err := Backfill(db)
+ require.NoError(t, err)
+ assert.Zero(t, count)
+
+ got, ok, err := GetByExternalID(db, "strava:550e8400-e29b-41d4-a716-446655440000")
+ require.NoError(t, err)
+ require.True(t, ok)
+ _, found := got.CotacolScore()
+ assert.False(t, found)
+}
+
+func TestUpsertUpdatesExisting(t *testing.T) {
+ db := newTestDB(t)
+ ride := sampleRide(t)
+ require.NoError(t, Save(db, ride))
+ ride.Name = "Renamed Ride"
+ ride.DistanceM = 50_000
+ require.NoError(t, Save(db, ride))
+
+ got, ok, err := GetByExternalID(db, "strava:1234")
+ require.NoError(t, err)
+ require.True(t, ok)
+ assert.Equal(t, "Renamed Ride", got.Name)
+ assert.Equal(t, 50_000.0, got.DistanceM)
+}
+
+func TestList(t *testing.T) {
+ db := newTestDB(t)
+ first := sampleRide(t)
+ first.StartDate = time.Date(2026, 7, 1, 7, 0, 0, 0, time.UTC)
+ second := sampleRide(t)
+ second.ExternalID = "strava:5678"
+ second.Name = "Evening Ride"
+ require.NoError(t, Save(db, first))
+ require.NoError(t, Save(db, second))
+
+ rides, err := List(db)
+ require.NoError(t, err)
+ require.Len(t, rides, 2)
+ assert.Equal(t, "strava:5678", rides[0].ExternalID)
+ assert.Equal(t, "strava:1234", rides[1].ExternalID)
+ kcore.Assert(len(rides) == 2, "two rides")
+}
+
+func TestListSortedByCotacolKeepsStaleLast(t *testing.T) {
+ db := newTestDB(t)
+ first := sampleRide(t)
+ first.ExternalID = "strava:550e8400-e29b-41d4-a716-446655440000"
+ first.DistanceM = 20_000
+ second := sampleRide(t)
+ second.ExternalID = "strava:6ba7b810-9dad-41d1-80b4-00c04fd430c8"
+ second.DistanceM = 40_000
+ require.NoError(t, Save(db, first))
+ require.NoError(t, Save(db, second))
+ _, err := db.Exec(`
+ UPDATE rides
+ SET cotacol_score = CASE external_id WHEN ? THEN 4 ELSE 6 END,
+ cotacol_algo_version = ?
+ `, first.ExternalID, CotacolAlgorithmVersion)
+ require.NoError(t, err)
+
+ items, err := ListSorted(db, SortCotacol, false)
+ require.NoError(t, err)
+ assert.Equal(t, first.ExternalID, items[0].ExternalID)
+ assert.Equal(t, second.ExternalID, items[1].ExternalID)
+
+ _, err = db.Exec("UPDATE rides SET cotacol_algo_version = ? WHERE external_id = ?", "old", first.ExternalID)
+ require.NoError(t, err)
+ items, err = ListSorted(db, SortCotacol, false)
+ require.NoError(t, err)
+ assert.Equal(t, second.ExternalID, items[0].ExternalID)
+ assert.Equal(t, first.ExternalID, items[1].ExternalID)
+}
+
+func TestListSortedByCotacolPer100Km(t *testing.T) {
+ db := newTestDB(t)
+ first := sampleRide(t)
+ first.ExternalID = "strava:550e8400-e29b-41d4-a716-446655440000"
+ first.DistanceM = 20_000
+ second := sampleRide(t)
+ second.ExternalID = "strava:6ba7b810-9dad-41d1-80b4-00c04fd430c8"
+ second.DistanceM = 40_000
+ require.NoError(t, Save(db, first))
+ require.NoError(t, Save(db, second))
+ _, err := db.Exec(`
+ UPDATE rides
+ SET cotacol_score = CASE external_id WHEN ? THEN 4 ELSE 6 END,
+ cotacol_algo_version = ?
+ `, first.ExternalID, CotacolAlgorithmVersion)
+ require.NoError(t, err)
+
+ items, err := ListSorted(db, SortCotacolKm, false)
+ require.NoError(t, err)
+ assert.Equal(t, second.ExternalID, items[0].ExternalID)
+ assert.Equal(t, first.ExternalID, items[1].ExternalID)
+}
generated by cgit v1.3 (git 2.53.0) at 2026-10-18 13:31:06 +0000