mirror of
https://github.com/IS1DI/x0gp.git
synced 2026-07-16 20:47:55 +00:00
422 lines
14 KiB
Go
422 lines
14 KiB
Go
package postgres
|
|
|
|
import (
|
|
"context"
|
|
"errors"
|
|
"fmt"
|
|
|
|
"github.com/jackc/pgx/v5"
|
|
"github.com/jackc/pgx/v5/pgconn"
|
|
"github.com/jackc/pgx/v5/pgxpool"
|
|
|
|
"github.com/x0gp/server/internal/catalog"
|
|
)
|
|
|
|
// PgStore implements catalog.Store backed by Postgres + TimescaleDB.
|
|
type PgStore struct {
|
|
pool *pgxpool.Pool
|
|
}
|
|
|
|
// NewPgStore wraps an open pool.
|
|
func NewPgStore(pool *pgxpool.Pool) *PgStore {
|
|
return &PgStore{pool: pool}
|
|
}
|
|
|
|
// Compile-time check.
|
|
var _ catalog.Store = (*PgStore)(nil)
|
|
|
|
// ---------------------------------------------------------------------------
|
|
// Load
|
|
// ---------------------------------------------------------------------------
|
|
|
|
// Load reads the full catalog into memory. Used at startup.
|
|
func (s *PgStore) Load(ctx context.Context) ([]catalog.TrackMeta, []catalog.CarMeta, error) {
|
|
tracks, err := s.loadTracks(ctx)
|
|
if err != nil {
|
|
return nil, nil, fmt.Errorf("load tracks: %w", err)
|
|
}
|
|
cars, err := s.loadCars(ctx)
|
|
if err != nil {
|
|
return nil, nil, fmt.Errorf("load cars: %w", err)
|
|
}
|
|
return tracks, cars, nil
|
|
}
|
|
|
|
func (s *PgStore) loadTracks(ctx context.Context) ([]catalog.TrackMeta, error) {
|
|
rows, err := s.pool.Query(ctx, `
|
|
SELECT id, name, description, author_id, visibility, scale,
|
|
length_m, width_m, lane_width_m, surface,
|
|
best_lap_ms, best_lap_holder,
|
|
bounds_min_x, bounds_min_y, bounds_max_x, bounds_max_y,
|
|
created_ms, updated_ms
|
|
FROM tracks
|
|
ORDER BY id`)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
|
|
out := make([]catalog.TrackMeta, 0)
|
|
for rows.Next() {
|
|
var t catalog.TrackMeta
|
|
if err := rows.Scan(
|
|
&t.ID, &t.Name, &t.Description, &t.AuthorID, &t.Visibility, &t.Scale,
|
|
&t.LengthM, &t.WidthM, &t.LaneWidthM, &t.Surface,
|
|
&t.BestLapMs, &t.BestLapHolder,
|
|
&t.Bounds.MinX, &t.Bounds.MinY, &t.Bounds.MaxX, &t.Bounds.MaxY,
|
|
&t.CreatedMs, &t.UpdatedMs,
|
|
); err != nil {
|
|
return nil, err
|
|
}
|
|
// Derive bounds length/width if not stored explicitly.
|
|
t.Bounds.LengthM = t.Bounds.MaxX - t.Bounds.MinX
|
|
t.Bounds.WidthM = t.Bounds.MaxY - t.Bounds.MinY
|
|
out = append(out, t)
|
|
}
|
|
if err := rows.Err(); err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
// Fetch waypoints and tags per track.
|
|
for i := range out {
|
|
wps, err := s.loadWaypoints(ctx, out[i].ID)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("waypoints %s: %w", out[i].ID, err)
|
|
}
|
|
out[i].Centerline = wps
|
|
tags, err := s.loadTags(ctx, out[i].ID)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("tags %s: %w", out[i].ID, err)
|
|
}
|
|
out[i].Tags = tags
|
|
}
|
|
return out, nil
|
|
}
|
|
|
|
func (s *PgStore) loadWaypoints(ctx context.Context, trackID string) ([]catalog.Waypoint, error) {
|
|
rows, err := s.pool.Query(ctx, `
|
|
SELECT x, y, heading_rad, speed_ms, curvature
|
|
FROM track_waypoints
|
|
WHERE track_id = $1
|
|
ORDER BY seq`, trackID)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
out := make([]catalog.Waypoint, 0)
|
|
for rows.Next() {
|
|
var w catalog.Waypoint
|
|
if err := rows.Scan(&w.X, &w.Y, &w.HeadingRad, &w.SpeedMs, &w.Curvature); err != nil {
|
|
return nil, err
|
|
}
|
|
out = append(out, w)
|
|
}
|
|
return out, rows.Err()
|
|
}
|
|
|
|
func (s *PgStore) loadTags(ctx context.Context, trackID string) ([]string, error) {
|
|
rows, err := s.pool.Query(ctx,
|
|
`SELECT tag FROM track_tags WHERE track_id = $1 ORDER BY tag`, trackID)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
out := make([]string, 0)
|
|
for rows.Next() {
|
|
var tag string
|
|
if err := rows.Scan(&tag); err != nil {
|
|
return nil, err
|
|
}
|
|
out = append(out, tag)
|
|
}
|
|
return out, rows.Err()
|
|
}
|
|
|
|
func (s *PgStore) loadCars(ctx context.Context) ([]catalog.CarMeta, error) {
|
|
rows, err := s.pool.Query(ctx, `
|
|
SELECT id, name, owner_id, visibility, scale,
|
|
length_mm, width_mm, height_mm, weight_g,
|
|
chassis_model, chassis_material, chassis_printed,
|
|
chassis_wheelbase_mm, chassis_track_mm,
|
|
motor_kind, motor_class, motor_kv, motor_power_w,
|
|
battery_voltage_v, battery_capacity_mah, battery_cells, battery_chemistry,
|
|
drive, top_speed_ms, color_hex, avatar_url, active,
|
|
total_distance_m, total_races, total_laps, best_lap_ms,
|
|
created_ms, updated_ms, device_id
|
|
FROM cars
|
|
ORDER BY id`)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
out := make([]catalog.CarMeta, 0)
|
|
for rows.Next() {
|
|
var c catalog.CarMeta
|
|
if err := rows.Scan(
|
|
&c.ID, &c.Name, &c.OwnerID, &c.Visibility, &c.Scale,
|
|
&c.LengthMm, &c.WidthMm, &c.HeightMm, &c.WeightG,
|
|
&c.Chassis.Model, &c.Chassis.Material, &c.Chassis.Printed,
|
|
&c.Chassis.WheelbaseMm, &c.Chassis.TrackMm,
|
|
&c.Motor.Kind, &c.Motor.Class, &c.Motor.KV, &c.Motor.PowerW,
|
|
&c.Battery.VoltageV, &c.Battery.CapacityMah, &c.Battery.Cells, &c.Battery.Chemistry,
|
|
&c.Drive, &c.TopSpeedMs, &c.ColorHex, &c.AvatarURL, &c.Active,
|
|
&c.TotalDistanceM, &c.TotalRaces, &c.TotalLaps, &c.BestLapMs,
|
|
&c.CreatedMs, &c.UpdatedMs, &c.DeviceID,
|
|
); err != nil {
|
|
return nil, err
|
|
}
|
|
out = append(out, c)
|
|
}
|
|
return out, rows.Err()
|
|
}
|
|
|
|
// ---------------------------------------------------------------------------
|
|
// Mutators
|
|
// ---------------------------------------------------------------------------
|
|
|
|
// UpsertTrack writes the track, its waypoints, and its tags in one transaction.
|
|
func (s *PgStore) UpsertTrack(ctx context.Context, t catalog.TrackMeta) error {
|
|
tx, err := s.pool.BeginTx(ctx, pgx.TxOptions{})
|
|
if err != nil {
|
|
return err
|
|
}
|
|
defer tx.Rollback(ctx)
|
|
|
|
if _, err := tx.Exec(ctx, `
|
|
INSERT INTO tracks (id, name, description, author_id, visibility, scale,
|
|
length_m, width_m, lane_width_m, surface,
|
|
best_lap_ms, best_lap_holder,
|
|
bounds_min_x, bounds_min_y, bounds_max_x, bounds_max_y,
|
|
created_ms, updated_ms)
|
|
VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18)
|
|
ON CONFLICT (id) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
description = EXCLUDED.description,
|
|
author_id = EXCLUDED.author_id,
|
|
visibility = EXCLUDED.visibility,
|
|
scale = EXCLUDED.scale,
|
|
length_m = EXCLUDED.length_m,
|
|
width_m = EXCLUDED.width_m,
|
|
lane_width_m = EXCLUDED.lane_width_m,
|
|
surface = EXCLUDED.surface,
|
|
best_lap_ms = EXCLUDED.best_lap_ms,
|
|
best_lap_holder = EXCLUDED.best_lap_holder,
|
|
bounds_min_x = EXCLUDED.bounds_min_x,
|
|
bounds_min_y = EXCLUDED.bounds_min_y,
|
|
bounds_max_x = EXCLUDED.bounds_max_x,
|
|
bounds_max_y = EXCLUDED.bounds_max_y,
|
|
updated_ms = EXCLUDED.updated_ms`,
|
|
t.ID, t.Name, t.Description, t.AuthorID, string(t.Visibility), t.Scale,
|
|
t.LengthM, t.WidthM, t.LaneWidthM, string(t.Surface),
|
|
t.BestLapMs, t.BestLapHolder,
|
|
t.Bounds.MinX, t.Bounds.MinY, t.Bounds.MaxX, t.Bounds.MaxY,
|
|
t.CreatedMs, t.UpdatedMs,
|
|
); err != nil {
|
|
return err
|
|
}
|
|
|
|
if _, err := tx.Exec(ctx, `DELETE FROM track_waypoints WHERE track_id = $1`, t.ID); err != nil {
|
|
return err
|
|
}
|
|
for i, w := range t.Centerline {
|
|
if _, err := tx.Exec(ctx, `
|
|
INSERT INTO track_waypoints (track_id, seq, x, y, heading_rad, speed_ms, curvature)
|
|
VALUES ($1, $2, $3, $4, $5, $6, $7)`,
|
|
t.ID, i, w.X, w.Y, w.HeadingRad, w.SpeedMs, w.Curvature,
|
|
); err != nil {
|
|
return err
|
|
}
|
|
}
|
|
|
|
if _, err := tx.Exec(ctx, `DELETE FROM track_tags WHERE track_id = $1`, t.ID); err != nil {
|
|
return err
|
|
}
|
|
for _, tag := range t.Tags {
|
|
if _, err := tx.Exec(ctx,
|
|
`INSERT INTO track_tags (track_id, tag) VALUES ($1, $2)`,
|
|
t.ID, tag,
|
|
); err != nil {
|
|
return err
|
|
}
|
|
}
|
|
|
|
return tx.Commit(ctx)
|
|
}
|
|
|
|
// DeleteTrack removes a track and all child rows (cascade on FK).
|
|
func (s *PgStore) DeleteTrack(ctx context.Context, id string) error {
|
|
_, err := s.pool.Exec(ctx, `DELETE FROM tracks WHERE id = $1`, id)
|
|
return err
|
|
}
|
|
|
|
// UpsertCar writes a car (insert or update).
|
|
func (s *PgStore) UpsertCar(ctx context.Context, c catalog.CarMeta) error {
|
|
_, err := s.pool.Exec(ctx, `
|
|
INSERT INTO cars (id, name, owner_id, visibility, scale,
|
|
length_mm, width_mm, height_mm, weight_g,
|
|
chassis_model, chassis_material, chassis_printed,
|
|
chassis_wheelbase_mm, chassis_track_mm,
|
|
motor_kind, motor_class, motor_kv, motor_power_w,
|
|
battery_voltage_v, battery_capacity_mah, battery_cells, battery_chemistry,
|
|
drive, top_speed_ms, color_hex, avatar_url, active,
|
|
total_distance_m, total_races, total_laps, best_lap_ms,
|
|
created_ms, updated_ms, device_id)
|
|
VALUES ($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)
|
|
ON CONFLICT (id) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
owner_id = EXCLUDED.owner_id,
|
|
visibility = EXCLUDED.visibility,
|
|
scale = EXCLUDED.scale,
|
|
length_mm = EXCLUDED.length_mm,
|
|
width_mm = EXCLUDED.width_mm,
|
|
height_mm = EXCLUDED.height_mm,
|
|
weight_g = EXCLUDED.weight_g,
|
|
chassis_model = EXCLUDED.chassis_model,
|
|
chassis_material = EXCLUDED.chassis_material,
|
|
chassis_printed = EXCLUDED.chassis_printed,
|
|
chassis_wheelbase_mm= EXCLUDED.chassis_wheelbase_mm,
|
|
chassis_track_mm = EXCLUDED.chassis_track_mm,
|
|
motor_kind = EXCLUDED.motor_kind,
|
|
motor_class = EXCLUDED.motor_class,
|
|
motor_kv = EXCLUDED.motor_kv,
|
|
motor_power_w = EXCLUDED.motor_power_w,
|
|
battery_voltage_v = EXCLUDED.battery_voltage_v,
|
|
battery_capacity_mah= EXCLUDED.battery_capacity_mah,
|
|
battery_cells = EXCLUDED.battery_cells,
|
|
battery_chemistry = EXCLUDED.battery_chemistry,
|
|
drive = EXCLUDED.drive,
|
|
top_speed_ms = EXCLUDED.top_speed_ms,
|
|
color_hex = EXCLUDED.color_hex,
|
|
avatar_url = EXCLUDED.avatar_url,
|
|
active = EXCLUDED.active,
|
|
total_distance_m = EXCLUDED.total_distance_m,
|
|
total_races = EXCLUDED.total_races,
|
|
total_laps = EXCLUDED.total_laps,
|
|
best_lap_ms = EXCLUDED.best_lap_ms,
|
|
updated_ms = EXCLUDED.updated_ms,
|
|
device_id = EXCLUDED.device_id`,
|
|
c.ID, c.Name, c.OwnerID, string(c.Visibility), c.Scale,
|
|
c.LengthMm, c.WidthMm, c.HeightMm, c.WeightG,
|
|
c.Chassis.Model, c.Chassis.Material, c.Chassis.Printed,
|
|
c.Chassis.WheelbaseMm, c.Chassis.TrackMm,
|
|
c.Motor.Kind, c.Motor.Class, c.Motor.KV, c.Motor.PowerW,
|
|
c.Battery.VoltageV, c.Battery.CapacityMah, c.Battery.Cells, c.Battery.Chemistry,
|
|
string(c.Drive), c.TopSpeedMs, c.ColorHex, c.AvatarURL, c.Active,
|
|
c.TotalDistanceM, c.TotalRaces, c.TotalLaps, c.BestLapMs,
|
|
c.CreatedMs, c.UpdatedMs, c.DeviceID,
|
|
)
|
|
return err
|
|
}
|
|
|
|
// DeleteCar removes a car by id.
|
|
func (s *PgStore) DeleteCar(ctx context.Context, id string) error {
|
|
_, err := s.pool.Exec(ctx, `DELETE FROM cars WHERE id = $1`, id)
|
|
return err
|
|
}
|
|
|
|
// UpdateCarStats applies a partial mutation to the stats columns. We
|
|
// first read the row, run the callback, then write the mutated fields
|
|
// back. This mirrors the in-memory implementation used by tests.
|
|
func (s *PgStore) UpdateCarStats(ctx context.Context, id string, fn func(*catalog.CarMeta)) error {
|
|
tx, err := s.pool.BeginTx(ctx, pgx.TxOptions{})
|
|
if err != nil {
|
|
return err
|
|
}
|
|
defer tx.Rollback(ctx)
|
|
|
|
var c catalog.CarMeta
|
|
err = tx.QueryRow(ctx, `
|
|
SELECT id, name, owner_id, visibility, scale,
|
|
length_mm, width_mm, height_mm, weight_g,
|
|
chassis_model, chassis_material, chassis_printed,
|
|
chassis_wheelbase_mm, chassis_track_mm,
|
|
motor_kind, motor_class, motor_kv, motor_power_w,
|
|
battery_voltage_v, battery_capacity_mah, battery_cells, battery_chemistry,
|
|
drive, top_speed_ms, color_hex, avatar_url, active,
|
|
total_distance_m, total_races, total_laps, best_lap_ms,
|
|
created_ms, updated_ms, device_id
|
|
FROM cars WHERE id = $1 FOR UPDATE`, id,
|
|
).Scan(
|
|
&c.ID, &c.Name, &c.OwnerID, &c.Visibility, &c.Scale,
|
|
&c.LengthMm, &c.WidthMm, &c.HeightMm, &c.WeightG,
|
|
&c.Chassis.Model, &c.Chassis.Material, &c.Chassis.Printed,
|
|
&c.Chassis.WheelbaseMm, &c.Chassis.TrackMm,
|
|
&c.Motor.Kind, &c.Motor.Class, &c.Motor.KV, &c.Motor.PowerW,
|
|
&c.Battery.VoltageV, &c.Battery.CapacityMah, &c.Battery.Cells, &c.Battery.Chemistry,
|
|
&c.Drive, &c.TopSpeedMs, &c.ColorHex, &c.AvatarURL, &c.Active,
|
|
&c.TotalDistanceM, &c.TotalRaces, &c.TotalLaps, &c.BestLapMs,
|
|
&c.CreatedMs, &c.UpdatedMs, &c.DeviceID,
|
|
)
|
|
if err != nil {
|
|
if errors.Is(err, pgx.ErrNoRows) {
|
|
return catalog.ErrNotFound
|
|
}
|
|
var pgErr *pgconn.PgError
|
|
if errors.As(err, &pgErr) {
|
|
return err
|
|
}
|
|
return err
|
|
}
|
|
fn(&c)
|
|
if _, err := tx.Exec(ctx, `
|
|
UPDATE cars SET
|
|
total_distance_m = $2,
|
|
total_races = $3,
|
|
total_laps = $4,
|
|
best_lap_ms = $5,
|
|
updated_ms = $6
|
|
WHERE id = $1`,
|
|
c.ID, c.TotalDistanceM, c.TotalRaces, c.TotalLaps, c.BestLapMs, c.UpdatedMs,
|
|
); err != nil {
|
|
return err
|
|
}
|
|
return tx.Commit(ctx)
|
|
}
|
|
|
|
// GetTrackCalendar returns all track calendar entries ordered by start_at.
|
|
func (s *PgStore) GetTrackCalendar(ctx context.Context) ([]catalog.TrackCalendarEntry, error) {
|
|
query := `SELECT id, track_id, start_at, end_at FROM track_calendar ORDER BY start_at ASC`
|
|
rows, err := s.pool.Query(ctx, query)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
|
|
var entries []catalog.TrackCalendarEntry
|
|
for rows.Next() {
|
|
var entry catalog.TrackCalendarEntry
|
|
if err := rows.Scan(&entry.ID, &entry.TrackID, &entry.StartAt, &entry.EndAt); err != nil {
|
|
return nil, err
|
|
}
|
|
entries = append(entries, entry)
|
|
}
|
|
return entries, nil
|
|
}
|
|
|
|
// CreateTrackCalendar inserts a new calendar entry.
|
|
func (s *PgStore) CreateTrackCalendar(ctx context.Context, entry catalog.TrackCalendarEntry) (catalog.TrackCalendarEntry, error) {
|
|
query := `
|
|
INSERT INTO track_calendar (track_id, start_at, end_at)
|
|
VALUES ($1, $2, $3)
|
|
RETURNING id, track_id, start_at, end_at`
|
|
|
|
row := s.pool.QueryRow(ctx, query, entry.TrackID, entry.StartAt, entry.EndAt)
|
|
var res catalog.TrackCalendarEntry
|
|
if err := row.Scan(&res.ID, &res.TrackID, &res.StartAt, &res.EndAt); err != nil {
|
|
return catalog.TrackCalendarEntry{}, err
|
|
}
|
|
return res, nil
|
|
}
|
|
|
|
// DeleteTrackCalendar deletes a calendar entry by ID.
|
|
func (s *PgStore) DeleteTrackCalendar(ctx context.Context, id int) error {
|
|
command := `DELETE FROM track_calendar WHERE id = $1`
|
|
res, err := s.pool.Exec(ctx, command, id)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
if res.RowsAffected() == 0 {
|
|
return catalog.ErrNotFound
|
|
}
|
|
return nil
|
|
} |