-- 最终业务数据库基线（Ticket03）。本脚本只创建结构，不导入演示业务数据。
|
-- 业务关联由事务、删除顺序、唯一约束和索引维护，不建立物理外键。
|
SET NAMES utf8mb4;
|
SET time_zone = '+00:00';
|
|
CREATE TABLE t_battery_group (
|
group_id BIGINT NOT NULL AUTO_INCREMENT,
|
group_name VARCHAR(100) NOT NULL,
|
model VARCHAR(100) NOT NULL,
|
purpose VARCHAR(500) NULL,
|
status CHAR(1) NOT NULL DEFAULT '0',
|
del_flag TINYINT NOT NULL DEFAULT 0,
|
create_by VARCHAR(64) NOT NULL DEFAULT '',
|
create_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
update_by VARCHAR(64) NOT NULL DEFAULT '',
|
update_time DATETIME(3) NULL,
|
remark VARCHAR(500) NULL,
|
PRIMARY KEY (group_id),
|
UNIQUE KEY uk_battery_group_name_del (group_name, del_flag),
|
KEY idx_battery_group_status (status, del_flag, create_time)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='电池组';
|
|
CREATE TABLE t_battery_group_battery (
|
relation_id BIGINT NOT NULL AUTO_INCREMENT,
|
group_id BIGINT NOT NULL,
|
battery_no VARCHAR(100) NOT NULL,
|
sort_order INT NOT NULL DEFAULT 0,
|
del_flag TINYINT NOT NULL DEFAULT 0,
|
PRIMARY KEY (relation_id),
|
UNIQUE KEY uk_group_battery_no_del (group_id, battery_no, del_flag),
|
KEY idx_group_battery (group_id, del_flag, sort_order)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='电池组电池号';
|
|
CREATE TABLE t_battery_group_field (
|
field_id BIGINT NOT NULL AUTO_INCREMENT,
|
field_name VARCHAR(100) NOT NULL,
|
field_type VARCHAR(20) NOT NULL,
|
status CHAR(1) NOT NULL DEFAULT '0',
|
del_flag TINYINT NOT NULL DEFAULT 0,
|
create_by VARCHAR(64) NOT NULL DEFAULT '',
|
create_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
update_by VARCHAR(64) NOT NULL DEFAULT '',
|
update_time DATETIME(3) NULL,
|
remark VARCHAR(500) NULL,
|
PRIMARY KEY (field_id),
|
UNIQUE KEY uk_battery_group_field_name_del (field_name, del_flag),
|
KEY idx_battery_group_field_status (status, del_flag)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='业务字段定义';
|
|
CREATE TABLE t_battery_group_template (
|
template_id BIGINT NOT NULL AUTO_INCREMENT,
|
template_name VARCHAR(100) NOT NULL,
|
create_by VARCHAR(64) NOT NULL DEFAULT '',
|
create_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
update_by VARCHAR(64) NOT NULL DEFAULT '',
|
update_time DATETIME(3) NULL,
|
PRIMARY KEY (template_id),
|
UNIQUE KEY uk_battery_group_template_name (template_name)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='电池组字段模板';
|
|
CREATE TABLE t_battery_group_template_field (
|
relation_id BIGINT NOT NULL AUTO_INCREMENT,
|
template_id BIGINT NOT NULL,
|
field_id BIGINT NOT NULL,
|
sort_order INT NOT NULL DEFAULT 0,
|
create_by VARCHAR(64) NOT NULL DEFAULT '',
|
create_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
del_flag TINYINT NOT NULL DEFAULT 0,
|
PRIMARY KEY (relation_id),
|
UNIQUE KEY uk_template_field_del (template_id, field_id, del_flag),
|
KEY idx_template_field_order (template_id, del_flag, sort_order)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='模板字段关系';
|
|
CREATE TABLE t_battery_group_relation_field (
|
relation_id BIGINT NOT NULL AUTO_INCREMENT,
|
group_id BIGINT NOT NULL,
|
template_id BIGINT NULL,
|
battery_group_field_id BIGINT NOT NULL,
|
field_value VARCHAR(500) NULL,
|
del_flag TINYINT NOT NULL DEFAULT 0,
|
PRIMARY KEY (relation_id),
|
UNIQUE KEY uk_group_field_del (group_id, battery_group_field_id, del_flag),
|
KEY idx_group_relation_field (group_id, del_flag)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='电池组当前字段值';
|
|
CREATE TABLE t_device_adapter (
|
adapter_id BIGINT NOT NULL AUTO_INCREMENT,
|
adapter_code VARCHAR(64) NOT NULL,
|
adapter_name VARCHAR(100) NOT NULL,
|
adapter_type VARCHAR(32) NOT NULL,
|
host VARCHAR(255) NOT NULL,
|
port INT NOT NULL,
|
poll_interval_ms INT NOT NULL DEFAULT 5000,
|
timezone_id VARCHAR(64) NOT NULL DEFAULT 'Asia/Hong_Kong',
|
read_only TINYINT(1) NOT NULL DEFAULT 1,
|
capabilities JSON NOT NULL,
|
connection_status VARCHAR(16) NOT NULL DEFAULT 'OFFLINE',
|
last_error_code VARCHAR(64) NULL,
|
last_error_message VARCHAR(500) NULL,
|
last_attempt_at_utc DATETIME(3) NULL,
|
last_observed_at_utc DATETIME(3) NULL,
|
status CHAR(1) NOT NULL DEFAULT '0',
|
del_flag TINYINT NOT NULL DEFAULT 0,
|
create_by VARCHAR(64) NOT NULL DEFAULT '',
|
create_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
update_by VARCHAR(64) NOT NULL DEFAULT '',
|
update_time DATETIME(3) NULL,
|
remark VARCHAR(500) NULL,
|
PRIMARY KEY (adapter_id),
|
UNIQUE KEY uk_adapter_code_del (adapter_code, del_flag),
|
KEY idx_adapter_connection (connection_status, status, del_flag)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='设备适配器实例';
|
|
CREATE TABLE t_detection_cabinet (
|
cabinet_id BIGINT NOT NULL AUTO_INCREMENT,
|
adapter_id BIGINT NOT NULL,
|
cabinet_code VARCHAR(64) NOT NULL,
|
cabinet_name VARCHAR(100) NOT NULL,
|
manufacturer VARCHAR(100) NULL,
|
model VARCHAR(100) NULL,
|
capacity DECIMAL(10,2) NOT NULL DEFAULT 1,
|
channel_count INT NOT NULL DEFAULT 1,
|
vendor_device_type INT NOT NULL,
|
vendor_device_id INT NOT NULL,
|
ip_address VARCHAR(45) NULL,
|
mac_address VARCHAR(64) NULL,
|
registration_status VARCHAR(16) NOT NULL DEFAULT 'REGISTERED',
|
online_status VARCHAR(16) NOT NULL DEFAULT 'OFFLINE',
|
last_seen_at_utc DATETIME(3) NULL,
|
status CHAR(1) NOT NULL DEFAULT '0',
|
del_flag TINYINT NOT NULL DEFAULT 0,
|
create_by VARCHAR(64) NOT NULL DEFAULT '',
|
create_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
update_by VARCHAR(64) NOT NULL DEFAULT '',
|
update_time DATETIME(3) NULL,
|
remark VARCHAR(500) NULL,
|
PRIMARY KEY (cabinet_id),
|
UNIQUE KEY uk_cabinet_code_del (cabinet_code, del_flag),
|
UNIQUE KEY uk_cabinet_vendor_identity (adapter_id, vendor_device_type, vendor_device_id, del_flag),
|
KEY idx_cabinet_status (registration_status, online_status, status, del_flag)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='检测柜';
|
|
CREATE TABLE t_detection_channel (
|
channel_id BIGINT NOT NULL AUTO_INCREMENT,
|
adapter_id BIGINT NOT NULL,
|
cabinet_id BIGINT NOT NULL,
|
devtype INT NOT NULL,
|
devid INT NOT NULL,
|
subdevid INT NOT NULL,
|
chlid INT NOT NULL,
|
display_no VARCHAR(64) NULL,
|
online_status VARCHAR(16) NOT NULL DEFAULT 'OFFLINE',
|
work_status VARCHAR(32) NOT NULL DEFAULT 'UNOBSERVABLE',
|
last_seen_at_utc DATETIME(3) NULL,
|
status CHAR(1) NOT NULL DEFAULT '0',
|
del_flag TINYINT NOT NULL DEFAULT 0,
|
create_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
update_time DATETIME(3) NULL,
|
PRIMARY KEY (channel_id),
|
UNIQUE KEY uk_channel_stable_identity (adapter_id, devtype, devid, subdevid, chlid, del_flag),
|
KEY idx_channel_cabinet (cabinet_id, status, del_flag),
|
KEY idx_channel_observed (online_status, work_status, last_seen_at_utc)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='检测柜通道事实';
|
|
CREATE TABLE t_collector (
|
collector_id BIGINT NOT NULL AUTO_INCREMENT,
|
collector_code VARCHAR(64) NOT NULL,
|
collector_name VARCHAR(100) NOT NULL,
|
adapter_id BIGINT NULL,
|
ip_address VARCHAR(45) NULL,
|
mac_address VARCHAR(64) NULL,
|
manufacturer VARCHAR(100) NULL,
|
model VARCHAR(100) NULL,
|
channel_count INT NOT NULL DEFAULT 0,
|
online_status VARCHAR(16) NOT NULL DEFAULT 'OFFLINE',
|
last_seen_at_utc DATETIME(3) NULL,
|
status CHAR(1) NOT NULL DEFAULT '0',
|
del_flag TINYINT NOT NULL DEFAULT 0,
|
create_by VARCHAR(64) NOT NULL DEFAULT '',
|
create_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
update_by VARCHAR(64) NOT NULL DEFAULT '',
|
update_time DATETIME(3) NULL,
|
remark VARCHAR(500) NULL,
|
PRIMARY KEY (collector_id),
|
UNIQUE KEY uk_collector_code_del (collector_code, del_flag),
|
KEY idx_collector_status (online_status, status, del_flag)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='采集器';
|
|
CREATE TABLE t_test_type (
|
test_type_id BIGINT NOT NULL AUTO_INCREMENT,
|
test_type_name VARCHAR(100) NOT NULL,
|
status CHAR(1) NOT NULL DEFAULT '0',
|
del_flag TINYINT NOT NULL DEFAULT 0,
|
create_by VARCHAR(64) NOT NULL DEFAULT '',
|
create_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
update_by VARCHAR(64) NOT NULL DEFAULT '',
|
update_time DATETIME(3) NULL,
|
remark VARCHAR(500) NULL,
|
PRIMARY KEY (test_type_id),
|
UNIQUE KEY uk_test_type_name_del (test_type_name, del_flag),
|
KEY idx_test_type_status (status, del_flag)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='测试类型';
|
|
CREATE TABLE t_test (
|
test_id BIGINT NOT NULL AUTO_INCREMENT,
|
test_name VARCHAR(100) NOT NULL,
|
creator_id BIGINT NOT NULL,
|
principal_id BIGINT NOT NULL,
|
test_type_id BIGINT NOT NULL,
|
test_purpose TEXT NOT NULL,
|
test_status VARCHAR(32) NOT NULL DEFAULT 'WAITING',
|
reception_status VARCHAR(32) NOT NULL DEFAULT 'NOT_STARTED',
|
monitor_timeout_minutes INT NOT NULL DEFAULT 30,
|
started_at_utc DATETIME(3) NULL,
|
ended_at_utc DATETIME(3) NULL,
|
logic_step_mappings_json JSON NULL,
|
console_detail_json JSON NULL,
|
del_flag TINYINT NOT NULL DEFAULT 0,
|
create_by VARCHAR(64) NOT NULL DEFAULT '',
|
create_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
update_by VARCHAR(64) NOT NULL DEFAULT '',
|
update_time DATETIME(3) NULL,
|
remark VARCHAR(500) NULL,
|
PRIMARY KEY (test_id),
|
UNIQUE KEY uk_test_name_del (test_name, del_flag),
|
KEY idx_test_status_time (test_status, reception_status, create_time),
|
KEY idx_test_principal (principal_id, test_status)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='测试';
|
|
CREATE TABLE t_test_battery (
|
test_battery_id BIGINT NOT NULL AUTO_INCREMENT,
|
test_id BIGINT NOT NULL,
|
group_id BIGINT NOT NULL,
|
battery_no VARCHAR(100) NOT NULL,
|
collector_id BIGINT NOT NULL,
|
del_flag TINYINT NOT NULL DEFAULT 0,
|
PRIMARY KEY (test_battery_id),
|
UNIQUE KEY uk_test_battery_no_del (test_id, battery_no, del_flag),
|
KEY idx_test_battery_group (group_id, del_flag),
|
KEY idx_test_battery_test (test_id, del_flag)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='测试电池号';
|
|
CREATE TABLE t_test_channel_binding (
|
binding_id BIGINT NOT NULL AUTO_INCREMENT,
|
test_id BIGINT NOT NULL,
|
test_battery_id BIGINT NOT NULL,
|
channel_id BIGINT NOT NULL,
|
expected_barcode VARCHAR(100) NULL,
|
claim_status VARCHAR(32) NOT NULL DEFAULT 'PENDING',
|
claimed_at_utc DATETIME(3) NULL,
|
released_at_utc DATETIME(3) NULL,
|
PRIMARY KEY (binding_id),
|
UNIQUE KEY uk_test_binding_battery (test_id, test_battery_id),
|
UNIQUE KEY uk_test_binding_channel (test_id, channel_id),
|
KEY idx_binding_channel (channel_id, claim_status)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='测试检测柜通道绑定';
|
|
CREATE TABLE t_test_collector_channel (
|
mapping_id BIGINT NOT NULL AUTO_INCREMENT,
|
test_id BIGINT NOT NULL,
|
test_battery_id BIGINT NOT NULL,
|
collector_id BIGINT NOT NULL,
|
measurement_code VARCHAR(100) NOT NULL,
|
measurement_name VARCHAR(100) NULL,
|
channel_code VARCHAR(100) NOT NULL,
|
PRIMARY KEY (mapping_id),
|
UNIQUE KEY uk_test_collector_measurement (test_id, test_battery_id, measurement_code),
|
UNIQUE KEY uk_test_collector_channel (test_id, collector_id, channel_code),
|
KEY idx_test_collector_battery (test_battery_id)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='测试采集器通道绑定';
|
|
CREATE TABLE t_test_warning_threshold (
|
threshold_id BIGINT NOT NULL AUTO_INCREMENT,
|
test_id BIGINT NOT NULL,
|
metric_code VARCHAR(100) NOT NULL,
|
min_value DECIMAL(30,12) NULL,
|
max_value DECIMAL(30,12) NULL,
|
unit VARCHAR(32) NOT NULL DEFAULT '',
|
severity VARCHAR(32) NOT NULL DEFAULT 'WARNING',
|
del_flag TINYINT NOT NULL DEFAULT 0,
|
PRIMARY KEY (threshold_id),
|
UNIQUE KEY uk_test_threshold (test_id, metric_code, del_flag),
|
KEY idx_threshold_test (test_id, severity)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='测试预警阈值';
|
|
CREATE TABLE t_test_realtime_record (
|
record_id BIGINT NOT NULL AUTO_INCREMENT,
|
test_id BIGINT NOT NULL,
|
test_battery_id BIGINT NOT NULL,
|
binding_id BIGINT NOT NULL,
|
adapter_code VARCHAR(64) NOT NULL,
|
cabinet_devtype INT NULL,
|
cabinet_devid INT NULL,
|
cabinet_subdevid INT NULL,
|
cabinet_chlid INT NULL,
|
cabinet_sequence_id BIGINT NULL,
|
cabinet_device_time_utc DATETIME(3) NULL,
|
cabinet_device_time_raw VARCHAR(64) NULL,
|
cabinet_adapter_timezone_id VARCHAR(64) NULL,
|
collector_id BIGINT NULL,
|
collector_channel_code VARCHAR(100) NULL,
|
collector_sequence_id BIGINT NULL,
|
collector_device_time_utc DATETIME(3) NULL,
|
collector_device_time_raw VARCHAR(64) NULL,
|
collector_adapter_timezone_id VARCHAR(64) NULL,
|
capture_slot_utc DATETIME(3) NOT NULL,
|
server_received_at_utc DATETIME(3) NOT NULL,
|
cycle_no INT NULL,
|
step_no INT NULL,
|
step_type VARCHAR(64) NULL,
|
step_name VARCHAR(191) NULL,
|
voltage_v DECIMAL(30,12) NULL,
|
current_a DECIMAL(30,12) NULL,
|
capacity_ah DECIMAL(30,12) NULL,
|
energy_wh DECIMAL(30,12) NULL,
|
temperature DECIMAL(30,12) NULL,
|
positive_potential DECIMAL(30,12) NULL,
|
negative_potential DECIMAL(30,12) NULL,
|
extension_measurements_json JSON NULL,
|
content_sha256 BINARY(32) NOT NULL,
|
PRIMARY KEY (record_id),
|
UNIQUE KEY uk_realtime_slot (test_id, test_battery_id, capture_slot_utc),
|
KEY idx_realtime_test_time (test_id, capture_slot_utc),
|
KEY idx_realtime_binding_time (binding_id, server_received_at_utc)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='实时采样记录';
|
|
CREATE TABLE t_test_step_record (
|
step_record_id BIGINT NOT NULL AUTO_INCREMENT,
|
test_id BIGINT NOT NULL,
|
test_battery_id BIGINT NOT NULL,
|
adapter_code VARCHAR(64) NOT NULL,
|
source_identity VARCHAR(256) NOT NULL,
|
source_record_id VARCHAR(128) NULL,
|
adapter_timezone_id VARCHAR(64) NOT NULL,
|
device_time_utc DATETIME(3) NOT NULL,
|
device_time_raw VARCHAR(64) NOT NULL,
|
cycle_no INT NOT NULL,
|
step_no INT NOT NULL,
|
step_type VARCHAR(64) NULL,
|
step_name VARCHAR(191) NULL,
|
voltage_v DECIMAL(30,12) NULL,
|
current_a DECIMAL(30,12) NULL,
|
capacity_ah DECIMAL(30,12) NULL,
|
energy_wh DECIMAL(30,12) NULL,
|
extension_measurements_json JSON NULL,
|
content_sha256 BINARY(32) NOT NULL,
|
PRIMARY KEY (step_record_id),
|
UNIQUE KEY uk_step_identity (test_battery_id, source_identity, source_record_id, device_time_utc, cycle_no, step_no),
|
KEY idx_step_test_order (test_id, test_battery_id, cycle_no, step_no)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='工步记录';
|
|
CREATE TABLE t_test_cycle_record (
|
cycle_record_id BIGINT NOT NULL AUTO_INCREMENT,
|
test_id BIGINT NOT NULL,
|
test_battery_id BIGINT NOT NULL,
|
adapter_code VARCHAR(64) NOT NULL,
|
source_identity VARCHAR(256) NOT NULL,
|
source_record_id VARCHAR(128) NULL,
|
adapter_timezone_id VARCHAR(64) NOT NULL,
|
device_time_utc DATETIME(3) NOT NULL,
|
device_time_raw VARCHAR(64) NOT NULL,
|
cycle_no INT NOT NULL,
|
charge_capacity_ah DECIMAL(30,12) NULL,
|
discharge_capacity_ah DECIMAL(30,12) NULL,
|
charge_energy_wh DECIMAL(30,12) NULL,
|
discharge_energy_wh DECIMAL(30,12) NULL,
|
efficiency DECIMAL(30,12) NULL,
|
extension_measurements_json JSON NULL,
|
content_sha256 BINARY(32) NOT NULL,
|
PRIMARY KEY (cycle_record_id),
|
UNIQUE KEY uk_cycle_identity (test_battery_id, source_identity, source_record_id, device_time_utc, cycle_no),
|
KEY idx_cycle_test_order (test_id, test_battery_id, cycle_no)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='循环记录';
|
|
CREATE TABLE t_test_logic_step_mapping (
|
mapping_id BIGINT NOT NULL AUTO_INCREMENT,
|
test_id BIGINT NOT NULL,
|
logical_no INT NOT NULL,
|
logical_name VARCHAR(191) NOT NULL,
|
start_step_no INT NOT NULL,
|
end_step_no INT NOT NULL,
|
PRIMARY KEY (mapping_id),
|
UNIQUE KEY uk_logic_step_no (test_id, logical_no),
|
KEY idx_logic_step_range (test_id, start_step_no, end_step_no)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='测试逻辑工步映射';
|
|
CREATE TABLE t_test_data_revision_action (
|
action_id BIGINT NOT NULL AUTO_INCREMENT,
|
test_id BIGINT NOT NULL,
|
data_type VARCHAR(16) NOT NULL,
|
operation_type VARCHAR(16) NOT NULL,
|
scope_summary VARCHAR(1000) NOT NULL,
|
identity_summary JSON NOT NULL,
|
before_content_json JSON NULL,
|
changes_summary JSON NULL,
|
reason VARCHAR(500) NOT NULL,
|
operator_id BIGINT NULL,
|
operator_name VARCHAR(64) NOT NULL,
|
created_at_utc DATETIME(3) NOT NULL,
|
PRIMARY KEY (action_id),
|
KEY idx_data_revision_test_time (test_id, data_type, created_at_utc)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='测试数据修订动作';
|
|
CREATE TABLE t_test_warning_exception (
|
exception_id BIGINT NOT NULL AUTO_INCREMENT,
|
test_id BIGINT NOT NULL,
|
test_battery_id BIGINT NULL,
|
threshold_id BIGINT NOT NULL,
|
source_type VARCHAR(32) NOT NULL,
|
source_identity VARCHAR(256) NOT NULL,
|
metric_code VARCHAR(100) NOT NULL,
|
abnormal_value DECIMAL(30,12) NULL,
|
unit VARCHAR(32) NOT NULL DEFAULT '',
|
rule_snapshot JSON NOT NULL,
|
severity VARCHAR(32) NOT NULL DEFAULT 'WARNING',
|
exception_status VARCHAR(16) NOT NULL DEFAULT 'OPEN',
|
first_occurred_at_utc DATETIME(3) NOT NULL,
|
last_occurred_at_utc DATETIME(3) NOT NULL,
|
occurrence_count BIGINT NOT NULL DEFAULT 1,
|
handled_by BIGINT NULL,
|
handled_at_utc DATETIME(3) NULL,
|
handling_note VARCHAR(1000) NULL,
|
open_dedupe_key VARCHAR(512) GENERATED ALWAYS AS
|
(CASE WHEN exception_status = 'OPEN'
|
THEN CONCAT(test_id, ':', threshold_id, ':', metric_code)
|
ELSE NULL END) STORED,
|
PRIMARY KEY (exception_id),
|
KEY idx_warning_exception_test (test_id, exception_status, last_occurred_at_utc),
|
UNIQUE KEY uk_warning_exception_open (open_dedupe_key)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='测试预警异常';
|
|
ALTER TABLE t_device_adapter
|
ADD CONSTRAINT chk_adapter_connection_status
|
CHECK (connection_status IN ('ONLINE', 'OFFLINE'));
|
ALTER TABLE t_detection_cabinet
|
ADD CONSTRAINT chk_cabinet_registration_status
|
CHECK (registration_status IN ('DISCOVERED', 'REGISTERED', 'DISABLED')),
|
ADD CONSTRAINT chk_cabinet_online_status
|
CHECK (online_status IN ('ONLINE', 'OFFLINE'));
|
ALTER TABLE t_detection_channel
|
ADD CONSTRAINT chk_channel_online_status
|
CHECK (online_status IN ('ONLINE', 'OFFLINE')),
|
ADD CONSTRAINT chk_channel_work_status
|
CHECK (work_status IN ('RUNNING', 'PAUSED', 'ENDED', 'ABNORMAL', 'UNOBSERVABLE'));
|
ALTER TABLE t_collector
|
ADD CONSTRAINT chk_collector_online_status
|
CHECK (online_status IN ('ONLINE', 'OFFLINE'));
|
ALTER TABLE t_test
|
ADD CONSTRAINT chk_test_status
|
CHECK (test_status IN ('WAITING', 'RUNNING', 'PAUSED', 'COMPLETED')),
|
ADD CONSTRAINT chk_test_reception_status
|
CHECK (reception_status IN ('NOT_STARTED', 'RECEIVING', 'PAUSED', 'ENDED'));
|
ALTER TABLE t_test_warning_exception
|
ADD CONSTRAINT chk_warning_exception_status
|
CHECK (exception_status IN ('OPEN', 'CLOSED'));
|
ALTER TABLE t_test_data_revision_action
|
ADD CONSTRAINT chk_data_revision_type
|
CHECK (data_type IN ('REALTIME', 'STEP', 'CYCLE')),
|
ADD CONSTRAINT chk_data_revision_operation
|
CHECK (operation_type IN ('EDIT', 'IMPORT', 'DELETE'));
|
