-- ============================================================================ -- S0-UAT-DEMO-MASTER-DATA-CLOSURE-1 · VERIFY -- -- ⚠️ 执行契约（AutoVersionUpdate.RunVerifyScriptIfExists）： -- 每条语句经 db.Ado.SqlQuerySingle(statement) 执行，取「首行首列」， -- 并要求 IsTruthy(result) == true，否则抛错、整个 migration 判 Failed。 -- 因此本文件的硬性约束： -- 1) 禁止 SET @var —— SqlQuerySingle 对 SET 返回 null → 立即判失败； -- 租户 ID 一律内联子查询，每条语句自包含。 -- 2) 每条语句必须返回「通过时为真」的标量（用 = / <=> 比较得 1/0）， -- 不能返回「错误条数」（0 = 通过会被 IsTruthy 判成 false）。 -- 3) 一条语句一个断言，不用多行 UNION 汇总。 -- -- 全部为 SELECT，无任何写操作。 -- PRE 阶段可手工执行本文件了解基线；POST / RERUN 由 AutoVersionUpdate 自动运行。 -- ============================================================================ -- --------------------------------------------------------------------------- -- §1 目标计数（RERUN 后必须仍然成立 —— 这就是幂等的直接证据） -- --------------------------------------------------------------------------- SELECT COUNT(*) = 26 FROM EmployeeMaster WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1); SELECT COUNT(*) = 13 FROM SuppMaster WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1); SELECT COUNT(*) = 15 FROM EmpWorkDutyMaster WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1); SELECT COUNT(*) = 57 FROM NbrTypeMaster WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1); SELECT COUNT(*) = 20 FROM LocationShelfMaster WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1); SELECT COUNT(*) = 8 FROM CostCtrMaster WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1); SELECT COUNT(*) = 3 FROM S0OrderReviewCycle WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1); SELECT COUNT(*) = 3 FROM S0ProductDesignCycle WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1); SELECT COUNT(*) = 3 FROM S0MaterialPlanCycle WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1); SELECT COUNT(*) = 3 FROM S0OrderScheduleCycle WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1); SELECT COUNT(*) = 4 FROM S0CategoryLeadTime WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1); -- 25 名员工有部门（19 复制 + 4 seed + 2 WB8 补正；WB8-UNB 仍无部门，属刻意保留） SELECT COUNT(*) = 25 FROM EmployeeMaster WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) AND Department IS NOT NULL AND Department <> ''; -- 5 名 login-bound assignee SELECT COUNT(*) = 5 FROM EmployeeMaster WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) AND sys_user_id IS NOT NULL; -- 回归：本批不得改动的对象 SELECT COUNT(*) = 5 FROM CustMaster WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1); SELECT COUNT(*) = 7 FROM DepartmentMaster WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1); SELECT COUNT(*) = 18 FROM LocationMaster WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1); SELECT COUNT(*) = 20 FROM LineMaster WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1); SELECT COUNT(*) = 56 FROM ItemMaster WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1); SELECT COUNT(*) = 7 FROM WorkCtrMaster WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1); SELECT COUNT(*) = 91 FROM NbrControl WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1); -- --------------------------------------------------------------------------- -- §2 逻辑 FK 校验 -- --------------------------------------------------------------------------- -- 2.1 Employee → DepartmentMaster：无悬空 SELECT COUNT(*) = 0 FROM EmployeeMaster e LEFT JOIN DepartmentMaster d ON d.tenant_id=e.tenant_id AND d.Department=e.Department WHERE e.tenant_id=(SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) AND e.Department IS NOT NULL AND e.Department<>'' AND d.RecID IS NULL; -- 2.2 Employee → SysUser 且同租户：无悬空 SELECT COUNT(*) = 0 FROM EmployeeMaster e LEFT JOIN SysUser u ON u.Id=e.sys_user_id AND u.TenantId=e.tenant_id WHERE e.tenant_id=(SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) AND e.sys_user_id IS NOT NULL AND u.Id IS NULL; -- 2.3 srm_purchase → SuppMaster：PRE=3 悬空 → POST 必须 0 SELECT COUNT(DISTINCT p.supplier_number) = 0 FROM srm_purchase p LEFT JOIN SuppMaster s ON s.tenant_id=p.tenant_id AND s.Supp=p.supplier_number WHERE p.tenant_id=(SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) AND s.RecID IS NULL; -- 2.4 WorkCtrMaster → DepartmentMaster：PRE=7 悬空 → POST 必须 0 SELECT COUNT(*) = 0 FROM WorkCtrMaster w LEFT JOIN DepartmentMaster d ON d.tenant_id=w.tenant_id AND d.Department=w.Department WHERE w.tenant_id=(SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) AND d.RecID IS NULL; -- 2.5 EmpWorkDuty → Employee / Location / Item：无悬空 SELECT COUNT(*) = 0 FROM EmpWorkDutyMaster k LEFT JOIN EmployeeMaster e ON e.tenant_id=k.tenant_id AND e.Domain=k.Domain AND e.Employee=k.Employee LEFT JOIN LocationMaster m ON m.tenant_id=k.tenant_id AND m.location=k.Location LEFT JOIN ItemMaster i ON i.tenant_id=k.tenant_id AND i.ItemNum=k.ItemNum1 WHERE k.tenant_id=(SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) AND (e.RecID IS NULL OR m.RecID IS NULL OR i.RecID IS NULL); -- 2.6 LocationShelf → LocationMaster（shelf.domain_code ↔ LocationMaster.Domain）：无悬空 SELECT COUNT(*) = 0 FROM LocationShelfMaster s LEFT JOIN LocationMaster l ON l.tenant_id=s.tenant_id AND l.Domain=s.domain_code AND l.location=s.location WHERE s.tenant_id=(SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) AND l.RecID IS NULL; -- 2.7 NbrControl → NbrTypeMaster：期望剩 42 条悬空（源租户仅覆盖 49/91，其余全库无定义，本批不造数） SELECT COUNT(*) = 42 FROM NbrControl n LEFT JOIN NbrTypeMaster t2 ON t2.tenant_id=n.tenant_id AND t2.NbrType=n.NbrType WHERE n.tenant_id=(SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) AND t2.RecID IS NULL; -- 2.8 ProductDesignCycle.item_type → ItemMaster.ItemType：无悬空 SELECT COUNT(*) = 0 FROM S0ProductDesignCycle c WHERE c.tenant_id=(SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) AND NOT EXISTS (SELECT 1 FROM ItemMaster i WHERE i.tenant_id=c.tenant_id AND i.ItemType=c.item_type); -- 2.9 七部门 7/7 均至少 1 名员工 SELECT COUNT(*) = 0 FROM (SELECT d.Department FROM DepartmentMaster d LEFT JOIN EmployeeMaster e ON e.tenant_id=d.tenant_id AND e.Department=d.Department WHERE d.tenant_id=(SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) GROUP BY d.Department HAVING COUNT(e.RecID)=0) z; -- 2.10 五名 login-bound assignee 均有合法部门 SELECT COUNT(*) = 5 FROM SysUser u JOIN EmployeeMaster e ON e.sys_user_id=u.Id AND e.tenant_id=u.TenantId JOIN DepartmentMaster d ON d.tenant_id=e.tenant_id AND d.Department=e.Department WHERE u.TenantId=(SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) AND u.Account IN ('UATPlanA','UATPurchaseA','UATWarehouseA','UATQualityA','UATExceptionA'); -- 2.11 WB8-UNB 保持未绑定（WP-WB8「未绑定应仅告警」用例的 fixture，不得被本批污染） SELECT COUNT(*) = 1 FROM EmployeeMaster WHERE tenant_id=(SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) AND Employee='WB8-UNB' AND sys_user_id IS NULL; -- --------------------------------------------------------------------------- -- §3 作用域纯净（新增/受影响表不得出现 0 / 1 / 默认租户 / NULL） -- --------------------------------------------------------------------------- SELECT COUNT(*) = 0 FROM EmployeeMaster WHERE tenant_id IS NULL OR tenant_id IN (0,1,1300000000001); SELECT COUNT(*) = 0 FROM SuppMaster WHERE tenant_id IS NULL OR tenant_id IN (0,1,1300000000001); SELECT COUNT(*) = 0 FROM EmpWorkDutyMaster WHERE tenant_id IS NULL OR tenant_id IN (0,1,1300000000001); SELECT COUNT(*) = 0 FROM NbrTypeMaster WHERE tenant_id IS NULL OR tenant_id IN (0,1,1300000000001); SELECT COUNT(*) = 0 FROM LocationShelfMaster WHERE tenant_id IS NULL OR tenant_id IN (0,1,1300000000001); SELECT COUNT(*) = 0 FROM CostCtrMaster WHERE tenant_id IS NULL OR tenant_id IN (0,1,1300000000001); -- 本批新增行（Domain='8010'）的作用域三件套必须全部落在目标口径（company/factory = 部门锚） SELECT COUNT(*) = 0 FROM EmployeeMaster e WHERE e.tenant_id=(SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) AND e.Domain='8010' AND (e.company_ref_id <> (SELECT d.company_ref_id FROM DepartmentMaster d WHERE d.tenant_id=e.tenant_id LIMIT 1) OR e.factory_ref_id <> (SELECT d.factory_ref_id FROM DepartmentMaster d WHERE d.tenant_id=e.tenant_id LIMIT 1)); -- 供应商同理 SELECT COUNT(*) = 0 FROM SuppMaster s WHERE s.tenant_id=(SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) AND (s.company_ref_id <> (SELECT d.company_ref_id FROM DepartmentMaster d WHERE d.tenant_id=s.tenant_id LIMIT 1) OR s.factory_ref_id <> (SELECT d.factory_ref_id FROM DepartmentMaster d WHERE d.tenant_id=s.tenant_id LIMIT 1)); -- --------------------------------------------------------------------------- -- §4 源租户零变更守卫（标记式不变量，非绝对总数） -- -- ⚠️ 刻意不写「源租户当前总数 = 464 / 3185 / …」这类硬编码常量： -- 源租户会被其它合法批次正常增长，把它固化成 migration 契约，会在 -- 未来的 fresh environment / 延迟执行场景下产生误报。 -- -- 本节改用「本批次写入标记」作为稳定不变量：本批所有写入一律带 -- CreateUser / create_user = 'UAT_DEMO_SEED'，且 tenant_id 恒为 @dst。 -- 若任何一条带本批标记的行出现在源租户，即证明发生了越界写入。 -- -- 源租户「行数完全未变」的运行时证据由独立的 -- WIP-S0-UAT-CLOSURE1.snapshot.sql 在 PRE / POST 两次执行后比对给出， -- 不进入本永久 verify 契约。 -- --------------------------------------------------------------------------- SELECT COUNT(*) = 0 FROM EmployeeMaster WHERE tenant_id <> (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) AND CreateUser = 'UAT_DEMO_SEED'; SELECT COUNT(*) = 0 FROM SuppMaster WHERE tenant_id <> (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) AND CreateUser = 'UAT_DEMO_SEED'; SELECT COUNT(*) = 0 FROM NbrTypeMaster WHERE tenant_id <> (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) AND CreateUser = 'UAT_DEMO_SEED'; SELECT COUNT(*) = 0 FROM CostCtrMaster WHERE tenant_id <> (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) AND CreateUser = 'UAT_DEMO_SEED'; SELECT COUNT(*) = 0 FROM EmpWorkDutyMaster WHERE tenant_id <> (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) AND CreateUser = 'UAT_DEMO_SEED'; SELECT COUNT(*) = 0 FROM LocationShelfMaster WHERE tenant_id <> (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) AND create_user = 'UAT_DEMO_SEED'; SELECT COUNT(*) = 0 FROM S0OrderReviewCycle WHERE tenant_id <> (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) AND create_user = 'UAT_DEMO_SEED'; SELECT COUNT(*) = 0 FROM S0ProductDesignCycle WHERE tenant_id <> (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) AND create_user = 'UAT_DEMO_SEED'; SELECT COUNT(*) = 0 FROM S0MaterialPlanCycle WHERE tenant_id <> (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) AND create_user = 'UAT_DEMO_SEED'; SELECT COUNT(*) = 0 FROM S0OrderScheduleCycle WHERE tenant_id <> (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) AND create_user = 'UAT_DEMO_SEED'; SELECT COUNT(*) = 0 FROM S0CategoryLeadTime WHERE tenant_id <> (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) AND create_user = 'UAT_DEMO_SEED'; -- 本批 UPDATE 同样只允许落在目标租户 SELECT COUNT(*) = 0 FROM EmployeeMaster WHERE tenant_id <> (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) AND UpdateUser = 'UAT_DEMO_SEED'; SELECT COUNT(*) = 0 FROM WorkCtrMaster WHERE tenant_id <> (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) AND UpdateUser = 'UAT_DEMO_SEED'; -- --------------------------------------------------------------------------- -- §5 业务键唯一性（rerun 未产生重复的直接证据） -- --------------------------------------------------------------------------- SELECT COUNT(*) = 0 FROM (SELECT Domain, Employee FROM EmployeeMaster WHERE tenant_id=(SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) GROUP BY Domain, Employee HAVING COUNT(*)>1) a; SELECT COUNT(*) = 0 FROM (SELECT factory_ref_id, Supp FROM SuppMaster WHERE tenant_id=(SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) GROUP BY factory_ref_id, Supp HAVING COUNT(*)>1) b; SELECT COUNT(*) = 0 FROM (SELECT Domain, NbrClass, NbrType FROM NbrTypeMaster WHERE tenant_id=(SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) GROUP BY Domain, NbrClass, NbrType HAVING COUNT(*)>1) c; SELECT COUNT(*) = 0 FROM (SELECT Domain, CostCtr FROM CostCtrMaster WHERE tenant_id=(SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) GROUP BY Domain, CostCtr HAVING COUNT(*)>1) d; SELECT COUNT(*) = 0 FROM (SELECT Domain, Employee, Duty, Location FROM EmpWorkDutyMaster WHERE tenant_id=(SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) GROUP BY Domain, Employee, Duty, Location HAVING COUNT(*)>1) e; SELECT COUNT(*) = 0 FROM (SELECT domain_code, location, inv_shelf FROM LocationShelfMaster WHERE tenant_id=(SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) GROUP BY domain_code, location, inv_shelf HAVING COUNT(*)>1) f; SELECT COUNT(*) = 0 FROM (SELECT factory_ref_id, order_type FROM S0OrderReviewCycle WHERE tenant_id=(SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) GROUP BY factory_ref_id, order_type HAVING COUNT(*)>1) g;