This is an automated email from the ASF dual-hosted git repository.
gongchao pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/hertzbeat.git
The following commit(s) were added to refs/heads/master by this push:
new 9600a5e56f [improve] upgrade script merge (#3987)
9600a5e56f is described below
commit 9600a5e56fe065fa32178961d148e6717559bb3d
Author: Duansg <[email protected]>
AuthorDate: Mon Jan 19 23:47:32 2026 +0800
[improve] upgrade script merge (#3987)
---
.../db/migration/h2/V180__update_column.sql | 517 +++++++++++++++++++-
.../db/migration/h2/V181__update_indexes.sql | 534 ---------------------
.../db/migration/mysql/V180__update_column.sql | 435 ++++++++++++++++-
.../db/migration/mysql/V181__update_indexes.sql | 450 -----------------
.../migration/postgresql/V180__update_column.sql | 232 ++++++++-
.../migration/postgresql/V181__update_indexes.sql | 247 ----------
6 files changed, 1177 insertions(+), 1238 deletions(-)
diff --git
a/hertzbeat-startup/src/main/resources/db/migration/h2/V180__update_column.sql
b/hertzbeat-startup/src/main/resources/db/migration/h2/V180__update_column.sql
index 09a4c8e344..b770da958b 100644
---
a/hertzbeat-startup/src/main/resources/db/migration/h2/V180__update_column.sql
+++
b/hertzbeat-startup/src/main/resources/db/migration/h2/V180__update_column.sql
@@ -72,7 +72,7 @@ void migrateHistoryTable(java.sql.Connection conn) throws
java.sql.SQLException
try (java.sql.ResultSet rs = conn.getMetaData().getColumns(null, null,
"HZB_HISTORY", "METRIC_LABELS")) {
if (rs.next()) metricLabelsExists = true;
}
-
+
if (monitorIdExists) {
try (java.sql.Statement stmt = conn.createStatement()) {
if (!metricLabelsExists) {
@@ -92,3 +92,518 @@ void migrateHistoryTable(java.sql.Connection conn) throws
java.sql.SQLException
$$;
CALL MIGRATE_HISTORY_TABLE();
DROP ALIAS MIGRATE_HISTORY_TABLE;
+
+-- ========================================
+-- hzb_alert_define_monitor_bind table
+-- ========================================
+CREATE ALIAS UPDATE_ALERT_DEFINE_MONITOR_BIND_INDEXES AS $$
+void updateAlertDefineMonitorBindIndexes(java.sql.Connection conn) throws
java.sql.SQLException {
+ boolean tableExists = false;
+ try (java.sql.ResultSet rs = conn.getMetaData().getTables(null, null,
"HZB_ALERT_DEFINE_MONITOR_BIND", null)) {
+ if (rs.next()) tableExists = true;
+ }
+
+ if (tableExists) {
+ try (java.sql.Statement stmt = conn.createStatement()) {
+ // Drop old index if exists
+ try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_ALERT_DEFINE_MONITOR_BIND", false, false)) {
+ while (rs.next()) {
+ String indexName = rs.getString("INDEX_NAME");
+ if
("INDEX_ALERT_DEFINE_MONITOR".equalsIgnoreCase(indexName)) {
+ stmt.execute("DROP INDEX INDEX_ALERT_DEFINE_MONITOR");
+ break;
+ }
+ }
+ } catch (Exception e) {
+ // Index may not exist, continue
+ }
+
+ // Create new indexes if not exist
+ boolean alertDefineIdIndexExists = false;
+ boolean monitorIdIndexExists = false;
+
+ try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_ALERT_DEFINE_MONITOR_BIND", false, false)) {
+ while (rs.next()) {
+ String indexName = rs.getString("INDEX_NAME");
+ if ("IDX_ALERT_DEFINE_ID".equalsIgnoreCase(indexName)) {
+ alertDefineIdIndexExists = true;
+ }
+ if ("IDX_MONITOR_ID".equalsIgnoreCase(indexName)) {
+ monitorIdIndexExists = true;
+ }
+ }
+ }
+
+ if (!alertDefineIdIndexExists) {
+ stmt.execute("CREATE INDEX IDX_ALERT_DEFINE_ID ON
HZB_ALERT_DEFINE_MONITOR_BIND(ALERT_DEFINE_ID)");
+ }
+
+ if (!monitorIdIndexExists) {
+ stmt.execute("CREATE INDEX IDX_MONITOR_ID ON
HZB_ALERT_DEFINE_MONITOR_BIND(MONITOR_ID)");
+ }
+ }
+ }
+}
+$$;
+CALL UPDATE_ALERT_DEFINE_MONITOR_BIND_INDEXES();
+DROP ALIAS UPDATE_ALERT_DEFINE_MONITOR_BIND_INDEXES;
+
+-- ========================================
+-- hzb_collector_monitor_bind table
+-- ========================================
+CREATE ALIAS UPDATE_COLLECTOR_MONITOR_BIND_INDEXES AS $$
+void updateCollectorMonitorBindIndexes(java.sql.Connection conn) throws
java.sql.SQLException {
+ boolean tableExists = false;
+ try (java.sql.ResultSet rs = conn.getMetaData().getTables(null, null,
"HZB_COLLECTOR_MONITOR_BIND", null)) {
+ if (rs.next()) tableExists = true;
+ }
+
+ if (tableExists) {
+ try (java.sql.Statement stmt = conn.createStatement()) {
+ // Drop old index if exists
+ try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_COLLECTOR_MONITOR_BIND", false, false)) {
+ while (rs.next()) {
+ String indexName = rs.getString("INDEX_NAME");
+ if ("INDEX_COLLECTOR_MONITOR".equalsIgnoreCase(indexName))
{
+ stmt.execute("DROP INDEX INDEX_COLLECTOR_MONITOR");
+ break;
+ }
+ }
+ } catch (Exception e) {
+ // Index may not exist, continue
+ }
+
+ // Create new indexes if not exist
+ boolean collectorIndexExists = false;
+ boolean monitorIdIndexExists = false;
+
+ try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_COLLECTOR_MONITOR_BIND", false, false)) {
+ while (rs.next()) {
+ String indexName = rs.getString("INDEX_NAME");
+ if
("IDX_COLLECTOR_MONITOR_COLLECTOR".equalsIgnoreCase(indexName)) {
+ collectorIndexExists = true;
+ }
+ if
("IDX_COLLECTOR_MONITOR_MONITOR_ID".equalsIgnoreCase(indexName)) {
+ monitorIdIndexExists = true;
+ }
+ }
+ }
+
+ if (!collectorIndexExists) {
+ stmt.execute("CREATE INDEX IDX_COLLECTOR_MONITOR_COLLECTOR ON
HZB_COLLECTOR_MONITOR_BIND(COLLECTOR)");
+ }
+
+ if (!monitorIdIndexExists) {
+ stmt.execute("CREATE INDEX IDX_COLLECTOR_MONITOR_MONITOR_ID ON
HZB_COLLECTOR_MONITOR_BIND(MONITOR_ID)");
+ }
+ }
+ }
+}
+$$;
+CALL UPDATE_COLLECTOR_MONITOR_BIND_INDEXES();
+DROP ALIAS UPDATE_COLLECTOR_MONITOR_BIND_INDEXES;
+
+-- ========================================
+-- hzb_monitor table
+-- ========================================
+CREATE ALIAS UPDATE_MONITOR_INDEXES AS $$
+void updateMonitorIndexes(java.sql.Connection conn) throws
java.sql.SQLException {
+ boolean tableExists = false;
+ try (java.sql.ResultSet rs = conn.getMetaData().getTables(null, null,
"HZB_MONITOR", null)) {
+ if (rs.next()) tableExists = true;
+ }
+
+ if (tableExists) {
+ try (java.sql.Statement stmt = conn.createStatement()) {
+ // Drop old index if exists
+ try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_MONITOR", false, false)) {
+ while (rs.next()) {
+ String indexName = rs.getString("INDEX_NAME");
+ if ("MONITOR_QUERY_INDEX".equalsIgnoreCase(indexName)) {
+ stmt.execute("DROP INDEX MONITOR_QUERY_INDEX");
+ break;
+ }
+ }
+ } catch (Exception e) {
+ // Index may not exist, continue
+ }
+
+ // Create new indexes if not exist
+ boolean appIndexExists = false;
+ boolean instanceIndexExists = false;
+ boolean nameIndexExists = false;
+
+ try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_MONITOR", false, false)) {
+ while (rs.next()) {
+ String indexName = rs.getString("INDEX_NAME");
+ if ("IDX_HZB_MONITOR_APP".equalsIgnoreCase(indexName)) {
+ appIndexExists = true;
+ }
+ if
("IDX_HZB_MONITOR_INSTANCE".equalsIgnoreCase(indexName)) {
+ instanceIndexExists = true;
+ }
+ if ("IDX_HZB_MONITOR_NAME".equalsIgnoreCase(indexName)) {
+ nameIndexExists = true;
+ }
+ }
+ }
+
+ if (!appIndexExists) {
+ stmt.execute("CREATE INDEX IDX_HZB_MONITOR_APP ON
HZB_MONITOR(APP)");
+ }
+
+ if (!instanceIndexExists) {
+ stmt.execute("CREATE INDEX IDX_HZB_MONITOR_INSTANCE ON
HZB_MONITOR(INSTANCE)");
+ }
+
+ if (!nameIndexExists) {
+ stmt.execute("CREATE INDEX IDX_HZB_MONITOR_NAME ON
HZB_MONITOR(NAME)");
+ }
+ }
+ }
+}
+$$;
+CALL UPDATE_MONITOR_INDEXES();
+DROP ALIAS UPDATE_MONITOR_INDEXES;
+
+-- ========================================
+-- hzb_monitor_bind table
+-- ========================================
+CREATE ALIAS UPDATE_MONITOR_BIND_INDEXES AS $$
+void updateMonitorBindIndexes(java.sql.Connection conn) throws
java.sql.SQLException {
+ boolean tableExists = false;
+ try (java.sql.ResultSet rs = conn.getMetaData().getTables(null, null,
"HZB_MONITOR_BIND", null)) {
+ if (rs.next()) tableExists = true;
+ }
+
+ if (tableExists) {
+ try (java.sql.Statement stmt = conn.createStatement()) {
+ // Create new index if not exist
+ boolean bindIndexExists = false;
+
+ try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_MONITOR_BIND", false, false)) {
+ while (rs.next()) {
+ String indexName = rs.getString("INDEX_NAME");
+ if ("INDEX_MONITOR_BIND".equalsIgnoreCase(indexName)) {
+ bindIndexExists = true;
+ }
+ }
+ }
+
+ if (!bindIndexExists) {
+ stmt.execute("CREATE INDEX INDEX_MONITOR_BIND ON
HZB_MONITOR_BIND(BIZ_ID)");
+ }
+ }
+ }
+}
+$$;
+CALL UPDATE_MONITOR_BIND_INDEXES();
+DROP ALIAS UPDATE_MONITOR_BIND_INDEXES;
+
+-- ========================================
+-- hzb_status_page_incident_component_bind table
+-- Special handling: component_id might have auto-created index from FK
+-- ========================================
+CREATE ALIAS UPDATE_STATUS_PAGE_INCIDENT_COMPONENT_BIND_INDEXES AS $$
+void updateStatusPageIncidentComponentBindIndexes(java.sql.Connection conn)
throws java.sql.SQLException {
+ boolean tableExists = false;
+ try (java.sql.ResultSet rs = conn.getMetaData().getTables(null, null,
"HZB_STATUS_PAGE_INCIDENT_COMPONENT_BIND", null)) {
+ if (rs.next()) tableExists = true;
+ }
+
+ if (tableExists) {
+ try (java.sql.Statement stmt = conn.createStatement()) {
+ // Check if component_id column already has any index
+ boolean hasComponentIdIndex = false;
+ try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_STATUS_PAGE_INCIDENT_COMPONENT_BIND", false, false)) {
+ while (rs.next()) {
+ String columnName = rs.getString("COLUMN_NAME");
+ if (columnName != null &&
"COMPONENT_ID".equalsIgnoreCase(columnName)) {
+ hasComponentIdIndex = true;
+ break;
+ }
+ }
+ }
+
+ // Create index on component_id only if no index exists on this
column
+ if (!hasComponentIdIndex) {
+ stmt.execute("CREATE INDEX IDX_INCIDENT_COMPONENT_COMPONENT_ID
ON HZB_STATUS_PAGE_INCIDENT_COMPONENT_BIND(COMPONENT_ID)");
+ }
+ }
+ }
+}
+$$;
+CALL UPDATE_STATUS_PAGE_INCIDENT_COMPONENT_BIND_INDEXES();
+DROP ALIAS UPDATE_STATUS_PAGE_INCIDENT_COMPONENT_BIND_INDEXES;
+
+-- ========================================
+-- hzb_push_metrics table
+-- ========================================
+CREATE ALIAS UPDATE_PUSH_METRICS_INDEXES AS $$
+void updatePushMetricsIndexes(java.sql.Connection conn) throws
java.sql.SQLException {
+ boolean tableExists = false;
+ try (java.sql.ResultSet rs = conn.getMetaData().getTables(null, null,
"HZB_PUSH_METRICS", null)) {
+ if (rs.next()) tableExists = true;
+ }
+
+ if (tableExists) {
+ try (java.sql.Statement stmt = conn.createStatement()) {
+ // Drop old index if exists
+ try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_PUSH_METRICS", false, false)) {
+ while (rs.next()) {
+ String indexName = rs.getString("INDEX_NAME");
+ if ("PUSH_QUERY_INDEX".equalsIgnoreCase(indexName)) {
+ stmt.execute("DROP INDEX PUSH_QUERY_INDEX");
+ break;
+ }
+ }
+ } catch (Exception e) {
+ // Index may not exist, continue
+ }
+
+ // Create new indexes if not exist
+ boolean monitorIdIndexExists = false;
+ boolean timeIndexExists = false;
+
+ try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_PUSH_METRICS", false, false)) {
+ while (rs.next()) {
+ String indexName = rs.getString("INDEX_NAME");
+ if
("IDX_PUSH_METRICS_MONITOR_ID".equalsIgnoreCase(indexName)) {
+ monitorIdIndexExists = true;
+ }
+ if ("IDX_PUSH_METRICS_TIME".equalsIgnoreCase(indexName)) {
+ timeIndexExists = true;
+ }
+ }
+ }
+
+ if (!monitorIdIndexExists) {
+ stmt.execute("CREATE INDEX IDX_PUSH_METRICS_MONITOR_ID ON
HZB_PUSH_METRICS(MONITOR_ID)");
+ }
+
+ if (!timeIndexExists) {
+ stmt.execute("CREATE INDEX IDX_PUSH_METRICS_TIME ON
HZB_PUSH_METRICS(TIME)");
+ }
+ }
+ }
+}
+$$;
+CALL UPDATE_PUSH_METRICS_INDEXES();
+DROP ALIAS UPDATE_PUSH_METRICS_INDEXES;
+
+-- ========================================
+-- hzb_history table
+-- ========================================
+CREATE ALIAS UPDATE_HISTORY_INDEXES AS $$
+void updateHistoryIndexes(java.sql.Connection conn) throws
java.sql.SQLException {
+ boolean tableExists = false;
+ try (java.sql.ResultSet rs = conn.getMetaData().getTables(null, null,
"HZB_HISTORY", null)) {
+ if (rs.next()) tableExists = true;
+ }
+
+ if (tableExists) {
+ try (java.sql.Statement stmt = conn.createStatement()) {
+ // Drop old index if exists
+ try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_HISTORY", false, false)) {
+ while (rs.next()) {
+ String indexName = rs.getString("INDEX_NAME");
+ if ("HISTORY_QUERY_INDEX".equalsIgnoreCase(indexName)) {
+ stmt.execute("DROP INDEX HISTORY_QUERY_INDEX");
+ break;
+ }
+ }
+ } catch (Exception e) {
+ // Index may not exist, continue
+ }
+
+ // Create new indexes if not exist
+ boolean instanceIndexExists = false;
+ boolean appIndexExists = false;
+ boolean metricsIndexExists = false;
+ boolean metricIndexExists = false;
+
+ try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_HISTORY", false, false)) {
+ while (rs.next()) {
+ String indexName = rs.getString("INDEX_NAME");
+ if
("IDX_HZB_HISTORY_INSTANCE".equalsIgnoreCase(indexName)) {
+ instanceIndexExists = true;
+ }
+ if ("IDX_HZB_HISTORY_APP".equalsIgnoreCase(indexName)) {
+ appIndexExists = true;
+ }
+ if ("IDX_HZB_HISTORY_METRICS".equalsIgnoreCase(indexName))
{
+ metricsIndexExists = true;
+ }
+ if ("IDX_HZB_HISTORY_METRIC".equalsIgnoreCase(indexName)) {
+ metricIndexExists = true;
+ }
+ }
+ }
+
+ if (!instanceIndexExists) {
+ stmt.execute("CREATE INDEX IDX_HZB_HISTORY_INSTANCE ON
HZB_HISTORY(INSTANCE)");
+ }
+
+ if (!appIndexExists) {
+ stmt.execute("CREATE INDEX IDX_HZB_HISTORY_APP ON
HZB_HISTORY(APP)");
+ }
+
+ if (!metricsIndexExists) {
+ stmt.execute("CREATE INDEX IDX_HZB_HISTORY_METRICS ON
HZB_HISTORY(METRICS)");
+ }
+
+ if (!metricIndexExists) {
+ stmt.execute("CREATE INDEX IDX_HZB_HISTORY_METRIC ON
HZB_HISTORY(METRIC)");
+ }
+ }
+ }
+}
+$$;
+CALL UPDATE_HISTORY_INDEXES();
+DROP ALIAS UPDATE_HISTORY_INDEXES;
+
+-- ========================================
+-- hzb_param table
+-- ========================================
+CREATE ALIAS UPDATE_PARAM_TABLE_INDEXES AS $$
+void updateParamTableIndexes(java.sql.Connection conn) throws
java.sql.SQLException {
+ boolean tableExists = false;
+ try (java.sql.ResultSet rs = conn.getMetaData().getTables(null, null,
"HZB_PARAM", null)) {
+ if (rs.next()) tableExists = true;
+ }
+
+ if (tableExists) {
+ try (java.sql.Statement stmt = conn.createStatement()) {
+ // Drop old index if exists
+ try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_PARAM", false, false)) {
+ while (rs.next()) {
+ String indexName = rs.getString("INDEX_NAME");
+ if
("IDX_HZB_PARAM_MONITOR_ID".equalsIgnoreCase(indexName)) {
+ stmt.execute("DROP INDEX IDX_HZB_PARAM_MONITOR_ID");
+ break;
+ }
+ }
+ } catch (Exception e) {
+ // Index may not exist, continue
+ }
+
+ // Create new index if not exist
+ boolean indexExists = false;
+ try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_PARAM", false, false)) {
+ while (rs.next()) {
+ if
("IDX_HZB_PARAM_MONITOR_ID".equalsIgnoreCase(rs.getString("INDEX_NAME"))) {
+ indexExists = true;
+ break;
+ }
+ }
+ }
+ if (!indexExists) {
+ stmt.execute("CREATE INDEX IDX_HZB_PARAM_MONITOR_ID ON
HZB_PARAM(MONITOR_ID)");
+ }
+
+ // Drop old unique constraint if exists
+ try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_PARAM", true, false)) {
+ while (rs.next()) {
+ String indexName = rs.getString("INDEX_NAME");
+ if (indexName != null &&
indexName.equalsIgnoreCase("UK_HZB_PARAM_MONITOR_FIELD")) {
+ stmt.execute("DROP INDEX UK_HZB_PARAM_MONITOR_FIELD");
+ break;
+ }
+ }
+ } catch (Exception e) {
+ // Constraint may not exist, continue
+ }
+
+ // Create new unique constraint if not exist
+ boolean constraintExists = false;
+ try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_PARAM", true, false)) {
+ while (rs.next()) {
+ String indexName = rs.getString("INDEX_NAME");
+ if (indexName != null &&
indexName.equalsIgnoreCase("UK_HZB_PARAM_MONITOR_FIELD")) {
+ constraintExists = true;
+ break;
+ }
+ }
+ }
+ if (!constraintExists) {
+ stmt.execute("CREATE UNIQUE INDEX UK_HZB_PARAM_MONITOR_FIELD
ON HZB_PARAM(MONITOR_ID, FIELD)");
+ }
+ }
+ }
+}
+$$;
+CALL UPDATE_PARAM_TABLE_INDEXES();
+DROP ALIAS UPDATE_PARAM_TABLE_INDEXES;
+
+-- ========================================
+-- hzb_plugin_param table
+-- ========================================
+CREATE ALIAS UPDATE_PLUGIN_PARAM_TABLE_INDEXES AS $$
+void updatePluginParamTableIndexes(java.sql.Connection conn) throws
java.sql.SQLException {
+ boolean tableExists = false;
+ try (java.sql.ResultSet rs = conn.getMetaData().getTables(null, null,
"HZB_PLUGIN_PARAM", null)) {
+ if (rs.next()) tableExists = true;
+ }
+
+ if (tableExists) {
+ try (java.sql.Statement stmt = conn.createStatement()) {
+ // Drop old index if exists
+ try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_PLUGIN_PARAM", false, false)) {
+ while (rs.next()) {
+ String indexName = rs.getString("INDEX_NAME");
+ if
("IDX_HZB_PLUGIN_PARAM_PLUGIN_METADATA_ID".equalsIgnoreCase(indexName)) {
+ stmt.execute("DROP INDEX
IDX_HZB_PLUGIN_PARAM_PLUGIN_METADATA_ID");
+ break;
+ }
+ }
+ } catch (Exception e) {
+ // Index may not exist, continue
+ }
+
+ // Create new index if not exist
+ boolean indexExists = false;
+ try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_PLUGIN_PARAM", false, false)) {
+ while (rs.next()) {
+ if
("IDX_HZB_PLUGIN_PARAM_PLUGIN_METADATA_ID".equalsIgnoreCase(rs.getString("INDEX_NAME")))
{
+ indexExists = true;
+ break;
+ }
+ }
+ }
+ if (!indexExists) {
+ stmt.execute("CREATE INDEX
IDX_HZB_PLUGIN_PARAM_PLUGIN_METADATA_ID ON
HZB_PLUGIN_PARAM(PLUGIN_METADATA_ID)");
+ }
+
+ // Drop old unique constraint if exists
+ try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_PLUGIN_PARAM", true, false)) {
+ while (rs.next()) {
+ String indexName = rs.getString("INDEX_NAME");
+ if (indexName != null &&
indexName.equalsIgnoreCase("UK_HZB_PLUGIN_PARAM_METADATA_FIELD")) {
+ stmt.execute("DROP INDEX
UK_HZB_PLUGIN_PARAM_METADATA_FIELD");
+ break;
+ }
+ }
+ } catch (Exception e) {
+ // Constraint may not exist, continue
+ }
+
+ // Create new unique constraint if not exist
+ boolean constraintExists = false;
+ try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_PLUGIN_PARAM", true, false)) {
+ while (rs.next()) {
+ String indexName = rs.getString("INDEX_NAME");
+ if (indexName != null &&
indexName.equalsIgnoreCase("UK_HZB_PLUGIN_PARAM_METADATA_FIELD")) {
+ constraintExists = true;
+ break;
+ }
+ }
+ }
+ if (!constraintExists) {
+ stmt.execute("CREATE UNIQUE INDEX
UK_HZB_PLUGIN_PARAM_METADATA_FIELD ON HZB_PLUGIN_PARAM(PLUGIN_METADATA_ID,
FIELD)");
+ }
+ }
+ }
+}
+$$;
+CALL UPDATE_PLUGIN_PARAM_TABLE_INDEXES();
+DROP ALIAS UPDATE_PLUGIN_PARAM_TABLE_INDEXES;
diff --git
a/hertzbeat-startup/src/main/resources/db/migration/h2/V181__update_indexes.sql
b/hertzbeat-startup/src/main/resources/db/migration/h2/V181__update_indexes.sql
deleted file mode 100644
index dcf84b2794..0000000000
---
a/hertzbeat-startup/src/main/resources/db/migration/h2/V181__update_indexes.sql
+++ /dev/null
@@ -1,534 +0,0 @@
--- Licensed to the Apache Software Foundation (ASF) under one
--- or more contributor license agreements. See the NOTICE file
--- distributed with this work for additional information
--- regarding copyright ownership. The ASF licenses this file
--- to you under the Apache License, Version 2.0 (the
--- "License"); you may not use this file except in compliance
--- with the License. You may obtain a copy of the License at
---
--- http://www.apache.org/licenses/LICENSE-2.0
---
--- Unless required by applicable law or agreed to in writing,
--- software distributed under the License is distributed on an
--- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express
--- or implied. See the License for the specific language
--- governing permissions and limitations
--- under the License.
-
--- Ensure every SQL can rerun without error
--- This migration updates indexes to match the entity annotations
-
--- ========================================
--- hzb_alert_define_monitor_bind table
--- ========================================
-CREATE ALIAS UPDATE_ALERT_DEFINE_MONITOR_BIND_INDEXES AS $$
-void updateAlertDefineMonitorBindIndexes(java.sql.Connection conn) throws
java.sql.SQLException {
- boolean tableExists = false;
- try (java.sql.ResultSet rs = conn.getMetaData().getTables(null, null,
"HZB_ALERT_DEFINE_MONITOR_BIND", null)) {
- if (rs.next()) tableExists = true;
- }
-
- if (tableExists) {
- try (java.sql.Statement stmt = conn.createStatement()) {
- // Drop old index if exists
- try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_ALERT_DEFINE_MONITOR_BIND", false, false)) {
- while (rs.next()) {
- String indexName = rs.getString("INDEX_NAME");
- if
("INDEX_ALERT_DEFINE_MONITOR".equalsIgnoreCase(indexName)) {
- stmt.execute("DROP INDEX INDEX_ALERT_DEFINE_MONITOR");
- break;
- }
- }
- } catch (Exception e) {
- // Index may not exist, continue
- }
-
- // Create new indexes if not exist
- boolean alertDefineIdIndexExists = false;
- boolean monitorIdIndexExists = false;
-
- try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_ALERT_DEFINE_MONITOR_BIND", false, false)) {
- while (rs.next()) {
- String indexName = rs.getString("INDEX_NAME");
- if ("IDX_ALERT_DEFINE_ID".equalsIgnoreCase(indexName)) {
- alertDefineIdIndexExists = true;
- }
- if ("IDX_MONITOR_ID".equalsIgnoreCase(indexName)) {
- monitorIdIndexExists = true;
- }
- }
- }
-
- if (!alertDefineIdIndexExists) {
- stmt.execute("CREATE INDEX IDX_ALERT_DEFINE_ID ON
HZB_ALERT_DEFINE_MONITOR_BIND(ALERT_DEFINE_ID)");
- }
-
- if (!monitorIdIndexExists) {
- stmt.execute("CREATE INDEX IDX_MONITOR_ID ON
HZB_ALERT_DEFINE_MONITOR_BIND(MONITOR_ID)");
- }
- }
- }
-}
-$$;
-CALL UPDATE_ALERT_DEFINE_MONITOR_BIND_INDEXES();
-DROP ALIAS UPDATE_ALERT_DEFINE_MONITOR_BIND_INDEXES;
-
--- ========================================
--- hzb_collector_monitor_bind table
--- ========================================
-CREATE ALIAS UPDATE_COLLECTOR_MONITOR_BIND_INDEXES AS $$
-void updateCollectorMonitorBindIndexes(java.sql.Connection conn) throws
java.sql.SQLException {
- boolean tableExists = false;
- try (java.sql.ResultSet rs = conn.getMetaData().getTables(null, null,
"HZB_COLLECTOR_MONITOR_BIND", null)) {
- if (rs.next()) tableExists = true;
- }
-
- if (tableExists) {
- try (java.sql.Statement stmt = conn.createStatement()) {
- // Drop old index if exists
- try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_COLLECTOR_MONITOR_BIND", false, false)) {
- while (rs.next()) {
- String indexName = rs.getString("INDEX_NAME");
- if ("INDEX_COLLECTOR_MONITOR".equalsIgnoreCase(indexName))
{
- stmt.execute("DROP INDEX INDEX_COLLECTOR_MONITOR");
- break;
- }
- }
- } catch (Exception e) {
- // Index may not exist, continue
- }
-
- // Create new indexes if not exist
- boolean collectorIndexExists = false;
- boolean monitorIdIndexExists = false;
-
- try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_COLLECTOR_MONITOR_BIND", false, false)) {
- while (rs.next()) {
- String indexName = rs.getString("INDEX_NAME");
- if
("IDX_COLLECTOR_MONITOR_COLLECTOR".equalsIgnoreCase(indexName)) {
- collectorIndexExists = true;
- }
- if
("IDX_COLLECTOR_MONITOR_MONITOR_ID".equalsIgnoreCase(indexName)) {
- monitorIdIndexExists = true;
- }
- }
- }
-
- if (!collectorIndexExists) {
- stmt.execute("CREATE INDEX IDX_COLLECTOR_MONITOR_COLLECTOR ON
HZB_COLLECTOR_MONITOR_BIND(COLLECTOR)");
- }
-
- if (!monitorIdIndexExists) {
- stmt.execute("CREATE INDEX IDX_COLLECTOR_MONITOR_MONITOR_ID ON
HZB_COLLECTOR_MONITOR_BIND(MONITOR_ID)");
- }
- }
- }
-}
-$$;
-CALL UPDATE_COLLECTOR_MONITOR_BIND_INDEXES();
-DROP ALIAS UPDATE_COLLECTOR_MONITOR_BIND_INDEXES;
-
--- ========================================
--- hzb_monitor table
--- ========================================
-CREATE ALIAS UPDATE_MONITOR_INDEXES AS $$
-void updateMonitorIndexes(java.sql.Connection conn) throws
java.sql.SQLException {
- boolean tableExists = false;
- try (java.sql.ResultSet rs = conn.getMetaData().getTables(null, null,
"HZB_MONITOR", null)) {
- if (rs.next()) tableExists = true;
- }
-
- if (tableExists) {
- try (java.sql.Statement stmt = conn.createStatement()) {
- // Drop old index if exists
- try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_MONITOR", false, false)) {
- while (rs.next()) {
- String indexName = rs.getString("INDEX_NAME");
- if ("MONITOR_QUERY_INDEX".equalsIgnoreCase(indexName)) {
- stmt.execute("DROP INDEX MONITOR_QUERY_INDEX");
- break;
- }
- }
- } catch (Exception e) {
- // Index may not exist, continue
- }
-
- // Create new indexes if not exist
- boolean appIndexExists = false;
- boolean instanceIndexExists = false;
- boolean nameIndexExists = false;
-
- try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_MONITOR", false, false)) {
- while (rs.next()) {
- String indexName = rs.getString("INDEX_NAME");
- if ("IDX_HZB_MONITOR_APP".equalsIgnoreCase(indexName)) {
- appIndexExists = true;
- }
- if
("IDX_HZB_MONITOR_INSTANCE".equalsIgnoreCase(indexName)) {
- instanceIndexExists = true;
- }
- if ("IDX_HZB_MONITOR_NAME".equalsIgnoreCase(indexName)) {
- nameIndexExists = true;
- }
- }
- }
-
- if (!appIndexExists) {
- stmt.execute("CREATE INDEX IDX_HZB_MONITOR_APP ON
HZB_MONITOR(APP)");
- }
-
- if (!instanceIndexExists) {
- stmt.execute("CREATE INDEX IDX_HZB_MONITOR_INSTANCE ON
HZB_MONITOR(INSTANCE)");
- }
-
- if (!nameIndexExists) {
- stmt.execute("CREATE INDEX IDX_HZB_MONITOR_NAME ON
HZB_MONITOR(NAME)");
- }
- }
- }
-}
-$$;
-CALL UPDATE_MONITOR_INDEXES();
-DROP ALIAS UPDATE_MONITOR_INDEXES;
-
--- ========================================
--- hzb_monitor_bind table
--- ========================================
-CREATE ALIAS UPDATE_MONITOR_BIND_INDEXES AS $$
-void updateMonitorBindIndexes(java.sql.Connection conn) throws
java.sql.SQLException {
- boolean tableExists = false;
- try (java.sql.ResultSet rs = conn.getMetaData().getTables(null, null,
"HZB_MONITOR_BIND", null)) {
- if (rs.next()) tableExists = true;
- }
-
- if (tableExists) {
- try (java.sql.Statement stmt = conn.createStatement()) {
- // Create new index if not exist
- boolean bindIndexExists = false;
-
- try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_MONITOR_BIND", false, false)) {
- while (rs.next()) {
- String indexName = rs.getString("INDEX_NAME");
- if ("INDEX_MONITOR_BIND".equalsIgnoreCase(indexName)) {
- bindIndexExists = true;
- }
- }
- }
-
- if (!bindIndexExists) {
- stmt.execute("CREATE INDEX INDEX_MONITOR_BIND ON
HZB_MONITOR_BIND(BIZ_ID)");
- }
- }
- }
-}
-$$;
-CALL UPDATE_MONITOR_BIND_INDEXES();
-DROP ALIAS UPDATE_MONITOR_BIND_INDEXES;
-
--- ========================================
--- hzb_status_page_incident_component_bind table
--- Special handling: component_id might have auto-created index from FK
--- ========================================
-CREATE ALIAS UPDATE_STATUS_PAGE_INCIDENT_COMPONENT_BIND_INDEXES AS $$
-void updateStatusPageIncidentComponentBindIndexes(java.sql.Connection conn)
throws java.sql.SQLException {
- boolean tableExists = false;
- try (java.sql.ResultSet rs = conn.getMetaData().getTables(null, null,
"HZB_STATUS_PAGE_INCIDENT_COMPONENT_BIND", null)) {
- if (rs.next()) tableExists = true;
- }
-
- if (tableExists) {
- try (java.sql.Statement stmt = conn.createStatement()) {
- // Check if component_id column already has any index
- boolean hasComponentIdIndex = false;
- try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_STATUS_PAGE_INCIDENT_COMPONENT_BIND", false, false)) {
- while (rs.next()) {
- String columnName = rs.getString("COLUMN_NAME");
- if (columnName != null &&
"COMPONENT_ID".equalsIgnoreCase(columnName)) {
- hasComponentIdIndex = true;
- break;
- }
- }
- }
-
- // Create index on component_id only if no index exists on this
column
- if (!hasComponentIdIndex) {
- stmt.execute("CREATE INDEX IDX_INCIDENT_COMPONENT_COMPONENT_ID
ON HZB_STATUS_PAGE_INCIDENT_COMPONENT_BIND(COMPONENT_ID)");
- }
- }
- }
-}
-$$;
-CALL UPDATE_STATUS_PAGE_INCIDENT_COMPONENT_BIND_INDEXES();
-DROP ALIAS UPDATE_STATUS_PAGE_INCIDENT_COMPONENT_BIND_INDEXES;
-
--- ========================================
--- hzb_push_metrics table
--- ========================================
-CREATE ALIAS UPDATE_PUSH_METRICS_INDEXES AS $$
-void updatePushMetricsIndexes(java.sql.Connection conn) throws
java.sql.SQLException {
- boolean tableExists = false;
- try (java.sql.ResultSet rs = conn.getMetaData().getTables(null, null,
"HZB_PUSH_METRICS", null)) {
- if (rs.next()) tableExists = true;
- }
-
- if (tableExists) {
- try (java.sql.Statement stmt = conn.createStatement()) {
- // Drop old index if exists
- try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_PUSH_METRICS", false, false)) {
- while (rs.next()) {
- String indexName = rs.getString("INDEX_NAME");
- if ("PUSH_QUERY_INDEX".equalsIgnoreCase(indexName)) {
- stmt.execute("DROP INDEX PUSH_QUERY_INDEX");
- break;
- }
- }
- } catch (Exception e) {
- // Index may not exist, continue
- }
-
- // Create new indexes if not exist
- boolean monitorIdIndexExists = false;
- boolean timeIndexExists = false;
-
- try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_PUSH_METRICS", false, false)) {
- while (rs.next()) {
- String indexName = rs.getString("INDEX_NAME");
- if
("IDX_PUSH_METRICS_MONITOR_ID".equalsIgnoreCase(indexName)) {
- monitorIdIndexExists = true;
- }
- if ("IDX_PUSH_METRICS_TIME".equalsIgnoreCase(indexName)) {
- timeIndexExists = true;
- }
- }
- }
-
- if (!monitorIdIndexExists) {
- stmt.execute("CREATE INDEX IDX_PUSH_METRICS_MONITOR_ID ON
HZB_PUSH_METRICS(MONITOR_ID)");
- }
-
- if (!timeIndexExists) {
- stmt.execute("CREATE INDEX IDX_PUSH_METRICS_TIME ON
HZB_PUSH_METRICS(TIME)");
- }
- }
- }
-}
-$$;
-CALL UPDATE_PUSH_METRICS_INDEXES();
-DROP ALIAS UPDATE_PUSH_METRICS_INDEXES;
-
--- ========================================
--- hzb_history table
--- ========================================
-CREATE ALIAS UPDATE_HISTORY_INDEXES AS $$
-void updateHistoryIndexes(java.sql.Connection conn) throws
java.sql.SQLException {
- boolean tableExists = false;
- try (java.sql.ResultSet rs = conn.getMetaData().getTables(null, null,
"HZB_HISTORY", null)) {
- if (rs.next()) tableExists = true;
- }
-
- if (tableExists) {
- try (java.sql.Statement stmt = conn.createStatement()) {
- // Drop old index if exists
- try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_HISTORY", false, false)) {
- while (rs.next()) {
- String indexName = rs.getString("INDEX_NAME");
- if ("HISTORY_QUERY_INDEX".equalsIgnoreCase(indexName)) {
- stmt.execute("DROP INDEX HISTORY_QUERY_INDEX");
- break;
- }
- }
- } catch (Exception e) {
- // Index may not exist, continue
- }
-
- // Create new indexes if not exist
- boolean instanceIndexExists = false;
- boolean appIndexExists = false;
- boolean metricsIndexExists = false;
- boolean metricIndexExists = false;
-
- try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_HISTORY", false, false)) {
- while (rs.next()) {
- String indexName = rs.getString("INDEX_NAME");
- if
("IDX_HZB_HISTORY_INSTANCE".equalsIgnoreCase(indexName)) {
- instanceIndexExists = true;
- }
- if ("IDX_HZB_HISTORY_APP".equalsIgnoreCase(indexName)) {
- appIndexExists = true;
- }
- if ("IDX_HZB_HISTORY_METRICS".equalsIgnoreCase(indexName))
{
- metricsIndexExists = true;
- }
- if ("IDX_HZB_HISTORY_METRIC".equalsIgnoreCase(indexName)) {
- metricIndexExists = true;
- }
- }
- }
-
- if (!instanceIndexExists) {
- stmt.execute("CREATE INDEX IDX_HZB_HISTORY_INSTANCE ON
HZB_HISTORY(INSTANCE)");
- }
-
- if (!appIndexExists) {
- stmt.execute("CREATE INDEX IDX_HZB_HISTORY_APP ON
HZB_HISTORY(APP)");
- }
-
- if (!metricsIndexExists) {
- stmt.execute("CREATE INDEX IDX_HZB_HISTORY_METRICS ON
HZB_HISTORY(METRICS)");
- }
-
- if (!metricIndexExists) {
- stmt.execute("CREATE INDEX IDX_HZB_HISTORY_METRIC ON
HZB_HISTORY(METRIC)");
- }
- }
- }
-}
-$$;
-CALL UPDATE_HISTORY_INDEXES();
-DROP ALIAS UPDATE_HISTORY_INDEXES;
-
--- ========================================
--- hzb_param table
--- ========================================
-CREATE ALIAS UPDATE_PARAM_TABLE_INDEXES AS $$
-void updateParamTableIndexes(java.sql.Connection conn) throws
java.sql.SQLException {
- boolean tableExists = false;
- try (java.sql.ResultSet rs = conn.getMetaData().getTables(null, null,
"HZB_PARAM", null)) {
- if (rs.next()) tableExists = true;
- }
-
- if (tableExists) {
- try (java.sql.Statement stmt = conn.createStatement()) {
- // Drop old index if exists
- try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_PARAM", false, false)) {
- while (rs.next()) {
- String indexName = rs.getString("INDEX_NAME");
- if
("IDX_HZB_PARAM_MONITOR_ID".equalsIgnoreCase(indexName)) {
- stmt.execute("DROP INDEX IDX_HZB_PARAM_MONITOR_ID");
- break;
- }
- }
- } catch (Exception e) {
- // Index may not exist, continue
- }
-
- // Create new index if not exist
- boolean indexExists = false;
- try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_PARAM", false, false)) {
- while (rs.next()) {
- if
("IDX_HZB_PARAM_MONITOR_ID".equalsIgnoreCase(rs.getString("INDEX_NAME"))) {
- indexExists = true;
- break;
- }
- }
- }
- if (!indexExists) {
- stmt.execute("CREATE INDEX IDX_HZB_PARAM_MONITOR_ID ON
HZB_PARAM(MONITOR_ID)");
- }
-
- // Drop old unique constraint if exists
- try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_PARAM", true, false)) {
- while (rs.next()) {
- String indexName = rs.getString("INDEX_NAME");
- if (indexName != null &&
indexName.equalsIgnoreCase("UK_HZB_PARAM_MONITOR_FIELD")) {
- stmt.execute("DROP INDEX UK_HZB_PARAM_MONITOR_FIELD");
- break;
- }
- }
- } catch (Exception e) {
- // Constraint may not exist, continue
- }
-
- // Create new unique constraint if not exist
- boolean constraintExists = false;
- try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_PARAM", true, false)) {
- while (rs.next()) {
- String indexName = rs.getString("INDEX_NAME");
- if (indexName != null &&
indexName.equalsIgnoreCase("UK_HZB_PARAM_MONITOR_FIELD")) {
- constraintExists = true;
- break;
- }
- }
- }
- if (!constraintExists) {
- stmt.execute("CREATE UNIQUE INDEX UK_HZB_PARAM_MONITOR_FIELD
ON HZB_PARAM(MONITOR_ID, FIELD)");
- }
- }
- }
-}
-$$;
-CALL UPDATE_PARAM_TABLE_INDEXES();
-DROP ALIAS UPDATE_PARAM_TABLE_INDEXES;
-
--- ========================================
--- hzb_plugin_param table
--- ========================================
-CREATE ALIAS UPDATE_PLUGIN_PARAM_TABLE_INDEXES AS $$
-void updatePluginParamTableIndexes(java.sql.Connection conn) throws
java.sql.SQLException {
- boolean tableExists = false;
- try (java.sql.ResultSet rs = conn.getMetaData().getTables(null, null,
"HZB_PLUGIN_PARAM", null)) {
- if (rs.next()) tableExists = true;
- }
-
- if (tableExists) {
- try (java.sql.Statement stmt = conn.createStatement()) {
- // Drop old index if exists
- try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_PLUGIN_PARAM", false, false)) {
- while (rs.next()) {
- String indexName = rs.getString("INDEX_NAME");
- if
("IDX_HZB_PLUGIN_PARAM_PLUGIN_METADATA_ID".equalsIgnoreCase(indexName)) {
- stmt.execute("DROP INDEX
IDX_HZB_PLUGIN_PARAM_PLUGIN_METADATA_ID");
- break;
- }
- }
- } catch (Exception e) {
- // Index may not exist, continue
- }
-
- // Create new index if not exist
- boolean indexExists = false;
- try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_PLUGIN_PARAM", false, false)) {
- while (rs.next()) {
- if
("IDX_HZB_PLUGIN_PARAM_PLUGIN_METADATA_ID".equalsIgnoreCase(rs.getString("INDEX_NAME")))
{
- indexExists = true;
- break;
- }
- }
- }
- if (!indexExists) {
- stmt.execute("CREATE INDEX
IDX_HZB_PLUGIN_PARAM_PLUGIN_METADATA_ID ON
HZB_PLUGIN_PARAM(PLUGIN_METADATA_ID)");
- }
-
- // Drop old unique constraint if exists
- try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_PLUGIN_PARAM", true, false)) {
- while (rs.next()) {
- String indexName = rs.getString("INDEX_NAME");
- if (indexName != null &&
indexName.equalsIgnoreCase("UK_HZB_PLUGIN_PARAM_METADATA_FIELD")) {
- stmt.execute("DROP INDEX
UK_HZB_PLUGIN_PARAM_METADATA_FIELD");
- break;
- }
- }
- } catch (Exception e) {
- // Constraint may not exist, continue
- }
-
- // Create new unique constraint if not exist
- boolean constraintExists = false;
- try (java.sql.ResultSet rs = conn.getMetaData().getIndexInfo(null,
null, "HZB_PLUGIN_PARAM", true, false)) {
- while (rs.next()) {
- String indexName = rs.getString("INDEX_NAME");
- if (indexName != null &&
indexName.equalsIgnoreCase("UK_HZB_PLUGIN_PARAM_METADATA_FIELD")) {
- constraintExists = true;
- break;
- }
- }
- }
- if (!constraintExists) {
- stmt.execute("CREATE UNIQUE INDEX
UK_HZB_PLUGIN_PARAM_METADATA_FIELD ON HZB_PLUGIN_PARAM(PLUGIN_METADATA_ID,
FIELD)");
- }
- }
- }
-}
-$$;
-CALL UPDATE_PLUGIN_PARAM_TABLE_INDEXES();
-DROP ALIAS UPDATE_PLUGIN_PARAM_TABLE_INDEXES;
diff --git
a/hertzbeat-startup/src/main/resources/db/migration/mysql/V180__update_column.sql
b/hertzbeat-startup/src/main/resources/db/migration/mysql/V180__update_column.sql
index 2f7798360f..e542af106b 100644
---
a/hertzbeat-startup/src/main/resources/db/migration/mysql/V180__update_column.sql
+++
b/hertzbeat-startup/src/main/resources/db/migration/mysql/V180__update_column.sql
@@ -91,7 +91,7 @@ BEGIN
SELECT COUNT(*) INTO monitor_id_exists FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'hzb_history' AND COLUMN_NAME
= 'monitor_id';
SELECT COUNT(*) INTO metric_labels_exists FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'hzb_history' AND COLUMN_NAME
= 'metric_labels';
-
+
IF monitor_id_exists > 0 THEN
IF metric_labels_exists = 0 THEN
SET @sql_rename = 'ALTER TABLE hzb_history CHANGE instance
metric_labels VARCHAR(5000)';
@@ -111,9 +111,9 @@ BEGIN
EXECUTE stmt_resize;
DEALLOCATE PREPARE stmt_resize;
END IF;
-
+
UPDATE hzb_history h JOIN hzb_monitor m ON h.monitor_id = m.id SET
h.instance = m.instance;
-
+
SET @sql_drop = 'ALTER TABLE hzb_history DROP COLUMN monitor_id';
PREPARE stmt_drop FROM @sql_drop;
EXECUTE stmt_drop;
@@ -124,4 +124,433 @@ DELIMITER ;
CALL MigrateHistoryTable();
DROP PROCEDURE IF EXISTS MigrateHistoryTable;
+-- ========================================
+-- hzb_alert_define_monitor_bind table
+-- ========================================
+DELIMITER //
+CREATE PROCEDURE UpdateAlertDefineMonitorBindIndexes()
+BEGIN
+ DECLARE table_exists INT;
+
+ SELECT COUNT(*) INTO table_exists
+ FROM INFORMATION_SCHEMA.TABLES
+ WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME =
'hzb_alert_define_monitor_bind';
+
+ IF table_exists = 1 THEN
+ -- Drop old index if exists
+ IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_alert_define_monitor_bind'
+ AND INDEX_NAME = 'index_alert_define_monitor') THEN
+ SET @drop_sql = 'DROP INDEX index_alert_define_monitor ON
hzb_alert_define_monitor_bind';
+ PREPARE stmt FROM @drop_sql;
+ EXECUTE stmt;
+ DEALLOCATE PREPARE stmt;
+ END IF;
+
+ -- Create new indexes if not exist
+ IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_alert_define_monitor_bind'
+ AND INDEX_NAME = 'idx_alert_define_id') THEN
+ CREATE INDEX idx_alert_define_id ON
hzb_alert_define_monitor_bind(alert_define_id);
+ END IF;
+
+ IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_alert_define_monitor_bind'
+ AND INDEX_NAME = 'idx_monitor_id') THEN
+ CREATE INDEX idx_monitor_id ON
hzb_alert_define_monitor_bind(monitor_id);
+ END IF;
+ END IF;
+END //
+DELIMITER ;
+
+CALL UpdateAlertDefineMonitorBindIndexes();
+DROP PROCEDURE IF EXISTS UpdateAlertDefineMonitorBindIndexes;
+
+-- ========================================
+-- hzb_collector_monitor_bind table
+-- ========================================
+DELIMITER //
+CREATE PROCEDURE UpdateCollectorMonitorBindIndexes()
+BEGIN
+ DECLARE table_exists INT;
+
+ SELECT COUNT(*) INTO table_exists
+ FROM INFORMATION_SCHEMA.TABLES
+ WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME =
'hzb_collector_monitor_bind';
+
+ IF table_exists = 1 THEN
+ -- Drop old index if exists
+ IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_collector_monitor_bind'
+ AND INDEX_NAME = 'index_collector_monitor') THEN
+ SET @drop_sql = 'DROP INDEX index_collector_monitor ON
hzb_collector_monitor_bind';
+ PREPARE stmt FROM @drop_sql;
+ EXECUTE stmt;
+ DEALLOCATE PREPARE stmt;
+ END IF;
+
+ -- Create new indexes if not exist
+ IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_collector_monitor_bind'
+ AND INDEX_NAME = 'idx_collector_monitor_collector') THEN
+ CREATE INDEX idx_collector_monitor_collector ON
hzb_collector_monitor_bind(collector);
+ END IF;
+
+ IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_collector_monitor_bind'
+ AND INDEX_NAME = 'idx_collector_monitor_monitor_id') THEN
+ CREATE INDEX idx_collector_monitor_monitor_id ON
hzb_collector_monitor_bind(monitor_id);
+ END IF;
+ END IF;
+END //
+DELIMITER ;
+
+CALL UpdateCollectorMonitorBindIndexes();
+DROP PROCEDURE IF EXISTS UpdateCollectorMonitorBindIndexes;
+
+-- ========================================
+-- hzb_monitor table
+-- ========================================
+DELIMITER //
+CREATE PROCEDURE UpdateMonitorIndexes()
+BEGIN
+ DECLARE table_exists INT;
+
+ SELECT COUNT(*) INTO table_exists
+ FROM INFORMATION_SCHEMA.TABLES
+ WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'hzb_monitor';
+
+ IF table_exists = 1 THEN
+ -- Drop old index if exists
+ IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_monitor'
+ AND INDEX_NAME = 'monitor_query_index') THEN
+ SET @drop_sql = 'DROP INDEX monitor_query_index ON hzb_monitor';
+ PREPARE stmt FROM @drop_sql;
+ EXECUTE stmt;
+ DEALLOCATE PREPARE stmt;
+ END IF;
+
+ -- Create new indexes if not exist
+ IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_monitor'
+ AND INDEX_NAME = 'idx_hzb_monitor_app') THEN
+ CREATE INDEX idx_hzb_monitor_app ON hzb_monitor(app);
+ END IF;
+
+ IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_monitor'
+ AND INDEX_NAME = 'idx_hzb_monitor_instance') THEN
+ CREATE INDEX idx_hzb_monitor_instance ON hzb_monitor(instance);
+ END IF;
+
+ IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_monitor'
+ AND INDEX_NAME = 'idx_hzb_monitor_name') THEN
+ CREATE INDEX idx_hzb_monitor_name ON hzb_monitor(name);
+ END IF;
+ END IF;
+END //
+DELIMITER ;
+
+CALL UpdateMonitorIndexes();
+DROP PROCEDURE IF EXISTS UpdateMonitorIndexes;
+
+-- ========================================
+-- hzb_monitor_bind table
+-- ========================================
+DELIMITER //
+CREATE PROCEDURE UpdateMonitorBindIndexes()
+BEGIN
+ DECLARE table_exists INT;
+
+ SELECT COUNT(*) INTO table_exists
+ FROM INFORMATION_SCHEMA.TABLES
+ WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'hzb_monitor_bind';
+
+ IF table_exists = 1 THEN
+ -- Create new index if not exist
+ IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_monitor_bind'
+ AND INDEX_NAME = 'index_monitor_bind') THEN
+ CREATE INDEX index_monitor_bind ON hzb_monitor_bind(biz_id);
+ END IF;
+ END IF;
+END //
+DELIMITER ;
+
+CALL UpdateMonitorBindIndexes();
+DROP PROCEDURE IF EXISTS UpdateMonitorBindIndexes;
+
+-- ========================================
+-- hzb_status_page_incident_component_bind table
+-- Special handling: component_id might have auto-created index from FK
+-- ========================================
+DELIMITER //
+CREATE PROCEDURE UpdateStatusPageIncidentComponentBindIndexes()
+BEGIN
+ DECLARE table_exists INT;
+ DECLARE component_id_has_index INT;
+
+ SELECT COUNT(*) INTO table_exists
+ FROM INFORMATION_SCHEMA.TABLES
+ WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME =
'hzb_status_page_incident_component_bind';
+
+ IF table_exists = 1 THEN
+ -- Check if component_id column already has any index (including
auto-created by FK)
+ SELECT COUNT(*) INTO component_id_has_index
+ FROM INFORMATION_SCHEMA.STATISTICS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_status_page_incident_component_bind'
+ AND COLUMN_NAME = 'component_id'
+ AND INDEX_NAME != 'PRIMARY';
+
+ -- Create index on component_id only if no index exists on this column
+ IF component_id_has_index = 0 THEN
+ CREATE INDEX idx_incident_component_component_id ON
hzb_status_page_incident_component_bind(component_id);
+ END IF;
+ END IF;
+END //
+DELIMITER ;
+
+CALL UpdateStatusPageIncidentComponentBindIndexes();
+DROP PROCEDURE IF EXISTS UpdateStatusPageIncidentComponentBindIndexes;
+
+-- ========================================
+-- hzb_push_metrics table
+-- ========================================
+DELIMITER //
+CREATE PROCEDURE UpdatePushMetricsIndexes()
+BEGIN
+ DECLARE table_exists INT;
+
+ SELECT COUNT(*) INTO table_exists
+ FROM INFORMATION_SCHEMA.TABLES
+ WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'hzb_push_metrics';
+
+ IF table_exists = 1 THEN
+ -- Drop old index if exists
+ IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_push_metrics'
+ AND INDEX_NAME = 'push_query_index') THEN
+ SET @drop_sql = 'DROP INDEX push_query_index ON hzb_push_metrics';
+ PREPARE stmt FROM @drop_sql;
+ EXECUTE stmt;
+ DEALLOCATE PREPARE stmt;
+ END IF;
+
+ -- Create new indexes if not exist
+ IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_push_metrics'
+ AND INDEX_NAME = 'idx_push_metrics_monitor_id') THEN
+ CREATE INDEX idx_push_metrics_monitor_id ON
hzb_push_metrics(monitor_id);
+ END IF;
+
+ IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_push_metrics'
+ AND INDEX_NAME = 'idx_push_metrics_time') THEN
+ CREATE INDEX idx_push_metrics_time ON hzb_push_metrics(time);
+ END IF;
+ END IF;
+END //
+DELIMITER ;
+
+CALL UpdatePushMetricsIndexes();
+DROP PROCEDURE IF EXISTS UpdatePushMetricsIndexes;
+
+-- ========================================
+-- hzb_history table
+-- ========================================
+DELIMITER //
+CREATE PROCEDURE UpdateHistoryIndexes()
+BEGIN
+ DECLARE table_exists INT;
+
+ SELECT COUNT(*) INTO table_exists
+ FROM INFORMATION_SCHEMA.TABLES
+ WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'hzb_history';
+
+ IF table_exists = 1 THEN
+ -- Drop old index if exists
+ IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_history'
+ AND INDEX_NAME = 'history_query_index') THEN
+ SET @drop_sql = 'DROP INDEX history_query_index ON hzb_history';
+ PREPARE stmt FROM @drop_sql;
+ EXECUTE stmt;
+ DEALLOCATE PREPARE stmt;
+ END IF;
+
+ -- Create new indexes if not exist
+ IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_history'
+ AND INDEX_NAME = 'idx_hzb_history_instance') THEN
+ CREATE INDEX idx_hzb_history_instance ON hzb_history(instance);
+ END IF;
+
+ IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_history'
+ AND INDEX_NAME = 'idx_hzb_history_app') THEN
+ CREATE INDEX idx_hzb_history_app ON hzb_history(app);
+ END IF;
+
+ IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_history'
+ AND INDEX_NAME = 'idx_hzb_history_metrics') THEN
+ CREATE INDEX idx_hzb_history_metrics ON hzb_history(metrics);
+ END IF;
+
+ IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_history'
+ AND INDEX_NAME = 'idx_hzb_history_metric') THEN
+ CREATE INDEX idx_hzb_history_metric ON hzb_history(metric);
+ END IF;
+ END IF;
+END //
+DELIMITER ;
+
+CALL UpdateHistoryIndexes();
+DROP PROCEDURE IF EXISTS UpdateHistoryIndexes;
+
+-- ========================================
+-- hzb_param table
+-- ========================================
+DELIMITER //
+CREATE PROCEDURE UpdateParamTableIndexes()
+BEGIN
+ DECLARE table_exists INT;
+
+ SELECT COUNT(*) INTO table_exists
+ FROM INFORMATION_SCHEMA.TABLES
+ WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'hzb_param';
+
+ IF table_exists = 1 THEN
+ -- Drop old index if exists
+ IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_param'
+ AND INDEX_NAME = 'idx_hzb_param_monitor_id') THEN
+ SET @drop_sql = 'DROP INDEX idx_hzb_param_monitor_id ON hzb_param';
+ PREPARE stmt FROM @drop_sql;
+ EXECUTE stmt;
+ DEALLOCATE PREPARE stmt;
+ END IF;
+
+ -- Create new index if not exist
+ IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_param'
+ AND INDEX_NAME = 'idx_hzb_param_monitor_id') THEN
+ CREATE INDEX idx_hzb_param_monitor_id ON hzb_param(monitor_id);
+ END IF;
+
+ -- Drop old unique constraint if exists
+ IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_param'
+ AND CONSTRAINT_TYPE = 'UNIQUE'
+ AND CONSTRAINT_NAME = 'uk_hzb_param_monitor_field') THEN
+ SET @drop_sql = 'ALTER TABLE hzb_param DROP INDEX
uk_hzb_param_monitor_field';
+ PREPARE stmt FROM @drop_sql;
+ EXECUTE stmt;
+ DEALLOCATE PREPARE stmt;
+ END IF;
+
+ -- Create new unique constraint if not exist
+ IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_param'
+ AND CONSTRAINT_TYPE = 'UNIQUE'
+ AND CONSTRAINT_NAME = 'uk_hzb_param_monitor_field') THEN
+ ALTER TABLE hzb_param
+ ADD CONSTRAINT uk_hzb_param_monitor_field
+ UNIQUE (monitor_id, field);
+ END IF;
+ END IF;
+END //
+DELIMITER ;
+
+CALL UpdateParamTableIndexes();
+DROP PROCEDURE IF EXISTS UpdateParamTableIndexes;
+
+-- ========================================
+-- hzb_plugin_param table
+-- ========================================
+DELIMITER //
+CREATE PROCEDURE UpdatePluginParamTableIndexes()
+BEGIN
+ DECLARE table_exists INT;
+
+ SELECT COUNT(*) INTO table_exists
+ FROM INFORMATION_SCHEMA.TABLES
+ WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'hzb_plugin_param';
+
+ IF table_exists = 1 THEN
+ -- Drop old index if exists
+ IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_plugin_param'
+ AND INDEX_NAME = 'idx_hzb_plugin_param_plugin_metadata_id')
THEN
+ SET @drop_sql = 'DROP INDEX
idx_hzb_plugin_param_plugin_metadata_id ON hzb_plugin_param';
+ PREPARE stmt FROM @drop_sql;
+ EXECUTE stmt;
+ DEALLOCATE PREPARE stmt;
+ END IF;
+
+ -- Create new index if not exist
+ IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_plugin_param'
+ AND INDEX_NAME =
'idx_hzb_plugin_param_plugin_metadata_id') THEN
+ CREATE INDEX idx_hzb_plugin_param_plugin_metadata_id ON
hzb_plugin_param(plugin_metadata_id);
+ END IF;
+
+ -- Drop old unique constraint if exists
+ IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_plugin_param'
+ AND CONSTRAINT_TYPE = 'UNIQUE'
+ AND CONSTRAINT_NAME = 'uk_hzb_plugin_param_metadata_field')
THEN
+ SET @drop_sql = 'ALTER TABLE hzb_plugin_param DROP INDEX
uk_hzb_plugin_param_metadata_field';
+ PREPARE stmt FROM @drop_sql;
+ EXECUTE stmt;
+ DEALLOCATE PREPARE stmt;
+ END IF;
+
+ -- Create new unique constraint if not exist
+ IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'hzb_plugin_param'
+ AND CONSTRAINT_TYPE = 'UNIQUE'
+ AND CONSTRAINT_NAME =
'uk_hzb_plugin_param_metadata_field') THEN
+ ALTER TABLE hzb_plugin_param
+ ADD CONSTRAINT uk_hzb_plugin_param_metadata_field
+ UNIQUE (plugin_metadata_id, field);
+ END IF;
+ END IF;
+END //
+DELIMITER ;
+
+CALL UpdatePluginParamTableIndexes();
+DROP PROCEDURE IF EXISTS UpdatePluginParamTableIndexes;
+
COMMIT;
diff --git
a/hertzbeat-startup/src/main/resources/db/migration/mysql/V181__update_indexes.sql
b/hertzbeat-startup/src/main/resources/db/migration/mysql/V181__update_indexes.sql
deleted file mode 100644
index 88442f98c6..0000000000
---
a/hertzbeat-startup/src/main/resources/db/migration/mysql/V181__update_indexes.sql
+++ /dev/null
@@ -1,450 +0,0 @@
--- Licensed to the Apache Software Foundation (ASF) under one
--- or more contributor license agreements. See the NOTICE file
--- distributed with this work for additional information
--- regarding copyright ownership. The ASF licenses this file
--- to you under the Apache License, Version 2.0 (the
--- "License"); you may not use this file except in compliance
--- with the License. You may obtain a copy of the License at
---
--- http://www.apache.org/licenses/LICENSE-2.0
---
--- Unless required by applicable law or agreed to in writing,
--- software distributed under the License is distributed on an
--- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express
--- or implied. See the License for the specific language
--- governing permissions and limitations
--- under the License.
-
--- Ensure every SQL can rerun without error
--- This migration updates indexes to match the entity annotations
-
--- ========================================
--- hzb_alert_define_monitor_bind table
--- ========================================
-DELIMITER //
-CREATE PROCEDURE UpdateAlertDefineMonitorBindIndexes()
-BEGIN
- DECLARE table_exists INT;
-
- SELECT COUNT(*) INTO table_exists
- FROM INFORMATION_SCHEMA.TABLES
- WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME =
'hzb_alert_define_monitor_bind';
-
- IF table_exists = 1 THEN
- -- Drop old index if exists
- IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
- WHERE TABLE_SCHEMA = DATABASE()
- AND TABLE_NAME = 'hzb_alert_define_monitor_bind'
- AND INDEX_NAME = 'index_alert_define_monitor') THEN
- SET @drop_sql = 'DROP INDEX index_alert_define_monitor ON
hzb_alert_define_monitor_bind';
- PREPARE stmt FROM @drop_sql;
- EXECUTE stmt;
- DEALLOCATE PREPARE stmt;
- END IF;
-
- -- Create new indexes if not exist
- IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
- WHERE TABLE_SCHEMA = DATABASE()
- AND TABLE_NAME = 'hzb_alert_define_monitor_bind'
- AND INDEX_NAME = 'idx_alert_define_id') THEN
- CREATE INDEX idx_alert_define_id ON
hzb_alert_define_monitor_bind(alert_define_id);
- END IF;
-
- IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
- WHERE TABLE_SCHEMA = DATABASE()
- AND TABLE_NAME = 'hzb_alert_define_monitor_bind'
- AND INDEX_NAME = 'idx_monitor_id') THEN
- CREATE INDEX idx_monitor_id ON
hzb_alert_define_monitor_bind(monitor_id);
- END IF;
- END IF;
-END //
-DELIMITER ;
-
-CALL UpdateAlertDefineMonitorBindIndexes();
-DROP PROCEDURE IF EXISTS UpdateAlertDefineMonitorBindIndexes;
-
--- ========================================
--- hzb_collector_monitor_bind table
--- ========================================
-DELIMITER //
-CREATE PROCEDURE UpdateCollectorMonitorBindIndexes()
-BEGIN
- DECLARE table_exists INT;
-
- SELECT COUNT(*) INTO table_exists
- FROM INFORMATION_SCHEMA.TABLES
- WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME =
'hzb_collector_monitor_bind';
-
- IF table_exists = 1 THEN
- -- Drop old index if exists
- IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
- WHERE TABLE_SCHEMA = DATABASE()
- AND TABLE_NAME = 'hzb_collector_monitor_bind'
- AND INDEX_NAME = 'index_collector_monitor') THEN
- SET @drop_sql = 'DROP INDEX index_collector_monitor ON
hzb_collector_monitor_bind';
- PREPARE stmt FROM @drop_sql;
- EXECUTE stmt;
- DEALLOCATE PREPARE stmt;
- END IF;
-
- -- Create new indexes if not exist
- IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
- WHERE TABLE_SCHEMA = DATABASE()
- AND TABLE_NAME = 'hzb_collector_monitor_bind'
- AND INDEX_NAME = 'idx_collector_monitor_collector') THEN
- CREATE INDEX idx_collector_monitor_collector ON
hzb_collector_monitor_bind(collector);
- END IF;
-
- IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
- WHERE TABLE_SCHEMA = DATABASE()
- AND TABLE_NAME = 'hzb_collector_monitor_bind'
- AND INDEX_NAME = 'idx_collector_monitor_monitor_id') THEN
- CREATE INDEX idx_collector_monitor_monitor_id ON
hzb_collector_monitor_bind(monitor_id);
- END IF;
- END IF;
-END //
-DELIMITER ;
-
-CALL UpdateCollectorMonitorBindIndexes();
-DROP PROCEDURE IF EXISTS UpdateCollectorMonitorBindIndexes;
-
--- ========================================
--- hzb_monitor table
--- ========================================
-DELIMITER //
-CREATE PROCEDURE UpdateMonitorIndexes()
-BEGIN
- DECLARE table_exists INT;
-
- SELECT COUNT(*) INTO table_exists
- FROM INFORMATION_SCHEMA.TABLES
- WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'hzb_monitor';
-
- IF table_exists = 1 THEN
- -- Drop old index if exists
- IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
- WHERE TABLE_SCHEMA = DATABASE()
- AND TABLE_NAME = 'hzb_monitor'
- AND INDEX_NAME = 'monitor_query_index') THEN
- SET @drop_sql = 'DROP INDEX monitor_query_index ON hzb_monitor';
- PREPARE stmt FROM @drop_sql;
- EXECUTE stmt;
- DEALLOCATE PREPARE stmt;
- END IF;
-
- -- Create new indexes if not exist
- IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
- WHERE TABLE_SCHEMA = DATABASE()
- AND TABLE_NAME = 'hzb_monitor'
- AND INDEX_NAME = 'idx_hzb_monitor_app') THEN
- CREATE INDEX idx_hzb_monitor_app ON hzb_monitor(app);
- END IF;
-
- IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
- WHERE TABLE_SCHEMA = DATABASE()
- AND TABLE_NAME = 'hzb_monitor'
- AND INDEX_NAME = 'idx_hzb_monitor_instance') THEN
- CREATE INDEX idx_hzb_monitor_instance ON hzb_monitor(instance);
- END IF;
-
- IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
- WHERE TABLE_SCHEMA = DATABASE()
- AND TABLE_NAME = 'hzb_monitor'
- AND INDEX_NAME = 'idx_hzb_monitor_name') THEN
- CREATE INDEX idx_hzb_monitor_name ON hzb_monitor(name);
- END IF;
- END IF;
-END //
-DELIMITER ;
-
-CALL UpdateMonitorIndexes();
-DROP PROCEDURE IF EXISTS UpdateMonitorIndexes;
-
--- ========================================
--- hzb_monitor_bind table
--- ========================================
-DELIMITER //
-CREATE PROCEDURE UpdateMonitorBindIndexes()
-BEGIN
- DECLARE table_exists INT;
-
- SELECT COUNT(*) INTO table_exists
- FROM INFORMATION_SCHEMA.TABLES
- WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'hzb_monitor_bind';
-
- IF table_exists = 1 THEN
- -- Create new index if not exist
- IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
- WHERE TABLE_SCHEMA = DATABASE()
- AND TABLE_NAME = 'hzb_monitor_bind'
- AND INDEX_NAME = 'index_monitor_bind') THEN
- CREATE INDEX index_monitor_bind ON hzb_monitor_bind(biz_id);
- END IF;
- END IF;
-END //
-DELIMITER ;
-
-CALL UpdateMonitorBindIndexes();
-DROP PROCEDURE IF EXISTS UpdateMonitorBindIndexes;
-
--- ========================================
--- hzb_status_page_incident_component_bind table
--- Special handling: component_id might have auto-created index from FK
--- ========================================
-DELIMITER //
-CREATE PROCEDURE UpdateStatusPageIncidentComponentBindIndexes()
-BEGIN
- DECLARE table_exists INT;
- DECLARE component_id_has_index INT;
-
- SELECT COUNT(*) INTO table_exists
- FROM INFORMATION_SCHEMA.TABLES
- WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME =
'hzb_status_page_incident_component_bind';
-
- IF table_exists = 1 THEN
- -- Check if component_id column already has any index (including
auto-created by FK)
- SELECT COUNT(*) INTO component_id_has_index
- FROM INFORMATION_SCHEMA.STATISTICS
- WHERE TABLE_SCHEMA = DATABASE()
- AND TABLE_NAME = 'hzb_status_page_incident_component_bind'
- AND COLUMN_NAME = 'component_id'
- AND INDEX_NAME != 'PRIMARY';
-
- -- Create index on component_id only if no index exists on this column
- IF component_id_has_index = 0 THEN
- CREATE INDEX idx_incident_component_component_id ON
hzb_status_page_incident_component_bind(component_id);
- END IF;
- END IF;
-END //
-DELIMITER ;
-
-CALL UpdateStatusPageIncidentComponentBindIndexes();
-DROP PROCEDURE IF EXISTS UpdateStatusPageIncidentComponentBindIndexes;
-
--- ========================================
--- hzb_push_metrics table
--- ========================================
-DELIMITER //
-CREATE PROCEDURE UpdatePushMetricsIndexes()
-BEGIN
- DECLARE table_exists INT;
-
- SELECT COUNT(*) INTO table_exists
- FROM INFORMATION_SCHEMA.TABLES
- WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'hzb_push_metrics';
-
- IF table_exists = 1 THEN
- -- Drop old index if exists
- IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
- WHERE TABLE_SCHEMA = DATABASE()
- AND TABLE_NAME = 'hzb_push_metrics'
- AND INDEX_NAME = 'push_query_index') THEN
- SET @drop_sql = 'DROP INDEX push_query_index ON hzb_push_metrics';
- PREPARE stmt FROM @drop_sql;
- EXECUTE stmt;
- DEALLOCATE PREPARE stmt;
- END IF;
-
- -- Create new indexes if not exist
- IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
- WHERE TABLE_SCHEMA = DATABASE()
- AND TABLE_NAME = 'hzb_push_metrics'
- AND INDEX_NAME = 'idx_push_metrics_monitor_id') THEN
- CREATE INDEX idx_push_metrics_monitor_id ON
hzb_push_metrics(monitor_id);
- END IF;
-
- IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
- WHERE TABLE_SCHEMA = DATABASE()
- AND TABLE_NAME = 'hzb_push_metrics'
- AND INDEX_NAME = 'idx_push_metrics_time') THEN
- CREATE INDEX idx_push_metrics_time ON hzb_push_metrics(time);
- END IF;
- END IF;
-END //
-DELIMITER ;
-
-CALL UpdatePushMetricsIndexes();
-DROP PROCEDURE IF EXISTS UpdatePushMetricsIndexes;
-
--- ========================================
--- hzb_history table
--- ========================================
-DELIMITER //
-CREATE PROCEDURE UpdateHistoryIndexes()
-BEGIN
- DECLARE table_exists INT;
-
- SELECT COUNT(*) INTO table_exists
- FROM INFORMATION_SCHEMA.TABLES
- WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'hzb_history';
-
- IF table_exists = 1 THEN
- -- Drop old index if exists
- IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
- WHERE TABLE_SCHEMA = DATABASE()
- AND TABLE_NAME = 'hzb_history'
- AND INDEX_NAME = 'history_query_index') THEN
- SET @drop_sql = 'DROP INDEX history_query_index ON hzb_history';
- PREPARE stmt FROM @drop_sql;
- EXECUTE stmt;
- DEALLOCATE PREPARE stmt;
- END IF;
-
- -- Create new indexes if not exist
- IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
- WHERE TABLE_SCHEMA = DATABASE()
- AND TABLE_NAME = 'hzb_history'
- AND INDEX_NAME = 'idx_hzb_history_instance') THEN
- CREATE INDEX idx_hzb_history_instance ON hzb_history(instance);
- END IF;
-
- IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
- WHERE TABLE_SCHEMA = DATABASE()
- AND TABLE_NAME = 'hzb_history'
- AND INDEX_NAME = 'idx_hzb_history_app') THEN
- CREATE INDEX idx_hzb_history_app ON hzb_history(app);
- END IF;
-
- IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
- WHERE TABLE_SCHEMA = DATABASE()
- AND TABLE_NAME = 'hzb_history'
- AND INDEX_NAME = 'idx_hzb_history_metrics') THEN
- CREATE INDEX idx_hzb_history_metrics ON hzb_history(metrics);
- END IF;
-
- IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
- WHERE TABLE_SCHEMA = DATABASE()
- AND TABLE_NAME = 'hzb_history'
- AND INDEX_NAME = 'idx_hzb_history_metric') THEN
- CREATE INDEX idx_hzb_history_metric ON hzb_history(metric);
- END IF;
- END IF;
-END //
-DELIMITER ;
-
-CALL UpdateHistoryIndexes();
-DROP PROCEDURE IF EXISTS UpdateHistoryIndexes;
-
--- ========================================
--- hzb_param table
--- ========================================
-DELIMITER //
-CREATE PROCEDURE UpdateParamTableIndexes()
-BEGIN
- DECLARE table_exists INT;
-
- SELECT COUNT(*) INTO table_exists
- FROM INFORMATION_SCHEMA.TABLES
- WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'hzb_param';
-
- IF table_exists = 1 THEN
- -- Drop old index if exists
- IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
- WHERE TABLE_SCHEMA = DATABASE()
- AND TABLE_NAME = 'hzb_param'
- AND INDEX_NAME = 'idx_hzb_param_monitor_id') THEN
- SET @drop_sql = 'DROP INDEX idx_hzb_param_monitor_id ON hzb_param';
- PREPARE stmt FROM @drop_sql;
- EXECUTE stmt;
- DEALLOCATE PREPARE stmt;
- END IF;
-
- -- Create new index if not exist
- IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
- WHERE TABLE_SCHEMA = DATABASE()
- AND TABLE_NAME = 'hzb_param'
- AND INDEX_NAME = 'idx_hzb_param_monitor_id') THEN
- CREATE INDEX idx_hzb_param_monitor_id ON hzb_param(monitor_id);
- END IF;
-
- -- Drop old unique constraint if exists
- IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
- WHERE TABLE_SCHEMA = DATABASE()
- AND TABLE_NAME = 'hzb_param'
- AND CONSTRAINT_TYPE = 'UNIQUE'
- AND CONSTRAINT_NAME = 'uk_hzb_param_monitor_field') THEN
- SET @drop_sql = 'ALTER TABLE hzb_param DROP INDEX
uk_hzb_param_monitor_field';
- PREPARE stmt FROM @drop_sql;
- EXECUTE stmt;
- DEALLOCATE PREPARE stmt;
- END IF;
-
- -- Create new unique constraint if not exist
- IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
- WHERE TABLE_SCHEMA = DATABASE()
- AND TABLE_NAME = 'hzb_param'
- AND CONSTRAINT_TYPE = 'UNIQUE'
- AND CONSTRAINT_NAME = 'uk_hzb_param_monitor_field') THEN
- ALTER TABLE hzb_param
- ADD CONSTRAINT uk_hzb_param_monitor_field
- UNIQUE (monitor_id, field);
- END IF;
- END IF;
-END //
-DELIMITER ;
-
-CALL UpdateParamTableIndexes();
-DROP PROCEDURE IF EXISTS UpdateParamTableIndexes;
-
--- ========================================
--- hzb_plugin_param table
--- ========================================
-DELIMITER //
-CREATE PROCEDURE UpdatePluginParamTableIndexes()
-BEGIN
- DECLARE table_exists INT;
-
- SELECT COUNT(*) INTO table_exists
- FROM INFORMATION_SCHEMA.TABLES
- WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'hzb_plugin_param';
-
- IF table_exists = 1 THEN
- -- Drop old index if exists
- IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
- WHERE TABLE_SCHEMA = DATABASE()
- AND TABLE_NAME = 'hzb_plugin_param'
- AND INDEX_NAME = 'idx_hzb_plugin_param_plugin_metadata_id')
THEN
- SET @drop_sql = 'DROP INDEX
idx_hzb_plugin_param_plugin_metadata_id ON hzb_plugin_param';
- PREPARE stmt FROM @drop_sql;
- EXECUTE stmt;
- DEALLOCATE PREPARE stmt;
- END IF;
-
- -- Create new index if not exist
- IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
- WHERE TABLE_SCHEMA = DATABASE()
- AND TABLE_NAME = 'hzb_plugin_param'
- AND INDEX_NAME =
'idx_hzb_plugin_param_plugin_metadata_id') THEN
- CREATE INDEX idx_hzb_plugin_param_plugin_metadata_id ON
hzb_plugin_param(plugin_metadata_id);
- END IF;
-
- -- Drop old unique constraint if exists
- IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
- WHERE TABLE_SCHEMA = DATABASE()
- AND TABLE_NAME = 'hzb_plugin_param'
- AND CONSTRAINT_TYPE = 'UNIQUE'
- AND CONSTRAINT_NAME = 'uk_hzb_plugin_param_metadata_field')
THEN
- SET @drop_sql = 'ALTER TABLE hzb_plugin_param DROP INDEX
uk_hzb_plugin_param_metadata_field';
- PREPARE stmt FROM @drop_sql;
- EXECUTE stmt;
- DEALLOCATE PREPARE stmt;
- END IF;
-
- -- Create new unique constraint if not exist
- IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
- WHERE TABLE_SCHEMA = DATABASE()
- AND TABLE_NAME = 'hzb_plugin_param'
- AND CONSTRAINT_TYPE = 'UNIQUE'
- AND CONSTRAINT_NAME =
'uk_hzb_plugin_param_metadata_field') THEN
- ALTER TABLE hzb_plugin_param
- ADD CONSTRAINT uk_hzb_plugin_param_metadata_field
- UNIQUE (plugin_metadata_id, field);
- END IF;
- END IF;
-END //
-DELIMITER ;
-
-CALL UpdatePluginParamTableIndexes();
-DROP PROCEDURE IF EXISTS UpdatePluginParamTableIndexes;
-
-COMMIT;
diff --git
a/hertzbeat-startup/src/main/resources/db/migration/postgresql/V180__update_column.sql
b/hertzbeat-startup/src/main/resources/db/migration/postgresql/V180__update_column.sql
index 09cecdb64c..99d1bde448 100644
---
a/hertzbeat-startup/src/main/resources/db/migration/postgresql/V180__update_column.sql
+++
b/hertzbeat-startup/src/main/resources/db/migration/postgresql/V180__update_column.sql
@@ -62,14 +62,240 @@ BEGIN
UPDATE hzb_history SET instance = NULL;
ALTER TABLE hzb_history ALTER COLUMN instance TYPE VARCHAR(255);
END IF;
-
+
UPDATE hzb_history h
SET instance = m.instance
FROM hzb_monitor m
WHERE h.monitor_id = m.id;
-
+
ALTER TABLE hzb_history DROP COLUMN monitor_id;
END IF;
END $$;
-commit;
+-- ========================================
+-- hzb_alert_define_monitor_bind table
+-- ========================================
+DO $$
+BEGIN
+ IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name =
'hzb_alert_define_monitor_bind') THEN
+ -- Drop old index if exists
+ IF EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_alert_define_monitor_bind' AND indexname = 'index_alert_define_monitor')
THEN
+ DROP INDEX IF EXISTS index_alert_define_monitor;
+ END IF;
+
+ -- Create new indexes if not exist
+ IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_alert_define_monitor_bind' AND indexname = 'idx_alert_define_id') THEN
+ CREATE INDEX idx_alert_define_id ON
hzb_alert_define_monitor_bind(alert_define_id);
+ END IF;
+
+ IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_alert_define_monitor_bind' AND indexname = 'idx_monitor_id') THEN
+ CREATE INDEX idx_monitor_id ON
hzb_alert_define_monitor_bind(monitor_id);
+ END IF;
+ END IF;
+END $$;
+
+-- ========================================
+-- hzb_collector_monitor_bind table
+-- ========================================
+DO $$
+BEGIN
+ IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name =
'hzb_collector_monitor_bind') THEN
+ -- Drop old index if exists
+ IF EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_collector_monitor_bind' AND indexname = 'index_collector_monitor') THEN
+ DROP INDEX IF EXISTS index_collector_monitor;
+ END IF;
+
+ -- Create new indexes if not exist
+ IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_collector_monitor_bind' AND indexname = 'idx_collector_monitor_collector')
THEN
+ CREATE INDEX idx_collector_monitor_collector ON
hzb_collector_monitor_bind(collector);
+ END IF;
+
+ IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_collector_monitor_bind' AND indexname =
'idx_collector_monitor_monitor_id') THEN
+ CREATE INDEX idx_collector_monitor_monitor_id ON
hzb_collector_monitor_bind(monitor_id);
+ END IF;
+ END IF;
+END $$;
+
+-- ========================================
+-- hzb_monitor table
+-- ========================================
+DO $$
+BEGIN
+ IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name =
'hzb_monitor') THEN
+ -- Drop old index if exists
+ IF EXISTS (SELECT 1 FROM pg_indexes WHERE tablename = 'hzb_monitor'
AND indexname = 'monitor_query_index') THEN
+ DROP INDEX IF EXISTS monitor_query_index;
+ END IF;
+
+ -- Create new indexes if not exist
+ IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_monitor' AND indexname = 'idx_hzb_monitor_app') THEN
+ CREATE INDEX idx_hzb_monitor_app ON hzb_monitor(app);
+ END IF;
+
+ IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_monitor' AND indexname = 'idx_hzb_monitor_instance') THEN
+ CREATE INDEX idx_hzb_monitor_instance ON hzb_monitor(instance);
+ END IF;
+
+ IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_monitor' AND indexname = 'idx_hzb_monitor_name') THEN
+ CREATE INDEX idx_hzb_monitor_name ON hzb_monitor(name);
+ END IF;
+ END IF;
+END $$;
+
+-- ========================================
+-- hzb_monitor_bind table
+-- ========================================
+DO $$
+BEGIN
+ IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name =
'hzb_monitor_bind') THEN
+ -- Create new index if not exist
+ IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_monitor_bind' AND indexname = 'index_monitor_bind') THEN
+ CREATE INDEX index_monitor_bind ON hzb_monitor_bind(biz_id);
+ END IF;
+ END IF;
+END $$;
+
+-- ========================================
+-- hzb_status_page_incident_component_bind table
+-- Special handling: component_id might have auto-created index from FK
+-- ========================================
+DO $$
+BEGIN
+ IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name =
'hzb_status_page_incident_component_bind') THEN
+ -- Check if component_id column already has any index, create if not
+ IF NOT EXISTS (
+ SELECT 1 FROM pg_indexes
+ WHERE tablename = 'hzb_status_page_incident_component_bind'
+ AND indexdef LIKE '%component_id%'
+ AND indexname NOT LIKE '%_pkey'
+ ) THEN
+ CREATE INDEX idx_incident_component_component_id ON
hzb_status_page_incident_component_bind(component_id);
+ END IF;
+ END IF;
+END $$;
+
+-- ========================================
+-- hzb_push_metrics table
+-- ========================================
+DO $$
+BEGIN
+ IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name =
'hzb_push_metrics') THEN
+ -- Drop old index if exists
+ IF EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_push_metrics' AND indexname = 'push_query_index') THEN
+ DROP INDEX IF EXISTS push_query_index;
+ END IF;
+
+ -- Create new indexes if not exist
+ IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_push_metrics' AND indexname = 'idx_push_metrics_monitor_id') THEN
+ CREATE INDEX idx_push_metrics_monitor_id ON
hzb_push_metrics(monitor_id);
+ END IF;
+
+ IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_push_metrics' AND indexname = 'idx_push_metrics_time') THEN
+ CREATE INDEX idx_push_metrics_time ON hzb_push_metrics(time);
+ END IF;
+ END IF;
+END $$;
+
+-- ========================================
+-- hzb_history table
+-- ========================================
+DO $$
+BEGIN
+ IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name =
'hzb_history') THEN
+ -- Drop old index if exists
+ IF EXISTS (SELECT 1 FROM pg_indexes WHERE tablename = 'hzb_history'
AND indexname = 'history_query_index') THEN
+ DROP INDEX IF EXISTS history_query_index;
+ END IF;
+
+ -- Create new indexes if not exist
+ IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_history' AND indexname = 'idx_hzb_history_instance') THEN
+ CREATE INDEX idx_hzb_history_instance ON hzb_history(instance);
+ END IF;
+
+ IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_history' AND indexname = 'idx_hzb_history_app') THEN
+ CREATE INDEX idx_hzb_history_app ON hzb_history(app);
+ END IF;
+
+ IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_history' AND indexname = 'idx_hzb_history_metrics') THEN
+ CREATE INDEX idx_hzb_history_metrics ON hzb_history(metrics);
+ END IF;
+
+ IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_history' AND indexname = 'idx_hzb_history_metric') THEN
+ CREATE INDEX idx_hzb_history_metric ON hzb_history(metric);
+ END IF;
+ END IF;
+END $$;
+
+-- ========================================
+-- hzb_param table
+-- ========================================
+DO $$
+BEGIN
+ IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name =
'hzb_param') THEN
+ -- Drop old index if exists
+ IF EXISTS (SELECT 1 FROM pg_indexes WHERE tablename = 'hzb_param' AND
indexname = 'idx_hzb_param_monitor_id') THEN
+ DROP INDEX IF EXISTS idx_hzb_param_monitor_id;
+ END IF;
+
+ -- Create new index if not exist
+ IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename = 'hzb_param'
AND indexname = 'idx_hzb_param_monitor_id') THEN
+ CREATE INDEX idx_hzb_param_monitor_id ON hzb_param(monitor_id);
+ END IF;
+
+ -- Drop old unique constraint if exists
+ IF EXISTS (SELECT 1 FROM information_schema.table_constraints
+ WHERE table_name = 'hzb_param'
+ AND constraint_type = 'UNIQUE'
+ AND constraint_name = 'uk_hzb_param_monitor_field') THEN
+ ALTER TABLE hzb_param DROP CONSTRAINT IF EXISTS
uk_hzb_param_monitor_field;
+ END IF;
+
+ -- Create new unique constraint if not exist
+ IF NOT EXISTS (SELECT 1 FROM information_schema.table_constraints
+ WHERE table_name = 'hzb_param'
+ AND constraint_type = 'UNIQUE'
+ AND constraint_name = 'uk_hzb_param_monitor_field') THEN
+ ALTER TABLE hzb_param
+ ADD CONSTRAINT uk_hzb_param_monitor_field
+ UNIQUE (monitor_id, field);
+ END IF;
+ END IF;
+END $$;
+
+-- ========================================
+-- hzb_plugin_param table
+-- ========================================
+DO $$
+BEGIN
+ IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name =
'hzb_plugin_param') THEN
+ -- Drop old index if exists
+ IF EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_plugin_param' AND indexname = 'idx_hzb_plugin_param_plugin_metadata_id')
THEN
+ DROP INDEX IF EXISTS idx_hzb_plugin_param_plugin_metadata_id;
+ END IF;
+
+ -- Create new index if not exist
+ IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_plugin_param' AND indexname = 'idx_hzb_plugin_param_plugin_metadata_id')
THEN
+ CREATE INDEX idx_hzb_plugin_param_plugin_metadata_id ON
hzb_plugin_param(plugin_metadata_id);
+ END IF;
+
+ -- Drop old unique constraint if exists
+ IF EXISTS (SELECT 1 FROM information_schema.table_constraints
+ WHERE table_name = 'hzb_plugin_param'
+ AND constraint_type = 'UNIQUE'
+ AND constraint_name = 'uk_hzb_plugin_param_metadata_field')
THEN
+ ALTER TABLE hzb_plugin_param DROP CONSTRAINT IF EXISTS
uk_hzb_plugin_param_metadata_field;
+ END IF;
+
+ -- Create new unique constraint if not exist
+ IF NOT EXISTS (SELECT 1 FROM information_schema.table_constraints
+ WHERE table_name = 'hzb_plugin_param'
+ AND constraint_type = 'UNIQUE'
+ AND constraint_name =
'uk_hzb_plugin_param_metadata_field') THEN
+ ALTER TABLE hzb_plugin_param
+ ADD CONSTRAINT uk_hzb_plugin_param_metadata_field
+ UNIQUE (plugin_metadata_id, field);
+ END IF;
+ END IF;
+END $$;
+
+COMMIT;
diff --git
a/hertzbeat-startup/src/main/resources/db/migration/postgresql/V181__update_indexes.sql
b/hertzbeat-startup/src/main/resources/db/migration/postgresql/V181__update_indexes.sql
deleted file mode 100644
index da88582de3..0000000000
---
a/hertzbeat-startup/src/main/resources/db/migration/postgresql/V181__update_indexes.sql
+++ /dev/null
@@ -1,247 +0,0 @@
--- Licensed to the Apache Software Foundation (ASF) under one
--- or more contributor license agreements. See the NOTICE file
--- distributed with this work for additional information
--- regarding copyright ownership. The ASF licenses this file
--- to you under the Apache License, Version 2.0 (the
--- "License"); you may not use this file except in compliance
--- with the License. You may obtain a copy of the License at
---
--- http://www.apache.org/licenses/LICENSE-2.0
---
--- Unless required by applicable law or agreed to in writing,
--- software distributed under the License is distributed on an
--- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express
--- or implied. See the License for the specific language
--- governing permissions and limitations
--- under the License.
-
--- Ensure every SQL can rerun without error
--- This migration updates indexes to match the entity annotations
-
--- ========================================
--- hzb_alert_define_monitor_bind table
--- ========================================
-DO $$
-BEGIN
- IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name =
'hzb_alert_define_monitor_bind') THEN
- -- Drop old index if exists
- IF EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_alert_define_monitor_bind' AND indexname = 'index_alert_define_monitor')
THEN
- DROP INDEX IF EXISTS index_alert_define_monitor;
- END IF;
-
- -- Create new indexes if not exist
- IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_alert_define_monitor_bind' AND indexname = 'idx_alert_define_id') THEN
- CREATE INDEX idx_alert_define_id ON
hzb_alert_define_monitor_bind(alert_define_id);
- END IF;
-
- IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_alert_define_monitor_bind' AND indexname = 'idx_monitor_id') THEN
- CREATE INDEX idx_monitor_id ON
hzb_alert_define_monitor_bind(monitor_id);
- END IF;
- END IF;
-END $$;
-
--- ========================================
--- hzb_collector_monitor_bind table
--- ========================================
-DO $$
-BEGIN
- IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name =
'hzb_collector_monitor_bind') THEN
- -- Drop old index if exists
- IF EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_collector_monitor_bind' AND indexname = 'index_collector_monitor') THEN
- DROP INDEX IF EXISTS index_collector_monitor;
- END IF;
-
- -- Create new indexes if not exist
- IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_collector_monitor_bind' AND indexname = 'idx_collector_monitor_collector')
THEN
- CREATE INDEX idx_collector_monitor_collector ON
hzb_collector_monitor_bind(collector);
- END IF;
-
- IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_collector_monitor_bind' AND indexname =
'idx_collector_monitor_monitor_id') THEN
- CREATE INDEX idx_collector_monitor_monitor_id ON
hzb_collector_monitor_bind(monitor_id);
- END IF;
- END IF;
-END $$;
-
--- ========================================
--- hzb_monitor table
--- ========================================
-DO $$
-BEGIN
- IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name =
'hzb_monitor') THEN
- -- Drop old index if exists
- IF EXISTS (SELECT 1 FROM pg_indexes WHERE tablename = 'hzb_monitor'
AND indexname = 'monitor_query_index') THEN
- DROP INDEX IF EXISTS monitor_query_index;
- END IF;
-
- -- Create new indexes if not exist
- IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_monitor' AND indexname = 'idx_hzb_monitor_app') THEN
- CREATE INDEX idx_hzb_monitor_app ON hzb_monitor(app);
- END IF;
-
- IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_monitor' AND indexname = 'idx_hzb_monitor_instance') THEN
- CREATE INDEX idx_hzb_monitor_instance ON hzb_monitor(instance);
- END IF;
-
- IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_monitor' AND indexname = 'idx_hzb_monitor_name') THEN
- CREATE INDEX idx_hzb_monitor_name ON hzb_monitor(name);
- END IF;
- END IF;
-END $$;
-
--- ========================================
--- hzb_monitor_bind table
--- ========================================
-DO $$
-BEGIN
- IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name =
'hzb_monitor_bind') THEN
- -- Create new index if not exist
- IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_monitor_bind' AND indexname = 'index_monitor_bind') THEN
- CREATE INDEX index_monitor_bind ON hzb_monitor_bind(biz_id);
- END IF;
- END IF;
-END $$;
-
--- ========================================
--- hzb_status_page_incident_component_bind table
--- Special handling: component_id might have auto-created index from FK
--- ========================================
-DO $$
-BEGIN
- IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name =
'hzb_status_page_incident_component_bind') THEN
- -- Check if component_id column already has any index, create if not
- IF NOT EXISTS (
- SELECT 1 FROM pg_indexes
- WHERE tablename = 'hzb_status_page_incident_component_bind'
- AND indexdef LIKE '%component_id%'
- AND indexname NOT LIKE '%_pkey'
- ) THEN
- CREATE INDEX idx_incident_component_component_id ON
hzb_status_page_incident_component_bind(component_id);
- END IF;
- END IF;
-END $$;
-
--- ========================================
--- hzb_push_metrics table
--- ========================================
-DO $$
-BEGIN
- IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name =
'hzb_push_metrics') THEN
- -- Drop old index if exists
- IF EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_push_metrics' AND indexname = 'push_query_index') THEN
- DROP INDEX IF EXISTS push_query_index;
- END IF;
-
- -- Create new indexes if not exist
- IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_push_metrics' AND indexname = 'idx_push_metrics_monitor_id') THEN
- CREATE INDEX idx_push_metrics_monitor_id ON
hzb_push_metrics(monitor_id);
- END IF;
-
- IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_push_metrics' AND indexname = 'idx_push_metrics_time') THEN
- CREATE INDEX idx_push_metrics_time ON hzb_push_metrics(time);
- END IF;
- END IF;
-END $$;
-
--- ========================================
--- hzb_history table
--- ========================================
-DO $$
-BEGIN
- IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name =
'hzb_history') THEN
- -- Drop old index if exists
- IF EXISTS (SELECT 1 FROM pg_indexes WHERE tablename = 'hzb_history'
AND indexname = 'history_query_index') THEN
- DROP INDEX IF EXISTS history_query_index;
- END IF;
-
- -- Create new indexes if not exist
- IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_history' AND indexname = 'idx_hzb_history_instance') THEN
- CREATE INDEX idx_hzb_history_instance ON hzb_history(instance);
- END IF;
-
- IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_history' AND indexname = 'idx_hzb_history_app') THEN
- CREATE INDEX idx_hzb_history_app ON hzb_history(app);
- END IF;
-
- IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_history' AND indexname = 'idx_hzb_history_metrics') THEN
- CREATE INDEX idx_hzb_history_metrics ON hzb_history(metrics);
- END IF;
-
- IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_history' AND indexname = 'idx_hzb_history_metric') THEN
- CREATE INDEX idx_hzb_history_metric ON hzb_history(metric);
- END IF;
- END IF;
-END $$;
-
--- ========================================
--- hzb_param table
--- ========================================
-DO $$
-BEGIN
- IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name =
'hzb_param') THEN
- -- Drop old index if exists
- IF EXISTS (SELECT 1 FROM pg_indexes WHERE tablename = 'hzb_param' AND
indexname = 'idx_hzb_param_monitor_id') THEN
- DROP INDEX IF EXISTS idx_hzb_param_monitor_id;
- END IF;
-
- -- Create new index if not exist
- IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename = 'hzb_param'
AND indexname = 'idx_hzb_param_monitor_id') THEN
- CREATE INDEX idx_hzb_param_monitor_id ON hzb_param(monitor_id);
- END IF;
-
- -- Drop old unique constraint if exists
- IF EXISTS (SELECT 1 FROM information_schema.table_constraints
- WHERE table_name = 'hzb_param'
- AND constraint_type = 'UNIQUE'
- AND constraint_name = 'uk_hzb_param_monitor_field') THEN
- ALTER TABLE hzb_param DROP CONSTRAINT IF EXISTS
uk_hzb_param_monitor_field;
- END IF;
-
- -- Create new unique constraint if not exist
- IF NOT EXISTS (SELECT 1 FROM information_schema.table_constraints
- WHERE table_name = 'hzb_param'
- AND constraint_type = 'UNIQUE'
- AND constraint_name = 'uk_hzb_param_monitor_field') THEN
- ALTER TABLE hzb_param
- ADD CONSTRAINT uk_hzb_param_monitor_field
- UNIQUE (monitor_id, field);
- END IF;
- END IF;
-END $$;
-
--- ========================================
--- hzb_plugin_param table
--- ========================================
-DO $$
-BEGIN
- IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name =
'hzb_plugin_param') THEN
- -- Drop old index if exists
- IF EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_plugin_param' AND indexname = 'idx_hzb_plugin_param_plugin_metadata_id')
THEN
- DROP INDEX IF EXISTS idx_hzb_plugin_param_plugin_metadata_id;
- END IF;
-
- -- Create new index if not exist
- IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename =
'hzb_plugin_param' AND indexname = 'idx_hzb_plugin_param_plugin_metadata_id')
THEN
- CREATE INDEX idx_hzb_plugin_param_plugin_metadata_id ON
hzb_plugin_param(plugin_metadata_id);
- END IF;
-
- -- Drop old unique constraint if exists
- IF EXISTS (SELECT 1 FROM information_schema.table_constraints
- WHERE table_name = 'hzb_plugin_param'
- AND constraint_type = 'UNIQUE'
- AND constraint_name = 'uk_hzb_plugin_param_metadata_field')
THEN
- ALTER TABLE hzb_plugin_param DROP CONSTRAINT IF EXISTS
uk_hzb_plugin_param_metadata_field;
- END IF;
-
- -- Create new unique constraint if not exist
- IF NOT EXISTS (SELECT 1 FROM information_schema.table_constraints
- WHERE table_name = 'hzb_plugin_param'
- AND constraint_type = 'UNIQUE'
- AND constraint_name =
'uk_hzb_plugin_param_metadata_field') THEN
- ALTER TABLE hzb_plugin_param
- ADD CONSTRAINT uk_hzb_plugin_param_metadata_field
- UNIQUE (plugin_metadata_id, field);
- END IF;
- END IF;
-END $$;
-
-COMMIT;
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
