-- 观测闭环持久化增量约束。 -- V1-V3 是不可变基线；本迁移只补充语义字段、约束和查询索引，不写入演示业务数据。 ALTER TABLE t_test ADD COLUMN creator_id BIGINT NULL COMMENT '业务创建人，与 create_by 提交账号分离' AFTER create_by, ADD CONSTRAINT chk_test_status CHECK (test_status IN ('WAITING', 'RUNNING', 'PAUSED', 'RUNNING_ABNORMAL', 'COMPLETION_PENDING', 'COMPLETED', 'TERMINATED')), ADD CONSTRAINT chk_monitoring_status CHECK (monitoring_status IN ('NOT_MONITORED', 'WAITING_DEVICE_START', 'MONITORING', 'BACKFILLING', 'MONITORING_ENDED', 'CANCELLED')), ADD CONSTRAINT chk_test_monitor_timeout CHECK (monitor_timeout_minutes > 0), ADD KEY idx_test_creator_status (creator_id, test_status); ALTER TABLE t_device_adapter ADD CONSTRAINT chk_adapter_endpoint_port CHECK (port BETWEEN 1 AND 65535), ADD CONSTRAINT chk_adapter_poll_interval CHECK (poll_interval_ms > 0), ADD CONSTRAINT chk_adapter_timezone CHECK (timezone_id <> ''); ALTER TABLE t_detection_cabinet ADD CONSTRAINT chk_cabinet_capacity CHECK (capacity > 0 AND channel_count > 0); ALTER TABLE t_detection_channel ADD KEY idx_channel_identity_lookup (adapter_id, devtype, devid, subdevid, chlid), ADD CONSTRAINT chk_channel_status CHECK (online_status <> '' AND work_status <> ''); ALTER TABLE t_test_warning_threshold ADD CONSTRAINT chk_threshold_unit CHECK (unit IN ('A', 'V', 'Ah', 'Wh', '℃')), ADD CONSTRAINT chk_threshold_range CHECK (min_value IS NULL OR max_value IS NULL OR min_value <= max_value); ALTER TABLE t_test_channel_binding ADD UNIQUE KEY uk_binding_bts_test (channel_id, bts_test_id), ADD KEY idx_binding_test_channel (test_id, channel_id, claim_status), ADD CONSTRAINT chk_binding_claim_status CHECK (claim_status IN ('PENDING', 'CLAIMED', 'RELEASED', 'CONFLICT')); ALTER TABLE t_channel_snapshot ADD COLUMN device_time_raw VARCHAR(64) NULL COMMENT 'BTS 原始无时区时间文本' AFTER device_time_utc, ADD COLUMN adapter_timezone_id VARCHAR(64) NOT NULL DEFAULT 'Asia/Hong_Kong' COMMENT '解释设备时间所用时区' AFTER device_time_raw, ADD CONSTRAINT chk_snapshot_timezone CHECK (adapter_timezone_id <> ''), ADD KEY idx_snapshot_channel_time (channel_id, received_at_utc); ALTER TABLE t_test_sample ADD COLUMN adapter_timezone_id VARCHAR(64) NOT NULL DEFAULT 'Asia/Hong_Kong' COMMENT '解释设备时间所用时区' AFTER device_time_raw, ADD CONSTRAINT chk_sample_identity CHECK (bts_test_id <> '' AND seq_id > 0), ADD CONSTRAINT chk_sample_device_time_raw CHECK (device_time_raw <> ''), ADD CONSTRAINT chk_sample_timezone CHECK (adapter_timezone_id <> ''), ADD KEY idx_sample_test_time (test_id, device_time_utc, seq_id); ALTER TABLE t_test_sample_measurement ADD CONSTRAINT chk_measurement_source CHECK (source_type <> ''), ADD CONSTRAINT chk_measurement_value CHECK (metric_value IS NOT NULL OR text_value IS NOT NULL); ALTER TABLE t_history_sync_cursor ADD CONSTRAINT chk_history_cursor_identity CHECK (bts_test_id <> '' AND next_seq_id > 0), ADD KEY idx_history_cursor_binding_status (binding_id, sync_status); ALTER TABLE t_data_gap ADD CONSTRAINT chk_data_gap_range CHECK (bts_test_id <> '' AND missing_from_seq > 0 AND missing_from_seq <= missing_to_seq), ADD KEY idx_gap_binding_seq (binding_id, bts_test_id, missing_from_seq, gap_status); ALTER TABLE t_test_exception ADD COLUMN open_dedupe_key VARCHAR(320) GENERATED ALWAYS AS ( CASE WHEN exception_status = 'OPEN' THEN CONCAT(COALESCE(test_id, 0), ':', COALESCE(channel_id, 0), ':', exception_type) ELSE NULL END ) STORED, ADD UNIQUE KEY uk_exception_open_dedupe (open_dedupe_key), ADD CONSTRAINT chk_exception_status CHECK (exception_status IN ('OPEN', 'HANDLED', 'CLOSED')), ADD CONSTRAINT chk_exception_occurrence_count CHECK (occurrence_count > 0); ALTER TABLE t_test_audit ADD CONSTRAINT chk_audit_actor CHECK (actor_id IS NOT NULL OR actor_name <> ''), ADD KEY idx_test_audit_correlation (correlation_id); ALTER TABLE t_protocol_message ADD CONSTRAINT chk_protocol_direction CHECK (direction IN ('REQUEST', 'RESPONSE')), ADD CONSTRAINT chk_protocol_parse_status CHECK (parse_status IN ('SUCCESS', 'FAILED', 'CONFLICT')), ADD CONSTRAINT chk_protocol_retention CHECK (retention_until_utc >= occurred_at_utc); -- 关系约束集中在增量迁移，避免改变已应用基线的脚本内容。 ALTER TABLE t_detection_cabinet ADD CONSTRAINT fk_cabinet_adapter FOREIGN KEY (adapter_id) REFERENCES t_device_adapter (adapter_id); ALTER TABLE t_detection_channel ADD CONSTRAINT fk_channel_adapter FOREIGN KEY (adapter_id) REFERENCES t_device_adapter (adapter_id), ADD CONSTRAINT fk_channel_cabinet FOREIGN KEY (cabinet_id) REFERENCES t_detection_cabinet (cabinet_id); ALTER TABLE t_collector ADD CONSTRAINT fk_collector_adapter FOREIGN KEY (adapter_id) REFERENCES t_device_adapter (adapter_id); ALTER TABLE t_test ADD CONSTRAINT fk_test_type FOREIGN KEY (test_type_id) REFERENCES t_test_type (test_type_id); ALTER TABLE t_test_plan_step ADD CONSTRAINT fk_plan_step_test FOREIGN KEY (test_id) REFERENCES t_test (test_id); ALTER TABLE t_test_battery ADD CONSTRAINT fk_test_battery_test FOREIGN KEY (test_id) REFERENCES t_test (test_id), ADD CONSTRAINT fk_test_battery_group FOREIGN KEY (group_id) REFERENCES t_battery_group (group_id); ALTER TABLE t_test_battery_field ADD CONSTRAINT fk_test_battery_field_relation FOREIGN KEY (relation_id) REFERENCES t_test_battery (relation_id); ALTER TABLE t_test_warning_threshold ADD CONSTRAINT fk_test_threshold_test FOREIGN KEY (test_id) REFERENCES t_test (test_id); ALTER TABLE t_test_channel_binding ADD CONSTRAINT fk_binding_test FOREIGN KEY (test_id) REFERENCES t_test (test_id), ADD CONSTRAINT fk_binding_battery FOREIGN KEY (battery_relation_id) REFERENCES t_test_battery (relation_id), ADD CONSTRAINT fk_binding_channel FOREIGN KEY (channel_id) REFERENCES t_detection_channel (channel_id); ALTER TABLE t_channel_lease ADD CONSTRAINT fk_lease_test FOREIGN KEY (test_id) REFERENCES t_test (test_id), ADD CONSTRAINT fk_lease_binding FOREIGN KEY (binding_id) REFERENCES t_test_channel_binding (binding_id), ADD CONSTRAINT fk_lease_channel FOREIGN KEY (channel_id) REFERENCES t_detection_channel (channel_id); ALTER TABLE t_channel_snapshot ADD CONSTRAINT fk_snapshot_channel FOREIGN KEY (channel_id) REFERENCES t_detection_channel (channel_id), ADD CONSTRAINT fk_snapshot_test FOREIGN KEY (test_id) REFERENCES t_test (test_id), ADD CONSTRAINT fk_snapshot_binding FOREIGN KEY (binding_id) REFERENCES t_test_channel_binding (binding_id); ALTER TABLE t_test_sample ADD CONSTRAINT fk_sample_test FOREIGN KEY (test_id) REFERENCES t_test (test_id), ADD CONSTRAINT fk_sample_binding FOREIGN KEY (binding_id) REFERENCES t_test_channel_binding (binding_id), ADD CONSTRAINT fk_sample_channel FOREIGN KEY (channel_id) REFERENCES t_detection_channel (channel_id); ALTER TABLE t_test_sample_measurement ADD CONSTRAINT fk_measurement_sample FOREIGN KEY (sample_id) REFERENCES t_test_sample (sample_id); ALTER TABLE t_history_sync_cursor ADD CONSTRAINT fk_cursor_binding FOREIGN KEY (binding_id) REFERENCES t_test_channel_binding (binding_id); ALTER TABLE t_data_gap ADD CONSTRAINT fk_gap_test FOREIGN KEY (test_id) REFERENCES t_test (test_id), ADD CONSTRAINT fk_gap_binding FOREIGN KEY (binding_id) REFERENCES t_test_channel_binding (binding_id); ALTER TABLE t_device_run_event ADD CONSTRAINT fk_run_event_channel FOREIGN KEY (channel_id) REFERENCES t_detection_channel (channel_id), ADD CONSTRAINT fk_run_event_test FOREIGN KEY (claimed_test_id) REFERENCES t_test (test_id), ADD CONSTRAINT fk_run_event_binding FOREIGN KEY (claimed_binding_id) REFERENCES t_test_channel_binding (binding_id); ALTER TABLE t_test_exception ADD CONSTRAINT fk_exception_test FOREIGN KEY (test_id) REFERENCES t_test (test_id), ADD CONSTRAINT fk_exception_channel FOREIGN KEY (channel_id) REFERENCES t_detection_channel (channel_id); ALTER TABLE t_test_audit ADD CONSTRAINT fk_audit_test FOREIGN KEY (test_id) REFERENCES t_test (test_id); ALTER TABLE t_protocol_message ADD CONSTRAINT fk_protocol_adapter FOREIGN KEY (adapter_id) REFERENCES t_device_adapter (adapter_id), ADD CONSTRAINT fk_protocol_test FOREIGN KEY (test_id) REFERENCES t_test (test_id);