-- Ticket08：补齐三类测试事实的绑定、接收时间、来源编号和幂等键；原始事实只追加不覆盖。
|
ALTER TABLE t_test_realtime_record
|
ADD COLUMN source_identity VARCHAR(256) NULL AFTER adapter_code,
|
ADD COLUMN source_record_id VARCHAR(128) NULL AFTER source_identity;
|
|
UPDATE t_test_realtime_record
|
SET source_identity = adapter_code,
|
source_record_id = CASE WHEN cabinet_sequence_id IS NOT NULL THEN CONCAT('cabinet:', cabinet_sequence_id)
|
WHEN collector_sequence_id IS NOT NULL THEN CONCAT('collector:', collector_sequence_id)
|
ELSE NULL END
|
WHERE source_identity IS NULL;
|
|
ALTER TABLE t_test_realtime_record
|
MODIFY COLUMN source_identity VARCHAR(256) NOT NULL,
|
ADD KEY idx_realtime_source (test_battery_id, source_identity, source_record_id);
|
|
ALTER TABLE t_test_step_record
|
ADD COLUMN binding_id BIGINT NULL AFTER test_battery_id,
|
ADD COLUMN dedupe_key VARCHAR(512) NULL AFTER source_record_id,
|
ADD COLUMN server_received_at_utc DATETIME(3) NULL AFTER device_time_raw;
|
|
UPDATE t_test_step_record record_fact
|
JOIN t_test_channel_binding binding ON binding.test_id = record_fact.test_id
|
AND binding.test_battery_id = record_fact.test_battery_id
|
SET record_fact.binding_id = binding.binding_id,
|
record_fact.server_received_at_utc = record_fact.device_time_utc,
|
record_fact.dedupe_key = CASE
|
WHEN record_fact.source_record_id IS NOT NULL AND TRIM(record_fact.source_record_id) <> ''
|
THEN CONCAT('record:', record_fact.source_record_id)
|
ELSE CONCAT('fallback:', DATE_FORMAT(record_fact.device_time_utc, '%Y-%m-%dT%H:%i:%s.%fZ'),
|
':', record_fact.cycle_no, ':', record_fact.step_no)
|
END;
|
|
ALTER TABLE t_test_step_record
|
MODIFY COLUMN binding_id BIGINT NOT NULL,
|
MODIFY COLUMN dedupe_key VARCHAR(512) NOT NULL,
|
MODIFY COLUMN server_received_at_utc DATETIME(3) NOT NULL,
|
DROP INDEX uk_step_identity,
|
-- 前缀索引控制 InnoDB 索引长度，同时保留来源身份和幂等键的稳定区分。
|
ADD UNIQUE KEY uk_step_dedupe (test_battery_id, source_identity(128), dedupe_key(128)),
|
ADD KEY idx_step_binding_time (binding_id, server_received_at_utc);
|
|
ALTER TABLE t_test_cycle_record
|
ADD COLUMN binding_id BIGINT NULL AFTER test_battery_id,
|
ADD COLUMN dedupe_key VARCHAR(512) NULL AFTER source_record_id,
|
ADD COLUMN server_received_at_utc DATETIME(3) NULL AFTER device_time_raw;
|
|
UPDATE t_test_cycle_record record_fact
|
JOIN t_test_channel_binding binding ON binding.test_id = record_fact.test_id
|
AND binding.test_battery_id = record_fact.test_battery_id
|
SET record_fact.binding_id = binding.binding_id,
|
record_fact.server_received_at_utc = record_fact.device_time_utc,
|
record_fact.dedupe_key = CASE
|
WHEN record_fact.source_record_id IS NOT NULL AND TRIM(record_fact.source_record_id) <> ''
|
THEN CONCAT('record:', record_fact.source_record_id)
|
ELSE CONCAT('fallback:', DATE_FORMAT(record_fact.device_time_utc, '%Y-%m-%dT%H:%i:%s.%fZ'),
|
':', record_fact.cycle_no)
|
END;
|
|
ALTER TABLE t_test_cycle_record
|
MODIFY COLUMN binding_id BIGINT NOT NULL,
|
MODIFY COLUMN dedupe_key VARCHAR(512) NOT NULL,
|
MODIFY COLUMN server_received_at_utc DATETIME(3) NOT NULL,
|
DROP INDEX uk_cycle_identity,
|
ADD UNIQUE KEY uk_cycle_dedupe (test_battery_id, source_identity(128), dedupe_key(128)),
|
ADD KEY idx_cycle_binding_time (binding_id, server_received_at_utc);
|
