37 lines
1.9 KiB
MySQL
37 lines
1.9 KiB
MySQL
|
-- ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
|
|||
|
|
-- Migration 04 — Bug Fix: distance unit correction + column rename
|
|||
|
|
-- ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
|
|||
|
|
-- BUG-02: tracksolid.trips.distance_m was storing millimetres not metres.
|
|||
|
|
--
|
|||
|
|
-- Root cause: ingestion code applied `km * 1000` but the API already returns
|
|||
|
|
-- values in km, producing mm. Confirmed by cross-checking stored values against
|
|||
|
|
-- avg_speed_kmh × driving_time_s — e.g. 4,203,000 stored for a 4.203 km trip.
|
|||
|
|
--
|
|||
|
|
-- Fix applied here:
|
|||
|
|
-- 1. Divide all existing rows by 1,000,000 to convert mm → km.
|
|||
|
|
-- 2. Rename the column to distance_km to eliminate future ambiguity.
|
|||
|
|
--
|
|||
|
|
-- Corresponding code fix: removed * 1000 from poll_trips() in
|
|||
|
|
-- ingest_movement_rev.py and push_trip_report() in webhook_receiver_rev.py.
|
|||
|
|
-- Both now store the raw API value directly as km.
|
|||
|
|
--
|
|||
|
|
-- Run once against tracksolid_db before deploying updated ingestion containers.
|
|||
|
|
-- ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
|
|||
|
|
|
|||
|
|
BEGIN;
|
|||
|
|
|
|||
|
|
-- Step 1: Correct all existing stored values (mm → km)
|
|||
|
|
UPDATE tracksolid.trips
|
|||
|
|
SET distance_m = distance_m / 1000000.0
|
|||
|
|
WHERE distance_m IS NOT NULL;
|
|||
|
|
|
|||
|
|
-- Step 2: Rename column
|
|||
|
|
ALTER TABLE tracksolid.trips
|
|||
|
|
RENAME COLUMN distance_m TO distance_km;
|
|||
|
|
|
|||
|
|
-- Step 3: Update column comment
|
|||
|
|
COMMENT ON COLUMN tracksolid.trips.distance_km
|
|||
|
|
IS 'Trip distance in kilometres. Corrected from mm storage on migration 04 (2026-04-10).';
|
|||
|
|
|
|||
|
|
COMMIT;
|