import java.io.*;
|
import java.nio.charset.StandardCharsets;
|
import java.nio.file.*;
|
import java.security.MessageDigest;
|
import java.sql.*;
|
import java.time.LocalDateTime;
|
import java.time.format.DateTimeFormatter;
|
import java.util.*;
|
import java.util.zip.GZIPOutputStream;
|
|
/** Temporary, scoped administrator for the requested mom-thzynew data cleanup. */
|
public class TempDbCleanup {
|
private static final String DB = "mom-thzynew";
|
private static final Set<String> PRO_TABLES = Set.of(
|
"mes_pro_bag_code", "mes_pro_bag_code_replace", "mes_pro_batch",
|
"mes_pro_batch_worker", "mes_pro_pallet", "mes_pro_qr_export_task",
|
"mes_pro_scan_event");
|
private static final Set<String> KEEP = Set.of("mes_wm_warehouse", "mes_wm_warehouse_area",
|
"mes_wm_warehouse_location", "mes_pro_line");
|
|
public static void main(String[] args) throws Exception {
|
if (args.length < 1 || !Set.of("inventory", "backup", "clean").contains(args[0])
|
|| (args[0].equals("clean") ? args.length != 2 : args.length != 1)) {
|
throw new IllegalArgumentException("Usage: TempDbCleanup inventory|backup|clean <backup.sql.gz>");
|
}
|
String yaml = Files.readString(Path.of("yudao-server/src/main/resources/application-thzy.yaml"));
|
String block = yaml.substring(yaml.indexOf("master:"));
|
String user = null, pass = null;
|
for (String line : block.split("\\R")) {
|
if (line.trim().startsWith("username:")) user = value(line);
|
if (line.trim().startsWith("password:")) { pass = value(line); break; }
|
}
|
if (user == null || pass == null) throw new IllegalStateException("Missing database credentials");
|
String url = "jdbc:mysql://1.95.78.124:9000/" + DB
|
+ "?useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&useCursorFetch=true";
|
try (Connection conn = DriverManager.getConnection(url, user, pass)) {
|
if (!DB.equals(conn.getCatalog())) throw new IllegalStateException("Wrong database: " + conn.getCatalog());
|
List<String> tables = targetTables(conn);
|
System.out.println("DATABASE=" + DB + " TARGET_TABLES=" + tables.size());
|
if (args[0].equals("inventory")) inventory(conn, tables);
|
if (args[0].equals("backup")) backup(conn, tables);
|
if (args[0].equals("clean")) clean(conn, tables, Path.of(args[1]));
|
}
|
}
|
|
private static String value(String line) {
|
return line.trim().split(":", 2)[1].trim().split("\\s+#", 2)[0];
|
}
|
|
private static List<String> targetTables(Connection conn) throws SQLException {
|
List<String> names = new ArrayList<>();
|
try (Statement s = conn.createStatement(); ResultSet r = s.executeQuery(
|
"SELECT table_name FROM information_schema.tables WHERE table_schema='" + DB + "' "
|
+ "AND table_type='BASE TABLE' ORDER BY table_name")) {
|
while (r.next()) {
|
String t = r.getString(1);
|
if (KEEP.contains(t)) continue;
|
if (PRO_TABLES.contains(t) || t.startsWith("mes_qc_") || t.startsWith("mes_wm_")
|
|| t.startsWith("mes_trace_") || t.startsWith("crm_")) names.add(t);
|
}
|
}
|
return names;
|
}
|
|
private static long count(Connection conn, String table) throws SQLException {
|
try (Statement s = conn.createStatement(); ResultSet r = s.executeQuery("SELECT COUNT(*) FROM `" + table + "`")) {
|
r.next(); return r.getLong(1);
|
}
|
}
|
|
private static void inventory(Connection conn, List<String> tables) throws SQLException {
|
for (String t : tables) {
|
long n = count(conn, t);
|
if (n > 0) System.out.println(t + "=" + n);
|
}
|
for (String t : KEEP) System.out.println("KEEP " + t + "=" + count(conn, t));
|
try (Statement s = conn.createStatement(); ResultSet r = s.executeQuery(
|
"SELECT status, COUNT(*), SUM(total_count-finished_count) FROM mes_pro_qr_export_task "
|
+ "WHERE status IN (10,20) GROUP BY status")) {
|
while (r.next()) System.out.println("ACTIVE_EXPORT status=" + r.getInt(1)
|
+ " tasks=" + r.getLong(2) + " remaining=" + r.getLong(3));
|
}
|
}
|
|
private static void requireIdle(Connection conn) throws SQLException {
|
try (Statement s = conn.createStatement(); ResultSet r = s.executeQuery(
|
"SELECT COUNT(*) FROM mes_pro_qr_export_task WHERE status IN (10,20)")) {
|
r.next();
|
if (r.getLong(1) != 0) throw new IllegalStateException("QR export task still active; aborting");
|
}
|
}
|
|
private static void backup(Connection conn, List<String> tables) throws Exception {
|
requireIdle(conn);
|
Path dir = Path.of("db-backups");
|
Files.createDirectories(dir);
|
String stamp = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMdd-HHmmss"));
|
Path dest = dir.resolve(DB + "-business-cleanup-" + stamp + ".sql.gz");
|
Path temp = dir.resolve(dest.getFileName() + ".part");
|
long total = 0;
|
Properties manifest = new Properties();
|
manifest.setProperty("database", DB);
|
manifest.setProperty("table.count", Integer.toString(tables.size()));
|
conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
|
conn.setAutoCommit(false);
|
try (BufferedWriter out = new BufferedWriter(new OutputStreamWriter(
|
new GZIPOutputStream(Files.newOutputStream(temp), 1024 * 1024), StandardCharsets.UTF_8), 1024 * 1024)) {
|
out.write("-- mom-thzynew requested business-data backup; restore only to the intended database\n");
|
out.write("SET NAMES utf8mb4;\nSET sql_mode='';\n");
|
for (String table : tables) {
|
long rows = 0;
|
try (Statement s = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)) {
|
s.setFetchSize(1000);
|
try (ResultSet r = s.executeQuery("SELECT * FROM `" + table + "`")) {
|
ResultSetMetaData md = r.getMetaData();
|
int cols = md.getColumnCount();
|
StringJoiner columns = new StringJoiner(",");
|
for (int i = 1; i <= cols; i++) columns.add("`" + md.getColumnName(i) + "`");
|
String prefix = "INSERT INTO `" + table + "` (" + columns + ") VALUES (";
|
while (r.next()) {
|
out.write(prefix);
|
for (int i = 1; i <= cols; i++) {
|
if (i > 1) out.write(',');
|
writeSqlValue(out, r, md, i);
|
}
|
out.write(");\n");
|
rows++;
|
}
|
}
|
}
|
total += rows;
|
manifest.setProperty("row." + table, Long.toString(rows));
|
if (rows > 0) System.out.println("BACKED_UP " + table + "=" + rows);
|
}
|
out.write("-- backup complete: " + total + " rows\n");
|
conn.rollback();
|
} catch (Exception e) {
|
conn.rollback();
|
Files.deleteIfExists(temp);
|
throw e;
|
} finally {
|
conn.setAutoCommit(true);
|
}
|
Files.move(temp, dest, StandardCopyOption.ATOMIC_MOVE);
|
manifest.setProperty("sha256", sha256(dest));
|
manifest.setProperty("rows.total", Long.toString(total));
|
try (OutputStream m = Files.newOutputStream(Path.of(dest + ".manifest"))) {
|
manifest.store(m, "mom-thzynew business data backup manifest");
|
}
|
System.out.println("BACKUP_FILE=" + dest.toAbsolutePath() + " ROWS=" + total
|
+ " BYTES=" + Files.size(dest));
|
}
|
|
private static void writeSqlValue(Writer out, ResultSet r, ResultSetMetaData md, int i) throws Exception {
|
int type = md.getColumnType(i);
|
if (type == Types.BINARY || type == Types.VARBINARY || type == Types.LONGVARBINARY
|
|| type == Types.BLOB || type == Types.BIT) {
|
byte[] bytes = r.getBytes(i);
|
if (bytes == null) { out.write("NULL"); return; }
|
out.write("X'");
|
for (byte b : bytes) out.write(String.format("%02X", b & 0xff));
|
out.write("'");
|
return;
|
}
|
String value = r.getString(i);
|
if (value == null) { out.write("NULL"); return; }
|
if (type == Types.INTEGER || type == Types.BIGINT || type == Types.SMALLINT
|
|| type == Types.TINYINT || type == Types.DECIMAL || type == Types.NUMERIC
|
|| type == Types.DOUBLE || type == Types.FLOAT || type == Types.REAL) {
|
out.write(value); return;
|
}
|
out.write('\'');
|
for (int p = 0; p < value.length(); p++) {
|
char c = value.charAt(p);
|
switch (c) {
|
case '\\' -> out.write("\\\\");
|
case '\'' -> out.write("\\'");
|
case '\n' -> out.write("\\n");
|
case '\r' -> out.write("\\r");
|
case '\0' -> out.write("\\0");
|
case 26 -> out.write("\\Z");
|
default -> out.write(c);
|
}
|
}
|
out.write('\'');
|
}
|
|
private static String sha256(Path path) throws Exception {
|
MessageDigest digest = MessageDigest.getInstance("SHA-256");
|
try (InputStream in = Files.newInputStream(path)) {
|
byte[] buf = new byte[1024 * 1024];
|
int n;
|
while ((n = in.read(buf)) != -1) digest.update(buf, 0, n);
|
}
|
return HexFormat.of().formatHex(digest.digest());
|
}
|
|
private static void clean(Connection conn, List<String> tables, Path backup) throws Exception {
|
requireIdle(conn);
|
Properties manifest = new Properties();
|
try (InputStream in = Files.newInputStream(Path.of(backup + ".manifest"))) { manifest.load(in); }
|
if (!DB.equals(manifest.getProperty("database"))
|
|| tables.size() != Integer.parseInt(manifest.getProperty("table.count"))
|
|| !sha256(backup).equals(manifest.getProperty("sha256"))) {
|
throw new IllegalStateException("Backup integrity or target mismatch");
|
}
|
// Verify every table before any destructive statement. New writes cause a hard abort.
|
Map<String, Long> expected = new LinkedHashMap<>();
|
for (String table : tables) {
|
String raw = manifest.getProperty("row." + table);
|
if (raw == null) throw new IllegalStateException("Missing backup count: " + table);
|
long n = Long.parseLong(raw);
|
long now = count(conn, table);
|
if (n != now) throw new IllegalStateException("Table changed since backup: " + table
|
+ " backup=" + n + " current=" + now);
|
expected.put(table, n);
|
}
|
for (String table : List.of("mes_pro_line", "mes_wm_warehouse", "mes_wm_warehouse_area",
|
"mes_wm_warehouse_location")) {
|
System.out.println("PRESERVE " + table + "=" + count(conn, table));
|
}
|
for (Map.Entry<String, Long> entry : expected.entrySet()) {
|
if (entry.getValue() == 0) continue;
|
requireIdle(conn);
|
try (Statement s = conn.createStatement()) {
|
s.executeUpdate("TRUNCATE TABLE `" + entry.getKey() + "`");
|
}
|
System.out.println("CLEARED " + entry.getKey() + "=" + entry.getValue());
|
}
|
inventory(conn, tables);
|
}
|
}
|
