36 lines
2.2 KiB
SQL
36 lines
2.2 KiB
SQL
-- =============================================================================
|
|
-- Migration: Add GPS Tracking Fields
|
|
-- Date: 2026-03-11
|
|
-- Description: Adds raw GPS, velocity, speed and heading columns to the
|
|
-- deliveries and deliverylogs tables.
|
|
--
|
|
-- All columns are nullable so that:
|
|
-- 1. Old rider app versions that don't send these fields continue to work.
|
|
-- 2. Existing rows are not affected (they will have NULL values).
|
|
--
|
|
-- Safe to re-run: each ALTER uses IF NOT EXISTS column check pattern.
|
|
-- =============================================================================
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- Table: deliverylogs
|
|
-- -----------------------------------------------------------------------------
|
|
ALTER TABLE deliverylogs
|
|
ADD COLUMN IF NOT EXISTS raw_latitude VARCHAR(20) NULL DEFAULT NULL COMMENT 'Raw (unfiltered) GPS latitude from device',
|
|
ADD COLUMN IF NOT EXISTS raw_longitude VARCHAR(20) NULL DEFAULT NULL COMMENT 'Raw (unfiltered) GPS longitude from device',
|
|
ADD COLUMN IF NOT EXISTS velocity_lat VARCHAR(20) NULL DEFAULT NULL COMMENT 'Kalman-filtered velocity component (latitude direction)',
|
|
ADD COLUMN IF NOT EXISTS velocity_lng VARCHAR(20) NULL DEFAULT NULL COMMENT 'Kalman-filtered velocity component (longitude direction)',
|
|
ADD COLUMN IF NOT EXISTS speed VARCHAR(10) NULL DEFAULT NULL COMMENT 'Calculated speed in m/s or km/h',
|
|
ADD COLUMN IF NOT EXISTS heading VARCHAR(10) NULL DEFAULT NULL COMMENT 'Bearing/direction in degrees (0-360)';
|
|
|
|
-- =============================================================================
|
|
-- Verification queries (run after migration to confirm columns exist):
|
|
-- =============================================================================
|
|
-- SHOW COLUMNS FROM deliveries LIKE 'raw_%';
|
|
-- SHOW COLUMNS FROM deliveries LIKE 'velocity_%';
|
|
-- SHOW COLUMNS FROM deliveries LIKE 'speed';
|
|
-- SHOW COLUMNS FROM deliveries LIKE 'heading';
|
|
-- SHOW COLUMNS FROM deliverylogs LIKE 'raw_%';
|
|
-- SHOW COLUMNS FROM deliverylogs LIKE 'velocity_%';
|
|
-- SHOW COLUMNS FROM deliverylogs LIKE 'speed';
|
|
-- SHOW COLUMNS FROM deliverylogs LIKE 'heading';
|