generated from azures04/Base-REST-API
azures04
e5eccb77db
Add database DDL and server-side data access/service layers for transport domain. New SQL DDL files create transport_types, lines, stations, platforms and platform_lines tables (with FKs, indexes, spatial POINT column, CHECK constraints, and triggers to populate location). New repositories implement queries and CRUD for lines, stations, platforms and platform_lines (including distance/nearest-platform and accessibility queries). A transportService aggregates repo functions and exposes high-level operations (CRUD, station/line lookups, platform-line management and nearest-platform validation).
22 lines
849 B
SQL
22 lines
849 B
SQL
CREATE TABLE IF NOT EXISTS `platforms` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`stationId` INT UNSIGNED NOT NULL,
|
|
`name` VARCHAR(255) NULL,
|
|
`direction` VARCHAR(255) NULL,
|
|
`latitude` DECIMAL(10, 8) NOT NULL,
|
|
`longitude` DECIMAL(11, 8) NOT NULL,
|
|
`location` POINT NOT NULL,
|
|
`isPRM` BOOLEAN NOT NULL DEFAULT FALSE,
|
|
`hasElevator` BOOLEAN NOT NULL DEFAULT FALSE,
|
|
`createdAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
PRIMARY KEY (id),
|
|
SPATIAL INDEX idx_platforms_location (location),
|
|
INDEX idx_platforms_station (stationId),
|
|
|
|
CONSTRAINT fk_platforms_station FOREIGN KEY (stationId)
|
|
REFERENCES stations(id) ON DELETE CASCADE,
|
|
|
|
CONSTRAINT chk_platforms_latitude CHECK (latitude BETWEEN -90 AND 90),
|
|
CONSTRAINT chk_platforms_longitude CHECK (longitude BETWEEN -180 AND 180)
|
|
); |