PermSqlIdempotencyCheck.java 5.0 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108
  1. import java.nio.file.*;
  2. import java.sql.*;
  3. import java.util.*;
  4. /** 阶段4 验收:sql/perm.sql 在 dev 库连续执行两遍,比对 9 张表行数与关键数据是否稳定。 */
  5. public class PermSqlIdempotencyCheck {
  6. static final String URL = "jdbc:postgresql://192.168.0.106:5432/zsjz-ai";
  7. static final String[] TABLES = {"perm_dept", "perm_user", "perm_post", "perm_role", "perm_menu",
  8. "perm_user_role", "perm_role_menu", "perm_role_dept", "perm_user_post"};
  9. public static void main(String[] args) throws Exception {
  10. String sql = Files.readString(Path.of(args.length > 0 ? args[0] : "sql/perm.sql"));
  11. List<String> stmts = split(sql);
  12. System.out.println("解析语句数: " + stmts.size());
  13. try (Connection c = DriverManager.getConnection(URL, "postgres", "postgres")) {
  14. c.setAutoCommit(true);
  15. Map<String, Long> before = counts(c);
  16. System.out.println("执行前行数: " + before);
  17. for (int round = 1; round <= 2; round++) {
  18. long t0 = System.currentTimeMillis();
  19. int ok = 0;
  20. for (String s : stmts) {
  21. try (Statement st = c.createStatement()) {
  22. st.execute(s);
  23. ok++;
  24. } catch (SQLException e) {
  25. System.out.println("第" + round + "遍失败语句: " + s.substring(0, Math.min(120, s.length())));
  26. throw e;
  27. }
  28. }
  29. Map<String, Long> after = counts(c);
  30. System.out.println("第" + round + "遍执行成功 " + ok + " 条, 耗时 "
  31. + (System.currentTimeMillis() - t0) + "ms, 行数: " + after);
  32. if (round == 2 && !after.equals(before)) {
  33. System.out.println("!! 第二遍后行数与执行前不一致(存在非幂等写入)");
  34. }
  35. }
  36. System.out.println("--- 关键数据校验 ---");
  37. query(c, "SELECT user_id, user_name, nick_name, status, del_flag, left(password,7) AS pwd_prefix FROM perm_user ORDER BY user_id");
  38. query(c, "SELECT ur.user_id, r.role_key FROM perm_user_role ur JOIN perm_role r ON r.role_id=ur.role_id ORDER BY ur.user_id");
  39. query(c, "SELECT (SELECT count(*) FROM perm_menu) AS menu_total, (SELECT count(*) FROM perm_role_menu WHERE role_id=1) AS superadmin_bound");
  40. query(c, "SELECT count(*) AS orphan_user_role FROM perm_user_role ur WHERE NOT EXISTS (SELECT 1 FROM perm_user u WHERE u.user_id=ur.user_id)");
  41. query(c, "SELECT count(*) AS orphan_role_menu FROM perm_role_menu rm WHERE NOT EXISTS (SELECT 1 FROM perm_menu m WHERE m.menu_id=rm.menu_id)");
  42. query(c, "SELECT count(*) AS sys_user_total, (SELECT count(*) FROM perm_user) AS perm_user_total FROM sys_user");
  43. }
  44. }
  45. static Map<String, Long> counts(Connection c) throws SQLException {
  46. Map<String, Long> m = new LinkedHashMap<>();
  47. for (String t : TABLES) {
  48. try (Statement st = c.createStatement(); ResultSet rs = st.executeQuery("SELECT count(*) FROM " + t)) {
  49. rs.next();
  50. m.put(t, rs.getLong(1));
  51. }
  52. }
  53. return m;
  54. }
  55. static void query(Connection c, String sql) throws SQLException {
  56. System.out.println("SQL> " + sql);
  57. try (Statement st = c.createStatement(); ResultSet rs = st.executeQuery(sql)) {
  58. ResultSetMetaData md = rs.getMetaData();
  59. while (rs.next()) {
  60. StringBuilder sb = new StringBuilder(" ");
  61. for (int i = 1; i <= md.getColumnCount(); i++) {
  62. sb.append(md.getColumnLabel(i)).append('=').append(rs.getString(i)).append(" ");
  63. }
  64. System.out.println(sb);
  65. }
  66. }
  67. }
  68. /** 去掉 -- 行注释后按分号切分,忽略单引号字符串内的分号 */
  69. static List<String> split(String sql) {
  70. StringBuilder noComments = new StringBuilder();
  71. for (String line : sql.split("\n")) {
  72. String trimmed = line.stripLeading();
  73. if (trimmed.startsWith("--")) {
  74. continue;
  75. }
  76. noComments.append(line).append('\n');
  77. }
  78. List<String> out = new ArrayList<>();
  79. StringBuilder cur = new StringBuilder();
  80. boolean inStr = false;
  81. String s = noComments.toString();
  82. for (int i = 0; i < s.length(); i++) {
  83. char ch = s.charAt(i);
  84. if (ch == '\'') {
  85. inStr = !inStr;
  86. }
  87. if (ch == ';' && !inStr) {
  88. String stmt = cur.toString().trim();
  89. if (!stmt.isEmpty()) {
  90. out.add(stmt);
  91. }
  92. cur.setLength(0);
  93. } else {
  94. cur.append(ch);
  95. }
  96. }
  97. if (!cur.toString().trim().isEmpty()) {
  98. out.add(cur.toString().trim());
  99. }
  100. return out;
  101. }
  102. }