From e2ecf6368cc6f5d906bc5cf3026931c8dd15d600 Mon Sep 17 00:00:00 2001 From: Jacek Date: Thu, 17 Jun 2021 09:33:31 +0200 Subject: [PATCH] SONAR-14792 Fix issue on Oracle DB by recreating PK for 'ce_activity' When switching from ID to UUID for 'ce_activity' table, it has been forgotten to drop unique index on UUID, though PK has been created. In all DBs it worked well, but for Oracle already existing index has been associated with PK (worked well on upgrade path). When new schema has been introduced, that's not the case, as for Oracle when creating PK, unique index also will be created. That means we tried to create another index on top of PK index, which was causing failures. To fix that issue previous UUID index will be dropped and PK will be recreated, so that we end with clean state. --- server/sonar-db-dao/src/schema/schema-sq.ddl | 1 - .../version/v00/CreateInitialSchema.java | 1 - ...maryKeyOnUuidColumnOfCeActivityTable.java} | 17 +++--- .../db/migration/version/v90/DbVersion90.java | 4 +- .../version/v90/DropCeActivityUuidIndex.java} | 21 +++----- ...imaryKeyOnUuidColumnOfCeActivityTable.java | 40 ++++++++++++++ ...yKeyOnUuidColumnOfCeActivityTableTest.java | 40 ++++++++++++++ .../version/v90/DbVersion90Test.java | 8 +-- .../v90/DropCeActivityUuidIndexTest.java | 49 +++++++++++++++++ ...yKeyOnUuidColumnOfCeActivityTableTest.java | 53 +++++++++++++++++++ .../internal_properties.sql | 8 --- .../schema.sql | 30 +++++++++++ .../DropCeActivityUuidIndexTest/schema.sql | 32 +++++++++++ .../schema.sql | 31 +++++++++++ 14 files changed, 296 insertions(+), 39 deletions(-) rename server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v90/{InitialMigration.java => AddPrimaryKeyOnUuidColumnOfCeActivityTable.java} (68%) rename server/sonar-db-migration/src/{test/java/org/sonar/server/platform/db/migration/version/v90/InitialMigrationTest.java => main/java/org/sonar/server/platform/db/migration/version/v90/DropCeActivityUuidIndex.java} (63%) create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v90/DropPrimaryKeyOnUuidColumnOfCeActivityTable.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v90/AddPrimaryKeyOnUuidColumnOfCeActivityTableTest.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v90/DropCeActivityUuidIndexTest.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v90/DropPrimaryKeyOnUuidColumnOfCeActivityTableTest.java delete mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/DefaultOrganizationUuidProviderImplTest/internal_properties.sql create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v90/AddPrimaryKeyOnUuidColumnOfCeActivityTableTest/schema.sql create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v90/DropCeActivityUuidIndexTest/schema.sql create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v90/DropPrimaryKeyOnUuidColumnOfCeActivityTableTest/schema.sql diff --git a/server/sonar-db-dao/src/schema/schema-sq.ddl b/server/sonar-db-dao/src/schema/schema-sq.ddl index c7cd6763301..9bd1d4b4c57 100644 --- a/server/sonar-db-dao/src/schema/schema-sq.ddl +++ b/server/sonar-db-dao/src/schema/schema-sq.ddl @@ -131,7 +131,6 @@ CREATE INDEX "CE_ACTIVITY_ISLAST_KEY" ON "CE_ACTIVITY"("IS_LAST_KEY"); CREATE INDEX "CE_ACTIVITY_MAIN_COMPONENT" ON "CE_ACTIVITY"("MAIN_COMPONENT_UUID"); CREATE INDEX "CE_ACTIVITY_MAIN_ISLAST" ON "CE_ACTIVITY"("MAIN_IS_LAST", "STATUS"); CREATE INDEX "CE_ACTIVITY_MAIN_ISLAST_KEY" ON "CE_ACTIVITY"("MAIN_IS_LAST_KEY"); -CREATE UNIQUE INDEX "CE_ACTIVITY_UUID" ON "CE_ACTIVITY"("UUID"); CREATE TABLE "CE_QUEUE"( "UUID" VARCHAR(40) NOT NULL, 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 37851839f4f..8ee0768762d 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 @@ -356,7 +356,6 @@ public class CreateInitialSchema extends DdlChange { addIndex(context, tableName, "ce_activity_main_component", false, mainComponentUuidCol); addIndex(context, tableName, "ce_activity_main_islast", false, mainIsLastCol, statusCol); addIndex(context, tableName, "ce_activity_main_islast_key", false, mainIsLastKeyCol); - addIndex(context, tableName, "ce_activity_uuid", true, uuidCol); } private void createCeQueue(Context context) { diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v90/InitialMigration.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v90/AddPrimaryKeyOnUuidColumnOfCeActivityTable.java similarity index 68% rename from server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v90/InitialMigration.java rename to server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v90/AddPrimaryKeyOnUuidColumnOfCeActivityTable.java index dedfd099f72..48d44b55f1d 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v90/InitialMigration.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v90/AddPrimaryKeyOnUuidColumnOfCeActivityTable.java @@ -21,20 +21,19 @@ 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.step.DataChange; +import org.sonar.server.platform.db.migration.sql.AddPrimaryKeyBuilder; +import org.sonar.server.platform.db.migration.step.DdlChange; -/** - * Just a nop migration to put new migrations numbering in place, - * and let current DB version algorithm to not determine 9.0 as required to be downgraded - */ -public class InitialMigration extends DataChange { +public class AddPrimaryKeyOnUuidColumnOfCeActivityTable extends DdlChange { + private static final String TABLE_NAME = "ce_activity"; - public InitialMigration(Database db) { + public AddPrimaryKeyOnUuidColumnOfCeActivityTable(Database db) { super(db); } @Override - protected void execute(Context context) throws SQLException { - // nothing to do + 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 6521c151280..afeedf1ae9c 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 @@ -26,6 +26,8 @@ public class DbVersion90 implements DbVersion { @Override public void addSteps(MigrationStepRegistry registry) { registry - .add(5000, "Initial migration", InitialMigration.class); + .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); } } diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v90/InitialMigrationTest.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v90/DropCeActivityUuidIndex.java similarity index 63% rename from server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v90/InitialMigrationTest.java rename to server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v90/DropCeActivityUuidIndex.java index b9777f62e4a..5e5b2f398d1 100644 --- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v90/InitialMigrationTest.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v90/DropCeActivityUuidIndex.java @@ -19,23 +19,14 @@ */ package org.sonar.server.platform.db.migration.version.v90; -import java.sql.SQLException; -import org.junit.Test; import org.sonar.db.Database; -import org.sonar.server.platform.db.migration.step.DataChange.Context; +import org.sonar.server.platform.db.migration.step.DropIndexChange; -import static org.mockito.Mockito.mock; -import static org.mockito.Mockito.verifyNoInteractions; +public class DropCeActivityUuidIndex extends DropIndexChange { + private static final String INDEX_NAME = "ce_activity_uuid"; + private static final String TABLE_NAME = "ce_activity"; -public class InitialMigrationTest { - - private final InitialMigration underTest = new InitialMigration(mock(Database.class)); - - @Test - public void migration_should_do_nothing() throws SQLException { - Context mockContext = mock(Context.class); - underTest.execute(mockContext); - - verifyNoInteractions(mockContext); + public DropCeActivityUuidIndex(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/DropPrimaryKeyOnUuidColumnOfCeActivityTable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v90/DropPrimaryKeyOnUuidColumnOfCeActivityTable.java new file mode 100644 index 00000000000..7a4863bf649 --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v90/DropPrimaryKeyOnUuidColumnOfCeActivityTable.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 DropPrimaryKeyOnUuidColumnOfCeActivityTable extends DdlChange { + private static final String TABLE_NAME = "ce_activity"; + private final DropPrimaryKeySqlGenerator dropPrimaryKeySqlGenerator; + + public DropPrimaryKeyOnUuidColumnOfCeActivityTable(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/AddPrimaryKeyOnUuidColumnOfCeActivityTableTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v90/AddPrimaryKeyOnUuidColumnOfCeActivityTableTest.java new file mode 100644 index 00000000000..3b78d3d84d1 --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v90/AddPrimaryKeyOnUuidColumnOfCeActivityTableTest.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 AddPrimaryKeyOnUuidColumnOfCeActivityTableTest { + + @Rule + public final CoreDbTester db = CoreDbTester.createForSchema(AddPrimaryKeyOnUuidColumnOfCeActivityTableTest.class, "schema.sql"); + + private final AddPrimaryKeyOnUuidColumnOfCeActivityTable underTest = new AddPrimaryKeyOnUuidColumnOfCeActivityTable(db.database()); + + @Test + public void migration_should_drop_PK_on_ce_activity() throws SQLException { + db.assertNoPrimaryKey("ce_activity"); + underTest.execute(); + db.assertPrimaryKey("ce_activity", "pk_ce_activity", "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 0746d998d23..3e4910cbafe 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 @@ -27,7 +27,7 @@ import static org.sonar.server.platform.db.migration.version.DbVersionTestUtils. public class DbVersion90Test { - private DbVersion90 underTest = new DbVersion90(); + private final DbVersion90 underTest = new DbVersion90(); @Test public void verify_no_support_component() { @@ -35,13 +35,13 @@ public class DbVersion90Test { } @Test - public void migrationNumber_starts_at_5000() { - verifyMinimumMigrationNumber(underTest, 5000); + public void migrationNumber_starts_at_5001() { + verifyMinimumMigrationNumber(underTest, 5001); } @Test public void verify_migration_count() { - verifyMigrationCount(underTest, 1); + verifyMigrationCount(underTest, 3); } } diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v90/DropCeActivityUuidIndexTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v90/DropCeActivityUuidIndexTest.java new file mode 100644 index 00000000000..4e56e1f3685 --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v90/DropCeActivityUuidIndexTest.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 DropCeActivityUuidIndexTest { + + @Rule + public final CoreDbTester db = CoreDbTester.createForSchema(DropCeActivityUuidIndexTest.class, "schema.sql"); + + private final DropCeActivityUuidIndex underTest = new DropCeActivityUuidIndex(db.database()); + + @Test + public void migration_should_drop_unique_index_on_ce_activity() throws SQLException { + db.assertUniqueIndex("ce_activity", "ce_activity_uuid", "uuid"); + underTest.execute(); + db.assertIndexDoesNotExist("ce_activity", "ce_activity_uuid"); + } + + @Test + public void migration_should_be_reentrant() throws SQLException { + db.assertUniqueIndex("ce_activity", "ce_activity_uuid", "uuid"); + underTest.execute(); + // re-entrant + underTest.execute(); + db.assertIndexDoesNotExist("ce_activity", "ce_activity_uuid"); + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v90/DropPrimaryKeyOnUuidColumnOfCeActivityTableTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v90/DropPrimaryKeyOnUuidColumnOfCeActivityTableTest.java new file mode 100644 index 00000000000..e190e244a7b --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v90/DropPrimaryKeyOnUuidColumnOfCeActivityTableTest.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 DropPrimaryKeyOnUuidColumnOfCeActivityTableTest { + + @Rule + public final CoreDbTester db = CoreDbTester.createForSchema(DropPrimaryKeyOnUuidColumnOfCeActivityTableTest.class, "schema.sql"); + + private final DropPrimaryKeyOnUuidColumnOfCeActivityTable underTest = new DropPrimaryKeyOnUuidColumnOfCeActivityTable( + db.database(), + new DropPrimaryKeySqlGenerator(db.database(), new DbPrimaryKeyConstraintFinder(db.database()))); + + @Test + public void migration_should_drop_PK_on_ce_activity() throws SQLException { + db.assertPrimaryKey("ce_activity", "pk_ce_activity", "uuid"); + underTest.execute(); + db.assertNoPrimaryKey("ce_activity"); + } + + @Test + public void migration_should_be_reentrant() throws SQLException { + db.assertPrimaryKey("ce_activity", "pk_ce_activity", "uuid"); + underTest.execute(); + // re-entrant + underTest.execute(); + db.assertNoPrimaryKey("ce_activity"); + } +} diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/DefaultOrganizationUuidProviderImplTest/internal_properties.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/DefaultOrganizationUuidProviderImplTest/internal_properties.sql deleted file mode 100644 index e540040ec14..00000000000 --- a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/DefaultOrganizationUuidProviderImplTest/internal_properties.sql +++ /dev/null @@ -1,8 +0,0 @@ -CREATE TABLE "INTERNAL_PROPERTIES" ( - "KEE" VARCHAR(50) NOT NULL PRIMARY KEY, - "IS_EMPTY" BOOLEAN NOT NULL, - "TEXT_VALUE" VARCHAR(4000), - "CLOB_VALUE" CLOB, - "CREATED_AT" BIGINT -); -CREATE UNIQUE INDEX "UNIQ_INTERNAL_PROPERTIES" ON "INTERNAL_PROPERTIES" ("KEE"); diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v90/AddPrimaryKeyOnUuidColumnOfCeActivityTableTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v90/AddPrimaryKeyOnUuidColumnOfCeActivityTableTest/schema.sql new file mode 100644 index 00000000000..9c0c33ab47d --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v90/AddPrimaryKeyOnUuidColumnOfCeActivityTableTest/schema.sql @@ -0,0 +1,30 @@ +CREATE TABLE "CE_ACTIVITY"( + "UUID" VARCHAR(40) NOT NULL, + "TASK_TYPE" VARCHAR(15) NOT NULL, + "MAIN_COMPONENT_UUID" VARCHAR(40), + "COMPONENT_UUID" VARCHAR(40), + "STATUS" VARCHAR(15) NOT NULL, + "MAIN_IS_LAST" BOOLEAN NOT NULL, + "MAIN_IS_LAST_KEY" VARCHAR(55) NOT NULL, + "IS_LAST" BOOLEAN NOT NULL, + "IS_LAST_KEY" VARCHAR(55) NOT NULL, + "SUBMITTER_UUID" VARCHAR(255), + "SUBMITTED_AT" BIGINT NOT NULL, + "STARTED_AT" BIGINT, + "EXECUTED_AT" BIGINT, + "EXECUTION_COUNT" INTEGER NOT NULL, + "EXECUTION_TIME_MS" BIGINT, + "ANALYSIS_UUID" VARCHAR(50), + "ERROR_MESSAGE" VARCHAR(1000), + "ERROR_STACKTRACE" CLOB, + "ERROR_TYPE" VARCHAR(20), + "WORKER_UUID" VARCHAR(40), + "CREATED_AT" BIGINT NOT NULL, + "UPDATED_AT" BIGINT NOT NULL +); +CREATE INDEX "CE_ACTIVITY_COMPONENT" ON "CE_ACTIVITY"("COMPONENT_UUID"); +CREATE INDEX "CE_ACTIVITY_ISLAST" ON "CE_ACTIVITY"("IS_LAST", "STATUS"); +CREATE INDEX "CE_ACTIVITY_ISLAST_KEY" ON "CE_ACTIVITY"("IS_LAST_KEY"); +CREATE INDEX "CE_ACTIVITY_MAIN_COMPONENT" ON "CE_ACTIVITY"("MAIN_COMPONENT_UUID"); +CREATE INDEX "CE_ACTIVITY_MAIN_ISLAST" ON "CE_ACTIVITY"("MAIN_IS_LAST", "STATUS"); +CREATE INDEX "CE_ACTIVITY_MAIN_ISLAST_KEY" ON "CE_ACTIVITY"("MAIN_IS_LAST_KEY"); diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v90/DropCeActivityUuidIndexTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v90/DropCeActivityUuidIndexTest/schema.sql new file mode 100644 index 00000000000..b315ad3644e --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v90/DropCeActivityUuidIndexTest/schema.sql @@ -0,0 +1,32 @@ +CREATE TABLE "CE_ACTIVITY"( + "UUID" VARCHAR(40) NOT NULL, + "TASK_TYPE" VARCHAR(15) NOT NULL, + "MAIN_COMPONENT_UUID" VARCHAR(40), + "COMPONENT_UUID" VARCHAR(40), + "STATUS" VARCHAR(15) NOT NULL, + "MAIN_IS_LAST" BOOLEAN NOT NULL, + "MAIN_IS_LAST_KEY" VARCHAR(55) NOT NULL, + "IS_LAST" BOOLEAN NOT NULL, + "IS_LAST_KEY" VARCHAR(55) NOT NULL, + "SUBMITTER_UUID" VARCHAR(255), + "SUBMITTED_AT" BIGINT NOT NULL, + "STARTED_AT" BIGINT, + "EXECUTED_AT" BIGINT, + "EXECUTION_COUNT" INTEGER NOT NULL, + "EXECUTION_TIME_MS" BIGINT, + "ANALYSIS_UUID" VARCHAR(50), + "ERROR_MESSAGE" VARCHAR(1000), + "ERROR_STACKTRACE" CLOB, + "ERROR_TYPE" VARCHAR(20), + "WORKER_UUID" VARCHAR(40), + "CREATED_AT" BIGINT NOT NULL, + "UPDATED_AT" BIGINT NOT NULL +); +ALTER TABLE "CE_ACTIVITY" ADD CONSTRAINT "PK_CE_ACTIVITY" PRIMARY KEY("UUID"); +CREATE INDEX "CE_ACTIVITY_COMPONENT" ON "CE_ACTIVITY"("COMPONENT_UUID"); +CREATE INDEX "CE_ACTIVITY_ISLAST" ON "CE_ACTIVITY"("IS_LAST", "STATUS"); +CREATE INDEX "CE_ACTIVITY_ISLAST_KEY" ON "CE_ACTIVITY"("IS_LAST_KEY"); +CREATE INDEX "CE_ACTIVITY_MAIN_COMPONENT" ON "CE_ACTIVITY"("MAIN_COMPONENT_UUID"); +CREATE INDEX "CE_ACTIVITY_MAIN_ISLAST" ON "CE_ACTIVITY"("MAIN_IS_LAST", "STATUS"); +CREATE INDEX "CE_ACTIVITY_MAIN_ISLAST_KEY" ON "CE_ACTIVITY"("MAIN_IS_LAST_KEY"); +CREATE UNIQUE INDEX "CE_ACTIVITY_UUID" ON "CE_ACTIVITY"("UUID"); diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v90/DropPrimaryKeyOnUuidColumnOfCeActivityTableTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v90/DropPrimaryKeyOnUuidColumnOfCeActivityTableTest/schema.sql new file mode 100644 index 00000000000..aea312e76ba --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v90/DropPrimaryKeyOnUuidColumnOfCeActivityTableTest/schema.sql @@ -0,0 +1,31 @@ +CREATE TABLE "CE_ACTIVITY"( + "UUID" VARCHAR(40) NOT NULL, + "TASK_TYPE" VARCHAR(15) NOT NULL, + "MAIN_COMPONENT_UUID" VARCHAR(40), + "COMPONENT_UUID" VARCHAR(40), + "STATUS" VARCHAR(15) NOT NULL, + "MAIN_IS_LAST" BOOLEAN NOT NULL, + "MAIN_IS_LAST_KEY" VARCHAR(55) NOT NULL, + "IS_LAST" BOOLEAN NOT NULL, + "IS_LAST_KEY" VARCHAR(55) NOT NULL, + "SUBMITTER_UUID" VARCHAR(255), + "SUBMITTED_AT" BIGINT NOT NULL, + "STARTED_AT" BIGINT, + "EXECUTED_AT" BIGINT, + "EXECUTION_COUNT" INTEGER NOT NULL, + "EXECUTION_TIME_MS" BIGINT, + "ANALYSIS_UUID" VARCHAR(50), + "ERROR_MESSAGE" VARCHAR(1000), + "ERROR_STACKTRACE" CLOB, + "ERROR_TYPE" VARCHAR(20), + "WORKER_UUID" VARCHAR(40), + "CREATED_AT" BIGINT NOT NULL, + "UPDATED_AT" BIGINT NOT NULL +); +ALTER TABLE "CE_ACTIVITY" ADD CONSTRAINT "PK_CE_ACTIVITY" PRIMARY KEY("UUID"); +CREATE INDEX "CE_ACTIVITY_COMPONENT" ON "CE_ACTIVITY"("COMPONENT_UUID"); +CREATE INDEX "CE_ACTIVITY_ISLAST" ON "CE_ACTIVITY"("IS_LAST", "STATUS"); +CREATE INDEX "CE_ACTIVITY_ISLAST_KEY" ON "CE_ACTIVITY"("IS_LAST_KEY"); +CREATE INDEX "CE_ACTIVITY_MAIN_COMPONENT" ON "CE_ACTIVITY"("MAIN_COMPONENT_UUID"); +CREATE INDEX "CE_ACTIVITY_MAIN_ISLAST" ON "CE_ACTIVITY"("MAIN_IS_LAST", "STATUS"); +CREATE INDEX "CE_ACTIVITY_MAIN_ISLAST_KEY" ON "CE_ACTIVITY"("MAIN_IS_LAST_KEY"); -- 2.39.5