431 lines
18 KiB
SQL
431 lines
18 KiB
SQL
-- ================================================================
|
||
-- QAUP 机场车辆管理和碰撞避免系统统一数据库初始化脚本
|
||
-- 数据库名称：qaup
|
||
-- 版本：1.0.1
|
||
-- 创建时间：2025-07-12
|
||
-- 描述：该脚本包含完整的数据库结构，用于系统的全新部署
|
||
-- ================================================================
|
||
|
||
-- 1. 创建数据库（如果不存在）
|
||
-- 注意：此部分需要以数据库管理员身份执行
|
||
-- CREATE DATABASE qaup
|
||
-- WITH
|
||
-- OWNER = postgres
|
||
-- ENCODING = 'UTF8'
|
||
-- LC_COLLATE = 'en_US.UTF-8'
|
||
-- LC_CTYPE = 'en_US.UTF-8'
|
||
-- TABLESPACE = pg_default
|
||
-- CONNECTION LIMIT = -1
|
||
-- TEMPLATE = template0;
|
||
|
||
-- 设置数据库注释
|
||
-- COMMENT ON DATABASE qaup IS '机场车辆管理和碰撞避免统一数据库';
|
||
|
||
-- 连接到数据库并启用扩展
|
||
-- \c qaup
|
||
|
||
-- 2. 启用必要的PostgreSQL扩展
|
||
CREATE EXTENSION IF NOT EXISTS postgis;
|
||
CREATE EXTENSION IF NOT EXISTS postgis_topology;
|
||
|
||
-- 显示PostGIS版本信息（可选）
|
||
-- SELECT PostGIS_Version();
|
||
|
||
-- ================================================================
|
||
-- 3. 核心系统表（RuoYi框架基础表）
|
||
-- ================================================================
|
||
|
||
-- 3.1 用户信息表
|
||
DROP TABLE IF EXISTS "public"."sys_user" CASCADE;
|
||
CREATE TABLE "public"."sys_user" (
|
||
"user_id" BIGSERIAL NOT NULL,
|
||
"user_name" varchar(30) NOT NULL,
|
||
"nick_name" varchar(30) NOT NULL,
|
||
"user_type" varchar(2) DEFAULT '00',
|
||
"email" varchar(50) DEFAULT '',
|
||
"phonenumber" varchar(11) DEFAULT '',
|
||
"sex" char(1) DEFAULT '0',
|
||
"avatar" varchar(100) DEFAULT '',
|
||
"password" varchar(100) DEFAULT '',
|
||
"status" char(1) DEFAULT '0',
|
||
"del_flag" char(1) DEFAULT '0',
|
||
"login_ip" varchar(128) DEFAULT '',
|
||
"login_date" timestamp(6),
|
||
"create_by" varchar(64) DEFAULT '',
|
||
"create_time" timestamp(6),
|
||
"update_by" varchar(64) DEFAULT '',
|
||
"update_time" timestamp(6),
|
||
"remark" varchar(500) DEFAULT NULL,
|
||
CONSTRAINT "sys_user_pkey" PRIMARY KEY ("user_id")
|
||
);
|
||
|
||
COMMENT ON TABLE "public"."sys_user" IS '用户信息表';
|
||
COMMENT ON COLUMN "public"."sys_user"."user_id" IS '用户ID';
|
||
COMMENT ON COLUMN "public"."sys_user"."user_name" IS '用户账号';
|
||
COMMENT ON COLUMN "public"."sys_user"."nick_name" IS '用户昵称';
|
||
COMMENT ON COLUMN "public"."sys_user"."user_type" IS '用户类型（00系统用户）';
|
||
COMMENT ON COLUMN "public"."sys_user"."email" IS '用户邮箱';
|
||
COMMENT ON COLUMN "public"."sys_user"."phonenumber" IS '手机号码';
|
||
COMMENT ON COLUMN "public"."sys_user"."sex" IS '用户性别（0男 1女 2未知）';
|
||
COMMENT ON COLUMN "public"."sys_user"."avatar" IS '头像地址';
|
||
COMMENT ON COLUMN "public"."sys_user"."password" IS '密码';
|
||
COMMENT ON COLUMN "public"."sys_user"."status" IS '帐号状态（0正常 1停用）';
|
||
COMMENT ON COLUMN "public"."sys_user"."del_flag" IS '删除标志（0代表存在 2代表删除）';
|
||
|
||
-- 3.2 角色信息表
|
||
DROP TABLE IF EXISTS "public"."sys_role" CASCADE;
|
||
CREATE TABLE "public"."sys_role" (
|
||
"role_id" BIGSERIAL NOT NULL,
|
||
"role_name" varchar(30) NOT NULL,
|
||
"role_key" varchar(100) NOT NULL,
|
||
"role_sort" int4 NOT NULL,
|
||
"data_scope" char(1) DEFAULT '1',
|
||
"menu_check_strictly" boolean DEFAULT true,
|
||
"dept_check_strictly" boolean DEFAULT true,
|
||
"status" char(1) NOT NULL,
|
||
"del_flag" char(1) DEFAULT '0',
|
||
"create_by" varchar(64) DEFAULT '',
|
||
"create_time" timestamp(6),
|
||
"update_by" varchar(64) DEFAULT '',
|
||
"update_time" timestamp(6),
|
||
"remark" varchar(500) DEFAULT NULL,
|
||
CONSTRAINT "sys_role_pkey" PRIMARY KEY ("role_id")
|
||
);
|
||
|
||
COMMENT ON TABLE "public"."sys_role" IS '角色信息表';
|
||
COMMENT ON COLUMN "public"."sys_role"."role_id" IS '角色ID';
|
||
COMMENT ON COLUMN "public"."sys_role"."role_name" IS '角色名称';
|
||
COMMENT ON COLUMN "public"."sys_role"."role_key" IS '角色权限字符串';
|
||
COMMENT ON COLUMN "public"."sys_role"."role_sort" IS '显示顺序';
|
||
|
||
-- 3.3 部门表
|
||
DROP TABLE IF EXISTS "public"."sys_dept" CASCADE;
|
||
CREATE TABLE "public"."sys_dept" (
|
||
"dept_id" BIGSERIAL NOT NULL,
|
||
"parent_id" int8 DEFAULT 0,
|
||
"ancestors" varchar(50) DEFAULT '',
|
||
"dept_name" varchar(30) DEFAULT '',
|
||
"order_num" int4 DEFAULT 0,
|
||
"leader" varchar(20) DEFAULT NULL,
|
||
"phone" varchar(11) DEFAULT NULL,
|
||
"email" varchar(50) DEFAULT NULL,
|
||
"status" char(1) DEFAULT '0',
|
||
"del_flag" char(1) DEFAULT '0',
|
||
"create_by" varchar(64) DEFAULT '',
|
||
"create_time" timestamp(6),
|
||
"update_by" varchar(64) DEFAULT '',
|
||
"update_time" timestamp(6),
|
||
CONSTRAINT "sys_dept_pkey" PRIMARY KEY ("dept_id")
|
||
);
|
||
|
||
COMMENT ON TABLE "public"."sys_dept" IS '部门表';
|
||
COMMENT ON COLUMN "public"."sys_dept"."dept_id" IS '部门id';
|
||
COMMENT ON COLUMN "public"."sys_dept"."parent_id" IS '父部门id';
|
||
COMMENT ON COLUMN "public"."sys_dept"."ancestors" IS '祖级列表';
|
||
COMMENT ON COLUMN "public"."sys_dept"."dept_name" IS '部门名称';
|
||
|
||
-- ================================================================
|
||
-- 4. 车辆管理相关表
|
||
-- ================================================================
|
||
|
||
-- 4.1 车辆类型表
|
||
DROP TABLE IF EXISTS "public"."sys_vehicle_type" CASCADE;
|
||
CREATE TABLE "public"."sys_vehicle_type" (
|
||
"type_id" BIGSERIAL NOT NULL,
|
||
"type_name" varchar(100) NOT NULL,
|
||
"description" varchar(500),
|
||
"max_speed" int4,
|
||
"create_by" varchar(64),
|
||
"create_time" timestamp(6),
|
||
"update_by" varchar(64),
|
||
"update_time" timestamp(6),
|
||
"remark" varchar(500),
|
||
CONSTRAINT "sys_vehicle_type_pkey" PRIMARY KEY ("type_id")
|
||
);
|
||
|
||
COMMENT ON TABLE "public"."sys_vehicle_type" IS '车辆类型表';
|
||
COMMENT ON COLUMN "public"."sys_vehicle_type"."type_id" IS '类型ID';
|
||
COMMENT ON COLUMN "public"."sys_vehicle_type"."type_name" IS '类型名称';
|
||
COMMENT ON COLUMN "public"."sys_vehicle_type"."description" IS '类型描述';
|
||
COMMENT ON COLUMN "public"."sys_vehicle_type"."max_speed" IS '最大速度(km/h)';
|
||
|
||
-- 4.2 车辆信息表
|
||
DROP TABLE IF EXISTS "public"."sys_vehicle_info" CASCADE;
|
||
CREATE TABLE "public"."sys_vehicle_info" (
|
||
"vehicle_id" BIGSERIAL NOT NULL,
|
||
"license_plate" varchar(50) NOT NULL UNIQUE,
|
||
"vin_number" varchar(50) UNIQUE,
|
||
"type_id" int8 NOT NULL,
|
||
"brand" varchar(100),
|
||
"owning_unit" varchar(255),
|
||
"contact_person" varchar(100),
|
||
"phone_number" varchar(20),
|
||
"image_url" varchar(255),
|
||
"create_by" varchar(64),
|
||
"create_time" timestamp(6),
|
||
"update_by" varchar(64),
|
||
"update_time" timestamp(6),
|
||
"remark" varchar(500),
|
||
CONSTRAINT "sys_vehicle_info_pkey" PRIMARY KEY ("vehicle_id"),
|
||
CONSTRAINT "sys_vehicle_info_fk1" FOREIGN KEY ("type_id") REFERENCES "public"."sys_vehicle_type" ("type_id")
|
||
);
|
||
|
||
COMMENT ON TABLE "public"."sys_vehicle_info" IS '车辆信息表';
|
||
COMMENT ON COLUMN "public"."sys_vehicle_info"."vehicle_id" IS '车辆ID';
|
||
COMMENT ON COLUMN "public"."sys_vehicle_info"."license_plate" IS '车牌号';
|
||
COMMENT ON COLUMN "public"."sys_vehicle_info"."vin_number" IS 'VIN码';
|
||
COMMENT ON COLUMN "public"."sys_vehicle_info"."type_id" IS '类型ID';
|
||
COMMENT ON COLUMN "public"."sys_vehicle_info"."brand" IS '品牌';
|
||
COMMENT ON COLUMN "public"."sys_vehicle_info"."owning_unit" IS '所属单位';
|
||
|
||
-- 4.3 司机信息表
|
||
DROP TABLE IF EXISTS "public"."sys_driver_info" CASCADE;
|
||
CREATE TABLE "public"."sys_driver_info" (
|
||
"user_id" int8 NOT NULL,
|
||
"license_type" varchar(50) NOT NULL,
|
||
"create_by" varchar(64),
|
||
"create_time" timestamp(6),
|
||
"update_by" varchar(64),
|
||
"update_time" timestamp(6),
|
||
"remark" varchar(500),
|
||
CONSTRAINT "sys_driver_info_pkey" PRIMARY KEY ("user_id"),
|
||
CONSTRAINT "sys_driver_info_fk1" FOREIGN KEY ("user_id") REFERENCES "public"."sys_user" ("user_id")
|
||
);
|
||
|
||
COMMENT ON TABLE "public"."sys_driver_info" IS '驾驶员信息表';
|
||
COMMENT ON COLUMN "public"."sys_driver_info"."user_id" IS '用户ID';
|
||
COMMENT ON COLUMN "public"."sys_driver_info"."license_type" IS '驾驶证类型';
|
||
|
||
-- ================================================================
|
||
-- 5. 碰撞避免系统相关表（空间数据表）
|
||
-- ================================================================
|
||
|
||
-- 5.1 车辆位置表（含空间数据）
|
||
DROP TABLE IF EXISTS "public"."vehicle_location" CASCADE;
|
||
CREATE TABLE "public"."vehicle_location" (
|
||
"id" BIGSERIAL NOT NULL,
|
||
"vehicle_id" int8 NOT NULL,
|
||
"location" geometry(Point, 4326),
|
||
"timestamp" timestamp(6) NOT NULL,
|
||
"speed" numeric(5,2),
|
||
"direction" numeric(5,2),
|
||
"altitude" numeric(8,2),
|
||
"create_time" timestamp(6) DEFAULT CURRENT_TIMESTAMP,
|
||
CONSTRAINT "vehicle_location_pkey" PRIMARY KEY ("id"),
|
||
CONSTRAINT "vehicle_location_fk1" FOREIGN KEY ("vehicle_id") REFERENCES "public"."sys_vehicle_info" ("vehicle_id")
|
||
);
|
||
|
||
COMMENT ON TABLE "public"."vehicle_location" IS '车辆位置信息表（含空间数据）';
|
||
COMMENT ON COLUMN "public"."vehicle_location"."id" IS '记录ID';
|
||
COMMENT ON COLUMN "public"."vehicle_location"."vehicle_id" IS '车辆ID';
|
||
COMMENT ON COLUMN "public"."vehicle_location"."location" IS '位置信息（PostGIS几何类型）';
|
||
COMMENT ON COLUMN "public"."vehicle_location"."timestamp" IS '时间戳';
|
||
COMMENT ON COLUMN "public"."vehicle_location"."speed" IS '速度(km/h)';
|
||
COMMENT ON COLUMN "public"."vehicle_location"."direction" IS '方向角(度)';
|
||
COMMENT ON COLUMN "public"."vehicle_location"."altitude" IS '海拔高度(米)';
|
||
|
||
-- 为位置字段创建空间索引
|
||
CREATE INDEX "idx_vehicle_location_geom" ON "public"."vehicle_location" USING GIST ("location");
|
||
CREATE INDEX "idx_vehicle_location_vehicle_id" ON "public"."vehicle_location" ("vehicle_id");
|
||
CREATE INDEX "idx_vehicle_location_timestamp" ON "public"."vehicle_location" ("timestamp");
|
||
|
||
-- 5.2 机场区域表
|
||
DROP TABLE IF EXISTS "public"."airport_area" CASCADE;
|
||
CREATE TABLE "public"."airport_area" (
|
||
"id" BIGSERIAL NOT NULL,
|
||
"area_name" varchar(100) NOT NULL,
|
||
"area_type" varchar(50) NOT NULL,
|
||
"geometry" geometry(Polygon, 4326),
|
||
"properties" jsonb,
|
||
"create_time" timestamp(6) DEFAULT CURRENT_TIMESTAMP,
|
||
"update_time" timestamp(6),
|
||
CONSTRAINT "airport_area_pkey" PRIMARY KEY ("id")
|
||
);
|
||
|
||
COMMENT ON TABLE "public"."airport_area" IS '机场区域信息表';
|
||
COMMENT ON COLUMN "public"."airport_area"."id" IS '区域ID';
|
||
COMMENT ON COLUMN "public"."airport_area"."area_name" IS '区域名称';
|
||
COMMENT ON COLUMN "public"."airport_area"."area_type" IS '区域类型';
|
||
COMMENT ON COLUMN "public"."airport_area"."geometry" IS '区域几何形状';
|
||
COMMENT ON COLUMN "public"."airport_area"."properties" IS '区域属性（JSON格式）';
|
||
|
||
-- 为区域几何字段创建空间索引
|
||
CREATE INDEX "idx_airport_area_geom" ON "public"."airport_area" USING GIST ("geometry");
|
||
|
||
-- 5.3 路径冲突检测表
|
||
DROP TABLE IF EXISTS "public"."path_conflict_detection" CASCADE;
|
||
CREATE TABLE "public"."path_conflict_detection" (
|
||
"id" BIGSERIAL NOT NULL,
|
||
"object1_id" int8 NOT NULL,
|
||
"object2_id" int8 NOT NULL,
|
||
"conflict_type" varchar(50) NOT NULL,
|
||
"conflict_time" timestamp(6) NOT NULL,
|
||
"conflict_point" geometry(Point, 4326),
|
||
"risk_level" varchar(20) NOT NULL,
|
||
"status" varchar(20) DEFAULT 'ACTIVE',
|
||
"resolved_time" timestamp(6),
|
||
"create_time" timestamp(6) DEFAULT CURRENT_TIMESTAMP,
|
||
CONSTRAINT "path_conflict_detection_pkey" PRIMARY KEY ("id")
|
||
);
|
||
|
||
COMMENT ON TABLE "public"."path_conflict_detection" IS '路径冲突检测表';
|
||
COMMENT ON COLUMN "public"."path_conflict_detection"."id" IS '检测记录ID';
|
||
COMMENT ON COLUMN "public"."path_conflict_detection"."object1_id" IS '对象1的ID';
|
||
COMMENT ON COLUMN "public"."path_conflict_detection"."object2_id" IS '对象2的ID';
|
||
COMMENT ON COLUMN "public"."path_conflict_detection"."conflict_type" IS '冲突类型';
|
||
COMMENT ON COLUMN "public"."path_conflict_detection"."conflict_time" IS '冲突时间';
|
||
COMMENT ON COLUMN "public"."path_conflict_detection"."conflict_point" IS '冲突点位置';
|
||
COMMENT ON COLUMN "public"."path_conflict_detection"."risk_level" IS '风险等级';
|
||
|
||
-- 5.4 无人车指令表
|
||
DROP TABLE IF EXISTS "public"."unmanned_vehicle_commands" CASCADE;
|
||
CREATE TABLE "public"."unmanned_vehicle_commands" (
|
||
"id" BIGSERIAL NOT NULL,
|
||
"vehicle_id" int8 NOT NULL,
|
||
"command_type" varchar(50) NOT NULL,
|
||
"command_content" jsonb NOT NULL,
|
||
"status" varchar(20) DEFAULT 'PENDING',
|
||
"sent_time" timestamp(6),
|
||
"executed_time" timestamp(6),
|
||
"result" varchar(500),
|
||
"create_time" timestamp(6) DEFAULT CURRENT_TIMESTAMP,
|
||
CONSTRAINT "unmanned_vehicle_commands_pkey" PRIMARY KEY ("id"),
|
||
CONSTRAINT "unmanned_vehicle_commands_fk1" FOREIGN KEY ("vehicle_id") REFERENCES "public"."sys_vehicle_info" ("vehicle_id")
|
||
);
|
||
|
||
COMMENT ON TABLE "public"."unmanned_vehicle_commands" IS '无人车指令表';
|
||
COMMENT ON COLUMN "public"."unmanned_vehicle_commands"."id" IS '指令ID';
|
||
COMMENT ON COLUMN "public"."unmanned_vehicle_commands"."vehicle_id" IS '车辆ID';
|
||
COMMENT ON COLUMN "public"."unmanned_vehicle_commands"."command_type" IS '指令类型';
|
||
COMMENT ON COLUMN "public"."unmanned_vehicle_commands"."command_content" IS '指令内容（JSON格式）';
|
||
COMMENT ON COLUMN "public"."unmanned_vehicle_commands"."status" IS '指令状态';
|
||
|
||
-- ================================================================
|
||
-- 6. 系统配置和字典表
|
||
-- ================================================================
|
||
|
||
-- 6.1 系统配置表
|
||
DROP TABLE IF EXISTS "public"."sys_config" CASCADE;
|
||
CREATE TABLE "public"."sys_config" (
|
||
"config_id" BIGSERIAL NOT NULL,
|
||
"config_name" varchar(100) DEFAULT '',
|
||
"config_key" varchar(100) DEFAULT '',
|
||
"config_value" varchar(500) DEFAULT '',
|
||
"config_type" char(1) DEFAULT 'N',
|
||
"create_by" varchar(64) DEFAULT '',
|
||
"create_time" timestamp(6),
|
||
"update_by" varchar(64) DEFAULT '',
|
||
"update_time" timestamp(6),
|
||
"remark" varchar(500) DEFAULT NULL,
|
||
CONSTRAINT "sys_config_pkey" PRIMARY KEY ("config_id")
|
||
);
|
||
|
||
COMMENT ON TABLE "public"."sys_config" IS '参数配置表';
|
||
COMMENT ON COLUMN "public"."sys_config"."config_id" IS '参数主键';
|
||
COMMENT ON COLUMN "public"."sys_config"."config_name" IS '参数名称';
|
||
COMMENT ON COLUMN "public"."sys_config"."config_key" IS '参数键名';
|
||
COMMENT ON COLUMN "public"."sys_config"."config_value" IS '参数键值';
|
||
|
||
-- ================================================================
|
||
-- 7. 初始化基础数据
|
||
-- ================================================================
|
||
|
||
-- 7.1 插入车辆类型基础数据
|
||
INSERT INTO "public"."sys_vehicle_type" ("type_name", "description", "max_speed", "create_by", "create_time") VALUES
|
||
('无人车', '自动驾驶无人车辆', 30, 'admin', CURRENT_TIMESTAMP),
|
||
('摆渡车', '机场摆渡车辆', 40, 'admin', CURRENT_TIMESTAMP),
|
||
('货运车', '机场货运车辆', 50, 'admin', CURRENT_TIMESTAMP),
|
||
('清洁车', '机场清洁车辆', 25, 'admin', CURRENT_TIMESTAMP),
|
||
('维护车', '机场维护车辆', 60, 'admin', CURRENT_TIMESTAMP);
|
||
|
||
-- 7.2 插入系统配置基础数据
|
||
INSERT INTO "public"."sys_config" ("config_name", "config_key", "config_value", "config_type", "create_by", "create_time", "remark") VALUES
|
||
('数据采集间隔', 'data.collector.interval', '250', 'Y', 'admin', CURRENT_TIMESTAMP, '数据采集间隔时间，单位：毫秒'),
|
||
('WebSocket推送间隔', 'websocket.push.interval', '1000', 'Y', 'admin', CURRENT_TIMESTAMP, 'WebSocket推送间隔时间，单位：毫秒'),
|
||
('冲突检测阈值', 'collision.detection.threshold', '10', 'Y', 'admin', CURRENT_TIMESTAMP, '冲突检测距离阈值，单位：米'),
|
||
('速度限制阈值', 'speed.limit.threshold', '35', 'Y', 'admin', CURRENT_TIMESTAMP, '速度限制阈值，单位：km/h');
|
||
|
||
-- ================================================================
|
||
-- 8. 创建索引优化性能
|
||
-- ================================================================
|
||
|
||
-- 车辆信息相关索引
|
||
CREATE INDEX "idx_sys_vehicle_info_type_id" ON "public"."sys_vehicle_info" ("type_id");
|
||
CREATE INDEX "idx_sys_vehicle_info_license_plate" ON "public"."sys_vehicle_info" ("license_plate");
|
||
|
||
-- 位置数据相关索引（时间序列优化）
|
||
CREATE INDEX "idx_vehicle_location_time_vehicle" ON "public"."vehicle_location" ("timestamp" DESC, "vehicle_id");
|
||
|
||
-- 冲突检测相关索引
|
||
CREATE INDEX "idx_path_conflict_time" ON "public"."path_conflict_detection" ("conflict_time" DESC);
|
||
CREATE INDEX "idx_path_conflict_status" ON "public"."path_conflict_detection" ("status");
|
||
|
||
-- 指令表相关索引
|
||
CREATE INDEX "idx_unmanned_commands_vehicle_time" ON "public"."unmanned_vehicle_commands" ("vehicle_id", "create_time" DESC);
|
||
CREATE INDEX "idx_unmanned_commands_status" ON "public"."unmanned_vehicle_commands" ("status");
|
||
|
||
-- ================================================================
|
||
-- 9. 创建视图简化查询
|
||
-- ================================================================
|
||
|
||
-- 9.1 车辆详细信息视图
|
||
CREATE OR REPLACE VIEW "public"."v_vehicle_detail" AS
|
||
SELECT
|
||
vi.vehicle_id,
|
||
vi.license_plate,
|
||
vi.brand,
|
||
vi.owning_unit,
|
||
vi.contact_person,
|
||
vi.phone_number,
|
||
vt.type_name,
|
||
vt.max_speed,
|
||
vi.create_time
|
||
FROM "public"."sys_vehicle_info" vi
|
||
LEFT JOIN "public"."sys_vehicle_type" vt ON vi.type_id = vt.type_id;
|
||
|
||
COMMENT ON VIEW "public"."v_vehicle_detail" IS '车辆详细信息视图';
|
||
|
||
-- 9.2 最新位置信息视图
|
||
CREATE OR REPLACE VIEW "public"."v_latest_vehicle_position" AS
|
||
SELECT DISTINCT ON (vehicle_id)
|
||
vehicle_id,
|
||
ST_X(location) as longitude,
|
||
ST_Y(location) as latitude,
|
||
speed,
|
||
direction,
|
||
timestamp
|
||
FROM "public"."vehicle_location"
|
||
ORDER BY vehicle_id, timestamp DESC;
|
||
|
||
COMMENT ON VIEW "public"."v_latest_vehicle_position" IS '车辆最新位置信息视图';
|
||
|
||
-- ================================================================
|
||
-- 脚本执行完成提示
|
||
-- ================================================================
|
||
|
||
-- 输出成功信息
|
||
DO $$
|
||
BEGIN
|
||
RAISE NOTICE '=========================================';
|
||
RAISE NOTICE 'QAUP 数据库初始化完成！';
|
||
RAISE NOTICE '数据库版本: 1.0.1';
|
||
RAISE NOTICE '创建时间: %', CURRENT_TIMESTAMP;
|
||
RAISE NOTICE '========================================= ';
|
||
RAISE NOTICE '已创建表格数量: %', (
|
||
SELECT count(*)
|
||
FROM information_schema.tables
|
||
WHERE table_schema = 'public'
|
||
AND table_type = 'BASE TABLE'
|
||
);
|
||
RAISE NOTICE '已创建索引数量: %', (
|
||
SELECT count(*)
|
||
FROM pg_indexes
|
||
WHERE schemaname = 'public'
|
||
);
|
||
RAISE NOTICE '已创建视图数量: %', (
|
||
SELECT count(*)
|
||
FROM information_schema.views
|
||
WHERE table_schema = 'public'
|
||
);
|
||
RAISE NOTICE '=========================================';
|
||
END $$; |