ylrz_saas_pharmacy/java/scripts/ScanMasterAndAllTenantsLobster.java

import java.sql.*;

public class ScanMasterAndAllTenantsLobster {
    private static final String HOST = "cq-cdb-8fjmemkb.sql.tencentcdb.com";
    private static final int PORT = 27220;
    private static final String USER = "root";
    private static final String PWD = "Ylrz_1q2w3e4r5t6y";
    private static final String MASTER = "ylrz_saas";
    private static final String[] COLS = {
            "execution_mode", "enable_content_personalization", "enable_flow_personalization",
            "strict_fixed_workflow", "auto_start_instance_on_tag", "template_kind", "source_template_id"
    };

    public static void main(String[] a) throws Exception {
        checkDb("MASTER", MASTER, USER, PWD);
        String masterUrl = url(MASTER);
        try (Connection master = DriverManager.getConnection(masterUrl, USER, PWD)) {
            try (Statement st = master.createStatement();
                 ResultSet rs = st.executeQuery("SELECT id, db_url, db_account, db_pwd, status FROM tenant_info ORDER BY id")) {
                while (rs.next()) {
                    long id = rs.getLong("id");
                    int status = rs.getInt("status");
                    TenantDb t = parseJdbcUrl(rs.getString("db_url"), rs.getString("db_account"), rs.getString("db_pwd"));
                    try {
                        checkDb("tenantId=" + id + " status=" + status, t.database, t.user, t.password);
                    } catch (Exception e) {
                        System.out.println("[ERROR] tenantId=" + id + " db=" + t.database + " " + e.getMessage());
                    }
                }
            }
        }
    }

    private static void checkDb(String label, String db, String user, String pwd) throws SQLException {
        try (Connection c = DriverManager.getConnection(url(db), user, pwd)) {
            if (!tableExists(c, "company_workflow_lobster")) {
                System.out.println("[NO_TABLE] " + label + " db=" + db);
                return;
            }
            StringBuilder missing = new StringBuilder();
            for (String col : COLS) {
                if (!columnExists(c, "company_workflow_lobster", col)) {
                    if (missing.length() > 0) missing.append(',');
                    missing.append(col);
                }
            }
            if (missing.length() > 0) {
                System.out.println("[MISSING] " + label + " db=" + db + " cols=" + missing);
            }
        }
    }

    private static boolean tableExists(Connection c, String table) throws SQLException {
        try (PreparedStatement ps = c.prepareStatement(
                "SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=?")) {
            ps.setString(1, table);
            try (ResultSet rs = ps.executeQuery()) {
                return rs.next() && rs.getInt(1) > 0;
            }
        }
    }

    private static boolean columnExists(Connection c, String table, String column) throws SQLException {
        try (PreparedStatement ps = c.prepareStatement(
                "SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=? AND COLUMN_NAME=?")) {
            ps.setString(1, table);
            ps.setString(2, column);
            try (ResultSet rs = ps.executeQuery()) {
                return rs.next() && rs.getInt(1) > 0;
            }
        }
    }

    private static String url(String db) {
        return "jdbc:mysql://" + HOST + ":" + PORT + "/" + db
                + "?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&allowMultiQueries=true";
    }

    private static TenantDb parseJdbcUrl(String jdbcUrl, String user, String password) {
        String body = jdbcUrl.substring("jdbc:mysql://".length());
        int slash = body.indexOf('/');
        String hostPort = body.substring(0, slash);
        String rest = body.substring(slash + 1);
        int q = rest.indexOf('?');
        String database = q >= 0 ? rest.substring(0, q) : rest;
        int colon = hostPort.indexOf(':');
        TenantDb t = new TenantDb();
        t.database = database;
        t.user = user != null ? user : USER;
        t.password = password != null ? password : PWD;
        return t;
    }

    private static final class TenantDb {
        String database;
        String user;
        String password;
    }
}