aboutsummaryrefslogtreecommitdiff
path: root/rides
diff options
context:
space:
mode:
Diffstat (limited to 'rides')
-rw-r--r--rides/rides.go269
-rw-r--r--rides/rides_test.go304
2 files changed, 0 insertions, 573 deletions
diff --git a/rides/rides.go b/rides/rides.go
deleted file mode 100644
index 94fbc64..0000000
--- a/rides/rides.go
+++ /dev/null
@@ -1,269 +0,0 @@
-package rides
-
-import (
- "database/sql"
- "fmt"
- "log/slog"
- "time"
-
- "github.com/martinlehoux/biking_home/ride"
- "github.com/martinlehoux/kagamigo/kcore"
-)
-
-type Ride 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 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 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, 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 []Ride
- 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) ([]Ride, error) {
- return ListSorted(db, SortStartDate, true)
-}
-
-func ListSorted(db *sql.DB, column SortColumn, descending bool) ([]Ride, 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, 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)
- }
- query += expression + " " + direction + ", id DESC"
- rows, err := db.Query(query, args...)
- if err != nil {
- return nil, err
- }
- defer rows.Close()
- var rides []Ride
- 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) (Ride, bool, error) {
- row := db.QueryRow("SELECT "+columns+" FROM rides WHERE external_id = ?", externalID)
- ride, err := scanRide(row)
- if err == sql.ErrNoRows {
- return Ride{}, false, nil
- }
- if err != nil {
- return Ride{}, false, err
- }
- return ride, true, nil
-}
-
-func GetByID(db *sql.DB, id int64) (Ride, bool, error) {
- row := db.QueryRow("SELECT "+columns+" FROM rides WHERE id = ?", id)
- ride, err := scanRide(row)
- if err == sql.ErrNoRows {
- return Ride{}, false, nil
- }
- if err != nil {
- return Ride{}, false, err
- }
- return ride, true, nil
-}
-
-type scanner interface {
- Scan(dest ...any) error
-}
-
-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.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
- }
- if cotacolAlgoVersion.Valid {
- ride.cotacolAlgoVersion = cotacolAlgoVersion.String
- }
- ride.StartDate, err = time.Parse(time.RFC3339, startDate)
- if err != nil {
- return Ride{}, kcore.Wrap(err, "invalid start_date in rides row")
- }
- ride.CreatedAt, err = time.Parse(time.RFC3339, createdAt)
- if err != nil {
- return Ride{}, kcore.Wrap(err, "invalid created_at in rides row")
- }
- ride.UpdatedAt, err = time.Parse(time.RFC3339, updatedAt)
- if err != nil {
- return Ride{}, kcore.Wrap(err, "invalid updated_at in rides row")
- }
- return ride, nil
-}
-
-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
- }
- return *r.cotacolScore, true
-}
diff --git a/rides/rides_test.go b/rides/rides_test.go
deleted file mode 100644
index ea87172..0000000
--- a/rides/rides_test.go
+++ /dev/null
@@ -1,304 +0,0 @@
-package rides
-
-import (
- "database/sql"
- "os"
- "path/filepath"
- "testing"
- "time"
-
- "github.com/martinlehoux/biking_home/internal/dbtest"
- "github.com/martinlehoux/biking_home/ride"
- "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) Ride {
- t.Helper()
- gpxPath := filepath.Join(t.TempDir(), "ride.gpx")
- require.NoError(t, os.WriteFile(gpxPath, []byte(testGPX), 0o600))
- return Ride{
- 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, ride.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, 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)
- 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, ride.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, 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)
-}
generated by cgit v1.3 (git 2.53.0) at 2026-10-18 22:53:18 +0000