111 lines
6.0 KiB
SQL
111 lines
6.0 KiB
SQL
|
-- =============================================================================
|
|||
|
|
-- IT-3 补列（可重放）：人脸注册「回执态」字段 + §12 设备范围字段。
|
|||
|
|
-- 依据：公寓端/10 §2（表 10-2 / 10-3）、03-数据设计、CR-02 表 3-49 / 3-66、13 表 13-13。
|
|||
|
|
-- 口径：
|
|||
|
|
-- 1) 59 张业务表不变：只补列，不建表、不删列、不改既有 CHECK 与唯一键。
|
|||
|
|
-- 2) DV-3「注册态与回执态字段」= reg_state / upload_state / dispatch_state / verdict
|
|||
|
|
-- + platform_version；实库已有前四项与 verdict_version，仅缺 platform_version。
|
|||
|
|
-- 3) 平台回执为「拉升回」（CR-C07 零回调），platform_version 仅承载 #2 personRegVerdicts[]
|
|||
|
|
-- 的 platformVersion，不参与注册生效判定（生效点恒为值班员 CONFIRM）。
|
|||
|
|
-- 4) 表 10-2 的 gy_sb §12 补列（device_class / face_scope_value / face_cache_enabled）
|
|||
|
|
-- 本期只建列并登记 Mock 设备；按范围选设备与缓存开关的执行逻辑属 IT-7（N-26/N-27）。
|
|||
|
|
-- =============================================================================
|
|||
|
|
|
|||
|
|
-- 1. apt_person_face_reg.platform_version —— #2 回执版本
|
|||
|
|
SET @ddl_reg = (SELECT IF(COUNT(*) = 0,
|
|||
|
|
'ALTER TABLE apt_person_face_reg ADD COLUMN platform_version BIGINT NOT NULL DEFAULT 0 COMMENT ''平台回执版本（#2 personRegVerdicts[].platformVersion）'' AFTER verdict_version',
|
|||
|
|
'DO 0')
|
|||
|
|
FROM information_schema.COLUMNS
|
|||
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|||
|
|
AND TABLE_NAME = 'apt_person_face_reg'
|
|||
|
|
AND COLUMN_NAME = 'platform_version');
|
|||
|
|
PREPARE apt_stmt_reg FROM @ddl_reg;
|
|||
|
|
EXECUTE apt_stmt_reg;
|
|||
|
|
DEALLOCATE PREPARE apt_stmt_reg;
|
|||
|
|
|
|||
|
|
-- 2. apt_master_person.face_platform_version —— 主档人脸回执版本
|
|||
|
|
SET @ddl_person = (SELECT IF(COUNT(*) = 0,
|
|||
|
|
'ALTER TABLE apt_master_person ADD COLUMN face_platform_version BIGINT NOT NULL DEFAULT 0 COMMENT ''人脸主档平台回执版本'' AFTER feature_version',
|
|||
|
|
'DO 0')
|
|||
|
|
FROM information_schema.COLUMNS
|
|||
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|||
|
|
AND TABLE_NAME = 'apt_master_person'
|
|||
|
|
AND COLUMN_NAME = 'face_platform_version');
|
|||
|
|
PREPARE apt_stmt_person FROM @ddl_person;
|
|||
|
|
EXECUTE apt_stmt_person;
|
|||
|
|
DEALLOCATE PREPARE apt_stmt_person;
|
|||
|
|
|
|||
|
|
-- 3. apt_person_face_reg.unit_code —— N-34 必填要素（N-36 允许值班员修正）
|
|||
|
|
SET @ddl_unit = (SELECT IF(COUNT(*) = 0,
|
|||
|
|
'ALTER TABLE apt_person_face_reg ADD COLUMN unit_code VARCHAR(64) NULL COMMENT ''所属单位编码（N-34 必填要素，N-36 可修正）'' AFTER name',
|
|||
|
|
'DO 0')
|
|||
|
|
FROM information_schema.COLUMNS
|
|||
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|||
|
|
AND TABLE_NAME = 'apt_person_face_reg'
|
|||
|
|
AND COLUMN_NAME = 'unit_code');
|
|||
|
|
PREPARE apt_stmt_unit FROM @ddl_unit;
|
|||
|
|
EXECUTE apt_stmt_unit;
|
|||
|
|
DEALLOCATE PREPARE apt_stmt_unit;
|
|||
|
|
|
|||
|
|
-- 4. gy_sb.device_class —— 表 10-2 设备类别（CHECKIN / CONSUME / CALL / TABLET / OTHER）
|
|||
|
|
-- 表 10-15 / 10-16 的目标集计算依赖本列；IT-3 只建列 + 种子登记，
|
|||
|
|
-- 按范围选设备的逻辑属 IT-7（N-26 / N-27），本期不启用。
|
|||
|
|
SET @ddl_sb_class = (SELECT IF(COUNT(*) = 0,
|
|||
|
|
'ALTER TABLE gy_sb ADD COLUMN device_class VARCHAR(20) NOT NULL DEFAULT ''OTHER'' COMMENT ''设备类别 CHECKIN/CONSUME/CALL/TABLET/OTHER；IT-7 启用'' AFTER device_type',
|
|||
|
|
'DO 0')
|
|||
|
|
FROM information_schema.COLUMNS
|
|||
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|||
|
|
AND TABLE_NAME = 'gy_sb'
|
|||
|
|
AND COLUMN_NAME = 'device_class');
|
|||
|
|
PREPARE apt_stmt_sb_class FROM @ddl_sb_class;
|
|||
|
|
EXECUTE apt_stmt_sb_class;
|
|||
|
|
DEALLOCATE PREPARE apt_stmt_sb_class;
|
|||
|
|
|
|||
|
|
-- 5. gy_sb.face_scope_value —— 表 10-2 可见范围取值（房间号 / 类别白名单 / 区域编码）
|
|||
|
|
-- 与既有 face_scope_ref（IT-2 §12 预置引用列）并存：本期只建列，取值由 IT-7 N-27 写入。
|
|||
|
|
SET @ddl_sb_value = (SELECT IF(COUNT(*) = 0,
|
|||
|
|
'ALTER TABLE gy_sb ADD COLUMN face_scope_value VARCHAR(256) NULL COMMENT ''可见范围取值：房间号 / 类别白名单 / 区域编码；IT-7 启用'' AFTER face_scope_ref',
|
|||
|
|
'DO 0')
|
|||
|
|
FROM information_schema.COLUMNS
|
|||
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|||
|
|
AND TABLE_NAME = 'gy_sb'
|
|||
|
|
AND COLUMN_NAME = 'face_scope_value');
|
|||
|
|
PREPARE apt_stmt_sb_value FROM @ddl_sb_value;
|
|||
|
|
EXECUTE apt_stmt_sb_value;
|
|||
|
|
DEALLOCATE PREPARE apt_stmt_sb_value;
|
|||
|
|
|
|||
|
|
-- 6. gy_sb.face_cache_enabled —— 表 10-2 是否允许缓存人脸（平板等临时拉取设备为 0）
|
|||
|
|
SET @ddl_sb_cache = (SELECT IF(COUNT(*) = 0,
|
|||
|
|
'ALTER TABLE gy_sb ADD COLUMN face_cache_enabled TINYINT(1) NOT NULL DEFAULT 0 COMMENT ''是否允许缓存人脸；TABLET 等临时拉取设备须为 0'' AFTER face_scope_value',
|
|||
|
|
'DO 0')
|
|||
|
|
FROM information_schema.COLUMNS
|
|||
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|||
|
|
AND TABLE_NAME = 'gy_sb'
|
|||
|
|
AND COLUMN_NAME = 'face_cache_enabled');
|
|||
|
|
PREPARE apt_stmt_sb_cache FROM @ddl_sb_cache;
|
|||
|
|
EXECUTE apt_stmt_sb_cache;
|
|||
|
|
DEALLOCATE PREPARE apt_stmt_sb_cache;
|
|||
|
|
|
|||
|
|
-- 7. 校验
|
|||
|
|
SELECT '=== IT-3 回执态列（应 3 行）===' AS section;
|
|||
|
|
SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_COMMENT
|
|||
|
|
FROM information_schema.COLUMNS
|
|||
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|||
|
|
AND ( (TABLE_NAME = 'apt_person_face_reg' AND COLUMN_NAME IN ('platform_version','unit_code'))
|
|||
|
|
OR (TABLE_NAME = 'apt_master_person' AND COLUMN_NAME = 'face_platform_version'))
|
|||
|
|
ORDER BY TABLE_NAME, COLUMN_NAME;
|
|||
|
|
|
|||
|
|
SELECT '=== §12 设备范围补列（应 3 行 device_class/face_scope_value/face_cache_enabled）===' AS section;
|
|||
|
|
SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_COMMENT
|
|||
|
|
FROM information_schema.COLUMNS
|
|||
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|||
|
|
AND TABLE_NAME = 'gy_sb'
|
|||
|
|
AND COLUMN_NAME IN ('device_class','face_scope_value','face_cache_enabled')
|
|||
|
|
ORDER BY ORDINAL_POSITION;
|
|||
|
|
|
|||
|
|
SELECT '=== 注册双状态链 CHECK 未被改动（应 5 行 state/verdict/upload/dispatch/report）===' AS section;
|
|||
|
|
SELECT CONSTRAINT_NAME, CHECK_CLAUSE
|
|||
|
|
FROM information_schema.CHECK_CONSTRAINTS
|
|||
|
|
WHERE CONSTRAINT_SCHEMA = DATABASE()
|
|||
|
|
AND CONSTRAINT_NAME IN ('ck_facereg_state','ck_facereg_verdict','ck_facereg_upload','ck_facereg_dispatch','ck_facereg_report')
|
|||
|
|
ORDER BY CONSTRAINT_NAME;
|