SONAR-16419 Creation of new metadata columns in the rule table

6 files changed, 273 insertions, 2 deletions

diff --git a/server/sonar-db-dao/src/schema/schema-sq.ddl b/server/sonar-db-dao/src/schema/schema-sq.ddl
index 66155e186f4..dacc969bb29 100644
--- a/server/sonar-db-dao/src/schema/schema-sq.ddl
+++ b/server/sonar-db-dao/src/schema/schema-sq.ddl
@@ -848,7 +848,19 @@ CREATE TABLE "RULES"(
     "IS_EXTERNAL" BOOLEAN NOT NULL,
     "CREATED_AT" BIGINT,
     "UPDATED_AT" BIGINT,
-    "TEMPLATE_UUID" CHARACTER VARYING(40)
+    "TEMPLATE_UUID" CHARACTER VARYING(40),
+    "NOTE_DATA" CHARACTER LARGE OBJECT,
+    "NOTE_USER_UUID" CHARACTER VARYING(255),
+    "NOTE_CREATED_AT" BIGINT,
+    "NOTE_UPDATED_AT" BIGINT,
+    "REMEDIATION_FUNCTION" CHARACTER VARYING(20),
+    "REMEDIATION_GAP_MULT" CHARACTER VARYING(20),
+    "REMEDIATION_BASE_EFFORT" CHARACTER VARYING(20),
+    "TAGS" CHARACTER VARYING(4000),
+    "AD_HOC_NAME" CHARACTER VARYING(200),
+    "AD_HOC_DESCRIPTION" CHARACTER LARGE OBJECT,
+    "AD_HOC_SEVERITY" CHARACTER VARYING(10),
+    "AD_HOC_TYPE" TINYINT
 );
 ALTER TABLE "RULES" ADD CONSTRAINT "PK_RULES" PRIMARY KEY("UUID");
 CREATE UNIQUE INDEX "RULES_REPO_KEY" ON "RULES"("PLUGIN_RULE_KEY" NULLS FIRST, "PLUGIN_NAME" NULLS FIRST);
diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v00/CreateInitialSchema.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v00/CreateInitialSchema.java
index f3fb8d27207..f3ea9028e7a 100644
--- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v00/CreateInitialSchema.java
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v00/CreateInitialSchema.java
@@ -1462,7 +1462,7 @@ public class CreateInitialSchema extends DdlChange {
     context.execute(builder.build());
   }
 
