-- 观测闭环持久化增量约束。
|
-- 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);
|
