Christophe Vila
47dbf4e446
None of these three tables gained their own user_id column -- they're always accessed through a specific activity, so ownership is checked via a join/subquery against activities.user_id instead.
83 lines
2.9 KiB
Go
83 lines
2.9 KiB
Go
package store
|
|
|
|
import (
|
|
"context"
|
|
"database/sql"
|
|
"fmt"
|
|
)
|
|
|
|
// Sample is one ~1-second telemetry reading for an activity.
|
|
type Sample struct {
|
|
ElapsedSeconds float64
|
|
TimestampMs int64
|
|
HeartRate *float64
|
|
SpeedMps *float64
|
|
DistanceM *float64
|
|
ElevationM *float64
|
|
}
|
|
|
|
// ReplaceActivitySamples deletes any existing samples for activityID (owned
|
|
// by userID) and bulk-inserts the given set, so re-syncing an activity's
|
|
// details is idempotent.
|
|
func (db *DB) ReplaceActivitySamples(ctx context.Context, userID, activityID int64, samples []Sample) error {
|
|
var exists int
|
|
err := db.QueryRowContext(ctx, `SELECT 1 FROM activities WHERE id = ? AND user_id = ?`, activityID, userID).Scan(&exists)
|
|
if err == sql.ErrNoRows {
|
|
return fmt.Errorf("replace activity samples: activity %d not found for user %d", activityID, userID)
|
|
}
|
|
if err != nil {
|
|
return fmt.Errorf("replace activity samples for activity %d (user %d): %w", activityID, userID, err)
|
|
}
|
|
|
|
tx, err := db.BeginTx(ctx, nil)
|
|
if err != nil {
|
|
return fmt.Errorf("begin replace samples tx: %w", err)
|
|
}
|
|
defer tx.Rollback()
|
|
|
|
if _, err := tx.ExecContext(ctx, `DELETE FROM activity_samples WHERE activity_id = ?`, activityID); err != nil {
|
|
return fmt.Errorf("delete existing samples for activity %d: %w", activityID, err)
|
|
}
|
|
|
|
stmt, err := tx.PrepareContext(ctx, `
|
|
INSERT INTO activity_samples (activity_id, elapsed_seconds, timestamp_ms, heart_rate, speed_mps, distance_m, elevation_m)
|
|
VALUES (?,?,?,?,?,?,?)`)
|
|
if err != nil {
|
|
return fmt.Errorf("prepare insert sample: %w", err)
|
|
}
|
|
defer stmt.Close()
|
|
|
|
for _, s := range samples {
|
|
if _, err := stmt.ExecContext(ctx, activityID, s.ElapsedSeconds, s.TimestampMs, s.HeartRate, s.SpeedMps, s.DistanceM, s.ElevationM); err != nil {
|
|
return fmt.Errorf("insert sample for activity %d: %w", activityID, err)
|
|
}
|
|
}
|
|
return tx.Commit()
|
|
}
|
|
|
|
// SamplesForActivity returns all samples for an activity owned by userID,
|
|
// ordered by elapsed_seconds.
|
|
func (db *DB) SamplesForActivity(ctx context.Context, userID, activityID int64) ([]Sample, error) {
|
|
rows, err := db.QueryContext(ctx, `
|
|
SELECT activity_samples.elapsed_seconds, activity_samples.timestamp_ms, activity_samples.heart_rate,
|
|
activity_samples.speed_mps, activity_samples.distance_m, activity_samples.elevation_m
|
|
FROM activity_samples
|
|
JOIN activities ON activities.id = activity_samples.activity_id
|
|
WHERE activity_samples.activity_id = ? AND activities.user_id = ?
|
|
ORDER BY activity_samples.elapsed_seconds`, activityID, userID)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("samples for activity %d (user %d): %w", activityID, userID, err)
|
|
}
|
|
defer rows.Close()
|
|
|
|
samples := []Sample{}
|
|
for rows.Next() {
|
|
var s Sample
|
|
if err := rows.Scan(&s.ElapsedSeconds, &s.TimestampMs, &s.HeartRate, &s.SpeedMps, &s.DistanceM, &s.ElevationM); err != nil {
|
|
return nil, fmt.Errorf("scan sample row: %w", err)
|
|
}
|
|
samples = append(samples, s)
|
|
}
|
|
return samples, rows.Err()
|
|
}
|