This is an automated email from the ASF dual-hosted git repository.
shwstppr pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/cloudstack.git
The following commit(s) were added to refs/heads/main by this push:
new a791d46abed events,alerts: Add missing indexes (#8276)
a791d46abed is described below
commit a791d46abed03d261e91f616420254220f1d5994
Author: Vishesh <[email protected]>
AuthorDate: Wed Dec 13 10:24:36 2023 +0530
events,alerts: Add missing indexes (#8276)
This PR adds missing indexes on `alerts` & `events` tables.
For alerts table, some of the queries are part of a couple of APIs and some
operations. I have added the index for the same. Ref:
https://github.com/apache/cloudstack/blob/8f390873772ef59c3ba81c837fffe707ebd80e72/engine/schema/src/main/java/com/cloud/alert/dao/AlertDaoImpl.java#L40-L45
For Events table, we query for `resource_id` & `resource_type` in the UI
for a resource's events. Indexes were missing, so I have added those.
---
.../main/java/com/cloud/upgrade/dao/DatabaseAccessObject.java | 9 +++++----
.../src/main/java/com/cloud/upgrade/dao/DbUpgradeUtils.java | 6 +++---
.../src/main/java/com/cloud/upgrade/dao/Upgrade41810to41900.java | 8 ++++++++
.../java/com/cloud/upgrade/dao/DatabaseAccessObjectTest.java | 9 +++++----
4 files changed, 21 insertions(+), 11 deletions(-)
diff --git
a/engine/schema/src/main/java/com/cloud/upgrade/dao/DatabaseAccessObject.java
b/engine/schema/src/main/java/com/cloud/upgrade/dao/DatabaseAccessObject.java
index 0b38acb5c21..de161afea07 100644
---
a/engine/schema/src/main/java/com/cloud/upgrade/dao/DatabaseAccessObject.java
+++
b/engine/schema/src/main/java/com/cloud/upgrade/dao/DatabaseAccessObject.java
@@ -21,6 +21,7 @@ import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
+import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
public class DatabaseAccessObject {
@@ -85,8 +86,8 @@ public class DatabaseAccessObject {
return columnExists;
}
- public String generateIndexName(String tableName, String columnName) {
- return String.format("i_%s__%s", tableName, columnName);
+ public String generateIndexName(String tableName, String... columnName) {
+ return String.format("i_%s__%s", tableName,
StringUtils.join(columnName, "__"));
}
public boolean indexExists(Connection conn, String tableName, String
indexName) {
@@ -101,8 +102,8 @@ public class DatabaseAccessObject {
return false;
}
- public void createIndex(Connection conn, String tableName, String
columnName, String indexName) {
- String stmt = String.format("CREATE INDEX %s on %s (%s)", indexName,
tableName, columnName);
+ public void createIndex(Connection conn, String tableName, String
indexName, String... columnNames) {
+ String stmt = String.format("CREATE INDEX %s ON %s (%s)", indexName,
tableName, StringUtils.join(columnNames, ", "));
s_logger.debug("Statement: " + stmt);
try (PreparedStatement pstmt = conn.prepareStatement(stmt)) {
pstmt.execute();
diff --git
a/engine/schema/src/main/java/com/cloud/upgrade/dao/DbUpgradeUtils.java
b/engine/schema/src/main/java/com/cloud/upgrade/dao/DbUpgradeUtils.java
index 6b4e1814de0..51e6ac7b9a1 100644
--- a/engine/schema/src/main/java/com/cloud/upgrade/dao/DbUpgradeUtils.java
+++ b/engine/schema/src/main/java/com/cloud/upgrade/dao/DbUpgradeUtils.java
@@ -23,11 +23,11 @@ public class DbUpgradeUtils {
private static DatabaseAccessObject dao = new DatabaseAccessObject();
- public static void addIndexIfNeeded(Connection conn, String tableName,
String columnName) {
- String indexName = dao.generateIndexName(tableName, columnName);
+ public static void addIndexIfNeeded(Connection conn, String tableName,
String... columnNames) {
+ String indexName = dao.generateIndexName(tableName, columnNames);
if (!dao.indexExists(conn, tableName, indexName)) {
- dao.createIndex(conn, tableName, columnName, indexName);
+ dao.createIndex(conn, tableName, indexName, columnNames);
}
}
diff --git
a/engine/schema/src/main/java/com/cloud/upgrade/dao/Upgrade41810to41900.java
b/engine/schema/src/main/java/com/cloud/upgrade/dao/Upgrade41810to41900.java
index fd44e79e7cf..bdfe58cbf89 100644
--- a/engine/schema/src/main/java/com/cloud/upgrade/dao/Upgrade41810to41900.java
+++ b/engine/schema/src/main/java/com/cloud/upgrade/dao/Upgrade41810to41900.java
@@ -76,6 +76,7 @@ public class Upgrade41810to41900 implements DbUpgrade,
DbUpgradeSystemVmTemplate
public void performDataMigration(Connection conn) {
decryptConfigurationValuesFromAccountAndDomainScopesNotInSecureHiddenCategories(conn);
migrateBackupDates(conn);
+ addIndexes(conn);
}
@Override
@@ -254,4 +255,11 @@ public class Upgrade41810to41900 implements DbUpgrade,
DbUpgradeSystemVmTemplate
}
}
+ private void addIndexes(Connection conn) {
+ DbUpgradeUtils.addIndexIfNeeded(conn, "alert", "archived", "created");
+ DbUpgradeUtils.addIndexIfNeeded(conn, "alert", "type",
"data_center_id", "pod_id");
+
+ DbUpgradeUtils.addIndexIfNeeded(conn, "event", "resource_type",
"resource_id");
+ }
+
}
diff --git
a/engine/schema/src/test/java/com/cloud/upgrade/dao/DatabaseAccessObjectTest.java
b/engine/schema/src/test/java/com/cloud/upgrade/dao/DatabaseAccessObjectTest.java
index 8f1ee3e0450..bd05fbe3c4c 100644
---
a/engine/schema/src/test/java/com/cloud/upgrade/dao/DatabaseAccessObjectTest.java
+++
b/engine/schema/src/test/java/com/cloud/upgrade/dao/DatabaseAccessObjectTest.java
@@ -93,8 +93,8 @@ public class DatabaseAccessObjectTest {
@Test
public void generateIndexNameTest() {
- String indexName = dao.generateIndexName("mytable","mycolumn");
- Assert.assertEquals( "i_mytable__mycolumn", indexName);
+ String indexName = dao.generateIndexName("mytable","mycolumn1",
"mycolumn2");
+ Assert.assertEquals( "i_mytable__mycolumn1__mycolumn2", indexName);
}
@Test
@@ -136,10 +136,11 @@ public class DatabaseAccessObjectTest {
Connection conn = connectionMock;
String tableName = "mytable";
- String columnName = "mycolumn";
+ String columnName1 = "mycolumn1";
+ String columnName2 = "mycolumn2";
String indexName = "myindex";
- dao.createIndex(conn, tableName, columnName, indexName);
+ dao.createIndex(conn, tableName, indexName, columnName1, columnName2);
verify(connectionMock, times(1)).prepareStatement(anyString());
verify(preparedStatementMock, times(1)).execute();
verify(preparedStatementMock, times(1)).close();
