27 lines
888 B
SQL
27 lines
888 B
SQL
-- Per-departure metadata for partial, via-stop, and calendar-specific trips.
|
|
-- Existing clients can continue to use departure_times; trips is the richer source.
|
|
ALTER TABLE schedules
|
|
ADD COLUMN IF NOT EXISTS trips JSONB NOT NULL DEFAULT '[]'::jsonb;
|
|
|
|
UPDATE schedules
|
|
SET trips = (
|
|
SELECT COALESCE(
|
|
jsonb_agg(
|
|
jsonb_build_object(
|
|
'departureTime', to_char(departure_time, 'HH24:MI:SS'),
|
|
'startStopId', NULL,
|
|
'endStopId', NULL,
|
|
'viaStopIds', '[]'::jsonb,
|
|
'daysOfWeek', '[]'::jsonb,
|
|
'exceptDaysOfWeek', '[]'::jsonb,
|
|
'note', ''
|
|
)
|
|
ORDER BY departure_time
|
|
),
|
|
'[]'::jsonb
|
|
)
|
|
FROM unnest(departure_times) AS expanded_departures(departure_time)
|
|
)
|
|
WHERE trips = '[]'::jsonb
|
|
AND departure_times IS NOT NULL;
|