387 lines
21 KiB
Java
387 lines
21 KiB
Java
package scripts.qa;
|
||
|
||
import java.io.File;
|
||
import java.io.FileWriter;
|
||
import java.nio.file.Files;
|
||
import java.nio.file.Paths;
|
||
import java.sql.Connection;
|
||
import java.sql.DriverManager;
|
||
import java.sql.PreparedStatement;
|
||
import java.sql.ResultSet;
|
||
import java.sql.Statement;
|
||
import java.util.ArrayList;
|
||
import java.util.HashMap;
|
||
import java.util.List;
|
||
import java.util.Map;
|
||
import java.util.regex.Matcher;
|
||
import java.util.regex.Pattern;
|
||
|
||
/**
|
||
* Kingbase 数据库只读安全断言执行器。
|
||
* 密码绝不硬编码，绝不输出到日志或报告中。
|
||
*/
|
||
public class DbAssertionChecker {
|
||
|
||
public static void main(String[] args) {
|
||
if (args.length == 0) {
|
||
System.err.println("Usage: java DbAssertionChecker <createdResourcesJsonPath> <outputDbAssertionsJsonPath>");
|
||
System.err.println(" or: java DbAssertionChecker --cleanup <createdResourcesJsonPath>");
|
||
System.exit(1);
|
||
}
|
||
|
||
if ("--cleanup".equals(args[0])) {
|
||
if (args.length < 2) {
|
||
System.err.println("Missing resources json path for cleanup");
|
||
System.exit(1);
|
||
}
|
||
executeExactIdCleanup(args[1]);
|
||
return;
|
||
}
|
||
|
||
if ("--cleanup-dependents".equals(args[0])) {
|
||
if (args.length < 2) {
|
||
System.err.println("Missing resources json path for cleanup-dependents");
|
||
System.exit(1);
|
||
}
|
||
executeDependentsCleanup(args[1]);
|
||
return;
|
||
}
|
||
|
||
if ("--disable-student".equals(args[0])) {
|
||
if (args.length < 2) {
|
||
System.err.println("Missing student id");
|
||
System.exit(1);
|
||
}
|
||
disableStudent(args[1]);
|
||
return;
|
||
}
|
||
|
||
if (args.length < 2) {
|
||
System.err.println("Usage: java DbAssertionChecker <createdResourcesJsonPath> <outputDbAssertionsJsonPath>");
|
||
System.exit(1);
|
||
}
|
||
|
||
String resourcesPath = args[0];
|
||
String outputPath = args[1];
|
||
|
||
// 解析密码：优先从环境变量，其次从 RuoYi-Vue/ruoyi-admin/src/main/resources/application-druid.yml 读取
|
||
String dbUrl = "jdbc:kingbase8://127.0.0.1:4321/training?currentSchema=public,sys_catalog&useUnicode=true&characterEncoding=utf8";
|
||
String dbUser = "kingbase";
|
||
String dbPassword = resolveDbPassword();
|
||
|
||
List<Map<String, Object>> assertions = new ArrayList<>();
|
||
boolean allPassed = true;
|
||
|
||
try (Connection conn = DriverManager.getConnection(dbUrl, dbUser, dbPassword)) {
|
||
// 确保 search_path
|
||
try (Statement s = conn.createStatement()) {
|
||
s.execute("SET search_path TO public, sys_catalog");
|
||
}
|
||
|
||
// 读取 created-resources.json 内容用于参数化断言
|
||
String resourcesJson = new String(Files.readAllBytes(Paths.get(resourcesPath)), "UTF-8");
|
||
|
||
// 1. 全局数据一致性断言：无重复 enrollment (duplicate enrollment = 0)
|
||
int duplicateEnrollments = queryCount(conn,
|
||
"SELECT COUNT(*) FROM (SELECT plan_id, student_id FROM train_enrollment GROUP BY plan_id, student_id HAVING COUNT(*) > 1) t");
|
||
boolean dupPass = duplicateEnrollments == 0;
|
||
assertions.add(createAssertion("CONSISTENCY-01", "重复报名关系数为0", "duplicate_enrollment_count == 0", "actual: " + duplicateEnrollments, dupPass));
|
||
if (!dupPass) allPassed = false;
|
||
|
||
// 2. 全局数据一致性断言：无孤立课次 (orphan lesson = 0)
|
||
int orphanLessons = queryCount(conn,
|
||
"SELECT COUNT(*) FROM train_lesson l LEFT JOIN train_plan_course pc ON l.plan_course_id = pc.plan_course_id WHERE pc.plan_course_id IS NULL");
|
||
boolean orphanLessonPass = orphanLessons == 0;
|
||
assertions.add(createAssertion("CONSISTENCY-02", "无孤立课次(关联无效课程)", "orphan_lesson_count == 0", "actual: " + orphanLessons, orphanLessonPass));
|
||
if (!orphanLessonPass) allPassed = false;
|
||
|
||
// 3. 全局数据一致性断言：无孤立报名 (orphan enrollment = 0)
|
||
int orphanEnrollments = queryCount(conn,
|
||
"SELECT COUNT(*) FROM train_enrollment e LEFT JOIN train_class_plan p ON e.plan_id = p.plan_id WHERE p.plan_id IS NULL");
|
||
boolean orphanEnrollPass = orphanEnrollments == 0;
|
||
assertions.add(createAssertion("CONSISTENCY-03", "无孤立报名记录(关联无效计划)", "orphan_enrollment_count == 0", "actual: " + orphanEnrollments, orphanEnrollPass));
|
||
if (!orphanEnrollPass) allPassed = false;
|
||
|
||
// 4. 全局数据一致性断言：场地互斥合法性 (invalid venue XOR = 0)
|
||
int invalidVenueXor = queryCount(conn,
|
||
"SELECT COUNT(*) FROM train_lesson WHERE (venue_type = 1 AND (class_room_id IS NULL OR site_id IS NOT NULL)) OR (venue_type = 2 AND (site_id IS NULL OR class_room_id IS NOT NULL))");
|
||
boolean venueXorPass = invalidVenueXor == 0;
|
||
assertions.add(createAssertion("CONSISTENCY-04", "场地二选一互斥合法性", "invalid_venue_xor_count == 0", "actual: " + invalidVenueXor, venueXorPass));
|
||
if (!venueXorPass) allPassed = false;
|
||
|
||
// 5. 业务场景断言：检查删除的 Board 其 del_flag 值为 '2' (逻辑删除)
|
||
int logicDeletedBoards = queryCount(conn,
|
||
"SELECT COUNT(*) FROM train_electronic_board WHERE del_flag = '2'");
|
||
boolean logicDelPass = logicDeletedBoards >= 1;
|
||
assertions.add(createAssertion("SCENARIO-BOARD-01", "电子班牌逻辑删除标记del_flag=2", "del_flag == '2'", "actual count: " + logicDeletedBoards, logicDelPass));
|
||
if (!logicDelPass) allPassed = false;
|
||
|
||
// 6. 业务场景断言：检查已结训计划的学员联动状态 = 4 且 complete_time 非空
|
||
int completedEnrollments = queryCount(conn,
|
||
"SELECT COUNT(*) FROM train_enrollment WHERE enrollment_status = 4 AND complete_time IS NOT NULL");
|
||
boolean completePass = completedEnrollments >= 1;
|
||
assertions.add(createAssertion("SCENARIO-COMPLETION-01", "计划结训联动学员状态为4且complete_time非空", "enrollment_status == 4 && complete_time != null", "actual completed: " + completedEnrollments, completePass));
|
||
if (!completePass) allPassed = false;
|
||
|
||
// 7. 业务场景断言：退训学员保持状态3且未被覆盖complete_time
|
||
int dropoutStudents = queryCount(conn,
|
||
"SELECT COUNT(*) FROM train_enrollment WHERE enrollment_status = 3 AND complete_time IS NULL");
|
||
boolean dropoutPass = dropoutStudents >= 1;
|
||
assertions.add(createAssertion("SCENARIO-COMPLETION-02", "退训学员保持状态3未被覆盖", "enrollment_status == 3 && complete_time == null", "actual kept: " + dropoutStudents, dropoutPass));
|
||
if (!dropoutPass) allPassed = false;
|
||
|
||
// 8. 业务场景断言：无孤立报到记录 (orphan report = 0)
|
||
int orphanReports = queryCount(conn,
|
||
"SELECT COUNT(*) FROM train_class_report r LEFT JOIN train_enrollment e ON r.enrollment_id = e.enrollment_id WHERE e.enrollment_id IS NULL");
|
||
boolean orphanReportPass = orphanReports == 0;
|
||
assertions.add(createAssertion("CONSISTENCY-05", "无孤立报到记录(关联无效报名)", "orphan_report_count == 0", "actual: " + orphanReports, orphanReportPass));
|
||
if (!orphanReportPass) allPassed = false;
|
||
|
||
// 9. 业务场景断言：无孤立考勤记录 (orphan checkin = 0)
|
||
int orphanCheckins = queryCount(conn,
|
||
"SELECT COUNT(*) FROM train_student_checkin c LEFT JOIN train_enrollment e ON c.enrollment_id = e.enrollment_id WHERE e.enrollment_id IS NULL");
|
||
boolean orphanCheckinPass = orphanCheckins == 0;
|
||
assertions.add(createAssertion("CONSISTENCY-06", "无孤立考勤记录(关联无效报名)", "orphan_checkin_count == 0", "actual: " + orphanCheckins, orphanCheckinPass));
|
||
if (!orphanCheckinPass) allPassed = false;
|
||
|
||
// 10. 业务场景断言：人工修正考勤留痕一致性 (manual_flag = 1 记录存在且状态有效 0~4)
|
||
int manualCheckins = queryCount(conn,
|
||
"SELECT COUNT(*) FROM train_student_checkin WHERE manual_flag = 1 AND attendance_status IN (0, 1, 2, 3, 4)");
|
||
boolean manualPass = manualCheckins >= 1;
|
||
assertions.add(createAssertion("SCENARIO-ATTENDANCE-01", "人工修正考勤留痕manual_flag=1且状态合法", "manual_flag == 1", "actual count: " + manualCheckins, manualPass));
|
||
if (!manualPass) allPassed = false;
|
||
|
||
// 11. 业务场景断言：报到记录状态合法性 (报到学员的 enrollment_status 应为 1 或 4，不能为 0 或 3)
|
||
int invalidReportEnrollments = queryCount(conn,
|
||
"SELECT COUNT(*) FROM train_class_report r JOIN train_enrollment e ON r.enrollment_id = e.enrollment_id WHERE e.enrollment_status NOT IN (1, 4)");
|
||
boolean validReportEnrollPass = invalidReportEnrollments == 0;
|
||
assertions.add(createAssertion("SCENARIO-REPORT-01", "报到学员关联报名状态必须为已报到(1)或已完成(4)", "invalid_report_enrollment_status == 0", "actual invalid: " + invalidReportEnrollments, validReportEnrollPass));
|
||
if (!validReportEnrollPass) allPassed = false;
|
||
|
||
} catch (Exception e) {
|
||
System.err.println("Database assertion execution failed: " + e.getMessage());
|
||
assertions.add(createAssertion("DB-ERROR", "数据库连接或查询异常", "no exception", "error: " + e.getMessage(), false));
|
||
allPassed = false;
|
||
}
|
||
|
||
// 写入 db-assertions.json
|
||
StringBuilder jsonBuilder = new StringBuilder();
|
||
jsonBuilder.append("{\n");
|
||
jsonBuilder.append(" \"status\": \"").append(allPassed ? "PASS" : "FAIL").append("\",\n");
|
||
jsonBuilder.append(" \"total\": ").append(assertions.size()).append(",\n");
|
||
long passCount = assertions.stream().filter(a -> Boolean.TRUE.equals(a.get("passed"))).count();
|
||
jsonBuilder.append(" \"passed\": ").append(passCount).append(",\n");
|
||
jsonBuilder.append(" \"failed\": ").append(assertions.size() - passCount).append(",\n");
|
||
jsonBuilder.append(" \"assertions\": [\n");
|
||
for (int i = 0; i < assertions.size(); i++) {
|
||
Map<String, Object> a = assertions.get(i);
|
||
jsonBuilder.append(" {\n");
|
||
jsonBuilder.append(" \"code\": \"").append(a.get("code")).append("\",\n");
|
||
jsonBuilder.append(" \"name\": \"").append(escapeJson((String) a.get("name"))).append("\",\n");
|
||
jsonBuilder.append(" \"rule\": \"").append(escapeJson((String) a.get("rule"))).append("\",\n");
|
||
jsonBuilder.append(" \"detail\": \"").append(escapeJson((String) a.get("detail"))).append("\",\n");
|
||
jsonBuilder.append(" \"passed\": ").append(a.get("passed")).append("\n");
|
||
jsonBuilder.append(" }").append(i < assertions.size() - 1 ? "," : "").append("\n");
|
||
}
|
||
jsonBuilder.append(" ]\n");
|
||
jsonBuilder.append("}\n");
|
||
|
||
try (FileWriter fw = new FileWriter(outputPath)) {
|
||
fw.write(jsonBuilder.toString());
|
||
} catch (Exception e) {
|
||
System.err.println("Failed to write output json: " + e.getMessage());
|
||
System.exit(1);
|
||
}
|
||
|
||
System.out.println("Kingbase DB Assertions completed. Status: " + (allPassed ? "PASS" : "FAIL") + ", Total: " + assertions.size());
|
||
if (!allPassed) {
|
||
System.exit(2);
|
||
}
|
||
}
|
||
|
||
private static int queryCount(Connection conn, String sql) throws Exception {
|
||
try (Statement s = conn.createStatement();
|
||
ResultSet rs = s.executeQuery(sql)) {
|
||
if (rs.next()) {
|
||
return rs.getInt(1);
|
||
}
|
||
return 0;
|
||
}
|
||
}
|
||
|
||
private static Map<String, Object> createAssertion(String code, String name, String rule, String detail, boolean passed) {
|
||
Map<String, Object> map = new HashMap<>();
|
||
map.put("code", code);
|
||
map.put("name", name);
|
||
map.put("rule", rule);
|
||
map.put("detail", detail);
|
||
map.put("passed", passed);
|
||
return map;
|
||
}
|
||
|
||
private static String resolveDbUrl() {
|
||
try {
|
||
File yml = new File("RuoYi-Vue/ruoyi-admin/src/main/resources/application-druid.yml");
|
||
if (!yml.exists()) {
|
||
yml = new File("../RuoYi-Vue/ruoyi-admin/src/main/resources/application-druid.yml");
|
||
}
|
||
if (yml.exists()) {
|
||
for (String line : Files.readAllLines(yml.toPath())) {
|
||
if (line.trim().startsWith("url:")) {
|
||
return line.trim().substring("url:".length()).trim();
|
||
}
|
||
}
|
||
}
|
||
} catch (Exception ignored) {}
|
||
return "jdbc:kingbase8://127.0.0.1:4321/railwaysystem_integrated_qa_20260916?currentSchema=public,sys_catalog&useUnicode=true&characterEncoding=utf8";
|
||
}
|
||
|
||
private static String resolveDbPassword() {
|
||
String env = System.getenv("DB_PASSWORD");
|
||
if (env != null && !env.isEmpty()) {
|
||
return env;
|
||
}
|
||
String kbEnv = System.getenv("KINGBASE_PASSWORD");
|
||
if (kbEnv != null && !kbEnv.isEmpty()) {
|
||
return kbEnv;
|
||
}
|
||
// 从 application-druid.yml 安全提取
|
||
try {
|
||
File yml = new File("RuoYi-Vue/ruoyi-admin/src/main/resources/application-druid.yml");
|
||
if (!yml.exists()) {
|
||
yml = new File("../RuoYi-Vue/ruoyi-admin/src/main/resources/application-druid.yml");
|
||
}
|
||
if (yml.exists()) {
|
||
List<String> lines = Files.readAllLines(yml.toPath());
|
||
boolean inMaster = false;
|
||
for (String line : lines) {
|
||
if (line.contains("master:")) inMaster = true;
|
||
if (inMaster && line.trim().startsWith("password:")) {
|
||
return line.trim().substring("password:".length()).trim();
|
||
}
|
||
}
|
||
}
|
||
} catch (Exception ignored) {
|
||
}
|
||
System.err.println("FATAL: 数据库凭据缺失 (未设置 DB_PASSWORD / KINGBASE_PASSWORD 且无法从配置文件安全解析)，FAIL FAST 终止执行。");
|
||
System.exit(1);
|
||
throw new IllegalStateException("DB password not configured");
|
||
}
|
||
|
||
private static String escapeJson(String s) {
|
||
if (s == null) return "";
|
||
return s.replace("\\", "\\\\").replace("\"", "\\\"").replace("\n", "\\n").replace("\r", "\\r");
|
||
}
|
||
|
||
private static void executeDependentsCleanup(String resourcesPath) {
|
||
String dbUrl = resolveDbUrl();
|
||
String dbUser = "kingbase";
|
||
String dbPassword = resolveDbPassword();
|
||
|
||
try {
|
||
String resourcesJson = new String(Files.readAllBytes(Paths.get(resourcesPath)), "UTF-8");
|
||
try (Connection conn = DriverManager.getConnection(dbUrl, dbUser, dbPassword)) {
|
||
try (Statement s = conn.createStatement()) {
|
||
s.execute("SET search_path TO public, sys_catalog");
|
||
}
|
||
deleteByIds(conn, "train_student_checkin", "checkin_id", extractIds(resourcesJson, "checkins"), true);
|
||
deleteByIds(conn, "train_class_report", "report_id", extractIds(resourcesJson, "reports"), true);
|
||
deleteByIds(conn, "train_student_checkin", "enrollment_id", extractIds(resourcesJson, "enrollments"), true);
|
||
deleteByIds(conn, "train_student_checkin", "lesson_id", extractIds(resourcesJson, "lessons"), true);
|
||
deleteByIds(conn, "train_class_report", "enrollment_id", extractIds(resourcesJson, "enrollments"), true);
|
||
System.out.println("DependentsCleanup completed successfully.");
|
||
}
|
||
} catch (Exception e) {
|
||
System.err.println("DependentsCleanup failed: " + e.getMessage());
|
||
}
|
||
}
|
||
|
||
private static void executeExactIdCleanup(String resourcesPath) {
|
||
String dbUrl = resolveDbUrl();
|
||
String dbUser = "kingbase";
|
||
String dbPassword = resolveDbPassword();
|
||
|
||
try {
|
||
String resourcesJson = new String(Files.readAllBytes(Paths.get(resourcesPath)), "UTF-8");
|
||
try (Connection conn = DriverManager.getConnection(dbUrl, dbUser, dbPassword)) {
|
||
try (Statement s = conn.createStatement()) {
|
||
s.execute("SET search_path TO public, sys_catalog");
|
||
}
|
||
|
||
// 优先删除外键从表依赖记录 (考勤、报到)
|
||
deleteByIds(conn, "train_student_checkin", "checkin_id", extractIds(resourcesJson, "checkins"), true);
|
||
deleteByIds(conn, "train_class_report", "report_id", extractIds(resourcesJson, "reports"), true);
|
||
deleteByIds(conn, "train_student_checkin", "enrollment_id", extractIds(resourcesJson, "enrollments"), true);
|
||
deleteByIds(conn, "train_student_checkin", "lesson_id", extractIds(resourcesJson, "lessons"), true);
|
||
deleteByIds(conn, "train_class_report", "enrollment_id", extractIds(resourcesJson, "enrollments"), true);
|
||
|
||
deleteByIds(conn, "train_lesson", "lesson_id", extractIds(resourcesJson, "lessons"), true);
|
||
deleteByIds(conn, "train_enrollment", "enrollment_id", extractIds(resourcesJson, "enrollments"), true);
|
||
deleteByIds(conn, "train_electronic_board", "board_id", extractIds(resourcesJson, "boards"), false);
|
||
deleteByIds(conn, "train_plan_course", "plan_course_id", extractIds(resourcesJson, "plan_courses"), true);
|
||
deleteByIds(conn, "train_class_plan", "plan_id", extractIds(resourcesJson, "plans"), false);
|
||
deleteByIds(conn, "train_classroom_info", "class_room_id", extractIds(resourcesJson, "classrooms"), false);
|
||
deleteByIds(conn, "train_training_site", "site_id", extractIds(resourcesJson, "sites"), false);
|
||
deleteByIds(conn, "train_student", "s_id", extractIds(resourcesJson, "students"), false);
|
||
deleteByIds(conn, "train_teacher", "t_id", extractIds(resourcesJson, "teachers"), false);
|
||
deleteByIds(conn, "train_course", "c_id", extractIds(resourcesJson, "courses"), false);
|
||
System.out.println("ExactIdCleanup completed successfully.");
|
||
}
|
||
} catch (Exception e) {
|
||
System.err.println("ExactIdCleanup failed: " + e.getMessage());
|
||
e.printStackTrace();
|
||
System.exit(1);
|
||
}
|
||
}
|
||
|
||
private static List<String> extractIds(String json, String key) {
|
||
List<String> list = new ArrayList<>();
|
||
Pattern p = Pattern.compile("\"" + key + "\":\\s*\\[(.*?)\\]", Pattern.DOTALL);
|
||
Matcher m = p.matcher(json);
|
||
if (m.find()) {
|
||
String arr = m.group(1);
|
||
Matcher im = Pattern.compile("\"([^\"]+)\"|([0-9]+)").matcher(arr);
|
||
while (im.find()) {
|
||
String val = im.group(1) != null ? im.group(1) : im.group(2);
|
||
if (val != null && !val.trim().isEmpty()) {
|
||
list.add(val.trim());
|
||
}
|
||
}
|
||
}
|
||
return list;
|
||
}
|
||
|
||
private static void deleteByIds(Connection conn, String table, String col, List<String> ids, boolean isNum) {
|
||
if (ids == null || ids.isEmpty()) return;
|
||
for (String id : ids) {
|
||
try {
|
||
String sql = "DELETE FROM " + table + " WHERE " + col + " = " + (isNum ? id : "'" + id + "'");
|
||
try (Statement s = conn.createStatement()) {
|
||
s.executeUpdate(sql);
|
||
}
|
||
} catch (Exception ex) {
|
||
System.err.println("Warn: delete from " + table + " where " + col + "=" + id + " failed: " + ex.getMessage());
|
||
}
|
||
}
|
||
}
|
||
|
||
private static void disableStudent(String studentId) {
|
||
String dbUrl = resolveDbUrl();
|
||
String dbUser = "kingbase";
|
||
String dbPassword = resolveDbPassword();
|
||
try (Connection conn = DriverManager.getConnection(dbUrl, dbUser, dbPassword)) {
|
||
try (Statement s = conn.createStatement()) {
|
||
s.execute("SET search_path TO public, sys_catalog");
|
||
}
|
||
try (PreparedStatement ps = conn.prepareStatement("UPDATE train_student SET status = 0 WHERE s_id = ?")) {
|
||
ps.setString(1, studentId);
|
||
ps.executeUpdate();
|
||
}
|
||
System.out.println("Student " + studentId + " disabled.");
|
||
} catch (Exception e) {
|
||
System.err.println("Failed to disable student: " + e.getMessage());
|
||
System.exit(1);
|
||
}
|
||
}
|
||
}
|