From 31ce2a1d5ff5746249cd4de96762f5740aff907a Mon Sep 17 00:00:00 2001 From: Martin Kagamino Lehoux Date: Sat, 8 Aug 2026 22:29:49 +0200 Subject: perf: Load ride values from SQLite --- rides/rides.go | 27 +++++++++++++++++--- rides/rides_test.go | 72 +++++++++++++++++++++++++++++++++++++++++++++++++++++ 2 files changed, 95 insertions(+), 4 deletions(-) (limited to 'rides') diff --git a/rides/rides.go b/rides/rides.go index 619fefa..fb6185f 100644 --- a/rides/rides.go +++ b/rides/rides.go @@ -3,6 +3,7 @@ package rides import ( "database/sql" "fmt" + "log/slog" "time" "github.com/martinlehoux/biking_home/ride" @@ -35,6 +36,8 @@ const ( SortDistance SortColumn = "distance" SortMovingTime SortColumn = "moving_time" SortElevation SortColumn = "elevation" + SortCotacol SortColumn = "cotacol" + 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" @@ -86,12 +89,15 @@ func Backfill(db *sql.DB) (int, error) { if err := rows.Close(); err != nil { return 0, err } - for i, item := range pending { + backfilled := 0 + for _, item := range pending { if err := Save(db, item); err != nil { - return i, fmt.Errorf("backfill ride %q: %w", item.ExternalID, err) + slog.Warn("Failed to backfill ride values", "ride", item.ExternalID, "file", item.GPXPath, "error", err) + continue } + backfilled++ } - return len(pending), nil + return backfilled, nil } func List(db *sql.DB) ([]Ride, error) { @@ -107,7 +113,14 @@ func ListSorted(db *sql.DB, column SortColumn, descending bool) ([]Ride, error) if descending { direction = "DESC" } - rows, err := db.Query("SELECT " + columns + " FROM rides ORDER BY " + expression + " " + direction + ", id DESC") + query := "SELECT " + columns + " FROM rides ORDER BY " + args := []any{} + 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) + } + query += expression + " " + direction + ", id DESC" + rows, err := db.Query(query, args...) if err != nil { return nil, err } @@ -129,6 +142,12 @@ var sortExpressions = map[SortColumn]string{ SortDistance: "distance_m", SortMovingTime: "moving_time_s", 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) (Ride, bool, error) { diff --git a/rides/rides_test.go b/rides/rides_test.go index cbe5670..7f10ab5 100644 --- a/rides/rides_test.go +++ b/rides/rides_test.go @@ -117,6 +117,25 @@ func TestBackfill(t *testing.T) { 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, 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) @@ -149,3 +168,56 @@ func TestList(t *testing.T) { 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, ride.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, ride.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) +} -- cgit v1.2.3