-- Ticket14：按接收区间保存序号完整性问题，并为采集器历史记录保留区间归属。
|
ALTER TABLE t_test_collector_sample
|
ADD COLUMN reception_interval_id BIGINT NULL AFTER binding_id,
|
ADD KEY idx_collector_sample_interval (reception_interval_id),
|
ADD CONSTRAINT fk_collector_sample_reception_interval FOREIGN KEY (reception_interval_id)
|
REFERENCES t_test_reception_interval (interval_id);
|
|
CREATE TABLE t_test_integrity_issue (
|
issue_id BIGINT NOT NULL AUTO_INCREMENT,
|
test_id BIGINT NOT NULL,
|
interval_id BIGINT NOT NULL,
|
binding_id BIGINT NOT NULL,
|
source_type VARCHAR(32) NOT NULL,
|
source_id BIGINT NOT NULL,
|
source_channel_code VARCHAR(64) NULL,
|
issue_type VARCHAR(64) NOT NULL,
|
issue_status VARCHAR(16) NOT NULL DEFAULT 'OPEN',
|
missing_from_sequence BIGINT NULL,
|
missing_to_sequence BIGINT NULL,
|
retry_count INT NOT NULL DEFAULT 0,
|
detail VARCHAR(2000) NULL,
|
first_detected_at_utc DATETIME(3) NOT NULL,
|
last_detected_at_utc DATETIME(3) NOT NULL,
|
resolved_at_utc DATETIME(3) NULL,
|
PRIMARY KEY (issue_id),
|
KEY idx_integrity_issue_test (test_id, issue_status, last_detected_at_utc),
|
KEY idx_integrity_issue_interval (interval_id, source_type, source_id, source_channel_code),
|
CONSTRAINT fk_integrity_issue_test FOREIGN KEY (test_id) REFERENCES t_test (test_id),
|
CONSTRAINT fk_integrity_issue_interval FOREIGN KEY (interval_id) REFERENCES t_test_reception_interval (interval_id),
|
CONSTRAINT fk_integrity_issue_binding FOREIGN KEY (binding_id) REFERENCES t_test_channel_binding (binding_id),
|
CONSTRAINT chk_integrity_issue_source CHECK (source_type IN ('CABINET_CHANNEL', 'COLLECTOR_CHANNEL')),
|
CONSTRAINT chk_integrity_issue_status CHECK (issue_status IN ('OPEN', 'RESOLVED')),
|
CONSTRAINT chk_integrity_issue_range CHECK (missing_to_sequence IS NULL OR missing_to_sequence >= missing_from_sequence)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='测试接收区间完整性问题';
|
