105 lines
5.2 KiB
PL/PgSQL
105 lines
5.2 KiB
PL/PgSQL
-- 红绿灯系统数据库表创建脚本
|
||
-- 创建时间: 2025-01-05
|
||
-- 描述: 创建路口信息表和红绿灯设备表，支持多路口多设备管理
|
||
|
||
-- 1. 路口信息表
|
||
CREATE TABLE IF NOT EXISTS intersections (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
intersection_id VARCHAR(50) UNIQUE NOT NULL, -- 路口编号
|
||
intersection_name VARCHAR(100) NOT NULL, -- 路口名称
|
||
latitude DECIMAL(10, 8) NOT NULL, -- 纬度
|
||
longitude DECIMAL(11, 8) NOT NULL, -- 经度
|
||
area_code VARCHAR(20), -- 区域编码
|
||
description TEXT, -- 路口描述
|
||
is_active BOOLEAN DEFAULT true, -- 是否激活
|
||
created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- 2. 红绿灯设备表
|
||
CREATE TABLE IF NOT EXISTS traffic_lights (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
device_id VARCHAR(50) UNIQUE, -- 红绿灯设备编号（可选）
|
||
device_name VARCHAR(100) NOT NULL, -- 设备名称
|
||
ip_address VARCHAR(45) NOT NULL DEFAULT '0.0.0.0', -- 设备IP地址（唯一标识）
|
||
intersection_id VARCHAR(50) NOT NULL, -- 关联的路口编号
|
||
device_type VARCHAR(20) DEFAULT 'STANDARD', -- 设备类型
|
||
manufacturer VARCHAR(50), -- 制造商
|
||
model VARCHAR(50), -- 型号
|
||
install_date DATE, -- 安装日期
|
||
is_online BOOLEAN DEFAULT false, -- 是否在线
|
||
last_heartbeat TIMESTAMP, -- 最后心跳时间
|
||
is_active BOOLEAN DEFAULT true, -- 是否激活
|
||
created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
||
-- 外键约束
|
||
CONSTRAINT fk_traffic_light_intersection
|
||
FOREIGN KEY (intersection_id)
|
||
REFERENCES intersections(intersection_id)
|
||
ON DELETE CASCADE
|
||
ON UPDATE CASCADE
|
||
);
|
||
|
||
-- 创建索引
|
||
CREATE INDEX IF NOT EXISTS idx_intersection_id ON intersections(intersection_id);
|
||
CREATE INDEX IF NOT EXISTS idx_intersection_area_code ON intersections(area_code);
|
||
CREATE INDEX IF NOT EXISTS idx_intersection_active ON intersections(is_active);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_traffic_light_device_id ON traffic_lights(device_id);
|
||
CREATE INDEX IF NOT EXISTS idx_traffic_light_intersection ON traffic_lights(intersection_id);
|
||
CREATE INDEX IF NOT EXISTS idx_traffic_light_online ON traffic_lights(is_online);
|
||
CREATE INDEX IF NOT EXISTS idx_traffic_light_active ON traffic_lights(is_active);
|
||
CREATE UNIQUE INDEX IF NOT EXISTS idx_traffic_light_ip_unique ON traffic_lights(ip_address);
|
||
|
||
-- 创建更新时间触发器函数
|
||
CREATE OR REPLACE FUNCTION update_updated_time_column()
|
||
RETURNS TRIGGER AS $$
|
||
BEGIN
|
||
NEW.updated_time = CURRENT_TIMESTAMP;
|
||
RETURN NEW;
|
||
END;
|
||
$$ language 'plpgsql';
|
||
|
||
-- 为路口表创建更新时间触发器
|
||
CREATE TRIGGER update_intersections_updated_time
|
||
BEFORE UPDATE ON intersections
|
||
FOR EACH ROW
|
||
EXECUTE FUNCTION update_updated_time_column();
|
||
|
||
-- 为红绿灯设备表创建更新时间触发器
|
||
CREATE TRIGGER update_traffic_lights_updated_time
|
||
BEFORE UPDATE ON traffic_lights
|
||
FOR EACH ROW
|
||
EXECUTE FUNCTION update_updated_time_column();
|
||
|
||
-- 插入示例数据
|
||
INSERT INTO intersections (intersection_id, intersection_name, latitude, longitude, area_code, description)
|
||
VALUES
|
||
('DEFAULT_INTERSECTION', '默认路口', 36.35406879, 120.08341040, 'AREA_A', '机场主要路口，连接航站楼和跑道区域'),
|
||
('INTERSECTION_001', '主要路口1号', 36.35496367, 120.08688530, 'AREA_A', '机场主要路口，连接航站楼和跑道区域'),
|
||
('INTERSECTION_002', '次要路口2号', 36.35448347, 120.08502054, 'AREA_B', '机场次要路口，连接货运区域')
|
||
ON CONFLICT (intersection_id) DO NOTHING;
|
||
|
||
INSERT INTO traffic_lights (device_id, device_name, ip_address, intersection_id, device_type, manufacturer, model, install_date)
|
||
VALUES
|
||
('TL_001', '主路口红绿灯1号', '192.168.1.101', 'INTERSECTION_001', 'STANDARD', '海康威视', 'DS-TL100', '2024-01-01'),
|
||
('TL_002', '次路口红绿灯2号', '192.168.1.102', 'INTERSECTION_002', 'STANDARD', '大华技术', 'DH-TL200', '2024-01-15')
|
||
ON CONFLICT (device_id) DO NOTHING;
|
||
|
||
-- 添加注释
|
||
COMMENT ON TABLE intersections IS '路口信息表，存储机场内各个路口的基本信息';
|
||
COMMENT ON TABLE traffic_lights IS '红绿灯设备表，存储红绿灯设备信息及其与路口的绑定关系';
|
||
|
||
COMMENT ON COLUMN intersections.intersection_id IS '路口唯一编号';
|
||
COMMENT ON COLUMN intersections.intersection_name IS '路口名称';
|
||
COMMENT ON COLUMN intersections.latitude IS '路口纬度坐标';
|
||
COMMENT ON COLUMN intersections.longitude IS '路口经度坐标';
|
||
COMMENT ON COLUMN intersections.area_code IS '所属区域编码';
|
||
|
||
COMMENT ON COLUMN traffic_lights.device_id IS '红绿灯设备唯一编号（可选）';
|
||
COMMENT ON COLUMN traffic_lights.device_name IS '设备名称';
|
||
COMMENT ON COLUMN traffic_lights.ip_address IS '红绿灯设备IP地址';
|
||
COMMENT ON COLUMN traffic_lights.intersection_id IS '关联的路口编号';
|
||
COMMENT ON COLUMN traffic_lights.is_online IS '设备是否在线';
|
||
COMMENT ON COLUMN traffic_lights.last_heartbeat IS '最后一次心跳时间'; |