diff options
Diffstat (limited to 'official_climb/attempt.go')
| -rw-r--r-- | official_climb/attempt.go | 158 |
1 files changed, 158 insertions, 0 deletions
diff --git a/official_climb/attempt.go b/official_climb/attempt.go new file mode 100644 index 0000000..43778f0 --- /dev/null +++ b/official_climb/attempt.go @@ -0,0 +1,158 @@ +package official_climb + +import ( + "database/sql" + "fmt" + "time" + + "github.com/martinlehoux/biking_home/climb" +) + +// Attempt is one recorded passage of an official climb during one ride, +// identified by (rideID, climbID, occurrence). Scalar stats are snapshotted at +// recording time so climb listings never re-parse ride files. +type Attempt struct { + RideID int64 + ClimbID int64 + Occurrence int + DistanceM float64 + ElevationGainM float64 + SlopePercent float64 + Cotacol float64 + CotacolAlgoVersion string + StartedAt time.Time + EndedAt time.Time + CreatedAt time.Time +} + +const attemptColumns = "a.ride_id, a.climb_id, a.occurrence, a.distance_m, a.elevation_gain_m, a.slope_percent, a.cotacol, a.cotacol_algo_version, a.started_at, a.ended_at, a.created_at" + +// AttemptsForClimb returns every recorded attempt of one official climb, +// oldest ride first. +func AttemptsForClimb(db *sql.DB, climbID int64) ([]Attempt, error) { + return listAttempts(db, "climb_id = ?", climbID, "r.start_date, a.ride_id, a.occurrence") +} + +// AttemptsForRide returns every recorded attempt within one ride, in route +// order. +func AttemptsForRide(db *sql.DB, rideID int64) ([]Attempt, error) { + return listAttempts(db, "ride_id = ?", rideID, "a.occurrence") +} + +func listAttempts(db *sql.DB, where string, arg any, order string) ([]Attempt, error) { + rows, err := db.Query(` + SELECT `+attemptColumns+` + FROM ride_official_climb_attempts a + JOIN rides r ON r.id = a.ride_id + WHERE `+where+` + ORDER BY `+order, arg) + if err != nil { + return nil, fmt.Errorf("list official climb attempts: %w", err) + } + defer rows.Close() + attempts := make([]Attempt, 0) + for rows.Next() { + attempt, err := scanAttempt(rows) + if err != nil { + return nil, err + } + attempts = append(attempts, attempt) + } + if err := rows.Err(); err != nil { + return nil, fmt.Errorf("iterate official climb attempts: %w", err) + } + return attempts, nil +} + +// ReplaceRideAttempts atomically rewrites every attempt row of one ride, +// dropping stale matches and inserting the freshly analyzed ones. +func ReplaceRideAttempts(db *sql.DB, rideID int64, attempts []Attempt) error { + tx, err := db.Begin() + if err != nil { + return fmt.Errorf("replace ride attempts: %w", err) + } + defer func() { _ = tx.Rollback() }() + if _, err := tx.Exec("DELETE FROM ride_official_climb_attempts WHERE ride_id = ?", rideID); err != nil { + return fmt.Errorf("delete ride attempts: %w", err) + } + for _, attempt := range attempts { + if attempt.RideID != rideID { + return fmt.Errorf("replace ride attempts: attempt references ride %d, expected %d", attempt.RideID, rideID) + } + if _, err := tx.Exec(` + INSERT INTO ride_official_climb_attempts (ride_id, climb_id, occurrence, distance_m, elevation_gain_m, slope_percent, cotacol, cotacol_algo_version, started_at, ended_at) + VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) + `, attempt.RideID, attempt.ClimbID, attempt.Occurrence, attempt.DistanceM, attempt.ElevationGainM, attempt.SlopePercent, attempt.Cotacol, attempt.CotacolAlgoVersion, + attempt.StartedAt.UTC().Format(time.RFC3339), attempt.EndedAt.UTC().Format(time.RFC3339)); err != nil { + return fmt.Errorf("insert ride attempt: %w", err) + } + } + if err := tx.Commit(); err != nil { + return fmt.Errorf("commit ride attempts: %w", err) + } + return nil +} + +type attemptScanner interface { + Scan(dest ...any) error +} + +func scanAttempt(s attemptScanner) (Attempt, error) { + var ( + attempt Attempt + startedAt, endedAt string + createdAt sql.NullString + ) + if err := s.Scan(&attempt.RideID, &attempt.ClimbID, &attempt.Occurrence, &attempt.DistanceM, &attempt.ElevationGainM, &attempt.SlopePercent, &attempt.Cotacol, &attempt.CotacolAlgoVersion, &startedAt, &endedAt, &createdAt); err != nil { + return Attempt{}, err + } + var err error + if attempt.StartedAt, err = time.Parse(time.RFC3339, startedAt); err != nil { + return Attempt{}, fmt.Errorf("invalid ride_official_climb_attempts.started_at: %w", err) + } + if attempt.EndedAt, err = time.Parse(time.RFC3339, endedAt); err != nil { + return Attempt{}, fmt.Errorf("invalid ride_official_climb_attempts.ended_at: %w", err) + } + if createdAt.Valid { + if attempt.CreatedAt, err = time.Parse(time.RFC3339, createdAt.String); err != nil { + return Attempt{}, fmt.Errorf("invalid ride_official_climb_attempts.created_at: %w", err) + } + } + return attempt, nil +} + +// AttemptStats carries the analyzed values snapshotted into an attempt row. +type AttemptStats struct { + OfficialClimbID int64 + DistanceM float64 + ElevationGainM float64 + SlopePercent float64 + Cotacol float64 + StartIndex int + EndIndex int +} + +// AttemptFromStats builds the persisted snapshot of one analyzed climb +// occurrence. Timestamps come from the ride's recorded points bounding the +// climb interval. +func AttemptFromStats(rideID int64, occurrence int, stats AttemptStats, timestamps []time.Time) Attempt { + startedAt, endedAt := time.Time{}, time.Time{} + if stats.StartIndex < len(timestamps) { + startedAt = timestamps[stats.StartIndex] + } + if stats.EndIndex < len(timestamps) { + endedAt = timestamps[stats.EndIndex] + } + return Attempt{ + RideID: rideID, + ClimbID: stats.OfficialClimbID, + Occurrence: occurrence, + DistanceM: stats.DistanceM, + ElevationGainM: stats.ElevationGainM, + SlopePercent: stats.SlopePercent, + Cotacol: stats.Cotacol, + CotacolAlgoVersion: climb.CotacolAlgorithmVersion, + StartedAt: startedAt, + EndedAt: endedAt, + } +} |