From ed9e9fe01378bf17277ed300689b1304ee4497f6 Mon Sep 17 00:00:00 2001 From: =?utf8?q?L=C3=A9o=20Geoffroy?= Date: Wed, 17 May 2023 19:07:41 +0200 Subject: [PATCH] SONAR-18856 Extract governance reports data from properties table --- .../java/org/sonar/db/version/SqTables.java | 2 + .../org/sonar/db/purge/PurgeCommandsIT.java | 45 ++++- .../java/org/sonar/db/purge/PurgeDaoIT.java | 38 +++++ .../sonar/db/report/ReportScheduleDaoIT.java | 107 ++++++++++++ .../db/report/ReportSubscriptionDaoIT.java | 160 ++++++++++++++++++ .../src/main/java/org/sonar/db/DaoModule.java | 4 + .../src/main/java/org/sonar/db/DbClient.java | 15 ++ .../src/main/java/org/sonar/db/MyBatis.java | 4 + .../org/sonar/db/purge/PurgeCommands.java | 26 +++ .../java/org/sonar/db/purge/PurgeDao.java | 2 + .../java/org/sonar/db/purge/PurgeMapper.java | 8 + .../sonar/db/report/ReportScheduleDao.java | 49 ++++++ .../sonar/db/report/ReportScheduleDto.java | 69 ++++++++ .../sonar/db/report/ReportScheduleMapper.java | 37 ++++ .../db/report/ReportSubscriptionDao.java | 64 +++++++ .../db/report/ReportSubscriptionDto.java | 70 ++++++++ .../db/report/ReportSubscriptionMapper.java | 41 +++++ .../org/sonar/db/purge/PurgeMapper.xml | 18 ++ .../sonar/db/report/ReportScheduleMapper.xml | 61 +++++++ .../db/report/ReportSubscriptionMapper.xml | 83 +++++++++ server/sonar-db-dao/src/schema/schema-sq.ddl | 18 ++ .../version/v101/AddReportSchedulesTable.java | 48 ++++++ .../v101/AddReportSubscriptionsTable.java | 47 +++++ ...ateUniqueIndexForReportSchedulesTable.java | 66 ++++++++ ...niqueIndexForReportSubscriptionsTable.java | 70 ++++++++ .../migration/version/v101/DbVersion101.java | 10 +- .../version/v101/PopulateReportSchedules.java | 70 ++++++++ .../v101/PopulateReportSubscriptions.java | 68 ++++++++ .../version/v101/RemoveReportProperties.java | 52 ++++++ .../v101/AddReportSchedulesTableTest.java | 65 +++++++ .../v101/AddReportSubscriptionsTableTest.java | 63 +++++++ ...niqueIndexForReportSchedulesTableTest.java | 55 ++++++ ...eIndexForReportSubscriptionsTableTest.java | 56 ++++++ .../v101/PopulateReportSchedulesTest.java | 123 ++++++++++++++ .../v101/PopulateReportSubscriptionsTest.java | 121 +++++++++++++ .../v101/RemoveReportPropertiesTest.java | 81 +++++++++ .../schema.sql | 7 + .../schema.sql | 7 + .../PopulateReportSchedulesTest/schema.sql | 53 ++++++ .../schema.sql | 53 ++++++ .../RemoveReportPropertiesTest/schema.sql | 12 ++ .../server/permission/index/FooIndex.java | 7 +- 42 files changed, 2048 insertions(+), 7 deletions(-) create mode 100644 server/sonar-db-dao/src/it/java/org/sonar/db/report/ReportScheduleDaoIT.java create mode 100644 server/sonar-db-dao/src/it/java/org/sonar/db/report/ReportSubscriptionDaoIT.java create mode 100644 server/sonar-db-dao/src/main/java/org/sonar/db/report/ReportScheduleDao.java create mode 100644 server/sonar-db-dao/src/main/java/org/sonar/db/report/ReportScheduleDto.java create mode 100644 server/sonar-db-dao/src/main/java/org/sonar/db/report/ReportScheduleMapper.java create mode 100644 server/sonar-db-dao/src/main/java/org/sonar/db/report/ReportSubscriptionDao.java create mode 100644 server/sonar-db-dao/src/main/java/org/sonar/db/report/ReportSubscriptionDto.java create mode 100644 server/sonar-db-dao/src/main/java/org/sonar/db/report/ReportSubscriptionMapper.java create mode 100644 server/sonar-db-dao/src/main/resources/org/sonar/db/report/ReportScheduleMapper.xml create mode 100644 server/sonar-db-dao/src/main/resources/org/sonar/db/report/ReportSubscriptionMapper.xml create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/AddReportSchedulesTable.java create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/AddReportSubscriptionsTable.java create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/CreateUniqueIndexForReportSchedulesTable.java create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/CreateUniqueIndexForReportSubscriptionsTable.java create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/PopulateReportSchedules.java create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/PopulateReportSubscriptions.java create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/RemoveReportProperties.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v101/AddReportSchedulesTableTest.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v101/AddReportSubscriptionsTableTest.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v101/CreateUniqueIndexForReportSchedulesTableTest.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v101/CreateUniqueIndexForReportSubscriptionsTableTest.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v101/PopulateReportSchedulesTest.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v101/PopulateReportSubscriptionsTest.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v101/RemoveReportPropertiesTest.java create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v101/CreateUniqueIndexForReportSchedulesTableTest/schema.sql create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v101/CreateUniqueIndexForReportSubscriptionsTableTest/schema.sql create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v101/PopulateReportSchedulesTest/schema.sql create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v101/PopulateReportSubscriptionsTest/schema.sql create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v101/RemoveReportPropertiesTest/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 0a1c7e536d1..013ef03f9ec 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 @@ -93,6 +93,8 @@ public final class SqTables { "qgate_group_permissions", "quality_gate_conditions", "saml_message_ids", + "report_schedules", + "report_subscriptions", "rules", "rule_desc_sections", "rules_parameters", diff --git a/server/sonar-db-dao/src/it/java/org/sonar/db/purge/PurgeCommandsIT.java b/server/sonar-db-dao/src/it/java/org/sonar/db/purge/PurgeCommandsIT.java index 2031f270006..e73c543cb4a 100644 --- a/server/sonar-db-dao/src/it/java/org/sonar/db/purge/PurgeCommandsIT.java +++ b/server/sonar-db-dao/src/it/java/org/sonar/db/purge/PurgeCommandsIT.java @@ -50,8 +50,11 @@ import org.sonar.db.issue.IssueDto; import org.sonar.db.metric.MetricDto; import org.sonar.db.newcodeperiod.NewCodePeriodType; import org.sonar.db.permission.GlobalPermission; +import org.sonar.db.portfolio.PortfolioDto; import org.sonar.db.portfolio.PortfolioProjectDto; import org.sonar.db.project.ProjectDto; +import org.sonar.db.report.ReportScheduleDto; +import org.sonar.db.report.ReportSubscriptionDto; import org.sonar.db.rule.RuleDto; import org.sonar.db.user.GroupDto; import org.sonar.db.user.UserDto; @@ -558,7 +561,7 @@ public class PurgeCommandsIT { underTest.deleteIssues(projectOrView.uuid()); assertThat(dbTester.countSql("select count(uuid) from new_code_reference_issues where issue_key in (" + - String.join(", ", issueKeys) + ")")).isZero(); + String.join(", ", issueKeys) + ")")).isZero(); } @Test @@ -738,6 +741,42 @@ public class PurgeCommandsIT { .containsExactlyInAnyOrder(tuple(portfolio1.getUuid(), project.getUuid(), Set.of("anotherBranch"))); } + @Test + public void deleteReportSchedules_shouldDeleteReportShedules_if_root_is_branch() { + BranchDto mainBranch = dbTester.components().insertPrivateProject().getMainBranchDto(); + BranchDto mainBranch2 = dbTester.components().insertPrivateProject().getMainBranchDto(); + dbTester.getDbClient().reportScheduleDao().upsert(dbTester.getSession(), new ReportScheduleDto().setUuid("uuid") + .setBranchUuid(mainBranch.getUuid()) + .setLastSendTimeInMs(1)); + dbTester.getDbClient().reportScheduleDao().upsert(dbTester.getSession(), new ReportScheduleDto().setUuid("uuid2") + .setBranchUuid(mainBranch2.getUuid()) + .setLastSendTimeInMs(2)); + PurgeCommands purgeCommands = new PurgeCommands(dbTester.getSession(), profiler, system2); + + purgeCommands.deleteReportSchedules(mainBranch.getUuid()); + assertThat(dbTester.getDbClient().reportScheduleDao().selectAll(dbTester.getSession())).hasSize(1) + .extracting(r -> r.getUuid()).containsExactly("uuid2"); + } + + @Test + public void deleteReportSubscriptions_shouldDeleteReportSubscriptions_if_root_is_branch() { + BranchDto mainBranch = dbTester.components().insertPrivateProject().getMainBranchDto(); + BranchDto mainBranch2 = dbTester.components().insertPrivateProject().getMainBranchDto(); + dbTester.getDbClient().reportSubscriptionDao().insert(dbTester.getSession(), new ReportSubscriptionDto().setUuid("uuid") + .setUserUuid("userUuid") + .setBranchUuid(mainBranch.getUuid())); + + dbTester.getDbClient().reportSubscriptionDao().insert(dbTester.getSession(), new ReportSubscriptionDto().setUuid("uuid2") + .setUserUuid("userUuid") + .setBranchUuid(mainBranch2.getUuid())); + + PurgeCommands purgeCommands = new PurgeCommands(dbTester.getSession(), profiler, system2); + + purgeCommands.deleteReportSubscriptions(mainBranch.getUuid()); + assertThat(dbTester.getDbClient().reportSubscriptionDao().selectAll(dbTester.getSession())).hasSize(1) + .extracting(r -> r.getUuid()).containsExactly("uuid2"); + } + private void addPermissions(ComponentDto root) { if (!root.isPrivate()) { dbTester.users().insertProjectPermissionOnAnyone("foo1", root); @@ -869,14 +908,14 @@ public class PurgeCommandsIT { @DataProvider public static Object[] projects() { - return new Object[] { + return new Object[]{ ComponentTesting.newPrivateProjectDto(), ComponentTesting.newPublicProjectDto(), }; } @DataProvider public static Object[] views() { - return new Object[] { + return new Object[]{ ComponentTesting.newPortfolio(), ComponentTesting.newApplication() }; } diff --git a/server/sonar-db-dao/src/it/java/org/sonar/db/purge/PurgeDaoIT.java b/server/sonar-db-dao/src/it/java/org/sonar/db/purge/PurgeDaoIT.java index 7f4bdeb425a..06a526ebd04 100644 --- a/server/sonar-db-dao/src/it/java/org/sonar/db/purge/PurgeDaoIT.java +++ b/server/sonar-db-dao/src/it/java/org/sonar/db/purge/PurgeDaoIT.java @@ -79,6 +79,8 @@ import org.sonar.db.newcodeperiod.NewCodePeriodType; import org.sonar.db.portfolio.PortfolioProjectDto; import org.sonar.db.project.ProjectDto; import org.sonar.db.property.PropertyDto; +import org.sonar.db.report.ReportScheduleDto; +import org.sonar.db.report.ReportSubscriptionDto; import org.sonar.db.rule.RuleDto; import org.sonar.db.source.FileSourceDto; import org.sonar.db.user.UserDismissedMessageDto; @@ -658,6 +660,8 @@ public class PurgeDaoIT { // properties exist or active and for inactive branch insertPropertyFor(appBranchComponent, otherAppBranchComponent); + insertReportScheduleAndSubscriptionForBranch(appBranch.getUuid(), dbSession); + underTest.deleteBranch(dbSession, appBranch.getUuid()); dbSession.commit(); @@ -669,6 +673,29 @@ public class PurgeDaoIT { assertThat(uuidsIn("app_projects", "application_uuid")).containsOnly(app.getProjectDto().getUuid(), otherApp.getProjectDto().getUuid()); assertThat(uuidsIn("app_branch_project_branch", "application_branch_uuid")).containsOnly(otherAppBranch.getUuid()); assertThat(componentUuidsIn("properties")).containsOnly(otherAppBranch.getUuid()); + assertThat(uuidsIn("report_schedules", "branch_uuid")).isEmpty(); + assertThat(uuidsIn("report_subscriptions", "branch_uuid")).isEmpty(); + + } + + private void insertReportScheduleAndSubscriptionForBranch(String branchUuid, DbSession dbSession) { + db.getDbClient().reportSubscriptionDao().insert(dbSession, new ReportSubscriptionDto().setUuid("uuid") + .setUserUuid("userUuid") + .setBranchUuid(branchUuid)); + + db.getDbClient().reportScheduleDao().upsert(dbSession, new ReportScheduleDto().setUuid("uuid") + .setBranchUuid(branchUuid) + .setLastSendTimeInMs(2)); + } + + private void insertReportScheduleAndSubscriptionForPortfolio(String uuid, String portfolioUuid, DbSession dbSession) { + db.getDbClient().reportSubscriptionDao().insert(dbSession, new ReportSubscriptionDto().setUuid(uuid) + .setUserUuid("userUuid") + .setPortfolioUuid(portfolioUuid)); + + db.getDbClient().reportScheduleDao().upsert(dbSession, new ReportScheduleDto().setUuid(uuid) + .setPortfolioUuid(portfolioUuid) + .setLastSendTimeInMs(2)); } @Test @@ -1700,15 +1727,26 @@ public class PurgeDaoIT { ComponentDto subview3 = db.components().insertComponent(newSubPortfolio(view)); ComponentDto pc = db.components().insertComponent(newProjectCopy("a", db.components().insertPrivateProject().getMainBranchComponent(), view)); insertPropertyFor(view, subview1, subview2, subview3, pc); + + insertReportScheduleAndSubscriptionForPortfolio("uuid", view.uuid(), dbSession); + insertReportScheduleAndSubscriptionForPortfolio("uuid2", subview1.uuid(), dbSession); + insertReportScheduleAndSubscriptionForPortfolio("uuid3", subview3.uuid(), dbSession); + assertThat(getResourceIdOfProperties()).containsOnly(view.uuid(), subview1.uuid(), subview2.uuid(), subview3.uuid(), pc.uuid()); underTest.deleteNonRootComponentsInView(dbSession, singletonList(subview1)); assertThat(getResourceIdOfProperties()) .containsOnly(view.uuid(), subview2.uuid(), subview3.uuid(), pc.uuid()); + assertThat(uuidsIn("report_schedules", "portfolio_uuid")).containsOnly(view.uuid(), subview3.uuid()); + assertThat(uuidsIn("report_subscriptions", "portfolio_uuid")).containsOnly(view.uuid(), subview3.uuid()); underTest.deleteNonRootComponentsInView(dbSession, asList(subview2, subview3, pc)); assertThat(getResourceIdOfProperties()) .containsOnly(view.uuid(), pc.uuid()); + + assertThat(uuidsIn("report_schedules", "portfolio_uuid")).containsOnly(view.uuid()); + assertThat(uuidsIn("report_subscriptions", "portfolio_uuid")).containsOnly(view.uuid()); + } @Test diff --git a/server/sonar-db-dao/src/it/java/org/sonar/db/report/ReportScheduleDaoIT.java b/server/sonar-db-dao/src/it/java/org/sonar/db/report/ReportScheduleDaoIT.java new file mode 100644 index 00000000000..61b94fe685a --- /dev/null +++ b/server/sonar-db-dao/src/it/java/org/sonar/db/report/ReportScheduleDaoIT.java @@ -0,0 +1,107 @@ +/* + * SonarQube + * Copyright (C) 2009-2023 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.db.report; + +import java.util.List; +import org.jetbrains.annotations.NotNull; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.api.utils.System2; +import org.sonar.db.DbTester; + +import static org.assertj.core.api.Assertions.assertThat; + +public class ReportScheduleDaoIT { + + @Rule + public DbTester db = DbTester.create(System2.INSTANCE, true); + + private final ReportScheduleDao underTest = db.getDbClient().reportScheduleDao(); + + @Test + public void upsert_shouldPersistCorrectDataOnBranch() { + ReportScheduleDto reportScheduleDto = newReportScheduleDto("uuid").setBranchUuid("branch_uuid").setLastSendTimeInMs(1L); + underTest.upsert(db.getSession(), reportScheduleDto); + + List actual = underTest.selectAll(db.getSession()); + assertThat(actual).hasSize(1); + assertThat(actual.get(0)).extracting(ReportScheduleDto::getBranchUuid, ReportScheduleDto::getLastSendTimeInMs).containsExactly("branch_uuid", 1L); + } + + @NotNull + private static ReportScheduleDto newReportScheduleDto(String uuid) { + return new ReportScheduleDto().setUuid(uuid); + } + + @Test + public void upsert_shouldPersistCorrectDataOnPortfolio() { + ReportScheduleDto reportScheduleDto = newReportScheduleDto("uuid").setPortfolioUuid("portfolio_uuid").setLastSendTimeInMs(1L); + underTest.upsert(db.getSession(), reportScheduleDto); + + List actual = underTest.selectAll(db.getSession()); + assertThat(actual).hasSize(1); + assertThat(actual.get(0)).extracting(ReportScheduleDto::getPortfolioUuid, ReportScheduleDto::getLastSendTimeInMs).containsExactly("portfolio_uuid", 1L); + } + + @Test + public void upsert_shouldUpdateLastSendTimeInMsOnBranch() { + ReportScheduleDto reportScheduleDto = newReportScheduleDto("uuid").setBranchUuid("branch_uuid").setLastSendTimeInMs(1L); + underTest.upsert(db.getSession(), reportScheduleDto); + + reportScheduleDto.setLastSendTimeInMs(2L); + underTest.upsert(db.getSession(), reportScheduleDto); + + List actual = underTest.selectAll(db.getSession()); + assertThat(actual).hasSize(1); + assertThat(actual.get(0)).extracting(ReportScheduleDto::getBranchUuid, ReportScheduleDto::getLastSendTimeInMs).containsExactly("branch_uuid", 2L); + + } + + @Test + public void upsert_shouldUpdateLastSendTimeInMsOnPortfolio() { + ReportScheduleDto reportScheduleDto = newReportScheduleDto("uuid").setPortfolioUuid("portfolio_uuid").setLastSendTimeInMs(1L); + underTest.upsert(db.getSession(), reportScheduleDto); + + reportScheduleDto.setLastSendTimeInMs(2L); + underTest.upsert(db.getSession(), reportScheduleDto); + + List actual = underTest.selectAll(db.getSession()); + assertThat(actual).hasSize(1); + assertThat(actual.get(0)).extracting(ReportScheduleDto::getPortfolioUuid, ReportScheduleDto::getLastSendTimeInMs).containsExactly("portfolio_uuid", 2L); + } + + @Test + public void selectByBranch_shouldRetrieveCorrectInformationOnBranch() { + ReportScheduleDto reportScheduleDto = newReportScheduleDto("uuid").setBranchUuid("branch_uuid").setLastSendTimeInMs(1L); + underTest.upsert(db.getSession(), reportScheduleDto); + assertThat(underTest.selectByBranch(db.getSession(), "branch_uuid")) + .isPresent().get() + .extracting(ReportScheduleDto::getBranchUuid, ReportScheduleDto::getLastSendTimeInMs).containsExactly("branch_uuid", 1L); + } + + @Test + public void selectByPortfolio_shouldRetrieveCorrectInformationOnPortfolio() { + ReportScheduleDto reportScheduleDto = newReportScheduleDto("uuid").setPortfolioUuid("portfolio_uuid").setLastSendTimeInMs(1L); + underTest.upsert(db.getSession(), reportScheduleDto); + assertThat(underTest.selectByPortfolio(db.getSession(), "portfolio_uuid")) + .isPresent().get() + .extracting(ReportScheduleDto::getPortfolioUuid, ReportScheduleDto::getLastSendTimeInMs).containsExactly("portfolio_uuid", 1L); + } +} diff --git a/server/sonar-db-dao/src/it/java/org/sonar/db/report/ReportSubscriptionDaoIT.java b/server/sonar-db-dao/src/it/java/org/sonar/db/report/ReportSubscriptionDaoIT.java new file mode 100644 index 00000000000..cce3d8bca28 --- /dev/null +++ b/server/sonar-db-dao/src/it/java/org/sonar/db/report/ReportSubscriptionDaoIT.java @@ -0,0 +1,160 @@ +/* + * SonarQube + * Copyright (C) 2009-2023 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.db.report; + +import java.util.List; +import java.util.Optional; +import java.util.Set; +import org.apache.ibatis.exceptions.PersistenceException; +import org.jetbrains.annotations.NotNull; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.api.utils.System2; +import org.sonar.core.util.UuidFactoryImpl; +import org.sonar.db.DbTester; + +import static org.assertj.core.api.Assertions.assertThat; +import static org.assertj.core.api.Assertions.assertThatThrownBy; +import static org.assertj.core.api.Assertions.tuple; + +public class ReportSubscriptionDaoIT { + + @Rule + public DbTester db = DbTester.create(System2.INSTANCE, true); + + private final ReportSubscriptionDao underTest = db.getDbClient().reportSubscriptionDao(); + + + @Test + public void insert_shouldInsertSubscriptionCorrectly() { + underTest.insert(db.getSession(), createSubscriptionDto("uuid").setPortfolioUuid("pf").setUserUuid("userUuid")); + Set reportSubscriptionDtos = underTest.selectAll(db.getSession()); + assertThat(reportSubscriptionDtos).hasSize(1); + assertThat(reportSubscriptionDtos.iterator().next()).extracting(r -> r.getUuid(), r -> r.getUserUuid(), r -> r.getBranchUuid(), r -> r.getPortfolioUuid()) + .containsExactly("uuid", "userUuid", null, "pf"); + } + + @Test + public void insert_shouldPersistOnlyOneSubscription() { + ReportSubscriptionDto subscriptionDto = createSubscriptionDto("uuid").setPortfolioUuid("pf").setUserUuid("userUuid"); + underTest.insert(db.getSession(), subscriptionDto); + db.getSession().commit(); + + assertThatThrownBy(()-> underTest.insert(db.getSession(), subscriptionDto)).isNotNull(); + } + + @Test + public void insert_shouldPersistDifferentSubscriptions() { + underTest.insert(db.getSession(), createSubscriptionDto("uuid").setBranchUuid("branch").setUserUuid("userUuid")); + underTest.insert(db.getSession(), createSubscriptionDto("uuid2").setBranchUuid("branch").setUserUuid("userUuid2")); + underTest.insert(db.getSession(), createSubscriptionDto("uuid3").setBranchUuid("branch2").setUserUuid("userUuid")); + underTest.insert(db.getSession(), createSubscriptionDto("uuid4").setPortfolioUuid("pf").setUserUuid("userUuid")); + + Set reportSubscriptionDtos = underTest.selectAll(db.getSession()); + assertThat(reportSubscriptionDtos).hasSize(4); + } + + @Test + public void delete_shouldRemoveExistingSubscription() { + ReportSubscriptionDto subscriptionPf = createSubscriptionDto("uuid").setPortfolioUuid("pf").setUserUuid("userUuid"); + ReportSubscriptionDto subscriptionBranch = createSubscriptionDto("uuid2").setBranchUuid("branch").setUserUuid("userUuid2"); + + underTest.insert(db.getSession(), subscriptionPf); + underTest.insert(db.getSession(), subscriptionBranch); + + underTest.delete(db.getSession(), subscriptionPf); + + assertThat(underTest.selectAll(db.getSession())).hasSize(1) + .extracting(p->p.getUuid()).containsExactly("uuid2"); + + underTest.delete(db.getSession(), subscriptionBranch); + + assertThat(underTest.selectAll(db.getSession())).isEmpty(); + } + + @Test + public void selectByPortfolio_shouldReturnRelatedListOfSubscriptions() { + ReportSubscriptionDto subscriptionPf = createSubscriptionDto("uuid").setPortfolioUuid("pf").setUserUuid("userUuid"); + ReportSubscriptionDto subscriptionPf2 = createSubscriptionDto("uuid2").setPortfolioUuid("pf").setUserUuid("userUuid2"); + ReportSubscriptionDto subscriptionBranch = createSubscriptionDto("uuid3").setBranchUuid("branch").setUserUuid("userUuid2"); + + underTest.insert(db.getSession(), subscriptionPf); + underTest.insert(db.getSession(), subscriptionPf2); + underTest.insert(db.getSession(), subscriptionBranch); + + List reportSubscriptionDtos = underTest.selectByPortfolio(db.getSession(), subscriptionPf.getPortfolioUuid()); + + assertThat(reportSubscriptionDtos).hasSize(2).extracting(r -> r.getUuid(), r -> r.getUserUuid(), r -> r.getPortfolioUuid()) + .containsExactly(tuple("uuid", "userUuid", "pf"), tuple("uuid2", "userUuid2", "pf")); + } + + @Test + public void selectByProjectBranch_shouldReturnRelatedListOfSubscriptions() { + ReportSubscriptionDto subscriptionBranch = createSubscriptionDto("uuid").setBranchUuid("branch").setUserUuid("userUuid"); + ReportSubscriptionDto subscriptionBranch2 = createSubscriptionDto("uuid2").setBranchUuid("branch").setUserUuid("userUuid2"); + ReportSubscriptionDto subscriptionPf = createSubscriptionDto("uuid3").setPortfolioUuid("pf1").setUserUuid("userUuid2"); + + underTest.insert(db.getSession(), subscriptionBranch); + underTest.insert(db.getSession(), subscriptionBranch2); + underTest.insert(db.getSession(), subscriptionPf); + + List reportSubscriptionDtos = underTest.selectByProjectBranch(db.getSession(), "branch"); + + assertThat(reportSubscriptionDtos).hasSize(2).extracting(r -> r.getUuid(), r -> r.getUserUuid(), r -> r.getBranchUuid()) + .containsExactly(tuple("uuid", "userUuid", "branch"), tuple("uuid2", "userUuid2", "branch")); + } + + @Test + public void selectByUserAndPortfolio_shouldReturnRelatedSubscription() { + ReportSubscriptionDto subscriptionPf = createSubscriptionDto("uuid").setPortfolioUuid("pf").setUserUuid("userUuid"); + ReportSubscriptionDto subscriptionPf2 = createSubscriptionDto("uuid2").setPortfolioUuid("pf").setUserUuid("userUuid2"); + ReportSubscriptionDto subscriptionBranch = createSubscriptionDto("uuid3").setBranchUuid("branch").setUserUuid("userUuid2"); + + underTest.insert(db.getSession(), subscriptionPf); + underTest.insert(db.getSession(), subscriptionPf2); + underTest.insert(db.getSession(), subscriptionBranch); + + Optional reportSubscriptionDtos = underTest.selectByUserAndPortfolio(db.getSession(), subscriptionPf.getPortfolioUuid(), subscriptionPf.getUserUuid()); + + assertThat(reportSubscriptionDtos).isPresent().get().extracting(r->r.getUuid()).isEqualTo("uuid"); + } + + @Test + public void selectByUserAndBranch_shouldReturnRelatedSubscription() { + ReportSubscriptionDto subscriptionPf = createSubscriptionDto("uuid").setPortfolioUuid("pf").setUserUuid("userUuid"); + ReportSubscriptionDto subscriptionPf2 = createSubscriptionDto("uuid2").setPortfolioUuid("pf").setUserUuid("userUuid2"); + ReportSubscriptionDto subscriptionBranch = createSubscriptionDto("uuid3").setBranchUuid("branch").setUserUuid("userUuid2"); + + underTest.insert(db.getSession(), subscriptionPf); + underTest.insert(db.getSession(), subscriptionPf2); + underTest.insert(db.getSession(), subscriptionBranch); + + Optional reportSubscriptionDtos = underTest.selectByUserAndBranch(db.getSession(), subscriptionBranch.getBranchUuid(), subscriptionBranch.getUserUuid()); + + assertThat(reportSubscriptionDtos).isPresent().get().extracting(r->r.getUuid()).isEqualTo("uuid3"); + } + + @NotNull + private static ReportSubscriptionDto createSubscriptionDto(String uuid) { + return new ReportSubscriptionDto().setUuid(uuid); + } + + +} diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/DaoModule.java b/server/sonar-db-dao/src/main/java/org/sonar/db/DaoModule.java index 44a996e4b41..aeb4c6ed446 100644 --- a/server/sonar-db-dao/src/main/java/org/sonar/db/DaoModule.java +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/DaoModule.java @@ -78,6 +78,8 @@ import org.sonar.db.qualityprofile.QProfileEditUsersDao; import org.sonar.db.qualityprofile.QualityProfileDao; import org.sonar.db.qualityprofile.QualityProfileExportDao; import org.sonar.db.report.RegulatoryReportDao; +import org.sonar.db.report.ReportScheduleDao; +import org.sonar.db.report.ReportSubscriptionDao; import org.sonar.db.rule.RuleDao; import org.sonar.db.rule.RuleRepositoryDao; import org.sonar.db.scannercache.ScannerAnalysisCacheDao; @@ -162,6 +164,8 @@ public class DaoModule extends Module { QualityProfileDao.class, QualityProfileExportDao.class, RegulatoryReportDao.class, + ReportSubscriptionDao.class, + ReportScheduleDao.class, RoleDao.class, RuleDao.class, RuleRepositoryDao.class, diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/DbClient.java b/server/sonar-db-dao/src/main/java/org/sonar/db/DbClient.java index 80add385af8..7acf9c8ad5b 100644 --- a/server/sonar-db-dao/src/main/java/org/sonar/db/DbClient.java +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/DbClient.java @@ -78,6 +78,8 @@ import org.sonar.db.qualityprofile.QProfileEditUsersDao; import org.sonar.db.qualityprofile.QualityProfileDao; import org.sonar.db.qualityprofile.QualityProfileExportDao; import org.sonar.db.report.RegulatoryReportDao; +import org.sonar.db.report.ReportScheduleDao; +import org.sonar.db.report.ReportSubscriptionDao; import org.sonar.db.rule.RuleDao; import org.sonar.db.rule.RuleRepositoryDao; import org.sonar.db.scannercache.ScannerAnalysisCacheDao; @@ -181,6 +183,9 @@ public class DbClient { private final ScimGroupDao scimGroupDao; private final EntityDao entityDao; + private final ReportScheduleDao reportScheduleDao; + private final ReportSubscriptionDao reportSubscriptionDao; + public DbClient(Database database, MyBatis myBatis, DBSessions dbSessions, Dao... daos) { this.database = database; this.myBatis = myBatis; @@ -266,6 +271,8 @@ public class DbClient { scimUserDao = getDao(map, ScimUserDao.class); scimGroupDao = getDao(map, ScimGroupDao.class); entityDao = getDao(map, EntityDao.class); + reportScheduleDao = getDao(map, ReportScheduleDao.class); + reportSubscriptionDao = getDao(map, ReportSubscriptionDao.class); } public DbSession openSession(boolean batch) { @@ -589,5 +596,13 @@ public class DbClient { public EntityDao entityDao() { return entityDao; } + + public ReportScheduleDao reportScheduleDao(){ + return reportScheduleDao; + } + + public ReportSubscriptionDao reportSubscriptionDao() { + return reportSubscriptionDao; + } } diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/MyBatis.java b/server/sonar-db-dao/src/main/java/org/sonar/db/MyBatis.java index f2aeefdcc27..88c2322862a 100644 --- a/server/sonar-db-dao/src/main/java/org/sonar/db/MyBatis.java +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/MyBatis.java @@ -138,6 +138,8 @@ import org.sonar.db.qualityprofile.QProfileEditUsersMapper; import org.sonar.db.qualityprofile.QualityProfileExportMapper; import org.sonar.db.qualityprofile.QualityProfileMapper; import org.sonar.db.report.RegulatoryReportMapper; +import org.sonar.db.report.ReportScheduleMapper; +import org.sonar.db.report.ReportSubscriptionMapper; import org.sonar.db.rule.RuleMapper; import org.sonar.db.rule.RuleParamDto; import org.sonar.db.rule.RuleRepositoryMapper; @@ -314,6 +316,8 @@ public class MyBatis { QualityProfileMapper.class, QualityProfileExportMapper.class, RegulatoryReportMapper.class, + ReportScheduleMapper.class, + ReportSubscriptionMapper.class, RoleMapper.class, RuleMapper.class, RuleRepositoryMapper.class, diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/purge/PurgeCommands.java b/server/sonar-db-dao/src/main/java/org/sonar/db/purge/PurgeCommands.java index 614043369e4..632bf5ad99f 100644 --- a/server/sonar-db-dao/src/main/java/org/sonar/db/purge/PurgeCommands.java +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/purge/PurgeCommands.java @@ -234,6 +234,16 @@ class PurgeCommands { uuidsPartitions.forEach(purgeMapper::deletePropertiesByComponentUuids); session.commit(); profiler.stop(); + + profiler.start("deleteByRootAndSubviews (report_schedules)"); + uuidsPartitions.forEach(purgeMapper::deleteReportSchedulesByPortfolioUuids); + session.commit(); + profiler.stop(); + + profiler.start("deleteByRootAndSubviews (report_subscriptions)"); + uuidsPartitions.forEach(purgeMapper::deleteReportSubscriptionsByPortfolioUuids); + session.commit(); + profiler.stop(); } void deleteDisabledComponentsWithoutIssues(List disabledComponentsWithoutIssue) { @@ -492,4 +502,20 @@ class PurgeCommands { profiler.stop(); } + public void deleteReportSchedules(String rootUuid) { + profiler.start("deleteReportSchedules (report_schedules)"); + purgeMapper.deleteReportSchedulesByBranchUuid(rootUuid); + session.commit(); + profiler.stop(); + } + + + public void deleteReportSubscriptions(String rootUuid){ + profiler.start("deleteReportSubscriptions (report_subscriptions)"); + purgeMapper.deleteReportSubscriptionsByBranchUuid(rootUuid); + session.commit(); + profiler.stop(); + } + + } diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/purge/PurgeDao.java b/server/sonar-db-dao/src/main/java/org/sonar/db/purge/PurgeDao.java index c8ae626baa0..b8524112225 100644 --- a/server/sonar-db-dao/src/main/java/org/sonar/db/purge/PurgeDao.java +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/purge/PurgeDao.java @@ -256,6 +256,8 @@ public class PurgeDao implements Dao { commands.deleteProject(rootUuid); commands.deleteUserDismissedMessages(rootUuid); commands.deleteOutdatedProperties(rootUuid); + commands.deleteReportSchedules(rootUuid); + commands.deleteReportSubscriptions(rootUuid); } /** diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/purge/PurgeMapper.java b/server/sonar-db-dao/src/main/java/org/sonar/db/purge/PurgeMapper.java index 66d60bdd6f7..15d05650cf6 100644 --- a/server/sonar-db-dao/src/main/java/org/sonar/db/purge/PurgeMapper.java +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/purge/PurgeMapper.java @@ -178,4 +178,12 @@ public interface PurgeMapper { void deleteUserDismissedMessagesByProjectUuid(@Param("projectUuid") String projectUuid); void deleteScannerAnalysisCacheByBranchUuid(@Param("branchUuid") String branchUuid); + + void deleteReportSchedulesByBranchUuid(@Param("branchUuid") String branchUuid); + + void deleteReportSubscriptionsByBranchUuid(@Param("branchUuid") String branchUuid); + + void deleteReportSchedulesByPortfolioUuids(@Param("portfolioUuids") List portfolioUuids); + + void deleteReportSubscriptionsByPortfolioUuids(@Param("portfolioUuids") List portfolioUuids); } diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/report/ReportScheduleDao.java b/server/sonar-db-dao/src/main/java/org/sonar/db/report/ReportScheduleDao.java new file mode 100644 index 00000000000..13b5b674b11 --- /dev/null +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/report/ReportScheduleDao.java @@ -0,0 +1,49 @@ +/* + * SonarQube + * Copyright (C) 2009-2023 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.db.report; + +import java.util.List; +import java.util.Optional; +import org.sonar.db.Dao; +import org.sonar.db.DbSession; + +public class ReportScheduleDao implements Dao { + public Optional selectByBranch(DbSession dbSession, String branchUuid) { + return mapper(dbSession).selectByBranch(branchUuid); + } + + public Optional selectByPortfolio(DbSession dbSession, String portfolioUuid) { + return mapper(dbSession).selectByPortfolio(portfolioUuid); + } + + public void upsert(DbSession dbSession, ReportScheduleDto reportScheduleDto) { + if (!mapper(dbSession).update(reportScheduleDto)) { + mapper(dbSession).insert(reportScheduleDto); + } + } + + private static ReportScheduleMapper mapper(DbSession dbSession) { + return dbSession.getMapper(ReportScheduleMapper.class); + } + + public List selectAll(DbSession dbSession) { + return mapper(dbSession).selectAll(); + } +} diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/report/ReportScheduleDto.java b/server/sonar-db-dao/src/main/java/org/sonar/db/report/ReportScheduleDto.java new file mode 100644 index 00000000000..4b1c1ff0d90 --- /dev/null +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/report/ReportScheduleDto.java @@ -0,0 +1,69 @@ +/* + * SonarQube + * Copyright (C) 2009-2023 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.db.report; + +public class ReportScheduleDto { + + private String uuid; + private String portfolioUuid; + private String branchUuid; + private long lastSendTimeInMs; + + public ReportScheduleDto() { + //Default constructor + } + + public String getUuid() { + return uuid; + } + + public ReportScheduleDto setUuid(String uuid) { + this.uuid = uuid; + return this; + } + + public String getPortfolioUuid() { + return portfolioUuid; + } + + public ReportScheduleDto setPortfolioUuid(String portfolioUuid) { + this.portfolioUuid = portfolioUuid; + return this; + } + + public String getBranchUuid() { + return branchUuid; + } + + public ReportScheduleDto setBranchUuid(String branchUuid) { + this.branchUuid = branchUuid; + return this; + + } + + public long getLastSendTimeInMs() { + return lastSendTimeInMs; + } + + public ReportScheduleDto setLastSendTimeInMs(long lastSendTimeInMs) { + this.lastSendTimeInMs = lastSendTimeInMs; + return this; + } +} diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/report/ReportScheduleMapper.java b/server/sonar-db-dao/src/main/java/org/sonar/db/report/ReportScheduleMapper.java new file mode 100644 index 00000000000..b156420d237 --- /dev/null +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/report/ReportScheduleMapper.java @@ -0,0 +1,37 @@ +/* + * SonarQube + * Copyright (C) 2009-2023 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.db.report; + +import java.util.List; +import java.util.Optional; +import javax.annotation.Nullable; +import org.apache.ibatis.annotations.Param; + +public interface ReportScheduleMapper { + Optional selectByBranch(@Param("branchUuid") String branchUuid); + + Optional selectByPortfolio(@Nullable @Param("portfolioUuid") String portfolioUuid); + + List selectAll(); + + boolean insert(ReportScheduleDto reportScheduleDto); + + boolean update(ReportScheduleDto reportScheduleDto); +} diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/report/ReportSubscriptionDao.java b/server/sonar-db-dao/src/main/java/org/sonar/db/report/ReportSubscriptionDao.java new file mode 100644 index 00000000000..a7cbc694306 --- /dev/null +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/report/ReportSubscriptionDao.java @@ -0,0 +1,64 @@ +/* + * SonarQube + * Copyright (C) 2009-2023 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.db.report; + +import java.util.List; +import java.util.Optional; +import java.util.Set; +import org.apache.ibatis.annotations.Param; +import org.sonar.db.Dao; +import org.sonar.db.DbSession; + +public class ReportSubscriptionDao implements Dao { + + public Optional selectByUserAndPortfolio(DbSession dbSession, String portfolioUuid, String userUuid) { + return mapper(dbSession).selectByUserAndPortfolio(portfolioUuid, userUuid); + + } + + public Optional selectByUserAndBranch(DbSession dbSession, @Param("branchUuid") String branchUuid, @Param("userUuid") String userUuid) { + return mapper(dbSession).selectByUserAndBranch(branchUuid, userUuid); + + } + + public List selectByPortfolio(DbSession dbSession, String portfolioUuid) { + return mapper(dbSession).selectByPortfolio(portfolioUuid); + } + + public List selectByProjectBranch(DbSession dbSession, String branchUuid) { + return mapper(dbSession).selectByBranch(branchUuid); + } + + public Set selectAll(DbSession dbSession) { + return mapper(dbSession).selectAll(); + } + + public void delete(DbSession dbSession, ReportSubscriptionDto subscriptionDto) { + mapper(dbSession).delete(subscriptionDto); + } + + public void insert(DbSession dbSession, ReportSubscriptionDto subscriptionDto) { + mapper(dbSession).insert(subscriptionDto); + } + + private static ReportSubscriptionMapper mapper(DbSession dbSession) { + return dbSession.getMapper(ReportSubscriptionMapper.class); + } +} diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/report/ReportSubscriptionDto.java b/server/sonar-db-dao/src/main/java/org/sonar/db/report/ReportSubscriptionDto.java new file mode 100644 index 00000000000..8c7f16d1fd2 --- /dev/null +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/report/ReportSubscriptionDto.java @@ -0,0 +1,70 @@ +/* + * SonarQube + * Copyright (C) 2009-2023 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.db.report; + +import javax.annotation.Nullable; + +public class ReportSubscriptionDto { + + private String uuid; + private String portfolioUuid; + private String branchUuid; + private String userUuid; + + public ReportSubscriptionDto() { + //Default constructor + } + + public String getUuid() { + return uuid; + } + + public ReportSubscriptionDto setUuid(String uuid) { + this.uuid = uuid; + return this; + } + + public String getPortfolioUuid() { + return portfolioUuid; + } + + public ReportSubscriptionDto setPortfolioUuid(@Nullable String portfolioUuid) { + this.portfolioUuid = portfolioUuid; + return this; + } + + public String getBranchUuid() { + return branchUuid; + } + + public ReportSubscriptionDto setBranchUuid(@Nullable String branchUuid) { + this.branchUuid = branchUuid; + return this; + } + + public String getUserUuid() { + return userUuid; + } + + public ReportSubscriptionDto setUserUuid(String userUuid) { + this.userUuid = userUuid; + return this; + } +} diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/report/ReportSubscriptionMapper.java b/server/sonar-db-dao/src/main/java/org/sonar/db/report/ReportSubscriptionMapper.java new file mode 100644 index 00000000000..ae58736c3dd --- /dev/null +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/report/ReportSubscriptionMapper.java @@ -0,0 +1,41 @@ +/* + * SonarQube + * Copyright (C) 2009-2023 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.db.report; + +import java.util.List; +import java.util.Optional; +import java.util.Set; +import org.apache.ibatis.annotations.Param; + +public interface ReportSubscriptionMapper { + Optional selectByUserAndPortfolio(@Param("portfolioUuid") String portfolioUuid, @Param("userUuid") String userUuid); + + Optional selectByUserAndBranch(@Param("branchUuid") String branchUuid, @Param("userUuid") String userUuid); + + List selectByPortfolio(String portfolioUuid); + + List selectByBranch(String projectBranchUuid); + + Set selectAll(); + + void insert(ReportSubscriptionDto subscriptionDto); + + void delete(ReportSubscriptionDto subscriptionDto); +} diff --git a/server/sonar-db-dao/src/main/resources/org/sonar/db/purge/PurgeMapper.xml b/server/sonar-db-dao/src/main/resources/org/sonar/db/purge/PurgeMapper.xml index 1eb0d1f20a4..d21dd844696 100644 --- a/server/sonar-db-dao/src/main/resources/org/sonar/db/purge/PurgeMapper.xml +++ b/server/sonar-db-dao/src/main/resources/org/sonar/db/purge/PurgeMapper.xml @@ -605,5 +605,23 @@ delete from scanner_analysis_cache where branch_uuid = #{branchUuid,jdbcType=VARCHAR} + + + delete from report_schedules where branch_uuid = #{branchUuid,jdbcType=VARCHAR} + + + + delete from report_subscriptions where branch_uuid = #{branchUuid,jdbcType=VARCHAR} + + + + delete from report_schedules where portfolio_uuid in #{portfolioUuid, jdbcType=VARCHAR} + + + + delete from report_subscriptions where portfolio_uuid in #{portfolioUuid, jdbcType=VARCHAR} + diff --git a/server/sonar-db-dao/src/main/resources/org/sonar/db/report/ReportScheduleMapper.xml b/server/sonar-db-dao/src/main/resources/org/sonar/db/report/ReportScheduleMapper.xml new file mode 100644 index 00000000000..c6289d05a34 --- /dev/null +++ b/server/sonar-db-dao/src/main/resources/org/sonar/db/report/ReportScheduleMapper.xml @@ -0,0 +1,61 @@ + + + + + + + + insert into report_schedules ( + uuid, + portfolio_uuid, + branch_uuid, + last_send_time_in_ms + ) + values ( + #{uuid,jdbcType=VARCHAR}, + #{portfolioUuid,jdbcType=VARCHAR}, + #{branchUuid,jdbcType=VARCHAR}, + #{lastSendTimeInMs,jdbcType=BIGINT} + ) + + + + update report_schedules + set last_send_time_in_ms=#{lastSendTimeInMs,jdbcType=BIGINT} + where uuid=#{uuid,jdbcType=VARCHAR} + + + + + + + + + + + portfolio_uuid = #{portfolioUuid,jdbcType=VARCHAR} + portfolio_uuid IS NULL + + and + + branch_uuid = #{branchUuid,jdbcType=VARCHAR} + branch_uuid IS NULL + + + + + uuid as uuid, + portfolio_uuid as portfolioUuid, + branch_uuid as branchUuid, + last_send_time_in_ms as lastSendTimeInMs + + + diff --git a/server/sonar-db-dao/src/main/resources/org/sonar/db/report/ReportSubscriptionMapper.xml b/server/sonar-db-dao/src/main/resources/org/sonar/db/report/ReportSubscriptionMapper.xml new file mode 100644 index 00000000000..ff19bcb53e7 --- /dev/null +++ b/server/sonar-db-dao/src/main/resources/org/sonar/db/report/ReportSubscriptionMapper.xml @@ -0,0 +1,83 @@ + + + + + + + + + insert into report_subscriptions ( + uuid, + portfolio_uuid, + branch_uuid, + user_uuid + ) + values ( + #{uuid,jdbcType=VARCHAR}, + #{portfolioUuid,jdbcType=VARCHAR}, + #{branchUuid,jdbcType=VARCHAR}, + #{userUuid,jdbcType=VARCHAR} + ) + + + + + + + + + + + + + + delete from report_subscriptions + where + + + + user_uuid=#{userUuid,jdbcType=VARCHAR} and + + portfolio_uuid = #{portfolioUuid,jdbcType=VARCHAR} + portfolio_uuid IS NULL + + and + + branch_uuid = #{branchUuid,jdbcType=VARCHAR} + branch_uuid IS NULL + + + + + uuid as uuid, + user_uuid as userUuid, + portfolio_uuid as portfolioUuid, + branch_uuid as branchUuid + + + diff --git a/server/sonar-db-dao/src/schema/schema-sq.ddl b/server/sonar-db-dao/src/schema/schema-sq.ddl index b2bd8e58ffe..9c0dd073f9f 100644 --- a/server/sonar-db-dao/src/schema/schema-sq.ddl +++ b/server/sonar-db-dao/src/schema/schema-sq.ddl @@ -826,6 +826,24 @@ CREATE TABLE "QUALITY_GATES"( ); ALTER TABLE "QUALITY_GATES" ADD CONSTRAINT "PK_QUALITY_GATES" PRIMARY KEY("UUID"); +CREATE TABLE "REPORT_SCHEDULES"( + "UUID" CHARACTER VARYING(40) NOT NULL, + "PORTFOLIO_UUID" CHARACTER VARYING(40), + "BRANCH_UUID" CHARACTER VARYING(40), + "LAST_SEND_TIME_IN_MS" BIGINT NOT NULL +); +ALTER TABLE "REPORT_SCHEDULES" ADD CONSTRAINT "PK_REPORT_SCHEDULES" PRIMARY KEY("UUID"); +CREATE UNIQUE INDEX "UNIQ_REPORT_SCHEDULES" ON "REPORT_SCHEDULES"("PORTFOLIO_UUID" NULLS FIRST, "BRANCH_UUID" NULLS FIRST); + +CREATE TABLE "REPORT_SUBSCRIPTIONS"( + "UUID" CHARACTER VARYING(40) NOT NULL, + "PORTFOLIO_UUID" CHARACTER VARYING(40), + "BRANCH_UUID" CHARACTER VARYING(40), + "USER_UUID" CHARACTER VARYING(40) NOT NULL +); +ALTER TABLE "REPORT_SUBSCRIPTIONS" ADD CONSTRAINT "PK_REPORT_SUBSCRIPTIONS" PRIMARY KEY("UUID"); +CREATE UNIQUE INDEX "UNIQ_REPORT_SUBSCRIPTIONS" ON "REPORT_SUBSCRIPTIONS"("PORTFOLIO_UUID" NULLS FIRST, "BRANCH_UUID" NULLS FIRST, "USER_UUID" NULLS FIRST); + CREATE TABLE "RULE_DESC_SECTIONS"( "UUID" CHARACTER VARYING(40) NOT NULL, "RULE_UUID" CHARACTER VARYING(40) NOT NULL, diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/AddReportSchedulesTable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/AddReportSchedulesTable.java new file mode 100644 index 00000000000..a4f9e058feb --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/AddReportSchedulesTable.java @@ -0,0 +1,48 @@ +/* + * SonarQube + * Copyright (C) 2009-2023 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.v101; + +import java.sql.SQLException; +import org.sonar.db.Database; +import org.sonar.server.platform.db.migration.sql.CreateTableBuilder; +import org.sonar.server.platform.db.migration.step.CreateTableChange; + +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 AddReportSchedulesTable extends CreateTableChange { + + static final String TABLE_NAME = "report_schedules"; + + public AddReportSchedulesTable(Database db) { + super(db, TABLE_NAME); + } + + @Override + public void execute(Context context, String tableName) throws SQLException { + context.execute(new CreateTableBuilder(getDialect(), tableName) + .addPkColumn(newVarcharColumnDefBuilder().setColumnName("uuid").setIsNullable(false).setLimit(UUID_SIZE).build()) + .addColumn(newVarcharColumnDefBuilder().setColumnName("portfolio_uuid").setIsNullable(true).setLimit(UUID_SIZE).build()) + .addColumn(newVarcharColumnDefBuilder().setColumnName("branch_uuid").setIsNullable(true).setLimit(UUID_SIZE).build()) + .addColumn(newBigIntegerColumnDefBuilder().setColumnName("last_send_time_in_ms").setIsNullable(false).build()) + .build()); + } +} diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/AddReportSubscriptionsTable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/AddReportSubscriptionsTable.java new file mode 100644 index 00000000000..72095428a23 --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/AddReportSubscriptionsTable.java @@ -0,0 +1,47 @@ +/* + * SonarQube + * Copyright (C) 2009-2023 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.v101; + +import java.sql.SQLException; +import org.sonar.db.Database; +import org.sonar.server.platform.db.migration.sql.CreateTableBuilder; +import org.sonar.server.platform.db.migration.step.CreateTableChange; + +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 AddReportSubscriptionsTable extends CreateTableChange { + + static final String TABLE_NAME = "report_subscriptions"; + + public AddReportSubscriptionsTable(Database db) { + super(db, TABLE_NAME); + } + + @Override + public void execute(Context context, String tableName) throws SQLException { + context.execute(new CreateTableBuilder(getDialect(), tableName) + .addPkColumn(newVarcharColumnDefBuilder().setColumnName("uuid").setIsNullable(false).setLimit(UUID_SIZE).build()) + .addColumn(newVarcharColumnDefBuilder().setColumnName("portfolio_uuid").setIsNullable(true).setLimit(UUID_SIZE).build()) + .addColumn(newVarcharColumnDefBuilder().setColumnName("branch_uuid").setIsNullable(true).setLimit(UUID_SIZE).build()) + .addColumn(newVarcharColumnDefBuilder().setColumnName("user_uuid").setIsNullable(false).setLimit(UUID_SIZE).build()) + .build()); + } +} diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/CreateUniqueIndexForReportSchedulesTable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/CreateUniqueIndexForReportSchedulesTable.java new file mode 100644 index 00000000000..b09a82f9140 --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/CreateUniqueIndexForReportSchedulesTable.java @@ -0,0 +1,66 @@ +/* + * SonarQube + * Copyright (C) 2009-2023 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.v101; + +import com.google.common.annotations.VisibleForTesting; +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; + +import static org.sonar.server.platform.db.migration.version.v101.AddReportSchedulesTable.TABLE_NAME; + +public class CreateUniqueIndexForReportSchedulesTable extends DdlChange { + + @VisibleForTesting + static final String COLUMN_NAME_PORTFOLIO = "portfolio_uuid"; + @VisibleForTesting + static final String COLUMN_NAME_BRANCH = "branch_uuid"; + + @VisibleForTesting + static final String INDEX_NAME = "uniq_report_schedules"; + + + public CreateUniqueIndexForReportSchedulesTable(Database db) { + super(db); + } + + @Override + public void execute(Context context) throws SQLException { + + try (Connection connection = getDatabase().getDataSource().getConnection()) { + createUserUuidUniqueIndex(context, connection); + } + } + + private static void createUserUuidUniqueIndex(Context context, Connection connection) { + if (!DatabaseUtils.indexExistsIgnoreCase(TABLE_NAME, INDEX_NAME, connection)) { + context.execute(new CreateIndexBuilder() + .setTable(TABLE_NAME) + .setName(INDEX_NAME) + .addColumn(COLUMN_NAME_PORTFOLIO) + .addColumn(COLUMN_NAME_BRANCH) + .setUnique(true) + .build()); + } + } +} diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/CreateUniqueIndexForReportSubscriptionsTable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/CreateUniqueIndexForReportSubscriptionsTable.java new file mode 100644 index 00000000000..54b3b869853 --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/CreateUniqueIndexForReportSubscriptionsTable.java @@ -0,0 +1,70 @@ +/* + * SonarQube + * Copyright (C) 2009-2023 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.v101; + +import com.google.common.annotations.VisibleForTesting; +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; + +import static org.sonar.server.platform.db.migration.version.v101.AddReportSubscriptionsTable.TABLE_NAME; + + +public class CreateUniqueIndexForReportSubscriptionsTable extends DdlChange{ + + @VisibleForTesting + static final String COLUMN_NAME_PORTFOLIO = "portfolio_uuid"; + @VisibleForTesting + static final String COLUMN_NAME_BRANCH = "branch_uuid"; + @VisibleForTesting + static final String COLUMN_NAME_USER = "user_uuid"; + + @VisibleForTesting + static final String INDEX_NAME = "uniq_report_subscriptions"; + + + public CreateUniqueIndexForReportSubscriptionsTable(Database db) { + super(db); + } + + @Override + public void execute(DdlChange.Context context) throws SQLException { + + try (Connection connection = getDatabase().getDataSource().getConnection()) { + createUserUuidUniqueIndex(context, connection); + } + } + + private static void createUserUuidUniqueIndex(DdlChange.Context context, Connection connection) { + if (!DatabaseUtils.indexExistsIgnoreCase(TABLE_NAME, INDEX_NAME, connection)) { + context.execute(new CreateIndexBuilder() + .setTable(TABLE_NAME) + .setName(INDEX_NAME) + .addColumn(COLUMN_NAME_PORTFOLIO) + .addColumn(COLUMN_NAME_BRANCH) + .addColumn(COLUMN_NAME_USER) + .setUnique(true) + .build()); + } + } +} diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/DbVersion101.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/DbVersion101.java index 7f381029eaf..75738fdd44f 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/DbVersion101.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/DbVersion101.java @@ -59,6 +59,14 @@ public class DbVersion101 implements DbVersion { .add(10_1_015, "Add 'external_groups' table.", CreateExternalGroupsTable.class) .add(10_1_016, "Add index on 'external_groups(external_identity_provider, external_id).", CreateIndexOnExternalIdAndIdentityOnExternalGroupsTable.class) .add(10_1_017, "Add 'code_variants' column in 'issues' table", AddCodeVariantsColumnInIssuesTable.class) - .add(10_1_018, "Fix different uuids for subportfolios", FixDifferentUuidsForSubportfolios.class); + .add(10_1_018, "Fix different uuids for subportfolios", FixDifferentUuidsForSubportfolios.class) + .add(10_1_019, "Add report_schedules table", AddReportSchedulesTable.class) + .add(10_1_020, "Add report_subscriptions table", AddReportSubscriptionsTable.class) + .add(10_1_021, "Add report_schedules unique index", CreateUniqueIndexForReportSchedulesTable.class) + .add(10_1_022, "Add report_subscriptions unique index", CreateUniqueIndexForReportSubscriptionsTable.class) + .add(10_1_023, "Populate report_schedules table", PopulateReportSchedules.class) + .add(10_1_024, "Populate report_subscriptions table", PopulateReportSubscriptions.class) + .add(10_1_025, "Remove report properties", RemoveReportProperties.class) + ; } } diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/PopulateReportSchedules.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/PopulateReportSchedules.java new file mode 100644 index 00000000000..d0510efcb2e --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/PopulateReportSchedules.java @@ -0,0 +1,70 @@ +/* + * SonarQube + * Copyright (C) 2009-2023 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.v101; + +import java.sql.SQLException; +import org.sonar.core.util.UuidFactory; +import org.sonar.core.util.UuidFactoryImpl; +import org.sonar.db.Database; +import org.sonar.server.platform.db.migration.step.DataChange; +import org.sonar.server.platform.db.migration.step.MassUpdate; + +public class PopulateReportSchedules extends DataChange { + private static final String SELECT_QUERY = """ + SELECT port.uuid as portfolioUuid, pb.uuid as branchUuid, p.text_value as value + FROM properties p + LEFT JOIN portfolios port ON p.component_uuid = port.uuid + LEFT JOIN project_branches pb ON p.component_uuid = pb.uuid + WHERE p.prop_key = 'sonar.governance.report.lastSendTimeInMs' or p.prop_key = 'sonar.governance.report.project.branch.lastSendTimeInMs' + AND NOT EXISTS ( + SELECT * FROM report_schedules rs + WHERE rs.branch_uuid = p.component_uuid or rs.portfolio_uuid = p.component_uuid + ) + """; + + public PopulateReportSchedules(Database db) { + super(db); + } + + @Override + protected void execute(Context context) throws SQLException { + MassUpdate massUpdate = context.prepareMassUpdate(); + massUpdate.select(SELECT_QUERY); + massUpdate.update("insert into report_schedules (uuid, branch_uuid, portfolio_uuid, last_send_time_in_ms) values (?, ?, ?, ?)"); + massUpdate.execute((row, update) -> { + String portfolioUuid = row.getString(1); + String branchUuid = row.getString(2); + + // one and only one needs to be null + if ((portfolioUuid == null) == (branchUuid == null)) { + return false; + } + + String value = row.getString(3); + long ms = Long.parseLong(value); + + update.setString(1, UuidFactoryImpl.INSTANCE.create()); + update.setString(2, branchUuid); + update.setString(3, portfolioUuid); + update.setLong(4, ms); + return true; + }); + } +} diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/PopulateReportSubscriptions.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/PopulateReportSubscriptions.java new file mode 100644 index 00000000000..6576cdd2d46 --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/PopulateReportSubscriptions.java @@ -0,0 +1,68 @@ +/* + * SonarQube + * Copyright (C) 2009-2023 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.v101; + +import java.sql.SQLException; +import org.sonar.core.util.UuidFactoryImpl; +import org.sonar.db.Database; +import org.sonar.server.platform.db.migration.step.DataChange; +import org.sonar.server.platform.db.migration.step.MassUpdate; + +public class PopulateReportSubscriptions extends DataChange { + // TODO should we add a inner join on users to not transfer values of non-existent users? + private static final String SELECT_QUERY = """ + SELECT port.uuid as portfolioUuid, pb.uuid as branchUuid, p.user_uuid as userUuid + FROM properties p + LEFT JOIN portfolios port ON p.component_uuid = port.uuid + LEFT JOIN project_branches pb ON p.component_uuid = pb.uuid + WHERE p.prop_key = 'sonar.governance.report.userNotification' or p.prop_key = 'sonar.governance.report.project.branch.userNotification' + AND NOT EXISTS ( + SELECT * FROM report_subscriptions rs + WHERE (rs.branch_uuid = p.component_uuid or rs.portfolio_uuid = p.component_uuid) and rs.user_uuid = p.user_uuid + ) + """; + + public PopulateReportSubscriptions(Database db) { + super(db); + } + + @Override + protected void execute(Context context) throws SQLException { + MassUpdate massUpdate = context.prepareMassUpdate(); + massUpdate.select(SELECT_QUERY); + massUpdate.update("insert into report_subscriptions (uuid, branch_uuid, portfolio_uuid, user_uuid) values (?, ?, ?, ?)"); + massUpdate.execute((row, update) -> { + String portfolioUuid = row.getString(1); + String branchUuid = row.getString(2); + + // one and only one needs to be null + if ((portfolioUuid == null) == (branchUuid == null)) { + return false; + } + + String userUuid = row.getString(3); + update.setString(1, UuidFactoryImpl.INSTANCE.create()); + update.setString(2, branchUuid); + update.setString(3, portfolioUuid); + update.setString(4, userUuid); + return true; + }); + } +} diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/RemoveReportProperties.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/RemoveReportProperties.java new file mode 100644 index 00000000000..8aa10fddb02 --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/RemoveReportProperties.java @@ -0,0 +1,52 @@ +/* + * SonarQube + * Copyright (C) 2009-2023 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.v101; + +import java.sql.Connection; +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; + +public class RemoveReportProperties extends DataChange { + private static final String SELECT_QUERY = """ + SELECT p.uuid as uuid from properties p + WHERE p.prop_key = 'sonar.governance.report.lastSendTimeInMs' or p.prop_key = 'sonar.governance.report.project.branch.lastSendTimeInMs' + or p.prop_key = 'sonar.governance.report.userNotification' or p.prop_key = 'sonar.governance.report.project.branch.userNotification' + """; + + public RemoveReportProperties(Database db) { + super(db); + } + + @Override + protected void execute(Context context) throws SQLException { + try (Connection connection = getDatabase().getDataSource().getConnection()) { + MassUpdate massUpdate = context.prepareMassUpdate(); + massUpdate.select(SELECT_QUERY); + massUpdate.update("delete from properties where uuid = ?"); + massUpdate.execute((row, delete) -> { + String uuid = row.getString(1); + delete.setString(1, uuid); + return true; + }); + } + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v101/AddReportSchedulesTableTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v101/AddReportSchedulesTableTest.java new file mode 100644 index 00000000000..f30313bc7d9 --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v101/AddReportSchedulesTableTest.java @@ -0,0 +1,65 @@ +/* + * SonarQube + * Copyright (C) 2009-2023 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.v101; + +import java.sql.SQLException; +import java.sql.Types; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.db.CoreDbTester; +import org.sonar.server.platform.db.migration.step.DdlChange; + +import static org.sonar.server.platform.db.migration.def.VarcharColumnDef.UUID_SIZE; +import static org.sonar.server.platform.db.migration.version.v101.AddReportSchedulesTable.TABLE_NAME; + +public class AddReportSchedulesTableTest { + @Rule + public final CoreDbTester db = CoreDbTester.createEmpty(); + + private final DdlChange underTest = new AddReportSchedulesTable(db.database()); + + @Test + public void migration_should_create_a_table() throws SQLException { + db.assertTableDoesNotExist(TABLE_NAME); + + underTest.execute(); + + db.assertTableExists(TABLE_NAME); + db.assertColumnDefinition(TABLE_NAME, "uuid", Types.VARCHAR, UUID_SIZE, false); + db.assertColumnDefinition(TABLE_NAME, "portfolio_uuid", Types.VARCHAR, UUID_SIZE, true); + db.assertColumnDefinition(TABLE_NAME, "branch_uuid", Types.VARCHAR, UUID_SIZE, true); + db.assertColumnDefinition(TABLE_NAME, "last_send_time_in_ms", Types.BIGINT, null, false); + db.assertPrimaryKey(TABLE_NAME, "pk_report_schedules", "uuid"); + + } + + @Test + public void migration_should_be_reentrant() throws SQLException { + db.assertTableDoesNotExist(TABLE_NAME); + + underTest.execute(); + // re-entrant + underTest.execute(); + + db.assertTableExists(TABLE_NAME); + } + + +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v101/AddReportSubscriptionsTableTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v101/AddReportSubscriptionsTableTest.java new file mode 100644 index 00000000000..a46820299fb --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v101/AddReportSubscriptionsTableTest.java @@ -0,0 +1,63 @@ +/* + * SonarQube + * Copyright (C) 2009-2023 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.v101; + +import java.sql.SQLException; +import java.sql.Types; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.db.CoreDbTester; +import org.sonar.server.platform.db.migration.step.DdlChange; + +import static org.sonar.server.platform.db.migration.def.VarcharColumnDef.UUID_SIZE; +import static org.sonar.server.platform.db.migration.version.v101.AddReportSubscriptionsTable.TABLE_NAME; + +public class AddReportSubscriptionsTableTest { + @Rule + public final CoreDbTester db = CoreDbTester.createEmpty(); + + private final DdlChange underTest = new AddReportSubscriptionsTable(db.database()); + + @Test + public void migration_should_create_a_table() throws SQLException { + db.assertTableDoesNotExist(TABLE_NAME); + + underTest.execute(); + + db.assertTableExists(TABLE_NAME); + db.assertColumnDefinition(TABLE_NAME, "uuid", Types.VARCHAR, UUID_SIZE, false); + db.assertColumnDefinition(TABLE_NAME, "portfolio_uuid", Types.VARCHAR, UUID_SIZE, true); + db.assertColumnDefinition(TABLE_NAME, "branch_uuid", Types.VARCHAR, UUID_SIZE, true); + db.assertColumnDefinition(TABLE_NAME, "user_uuid", Types.VARCHAR, UUID_SIZE, false); + db.assertPrimaryKey(TABLE_NAME, "pk_report_subscriptions", "uuid"); + + } + + @Test + public void migration_should_be_reentrant() throws SQLException { + db.assertTableDoesNotExist(TABLE_NAME); + + underTest.execute(); + // re-entrant + underTest.execute(); + + db.assertTableExists(TABLE_NAME); + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v101/CreateUniqueIndexForReportSchedulesTableTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v101/CreateUniqueIndexForReportSchedulesTableTest.java new file mode 100644 index 00000000000..824e7a5eaf3 --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v101/CreateUniqueIndexForReportSchedulesTableTest.java @@ -0,0 +1,55 @@ +/* + * SonarQube + * Copyright (C) 2009-2023 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.v101; + +import java.sql.SQLException; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.db.CoreDbTester; + +import static org.sonar.server.platform.db.migration.version.v101.AddReportSchedulesTable.TABLE_NAME; +import static org.sonar.server.platform.db.migration.version.v101.CreateUniqueIndexForReportSchedulesTable.COLUMN_NAME_BRANCH; +import static org.sonar.server.platform.db.migration.version.v101.CreateUniqueIndexForReportSchedulesTable.COLUMN_NAME_PORTFOLIO; +import static org.sonar.server.platform.db.migration.version.v101.CreateUniqueIndexForReportSchedulesTable.INDEX_NAME; + + +public class CreateUniqueIndexForReportSchedulesTableTest { + @Rule + public final CoreDbTester db = CoreDbTester.createForSchema(CreateUniqueIndexForReportSchedulesTableTest.class, "schema.sql"); + + private final CreateUniqueIndexForReportSchedulesTable createUniqueIndexForReportSchedulesTable = new CreateUniqueIndexForReportSchedulesTable(db.database()); + + @Test + public void migration_should_create_index() throws SQLException { + db.assertIndexDoesNotExist(TABLE_NAME, INDEX_NAME); + + createUniqueIndexForReportSchedulesTable.execute(); + + db.assertUniqueIndex(TABLE_NAME, INDEX_NAME, COLUMN_NAME_PORTFOLIO, COLUMN_NAME_BRANCH); + } + + @Test + public void migration_should_be_reentrant() throws SQLException { + createUniqueIndexForReportSchedulesTable.execute(); + createUniqueIndexForReportSchedulesTable.execute(); + + db.assertUniqueIndex(TABLE_NAME, INDEX_NAME, COLUMN_NAME_PORTFOLIO, COLUMN_NAME_BRANCH); + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v101/CreateUniqueIndexForReportSubscriptionsTableTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v101/CreateUniqueIndexForReportSubscriptionsTableTest.java new file mode 100644 index 00000000000..63fe45fac0a --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v101/CreateUniqueIndexForReportSubscriptionsTableTest.java @@ -0,0 +1,56 @@ +/* + * SonarQube + * Copyright (C) 2009-2023 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.v101; + +import java.sql.SQLException; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.db.CoreDbTester; + +import static org.sonar.server.platform.db.migration.version.v101.AddReportSubscriptionsTable.TABLE_NAME; +import static org.sonar.server.platform.db.migration.version.v101.CreateUniqueIndexForReportSubscriptionsTable.COLUMN_NAME_BRANCH; +import static org.sonar.server.platform.db.migration.version.v101.CreateUniqueIndexForReportSubscriptionsTable.COLUMN_NAME_PORTFOLIO; +import static org.sonar.server.platform.db.migration.version.v101.CreateUniqueIndexForReportSubscriptionsTable.COLUMN_NAME_USER; +import static org.sonar.server.platform.db.migration.version.v101.CreateUniqueIndexForReportSubscriptionsTable.INDEX_NAME; + + +public class CreateUniqueIndexForReportSubscriptionsTableTest { + @Rule + public final CoreDbTester db = CoreDbTester.createForSchema(CreateUniqueIndexForReportSubscriptionsTableTest.class, "schema.sql"); + + private final CreateUniqueIndexForReportSubscriptionsTable createUniqueIndexForReportSubscriptionsTable = new CreateUniqueIndexForReportSubscriptionsTable(db.database()); + + @Test + public void migration_should_create_index() throws SQLException { + db.assertIndexDoesNotExist(TABLE_NAME, INDEX_NAME); + + createUniqueIndexForReportSubscriptionsTable.execute(); + + db.assertUniqueIndex(TABLE_NAME, INDEX_NAME, COLUMN_NAME_PORTFOLIO, COLUMN_NAME_BRANCH, COLUMN_NAME_USER); + } + + @Test + public void migration_should_be_reentrant() throws SQLException { + createUniqueIndexForReportSubscriptionsTable.execute(); + createUniqueIndexForReportSubscriptionsTable.execute(); + + db.assertUniqueIndex(TABLE_NAME, INDEX_NAME, COLUMN_NAME_PORTFOLIO, COLUMN_NAME_BRANCH, COLUMN_NAME_USER); + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v101/PopulateReportSchedulesTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v101/PopulateReportSchedulesTest.java new file mode 100644 index 00000000000..e39ca9944b1 --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v101/PopulateReportSchedulesTest.java @@ -0,0 +1,123 @@ +/* + * SonarQube + * Copyright (C) 2009-2023 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.v101; + +import java.sql.SQLException; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.core.util.UuidFactory; +import org.sonar.core.util.UuidFactoryFast; +import org.sonar.db.CoreDbTester; +import org.sonar.server.platform.db.migration.step.DataChange; + +import static org.assertj.core.api.Assertions.assertThat; +import static org.assertj.core.api.Assertions.tuple; + +public class PopulateReportSchedulesTest { + @Rule + public CoreDbTester db = CoreDbTester.createForSchema(PopulateReportSchedulesTest.class, "schema.sql"); + + private final UuidFactory uuidFactory = UuidFactoryFast.getInstance(); + private final DataChange underTest = new PopulateReportSchedules(db.database()); + + @Test + public void execute_shouldPopulateFromPortfolioProperties() throws SQLException { + insertPortfolio("uuid1"); + insertPortfolioProperty("uuid1", "1234"); + + underTest.execute(); + + assertThat(db.select("select * from report_schedules")) + .extracting(m -> m.get("PORTFOLIO_UUID"), m -> m.get("BRANCH_UUID"), m -> m.get("LAST_SEND_TIME_IN_MS")) + .containsOnly(tuple("uuid1", null, 1234L)); + } + + @Test + public void execute_shouldPopulateFromBranchProperties() throws SQLException { + insertBranch("uuid1"); + insertProjectBranchProperty("uuid1", "1234"); + + underTest.execute(); + + assertThat(db.select("select * from report_schedules")) + .extracting(m -> m.get("PORTFOLIO_UUID"), m -> m.get("BRANCH_UUID"), m -> m.get("LAST_SEND_TIME_IN_MS")) + .containsOnly(tuple(null, "uuid1", 1234L)); + } + + @Test + public void execute_whenPropertyMatchesBothBranchAndPortfolio_shouldNotPopulate() throws SQLException { + insertBranch("uuid1"); + insertPortfolio("uuid1"); + insertProjectBranchProperty("uuid1", "1234"); + + underTest.execute(); + underTest.execute(); + + assertThat(db.select("select * from report_schedules")).isEmpty(); + } + + private void insertPortfolio(String uuid) { + db.executeInsert("portfolios", + "uuid", uuid, + "kee", "kee_" + uuid, + "name", "name_" + uuid, + "root_uuid", uuid, + "private", true, + "selection_mode", "manual", + "created_at", 1000, + "updated_at", 1000 + ); + } + + private void insertProjectBranchProperty(String portfolioUuid, String value) { + db.executeInsert("properties", + "uuid", uuidFactory.create(), + "prop_key", "sonar.governance.report.project.branch.lastSendTimeInMs", + "is_empty", false, + "text_value", value, + "created_at", 1000, + "component_uuid", portfolioUuid + ); + } + + private void insertPortfolioProperty(String portfolioUuid, String value) { + db.executeInsert("properties", + "uuid", uuidFactory.create(), + "prop_key", "sonar.governance.report.lastSendTimeInMs", + "is_empty", false, + "text_value", value, + "created_at", 1000, + "component_uuid", portfolioUuid + ); + } + + private void insertBranch(String uuid) { + db.executeInsert("project_branches", + "uuid", uuid, + "project_uuid", "project_" + uuid, + "kee", "kee_" + uuid, + "branch_type", "BRANCH", + "created_at", "1000", + "updated_at", "1000", + "need_issue_sync", false, + "is_main", true + ); + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v101/PopulateReportSubscriptionsTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v101/PopulateReportSubscriptionsTest.java new file mode 100644 index 00000000000..6eb803b4d91 --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v101/PopulateReportSubscriptionsTest.java @@ -0,0 +1,121 @@ +/* + * SonarQube + * Copyright (C) 2009-2023 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.v101; + +import java.sql.SQLException; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.core.util.UuidFactory; +import org.sonar.core.util.UuidFactoryFast; +import org.sonar.db.CoreDbTester; +import org.sonar.server.platform.db.migration.step.DataChange; + +import static org.assertj.core.api.Assertions.assertThat; +import static org.assertj.core.api.Assertions.tuple; + +public class PopulateReportSubscriptionsTest { + @Rule + public CoreDbTester db = CoreDbTester.createForSchema(PopulateReportSubscriptionsTest.class, "schema.sql"); + + private final UuidFactory uuidFactory = UuidFactoryFast.getInstance(); + private final DataChange underTest = new PopulateReportSubscriptions(db.database()); + + @Test + public void execute_shouldPopulateFromPortfolioProperties() throws SQLException { + insertPortfolio("uuid1"); + insertPortfolioProperty("uuid1", "1234"); + + underTest.execute(); + + assertThat(db.select("select * from report_subscriptions")) + .extracting(m -> m.get("PORTFOLIO_UUID"), m -> m.get("BRANCH_UUID"), m -> m.get("USER_UUID")) + .containsOnly(tuple("uuid1", null, "1234")); + } + + @Test + public void execute_shouldPopulateFromBranchProperties() throws SQLException { + insertBranch("uuid1"); + insertBranchProperty("uuid1", "1234"); + + underTest.execute(); + + assertThat(db.select("select * from report_subscriptions")) + .extracting(m -> m.get("PORTFOLIO_UUID"), m -> m.get("BRANCH_UUID"), m -> m.get("USER_UUID")) + .containsOnly(tuple(null, "uuid1", "1234")); + } + + @Test + public void execute_whenPropertyMatchesBothBranchAndPortfolio_shouldNotPopulate() throws SQLException { + insertBranch("uuid1"); + insertPortfolio("uuid1"); + insertBranchProperty("uuid1", "1234"); + + underTest.execute(); + underTest.execute(); + + assertThat(db.select("select * from report_subscriptions")).isEmpty(); + } + + private void insertPortfolio(String uuid) { + db.executeInsert("portfolios", + "uuid", uuid, + "kee", "kee_" + uuid, + "name", "name_" + uuid, + "root_uuid", uuid, + "private", true, + "selection_mode", "manual", + "created_at", 1000, + "updated_at", 1000 + ); + } + + private void insertBranchProperty(String branchUuid, String userUuid){ + insertProperty( branchUuid, userUuid, "sonar.governance.report.userNotification"); + } + + private void insertPortfolioProperty(String branchUuid, String userUuid){ + insertProperty( branchUuid, userUuid, "sonar.governance.report.project.branch.userNotification"); + } + + private void insertProperty(String componentUuid, String userUuid, String propertyKey) { + db.executeInsert("properties", + "uuid", uuidFactory.create(), + "prop_key", propertyKey, + "is_empty", false, + "text_value", "true", + "created_at", 1000, + "component_uuid", componentUuid, + "user_uuid", userUuid + ); + } + + private void insertBranch(String uuid) { + db.executeInsert("project_branches", + "uuid", uuid, + "project_uuid", "project_" + uuid, + "kee", "kee_" + uuid, + "branch_type", "BRANCH", + "created_at", "1000", + "updated_at", "1000", + "need_issue_sync", false, + "is_main", true + ); + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v101/RemoveReportPropertiesTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v101/RemoveReportPropertiesTest.java new file mode 100644 index 00000000000..7fde5d33706 --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v101/RemoveReportPropertiesTest.java @@ -0,0 +1,81 @@ +/* + * SonarQube + * Copyright (C) 2009-2023 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.v101; + +import java.sql.SQLException; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.core.util.UuidFactory; +import org.sonar.core.util.UuidFactoryFast; +import org.sonar.db.CoreDbTester; +import org.sonar.server.platform.db.migration.step.DataChange; + +import static org.assertj.core.api.Assertions.assertThat; + +public class RemoveReportPropertiesTest { + + private static final String SONAR_GOVERNANCE_REPORT_USER_NOTIFICATION = "sonar.governance.report.userNotification"; + private static final String SONAR_GOVERNANCE_REPORT_PROJECT_BRANCH_USER_NOTIFICATION = "sonar.governance.report.project.branch.userNotification"; + private static final String SONAR_GOVERNANCE_REPORT_LAST_SEND_TIME_IN_MS = "sonar.governance.report.lastSendTimeInMs"; + private static final String SONAR_GOVERNANCE_REPORT_PROJECT_BRANCH_LAST_SEND_TIME_IN_MS = "sonar.governance.report.project.branch.lastSendTimeInMs"; + @Rule + public CoreDbTester db = CoreDbTester.createForSchema(RemoveReportPropertiesTest.class, "schema.sql"); + + private final DataChange underTest = new RemoveReportProperties(db.database()); + + private final UuidFactory uuidFactory = UuidFactoryFast.getInstance(); + + @Test + public void execute_shouldRemoveRelevantPropertiesFromTable() throws SQLException { + insertProperty( "branch_uuid", "user_uuid", SONAR_GOVERNANCE_REPORT_USER_NOTIFICATION, "true"); + insertProperty( "portfolio_uuid", "user_uuid", SONAR_GOVERNANCE_REPORT_PROJECT_BRANCH_USER_NOTIFICATION, "true"); + insertProperty( "branch_uuid", "user_uuid", SONAR_GOVERNANCE_REPORT_LAST_SEND_TIME_IN_MS, "12"); + insertProperty( "portfolio_uuid", "user_uuid", SONAR_GOVERNANCE_REPORT_PROJECT_BRANCH_LAST_SEND_TIME_IN_MS, "123"); + insertProperty( "portfolio_uuid", "user_uuid", "sonar.other.property", "123"); + + underTest.execute(); + + assertThat(db.select("select * from properties")).hasSize(1) + .extracting(r->r.get("PROP_KEY")).containsExactly("sonar.other.property"); + } + + @Test + public void execute_shouldBeIdempotent() throws SQLException { + insertProperty( "branch_uuid", "user_uuid", SONAR_GOVERNANCE_REPORT_USER_NOTIFICATION, "true"); + insertProperty( "portfolio_uuid", "user_uuid", SONAR_GOVERNANCE_REPORT_PROJECT_BRANCH_USER_NOTIFICATION, "true"); + + underTest.execute(); + underTest.execute(); + + assertThat(db.select("select * from properties")).isEmpty(); + } + + private void insertProperty(String componentUuid, String userUuid, String propertyKey, String value) { + db.executeInsert("properties", + "uuid", uuidFactory.create(), + "prop_key", propertyKey, + "is_empty", false, + "text_value", value, + "created_at", 1000, + "component_uuid", componentUuid, + "user_uuid", userUuid + ); + } +} diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v101/CreateUniqueIndexForReportSchedulesTableTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v101/CreateUniqueIndexForReportSchedulesTableTest/schema.sql new file mode 100644 index 00000000000..4f95a63924f --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v101/CreateUniqueIndexForReportSchedulesTableTest/schema.sql @@ -0,0 +1,7 @@ +CREATE TABLE "REPORT_SCHEDULES" +( + "UUID" CHARACTER VARYING(40) NOT NULL, + "PORTFOLIO_UUID" CHARACTER VARYING(40), + "BRANCH_UUID" CHARACTER VARYING(40), + "LAST_SEND_TIME_IN_MS" BIGINT NOT NULL +); diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v101/CreateUniqueIndexForReportSubscriptionsTableTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v101/CreateUniqueIndexForReportSubscriptionsTableTest/schema.sql new file mode 100644 index 00000000000..0eca93708d2 --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v101/CreateUniqueIndexForReportSubscriptionsTableTest/schema.sql @@ -0,0 +1,7 @@ +CREATE TABLE "REPORT_SUBSCRIPTIONS" +( + "UUID" CHARACTER VARYING(40) NOT NULL, + "PORTFOLIO_UUID" CHARACTER VARYING(40), + "BRANCH_UUID" CHARACTER VARYING(40), + "USER_UUID" CHARACTER VARYING(40) NOT NULL +); diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v101/PopulateReportSchedulesTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v101/PopulateReportSchedulesTest/schema.sql new file mode 100644 index 00000000000..09ad71d1580 --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v101/PopulateReportSchedulesTest/schema.sql @@ -0,0 +1,53 @@ +CREATE TABLE "REPORT_SCHEDULES"( + "UUID" CHARACTER VARYING(40) NOT NULL, + "PORTFOLIO_UUID" CHARACTER VARYING(40), + "BRANCH_UUID" CHARACTER VARYING(40), + "LAST_SEND_TIME_IN_MS" BIGINT NOT NULL +); + +CREATE TABLE "PROPERTIES"( + "UUID" CHARACTER VARYING(40) NOT NULL, + "PROP_KEY" CHARACTER VARYING(512) NOT NULL, + "IS_EMPTY" BOOLEAN NOT NULL, + "TEXT_VALUE" CHARACTER VARYING(4000), + "CLOB_VALUE" CHARACTER LARGE OBJECT, + "CREATED_AT" BIGINT NOT NULL, + "COMPONENT_UUID" CHARACTER VARYING(40), + "USER_UUID" CHARACTER VARYING(255) +); +ALTER TABLE "PROPERTIES" ADD CONSTRAINT "PK_PROPERTIES" PRIMARY KEY("UUID"); +CREATE INDEX "PROPERTIES_KEY" ON "PROPERTIES"("PROP_KEY" NULLS FIRST); + +CREATE TABLE "PORTFOLIOS"( + "UUID" CHARACTER VARYING(40) NOT NULL, + "KEE" CHARACTER VARYING(400) NOT NULL, + "NAME" CHARACTER VARYING(2000) NOT NULL, + "DESCRIPTION" CHARACTER VARYING(2000), + "ROOT_UUID" CHARACTER VARYING(40) NOT NULL, + "PARENT_UUID" CHARACTER VARYING(40), + "PRIVATE" BOOLEAN NOT NULL, + "SELECTION_MODE" CHARACTER VARYING(50) NOT NULL, + "SELECTION_EXPRESSION" CHARACTER VARYING(4000), + "CREATED_AT" BIGINT NOT NULL, + "UPDATED_AT" BIGINT NOT NULL, + "BRANCH_KEY" CHARACTER VARYING(255) +); +ALTER TABLE "PORTFOLIOS" ADD CONSTRAINT "PK_PORTFOLIOS" PRIMARY KEY("UUID"); +CREATE UNIQUE INDEX "UNIQ_PORTFOLIOS_KEE" ON "PORTFOLIOS"("KEE" NULLS FIRST); + +CREATE TABLE "PROJECT_BRANCHES"( + "UUID" CHARACTER VARYING(50) NOT NULL, + "PROJECT_UUID" CHARACTER VARYING(50) NOT NULL, + "KEE" CHARACTER VARYING(255) NOT NULL, + "BRANCH_TYPE" CHARACTER VARYING(12) NOT NULL, + "MERGE_BRANCH_UUID" CHARACTER VARYING(50), + "PULL_REQUEST_BINARY" BINARY LARGE OBJECT, + "MANUAL_BASELINE_ANALYSIS_UUID" CHARACTER VARYING(40), + "CREATED_AT" BIGINT NOT NULL, + "UPDATED_AT" BIGINT NOT NULL, + "EXCLUDE_FROM_PURGE" BOOLEAN DEFAULT FALSE NOT NULL, + "NEED_ISSUE_SYNC" BOOLEAN NOT NULL, + "IS_MAIN" BOOLEAN NOT NULL +); +ALTER TABLE "PROJECT_BRANCHES" ADD CONSTRAINT "PK_PROJECT_BRANCHES" PRIMARY KEY("UUID"); +CREATE UNIQUE INDEX "UNIQ_PROJECT_BRANCHES" ON "PROJECT_BRANCHES"("BRANCH_TYPE" NULLS FIRST, "PROJECT_UUID" NULLS FIRST, "KEE" NULLS FIRST); diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v101/PopulateReportSubscriptionsTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v101/PopulateReportSubscriptionsTest/schema.sql new file mode 100644 index 00000000000..66482b17ed9 --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v101/PopulateReportSubscriptionsTest/schema.sql @@ -0,0 +1,53 @@ +CREATE TABLE "REPORT_SUBSCRIPTIONS"( + "UUID" CHARACTER VARYING(40) NOT NULL, + "PORTFOLIO_UUID" CHARACTER VARYING(40), + "BRANCH_UUID" CHARACTER VARYING(40), + "USER_UUID" CHARACTER VARYING(40) NOT NULL +); + +CREATE TABLE "PROPERTIES"( + "UUID" CHARACTER VARYING(40) NOT NULL, + "PROP_KEY" CHARACTER VARYING(512) NOT NULL, + "IS_EMPTY" BOOLEAN NOT NULL, + "TEXT_VALUE" CHARACTER VARYING(4000), + "CLOB_VALUE" CHARACTER LARGE OBJECT, + "CREATED_AT" BIGINT NOT NULL, + "COMPONENT_UUID" CHARACTER VARYING(40), + "USER_UUID" CHARACTER VARYING(255) +); +ALTER TABLE "PROPERTIES" ADD CONSTRAINT "PK_PROPERTIES" PRIMARY KEY("UUID"); +CREATE INDEX "PROPERTIES_KEY" ON "PROPERTIES"("PROP_KEY" NULLS FIRST); + +CREATE TABLE "PORTFOLIOS"( + "UUID" CHARACTER VARYING(40) NOT NULL, + "KEE" CHARACTER VARYING(400) NOT NULL, + "NAME" CHARACTER VARYING(2000) NOT NULL, + "DESCRIPTION" CHARACTER VARYING(2000), + "ROOT_UUID" CHARACTER VARYING(40) NOT NULL, + "PARENT_UUID" CHARACTER VARYING(40), + "PRIVATE" BOOLEAN NOT NULL, + "SELECTION_MODE" CHARACTER VARYING(50) NOT NULL, + "SELECTION_EXPRESSION" CHARACTER VARYING(4000), + "CREATED_AT" BIGINT NOT NULL, + "UPDATED_AT" BIGINT NOT NULL, + "BRANCH_KEY" CHARACTER VARYING(255) +); +ALTER TABLE "PORTFOLIOS" ADD CONSTRAINT "PK_PORTFOLIOS" PRIMARY KEY("UUID"); +CREATE UNIQUE INDEX "UNIQ_PORTFOLIOS_KEE" ON "PORTFOLIOS"("KEE" NULLS FIRST); + +CREATE TABLE "PROJECT_BRANCHES"( + "UUID" CHARACTER VARYING(50) NOT NULL, + "PROJECT_UUID" CHARACTER VARYING(50) NOT NULL, + "KEE" CHARACTER VARYING(255) NOT NULL, + "BRANCH_TYPE" CHARACTER VARYING(12) NOT NULL, + "MERGE_BRANCH_UUID" CHARACTER VARYING(50), + "PULL_REQUEST_BINARY" BINARY LARGE OBJECT, + "MANUAL_BASELINE_ANALYSIS_UUID" CHARACTER VARYING(40), + "CREATED_AT" BIGINT NOT NULL, + "UPDATED_AT" BIGINT NOT NULL, + "EXCLUDE_FROM_PURGE" BOOLEAN DEFAULT FALSE NOT NULL, + "NEED_ISSUE_SYNC" BOOLEAN NOT NULL, + "IS_MAIN" BOOLEAN NOT NULL +); +ALTER TABLE "PROJECT_BRANCHES" ADD CONSTRAINT "PK_PROJECT_BRANCHES" PRIMARY KEY("UUID"); +CREATE UNIQUE INDEX "UNIQ_PROJECT_BRANCHES" ON "PROJECT_BRANCHES"("BRANCH_TYPE" NULLS FIRST, "PROJECT_UUID" NULLS FIRST, "KEE" NULLS FIRST); diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v101/RemoveReportPropertiesTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v101/RemoveReportPropertiesTest/schema.sql new file mode 100644 index 00000000000..cf3cb84e24e --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v101/RemoveReportPropertiesTest/schema.sql @@ -0,0 +1,12 @@ +CREATE TABLE "PROPERTIES"( + "UUID" CHARACTER VARYING(40) NOT NULL, + "PROP_KEY" CHARACTER VARYING(512) NOT NULL, + "IS_EMPTY" BOOLEAN NOT NULL, + "TEXT_VALUE" CHARACTER VARYING(4000), + "CLOB_VALUE" CHARACTER LARGE OBJECT, + "CREATED_AT" BIGINT NOT NULL, + "COMPONENT_UUID" CHARACTER VARYING(40), + "USER_UUID" CHARACTER VARYING(255) +); +ALTER TABLE "PROPERTIES" ADD CONSTRAINT "PK_PROPERTIES" PRIMARY KEY("UUID"); +CREATE INDEX "PROPERTIES_KEY" ON "PROPERTIES"("PROP_KEY" NULLS FIRST); diff --git a/server/sonar-webserver-es/src/test/java/org/sonar/server/permission/index/FooIndex.java b/server/sonar-webserver-es/src/test/java/org/sonar/server/permission/index/FooIndex.java index d41f8f26dc6..01830e1bd25 100644 --- a/server/sonar-webserver-es/src/test/java/org/sonar/server/permission/index/FooIndex.java +++ b/server/sonar-webserver-es/src/test/java/org/sonar/server/permission/index/FooIndex.java @@ -21,6 +21,7 @@ package org.sonar.server.permission.index; import java.util.Arrays; import java.util.List; +import org.elasticsearch.action.search.SearchRequest; import org.elasticsearch.index.query.QueryBuilders; import org.elasticsearch.search.SearchHits; import org.elasticsearch.search.builder.SearchSourceBuilder; @@ -41,9 +42,9 @@ public class FooIndex { public boolean hasAccessToProject(String projectUuid) { SearchHits hits = esClient.search(EsClient.prepareSearch(DESCRIPTOR.getName()) - .source(new SearchSourceBuilder().query(QueryBuilders.boolQuery() - .must(QueryBuilders.termQuery(FooIndexDefinition.FIELD_PROJECT_UUID, projectUuid)) - .filter(authorizationTypeSupport.createQueryFilter())))) + .source(new SearchSourceBuilder().query(QueryBuilders.boolQuery() + .must(QueryBuilders.termQuery(FooIndexDefinition.FIELD_PROJECT_UUID, projectUuid)) + .filter(authorizationTypeSupport.createQueryFilter())))) .getHits(); List names = Arrays.stream(hits.getHits()) .map(h -> h.getSourceAsMap().get(FooIndexDefinition.FIELD_NAME).toString()) -- 2.39.5