SONAR-17156 add unique index on project_uuid of project_badge_token and clean-up duplicates

7 files changed, 297 insertions, 0 deletions

diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v96/CreateIndexForProjectBadgeTokens.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v96/CreateIndexForProjectBadgeTokens.java
new file mode 100644
index 00000000000..4f8f9e0b2db
--- /dev/null
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v96/CreateIndexForProjectBadgeTokens.java
@@ -0,0 +1,61 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2022 SonarSource SA
+ * mailto:info AT sonarsource DOT com
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU Lesser General Public
+ * License as published by the Free Software Foundation; either
+ * version 3 of the License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+ * Lesser General Public License for more details.
+ *
+ * You should have received a copy of the GNU Lesser General Public License
+ * along with this program; if not, write to the Free Software Foundation,
+ * Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+ */
+package org.sonar.server.platform.db.migration.version.v96;
+
+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;
+
+public class CreateIndexForProjectBadgeTokens extends DdlChange {
+
+  @VisibleForTesting
+  static final String INDEX_NAME = "uniq_project_badge_token";
+  @VisibleForTesting
+  static final String TABLE = "project_badge_token";
+  @VisibleForTesting
+  static final String COLUMN_NAME = "project_uuid";
+
+  public CreateIndexForProjectBadgeTokens(Database db) {
+    super(db);
+  }
+
+  @Override
+  public void execute(Context context) throws SQLException {
+    try (Connection connection = getDatabase().getDataSource().getConnection()) {
+      createProjectBadgeTokenUniqueIndex(context, connection);
+    }
+  }
+
+  private static void createProjectBadgeTokenUniqueIndex(Context context, Connection connection) {
+    if (!DatabaseUtils.indexExistsIgnoreCase(TABLE, INDEX_NAME, connection)) {
+      context.execute(new CreateIndexBuilder()
+        .setTable(TABLE)
+        .setName(INDEX_NAME)
+        .addColumn(COLUMN_NAME)
+        .setUnique(true)
+        .build());
+    }
+  }
+
+}
diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v96/DbVersion96.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v96/DbVersion96.java
index aeb62b406b0..57d133f3fd3 100644
--- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v96/DbVersion96.java
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v96/DbVersion96.java
@@ -41,6 +41,8 @@ public class DbVersion96 implements DbVersion {
       .add(6510, "Create table 'push_events'", CreatePushEventsTable.class)
       .add(6511, "Create index 'idx_push_even_crea_uuid_proj' on 'push_events'", CreateIndexForPushEvents.class)
       .add(6512, "Add column 'language' to 'push_events'", AddLanguageColumnToPushEventsTable.class)
+      .add(6513, "Delete duplicated rows in 'project_badge_token'", DeleteDuplicatedProjectBadgeTokens.class)
+      .add(6514, "Add unique index on 'project_uuid' in 'project_badge_token'", CreateIndexForProjectBadgeTokens.class)
     ;
   }
 }
diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v96/DeleteDuplicatedProjectBadgeTokens.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v96/DeleteDuplicatedProjectBadgeTokens.java
new file mode 100644
index 00000000000..3d0f5050233
--- /dev/null
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v96/DeleteDuplicatedProjectBadgeTokens.java
@@ -0,0 +1,49 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2022 SonarSource SA
+ * mailto:info AT sonarsource DOT com
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU Lesser General Public
+ * License as published by the Free Software Foundation; either
+ * version 3 of the License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+ * Lesser General Public License for more details.
+ *
+ * You should have received a copy of the GNU Lesser General Public License
+ * along with this program; if not, write to the Free Software Foundation,
+ * Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+ */
+package org.sonar.server.platform.db.migration.version.v96;
+
+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.Upsert;
+
+public class DeleteDuplicatedProjectBadgeTokens extends DataChange {
+
+  private static final String DELETE_ITEMS_WITH_DIFFERENT_CREATION_DATES = "delete from project_badge_token where exists ("
+    + " select 1 from project_badge_token b where project_badge_token.project_uuid = b.project_uuid and project_badge_token.created_at > b.created_at)";
+  private static final String DELETE_ITEMS_WITH_SAME_CREATION_DATES = "delete from project_badge_token where exists ("
+    + " select 1 from project_badge_token b where project_badge_token.project_uuid = b.project_uuid and b.uuid < project_badge_token.uuid)";
+
+  public DeleteDuplicatedProjectBadgeTokens(Database db) {
+    super(db);
+  }
+
+  @Override
+  protected void execute(Context context) throws SQLException {
+    executeQuery(context, DELETE_ITEMS_WITH_DIFFERENT_CREATION_DATES);
+    executeQuery(context, DELETE_ITEMS_WITH_SAME_CREATION_DATES);
+  }
+
+  private static void executeQuery(Context context, String sql) throws SQLException {
+    Upsert upsert = context.prepareUpsert(sql);
+    upsert.execute();
+    upsert.commit();
+  }
+}
diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v96/CreateIndexForProjectBadgeTokensTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v96/CreateIndexForProjectBadgeTokensTest.java
new file mode 100644
index 00000000000..8b14b1b8315
--- /dev/null
+++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v96/CreateIndexForProjectBadgeTokensTest.java
@@ -0,0 +1,52 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2022 SonarSource SA
+ * mailto:info AT sonarsource DOT com
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU Lesser General Public
+ * License as published by the Free Software Foundation; either
+ * version 3 of the License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+ * Lesser General Public License for more details.
+ *
+ * You should have received a copy of the GNU Lesser General Public License
+ * along with this program; if not, write to the Free Software Foundation,
+ * Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+ */
+package org.sonar.server.platform.db.migration.version.v96;
+
+import java.sql.SQLException;
+import org.junit.Rule;
+import org.junit.Test;
+import org.sonar.db.CoreDbTester;
+
+public class CreateIndexForProjectBadgeTokensTest {
+
+  @Rule
+  public final CoreDbTester db = CoreDbTester.createForSchema(CreateIndexForProjectBadgeTokensTest.class, "schema.sql");
+
+  private final CreateIndexForProjectBadgeTokens createIndexForProjectBadgeTokens = new CreateIndexForProjectBadgeTokens(db.database());
+
+  @Test
+  public void migration_should_create_index() throws SQLException {
+    db.assertIndexDoesNotExist(CreateIndexForProjectBadgeTokens.TABLE, CreateIndexForProjectBadgeTokens.INDEX_NAME);
+
+    createIndexForProjectBadgeTokens.execute();
+
+    db.assertUniqueIndex(CreateIndexForProjectBadgeTokens.TABLE, CreateIndexForProjectBadgeTokens.INDEX_NAME, CreateIndexForProjectBadgeTokens.COLUMN_NAME);
+  }
+
+  @Test
+  public void migration_should_be_reentrant() throws SQLException {
+    createIndexForProjectBadgeTokens.execute();
+
+    createIndexForProjectBadgeTokens.execute();
+
+    db.assertUniqueIndex(CreateIndexForProjectBadgeTokens.TABLE, CreateIndexForProjectBadgeTokens.INDEX_NAME, CreateIndexForProjectBadgeTokens.COLUMN_NAME);
+  }
+
+}
diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v96/DeleteDuplicatedProjectBadgeTokensTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v96/DeleteDuplicatedProjectBadgeTokensTest.java
new file mode 100644
index 00000000000..75da8eac7e8
--- /dev/null
+++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v96/DeleteDuplicatedProjectBadgeTokensTest.java
@@ -0,0 +1,117 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2022 SonarSource SA
+ * mailto:info AT sonarsource DOT com
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU Lesser General Public
+ * License as published by the Free Software Foundation; either
+ * version 3 of the License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+ * Lesser General Public License for more details.
+ *
+ * You should have received a copy of the GNU Lesser General Public License
+ * along with this program; if not, write to the Free Software Foundation,
+ * Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+ */
+package org.sonar.server.platform.db.migration.version.v96;
+
+import java.sql.SQLException;
+import org.junit.Rule;
+import org.junit.Test;
+import org.sonar.db.CoreDbTester;
+
+import static org.assertj.core.api.Assertions.assertThat;
+
+public class DeleteDuplicatedProjectBadgeTokensTest {
+  @Rule
+  public final CoreDbTester db = CoreDbTester.createForSchema(DeleteDuplicatedProjectBadgeTokensTest.class, "schema.sql");
+
+  private final DeleteDuplicatedProjectBadgeTokens deleteDuplicatedProjectBadgeTokens = new DeleteDuplicatedProjectBadgeTokens(db.database());
+
+  @Test
+  public void deleteDuplicatedProjectBadgeTokens_whenTokenForDifferentProjects_doesNothing() throws SQLException {
+    insertProjectBadgeToken("uuid1", "proj1", 1);
+    insertProjectBadgeToken("uuid2", "proj2", 1);
+
+    deleteDuplicatedProjectBadgeTokens.execute();
+
+    assertThat(db.countRowsOfTable("project_badge_token")).isEqualTo(2);
+  }
+
+  @Test
+  public void deleteDuplicatedProjectBadgeTokens_whenSeveralTokensForSameProjectsAtTheSameTime_leavesOneToken() throws SQLException {
+    insertProjectBadgeToken("uuid1", "proj1", 1);
+    insertProjectBadgeToken("uuid2", "proj1", 1);
+    insertProjectBadgeToken("uuid3", "proj1", 1);
+
+    deleteDuplicatedProjectBadgeTokens.execute();
+
+    assertThat(db.countRowsOfTable("project_badge_token")).isEqualTo(1);
+  }
+
+  @Test
+  public void deleteDuplicatedProjectBadgeTokens_whenSeveralTokensForSameProjectsAtDifferentTime_leavesMostAncient() throws SQLException {
+    insertProjectBadgeToken("uuid1", "proj1", 2);
+    insertProjectBadgeToken("uuid2", "proj1", 1);
+    insertProjectBadgeToken("uuid3", "proj1", 3);
+
+    deleteDuplicatedProjectBadgeTokens.execute();
+
+    assertThat(db.countRowsOfTable("project_badge_token")).isEqualTo(1);
+    assertThat(db.selectFirst("select UUID from project_badge_token")).containsEntry("UUID", "uuid2");
+  }
+
+  @Test
+  public void deleteDuplicatedProjectBadgeTokens_whenSeveralTokensForSameProjectsAtDifferentAndSameTime_leavesAnyMostAncient() throws SQLException {
+    insertProjectBadgeToken("uuid1", "proj1", 2);
+    insertProjectBadgeToken("uuid2", "proj1", 1);
+    insertProjectBadgeToken("uuid3", "proj1", 3);
+    insertProjectBadgeToken("uuid4", "proj1", 1);
+    insertProjectBadgeToken("uuid5", "proj1", 2);
+
+    deleteDuplicatedProjectBadgeTokens.execute();
+
+    assertThat(db.countRowsOfTable("project_badge_token")).isEqualTo(1);
+    assertThat(db.selectFirst("select UUID from project_badge_token")).containsEntry("UUID", "uuid2");
+  }
+
+  @Test
+  public void deleteDuplicatedProjectBadgeTokens_whenSeveralTokensForSameProjectsAtDifferentAndSameTime_leavesAnyMostAncient2() throws SQLException {
+    insertProjectBadgeToken("uuid1", "proj1", 2);
+    insertProjectBadgeToken("uuid2", "proj1", 1);
+    insertProjectBadgeToken("uuid3", "proj1", 3);
+    insertProjectBadgeToken("uuid4", "proj2", 1);
+    insertProjectBadgeToken("uuid5", "proj2", 1);
+
+    deleteDuplicatedProjectBadgeTokens.execute();
+
+    assertThat(db.countRowsOfTable("project_badge_token")).isEqualTo(2);
+    assertThat(db.select("select UUID from project_badge_token")).extracting(e -> e.get("UUID")).containsOnly("uuid2", "uuid4");
+  }
+
+  @Test
+  public void deleteDuplicatedProjectBadgeTokens_reentrantTest() throws SQLException {
+    insertProjectBadgeToken("uuid1", "proj1", 1);
+    insertProjectBadgeToken("uuid2", "proj2", 1);
+
+    deleteDuplicatedProjectBadgeTokens.execute();
+    deleteDuplicatedProjectBadgeTokens.execute();
+    deleteDuplicatedProjectBadgeTokens.execute();
+
+    assertThat(db.countRowsOfTable("project_badge_token")).isEqualTo(2);
+  }
+
+  private void insertProjectBadgeToken(String uuid, String projectUuid, long createdAt) {
+    db.executeInsert("project_badge_token", "UUID", uuid,
+      "token", "TEST_TOKEN",
+      "project_uuid", projectUuid,
+      "created_at", createdAt,
+      "updated_at", createdAt
+    );
+  }
+
+}
diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v96/CreateIndexForProjectBadgeTokensTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v96/CreateIndexForProjectBadgeTokensTest/schema.sql
new file mode 100644
index 00000000000..b64839cf72d
--- /dev/null
+++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v96/CreateIndexForProjectBadgeTokensTest/schema.sql
@@ -0,0 +1,8 @@
+CREATE TABLE "PROJECT_BADGE_TOKEN"(
+    "UUID" CHARACTER VARYING(40) NOT NULL,
+    "TOKEN" CHARACTER VARYING(255) NOT NULL,
+    "PROJECT_UUID" CHARACTER VARYING(40) NOT NULL,
+    "CREATED_AT" BIGINT NOT NULL,
+    "UPDATED_AT" BIGINT NOT NULL
+);
+ALTER TABLE "PROJECT_BADGE_TOKEN" ADD CONSTRAINT "PK_PROJECT_BADGE_TOKEN" PRIMARY KEY("UUID");
diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v96/DeleteDuplicatedProjectBadgeTokensTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v96/DeleteDuplicatedProjectBadgeTokensTest/schema.sql
new file mode 100644
index 00000000000..b64839cf72d
--- /dev/null
+++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v96/DeleteDuplicatedProjectBadgeTokensTest/schema.sql
@@ -0,0 +1,8 @@
+CREATE TABLE "PROJECT_BADGE_TOKEN"(
+    "UUID" CHARACTER VARYING(40) NOT NULL,
+    "TOKEN" CHARACTER VARYING(255) NOT NULL,
+    "PROJECT_UUID" CHARACTER VARYING(40) NOT NULL,
+    "CREATED_AT" BIGINT NOT NULL,
+    "UPDATED_AT" BIGINT NOT NULL
+);
+ALTER TABLE "PROJECT_BADGE_TOKEN" ADD CONSTRAINT "PK_PROJECT_BADGE_TOKEN" PRIMARY KEY("UUID");