Zakhar Timoshenko
d13cc92d82
- Added a new cities table to the database to manage multiple cities independently. - Created a middleware to resolve city context based on X-City-Id header. - Updated routes and services to handle city-specific data for routes, stops, and alerts. - Implemented a CityContext in the frontend to manage active city state and provide city data across the application. - Added a CitySelector component for users to switch between cities. - Created a CitiesPage for admins to manage cities, including adding new cities with validation. - Updated various components and pages to reflect the active city context, ensuring data is displayed correctly based on the selected city. - Enhanced the schedule import functionality to restrict imports based on the active city.
114 lines
4.9 KiB
PL/PgSQL
114 lines
4.9 KiB
PL/PgSQL
-- Cities are independent transport networks. Existing Polotsk data is moved to
|
|
-- the default city so a second city can use the same route numbers and stop names.
|
|
CREATE TABLE IF NOT EXISTS cities (
|
|
id SERIAL PRIMARY KEY,
|
|
slug VARCHAR(64) NOT NULL UNIQUE,
|
|
name VARCHAR(120) NOT NULL UNIQUE,
|
|
emblem VARCHAR(64) NOT NULL DEFAULT '🏙️',
|
|
center_lat NUMERIC(9, 6),
|
|
center_lon NUMERIC(9, 6),
|
|
schedule_source_url TEXT,
|
|
is_active BOOLEAN NOT NULL DEFAULT true,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
CONSTRAINT cities_slug_format CHECK (slug ~ '^[a-z0-9]+(?:-[a-z0-9]+)*$'),
|
|
CONSTRAINT cities_center_pair CHECK (
|
|
(center_lat IS NULL AND center_lon IS NULL)
|
|
OR (center_lat BETWEEN -90 AND 90 AND center_lon BETWEEN -180 AND 180)
|
|
)
|
|
);
|
|
|
|
INSERT INTO cities (slug, name, emblem, center_lat, center_lon, schedule_source_url)
|
|
VALUES
|
|
('polotsk', 'Полоцк', '🏛️', 55.4869, 28.7856, 'https://ap2polotsk.of.by/ap2/'),
|
|
('novopolotsk', 'Новополоцк', '🏙️', 55.5318, 28.5987, NULL)
|
|
ON CONFLICT (slug) DO NOTHING;
|
|
|
|
ALTER TABLE routes ADD COLUMN IF NOT EXISTS city_id INTEGER;
|
|
ALTER TABLE stops ADD COLUMN IF NOT EXISTS city_id INTEGER;
|
|
ALTER TABLE alerts ADD COLUMN IF NOT EXISTS city_id INTEGER;
|
|
|
|
UPDATE routes SET city_id = (SELECT id FROM cities WHERE slug = 'polotsk') WHERE city_id IS NULL;
|
|
UPDATE stops SET city_id = (SELECT id FROM cities WHERE slug = 'polotsk') WHERE city_id IS NULL;
|
|
UPDATE alerts SET city_id = (SELECT id FROM cities WHERE slug = 'polotsk') WHERE city_id IS NULL;
|
|
|
|
ALTER TABLE routes ALTER COLUMN city_id SET NOT NULL;
|
|
ALTER TABLE stops ALTER COLUMN city_id SET NOT NULL;
|
|
ALTER TABLE alerts ALTER COLUMN city_id SET NOT NULL;
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'routes_city_id_fkey') THEN
|
|
ALTER TABLE routes ADD CONSTRAINT routes_city_id_fkey
|
|
FOREIGN KEY (city_id) REFERENCES cities(id) ON DELETE RESTRICT;
|
|
END IF;
|
|
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'stops_city_id_fkey') THEN
|
|
ALTER TABLE stops ADD CONSTRAINT stops_city_id_fkey
|
|
FOREIGN KEY (city_id) REFERENCES cities(id) ON DELETE RESTRICT;
|
|
END IF;
|
|
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'alerts_city_id_fkey') THEN
|
|
ALTER TABLE alerts ADD CONSTRAINT alerts_city_id_fkey
|
|
FOREIGN KEY (city_id) REFERENCES cities(id) ON DELETE RESTRICT;
|
|
END IF;
|
|
END $$;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_routes_city ON routes(city_id, is_active, route_number);
|
|
CREATE INDEX IF NOT EXISTS idx_stops_city ON stops(city_id, is_active, name);
|
|
CREATE INDEX IF NOT EXISTS idx_alerts_city_active ON alerts(city_id, is_active, start_time, end_time);
|
|
|
|
-- Incremental mobile sync must not leak even delete identifiers between cities.
|
|
ALTER TABLE sync_log ADD COLUMN IF NOT EXISTS city_id INTEGER REFERENCES cities(id) ON DELETE SET NULL;
|
|
|
|
UPDATE sync_log l SET city_id = r.city_id
|
|
FROM routes r
|
|
WHERE l.table_name = 'routes' AND l.record_id = r.id AND l.city_id IS NULL;
|
|
UPDATE sync_log l SET city_id = s.city_id
|
|
FROM stops s
|
|
WHERE l.table_name = 'stops' AND l.record_id = s.id AND l.city_id IS NULL;
|
|
UPDATE sync_log l SET city_id = r.city_id
|
|
FROM route_stops rs JOIN routes r ON r.id = rs.route_id
|
|
WHERE l.table_name = 'route_stops' AND l.record_id = rs.id AND l.city_id IS NULL;
|
|
UPDATE sync_log l SET city_id = r.city_id
|
|
FROM schedules s JOIN routes r ON r.id = s.route_id
|
|
WHERE l.table_name = 'schedules' AND l.record_id = s.id AND l.city_id IS NULL;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_sync_log_city_timestamp ON sync_log(city_id, timestamp DESC);
|
|
|
|
CREATE OR REPLACE FUNCTION log_sync_changes()
|
|
RETURNS TRIGGER AS $$
|
|
DECLARE
|
|
sync_city_id INTEGER;
|
|
related_route_id INTEGER;
|
|
BEGIN
|
|
IF TG_TABLE_NAME IN ('routes', 'stops', 'alerts') THEN
|
|
sync_city_id := CASE WHEN TG_OP = 'DELETE' THEN OLD.city_id ELSE NEW.city_id END;
|
|
ELSIF TG_TABLE_NAME IN ('route_stops', 'schedules') THEN
|
|
related_route_id := CASE WHEN TG_OP = 'DELETE' THEN OLD.route_id ELSE NEW.route_id END;
|
|
SELECT city_id INTO sync_city_id FROM routes WHERE id = related_route_id;
|
|
END IF;
|
|
|
|
IF (TG_OP = 'DELETE') THEN
|
|
INSERT INTO sync_log (table_name, record_id, action, city_id)
|
|
VALUES (TG_TABLE_NAME, OLD.id, 'delete', sync_city_id);
|
|
RETURN OLD;
|
|
ELSIF (TG_OP = 'UPDATE') THEN
|
|
INSERT INTO sync_log (table_name, record_id, action, city_id)
|
|
VALUES (TG_TABLE_NAME, NEW.id, 'update', sync_city_id);
|
|
RETURN NEW;
|
|
ELSIF (TG_OP = 'INSERT') THEN
|
|
INSERT INTO sync_log (table_name, record_id, action, city_id)
|
|
VALUES (TG_TABLE_NAME, NEW.id, 'insert', sync_city_id);
|
|
RETURN NEW;
|
|
END IF;
|
|
RETURN NULL;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'update_cities_updated_at') THEN
|
|
CREATE TRIGGER update_cities_updated_at BEFORE UPDATE ON cities
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
END IF;
|
|
END $$;
|