aboutsummaryrefslogtreecommitdiff
path: root/ride/store.go
diff options
context:
space:
mode:
Diffstat (limited to 'ride/store.go')
-rw-r--r--ride/store.go268
1 files changed, 268 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
+}
generated by cgit v1.3 (git 2.53.0) at 2026-10-18 19:54:30 +0000