1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
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,
}
}
|
