SONAR-23571 Add index on rule_tags table

4 files changed, 121 insertions, 0 deletions

diff --git a/server/sonar-db-dao/src/schema/schema-sq.ddl b/server/sonar-db-dao/src/schema/schema-sq.ddl
index 8a2f5916930..fa3ca75b1e9 100644
--- a/server/sonar-db-dao/src/schema/schema-sq.ddl
+++ b/server/sonar-db-dao/src/schema/schema-sq.ddl
@@ -963,6 +963,7 @@ CREATE TABLE "RULE_TAGS"(
     "IS_SYSTEM_TAG" BOOLEAN NOT NULL
 );
 ALTER TABLE "RULE_TAGS" ADD CONSTRAINT "PK_RULE_TAGS" PRIMARY KEY("VALUE", "RULE_UUID");
+CREATE UNIQUE NULLS NOT DISTINCT INDEX "RULE_TAGS_RULE_UUID" ON "RULE_TAGS"("RULE_UUID" NULLS FIRST, "VALUE" NULLS FIRST, "IS_SYSTEM_TAG" NULLS FIRST);
 
 CREATE TABLE "RULES"(
     "UUID" CHARACTER VARYING(40) NOT NULL,
diff --git a/server/sonar-db-migration/src/it/java/org/sonar/server/platform/db/migration/version/v202501/CreateIndexOnRuleTagsTableIT.java b/server/sonar-db-migration/src/it/java/org/sonar/server/platform/db/migration/version/v202501/CreateIndexOnRuleTagsTableIT.java
new file mode 100644
index 00000000000..a7a0585345e
--- /dev/null
+++ b/server/sonar-db-migration/src/it/java/org/sonar/server/platform/db/migration/version/v202501/CreateIndexOnRuleTagsTableIT.java
@@ -0,0 +1,59 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2024 SonarSource SA
+ * mailto:info AT sonarsource DOT com
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU Lesser General Public
+ * License as published by the Free Software Foundation; either
+ * version 3 of the License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+ * Lesser General Public License for more details.
+ *
+ * You should have received a copy of the GNU Lesser General Public License
+ * along with this program; if not, write to the Free Software Foundation,
+ * Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+ */
+package org.sonar.server.platform.db.migration.version.v202501;
+
+import java.sql.SQLException;
+import org.junit.jupiter.api.Test;
+import org.junit.jupiter.api.extension.RegisterExtension;
+import org.sonar.db.MigrationDbTester;
+import org.sonar.server.platform.db.migration.step.DdlChange;
+
+import static org.sonar.server.platform.db.migration.version.v202501.CreateIndexOnRuleTagsTable.COLUMN_NAME_IS_SYSTEM_TAG;
+import static org.sonar.server.platform.db.migration.version.v202501.CreateIndexOnRuleTagsTable.COLUMN_NAME_RULE_UUID;
+import static org.sonar.server.platform.db.migration.version.v202501.CreateIndexOnRuleTagsTable.COLUMN_NAME_VALUE;
+import static org.sonar.server.platform.db.migration.version.v202501.CreateIndexOnRuleTagsTable.INDEX_NAME;
+import static org.sonar.server.platform.db.migration.version.v202501.CreateIndexOnRuleTagsTable.TABLE_NAME;
+
+class CreateIndexOnRuleTagsTableIT {
+
+  @RegisterExtension
+  public final MigrationDbTester db = MigrationDbTester.createForMigrationStep(CreateIndexOnRuleTagsTable.class);
+
+  private final DdlChange underTest = new CreateIndexOnRuleTagsTable(db.database());
+
+  @Test
+  void migration_should_create_index() throws SQLException {
+    db.assertIndexDoesNotExist(TABLE_NAME, INDEX_NAME);
+
+    underTest.execute();
+
+    db.assertUniqueIndex(TABLE_NAME, INDEX_NAME, COLUMN_NAME_RULE_UUID, COLUMN_NAME_VALUE, COLUMN_NAME_IS_SYSTEM_TAG);
+  }
+
+  @Test
+  void migration_should_be_reentrant() throws SQLException {
+    db.assertIndexDoesNotExist(TABLE_NAME, INDEX_NAME);
+
+    underTest.execute();
+    underTest.execute();
+
+    db.assertUniqueIndex(TABLE_NAME, INDEX_NAME, COLUMN_NAME_RULE_UUID, COLUMN_NAME_VALUE, COLUMN_NAME_IS_SYSTEM_TAG);
+  }
+}
diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v202501/CreateIndexOnRuleTagsTable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v202501/CreateIndexOnRuleTagsTable.java
new file mode 100644
index 00000000000..79c134ea55c
--- /dev/null
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v202501/CreateIndexOnRuleTagsTable.java
@@ -0,0 +1,60 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2024 SonarSource SA
+ * mailto:info AT sonarsource DOT com
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU Lesser General Public
+ * License as published by the Free Software Foundation; either
+ * version 3 of the License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+ * Lesser General Public License for more details.
+ *
+ * You should have received a copy of the GNU Lesser General Public License
+ * along with this program; if not, write to the Free Software Foundation,
+ * Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+ */
+package org.sonar.server.platform.db.migration.version.v202501;
+
+import java.sql.Connection;
+import java.sql.SQLException;
+import org.sonar.db.Database;
+import org.sonar.db.DatabaseUtils;
+import org.sonar.server.platform.db.migration.sql.CreateIndexBuilder;
+import org.sonar.server.platform.db.migration.step.DdlChange;
+
+public class CreateIndexOnRuleTagsTable extends DdlChange {
+
+  static final String TABLE_NAME = "rule_tags";
+  static final String INDEX_NAME = "rule_tags_rule_uuid";
+  static final String COLUMN_NAME_RULE_UUID = "rule_uuid";
+  static final String COLUMN_NAME_VALUE = "value";
+  static final String COLUMN_NAME_IS_SYSTEM_TAG = "is_system_tag";
+
+  public CreateIndexOnRuleTagsTable(Database db) {
+    super(db);
+  }
+
+  @Override
+  public void execute(Context context) throws SQLException {
+    try (Connection connection = getDatabase().getDataSource().getConnection()) {
+      createUniqueIndex(context, connection);
+    }
+  }
+
+  private void createUniqueIndex(Context context, Connection connection) {
+    if (!DatabaseUtils.indexExistsIgnoreCase(TABLE_NAME, INDEX_NAME, connection)) {
+      context.execute(new CreateIndexBuilder(getDialect())
+        .setTable(TABLE_NAME)
+        .setName(INDEX_NAME)
+        .addColumn(COLUMN_NAME_RULE_UUID, false)
+        .addColumn(COLUMN_NAME_VALUE, false)
+        .addColumn(COLUMN_NAME_IS_SYSTEM_TAG, false)
+        .setUnique(true)
+        .build());
+    }
+  }
+}
diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v202501/DbVersion202501.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v202501/DbVersion202501.java
index 6ad62d2f856..b2d1d16893d 100644
--- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v202501/DbVersion202501.java
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v202501/DbVersion202501.java
@@ -40,6 +40,7 @@ public class DbVersion202501 implements DbVersion {
         MigrateConfidentialHeaderProperty.class)
       .add(2025_01_001, "Delete removed complexity measures from 'project_measures' table", DeleteRemovedComplexityMeasuresFromProjectMeasures.class)
       .add(2025_01_002, "Delete removed complexity metrics from 'metrics' table", DeleteRemovedComplexityMetrics.class)
+      .add(2025_01_003, "Create index on 'rule_tags' table", CreateIndexOnRuleTagsTable.class)
     ;
   }
 }