206 lines
6.8 KiB
SQL
206 lines
6.8 KiB
SQL
|
-- =====================================================================
|
|||
|
|
-- deploy/dev/mysql-init/02-mailbox-messages.sql（ACM2-76）
|
|||
|
|
-- 共享信箱 CMINMSGS 测试报文种子（DEV ONLY）。本目录被 dev 与 integration
|
|||
|
|
-- 两个 compose 共用，脚本只在空数据卷首启时执行；已有卷不补种，需删卷重建。
|
|||
|
|
--
|
|||
|
|
-- 报文格式：SIS 接口规范（docs/legacy/SIS_AODB_RMS-V0.1.md）META + 类型体，
|
|||
|
|
-- XML 按 docs/legacy/unisysaodbsis.xsd；只 INSERT 契约内列，不建表不变更（C-2）。
|
|||
|
|
-- INBOX_CURSOR 初值 W=0，应用首启即全部消费。SEQN 1–5，与 01-mailbox.sql
|
|||
|
|
-- （仅建表、无数据）无冲突。
|
|||
|
|
--
|
|||
|
|
-- 两条参考数据报文验证的是【现状行为】，US-13 落地后行为会变，需同步更新：
|
|||
|
|
-- · REGN/DNLD：命中 US-04 忽略清单（IgnoreRules）→ SKIPPED + 回填；
|
|||
|
|
-- · ARPT/DNLD：合法但不支持（codec 只认 SCHD/FLOP）→ FAILED(UNSUPPORTED)
|
|||
|
|
-- → 退避约 15s（max-attempts=5）→ DEAD(EXHAUSTED) → 回填；
|
|||
|
|
-- 排在种子最后（MSG_ID 最大），避免压住队头挡住 SCHD/FLOP（US-03 AC1）。
|
|||
|
|
-- 航班 FLID 用 800001/800002，与 seed-flight.sql 的 90001–90008 互不干扰。
|
|||
|
|
-- =====================================================================
|
|||
|
|
|
|||
|
|
USE `cdairport`;
|
|||
|
|
|
|||
|
|
-- ① SCHD/DNLD：一进一出两条 FLTR（覆盖 ROUT/ERUT/GTDT/CKDT/CHDT/CLDT/PSDT 子段）
|
|||
|
|
INSERT INTO CMINMSGS
|
|||
|
|
(CMINMSGS_CLOB_MSG, CMINMSGS_SUBSYSTEM_TYPE, CMINMSGS_SUBSYSTEM_SUBTYPE,
|
|||
|
|
CMINMSGS_SUBSYSTEM_NAME, CMINMSGS_SUBSYSTEM_SEQUENCE, CMINMSGS_SUBSYSTEM_DATE_SENT)
|
|||
|
|
VALUES
|
|||
|
|
('<?xml version="1.0" encoding="UTF-8"?>
|
|||
|
|
<MSG xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="unisysaodbsis.xsd">
|
|||
|
|
<META>
|
|||
|
|
<SNDR>AODB</SNDR>
|
|||
|
|
<SEQN>1</SEQN>
|
|||
|
|
<DTTM>20260919120001</DTTM>
|
|||
|
|
<TYPE>SCHD</TYPE>
|
|||
|
|
<STYP>DNLD</STYP>
|
|||
|
|
</META>
|
|||
|
|
<SCHD>
|
|||
|
|
<RECS>2</RECS>
|
|||
|
|
<FLTR>
|
|||
|
|
<FLID>800001</FLID>
|
|||
|
|
<ALCD>CA</ALCD>
|
|||
|
|
<FLNO>CA4104</FLNO>
|
|||
|
|
<MVIN>A</MVIN>
|
|||
|
|
<SODT>20SEP261235</SODT>
|
|||
|
|
<FLTY>S</FLTY>
|
|||
|
|
<FLIN>D</FLIN>
|
|||
|
|
<ROUT RTNO="1"><APCD>PEK</APCD><SCAT></SCAT><SCDT>20SEP261015</SCDT></ROUT>
|
|||
|
|
<ROUT RTNO="2"><APCD>CTU</APCD><SCAT>20SEP261235</SCAT><SCDT></SCDT></ROUT>
|
|||
|
|
<ACFT>738</ACFT>
|
|||
|
|
<RENO>B-2087</RENO>
|
|||
|
|
<TRML>T2</TRML>
|
|||
|
|
<MAXP>264</MAXP>
|
|||
|
|
<CLDT CLNO="1">
|
|||
|
|
<BELT>4</BELT>
|
|||
|
|
<BCLS>X</BCLS>
|
|||
|
|
<PCOT>20SEP261240</PCOT>
|
|||
|
|
<PCCT>20SEP261340</PCCT>
|
|||
|
|
<BTYP>D</BTYP>
|
|||
|
|
</CLDT>
|
|||
|
|
<PSDT PSNO="1">
|
|||
|
|
<PSST>105</PSST>
|
|||
|
|
<STST>20SEP261240</STST>
|
|||
|
|
<STET>20SEP261340</STET>
|
|||
|
|
</PSDT>
|
|||
|
|
<ERUT RTNO="1"><APCD>PEK</APCD><SCAT></SCAT><SCDT>20SEP261015</SCDT></ERUT>
|
|||
|
|
<ERUT RTNO="2"><APCD>CTU</APCD><SCAT>20SEP261235</SCAT><SCDT></SCDT></ERUT>
|
|||
|
|
</FLTR>
|
|||
|
|
<FLTR>
|
|||
|
|
<FLID>800002</FLID>
|
|||
|
|
<ALCD>3U</ALCD>
|
|||
|
|
<FLNO>3U8885</FLNO>
|
|||
|
|
<MVIN>D</MVIN>
|
|||
|
|
<SODT>20SEP261510</SODT>
|
|||
|
|
<FLTY>S</FLTY>
|
|||
|
|
<FLIN>D</FLIN>
|
|||
|
|
<ROUT RTNO="1"><APCD>CTU</APCD><SCAT></SCAT><SCDT>20SEP261510</SCDT></ROUT>
|
|||
|
|
<ROUT RTNO="2"><APCD>SZX</APCD><SCAT>20SEP261750</SCAT><SCDT></SCDT></ROUT>
|
|||
|
|
<ACFT>320</ACFT>
|
|||
|
|
<RENO>B-8690</RENO>
|
|||
|
|
<TRML>T2</TRML>
|
|||
|
|
<MAXP>158</MAXP>
|
|||
|
|
<GTDT GTNO="1">
|
|||
|
|
<GATE>D06</GATE>
|
|||
|
|
<PGOT>20SEP261410</PGOT>
|
|||
|
|
<PGCT>20SEP261510</PGCT>
|
|||
|
|
<GTYP>D</GTYP>
|
|||
|
|
</GTDT>
|
|||
|
|
<CKDT CKNO="1">
|
|||
|
|
<CHKC>C05</CHKC>
|
|||
|
|
<CCLS>Y</CCLS>
|
|||
|
|
<PCOT>20SEP261310</PCOT>
|
|||
|
|
<PCCT>20SEP261440</PCCT>
|
|||
|
|
<CTYP>D</CTYP>
|
|||
|
|
</CKDT>
|
|||
|
|
<CKDT CKNO="2">
|
|||
|
|
<CHKC>C06</CHKC>
|
|||
|
|
<CCLS>X</CCLS>
|
|||
|
|
<PCOT>20SEP261310</PCOT>
|
|||
|
|
<PCCT>20SEP261440</PCCT>
|
|||
|
|
<CTYP>D</CTYP>
|
|||
|
|
</CKDT>
|
|||
|
|
<CHDT CHNO="1">
|
|||
|
|
<CHUT>CH04</CHUT>
|
|||
|
|
<CCLS>X</CCLS>
|
|||
|
|
<PCBT>20SEP261340</PCBT>
|
|||
|
|
<PCET>20SEP261500</PCET>
|
|||
|
|
<CTYP>D</CTYP>
|
|||
|
|
</CHDT>
|
|||
|
|
<PSDT PSNO="1">
|
|||
|
|
<PSST>101</PSST>
|
|||
|
|
<STST>20SEP261330</STST>
|
|||
|
|
<STET>20SEP261525</STET>
|
|||
|
|
</PSDT>
|
|||
|
|
<ERUT RTNO="1"><APCD>CTU</APCD><SCAT></SCAT><SCDT>20SEP261510</SCDT></ERUT>
|
|||
|
|
<ERUT RTNO="2"><APCD>SZX</APCD><SCAT>20SEP261750</SCAT><SCDT></SCDT></ERUT>
|
|||
|
|
</FLTR>
|
|||
|
|
</SCHD>
|
|||
|
|
</MSG>', 'SCHD', 'DNLD', 'AODB', 1, '2026-09-19 12:00:01'),
|
|||
|
|
|
|||
|
|
-- ② FLOP/ACTT：800001 实际到达时间
|
|||
|
|
('<?xml version="1.0" encoding="UTF-8"?>
|
|||
|
|
<MSG xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="unisysaodbsis.xsd">
|
|||
|
|
<META>
|
|||
|
|
<SNDR>AODB</SNDR>
|
|||
|
|
<SEQN>2</SEQN>
|
|||
|
|
<DTTM>20260919120002</DTTM>
|
|||
|
|
<TYPE>FLOP</TYPE>
|
|||
|
|
<STYP>ACTT</STYP>
|
|||
|
|
</META>
|
|||
|
|
<FLOP>
|
|||
|
|
<FLID>800001</FLID>
|
|||
|
|
<FFID>CA-CA4104-A-20SEP261235-D</FFID>
|
|||
|
|
<ACTT>20SEP261228</ACTT>
|
|||
|
|
</FLOP>
|
|||
|
|
</MSG>', 'FLOP', 'ACTT', 'AODB', 2, '2026-09-19 12:00:02'),
|
|||
|
|
|
|||
|
|
-- ③ FLOP/GTDT：800002 登机门改派 D06 → D08
|
|||
|
|
('<?xml version="1.0" encoding="UTF-8"?>
|
|||
|
|
<MSG xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="unisysaodbsis.xsd">
|
|||
|
|
<META>
|
|||
|
|
<SNDR>AODB</SNDR>
|
|||
|
|
<SEQN>3</SEQN>
|
|||
|
|
<DTTM>20260919120003</DTTM>
|
|||
|
|
<TYPE>FLOP</TYPE>
|
|||
|
|
<STYP>GTDT</STYP>
|
|||
|
|
</META>
|
|||
|
|
<FLOP>
|
|||
|
|
<FLID>800002</FLID>
|
|||
|
|
<FFID>3U-3U8885-D-20SEP261510-D</FFID>
|
|||
|
|
<GTDT GTNO="1">
|
|||
|
|
<GATE>D08</GATE>
|
|||
|
|
<PGOT>20SEP261410</PGOT>
|
|||
|
|
<PGCT>20SEP261510</PGCT>
|
|||
|
|
<GTYP>D</GTYP>
|
|||
|
|
</GTDT>
|
|||
|
|
</FLOP>
|
|||
|
|
</MSG>', 'FLOP', 'GTDT', 'AODB', 3, '2026-09-19 12:00:03'),
|
|||
|
|
|
|||
|
|
-- ④ REGN/DNLD：现状行为 = 命中 US-04 忽略清单 → SKIPPED + 回填（US-13 后不再跳过）
|
|||
|
|
('<?xml version="1.0" encoding="UTF-8"?>
|
|||
|
|
<MSG xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="unisysaodbsis.xsd">
|
|||
|
|
<META>
|
|||
|
|
<SNDR>AODB</SNDR>
|
|||
|
|
<SEQN>4</SEQN>
|
|||
|
|
<DTTM>20260919120004</DTTM>
|
|||
|
|
<TYPE>REGN</TYPE>
|
|||
|
|
<STYP>DNLD</STYP>
|
|||
|
|
</META>
|
|||
|
|
<RECS>2</RECS>
|
|||
|
|
<REGN>
|
|||
|
|
<RNUM>B-8692</RNUM>
|
|||
|
|
<ITAT>320</ITAT>
|
|||
|
|
<ACAL>3U</ACAL>
|
|||
|
|
<MAXP>158</MAXP>
|
|||
|
|
</REGN>
|
|||
|
|
<REGN>
|
|||
|
|
<RNUM>B-32D1</RNUM>
|
|||
|
|
<ITAT>321</ITAT>
|
|||
|
|
<ACAL>3U</ACAL>
|
|||
|
|
<MAXP>199</MAXP>
|
|||
|
|
</REGN>
|
|||
|
|
</MSG>', 'REGN', 'DNLD', 'AODB', 4, '2026-09-19 12:00:04'),
|
|||
|
|
|
|||
|
|
-- ⑤ ARPT/DNLD 排最后：现状行为 = 合法但不支持 → FAILED(UNSUPPORTED)
|
|||
|
|
-- → 退避约 15s → DEAD(EXHAUSTED) → 回填；US-13 后预期改为 SKIPPED
|
|||
|
|
('<?xml version="1.0" encoding="UTF-8"?>
|
|||
|
|
<MSG xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="unisysaodbsis.xsd">
|
|||
|
|
<META>
|
|||
|
|
<SNDR>AODB</SNDR>
|
|||
|
|
<SEQN>5</SEQN>
|
|||
|
|
<DTTM>20260919120005</DTTM>
|
|||
|
|
<TYPE>ARPT</TYPE>
|
|||
|
|
<STYP>DNLD</STYP>
|
|||
|
|
</META>
|
|||
|
|
<RECS>1</RECS>
|
|||
|
|
<ARPT>
|
|||
|
|
<ITCD>CTU</ITCD>
|
|||
|
|
<ICCD>ZUUU</ICCD>
|
|||
|
|
<ANAM>CHENGDU SHUANGLIU INTERNATIONAL</ANAM>
|
|||
|
|
<ANMC>成都双流国际机场</ANMC>
|
|||
|
|
<BDIS>12</BDIS>
|
|||
|
|
<CTRY>CN</CTRY>
|
|||
|
|
<ACTY>CTU</ACTY>
|
|||
|
|
<ATYP>D</ATYP>
|
|||
|
|
<HAUL>L</HAUL>
|
|||
|
|
</ARPT>
|
|||
|
|
</MSG>', 'ARPT', 'DNLD', 'AODB', 5, '2026-09-19 12:00:05');
|