SONAR-14792 Fix issue on Oracle DB by recreating PK for 'events' table

12 files changed, 300 insertions, 3 deletions

diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v00/CreateInitialSchema.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v00/CreateInitialSchema.java
index 8ee0768762d..124d2baf996 100644
--- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v00/CreateInitialSchema.java
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v00/CreateInitialSchema.java
@@ -596,7 +596,6 @@ public class CreateInitialSchema extends DdlChange {
         .addColumn(TECHNICAL_CREATED_AT_COL)
         .addColumn(componentUuid)
         .build());
-    addIndex(context, tableName, "events_uuid", true, uuidCol);
     addIndex(context, tableName, "events_analysis", false, analysisUuidCol);
     addIndex(context, tableName, "events_component_uuid", false, componentUuid);
   }
diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v90/AddPrimaryKeyOnUuidColumnOfEventsTable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v90/AddPrimaryKeyOnUuidColumnOfEventsTable.java
new file mode 100644
index 00000000000..981f4a5041b
--- /dev/null
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v90/AddPrimaryKeyOnUuidColumnOfEventsTable.java
@@ -0,0 +1,39 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2021 SonarSource SA
+ * mailto:info AT sonarsource DOT com
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU Lesser General Public
+ * License as published by the Free Software Foundation; either
+ * version 3 of the License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+ * Lesser General Public License for more details.
+ *
+ * You should have received a copy of the GNU Lesser General Public License
+ * along with this program; if not, write to the Free Software Foundation,
+ * Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+ */
+package org.sonar.server.platform.db.migration.version.v90;
+
+import java.sql.SQLException;
+import org.sonar.db.Database;
+import org.sonar.server.platform.db.migration.sql.AddPrimaryKeyBuilder;
+import org.sonar.server.platform.db.migration.step.DdlChange;
+
+public class AddPrimaryKeyOnUuidColumnOfEventsTable extends DdlChange {
+  private static final String TABLE_NAME = "events";
+
+  public AddPrimaryKeyOnUuidColumnOfEventsTable(Database db) {
+    super(db);
+  }
+
+  @Override
+  public void execute(Context context) throws SQLException {
+    context.execute(new AddPrimaryKeyBuilder(TABLE_NAME, "uuid").build());
+  }
+
+}
diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v90/DbVersion90.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v90/DbVersion90.java
index afeedf1ae9c..e97973e15a3 100644
--- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v90/DbVersion90.java
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v90/DbVersion90.java
@@ -28,6 +28,9 @@ public class DbVersion90 implements DbVersion {
     registry
       .add(5001, "Drop PK on 'uuid' for 'ce_activity' table", DropPrimaryKeyOnUuidColumnOfCeActivityTable.class)
       .add(5002, "Drop 'ce_activity_uuid' index", DropCeActivityUuidIndex.class)
-      .add(5003, "Recreate PK on 'uuid' for 'ce_activity' table", AddPrimaryKeyOnUuidColumnOfCeActivityTable.class);
+      .add(5003, "Recreate PK on 'uuid' for 'ce_activity' table", AddPrimaryKeyOnUuidColumnOfCeActivityTable.class)
+      .add(5004, "Drop PK on 'uuid' for 'events' table", DropPrimaryKeyOnUuidColumnOfEventsTable.class)
+      .add(5005, "Drop 'events_uuid' index", DropEventsUuidIndex.class)
+      .add(5006, "Recreate PK on 'uuid' for 'events' table", AddPrimaryKeyOnUuidColumnOfEventsTable.class);
   }
 }
diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v90/DropEventsUuidIndex.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v90/DropEventsUuidIndex.java
new file mode 100644
index 00000000000..f5f1c38c642
--- /dev/null
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v90/DropEventsUuidIndex.java
@@ -0,0 +1,32 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2021 SonarSource SA
+ * mailto:info AT sonarsource DOT com
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU Lesser General Public
+ * License as published by the Free Software Foundation; either
+ * version 3 of the License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+ * Lesser General Public License for more details.
+ *
+ * You should have received a copy of the GNU Lesser General Public License
+ * along with this program; if not, write to the Free Software Foundation,
+ * Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+ */
+package org.sonar.server.platform.db.migration.version.v90;
+
+import org.sonar.db.Database;
+import org.sonar.server.platform.db.migration.step.DropIndexChange;
+
+public class DropEventsUuidIndex extends DropIndexChange {
+  private static final String INDEX_NAME = "events_uuid";
+  private static final String TABLE_NAME = "events";
+
+  public DropEventsUuidIndex(Database db) {
+    super(db, INDEX_NAME, TABLE_NAME);
+  }
+}
diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v90/DropPrimaryKeyOnUuidColumnOfEventsTable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v90/DropPrimaryKeyOnUuidColumnOfEventsTable.java
new file mode 100644
index 00000000000..8525b832959
--- /dev/null
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v90/DropPrimaryKeyOnUuidColumnOfEventsTable.java
@@ -0,0 +1,40 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2021 SonarSource SA
+ * mailto:info AT sonarsource DOT com
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU Lesser General Public
+ * License as published by the Free Software Foundation; either
+ * version 3 of the License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+ * Lesser General Public License for more details.
+ *
+ * You should have received a copy of the GNU Lesser General Public License
+ * along with this program; if not, write to the Free Software Foundation,
+ * Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+ */
+package org.sonar.server.platform.db.migration.version.v90;
+
+import java.sql.SQLException;
+import org.sonar.db.Database;
+import org.sonar.server.platform.db.migration.sql.DropPrimaryKeySqlGenerator;
+import org.sonar.server.platform.db.migration.step.DdlChange;
+
+public class DropPrimaryKeyOnUuidColumnOfEventsTable extends DdlChange {
+  private static final String TABLE_NAME = "events";
+  private final DropPrimaryKeySqlGenerator dropPrimaryKeySqlGenerator;
+
+  public DropPrimaryKeyOnUuidColumnOfEventsTable(Database db, DropPrimaryKeySqlGenerator dropPrimaryKeySqlGenerator) {
+    super(db);
+    this.dropPrimaryKeySqlGenerator = dropPrimaryKeySqlGenerator;
+  }
+
+  @Override
+  public void execute(Context context) throws SQLException {
+    context.execute(dropPrimaryKeySqlGenerator.generate(TABLE_NAME, "uuid", false));
+  }
+}
diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v90/AddPrimaryKeyOnUuidColumnOfEventsTableTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v90/AddPrimaryKeyOnUuidColumnOfEventsTableTest.java
new file mode 100644
index 00000000000..7198378d40a
--- /dev/null
+++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v90/AddPrimaryKeyOnUuidColumnOfEventsTableTest.java
@@ -0,0 +1,40 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2021 SonarSource SA
+ * mailto:info AT sonarsource DOT com
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU Lesser General Public
+ * License as published by the Free Software Foundation; either
+ * version 3 of the License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+ * Lesser General Public License for more details.
+ *
+ * You should have received a copy of the GNU Lesser General Public License
+ * along with this program; if not, write to the Free Software Foundation,
+ * Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+ */
+package org.sonar.server.platform.db.migration.version.v90;
+
+import java.sql.SQLException;
+import org.junit.Rule;
+import org.junit.Test;
+import org.sonar.db.CoreDbTester;
+
+public class AddPrimaryKeyOnUuidColumnOfEventsTableTest {
+
+  @Rule
+  public final CoreDbTester db = CoreDbTester.createForSchema(AddPrimaryKeyOnUuidColumnOfEventsTableTest.class, "schema.sql");
+
+  private final AddPrimaryKeyOnUuidColumnOfEventsTable underTest = new AddPrimaryKeyOnUuidColumnOfEventsTable(db.database());
+
+  @Test
+  public void migration_should_drop_PK_on_events() throws SQLException {
+    db.assertNoPrimaryKey("events");
+    underTest.execute();
+    db.assertPrimaryKey("events", "pk_events", "uuid");
+  }
+}
diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v90/DbVersion90Test.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v90/DbVersion90Test.java
index 3e4910cbafe..5b57d83e61d 100644
--- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v90/DbVersion90Test.java
+++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v90/DbVersion90Test.java
@@ -41,7 +41,7 @@ public class DbVersion90Test {
 
   @Test
   public void verify_migration_count() {
-    verifyMigrationCount(underTest, 3);
+    verifyMigrationCount(underTest, 6);
   }
 
 }
diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v90/DropEventsUuidIndexTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v90/DropEventsUuidIndexTest.java
new file mode 100644
index 00000000000..6827cf4d92f
--- /dev/null
+++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v90/DropEventsUuidIndexTest.java
@@ -0,0 +1,49 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2021 SonarSource SA
+ * mailto:info AT sonarsource DOT com
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU Lesser General Public
+ * License as published by the Free Software Foundation; either
+ * version 3 of the License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+ * Lesser General Public License for more details.
+ *
+ * You should have received a copy of the GNU Lesser General Public License
+ * along with this program; if not, write to the Free Software Foundation,
+ * Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+ */
+package org.sonar.server.platform.db.migration.version.v90;
+
+import java.sql.SQLException;
+import org.junit.Rule;
+import org.junit.Test;
+import org.sonar.db.CoreDbTester;
+
+public class DropEventsUuidIndexTest {
+
+  @Rule
+  public final CoreDbTester db = CoreDbTester.createForSchema(DropEventsUuidIndexTest.class, "schema.sql");
+
+  private final DropEventsUuidIndex underTest = new DropEventsUuidIndex(db.database());
+
+  @Test
+  public void migration_should_drop_unique_index_on_events() throws SQLException {
+    db.assertUniqueIndex("events", "events_uuid", "uuid");
+    underTest.execute();
+    db.assertIndexDoesNotExist("events", "events_uuid");
+  }
+
+  @Test
+  public void migration_should_be_reentrant() throws SQLException {
+    db.assertUniqueIndex("events", "events_uuid", "uuid");
+    underTest.execute();
+    // re-entrant
+    underTest.execute();
+    db.assertIndexDoesNotExist("events", "events_uuid");
+  }
+}
diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v90/DropPrimaryKeyOnUuidColumnOfEventsTableTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v90/DropPrimaryKeyOnUuidColumnOfEventsTableTest.java
new file mode 100644
index 00000000000..3d677fe8f0e
--- /dev/null
+++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v90/DropPrimaryKeyOnUuidColumnOfEventsTableTest.java
@@ -0,0 +1,53 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2021 SonarSource SA
+ * mailto:info AT sonarsource DOT com
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU Lesser General Public
+ * License as published by the Free Software Foundation; either
+ * version 3 of the License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+ * Lesser General Public License for more details.
+ *
+ * You should have received a copy of the GNU Lesser General Public License
+ * along with this program; if not, write to the Free Software Foundation,
+ * Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+ */
+package org.sonar.server.platform.db.migration.version.v90;
+
+import java.sql.SQLException;
+import org.junit.Rule;
+import org.junit.Test;
+import org.sonar.db.CoreDbTester;
+import org.sonar.server.platform.db.migration.sql.DbPrimaryKeyConstraintFinder;
+import org.sonar.server.platform.db.migration.sql.DropPrimaryKeySqlGenerator;
+
+public class DropPrimaryKeyOnUuidColumnOfEventsTableTest {
+
+  @Rule
+  public final CoreDbTester db = CoreDbTester.createForSchema(DropPrimaryKeyOnUuidColumnOfEventsTableTest.class, "schema.sql");
+
+  private final DropPrimaryKeyOnUuidColumnOfEventsTable underTest = new DropPrimaryKeyOnUuidColumnOfEventsTable(
+    db.database(),
+    new DropPrimaryKeySqlGenerator(db.database(), new DbPrimaryKeyConstraintFinder(db.database())));
+
+  @Test
+  public void migration_should_drop_PK_on_events() throws SQLException {
+    db.assertPrimaryKey("events", "pk_events", "uuid");
+    underTest.execute();
+    db.assertNoPrimaryKey("events");
+  }
+
+  @Test
+  public void migration_should_be_reentrant() throws SQLException {
+    db.assertPrimaryKey("events", "pk_events", "uuid");
+    underTest.execute();
+    // re-entrant
+    underTest.execute();
+    db.assertNoPrimaryKey("events");
+  }
+}
diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v90/AddPrimaryKeyOnUuidColumnOfEventsTableTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v90/AddPrimaryKeyOnUuidColumnOfEventsTableTest/schema.sql
new file mode 100644
index 00000000000..29a4bb24869
--- /dev/null
+++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v90/AddPrimaryKeyOnUuidColumnOfEventsTableTest/schema.sql
@@ -0,0 +1,13 @@
+CREATE TABLE "EVENTS"(
+    "UUID" VARCHAR(40) NOT NULL,
+    "ANALYSIS_UUID" VARCHAR(50) NOT NULL,
+    "NAME" VARCHAR(400),
+    "CATEGORY" VARCHAR(50),
+    "DESCRIPTION" VARCHAR(4000),
+    "EVENT_DATA" VARCHAR(4000),
+    "EVENT_DATE" BIGINT NOT NULL,
+    "CREATED_AT" BIGINT NOT NULL,
+    "COMPONENT_UUID" VARCHAR(50) NOT NULL
+);
+CREATE INDEX "EVENTS_ANALYSIS" ON "EVENTS"("ANALYSIS_UUID");
+CREATE INDEX "EVENTS_COMPONENT_UUID" ON "EVENTS"("COMPONENT_UUID");
diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v90/DropEventsUuidIndexTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v90/DropEventsUuidIndexTest/schema.sql
new file mode 100644
index 00000000000..fe450881978
--- /dev/null
+++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v90/DropEventsUuidIndexTest/schema.sql
@@ -0,0 +1,15 @@
+CREATE TABLE "EVENTS"(
+    "UUID" VARCHAR(40) NOT NULL,
+    "ANALYSIS_UUID" VARCHAR(50) NOT NULL,
+    "NAME" VARCHAR(400),
+    "CATEGORY" VARCHAR(50),
+    "DESCRIPTION" VARCHAR(4000),
+    "EVENT_DATA" VARCHAR(4000),
+    "EVENT_DATE" BIGINT NOT NULL,
+    "CREATED_AT" BIGINT NOT NULL,
+    "COMPONENT_UUID" VARCHAR(50) NOT NULL
+);
+ALTER TABLE "EVENTS" ADD CONSTRAINT "PK_EVENTS" PRIMARY KEY("UUID");
+CREATE UNIQUE INDEX "EVENTS_UUID" ON "EVENTS"("UUID");
+CREATE INDEX "EVENTS_ANALYSIS" ON "EVENTS"("ANALYSIS_UUID");
+CREATE INDEX "EVENTS_COMPONENT_UUID" ON "EVENTS"("COMPONENT_UUID");
diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v90/DropPrimaryKeyOnUuidColumnOfEventsTableTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v90/DropPrimaryKeyOnUuidColumnOfEventsTableTest/schema.sql
new file mode 100644
index 00000000000..28fa71b4e36
--- /dev/null
+++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v90/DropPrimaryKeyOnUuidColumnOfEventsTableTest/schema.sql
@@ -0,0 +1,14 @@
+CREATE TABLE "EVENTS"(
+    "UUID" VARCHAR(40) NOT NULL,
+    "ANALYSIS_UUID" VARCHAR(50) NOT NULL,
+    "NAME" VARCHAR(400),
+    "CATEGORY" VARCHAR(50),
+    "DESCRIPTION" VARCHAR(4000),
+    "EVENT_DATA" VARCHAR(4000),
+    "EVENT_DATE" BIGINT NOT NULL,
+    "CREATED_AT" BIGINT NOT NULL,
+    "COMPONENT_UUID" VARCHAR(50) NOT NULL
+);
+ALTER TABLE "EVENTS" ADD CONSTRAINT "PK_EVENTS" PRIMARY KEY("UUID");
+CREATE INDEX "EVENTS_ANALYSIS" ON "EVENTS"("ANALYSIS_UUID");
+CREATE INDEX "EVENTS_COMPONENT_UUID" ON "EVENTS"("COMPONENT_UUID");