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
159
160
161
162
163
164
165
166
167
168
169
|
package official_climb
import (
"database/sql"
"fmt"
"strings"
"time"
"github.com/martinlehoux/biking_home/climb"
)
// Attempt is one recorded passage of an official climb in a ride, keyed by (rideID, climbID, occurrence); stats are snapshotted so 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 refreshes a ride's attempt rows: fresh matches are written, stale ones dropped, the recorded-at stamp of survivors kept.
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() }()
keys := make([]string, 0, len(attempts))
args := make([]any, 0, 2*len(attempts)+1)
args = append(args, rideID)
for _, attempt := range attempts {
if attempt.RideID != rideID {
return fmt.Errorf("replace ride attempts: attempt references ride %d, expected %d", attempt.RideID, rideID)
}
keys = append(keys, "(?, ?)")
args = append(args, attempt.ClimbID, attempt.Occurrence)
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 (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(ride_id, climb_id, occurrence) DO UPDATE SET
distance_m = excluded.distance_m,
elevation_gain_m = excluded.elevation_gain_m,
slope_percent = excluded.slope_percent,
cotacol = excluded.cotacol,
cotacol_algo_version = excluded.cotacol_algo_version,
started_at = excluded.started_at,
ended_at = excluded.ended_at
`, 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)
}
}
prune := "DELETE FROM ride_official_climb_attempts WHERE ride_id = ?"
if len(keys) > 0 {
prune += " AND (climb_id, occurrence) NOT IN (VALUES " + strings.Join(keys, ", ") + ")"
}
if _, err := tx.Exec(prune, args...); err != nil {
return fmt.Errorf("drop stale ride attempts: %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 snapshots one analyzed climb occurrence; timestamps come from the ride 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,
}
}
|
