From cae2da28bfa432b9ec440219b514b057f5897c4f Mon Sep 17 00:00:00 2001 From: Belen Pruvost Date: Wed, 13 Oct 2021 15:41:19 +0200 Subject: [PATCH] SONAR-15441 - Create table for qgate_group_permissions --- .../java/org/sonar/db/version/SqTables.java | 1 + server/sonar-db-dao/src/schema/schema-sq.ddl | 9 +++ .../v92/CreateQGateGroupPermissionsTable.java | 32 ++++++++ .../v92/CreateQGatePermissionsTable.java | 77 +++++++++++++++++++ .../v92/CreateQGateUserPermissionsTable.java | 47 +---------- .../db/migration/version/v92/DbVersion92.java | 3 +- .../CreateQGateGroupPermissionsTableTest.java | 57 ++++++++++++++ .../schema.sql | 1 + 8 files changed, 182 insertions(+), 45 deletions(-) create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v92/CreateQGateGroupPermissionsTable.java create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v92/CreateQGatePermissionsTable.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v92/CreateQGateGroupPermissionsTableTest.java create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v92/CreateQGateGroupPermissionsTableTest/schema.sql diff --git a/server/sonar-db-core/src/main/java/org/sonar/db/version/SqTables.java b/server/sonar-db-core/src/main/java/org/sonar/db/version/SqTables.java index 8ca9b9f9405..5d2b3174ddb 100644 --- a/server/sonar-db-core/src/main/java/org/sonar/db/version/SqTables.java +++ b/server/sonar-db-core/src/main/java/org/sonar/db/version/SqTables.java @@ -87,6 +87,7 @@ public final class SqTables { "qprofile_edit_groups", "qprofile_edit_users", "quality_gates", + "qgate_group_permissions", "quality_gate_conditions", "saml_message_ids", "rules", diff --git a/server/sonar-db-dao/src/schema/schema-sq.ddl b/server/sonar-db-dao/src/schema/schema-sq.ddl index 2d69105804f..2d9f8ca32fd 100644 --- a/server/sonar-db-dao/src/schema/schema-sq.ddl +++ b/server/sonar-db-dao/src/schema/schema-sq.ddl @@ -714,6 +714,15 @@ CREATE TABLE "PROPERTIES"( ALTER TABLE "PROPERTIES" ADD CONSTRAINT "PK_PROPERTIES" PRIMARY KEY("UUID"); CREATE INDEX "PROPERTIES_KEY" ON "PROPERTIES"("PROP_KEY"); +CREATE TABLE "QGATE_GROUP_PERMISSIONS"( + "UUID" VARCHAR(40) NOT NULL, + "QUALITY_GATE_UUID" VARCHAR(40) NOT NULL, + "GROUP_UUID" VARCHAR(40) NOT NULL, + "CREATED_AT" BIGINT NOT NULL +); +ALTER TABLE "QGATE_GROUP_PERMISSIONS" ADD CONSTRAINT "PK_QGATE_GROUP_PERMISSIONS" PRIMARY KEY("UUID"); +CREATE INDEX "QG_GROUPS_UUID_IDX" ON "QGATE_GROUP_PERMISSIONS"("QUALITY_GATE_UUID"); + CREATE TABLE "QGATE_USER_PERMISSIONS"( "UUID" VARCHAR(40) NOT NULL, "QUALITY_GATE_UUID" VARCHAR(40) NOT NULL, diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v92/CreateQGateGroupPermissionsTable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v92/CreateQGateGroupPermissionsTable.java new file mode 100644 index 00000000000..8c0fec9037a --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v92/CreateQGateGroupPermissionsTable.java @@ -0,0 +1,32 @@ +/* + * SonarQube + * Copyright (C) 2009-2021 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.v92; + +import org.sonar.db.Database; + +public class CreateQGateGroupPermissionsTable extends CreateQGatePermissionsTable { + private static final String TABLE_NAME = "qgate_group_permissions"; + private static final String QUALITY_GATE_UUID_INDEX = "qg_groups_uuid_idx"; + private static final String COLUMN_NAME = "group_uuid"; + + public CreateQGateGroupPermissionsTable(Database db) { + super(db, TABLE_NAME, QUALITY_GATE_UUID_INDEX, COLUMN_NAME); + } +} diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v92/CreateQGatePermissionsTable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v92/CreateQGatePermissionsTable.java new file mode 100644 index 00000000000..93d424656da --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v92/CreateQGatePermissionsTable.java @@ -0,0 +1,77 @@ +/* + * SonarQube + * Copyright (C) 2009-2021 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.v92; + +import java.sql.SQLException; +import org.sonar.db.Database; +import org.sonar.db.DatabaseUtils; +import org.sonar.server.platform.db.migration.def.VarcharColumnDef; +import org.sonar.server.platform.db.migration.sql.CreateIndexBuilder; +import org.sonar.server.platform.db.migration.sql.CreateTableBuilder; +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.VarcharColumnDef.UUID_SIZE; +import static org.sonar.server.platform.db.migration.def.VarcharColumnDef.newVarcharColumnDefBuilder; + +public abstract class CreateQGatePermissionsTable extends DdlChange { + private final String tableName; + private final String indexName; + private final String columnName; + + protected CreateQGatePermissionsTable(Database db, String tableName, String indexName, String columnName) { + super(db); + this.tableName = tableName; + this.indexName = indexName; + this.columnName = columnName; + } + + @Override + public void execute(Context context) throws SQLException { + if (tableExists()) { + return; + } + + VarcharColumnDef qualityGateUuidColumn = newVarcharColumnBuilder("quality_gate_uuid").setIsNullable(false).setLimit(UUID_SIZE).build(); + context.execute(new CreateTableBuilder(getDialect(), tableName) + .addPkColumn(newVarcharColumnDefBuilder().setColumnName("uuid").setIsNullable(false).setLimit(UUID_SIZE).build()) + .addColumn(qualityGateUuidColumn) + .addColumn(newVarcharColumnBuilder(columnName).setIsNullable(false).setLimit(UUID_SIZE).build()) + .addColumn(newBigIntegerColumnDefBuilder().setColumnName("created_at").setIsNullable(false).build()) + .build()); + + CreateIndexBuilder builder = new CreateIndexBuilder() + .setTable(tableName) + .setName(indexName) + .setUnique(false) + .addColumn(qualityGateUuidColumn); + context.execute(builder.build()); + } + + private static VarcharColumnDef.Builder newVarcharColumnBuilder(String column) { + return newVarcharColumnDefBuilder().setColumnName(column); + } + + private boolean tableExists() throws SQLException { + try (var connection = getDatabase().getDataSource().getConnection()) { + return DatabaseUtils.tableExists(tableName, connection); + } + } +} diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v92/CreateQGateUserPermissionsTable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v92/CreateQGateUserPermissionsTable.java index d201ad278e5..86ff4c70c85 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v92/CreateQGateUserPermissionsTable.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v92/CreateQGateUserPermissionsTable.java @@ -19,55 +19,14 @@ */ package org.sonar.server.platform.db.migration.version.v92; -import java.sql.SQLException; import org.sonar.db.Database; -import org.sonar.db.DatabaseUtils; -import org.sonar.server.platform.db.migration.def.VarcharColumnDef; -import org.sonar.server.platform.db.migration.sql.CreateIndexBuilder; -import org.sonar.server.platform.db.migration.sql.CreateTableBuilder; -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.VarcharColumnDef.UUID_SIZE; -import static org.sonar.server.platform.db.migration.def.VarcharColumnDef.newVarcharColumnDefBuilder; - -public class CreateQGateUserPermissionsTable extends DdlChange { +public class CreateQGateUserPermissionsTable extends CreateQGatePermissionsTable { private static final String TABLE_NAME = "qgate_user_permissions"; private static final String QUALITY_GATE_UUID_INDEX = "quality_gate_uuid_idx"; + private static final String COLUMN_NAME = "user_uuid"; public CreateQGateUserPermissionsTable(Database db) { - super(db); - } - - @Override - public void execute(Context context) throws SQLException { - if (tableExists()) { - return; - } - - VarcharColumnDef qualityGateUuidColumn = newVarcharColumnBuilder("quality_gate_uuid").setIsNullable(false).setLimit(UUID_SIZE).build(); - context.execute(new CreateTableBuilder(getDialect(), TABLE_NAME) - .addPkColumn(newVarcharColumnDefBuilder().setColumnName("uuid").setIsNullable(false).setLimit(UUID_SIZE).build()) - .addColumn(qualityGateUuidColumn) - .addColumn(newVarcharColumnBuilder("user_uuid").setIsNullable(false).setLimit(UUID_SIZE).build()) - .addColumn(newBigIntegerColumnDefBuilder().setColumnName("created_at").setIsNullable(false).build()) - .build()); - - CreateIndexBuilder builder = new CreateIndexBuilder() - .setTable(TABLE_NAME) - .setName(QUALITY_GATE_UUID_INDEX) - .setUnique(false) - .addColumn(qualityGateUuidColumn); - context.execute(builder.build()); - } - - private static VarcharColumnDef.Builder newVarcharColumnBuilder(String column) { - return newVarcharColumnDefBuilder().setColumnName(column); - } - - private boolean tableExists() throws SQLException { - try (var connection = getDatabase().getDataSource().getConnection()) { - return DatabaseUtils.tableExists(TABLE_NAME, connection); - } + super(db, TABLE_NAME, QUALITY_GATE_UUID_INDEX, COLUMN_NAME); } } diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v92/DbVersion92.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v92/DbVersion92.java index 855459302f6..a9890d7b662 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v92/DbVersion92.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v92/DbVersion92.java @@ -29,6 +29,7 @@ public class DbVersion92 implements DbVersion { .add(6101, "Change size of column 'selection_expression' in 'Portfolios'", AlterSelectionExpressionInPortfoliosTable.class) .add(6102, "Migrate Bitbucket.org authentication plugin settings to built-in authentication settings", MigrateBibucketOrgPluginSettingsToBuiltInSettings.class) .add(6103, "Create column quick_fix_available in 'issues'", AddQuickFixAvailableColumnInIssuesTable.class) - .add(6104, "Create qgate_user_permissions Table", CreateQGateUserPermissionsTable.class); + .add(6104, "Create qgate_user_permissions Table", CreateQGateUserPermissionsTable.class) + .add(6105, "Create qgate_group_permissions Table", CreateQGateGroupPermissionsTable.class); } } diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v92/CreateQGateGroupPermissionsTableTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v92/CreateQGateGroupPermissionsTableTest.java new file mode 100644 index 00000000000..7d3a2380e2a --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v92/CreateQGateGroupPermissionsTableTest.java @@ -0,0 +1,57 @@ +/* + * SonarQube + * Copyright (C) 2009-2021 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.v92; + +import java.sql.SQLException; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.db.CoreDbTester; + +public class CreateQGateGroupPermissionsTableTest { + private final static String TABLE_NAME = "qgate_group_permissions"; + private final static String QUALITY_GATE_UUID_INDEX = "qg_groups_uuid_idx"; + + @Rule + public final CoreDbTester db = CoreDbTester.createForSchema(CreateQGateGroupPermissionsTableTest.class, "schema.sql"); + + private final CreateQGateGroupPermissionsTable underTest = new CreateQGateGroupPermissionsTable(db.database()); + + @Test + public void migration_should_create_a_table_with_index() throws SQLException { + db.assertTableDoesNotExist(TABLE_NAME); + + underTest.execute(); + + db.assertTableExists(TABLE_NAME); + db.assertIndex(TABLE_NAME, QUALITY_GATE_UUID_INDEX, "quality_gate_uuid"); + } + + @Test + public void migration_should_be_reentrant() throws SQLException { + db.assertTableDoesNotExist(TABLE_NAME); + + underTest.execute(); + //re-entrant + underTest.execute(); + + db.assertTableExists(TABLE_NAME); + db.assertIndex(TABLE_NAME, QUALITY_GATE_UUID_INDEX, "quality_gate_uuid"); + } +} diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v92/CreateQGateGroupPermissionsTableTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v92/CreateQGateGroupPermissionsTableTest/schema.sql new file mode 100644 index 00000000000..ed814d627c3 --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v92/CreateQGateGroupPermissionsTableTest/schema.sql @@ -0,0 +1 @@ +DROP TABLE IF EXISTS qgate_group_permissions; -- 2.39.5