From ce8cf42d1c5b05fe8ea1427dcb9aad9af2589d1d Mon Sep 17 00:00:00 2001
From: yeg <1347747024@qq.com>
Date: 星期三, 26 八月 2026 13:09:32 +0800
Subject: [PATCH] feat: add v4 observation persistence constraints
---
ruoyi-admin/src/test/java/com/ruoyi/config/FlywayMigrationContractTest.java | 60 +++++++++++++++
ruoyi-admin/src/main/resources/db/migration/V4__observation_persistence_constraints.sql | 175 +++++++++++++++++++++++++++++++++++++++++++
2 files changed, 235 insertions(+), 0 deletions(-)
diff --git a/ruoyi-admin/src/main/resources/db/migration/V4__observation_persistence_constraints.sql b/ruoyi-admin/src/main/resources/db/migration/V4__observation_persistence_constraints.sql
new file mode 100644
index 0000000..de30937
--- /dev/null
+++ b/ruoyi-admin/src/main/resources/db/migration/V4__observation_persistence_constraints.sql
@@ -0,0 +1,175 @@
+-- 观测闭环持久化增量约束。
+-- 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);
diff --git a/ruoyi-admin/src/test/java/com/ruoyi/config/FlywayMigrationContractTest.java b/ruoyi-admin/src/test/java/com/ruoyi/config/FlywayMigrationContractTest.java
new file mode 100644
index 0000000..a7d9518
--- /dev/null
+++ b/ruoyi-admin/src/test/java/com/ruoyi/config/FlywayMigrationContractTest.java
@@ -0,0 +1,60 @@
+package com.ruoyi.config;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+import static org.junit.jupiter.api.Assertions.assertFalse;
+import static org.junit.jupiter.api.Assertions.assertTrue;
+
+import java.io.InputStream;
+import java.nio.charset.StandardCharsets;
+import java.security.MessageDigest;
+import java.util.HexFormat;
+
+import org.junit.jupiter.api.Test;
+
+class FlywayMigrationContractTest
+{
+ private static final String V1_SHA256 = "37de76546e5aa0aa0ce7e976b73383a2d80ef97fd2db923d9e23dbc2b34534c9";
+ private static final String V2_SHA256 = "698023958beee6245b099cf4505d35874cd06823e4dbbc3ddcb11b8f76609b89";
+ private static final String V3_SHA256 = "763de1dfaa96e6f07f7ed3b3975e6f281dbc0c2e7f139da3bed6a2f1bfc39727";
+
+ @Test
+ void keepsImmutableBaselineAndAddsObservationIncrementOnly()
+ {
+ assertEquals(V1_SHA256, sha256("db/migration/V1__ruoyi_platform_baseline.sql"));
+ assertEquals(V2_SHA256, sha256("db/migration/V2__battery_detection_domain.sql"));
+ assertEquals(V3_SHA256, sha256("db/migration/V3__battery_detection_permissions.sql"));
+
+ String v4 = resource("db/migration/V4__observation_persistence_constraints.sql");
+ assertTrue(v4.contains("ADD COLUMN creator_id"));
+ assertTrue(v4.contains("uk_exception_open_dedupe"));
+ assertTrue(v4.contains("adapter_timezone_id"));
+ assertTrue(v4.contains("fk_sample_channel"));
+ assertFalse(v4.matches("(?s).*\\bINSERT\\s+INTO\\b.*"));
+ }
+
+ private static String sha256(String path)
+ {
+ try
+ {
+ return HexFormat.of().formatHex(MessageDigest.getInstance("SHA-256")
+ .digest(resource(path).getBytes(StandardCharsets.UTF_8)));
+ }
+ catch (Exception exception)
+ {
+ throw new AssertionError("无法读取迁移资源 " + path, exception);
+ }
+ }
+
+ private static String resource(String path)
+ {
+ try (InputStream stream = FlywayMigrationContractTest.class.getClassLoader().getResourceAsStream(path))
+ {
+ if (stream == null) throw new AssertionError("缺少迁移资源 " + path);
+ return new String(stream.readAllBytes(), StandardCharsets.UTF_8);
+ }
+ catch (Exception exception)
+ {
+ throw new AssertionError("无法读取迁移资源 " + path, exception);
+ }
+ }
+}
--
Gitblit v1.7.1