From 065455b4612697f9529e91a883ad74db2c2d8e6b Mon Sep 17 00:00:00 2001 From: Pierre Date: Fri, 20 May 2022 14:19:37 +0200 Subject: [PATCH] SONAR-16419 Creation of new metadata columns in the rule table --- server/sonar-db-dao/src/schema/schema-sq.ddl | 14 +- .../version/v00/CreateInitialSchema.java | 2 +- .../AddRulesMetadataColumnsToRulesTable.java | 157 ++++++++++++++++++ .../db/migration/version/v95/DbVersion95.java | 2 + ...dRulesMetadataColumnsToRulesTableTest.java | 73 ++++++++ .../schema.sql | 27 +++ 6 files changed, 273 insertions(+), 2 deletions(-) create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v95/AddRulesMetadataColumnsToRulesTable.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v95/AddRulesMetadataColumnsToRulesTableTest.java create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v95/AddRulesMetadataColumnsToRulesTableTest/schema.sql 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); -- 2.39.5