import java.nio.charset.StandardCharsets; import java.nio.file.Files; import java.nio.file.Path; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; import java.util.ArrayList; import java.util.List; /** * Apply lobster sys-only admin menus to master + tenant DB(s). * Usage: * java -cp ".;lib/mysql-connector-j-8.0.33.jar" ApplyLobsterSysOnlyAdminMenus [tenantDb] */ public class ApplyLobsterSysOnlyAdminMenus { private static final String HOST = "cq-cdb-8fjmemkb.sql.tencentcdb.com:27220"; private static final String USER = "root"; private static final String PASS = "Ylrz_1q2w3e4r5t6y"; private static final String MASTER_DB = "ylrz_saas"; private static final String SALES = "29900,32481,29949,29905,29950,29934,29906,3008,29912"; private static final String ADMIN_ONLY = "29925,29951,29963,29964,29907,29908,29909,29933,29965,29966"; private static final String ADMIN_DROP = "29947,29938,29944,29936,29946,29937"; public static void main(String[] args) throws Exception { Class.forName("com.mysql.cj.jdbc.Driver"); Path tenantSqlPath = Path.of("d:/ylrz_saas_new/java/fs-agent/src/main/resources/db/migration/tenant/V20260724_04__lobster_sys_only_admin_menus.sql"); Path masterSqlPath = Path.of("d:/ylrz_saas_new/java/sql/master/V20260724_04__lobster_sys_only_admin_menus.sql"); String tenantSql = readSql(tenantSqlPath); String masterSql = readSql(masterSqlPath); String tenantDb = args.length > 0 ? args[0] : "fs_tenant_cs1"; System.out.println("=== MASTER " + MASTER_DB + " ==="); try (Connection c = open(MASTER_DB)) { exec(c, masterSql); report(c, "tenant_sys_menu", "29900"); report(c, "tenant_company_menu", "29900"); } System.out.println("\n=== TENANT " + tenantDb + " ==="); try (Connection c = open(tenantDb)) { exec(c, tenantSql); report(c, "sys_menu", "29900"); report(c, "company_menu", "29900"); } writeReport(tenantDb); System.out.println("\nDone."); } private static String readSql(Path path) throws Exception { byte[] bytes = Files.readAllBytes(path); String sql = new String(bytes, StandardCharsets.UTF_8); if (!sql.isEmpty() && sql.charAt(0) == '') { sql = sql.substring(1); } return sql; } private static void exec(Connection c, String sql) throws Exception { try (Statement st = c.createStatement()) { st.execute(sql); } } private static void report(Connection c, String table, String rootId) throws Exception { System.out.println("\n--- " + table + " ---"); int sysCount = count(c, table, rootId, false); int salesCount = count(c, table, rootId, true); System.out.println("total under " + rootId + ": " + sysCount); System.out.println("sales visible leaf: " + salesCount); if ("sys_menu".equals(table) || "tenant_sys_menu".equals(table)) { listAdminOnly(c, table); } } private static int count(Connection c, String table, String rootId, boolean salesOnly) throws Exception { String sql = "SELECT COUNT(*) FROM " + table + " WHERE menu_id=" + rootId + " OR parent_id=" + rootId; if (salesOnly) { sql += " AND menu_type='C' AND visible='0' AND status='0' AND menu_id IN (" + SALES + ")"; } try (Statement st = c.createStatement(); ResultSet rs = st.executeQuery(sql)) { rs.next(); return rs.getInt(1); } } private static void listAdminOnly(Connection c, String table) throws Exception { String sql = "SELECT menu_id, menu_name, order_num, path FROM " + table + " WHERE parent_id=29900 AND menu_type='C' AND visible='0' AND status='0'" + " AND menu_id IN (" + ADMIN_ONLY + ") ORDER BY order_num, menu_id"; try (Statement st = c.createStatement(); ResultSet rs = st.executeQuery(sql)) { int n = 0; while (rs.next()) { n++; System.out.printf(" admin-only %2d. [%d] %s | %s%n", n, rs.getLong("menu_id"), rs.getString("menu_name"), rs.getString("path")); } System.out.println("admin-only count=" + n); } } private static void writeReport(String tenantDb) throws Exception { Path out = Path.of("d:/ylrz_saas_new/java/scripts/output/lobster-sys-only-admin-menus-report.txt"); Files.createDirectories(out.getParent()); List lines = new ArrayList<>(); lines.add("lobster sys-only admin menus applied"); lines.add("tenant=" + tenantDb); lines.add("sales_keep=" + SALES); lines.add("admin_only=" + ADMIN_ONLY); lines.add("admin_drop=" + ADMIN_DROP); try (Connection master = open(MASTER_DB); Connection tenant = open(tenantDb)) { lines.add(""); lines.add("=== MASTER tenant_sys_menu admin-only ==="); appendAdminLines(master, lines, "tenant_sys_menu"); lines.add(""); lines.add("=== MASTER tenant_company_menu (should be sales only) ==="); appendLeafLines(master, lines, "tenant_company_menu", SALES); lines.add(""); lines.add("=== TENANT sys_menu admin-only ==="); appendAdminLines(tenant, lines, "sys_menu"); lines.add(""); lines.add("=== TENANT company_menu (should be sales only) ==="); appendLeafLines(tenant, lines, "company_menu", SALES); } Files.write(out, lines, StandardCharsets.UTF_8); System.out.println("Report: " + out); } private static void appendAdminLines(Connection c, List lines, String table) throws Exception { String sql = "SELECT menu_id, menu_name, order_num, path FROM " + table + " WHERE parent_id=29900 AND menu_id IN (" + ADMIN_ONLY + ") ORDER BY order_num, menu_id"; try (Statement st = c.createStatement(); ResultSet rs = st.executeQuery(sql)) { while (rs.next()) { lines.add(String.format("[%d] %s | order=%d | %s", rs.getLong("menu_id"), rs.getString("menu_name"), rs.getInt("order_num"), rs.getString("path"))); } } } private static void appendLeafLines(Connection c, List lines, String table, String ids) throws Exception { String sql = "SELECT menu_id, menu_name, order_num, path FROM " + table + " WHERE parent_id=29900 AND menu_type='C' AND visible='0' AND status='0'" + " AND menu_id IN (" + ids + ") ORDER BY order_num, menu_id"; try (Statement st = c.createStatement(); ResultSet rs = st.executeQuery(sql)) { while (rs.next()) { lines.add(String.format("[%d] %s | order=%d | %s", rs.getLong("menu_id"), rs.getString("menu_name"), rs.getInt("order_num"), rs.getString("path"))); } } } private static Connection open(String db) throws Exception { String url = "jdbc:mysql://" + HOST + "/" + db + "?useSSL=false&serverTimezone=GMT%2B8&characterEncoding=utf8&allowMultiQueries=true"; return DriverManager.getConnection(url, USER, PASS); } }