| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108 |
- import java.nio.file.*;
- import java.sql.*;
- import java.util.*;
- /** 阶段4 验收:sql/perm.sql 在 dev 库连续执行两遍,比对 9 张表行数与关键数据是否稳定。 */
- public class PermSqlIdempotencyCheck {
- static final String URL = "jdbc:postgresql://192.168.0.106:5432/zsjz-ai";
- static final String[] TABLES = {"perm_dept", "perm_user", "perm_post", "perm_role", "perm_menu",
- "perm_user_role", "perm_role_menu", "perm_role_dept", "perm_user_post"};
- public static void main(String[] args) throws Exception {
- String sql = Files.readString(Path.of(args.length > 0 ? args[0] : "sql/perm.sql"));
- List<String> stmts = split(sql);
- System.out.println("解析语句数: " + stmts.size());
- try (Connection c = DriverManager.getConnection(URL, "postgres", "postgres")) {
- c.setAutoCommit(true);
- Map<String, Long> before = counts(c);
- System.out.println("执行前行数: " + before);
- for (int round = 1; round <= 2; round++) {
- long t0 = System.currentTimeMillis();
- int ok = 0;
- for (String s : stmts) {
- try (Statement st = c.createStatement()) {
- st.execute(s);
- ok++;
- } catch (SQLException e) {
- System.out.println("第" + round + "遍失败语句: " + s.substring(0, Math.min(120, s.length())));
- throw e;
- }
- }
- Map<String, Long> after = counts(c);
- System.out.println("第" + round + "遍执行成功 " + ok + " 条, 耗时 "
- + (System.currentTimeMillis() - t0) + "ms, 行数: " + after);
- if (round == 2 && !after.equals(before)) {
- System.out.println("!! 第二遍后行数与执行前不一致(存在非幂等写入)");
- }
- }
- System.out.println("--- 关键数据校验 ---");
- 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");
- 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");
- query(c, "SELECT (SELECT count(*) FROM perm_menu) AS menu_total, (SELECT count(*) FROM perm_role_menu WHERE role_id=1) AS superadmin_bound");
- 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)");
- 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)");
- query(c, "SELECT count(*) AS sys_user_total, (SELECT count(*) FROM perm_user) AS perm_user_total FROM sys_user");
- }
- }
- static Map<String, Long> counts(Connection c) throws SQLException {
- Map<String, Long> m = new LinkedHashMap<>();
- for (String t : TABLES) {
- try (Statement st = c.createStatement(); ResultSet rs = st.executeQuery("SELECT count(*) FROM " + t)) {
- rs.next();
- m.put(t, rs.getLong(1));
- }
- }
- return m;
- }
- static void query(Connection c, String sql) throws SQLException {
- System.out.println("SQL> " + sql);
- try (Statement st = c.createStatement(); ResultSet rs = st.executeQuery(sql)) {
- ResultSetMetaData md = rs.getMetaData();
- while (rs.next()) {
- StringBuilder sb = new StringBuilder(" ");
- for (int i = 1; i <= md.getColumnCount(); i++) {
- sb.append(md.getColumnLabel(i)).append('=').append(rs.getString(i)).append(" ");
- }
- System.out.println(sb);
- }
- }
- }
- /** 去掉 -- 行注释后按分号切分,忽略单引号字符串内的分号 */
- static List<String> split(String sql) {
- StringBuilder noComments = new StringBuilder();
- for (String line : sql.split("\n")) {
- String trimmed = line.stripLeading();
- if (trimmed.startsWith("--")) {
- continue;
- }
- noComments.append(line).append('\n');
- }
- List<String> out = new ArrayList<>();
- StringBuilder cur = new StringBuilder();
- boolean inStr = false;
- String s = noComments.toString();
- for (int i = 0; i < s.length(); i++) {
- char ch = s.charAt(i);
- if (ch == '\'') {
- inStr = !inStr;
- }
- if (ch == ';' && !inStr) {
- String stmt = cur.toString().trim();
- if (!stmt.isEmpty()) {
- out.add(stmt);
- }
- cur.setLength(0);
- } else {
- cur.append(ch);
- }
- }
- if (!cur.toString().trim().isEmpty()) {
- out.add(cur.toString().trim());
- }
- return out;
- }
- }
|