windyboy
25cc967bd2
- postgres-init：02 建 basicdata schema 与 25 张基础数据表（表列对齐 admin-api secondary 实体注解），03 灌双流 T2 种子（fims_* 待 Q25 不造数）； 独立 schema 避免 public 非空导致 Flyway 首次 migrate 失败 - admin-mysql-init：原样引入 admin-api 仓库 adminapi_* 建表与字典/UI 种子， v0.0.2 补 FLIGHT_TASK/FLIGHT_TYPE 条目；compose 强制 utf8mb4 并设 LANG， 防 initdb 客户端落 latin1 造成中文双重编码 - es-init 一次性服务：按 admin-api mapping 建 flight_hts2（别名 flight_hts）， 副本置 0 保持单节点 green - dev/mysql-init：CMINMSGS 5 条样例报文（SCHD/FLOP×2/REGN/ARPT）， REGN/ARPT 标注现状预期（SKIPPED / DEAD(EXHAUSTED)） - seed-flight.sql：Flyway 建表后手动执行，8 条航班 6 场景 + 资源明细 - admin-api.yml secondary 加 currentSchema=basicdata,public - README：初始化/种子/重建卷/验收边界说明 已用 podman compose 重建数据卷全链实测；ACM2-76 有评审与验收全记录。
180 lines
11 KiB
PL/PgSQL
180 lines
11 KiB
PL/PgSQL
-- =====================================================================
|
||
-- deploy/integration/seed-flight.sql（ACM2-76）
|
||
-- 联调栈航班当前态种子：手动执行，前置条件是应用以 MSGX_FLYWAY_ENABLED=true
|
||
-- 启动过一次（V1 基线已应用）。双流 T2 语境人造数据，运营日 2026-09-20。
|
||
--
|
||
-- 幂等：主表 FLIGHT_SCHD 用 ON CONFLICT (FLID) DO UPDATE（OPERATION_DAY 按
|
||
-- INV-12 非空后不可改变，只补空不覆盖）；明细表主键是 (FLID, ORDINAL)
|
||
-- （路线点含 ROUTE_KIND），FLID 上无唯一约束，故按 FLID 先删后插（INV-14
|
||
-- 的完整合并口径）。CREATED_AT/UPDATED_AT 为 NOT NULL，显式给值。
|
||
-- 只写决策层（FLIGHT_SCHD + 明细），不碰管道层（PROC_STATE/MSG_EVENT/
|
||
-- INBOX_CURSOR/PIPELINE_LOCK/REQ_TRACK/SCHD_SNAP_LOG）。
|
||
-- 执行：psql -h 127.0.0.1 -p 15432 -U msgx_dev -d msgx -f 本文件
|
||
-- =====================================================================
|
||
|
||
BEGIN;
|
||
|
||
DELETE FROM FLIGHT_GATE WHERE FLID IN ('90001','90002','90003','90004','90005','90006','90007','90008');
|
||
DELETE FROM FLIGHT_CHECKIN WHERE FLID IN ('90001','90002','90003','90004','90005','90006','90007','90008');
|
||
DELETE FROM FLIGHT_BELT WHERE FLID IN ('90001','90002','90003','90004','90005','90006','90007','90008');
|
||
DELETE FROM FLIGHT_STAND_PLAN WHERE FLID IN ('90001','90002','90003','90004','90005','90006','90007','90008');
|
||
DELETE FROM FLIGHT_CHUTE WHERE FLID IN ('90001','90002','90003','90004','90005','90006','90007','90008');
|
||
DELETE FROM FLIGHT_DELAY WHERE FLID IN ('90001','90002','90003','90004','90005','90006','90007','90008');
|
||
DELETE FROM FLIGHT_ROUTE_POINT WHERE FLID IN ('90001','90002','90003','90004','90005','90006','90007','90008');
|
||
|
||
-- 主行 upsert。OPERATION_DAY 只补空不覆盖（INV-12）；STATE_VERSION 重置为 1
|
||
-- 仅是测试种子语义，真实链路由处理写入推进。
|
||
INSERT INTO FLIGHT_SCHD (
|
||
FLID, OPERATION_DAY, STATE, STATE_VERSION, LAST_MSG_ID,
|
||
ALCD, FLNO, MVIN, SODT, FLTY, FLIN, ACFT, RENO, TRML, MAXP,
|
||
CSOP, CSFT, MAID, TAOP, TAFL, TAID,
|
||
ESTT, ACTT, STND, PHAG, CNCL, REMC,
|
||
CREATED_AT, UPDATED_AT
|
||
) VALUES
|
||
-- ① 进港：CA4418 CAN→CTU，已落地，转盘 3、机位 108
|
||
('90001', DATE '2026-09-20', 'ACTIVE', 1, NULL,
|
||
'CA', 'CA4418', 'A', '20SEP261400', 'S', 'D', '77W', 'B-2006', 'T2', '294',
|
||
NULL, NULL, NULL, NULL, NULL, NULL,
|
||
NULL, '20SEP261412', '108', '10002', NULL, NULL,
|
||
now(), now()),
|
||
-- ② 离港：3U8881 CTU→PEK，柜台 C01/C02、登机门 D05、滑槽 CH03、机位 101
|
||
('90002', DATE '2026-09-20', 'ACTIVE', 1, NULL,
|
||
'3U', '3U8881', 'D', '20SEP261630', 'S', 'D', '320', 'B-8690', 'T2', '158',
|
||
NULL, NULL, NULL, NULL, NULL, NULL,
|
||
'20SEP261630', NULL, '101', '10001', NULL, NULL,
|
||
now(), now()),
|
||
-- ③ 主/共享对·主航班：CA1858 CTU→SZX
|
||
('90003', DATE '2026-09-20', 'ACTIVE', 1, NULL,
|
||
'CA', 'CA1858', 'D', '20SEP261830', 'S', 'D', '321', 'B-8691', 'T2', '199',
|
||
NULL, NULL, NULL, NULL, NULL, NULL,
|
||
'20SEP261830', NULL, '102', '10002', NULL, NULL,
|
||
now(), now()),
|
||
-- ③ 主/共享对·共享航班：3U1858 挂主航班 CA1858（MAID/CSOP/CSFT）
|
||
('90004', DATE '2026-09-20', 'ACTIVE', 1, NULL,
|
||
'3U', '1858', 'D', '20SEP261830', 'S', 'D', '321', NULL, 'T2', '199',
|
||
'CA', 'CA1858', '90003', NULL, NULL, NULL,
|
||
'20SEP261830', NULL, '102', NULL, NULL, NULL,
|
||
now(), now()),
|
||
-- ④ 延误进港：SQ8351 SIN→CTU，预计延后 2 小时并带 DELY
|
||
('90005', DATE '2026-09-20', 'ACTIVE', 1, NULL,
|
||
'SQ', 'SQ8351', 'A', '20SEP260930', 'S', 'I', '359', 'B-306N', 'T2', '288',
|
||
NULL, NULL, NULL, NULL, NULL, NULL,
|
||
'20SEP261130', NULL, '107', '10002', NULL, 'Catering Delay',
|
||
now(), now()),
|
||
-- ⑤ 取消离港：EU2230 CTU→KMG（CNCL 记取消时间，STATE 仍 ACTIVE）
|
||
('90006', DATE '2026-09-20', 'ACTIVE', 1, NULL,
|
||
'EU', 'EU2230', 'D', '20SEP260800', 'S', 'D', 'ARJ', 'B-1115', 'T2', '78',
|
||
NULL, NULL, NULL, NULL, NULL, NULL,
|
||
'20SEP260800', NULL, NULL, '10002', '20SEP260730', NULL,
|
||
now(), now()),
|
||
-- ⑥ 过站进港：3U3210 KMG→CTU，连线后续航班 3U3211
|
||
('90007', DATE '2026-09-20', 'ACTIVE', 1, NULL,
|
||
'3U', '3U3210', 'A', '20SEP261000', 'S', 'D', '320', 'B-2345', 'T2', '162',
|
||
NULL, NULL, NULL, '3U', '3U3211', '90008',
|
||
'20SEP261000', '20SEP261007', '103', '10001', NULL, NULL,
|
||
now(), now()),
|
||
-- ⑥ 过站后续离港：3U3211 CTU→HGH
|
||
('90008', DATE '2026-09-20', 'ACTIVE', 1, NULL,
|
||
'3U', '3U3211', 'D', '20SEP261155', 'S', 'D', '320', 'B-2345', 'T2', '162',
|
||
NULL, NULL, NULL, NULL, NULL, NULL,
|
||
'20SEP261155', NULL, '103', '10001', NULL, NULL,
|
||
now(), now())
|
||
ON CONFLICT (FLID) DO UPDATE SET
|
||
STATE = EXCLUDED.STATE,
|
||
STATE_VERSION = EXCLUDED.STATE_VERSION,
|
||
LAST_MSG_ID = EXCLUDED.LAST_MSG_ID,
|
||
ALCD = EXCLUDED.ALCD,
|
||
FLNO = EXCLUDED.FLNO,
|
||
MVIN = EXCLUDED.MVIN,
|
||
SODT = EXCLUDED.SODT,
|
||
FLTY = EXCLUDED.FLTY,
|
||
FLIN = EXCLUDED.FLIN,
|
||
ACFT = EXCLUDED.ACFT,
|
||
RENO = EXCLUDED.RENO,
|
||
TRML = EXCLUDED.TRML,
|
||
MAXP = EXCLUDED.MAXP,
|
||
CSOP = EXCLUDED.CSOP,
|
||
CSFT = EXCLUDED.CSFT,
|
||
MAID = EXCLUDED.MAID,
|
||
TAOP = EXCLUDED.TAOP,
|
||
TAFL = EXCLUDED.TAFL,
|
||
TAID = EXCLUDED.TAID,
|
||
ESTT = EXCLUDED.ESTT,
|
||
ACTT = EXCLUDED.ACTT,
|
||
STND = EXCLUDED.STND,
|
||
PHAG = EXCLUDED.PHAG,
|
||
CNCL = EXCLUDED.CNCL,
|
||
REMC = EXCLUDED.REMC,
|
||
UPDATED_AT = now(),
|
||
-- OPERATION_DAY 非空后不可改变（INV-12）：只补空
|
||
OPERATION_DAY = COALESCE(FLIGHT_SCHD.OPERATION_DAY, EXCLUDED.OPERATION_DAY);
|
||
|
||
-- ① 90001 进港明细：转盘、计划机位、航线
|
||
INSERT INTO FLIGHT_BELT (FLID, ORDINAL, SOURCE_SEQ, BELT, BCLS, PCOT, PCCT, FBAG, LBAG, BTYP, CREATED_AT, UPDATED_AT) VALUES
|
||
('90001', 1, '1', '3', 'X', '20SEP261405', '20SEP261455', '20SEP261418', '20SEP261450', 'D', now(), now());
|
||
INSERT INTO FLIGHT_STAND_PLAN (FLID, ORDINAL, SOURCE_SEQ, PSST, STST, STET, CREATED_AT, UPDATED_AT) VALUES
|
||
('90001', 1, '1', '108', '20SEP261355', '20SEP261510', now(), now());
|
||
INSERT INTO FLIGHT_ROUTE_POINT (FLID, ORDINAL, SOURCE_SEQ, ROUTE_KIND, APCD, SCAT, SCDT, CREATED_AT, UPDATED_AT) VALUES
|
||
('90001', 1, '1', 'ROUT', 'CAN', NULL, '20SEP261155', now(), now()),
|
||
('90001', 2, '2', 'ROUT', 'CTU', '20SEP261400', NULL, now(), now()),
|
||
('90001', 1, '1', 'ERUT', 'CTU', NULL, '20SEP261400', now(), now()),
|
||
('90001', 2, '2', 'ERUT', 'CAN', NULL, '20SEP261155', now(), now());
|
||
|
||
-- ② 90002 离港明细：柜台×2、登机门、滑槽、计划机位、航线
|
||
INSERT INTO FLIGHT_CHECKIN (FLID, ORDINAL, SOURCE_SEQ, CHKC, CCLS, PCOT, PCCT, CTYP, CREATED_AT, UPDATED_AT) VALUES
|
||
('90002', 1, '1', 'C01', 'Y', '20SEP261430', '20SEP261600', 'D', now(), now()),
|
||
('90002', 2, '2', 'C02', 'X', '20SEP261430', '20SEP261600', 'D', now(), now());
|
||
INSERT INTO FLIGHT_GATE (FLID, ORDINAL, SOURCE_SEQ, GATE, PGOT, PGCT, GTYP, CREATED_AT, UPDATED_AT) VALUES
|
||
('90002', 1, '1', 'D05', '20SEP261530', '20SEP261620', 'D', now(), now());
|
||
INSERT INTO FLIGHT_CHUTE (FLID, ORDINAL, SOURCE_SEQ, CHUT, CHCLS, PCBT, PCET, CHTYP, CREATED_AT, UPDATED_AT) VALUES
|
||
('90002', 1, '1', 'CH03', 'X', '20SEP261330', '20SEP261600', 'D', now(), now());
|
||
INSERT INTO FLIGHT_STAND_PLAN (FLID, ORDINAL, SOURCE_SEQ, PSST, STST, STET, CREATED_AT, UPDATED_AT) VALUES
|
||
('90002', 1, '1', '101', '20SEP261445', '20SEP261645', now(), now());
|
||
INSERT INTO FLIGHT_ROUTE_POINT (FLID, ORDINAL, SOURCE_SEQ, ROUTE_KIND, APCD, SCAT, SCDT, CREATED_AT, UPDATED_AT) VALUES
|
||
('90002', 1, '1', 'ROUT', 'CTU', NULL, '20SEP261630', now(), now()),
|
||
('90002', 2, '2', 'ROUT', 'PEK', '20SEP261935', NULL, now(), now()),
|
||
('90002', 1, '1', 'ERUT', 'CTU', NULL, '20SEP261630', now(), now()),
|
||
('90002', 2, '2', 'ERUT', 'PEK', '20SEP261935', NULL, now(), now());
|
||
|
||
-- ③ 主/共享对明细：同一物理航班、各自独立行（禁止按资源号去重）
|
||
INSERT INTO FLIGHT_STAND_PLAN (FLID, ORDINAL, SOURCE_SEQ, PSST, STST, STET, CREATED_AT, UPDATED_AT) VALUES
|
||
('90003', 1, '1', '102', '20SEP261645', '20SEP261945', now(), now()),
|
||
('90004', 1, '1', '102', '20SEP261645', '20SEP261945', now(), now());
|
||
INSERT INTO FLIGHT_ROUTE_POINT (FLID, ORDINAL, SOURCE_SEQ, ROUTE_KIND, APCD, SCAT, SCDT, CREATED_AT, UPDATED_AT) VALUES
|
||
('90003', 1, '1', 'ROUT', 'CTU', NULL, '20SEP261830', now(), now()),
|
||
('90003', 2, '2', 'ROUT', 'SZX', '20SEP262105', NULL, now(), now()),
|
||
('90003', 1, '1', 'ERUT', 'CTU', NULL, '20SEP261830', now(), now()),
|
||
('90003', 2, '2', 'ERUT', 'SZX', '20SEP262105', NULL, now(), now()),
|
||
('90004', 1, '1', 'ROUT', 'CTU', NULL, '20SEP261830', now(), now()),
|
||
('90004', 2, '2', 'ROUT', 'SZX', '20SEP262105', NULL, now(), now());
|
||
|
||
-- ④ 90005 延误进港明细：延误、转盘、计划机位、航线
|
||
INSERT INTO FLIGHT_DELAY (FLID, ORDINAL, SOURCE_SEQ, CODE, STRT, DURA, CREATED_AT, UPDATED_AT) VALUES
|
||
('90005', 1, '1', '23', '20SEP260930', '0120', now(), now());
|
||
INSERT INTO FLIGHT_BELT (FLID, ORDINAL, SOURCE_SEQ, BELT, BCLS, PCOT, PCCT, BTYP, CREATED_AT, UPDATED_AT) VALUES
|
||
('90005', 1, '1', '5', 'X', '20SEP261135', '20SEP261225', 'I', now(), now());
|
||
INSERT INTO FLIGHT_STAND_PLAN (FLID, ORDINAL, SOURCE_SEQ, PSST, STST, STET, CREATED_AT, UPDATED_AT) VALUES
|
||
('90005', 1, '1', '107', '20SEP261050', '20SEP261240', now(), now());
|
||
INSERT INTO FLIGHT_ROUTE_POINT (FLID, ORDINAL, SOURCE_SEQ, ROUTE_KIND, APCD, SCAT, SCDT, CREATED_AT, UPDATED_AT) VALUES
|
||
('90005', 1, '1', 'ROUT', 'SIN', NULL, '20SEP260745', now(), now()),
|
||
('90005', 2, '2', 'ROUT', 'CTU', '20SEP260930', NULL, now(), now()),
|
||
('90005', 1, '1', 'ERUT', 'CTU', NULL, '20SEP260930', now(), now()),
|
||
('90005', 2, '2', 'ERUT', 'SIN', NULL, '20SEP260745', now(), now());
|
||
|
||
-- ⑥ 过站对明细：90007 计划机位 + 航线；90008 登机门 + 航线
|
||
INSERT INTO FLIGHT_STAND_PLAN (FLID, ORDINAL, SOURCE_SEQ, PSST, STST, STET, CREATED_AT, UPDATED_AT) VALUES
|
||
('90007', 1, '1', '103', '20SEP260955', '20SEP261140', now(), now());
|
||
INSERT INTO FLIGHT_ROUTE_POINT (FLID, ORDINAL, SOURCE_SEQ, ROUTE_KIND, APCD, SCAT, SCDT, CREATED_AT, UPDATED_AT) VALUES
|
||
('90007', 1, '1', 'ROUT', 'KMG', NULL, '20SEP260845', now(), now()),
|
||
('90007', 2, '2', 'ROUT', 'CTU', '20SEP261000', NULL, now(), now()),
|
||
('90007', 1, '1', 'ERUT', 'CTU', NULL, '20SEP261000', now(), now()),
|
||
('90007', 2, '2', 'ERUT', 'KMG', NULL, '20SEP260845', now(), now()),
|
||
('90008', 1, '1', 'ROUT', 'CTU', NULL, '20SEP261155', now(), now()),
|
||
('90008', 2, '2', 'ROUT', 'HGH', '20SEP261425', NULL, now(), now()),
|
||
('90008', 1, '1', 'ERUT', 'CTU', NULL, '20SEP261155', now(), now()),
|
||
('90008', 2, '2', 'ERUT', 'HGH', '20SEP261425', NULL, now(), now());
|
||
INSERT INTO FLIGHT_GATE (FLID, ORDINAL, SOURCE_SEQ, GATE, PGOT, PGCT, GTYP, CREATED_AT, UPDATED_AT) VALUES
|
||
('90008', 1, '1', 'D04', '20SEP261055', '20SEP261145', 'D', now(), now());
|
||
|
||
COMMIT;
|