From 9da52d7355f5a77f0adc9a658619b3bfef03ba3c Mon Sep 17 00:00:00 2001 From: Martin Kagamino Lehoux Date: Fri, 21 Aug 2026 06:56:12 +0200 Subject: feat(ride): persist canonical moving time, preserve Strava reference --- rides/rides.go | 84 ++++++++++++++++++++++++++++++++++++++--------------- rides/rides_test.go | 58 ++++++++++++++++++++++++++++++++++-- 2 files changed, 116 insertions(+), 26 deletions(-) (limited to 'rides') diff --git a/rides/rides.go b/rides/rides.go index f68952c..b0ce5fb 100644 --- a/rides/rides.go +++ b/rides/rides.go @@ -11,21 +11,23 @@ import ( ) type Ride struct { - ID int64 - ExternalID string - GPXPath string - Name string - Type string - StartDate time.Time - DistanceM float64 - MovingTimeS int64 - ElapsedTimeS int64 - TotalElevationGainM float64 - AverageSpeedMps float64 - cotacolScore *float64 - cotacolAlgoVersion string - CreatedAt time.Time - UpdatedAt time.Time + 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 @@ -40,36 +42,42 @@ const ( SortCotacolKm SortColumn = "cotacol_100km" ) -const columns = "id, external_id, gpx_path, name, type, start_date, distance_m, moving_time_s, elapsed_time_s, total_elevation_gain_m, average_speed_mps, cotacol_score, cotacol_algo_version, created_at, updated_at" +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 Ride) error { var cotacolScore any var cotacolAlgorithmVersion any + var movingTimeS any + var movingTimeAlgorithmVersion any if r.GPXPath != "" { parsed, err := ride.ParseFile(ride.GPXRideParser{}, r.GPXPath) if err != nil { - return fmt.Errorf("compute Cotacol for ride %q: %w", r.ExternalID, err) + return fmt.Errorf("compute ride values for %q: %w", r.ExternalID, err) } cotacolScore = ride.Cotacol(parsed) cotacolAlgorithmVersion = ride.CotacolAlgorithmVersion + movingTimeS = int64(ride.MovingTime(parsed) / time.Second) + movingTimeAlgorithmVersion = ride.MovingTimeAlgorithmVersion } _, err := db.Exec(` - INSERT INTO rides (external_id, gpx_path, name, type, start_date, distance_m, moving_time_s, elapsed_time_s, total_elevation_gain_m, average_speed_mps, cotacol_score, cotacol_algo_version) - VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) + 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.MovingTimeS, r.ElapsedTimeS, r.TotalElevationGainM, r.AverageSpeedMps, cotacolScore, cotacolAlgorithmVersion) + `, 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 } @@ -120,7 +128,9 @@ func ListSorted(db *sql.DB, column SortColumn, descending bool) ([]Ride, error) } query := "SELECT " + columns + " FROM rides ORDER BY " args := []any{} - if isComputedSort(column) { + if column == SortMovingTime { + args = append(args, ride.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, ride.CotacolAlgorithmVersion) } @@ -145,7 +155,7 @@ var sortExpressions = map[SortColumn]string{ SortName: "name COLLATE NOCASE", SortStartDate: "start_date", SortDistance: "distance_m", - SortMovingTime: "moving_time_s", + SortMovingTime: "CASE WHEN moving_time_algo_version = ? THEN COALESCE(moving_time_s, external_moving_time_s) ELSE external_moving_time_s END", SortElevation: "total_elevation_gain_m", SortCotacol: "cotacol_score", SortCotacolKm: "cotacol_score * 100000.0 / NULLIF(distance_m, 0)", @@ -187,15 +197,23 @@ func scanRide(s scanner) (Ride, error) { var ( ride Ride 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.MovingTimeS, &ride.ElapsedTimeS, &ride.TotalElevationGainM, &ride.AverageSpeedMps, &cotacolScore, &cotacolAlgoVersion, &createdAt, &updatedAt) + 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 Ride{}, err } + if movingTimeS.Valid { + ride.movingTimeS = &movingTimeS.Int64 + } + if movingTimeAlgoVer.Valid { + ride.movingTimeAlgoVersion = movingTimeAlgoVer.String + } if cotacolScore.Valid { ride.cotacolScore = &cotacolScore.Float64 } @@ -221,6 +239,26 @@ func (r Ride) CotacolAlgorithmVersion() string { return r.cotacolAlgoVersion } +func (r Ride) MovingTimeS() (int64, bool) { + if r.movingTimeS == nil { + return 0, false + } + return *r.movingTimeS, true +} + +func (r Ride) 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 Ride) EffectiveMovingTimeS() int64 { + if moving, ok := r.MovingTimeS(); ok && r.movingTimeAlgoVersion == ride.MovingTimeAlgorithmVersion { + return moving + } + return r.ExternalMovingTimeS +} + func (r Ride) CotacolScore() (float64, bool) { if r.cotacolScore == nil { return 0, false diff --git a/rides/rides_test.go b/rides/rides_test.go index 90998cd..fc8ff0d 100644 --- a/rides/rides_test.go +++ b/rides/rides_test.go @@ -29,14 +29,14 @@ func sampleRide(t *testing.T) Ride { Type: "Ride", StartDate: time.Date(2026, 8, 1, 7, 30, 0, 0, time.UTC), DistanceM: 42_195, - MovingTimeS: 7_200, + ExternalMovingTimeS: 7_200, ElapsedTimeS: 7_800, TotalElevationGainM: 850, AverageSpeedMps: 5.86, } } -const testGPX = `100200` +const testGPX = `100200` func TestUpsertAndGet(t *testing.T) { db := newTestDB(t) @@ -93,6 +93,20 @@ func TestSaveComputesCotacol(t *testing.T) { assert.Equal(t, ride.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, ride.MovingTimeAlgorithmVersion, got.MovingTimeAlgorithmVersion()) + assert.Equal(t, int64(7_200), got.ExternalMovingTimeS) +} + func TestSaveAllowsRideWithoutGPX(t *testing.T) { db := newTestDB(t) indoor := sampleRide(t) @@ -108,6 +122,44 @@ func TestSaveAllowsRideWithoutGPX(t *testing.T) { _, 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 TestBackfill(t *testing.T) { @@ -132,7 +184,7 @@ func TestBackfill(t *testing.T) { func TestBackfillSkipsInvalidRide(t *testing.T) { db := newTestDB(t) _, err := db.Exec(` - INSERT INTO rides (external_id, gpx_path, name, type, start_date, distance_m, moving_time_s, elapsed_time_s, total_elevation_gain_m, average_speed_mps) + 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) -- cgit v1.3