fix: NEZ-3021 transfer 补充无用表删除逻辑upgrade2107to2202

3 files changed, 51 insertions, 0 deletions

diff --git a/src/main/java/net/geedge/handler/AfterHandler.java b/src/main/java/net/geedge/handler/AfterHandler.java
index 2bff276..f77ebb0 100644
--- a/src/main/java/net/geedge/handler/AfterHandler.java
+++ b/src/main/java/net/geedge/handler/AfterHandler.java
@@ -28,6 +28,9 @@ public class AfterHandler implements CommandLineRunner {
 	@Autowired
 	private SqlDao sqlDao;
 
+	@Autowired
+	private SqlHandler sqlHandler;
+
 	@Override
 	public void run(String... args) throws Exception {
 
@@ -47,6 +50,9 @@ public class AfterHandler implements CommandLineRunner {
 		sqlDao.execute(sb.toString());
 		log.info("remove table successful");
 
+		sqlHandler.dropTable();
+		log.info("remove useless table successful");
+
 		// nezha.properties database.pin
 		// 21.07 -> 22.02 pin 加密
 		String propsPath = "/opt/nezha/nz-web/config/nezha.properties";
diff --git a/src/main/java/net/geedge/handler/SqlHandler.java b/src/main/java/net/geedge/handler/SqlHandler.java
index 84011fa..dc5bbae 100644
--- a/src/main/java/net/geedge/handler/SqlHandler.java
+++ b/src/main/java/net/geedge/handler/SqlHandler.java
@@ -348,4 +348,8 @@ public class SqlHandler {
         log.info("transfer alert message data Run Time:{}", sw.prettyPrint());
     }
 
+    public void dropTable() throws SQLException {
+        Resource classPathResource = new ClassPathResource("db/dropUseLessTable.sql");
+        ScriptUtils.executeSqlScript(dataSource.getConnection(), classPathResource);
+    }
 }
diff --git a/src/main/resources/db/dropUseLessTable.sql b/src/main/resources/db/dropUseLessTable.sql
new file mode 100644
index 0000000..0a78934
--- /dev/null
+++ b/src/main/resources/db/dropUseLessTable.sql
@@ -0,0 +1,41 @@
+/**
+ * 删除历史数据升级后的无用表
+ *
+ */
+
+DROP TABLE IF EXISTS `account`;
+DROP TABLE IF EXISTS `alert_message`;
+DROP TABLE IF EXISTS `asset`;
+DROP TABLE IF EXISTS `asset_tag`;
+DROP TABLE IF EXISTS `chart`;
+DROP TABLE IF EXISTS `chart_element`;
+DROP TABLE IF EXISTS `conf_event`;
+DROP TABLE IF EXISTS `endpoint`;
+DROP TABLE IF EXISTS `endpoint_state`;
+DROP TABLE IF EXISTS `idc`;
+DROP TABLE IF EXISTS `idc_traffic`;
+DROP TABLE IF EXISTS `metadata_label`;
+DROP TABLE IF EXISTS `metadata_metric`;
+DROP TABLE IF EXISTS `model`;
+DROP TABLE IF EXISTS `module`;
+DROP TABLE IF EXISTS `notification_script`;
+DROP TABLE IF EXISTS `panel`;
+DROP TABLE IF EXISTS `project`;
+DROP TABLE IF EXISTS `qrtz_blob_triggers`;
+DROP TABLE IF EXISTS `qrtz_calendars`;
+DROP TABLE IF EXISTS `qrtz_cron_triggers`;
+DROP TABLE IF EXISTS `qrtz_fired_triggers`;
+SET foreign_key_checks = 0;
+DROP TABLE IF EXISTS `qrtz_job_details`;
+SET foreign_key_checks = 1;
+DROP TABLE IF EXISTS `qrtz_locks`;
+DROP TABLE IF EXISTS `qrtz_paused_trigger_grps`;
+DROP TABLE IF EXISTS `qrtz_scheduler_state`;
+DROP TABLE IF EXISTS `qrtz_simple_triggers`;
+DROP TABLE IF EXISTS `qrtz_simprop_triggers`;
+DROP TABLE IF EXISTS `qrtz_triggers`;
+DROP TABLE IF EXISTS `sys_area`;
+DROP TABLE IF EXISTS `terminal_log`;
+DROP TABLE IF EXISTS `user_group`;
+DROP TABLE IF EXISTS `user_group_rel`;
+DROP TABLE IF EXISTS `user_notification`;
\ No newline at end of file