You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
188 lines
6.7 KiB
PL/PgSQL
188 lines
6.7 KiB
PL/PgSQL
-- Enable PostGIS extension
|
|
CREATE EXTENSION IF NOT EXISTS postgis;
|
|
|
|
-- API Keys table
|
|
CREATE TABLE api_keys (
|
|
id SERIAL PRIMARY KEY,
|
|
key VARCHAR(64) UNIQUE NOT NULL,
|
|
app_name VARCHAR(100) NOT NULL,
|
|
rate_limit INTEGER DEFAULT 1000,
|
|
is_active BOOLEAN DEFAULT true,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
last_used_at TIMESTAMP
|
|
);
|
|
|
|
-- Routes table (маршруты)
|
|
CREATE TABLE routes (
|
|
id SERIAL PRIMARY KEY,
|
|
route_number VARCHAR(10) NOT NULL,
|
|
name VARCHAR(200) NOT NULL,
|
|
type VARCHAR(20) NOT NULL CHECK (type IN ('bus', 'minibus', 'trolleybus', 'tram')),
|
|
color VARCHAR(7) DEFAULT '#0066CC',
|
|
is_active BOOLEAN DEFAULT true,
|
|
description TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Stops table (остановки) with geospatial support
|
|
CREATE TABLE stops (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(200) NOT NULL,
|
|
location GEOGRAPHY(Point, 4326) NOT NULL,
|
|
address VARCHAR(300),
|
|
description TEXT,
|
|
is_active BOOLEAN DEFAULT true,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Create spatial index for stops
|
|
CREATE INDEX idx_stops_location ON stops USING GIST(location);
|
|
|
|
-- Route stops junction table (связь маршрутов и остановок)
|
|
CREATE TABLE route_stops (
|
|
id SERIAL PRIMARY KEY,
|
|
route_id INTEGER NOT NULL REFERENCES routes(id) ON DELETE CASCADE,
|
|
stop_id INTEGER NOT NULL REFERENCES stops(id) ON DELETE CASCADE,
|
|
sequence INTEGER NOT NULL,
|
|
time_offset_minutes INTEGER NOT NULL DEFAULT 0,
|
|
UNIQUE(route_id, stop_id, sequence)
|
|
);
|
|
|
|
-- Schedules table (расписание)
|
|
CREATE TABLE schedules (
|
|
id SERIAL PRIMARY KEY,
|
|
route_id INTEGER NOT NULL REFERENCES routes(id) ON DELETE CASCADE,
|
|
day_type VARCHAR(20) NOT NULL CHECK (day_type IN ('weekday', 'saturday', 'sunday', 'holiday')),
|
|
departure_times TIME[] NOT NULL,
|
|
valid_from DATE,
|
|
valid_until DATE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Vehicles table (транспортные средства)
|
|
CREATE TABLE vehicles (
|
|
id SERIAL PRIMARY KEY,
|
|
vehicle_number VARCHAR(20) NOT NULL,
|
|
registration VARCHAR(20),
|
|
type VARCHAR(20) NOT NULL,
|
|
capacity INTEGER,
|
|
route_id INTEGER REFERENCES routes(id) ON DELETE SET NULL,
|
|
is_active BOOLEAN DEFAULT true,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Historical delays table (статистика задержек)
|
|
CREATE TABLE historical_delays (
|
|
id SERIAL PRIMARY KEY,
|
|
route_id INTEGER NOT NULL REFERENCES routes(id) ON DELETE CASCADE,
|
|
stop_id INTEGER NOT NULL REFERENCES stops(id) ON DELETE CASCADE,
|
|
hour_of_day INTEGER NOT NULL CHECK (hour_of_day BETWEEN 0 AND 23),
|
|
day_type VARCHAR(20) NOT NULL,
|
|
avg_delay_minutes INTEGER NOT NULL,
|
|
sample_count INTEGER DEFAULT 1,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE(route_id, stop_id, hour_of_day, day_type)
|
|
);
|
|
|
|
-- Alerts table (уведомления и оповещения)
|
|
CREATE TABLE alerts (
|
|
id SERIAL PRIMARY KEY,
|
|
route_id INTEGER REFERENCES routes(id) ON DELETE CASCADE,
|
|
alert_type VARCHAR(20) NOT NULL CHECK (alert_type IN ('delay', 'cancellation', 'detour', 'info')),
|
|
title VARCHAR(200) NOT NULL,
|
|
message TEXT NOT NULL,
|
|
start_time TIMESTAMP NOT NULL,
|
|
end_time TIMESTAMP,
|
|
is_active BOOLEAN DEFAULT true,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Sync log table (для offline синхронизации)
|
|
CREATE TABLE sync_log (
|
|
id SERIAL PRIMARY KEY,
|
|
table_name VARCHAR(50) NOT NULL,
|
|
record_id INTEGER NOT NULL,
|
|
action VARCHAR(20) NOT NULL CHECK (action IN ('insert', 'update', 'delete')),
|
|
timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Create indexes
|
|
CREATE INDEX idx_routes_number ON routes(route_number);
|
|
CREATE INDEX idx_routes_active ON routes(is_active);
|
|
CREATE INDEX idx_stops_name ON stops(name);
|
|
CREATE INDEX idx_route_stops_route ON route_stops(route_id);
|
|
CREATE INDEX idx_route_stops_stop ON route_stops(stop_id);
|
|
CREATE INDEX idx_schedules_route ON schedules(route_id);
|
|
CREATE INDEX idx_vehicles_route ON vehicles(route_id);
|
|
CREATE INDEX idx_alerts_active ON alerts(is_active, start_time, end_time);
|
|
CREATE INDEX idx_sync_log_timestamp ON sync_log(timestamp);
|
|
|
|
-- Create function to update updated_at timestamp
|
|
CREATE OR REPLACE FUNCTION update_updated_at_column()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = CURRENT_TIMESTAMP;
|
|
RETURN NEW;
|
|
END;
|
|
$$ language 'plpgsql';
|
|
|
|
-- Create triggers for updated_at
|
|
CREATE TRIGGER update_routes_updated_at BEFORE UPDATE ON routes
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
CREATE TRIGGER update_stops_updated_at BEFORE UPDATE ON stops
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
CREATE TRIGGER update_schedules_updated_at BEFORE UPDATE ON schedules
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
CREATE TRIGGER update_vehicles_updated_at BEFORE UPDATE ON vehicles
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
-- Create function to log changes for sync
|
|
CREATE OR REPLACE FUNCTION log_sync_changes()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
IF (TG_OP = 'DELETE') THEN
|
|
INSERT INTO sync_log (table_name, record_id, action)
|
|
VALUES (TG_TABLE_NAME, OLD.id, 'delete');
|
|
RETURN OLD;
|
|
ELSIF (TG_OP = 'UPDATE') THEN
|
|
INSERT INTO sync_log (table_name, record_id, action)
|
|
VALUES (TG_TABLE_NAME, NEW.id, 'update');
|
|
RETURN NEW;
|
|
ELSIF (TG_OP = 'INSERT') THEN
|
|
INSERT INTO sync_log (table_name, record_id, action)
|
|
VALUES (TG_TABLE_NAME, NEW.id, 'insert');
|
|
RETURN NEW;
|
|
END IF;
|
|
RETURN NULL;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Create sync triggers for main tables
|
|
CREATE TRIGGER sync_routes AFTER INSERT OR UPDATE OR DELETE ON routes
|
|
FOR EACH ROW EXECUTE FUNCTION log_sync_changes();
|
|
|
|
CREATE TRIGGER sync_stops AFTER INSERT OR UPDATE OR DELETE ON stops
|
|
FOR EACH ROW EXECUTE FUNCTION log_sync_changes();
|
|
|
|
CREATE TRIGGER sync_route_stops AFTER INSERT OR UPDATE OR DELETE ON route_stops
|
|
FOR EACH ROW EXECUTE FUNCTION log_sync_changes();
|
|
|
|
CREATE TRIGGER sync_schedules AFTER INSERT OR UPDATE OR DELETE ON schedules
|
|
FOR EACH ROW EXECUTE FUNCTION log_sync_changes();
|
|
|
|
-- Insert sample API key for development
|
|
INSERT INTO api_keys (key, app_name, rate_limit)
|
|
VALUES ('dev_key_12345678', 'Development App', 10000);
|
|
|
|
-- Grant permissions
|
|
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO transit_admin;
|
|
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO transit_admin;
|