ylrz_saas_pharmacy/java/scripts/ApplyVisibleCompanyIdsMigration.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class ApplyVisibleCompanyIdsMigration {

    private static final String DEFAULT_HOST = "cq-cdb-8fjmemkb.sql.tencentcdb.com";
    private static final int DEFAULT_PORT = 27220;
    private static final String DEFAULT_USER = "root";
    private static final String DEFAULT_PASSWORD = "Ylrz_1q2w3e4r5t6y";
    private static final String MASTER_DB = "ylrz_saas";

    private static final String CHECK_SQL =
            "SELECT COUNT(*) FROM information_schema.COLUMNS "
                    + "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'company_workflow_lobster' "
                    + "AND COLUMN_NAME = 'visible_company_ids'";

    private static final String ALTER_SQL =
            "ALTER TABLE company_workflow_lobster "
                    + "ADD COLUMN visible_company_ids VARCHAR(500) DEFAULT NULL AFTER company_id";

    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        List<TenantDb> tenants = new ArrayList<>();
        try (Connection master = connect(DEFAULT_HOST, DEFAULT_PORT, MASTER_DB, DEFAULT_USER, DEFAULT_PASSWORD)) {
            try (Statement st = master.createStatement();
                 ResultSet rs = st.executeQuery(
                         "SELECT id, db_name, db_url, db_account, db_pwd, status FROM tenant_info ORDER BY id")) {
                while (rs.next()) {
                    TenantDb t = new TenantDb();
                    t.id = rs.getLong("id");
                    t.status = rs.getInt("status");
                    t.dbName = rs.getString("db_name");
                    t.dbUrl = rs.getString("db_url");
                    t.user = rs.getString("db_account");
                    t.password = rs.getString("db_pwd");
                    parseJdbc(t);
                    tenants.add(t);
                }
            }
        }
        System.out.println("Tenants loaded: " + tenants.size());
        applyMaster();
        int ok = 0;
        int skip = 0;
        int fail = 0;
        for (TenantDb tenant : tenants) {
            if (tenant.status != 1) {
                continue;
            }
            try {
                if (apply(tenant)) {
                    ok++;
                } else {
                    skip++;
                }
            } catch (Exception e) {
                fail++;
                System.err.println("[FAIL] tenantId=" + tenant.id + " db=" + tenant.database + " - " + e.getMessage());
            }
        }
        System.out.println("Done. added=" + ok + " skip=" + skip + " fail=" + fail);
        if (fail > 0) {
            System.exit(1);
        }
    }

    private static void applyMaster() throws Exception {
        try (Connection conn = connect(DEFAULT_HOST, DEFAULT_PORT, MASTER_DB, DEFAULT_USER, DEFAULT_PASSWORD);
             Statement st = conn.createStatement()) {
            boolean exists;
            try (ResultSet rs = st.executeQuery(CHECK_SQL)) {
                rs.next();
                exists = rs.getInt(1) > 0;
            }
            if (exists) {
                System.out.println("[SKIP] master db=" + MASTER_DB);
                return;
            }
            st.execute(ALTER_SQL);
            System.out.println("[OK] master db=" + MASTER_DB);
        }
    }

    private static boolean apply(TenantDb tenant) throws Exception {
        try (Connection conn = connect(tenant.host, tenant.port, tenant.database, tenant.user, tenant.password);
             Statement st = conn.createStatement()) {
            boolean exists;
            try (ResultSet rs = st.executeQuery(CHECK_SQL)) {
                rs.next();
                exists = rs.getInt(1) > 0;
            }
            if (exists) {
                System.out.println("[SKIP] tenantId=" + tenant.id + " db=" + tenant.database);
                return false;
            }
            st.execute(ALTER_SQL);
            System.out.println("[OK] tenantId=" + tenant.id + " db=" + tenant.database);
            return true;
        }
    }

    private static void parseJdbc(TenantDb t) {
        if (t.dbUrl != null && t.dbUrl.startsWith("jdbc:mysql://")) {
            String body = t.dbUrl.substring("jdbc:mysql://".length());
            int slash = body.indexOf('/');
            String hostPort = slash >= 0 ? body.substring(0, slash) : body;
            String rest = slash >= 0 ? body.substring(slash + 1) : t.dbName;
            int q = rest.indexOf('?');
            t.database = q >= 0 ? rest.substring(0, q) : rest;
            int colon = hostPort.indexOf(':');
            if (colon >= 0) {
                t.host = hostPort.substring(0, colon);
                t.port = Integer.parseInt(hostPort.substring(colon + 1));
            } else {
                t.host = hostPort;
                t.port = DEFAULT_PORT;
            }
        } else {
            t.host = DEFAULT_HOST;
            t.port = DEFAULT_PORT;
            t.database = t.dbName;
        }
        if (t.user == null || t.user.isEmpty()) {
            t.user = DEFAULT_USER;
        }
        if (t.password == null || t.password.isEmpty()) {
            t.password = DEFAULT_PASSWORD;
        }
    }

    private static Connection connect(String host, int port, String database, String user, String password)
            throws SQLException {
        String url = String.format(
                "jdbc:mysql://%s:%d/%s?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=GMT%%2B8&allowMultiQueries=true",
                host, port, database);
        return DriverManager.getConnection(url, user, password);
    }

    private static final class TenantDb {
        long id;
        int status;
        String dbName;
        String dbUrl;
        String user;
        String password;
        String host;
        int port;
        String database;
    }
}