From e27638112d3c561e5ac1042efb5d3ebf935c3570 Mon Sep 17 00:00:00 2001 From: =?utf8?q?S=C3=A9bastien=20Lesaint?= Date: Fri, 26 May 2017 14:52:30 +0200 Subject: [PATCH] SONAR-9028 add DB migrations cleaning USER_ROLES and GROUP_ROLES --- .../v65/CleanOrphanRowsInGroupRoles.java | 71 +++++++ .../v65/CleanOrphanRowsInUserRoles.java | 71 +++++++ .../db/migration/version/v65/DbVersion65.java | 4 +- .../v65/CleanOrphanRowsInGroupRolesTest.java | 188 ++++++++++++++++++ .../v65/CleanOrphanRowsInUserRolesTest.java | 176 ++++++++++++++++ .../version/v65/DbVersion65Test.java | 2 +- .../group_roles_and_projects.sql | 55 +++++ .../user_roles_and_projects.sql | 55 +++++ 8 files changed, 620 insertions(+), 2 deletions(-) create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInGroupRoles.java create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInUserRoles.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInGroupRolesTest.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInUserRolesTest.java create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInGroupRolesTest/group_roles_and_projects.sql create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInUserRolesTest/user_roles_and_projects.sql diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInGroupRoles.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInGroupRoles.java new file mode 100644 index 00000000000..a7b696f7ccc --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInGroupRoles.java @@ -0,0 +1,71 @@ +/* + * SonarQube + * Copyright (C) 2009-2017 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.v65; + +import java.sql.SQLException; +import org.sonar.db.Database; +import org.sonar.server.platform.db.migration.step.DataChange; +import org.sonar.server.platform.db.migration.step.MassUpdate; +import org.sonar.server.platform.db.migration.step.Select; +import org.sonar.server.platform.db.migration.step.SqlStatement; + +public class CleanOrphanRowsInGroupRoles extends DataChange { + public CleanOrphanRowsInGroupRoles(Database db) { + super(db); + } + + @Override + protected void execute(Context context) throws SQLException { + deleteRowsWithoutComponent(context); + deleteRowsForNonRootComponent(context); + } + + private static void deleteRowsWithoutComponent(Context context) throws SQLException { + MassUpdate massUpdate = context.prepareMassUpdate(); + massUpdate.select("select distinct gr.resource_id from group_roles gr where" + + " gr.resource_id is not null" + + " and not exists (select id from projects p where p.id = gr.resource_id)"); + massUpdate.rowPluralName("rows without component"); + massUpdate.update("delete from group_roles where resource_id = ?"); + massUpdate.execute(CleanOrphanRowsInGroupRoles::handle); + } + + private static void deleteRowsForNonRootComponent(Context context) throws SQLException { + MassUpdate massUpdate = context.prepareMassUpdate(); + massUpdate.select("select distinct gr.resource_id from group_roles gr" + + " inner join projects p on p.id = gr.resource_id" + + " where" + + " p.scope <> ?" + + " or (p.qualifier <> ? and p.qualifier <> ?)") + .setString(1, "PRJ") + .setString(2, "TRK") + .setString(3, "VW"); + massUpdate.rowPluralName("rows for non-root component"); + massUpdate.update("delete from group_roles where resource_id = ?"); + massUpdate.execute(CleanOrphanRowsInGroupRoles::handle); + } + + private static boolean handle(Select.Row row, SqlStatement update) throws SQLException { + long resourceId = row.getLong(1); + + update.setLong(1, resourceId); + return true; + } +} diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInUserRoles.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInUserRoles.java new file mode 100644 index 00000000000..18067273956 --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInUserRoles.java @@ -0,0 +1,71 @@ +/* + * SonarQube + * Copyright (C) 2009-2017 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.v65; + +import java.sql.SQLException; +import org.sonar.db.Database; +import org.sonar.server.platform.db.migration.step.DataChange; +import org.sonar.server.platform.db.migration.step.MassUpdate; +import org.sonar.server.platform.db.migration.step.Select; +import org.sonar.server.platform.db.migration.step.SqlStatement; + +public class CleanOrphanRowsInUserRoles extends DataChange { + public CleanOrphanRowsInUserRoles(Database db) { + super(db); + } + + @Override + protected void execute(Context context) throws SQLException { + deleteRowsWithoutComponent(context); + deleteRowsForNonRootComponent(context); + } + + private static void deleteRowsWithoutComponent(Context context) throws SQLException { + MassUpdate massUpdate = context.prepareMassUpdate(); + massUpdate.select("select distinct ur.resource_id from user_roles ur where" + + " ur.resource_id is not null" + + " and not exists (select id from projects p where p.id = ur.resource_id)"); + massUpdate.rowPluralName("rows without component"); + massUpdate.update("delete from user_roles where resource_id = ?"); + massUpdate.execute(CleanOrphanRowsInUserRoles::handle); + } + + private static void deleteRowsForNonRootComponent(Context context) throws SQLException { + MassUpdate massUpdate = context.prepareMassUpdate(); + massUpdate.select("select distinct ur.resource_id from user_roles ur" + + " inner join projects p on p.id = ur.resource_id" + + " where" + + " p.scope <> ?" + + " or (p.qualifier <> ? and p.qualifier <> ?)") + .setString(1, "PRJ") + .setString(2, "TRK") + .setString(3, "VW"); + massUpdate.rowPluralName("rows for non-root component"); + massUpdate.update("delete from user_roles where resource_id = ?"); + massUpdate.execute(CleanOrphanRowsInUserRoles::handle); + } + + private static boolean handle(Select.Row row, SqlStatement update) throws SQLException { + long resourceId = row.getLong(1); + + update.setLong(1, resourceId); + return true; + } +} diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v65/DbVersion65.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v65/DbVersion65.java index f379342aa4a..df3ac853930 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v65/DbVersion65.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v65/DbVersion65.java @@ -27,6 +27,8 @@ public class DbVersion65 implements DbVersion { @Override public void addSteps(MigrationStepRegistry registry) { registry - .add(1700, "Drop table AUTHORS", DropTableAuthors.class); + .add(1700, "Drop table AUTHORS", DropTableAuthors.class) + .add(1701, "Clean orphans from USER_ROLES", CleanOrphanRowsInUserRoles.class) + .add(1702, "Clean orphans from GROUP_ROLES", CleanOrphanRowsInGroupRoles.class); } } diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInGroupRolesTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInGroupRolesTest.java new file mode 100644 index 00000000000..d5ca24e53ac --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInGroupRolesTest.java @@ -0,0 +1,188 @@ +/* + * SonarQube + * Copyright (C) 2009-2017 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.v65; + +import java.sql.SQLException; +import java.util.Random; +import javax.annotation.Nullable; +import org.junit.Rule; +import org.junit.Test; +import org.junit.rules.ExpectedException; +import org.sonar.db.CoreDbTester; + +import static java.lang.String.valueOf; +import static org.apache.commons.lang.RandomStringUtils.randomAlphanumeric; +import static org.assertj.core.api.Assertions.assertThat; + +public class CleanOrphanRowsInGroupRolesTest { + private static final String TABLE_GROUP_ROLES = "group_roles"; + private static final String PROJECT_SCOPE = "PRJ"; + private static final String QUALIFIER_VW = "VW"; + private static final String QUALIFIER_TRK = "TRK"; + + @Rule + public CoreDbTester db = CoreDbTester.createForSchema(CleanOrphanRowsInGroupRolesTest.class, "group_roles_and_projects.sql"); + @Rule + public ExpectedException expectedException = ExpectedException.none(); + + private CleanOrphanRowsInGroupRoles underTest = new CleanOrphanRowsInGroupRoles(db.database()); + + @Test + public void execute_has_no_effect_on_empty_table() throws SQLException { + underTest.execute(); + + assertThat(db.countRowsOfTable(TABLE_GROUP_ROLES)).isZero(); + } + + @Test + public void execute_does_not_delete_rows_without_resource_id() throws SQLException { + insertGroupRole(null); + + underTest.execute(); + + assertThat(db.countRowsOfTable(TABLE_GROUP_ROLES)).isEqualTo(1); + + } + + @Test + public void execute_deletes_rows_of_non_existent_component() throws SQLException { + insertGroupRole(new Random().nextInt()); + + underTest.execute(); + + assertThat(db.countRowsOfTable(TABLE_GROUP_ROLES)).isEqualTo(0); + } + + @Test + public void execute_deletes_rows_of_component_without_qualifier_PRJ() throws SQLException { + String scope = randomAlphanumeric(3); + insertGroupRole(insertComponent(scope, QUALIFIER_TRK)); + insertGroupRole(insertComponent(scope, QUALIFIER_VW)); + insertGroupRole(insertComponent(scope, randomAlphanumeric(3))); + assertThat(db.countRowsOfTable(TABLE_GROUP_ROLES)).isEqualTo(3); + + underTest.execute(); + + assertThat(db.countRowsOfTable(TABLE_GROUP_ROLES)).isEqualTo(0); + } + + @Test + public void execute_keeps_rows_with_qualifier_TRK() throws SQLException { + executeKeepsRowsWithSpecifiedQualifierAndScopeProject(QUALIFIER_TRK); + } + + @Test + public void execute_keeps_rows_with_qualifier_VW() throws SQLException { + executeKeepsRowsWithSpecifiedQualifierAndScopeProject(QUALIFIER_VW); + } + + @Test + public void execute_deletes_rows_of_component_with_qualifier_DEV() throws SQLException { + executeDeletesRowsWithSpecifiedQualifierAndScopeProject("DEV"); + } + + @Test + public void execute_deletes_rows_of_component_with_qualifier_DEV_PRJ() throws SQLException { + executeDeletesRowsWithSpecifiedQualifierAndScopeProject("DEV_PRJ"); + } + + @Test + public void execute_deletes_rows_of_component_with_qualifier_BRC() throws SQLException { + executeDeletesRowsWithSpecifiedQualifierAndScopeProject("BRC"); + } + + @Test + public void execute_deletes_rows_of_component_with_qualifier_DIR() throws SQLException { + executeDeletesRowsWithSpecifiedQualifierAndScopeProject("DIR"); + } + + @Test + public void execute_deletes_rows_of_component_with_qualifier_FIL() throws SQLException { + executeDeletesRowsWithSpecifiedQualifierAndScopeProject("FIL"); + } + + @Test + public void execute_deletes_rows_of_component_with_qualifier_UTS() throws SQLException { + executeDeletesRowsWithSpecifiedQualifierAndScopeProject("UTS"); + } + + @Test + public void execute_deletes_rows_of_component_with_unknown_qualifier() throws SQLException { + executeDeletesRowsWithSpecifiedQualifierAndScopeProject(randomAlphanumeric(3)); + } + + private void executeDeletesRowsWithSpecifiedQualifierAndScopeProject(String qualifier) throws SQLException { + int componentId = insertComponent(PROJECT_SCOPE, qualifier); + insertGroupRole(componentId); + insertAnyoneRole(componentId); + assertThat(db.countRowsOfTable(TABLE_GROUP_ROLES)).isEqualTo(2); + + underTest.execute(); + + assertThat(db.countRowsOfTable(TABLE_GROUP_ROLES)).isEqualTo(0); + } + + private void executeKeepsRowsWithSpecifiedQualifierAndScopeProject(String qualifier) throws SQLException { + int componentId = insertComponent(PROJECT_SCOPE, qualifier); + insertGroupRole(componentId); + insertAnyoneRole(componentId); + assertThat(db.countRowsOfTable(TABLE_GROUP_ROLES)).isEqualTo(2); + + underTest.execute(); + + assertThat(db.countRowsOfTable(TABLE_GROUP_ROLES)).isEqualTo(2); + } + + private void insertAnyoneRole(@Nullable Integer componentId) { + insertGroupRole(null, componentId); + } + + private void insertGroupRole(@Nullable Integer componentId) { + insertGroupRole(new Random().nextInt(), componentId); + } + + private void insertGroupRole(@Nullable Integer groupId, @Nullable Integer componentId) { + db.executeInsert( + TABLE_GROUP_ROLES, + "organization_uuid", randomAlphanumeric(3), + "group_id", groupId == null ? null : valueOf(groupId), + "resource_id", componentId == null ? null : valueOf(componentId), + "role", randomAlphanumeric(5)); + } + + private int insertComponent(@Nullable String scope, @Nullable String qualifier) { + return insertComponent(randomAlphanumeric(30), scope, qualifier); + } + + private int insertComponent(String uuid, @Nullable String scope, @Nullable String qualifier) { + db.executeInsert( + "projects", + "organization_uuid", randomAlphanumeric(3), + "uuid", uuid, + "uuid_path", "path_of_" + uuid, + "root_uuid", uuid, + "project_uuid", uuid, + "scope", scope, + "qualifier", qualifier, + "private", valueOf(new Random().nextBoolean()), + "enabled", valueOf(new Random().nextBoolean())); + return ((Long) db.selectFirst("select id as \"ID\" from projects where uuid='" + uuid + "'").get("ID")).intValue(); + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInUserRolesTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInUserRolesTest.java new file mode 100644 index 00000000000..7d7a684601e --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInUserRolesTest.java @@ -0,0 +1,176 @@ +/* + * SonarQube + * Copyright (C) 2009-2017 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.v65; + +import java.sql.SQLException; +import java.util.Random; +import javax.annotation.Nullable; +import org.junit.Rule; +import org.junit.Test; +import org.junit.rules.ExpectedException; +import org.sonar.db.CoreDbTester; + +import static java.lang.String.valueOf; +import static org.apache.commons.lang.RandomStringUtils.randomAlphanumeric; +import static org.assertj.core.api.Assertions.assertThat; + +public class CleanOrphanRowsInUserRolesTest { + private static final String TABLE_USER_ROLES = "user_roles"; + private static final String PROJECT_SCOPE = "PRJ"; + private static final String QUALIFIER_VW = "VW"; + private static final String QUALIFIER_TRK = "TRK"; + + @Rule + public CoreDbTester db = CoreDbTester.createForSchema(CleanOrphanRowsInUserRolesTest.class, "user_roles_and_projects.sql"); + @Rule + public ExpectedException expectedException = ExpectedException.none(); + + private CleanOrphanRowsInUserRoles underTest = new CleanOrphanRowsInUserRoles(db.database()); + + @Test + public void execute_has_no_effect_on_empty_table() throws SQLException { + underTest.execute(); + + assertThat(db.countRowsOfTable(TABLE_USER_ROLES)).isZero(); + } + + @Test + public void execute_does_not_delete_rows_without_resource_id() throws SQLException { + insertUserRole(null); + + underTest.execute(); + + assertThat(db.countRowsOfTable(TABLE_USER_ROLES)).isEqualTo(1); + + } + + @Test + public void execute_deletes_rows_of_non_existent_component() throws SQLException { + insertUserRole(new Random().nextInt()); + + underTest.execute(); + + assertThat(db.countRowsOfTable(TABLE_USER_ROLES)).isEqualTo(0); + } + + @Test + public void execute_deletes_rows_of_component_without_qualifier_PRJ() throws SQLException { + String scope = randomAlphanumeric(3); + insertUserRole(insertComponent(scope, QUALIFIER_TRK)); + insertUserRole(insertComponent(scope, QUALIFIER_VW)); + insertUserRole(insertComponent(scope, randomAlphanumeric(3))); + assertThat(db.countRowsOfTable(TABLE_USER_ROLES)).isEqualTo(3); + + underTest.execute(); + + assertThat(db.countRowsOfTable(TABLE_USER_ROLES)).isEqualTo(0); + } + + @Test + public void execute_keeps_rows_with_qualifier_TRK() throws SQLException { + executeKeepsRowsWithSpecifiedQualifierAndScopeProject(QUALIFIER_TRK); + } + + @Test + public void execute_keeps_rows_with_qualifier_VW() throws SQLException { + executeKeepsRowsWithSpecifiedQualifierAndScopeProject(QUALIFIER_VW); + } + + @Test + public void execute_deletes_rows_of_component_with_qualifier_DEV() throws SQLException { + executeDeletesRowsWithSpecifiedQualifierAndScopeProject("DEV"); + } + + @Test + public void execute_deletes_rows_of_component_with_qualifier_DEV_PRJ() throws SQLException { + executeDeletesRowsWithSpecifiedQualifierAndScopeProject("DEV_PRJ"); + } + + @Test + public void execute_deletes_rows_of_component_with_qualifier_BRC() throws SQLException { + executeDeletesRowsWithSpecifiedQualifierAndScopeProject("BRC"); + } + + @Test + public void execute_deletes_rows_of_component_with_qualifier_DIR() throws SQLException { + executeDeletesRowsWithSpecifiedQualifierAndScopeProject("DIR"); + } + + @Test + public void execute_deletes_rows_of_component_with_qualifier_FIL() throws SQLException { + executeDeletesRowsWithSpecifiedQualifierAndScopeProject("FIL"); + } + + @Test + public void execute_deletes_rows_of_component_with_qualifier_UTS() throws SQLException { + executeDeletesRowsWithSpecifiedQualifierAndScopeProject("UTS"); + } + + @Test + public void execute_deletes_rows_of_component_with_unknown_qualifier() throws SQLException { + executeDeletesRowsWithSpecifiedQualifierAndScopeProject(randomAlphanumeric(3)); + } + + private void executeDeletesRowsWithSpecifiedQualifierAndScopeProject(String qualifier) throws SQLException { + int componentId = insertComponent(PROJECT_SCOPE, qualifier); + insertUserRole(componentId); + + underTest.execute(); + + assertThat(db.countRowsOfTable(TABLE_USER_ROLES)).isEqualTo(0); + } + + private void executeKeepsRowsWithSpecifiedQualifierAndScopeProject(String qualifier) throws SQLException { + int componentId = insertComponent(PROJECT_SCOPE, qualifier); + insertUserRole(componentId); + + underTest.execute(); + + assertThat(db.countRowsOfTable(TABLE_USER_ROLES)).isEqualTo(1); + } + + private void insertUserRole(@Nullable Integer componentId) { + db.executeInsert( + TABLE_USER_ROLES, + "organization_uuid", randomAlphanumeric(3), + "user_id", valueOf(new Random().nextInt()), + "resource_id", componentId == null ? null : valueOf(componentId), + "role", randomAlphanumeric(5)); + } + + private int insertComponent(@Nullable String scope, @Nullable String qualifier) { + return insertComponent(randomAlphanumeric(30), scope, qualifier); + } + + private int insertComponent(String uuid, @Nullable String scope, @Nullable String qualifier) { + db.executeInsert( + "projects", + "organization_uuid", randomAlphanumeric(3), + "uuid", uuid, + "uuid_path", "path_of_" + uuid, + "root_uuid", uuid, + "project_uuid", uuid, + "scope", scope, + "qualifier", qualifier, + "private", valueOf(new Random().nextBoolean()), + "enabled", valueOf(new Random().nextBoolean())); + return ((Long) db.selectFirst("select id as \"ID\" from projects where uuid='" + uuid + "'").get("ID")).intValue(); + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/DbVersion65Test.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/DbVersion65Test.java index 6b8970797ba..fa2bfdb7dcb 100644 --- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/DbVersion65Test.java +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/DbVersion65Test.java @@ -35,6 +35,6 @@ public class DbVersion65Test { @Test public void verify_migration_count() { - verifyMigrationCount(underTest, 1); + verifyMigrationCount(underTest, 3); } } diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInGroupRolesTest/group_roles_and_projects.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInGroupRolesTest/group_roles_and_projects.sql new file mode 100644 index 00000000000..396d1c7ac90 --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInGroupRolesTest/group_roles_and_projects.sql @@ -0,0 +1,55 @@ +CREATE TABLE "GROUP_ROLES" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "ORGANIZATION_UUID" VARCHAR(40) NOT NULL, + "GROUP_ID" INTEGER, + "RESOURCE_ID" INTEGER, + "ROLE" VARCHAR(64) NOT NULL +); +CREATE INDEX "GROUP_ROLES_RESOURCE" ON "GROUP_ROLES" ("RESOURCE_ID"); +CREATE UNIQUE INDEX "UNIQ_GROUP_ROLES" ON "GROUP_ROLES" ("ORGANIZATION_UUID", "GROUP_ID", "RESOURCE_ID", "ROLE"); + +CREATE TABLE "PROJECTS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "ORGANIZATION_UUID" VARCHAR(40) NOT NULL, + "KEE" VARCHAR(400), + "UUID" VARCHAR(50) NOT NULL, + "UUID_PATH" VARCHAR(1500) NOT NULL, + "ROOT_UUID" VARCHAR(50) NOT NULL, + "PROJECT_UUID" VARCHAR(50) NOT NULL, + "MODULE_UUID" VARCHAR(50), + "MODULE_UUID_PATH" VARCHAR(1500), + "NAME" VARCHAR(2000), + "DESCRIPTION" VARCHAR(2000), + "PRIVATE" BOOLEAN NOT NULL, + "TAGS" VARCHAR(500), + "ENABLED" BOOLEAN NOT NULL DEFAULT TRUE, + "SCOPE" VARCHAR(3), + "QUALIFIER" VARCHAR(10), + "DEPRECATED_KEE" VARCHAR(400), + "PATH" VARCHAR(2000), + "LANGUAGE" VARCHAR(20), + "COPY_COMPONENT_UUID" VARCHAR(50), + "LONG_NAME" VARCHAR(2000), + "DEVELOPER_UUID" VARCHAR(50), + "CREATED_AT" TIMESTAMP, + "AUTHORIZATION_UPDATED_AT" BIGINT, + "B_CHANGED" BOOLEAN, + "B_COPY_COMPONENT_UUID" VARCHAR(50), + "B_DESCRIPTION" VARCHAR(2000), + "B_ENABLED" BOOLEAN, + "B_UUID_PATH" VARCHAR(1500), + "B_LANGUAGE" VARCHAR(20), + "B_LONG_NAME" VARCHAR(500), + "B_MODULE_UUID" VARCHAR(50), + "B_MODULE_UUID_PATH" VARCHAR(1500), + "B_NAME" VARCHAR(500), + "B_PATH" VARCHAR(2000), + "B_QUALIFIER" VARCHAR(10) +); +CREATE INDEX "PROJECTS_ORGANIZATION" ON "PROJECTS" ("ORGANIZATION_UUID"); +CREATE UNIQUE INDEX "PROJECTS_KEE" ON "PROJECTS" ("KEE"); +CREATE INDEX "PROJECTS_ROOT_UUID" ON "PROJECTS" ("ROOT_UUID"); +CREATE UNIQUE INDEX "PROJECTS_UUID" ON "PROJECTS" ("UUID"); +CREATE INDEX "PROJECTS_PROJECT_UUID" ON "PROJECTS" ("PROJECT_UUID"); +CREATE INDEX "PROJECTS_MODULE_UUID" ON "PROJECTS" ("MODULE_UUID"); +CREATE INDEX "PROJECTS_QUALIFIER" ON "PROJECTS" ("QUALIFIER"); diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInUserRolesTest/user_roles_and_projects.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInUserRolesTest/user_roles_and_projects.sql new file mode 100644 index 00000000000..930ac35232c --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInUserRolesTest/user_roles_and_projects.sql @@ -0,0 +1,55 @@ +CREATE TABLE "USER_ROLES" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "ORGANIZATION_UUID" VARCHAR(40) NOT NULL, + "USER_ID" INTEGER, + "RESOURCE_ID" INTEGER, + "ROLE" VARCHAR(64) NOT NULL +); +CREATE INDEX "USER_ROLES_RESOURCE" ON "USER_ROLES" ("RESOURCE_ID"); +CREATE INDEX "USER_ROLES_USER" ON "USER_ROLES" ("USER_ID"); + +CREATE TABLE "PROJECTS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "ORGANIZATION_UUID" VARCHAR(40) NOT NULL, + "KEE" VARCHAR(400), + "UUID" VARCHAR(50) NOT NULL, + "UUID_PATH" VARCHAR(1500) NOT NULL, + "ROOT_UUID" VARCHAR(50) NOT NULL, + "PROJECT_UUID" VARCHAR(50) NOT NULL, + "MODULE_UUID" VARCHAR(50), + "MODULE_UUID_PATH" VARCHAR(1500), + "NAME" VARCHAR(2000), + "DESCRIPTION" VARCHAR(2000), + "PRIVATE" BOOLEAN NOT NULL, + "TAGS" VARCHAR(500), + "ENABLED" BOOLEAN NOT NULL DEFAULT TRUE, + "SCOPE" VARCHAR(3), + "QUALIFIER" VARCHAR(10), + "DEPRECATED_KEE" VARCHAR(400), + "PATH" VARCHAR(2000), + "LANGUAGE" VARCHAR(20), + "COPY_COMPONENT_UUID" VARCHAR(50), + "LONG_NAME" VARCHAR(2000), + "DEVELOPER_UUID" VARCHAR(50), + "CREATED_AT" TIMESTAMP, + "AUTHORIZATION_UPDATED_AT" BIGINT, + "B_CHANGED" BOOLEAN, + "B_COPY_COMPONENT_UUID" VARCHAR(50), + "B_DESCRIPTION" VARCHAR(2000), + "B_ENABLED" BOOLEAN, + "B_UUID_PATH" VARCHAR(1500), + "B_LANGUAGE" VARCHAR(20), + "B_LONG_NAME" VARCHAR(500), + "B_MODULE_UUID" VARCHAR(50), + "B_MODULE_UUID_PATH" VARCHAR(1500), + "B_NAME" VARCHAR(500), + "B_PATH" VARCHAR(2000), + "B_QUALIFIER" VARCHAR(10) +); +CREATE INDEX "PROJECTS_ORGANIZATION" ON "PROJECTS" ("ORGANIZATION_UUID"); +CREATE UNIQUE INDEX "PROJECTS_KEE" ON "PROJECTS" ("KEE"); +CREATE INDEX "PROJECTS_ROOT_UUID" ON "PROJECTS" ("ROOT_UUID"); +CREATE UNIQUE INDEX "PROJECTS_UUID" ON "PROJECTS" ("UUID"); +CREATE INDEX "PROJECTS_PROJECT_UUID" ON "PROJECTS" ("PROJECT_UUID"); +CREATE INDEX "PROJECTS_MODULE_UUID" ON "PROJECTS" ("MODULE_UUID"); +CREATE INDEX "PROJECTS_QUALIFIER" ON "PROJECTS" ("QUALIFIER"); -- 2.39.5