import java.io.BufferedWriter;
|
import java.io.IOException;
|
import java.io.OutputStreamWriter;
|
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.ResultSetMetaData;
|
import java.sql.Statement;
|
import java.sql.Types;
|
import java.time.LocalDateTime;
|
import java.time.format.DateTimeFormatter;
|
import java.util.ArrayList;
|
import java.util.Arrays;
|
import java.util.List;
|
import java.util.zip.GZIPOutputStream;
|
|
/** One-time cleanup of the requested modules in mom-thzynew. No credentials are stored here. */
|
public class Cleanup {
|
private static final String DB = "mom-thzynew";
|
private static final String URL = "jdbc:mysql://1.95.78.124:9000/" + DB
|
+ "?useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&connectTimeout=5000";
|
private static final List<String> PRESERVED = List.of(
|
"mes_wm_warehouse", "mes_wm_warehouse_area", "mes_wm_warehouse_location", "mes_pro_line");
|
|
private static boolean selected(String table) {
|
if (PRESERVED.contains(table)) return false;
|
return table.startsWith("crm_")
|
|| table.startsWith("erp_sale_")
|
|| table.startsWith("mes_wm_")
|
|| table.startsWith("mes_qc_")
|
|| table.startsWith("mes_pro_bag_code")
|
|| table.startsWith("mes_pro_batch")
|
|| table.equals("mes_pro_pallet")
|
|| table.equals("mes_pro_scan_event")
|
|| table.equals("mes_trace_consumer_scan_event")
|
|| table.equals("mes_trace_cross_region_warning")
|
|| table.equals("mes_trace_event")
|
|| table.equals("mes_trace_integrity_record");
|
}
|
|
private static String password() throws IOException {
|
String yaml = Files.readString(Path.of("yudao-server/src/main/resources/application-thzy.yaml"));
|
return Arrays.stream(yaml.split("\\R")).map(String::trim)
|
.filter(line -> line.startsWith("password:"))
|
.findFirst().orElseThrow().substring("password:".length()).trim();
|
}
|
|
private static List<String> tables(Connection connection) throws Exception {
|
List<String> result = new ArrayList<>();
|
try (Statement statement = connection.createStatement();
|
ResultSet rows = statement.executeQuery("SELECT table_name FROM information_schema.tables "
|
+ "WHERE table_schema = DATABASE() AND table_type = 'BASE TABLE' ORDER BY table_name")) {
|
while (rows.next()) {
|
String table = rows.getString(1);
|
if (selected(table)) result.add(table);
|
}
|
}
|
return result;
|
}
|
|
private static long count(Connection connection, String table) throws Exception {
|
try (Statement statement = connection.createStatement();
|
ResultSet rows = statement.executeQuery("SELECT COUNT(*) FROM `" + table + "`")) {
|
rows.next();
|
return rows.getLong(1);
|
}
|
}
|
|
private static String hex(byte[] bytes) {
|
StringBuilder result = new StringBuilder(bytes.length * 2);
|
for (byte value : bytes) result.append(String.format("%02X", value & 0xff));
|
return result.toString();
|
}
|
|
private static String literal(ResultSet row, ResultSetMetaData meta, int column) throws Exception {
|
Object value = row.getObject(column);
|
if (value == null) return "NULL";
|
int type = meta.getColumnType(column);
|
if (type == Types.BINARY || type == Types.VARBINARY || type == Types.LONGVARBINARY
|
|| type == Types.BLOB || type == Types.BIT || type == Types.BOOLEAN) {
|
return "0x" + hex(row.getBytes(column));
|
}
|
if (type == Types.TINYINT || type == Types.SMALLINT || type == Types.INTEGER
|
|| type == Types.BIGINT || type == Types.FLOAT || type == Types.REAL
|
|| type == Types.DOUBLE || type == Types.DECIMAL || type == Types.NUMERIC) {
|
return row.getString(column);
|
}
|
return "CONVERT(0x" + hex(row.getString(column).getBytes(StandardCharsets.UTF_8)) + " USING utf8mb4)";
|
}
|
|
private static void backup(Connection connection, List<String> tables, Path destination) throws Exception {
|
Files.createDirectories(destination.getParent());
|
try (BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(
|
new GZIPOutputStream(Files.newOutputStream(destination)), StandardCharsets.UTF_8))) {
|
writer.write("-- Data backup before requested cleanup of " + DB + "\n");
|
writer.write("-- Restore only after reviewing target schema and current data.\n");
|
writer.write("SET NAMES utf8mb4;\n");
|
for (String table : tables) {
|
writer.write("-- " + table + "\n");
|
try (Statement statement = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY,
|
ResultSet.CONCUR_READ_ONLY)) {
|
statement.setFetchSize(Integer.MIN_VALUE);
|
try (ResultSet rows = statement.executeQuery("SELECT * FROM `" + table + "`")) {
|
ResultSetMetaData meta = rows.getMetaData();
|
StringBuilder columns = new StringBuilder();
|
for (int i = 1; i <= meta.getColumnCount(); i++) {
|
if (i > 1) columns.append(',');
|
columns.append('`').append(meta.getColumnName(i)).append('`');
|
}
|
while (rows.next()) {
|
writer.write("INSERT INTO `" + table + "` (" + columns + ") VALUES (");
|
for (int i = 1; i <= meta.getColumnCount(); i++) {
|
if (i > 1) writer.write(',');
|
writer.write(literal(rows, meta, i));
|
}
|
writer.write(");\n");
|
}
|
}
|
}
|
}
|
}
|
}
|
|
public static void main(String[] args) throws Exception {
|
if (args.length < 1 || !List.of("audit", "backup", "delete").contains(args[0])) {
|
throw new IllegalArgumentException("Usage: Cleanup audit|backup|delete [backup-path]");
|
}
|
try (Connection connection = DriverManager.getConnection(URL, "root", password())) {
|
if (!DB.equals(connection.getCatalog())) throw new IllegalStateException("Wrong database");
|
List<String> tables = tables(connection);
|
long total = 0;
|
for (String table : tables) {
|
long rows = count(connection, table);
|
total += rows;
|
if (rows > 0 || args[0].equals("audit")) System.out.println(table + "\t" + rows);
|
}
|
System.out.println("TABLES=" + tables.size() + " ROWS=" + total);
|
for (String preserved : PRESERVED) System.out.println("PRESERVED " + preserved + "=" + count(connection, preserved));
|
if (args[0].equals("audit")) return;
|
if (args.length != 2) throw new IllegalArgumentException("Backup path required");
|
Path backupPath = Path.of(args[1]).toAbsolutePath();
|
if (args[0].equals("backup")) {
|
if (Files.exists(backupPath)) throw new IllegalStateException("Backup already exists");
|
backup(connection, tables, backupPath);
|
System.out.println("BACKUP=" + backupPath + " BYTES=" + Files.size(backupPath));
|
return;
|
}
|
if (!Files.isRegularFile(backupPath) || Files.size(backupPath) == 0) {
|
throw new IllegalStateException("Backup is missing or empty");
|
}
|
if (total != 53982 || count(connection, "mes_wm_warehouse") != 5
|
|| count(connection, "mes_wm_warehouse_area") != 3
|
|| count(connection, "mes_wm_warehouse_location") != 4
|
|| count(connection, "mes_pro_line") != 3) {
|
throw new IllegalStateException("Database changed since backup; aborting cleanup");
|
}
|
connection.setAutoCommit(false);
|
try {
|
for (String table : tables) {
|
try (Statement statement = connection.createStatement()) {
|
System.out.println("DELETED " + table + "=" + statement.executeUpdate("DELETE FROM `" + table + "`"));
|
}
|
}
|
for (String table : tables) {
|
if (count(connection, table) != 0) throw new IllegalStateException("Not empty: " + table);
|
}
|
connection.commit();
|
} catch (Exception failure) {
|
connection.rollback();
|
throw failure;
|
}
|
System.out.println("COMMITTED " + LocalDateTime.now().format(DateTimeFormatter.ISO_LOCAL_DATE_TIME));
|
}
|
}
|
}
|