-  private static VarcharColumnDef.Builder newVarcharColumnBuilder(String column) {
+  public static VarcharColumnDef.Builder newVarcharColumnBuilder(String column) {
     return newVarcharColumnDefBuilder().setColumnName(column);
   }
 
diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v95/AddRulesMetadataColumnsToRulesTable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v95/AddRulesMetadataColumnsToRulesTable.java
new file mode 100644
index 00000000000..5f0d75874a3
--- /dev/null
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v95/AddRulesMetadataColumnsToRulesTable.java
@@ -0,0 +1,157 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2022 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.v95;
+
+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.AddColumnsBuilder;
+import org.sonar.server.platform.db.migration.step.DdlChange;
+
+import static org.sonar.server.platform.db.migration.def.BigIntegerColumnDef.newBigIntegerColumnDefBuilder;
+import static org.sonar.server.platform.db.migration.def.ClobColumnDef.newClobColumnDefBuilder;
+import static org.sonar.server.platform.db.migration.def.TinyIntColumnDef.newTinyIntColumnDefBuilder;
+import static org.sonar.server.platform.db.migration.def.VarcharColumnDef.USER_UUID_SIZE;
+import static org.sonar.server.platform.db.migration.version.v00.CreateInitialSchema.newVarcharColumnBuilder;
+
+public class AddRulesMetadataColumnsToRulesTable extends DdlChange {
+
+  private static final String TABLE_NAME = "rules";
+
+
+  public AddRulesMetadataColumnsToRulesTable(Database db) {
+    super(db);
+  }
+
+  @Override
+  public void execute(Context context) throws SQLException {
+    try (var connection = getDatabase().getDataSource().getConnection()) {
+      createNoteDataAt(context, connection);
+      createNoteUserUuid(context, connection);
+      createNoteCreatedAt(context, connection);
+      createNoteUpdatedAt(context, connection);
+      createRemediationFunction(context, connection);
+      createRemediationGapMult(context, connection);
+      createRemediationBaseEffort(context, connection);
+      createTags(context, connection);
+      createAdHocName(context, connection);
+      createAdHocDescription(context, connection);
+      createAdHocSeverity(context, connection);
+      createAdHocType(context, connection);
+    }
+  }
+
+  private void createAdHocType(Context context, Connection connection) {
+    if (!DatabaseUtils.tableColumnExists(connection, TABLE_NAME, "ad_hoc_type")) {
+      context.execute(new AddColumnsBuilder(getDialect(), TABLE_NAME)
+        .addColumn(newTinyIntColumnDefBuilder().setColumnName("ad_hoc_type").setIsNullable(true).build())
+        .build());
+    }
+  }
+
+  private void createAdHocSeverity(Context context, Connection connection) {
+    if (!DatabaseUtils.tableColumnExists(connection, TABLE_NAME, "ad_hoc_severity")) {
+      context.execute(new AddColumnsBuilder(getDialect(), TABLE_NAME)
+        .addColumn(newVarcharColumnBuilder("ad_hoc_severity").setLimit(10).setIsNullable(true).build())
+        .build());
+    }
+  }
+
+  private void createAdHocDescription(Context context, Connection connection) {
+    if (!DatabaseUtils.tableColumnExists(connection, TABLE_NAME, "ad_hoc_description")) {
+      context.execute(new AddColumnsBuilder(getDialect(), TABLE_NAME)
+        .addColumn(newClobColumnDefBuilder().setColumnName("ad_hoc_description").setIsNullable(true).build())
+        .build());
+    }
+  }
+
+  private void createAdHocName(Context context, Connection connection) {
+    if (!DatabaseUtils.tableColumnExists(connection, TABLE_NAME, "ad_hoc_name")) {
+      context.execute(new AddColumnsBuilder(getDialect(), TABLE_NAME)
+        .addColumn(newVarcharColumnBuilder("ad_hoc_name").setLimit(200).setIsNullable(true).build())
+        .build());
+    }
+  }
+
+  private void createTags(Context context, Connection connection) {
+    if (!DatabaseUtils.tableColumnExists(connection, TABLE_NAME, "tags")) {
+      context.execute(new AddColumnsBuilder(getDialect(), TABLE_NAME)
+        .addColumn(newVarcharColumnBuilder("tags").setLimit(4_000).setIsNullable(true).build())
+        .build());
+    }
+  }
+
+  private void createRemediationBaseEffort(Context context, Connection connection) {
+    if (!DatabaseUtils.tableColumnExists(connection, TABLE_NAME, "remediation_base_effort")) {
+      context.execute(new AddColumnsBuilder(getDialect(), TABLE_NAME)
+        .addColumn(newVarcharColumnBuilder("remediation_base_effort").setLimit(20).setIsNullable(true).build())
+        .build());
+    }
+  }
+
+  private void createRemediationGapMult(Context context, Connection connection) {
+    if (!DatabaseUtils.tableColumnExists(connection, TABLE_NAME, "remediation_gap_mult")) {
+      context.execute(new AddColumnsBuilder(getDialect(), TABLE_NAME)
+        .addColumn(newVarcharColumnBuilder("remediation_gap_mult").setLimit(20).setIsNullable(true).build())
+        .build());
+    }
+  }
+
+  private void createRemediationFunction(Context context, Connection connection) {
+    if (!DatabaseUtils.tableColumnExists(connection, TABLE_NAME, "remediation_function")) {
+      context.execute(new AddColumnsBuilder(getDialect(), TABLE_NAME)
+        .addColumn(newVarcharColumnBuilder("remediation_function").setLimit(20).setIsNullable(true).build())
+        .build());
+    }
+  }
+
+  private void createNoteUpdatedAt(Context context, Connection connection) {
+    if (!DatabaseUtils.tableColumnExists(connection, TABLE_NAME, "note_updated_at")) {
+      context.execute(new AddColumnsBuilder(getDialect(), TABLE_NAME)
+        .addColumn(newBigIntegerColumnDefBuilder().setColumnName("note_updated_at").setIsNullable(true).build())
+        .build());
+    }
+  }
+
+  private void createNoteCreatedAt(Context context, Connection connection) {
+    if (!DatabaseUtils.tableColumnExists(connection, TABLE_NAME, "note_created_at")) {
+      context.execute(new AddColumnsBuilder(getDialect(), TABLE_NAME)
+        .addColumn(newBigIntegerColumnDefBuilder().setColumnName("note_created_at").setIsNullable(true).build())
+        .build());
+    }
+  }
+
+  private void createNoteUserUuid(Context context, Connection connection) {
+    if (!DatabaseUtils.tableColumnExists(connection, TABLE_NAME, "note_user_uuid")) {
+      context.execute(new AddColumnsBuilder(getDialect(), TABLE_NAME)
+        .addColumn(newVarcharColumnBuilder("note_user_uuid").setLimit(USER_UUID_SIZE).setIsNullable(true).build())
+        .build());
+    }
+  }
+
+  private void createNoteDataAt(Context context, Connection connection) {
+    if (!DatabaseUtils.tableColumnExists(connection, TABLE_NAME, "note_data")) {
+      context.execute(new AddColumnsBuilder(getDialect(), TABLE_NAME)
+        .addColumn(newClobColumnDefBuilder().setColumnName("note_data").setIsNullable(true).build())
+        .build());
+    }
+  }
+}
diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v95/DbVersion95.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v95/DbVersion95.java
index 2b2652dfeb6..014a6819a37 100644
--- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v95/DbVersion95.java
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v95/DbVersion95.java
@@ -37,6 +37,8 @@ public class DbVersion95 implements DbVersion {
       .add(6409, "Drop column CREATED_AT from RULES_METADATA table", DropRuleMetadataCreatedAtColumn.class)
       .add(6410, "Drop column UPDATED_AT from RULES_METADATA table", DropRuleMetadataUpdatedAtColumn.class)
       .add(6411, "Overwrite plugin file hash to force reloading rules", OverwritePluginFileHash.class)
+
+      .add(6412, "Add rules_metadata columns to rules table", AddRulesMetadataColumnsToRulesTable.class)
       ;
   }
 }
diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v95/AddRulesMetadataColumnsToRulesTableTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v95/AddRulesMetadataColumnsToRulesTableTest.java
new file mode 100644
index 00000000000..342a9bd8140
--- /dev/null
+++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v95/AddRulesMetadataColumnsToRulesTableTest.java
@@ -0,0 +1,73 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2022 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.v95;
+
+import java.sql.SQLException;
+import org.junit.Rule;
+import org.junit.Test;
+import org.sonar.db.CoreDbTester;
+import org.sonar.server.platform.db.migration.step.DdlChange;
+
+import static java.sql.Types.BIGINT;
+import static java.sql.Types.CLOB;
+import static java.sql.Types.TINYINT;
+import static java.sql.Types.VARCHAR;
+import static org.sonar.server.platform.db.migration.def.VarcharColumnDef.USER_UUID_SIZE;
+
+public class AddRulesMetadataColumnsToRulesTableTest {
+
+  private final String TABLE = "rules";
+
+  @Rule
+  public CoreDbTester db = CoreDbTester.createForSchema(AddRulesMetadataColumnsToRulesTableTest.class, "schema.sql");
+
+  private final DdlChange underTest = new AddRulesMetadataColumnsToRulesTable(db.database());
+
+  @Test
+  public void columns_are_created() throws SQLException {
+    underTest.execute();
+
+    verifyColumns();
+  }
+
+  @Test
+  public void migration_is_reentrant() throws SQLException {
+    underTest.execute();
+    underTest.execute();
+
+    verifyColumns();
+  }
+
+  private void verifyColumns() {
+    db.assertColumnDefinition(TABLE, "note_data", CLOB, null, true);
+    db.assertColumnDefinition(TABLE, "note_user_uuid", VARCHAR, USER_UUID_SIZE, true);
+    db.assertColumnDefinition(TABLE, "note_created_at", BIGINT, null, true);
+    db.assertColumnDefinition(TABLE, "note_updated_at", BIGINT, null, true);
+    db.assertColumnDefinition(TABLE, "remediation_function", VARCHAR, 20, true);
+    db.assertColumnDefinition(TABLE, "remediation_gap_mult", VARCHAR, 20, true);
+    db.assertColumnDefinition(TABLE, "remediation_base_effort", VARCHAR, 20, true);
+    db.assertColumnDefinition(TABLE, "tags", VARCHAR, 4000, true);
+    db.assertColumnDefinition(TABLE, "ad_hoc_name", VARCHAR, 200, true);
+    db.assertColumnDefinition(TABLE, "ad_hoc_description", CLOB, null, true);
+    db.assertColumnDefinition(TABLE, "ad_hoc_severity", VARCHAR, 10, true);
+    db.assertColumnDefinition(TABLE, "ad_hoc_type", TINYINT, null, true);
+  }
+
+}
\ No newline at end of file
diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v95/AddRulesMetadataColumnsToRulesTableTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v95/AddRulesMetadataColumnsToRulesTableTest/schema.sql
new file mode 100644
index 00000000000..43ad859a2ad
--- /dev/null
+++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v95/AddRulesMetadataColumnsToRulesTableTest/schema.sql
@@ -0,0 +1,27 @@
+CREATE TABLE "RULES"(
+    "UUID" CHARACTER VARYING(40) NOT NULL,
+    "NAME" CHARACTER VARYING(200),
+    "PLUGIN_RULE_KEY" CHARACTER VARYING(200) NOT NULL,
+    "PLUGIN_KEY" CHARACTER VARYING(200),
+    "PLUGIN_CONFIG_KEY" CHARACTER VARYING(200),
+    "PLUGIN_NAME" CHARACTER VARYING(255) NOT NULL,
+    "SCOPE" CHARACTER VARYING(20) NOT NULL,
+    "PRIORITY" INTEGER,
+    "STATUS" CHARACTER VARYING(40),
+    "LANGUAGE" CHARACTER VARYING(20),
+    "DEF_REMEDIATION_FUNCTION" CHARACTER VARYING(20),
+    "DEF_REMEDIATION_GAP_MULT" CHARACTER VARYING(20),
+    "DEF_REMEDIATION_BASE_EFFORT" CHARACTER VARYING(20),
+    "GAP_DESCRIPTION" CHARACTER VARYING(4000),
+    "SYSTEM_TAGS" CHARACTER VARYING(4000),
+    "IS_TEMPLATE" BOOLEAN DEFAULT FALSE NOT NULL,
+    "DESCRIPTION_FORMAT" CHARACTER VARYING(20),
+    "RULE_TYPE" TINYINT,
+    "SECURITY_STANDARDS" CHARACTER VARYING(4000),
+    "IS_AD_HOC" BOOLEAN NOT NULL,
+    "IS_EXTERNAL" BOOLEAN NOT NULL,
+    "CREATED_AT" BIGINT,
+    "UPDATED_AT" BIGINT
+);
+ALTER TABLE "RULES" ADD CONSTRAINT "PK_RULES" PRIMARY KEY("UUID");
+CREATE UNIQUE INDEX "RULES_REPO_KEY" ON "RULES"("PLUGIN_RULE_KEY" NULLS FIRST, "PLUGIN_NAME" NULLS FIRST);