From 25815e935522f4e7c7826d9617ae0a381998f88b Mon Sep 17 00:00:00 2001 From: Jacek Date: Thu, 17 Jun 2021 12:25:47 +0200 Subject: [PATCH] SONAR-14792 Fix issue on Oracle DB by recreating PK for 'issues' table --- server/sonar-db-dao/src/schema/schema-sq.ddl | 1 - .../version/v00/CreateInitialSchema.java | 1 - ...AddPrimaryKeyOnKeeColumnOfIssuesTable.java | 39 ++++++++++++++ .../db/migration/version/v90/DbVersion90.java | 5 +- .../version/v90/DropIssuesKeeIndex.java | 32 +++++++++++ ...ropPrimaryKeyOnKeeColumnOfIssuesTable.java | 40 ++++++++++++++ ...rimaryKeyOnKeeColumnOfIssuesTableTest.java | 39 ++++++++++++++ .../version/v90/DbVersion90Test.java | 2 +- .../version/v90/DropIssuesKeeIndexTest.java | 48 +++++++++++++++++ ...rimaryKeyOnKeeColumnOfIssuesTableTest.java | 53 +++++++++++++++++++ .../schema.sql | 36 +++++++++++++ .../v90/DropIssuesKeeIndexTest/schema.sql | 38 +++++++++++++ .../schema.sql | 37 +++++++++++++ 13 files changed, 367 insertions(+), 4 deletions(-) create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v90/AddPrimaryKeyOnKeeColumnOfIssuesTable.java create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v90/DropIssuesKeeIndex.java create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v90/DropPrimaryKeyOnKeeColumnOfIssuesTable.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v90/AddPrimaryKeyOnKeeColumnOfIssuesTableTest.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v90/DropIssuesKeeIndexTest.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v90/DropPrimaryKeyOnKeeColumnOfIssuesTableTest.java create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v90/AddPrimaryKeyOnKeeColumnOfIssuesTableTest/schema.sql create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v90/DropIssuesKeeIndexTest/schema.sql create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v90/DropPrimaryKeyOnKeeColumnOfIssuesTableTest/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 6e984da7b07..a108c6ec266 100644 --- a/server/sonar-db-dao/src/schema/schema-sq.ddl +++ b/server/sonar-db-dao/src/schema/schema-sq.ddl @@ -420,7 +420,6 @@ ALTER TABLE "ISSUES" ADD CONSTRAINT "PK_ISSUES" PRIMARY KEY("KEE"); CREATE INDEX "ISSUES_ASSIGNEE" ON "ISSUES"("ASSIGNEE"); CREATE INDEX "ISSUES_COMPONENT_UUID" ON "ISSUES"("COMPONENT_UUID"); CREATE INDEX "ISSUES_CREATION_DATE" ON "ISSUES"("ISSUE_CREATION_DATE"); -CREATE UNIQUE INDEX "ISSUES_KEE" ON "ISSUES"("KEE"); CREATE INDEX "ISSUES_PROJECT_UUID" ON "ISSUES"("PROJECT_UUID"); CREATE INDEX "ISSUES_RESOLUTION" ON "ISSUES"("RESOLUTION"); CREATE INDEX "ISSUES_UPDATED_AT" ON "ISSUES"("UPDATED_AT"); 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 124d2baf996..e9e4ecdf4ec 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 @@ -763,7 +763,6 @@ public class CreateInitialSchema extends DdlChange { addIndex(context, tableName, "issues_assignee", false, assigneeCol); addIndex(context, tableName, "issues_component_uuid", false, componentUuidCol); addIndex(context, tableName, "issues_creation_date", false, issueCreationDateCol); - addIndex(context, tableName, "issues_kee", true, keeCol); addIndex(context, tableName, "issues_project_uuid", false, projectUuidCol); addIndex(context, tableName, "issues_resolution", false, resolutionCol); addIndex(context, tableName, "issues_updated_at", false, updatedAtCol); diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v90/AddPrimaryKeyOnKeeColumnOfIssuesTable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v90/AddPrimaryKeyOnKeeColumnOfIssuesTable.java new file mode 100644 index 00000000000..200097fd282 --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v90/AddPrimaryKeyOnKeeColumnOfIssuesTable.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 AddPrimaryKeyOnKeeColumnOfIssuesTable extends DdlChange { + private static final String TABLE_NAME = "issues"; + + public AddPrimaryKeyOnKeeColumnOfIssuesTable(Database db) { + super(db); + } + + @Override + public void execute(Context context) throws SQLException { + context.execute(new AddPrimaryKeyBuilder(TABLE_NAME, "kee").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 e97973e15a3..e66843adb73 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 @@ -31,6 +31,9 @@ public class DbVersion90 implements DbVersion { .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); + .add(5006, "Recreate PK on 'uuid' for 'events' table", AddPrimaryKeyOnUuidColumnOfEventsTable.class) + .add(5007, "Drop PK on 'kee' for 'issues' table", DropPrimaryKeyOnKeeColumnOfIssuesTable.class) + .add(5008, "Drop 'issues_kee' index", DropIssuesKeeIndex.class) + .add(5009, "Recreate PK on 'kee' for 'issues' table", AddPrimaryKeyOnKeeColumnOfIssuesTable.class); } } diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v90/DropIssuesKeeIndex.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v90/DropIssuesKeeIndex.java new file mode 100644 index 00000000000..3a6df0518c3 --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v90/DropIssuesKeeIndex.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 DropIssuesKeeIndex extends DropIndexChange { + private static final String INDEX_NAME = "issues_kee"; + private static final String TABLE_NAME = "issues"; + + public DropIssuesKeeIndex(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/DropPrimaryKeyOnKeeColumnOfIssuesTable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v90/DropPrimaryKeyOnKeeColumnOfIssuesTable.java new file mode 100644 index 00000000000..f53b1f0193c --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v90/DropPrimaryKeyOnKeeColumnOfIssuesTable.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 DropPrimaryKeyOnKeeColumnOfIssuesTable extends DdlChange { + private static final String TABLE_NAME = "issues"; + private final DropPrimaryKeySqlGenerator dropPrimaryKeySqlGenerator; + + public DropPrimaryKeyOnKeeColumnOfIssuesTable(Database db, DropPrimaryKeySqlGenerator dropPrimaryKeySqlGenerator) { + super(db); + this.dropPrimaryKeySqlGenerator = dropPrimaryKeySqlGenerator; + } + + @Override + public void execute(Context context) throws SQLException { + context.execute(dropPrimaryKeySqlGenerator.generate(TABLE_NAME, "kee", false)); + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v90/AddPrimaryKeyOnKeeColumnOfIssuesTableTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v90/AddPrimaryKeyOnKeeColumnOfIssuesTableTest.java new file mode 100644 index 00000000000..c1455f71e7d --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v90/AddPrimaryKeyOnKeeColumnOfIssuesTableTest.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.junit.Rule; +import org.junit.Test; +import org.sonar.db.CoreDbTester; + +public class AddPrimaryKeyOnKeeColumnOfIssuesTableTest { + @Rule + public final CoreDbTester db = CoreDbTester.createForSchema(AddPrimaryKeyOnKeeColumnOfIssuesTableTest.class, "schema.sql"); + + private final AddPrimaryKeyOnKeeColumnOfIssuesTable underTest = new AddPrimaryKeyOnKeeColumnOfIssuesTable(db.database()); + + @Test + public void migration_should_drop_PK_on_ce_activity() throws SQLException { + db.assertNoPrimaryKey("issues"); + underTest.execute(); + db.assertPrimaryKey("issues", "pk_issues", "kee"); + } +} 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 5b57d83e61d..e261935bbdb 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, 6); + verifyMigrationCount(underTest, 9); } } diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v90/DropIssuesKeeIndexTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v90/DropIssuesKeeIndexTest.java new file mode 100644 index 00000000000..db5ec04ec02 --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v90/DropIssuesKeeIndexTest.java @@ -0,0 +1,48 @@ +/* + * 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 DropIssuesKeeIndexTest { + @Rule + public final CoreDbTester db = CoreDbTester.createForSchema(DropIssuesKeeIndexTest.class, "schema.sql"); + + private final DropIssuesKeeIndex underTest = new DropIssuesKeeIndex(db.database()); + + @Test + public void migration_should_drop_unique_index_on_issues() throws SQLException { + db.assertUniqueIndex("issues", "issues_kee", "kee"); + underTest.execute(); + db.assertIndexDoesNotExist("issues", "issues_kee"); + } + + @Test + public void migration_should_be_reentrant() throws SQLException { + db.assertUniqueIndex("issues", "issues_kee", "kee"); + underTest.execute(); + // re-entrant + underTest.execute(); + db.assertIndexDoesNotExist("issues", "issues_kee"); + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v90/DropPrimaryKeyOnKeeColumnOfIssuesTableTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v90/DropPrimaryKeyOnKeeColumnOfIssuesTableTest.java new file mode 100644 index 00000000000..e91c70663ed --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v90/DropPrimaryKeyOnKeeColumnOfIssuesTableTest.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 DropPrimaryKeyOnKeeColumnOfIssuesTableTest { + + @Rule + public final CoreDbTester db = CoreDbTester.createForSchema(DropPrimaryKeyOnKeeColumnOfIssuesTableTest.class, "schema.sql"); + + private final DropPrimaryKeyOnKeeColumnOfIssuesTable underTest = new DropPrimaryKeyOnKeeColumnOfIssuesTable( + db.database(), + new DropPrimaryKeySqlGenerator(db.database(), new DbPrimaryKeyConstraintFinder(db.database()))); + + @Test + public void migration_should_drop_PK_on_ce_activity() throws SQLException { + db.assertPrimaryKey("issues", "pk_issues", "kee"); + underTest.execute(); + db.assertNoPrimaryKey("issues"); + } + + @Test + public void migration_should_be_reentrant() throws SQLException { + db.assertPrimaryKey("issues", "pk_issues", "kee"); + underTest.execute(); + // re-entrant + underTest.execute(); + db.assertNoPrimaryKey("issues"); + } +} diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v90/AddPrimaryKeyOnKeeColumnOfIssuesTableTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v90/AddPrimaryKeyOnKeeColumnOfIssuesTableTest/schema.sql new file mode 100644 index 00000000000..ed89fdd9c21 --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v90/AddPrimaryKeyOnKeeColumnOfIssuesTableTest/schema.sql @@ -0,0 +1,36 @@ +CREATE TABLE "ISSUES"( + "KEE" VARCHAR(50) NOT NULL, + "RULE_UUID" VARCHAR(40), + "SEVERITY" VARCHAR(10), + "MANUAL_SEVERITY" BOOLEAN NOT NULL, + "MESSAGE" VARCHAR(4000), + "LINE" INTEGER, + "GAP" DOUBLE, + "STATUS" VARCHAR(20), + "RESOLUTION" VARCHAR(20), + "CHECKSUM" VARCHAR(1000), + "REPORTER" VARCHAR(255), + "ASSIGNEE" VARCHAR(255), + "AUTHOR_LOGIN" VARCHAR(255), + "ACTION_PLAN_KEY" VARCHAR(50), + "ISSUE_ATTRIBUTES" VARCHAR(4000), + "EFFORT" INTEGER, + "CREATED_AT" BIGINT, + "UPDATED_AT" BIGINT, + "ISSUE_CREATION_DATE" BIGINT, + "ISSUE_UPDATE_DATE" BIGINT, + "ISSUE_CLOSE_DATE" BIGINT, + "TAGS" VARCHAR(4000), + "COMPONENT_UUID" VARCHAR(50), + "PROJECT_UUID" VARCHAR(50), + "LOCATIONS" BLOB, + "ISSUE_TYPE" TINYINT, + "FROM_HOTSPOT" BOOLEAN +); +CREATE INDEX "ISSUES_ASSIGNEE" ON "ISSUES"("ASSIGNEE"); +CREATE INDEX "ISSUES_COMPONENT_UUID" ON "ISSUES"("COMPONENT_UUID"); +CREATE INDEX "ISSUES_CREATION_DATE" ON "ISSUES"("ISSUE_CREATION_DATE"); +CREATE INDEX "ISSUES_PROJECT_UUID" ON "ISSUES"("PROJECT_UUID"); +CREATE INDEX "ISSUES_RESOLUTION" ON "ISSUES"("RESOLUTION"); +CREATE INDEX "ISSUES_UPDATED_AT" ON "ISSUES"("UPDATED_AT"); +CREATE INDEX "ISSUES_RULE_UUID" ON "ISSUES"("RULE_UUID"); diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v90/DropIssuesKeeIndexTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v90/DropIssuesKeeIndexTest/schema.sql new file mode 100644 index 00000000000..dd444fe5d80 --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v90/DropIssuesKeeIndexTest/schema.sql @@ -0,0 +1,38 @@ +CREATE TABLE "ISSUES"( + "KEE" VARCHAR(50) NOT NULL, + "RULE_UUID" VARCHAR(40), + "SEVERITY" VARCHAR(10), + "MANUAL_SEVERITY" BOOLEAN NOT NULL, + "MESSAGE" VARCHAR(4000), + "LINE" INTEGER, + "GAP" DOUBLE, + "STATUS" VARCHAR(20), + "RESOLUTION" VARCHAR(20), + "CHECKSUM" VARCHAR(1000), + "REPORTER" VARCHAR(255), + "ASSIGNEE" VARCHAR(255), + "AUTHOR_LOGIN" VARCHAR(255), + "ACTION_PLAN_KEY" VARCHAR(50), + "ISSUE_ATTRIBUTES" VARCHAR(4000), + "EFFORT" INTEGER, + "CREATED_AT" BIGINT, + "UPDATED_AT" BIGINT, + "ISSUE_CREATION_DATE" BIGINT, + "ISSUE_UPDATE_DATE" BIGINT, + "ISSUE_CLOSE_DATE" BIGINT, + "TAGS" VARCHAR(4000), + "COMPONENT_UUID" VARCHAR(50), + "PROJECT_UUID" VARCHAR(50), + "LOCATIONS" BLOB, + "ISSUE_TYPE" TINYINT, + "FROM_HOTSPOT" BOOLEAN +); +ALTER TABLE "ISSUES" ADD CONSTRAINT "PK_ISSUES" PRIMARY KEY("KEE"); +CREATE INDEX "ISSUES_ASSIGNEE" ON "ISSUES"("ASSIGNEE"); +CREATE INDEX "ISSUES_COMPONENT_UUID" ON "ISSUES"("COMPONENT_UUID"); +CREATE INDEX "ISSUES_CREATION_DATE" ON "ISSUES"("ISSUE_CREATION_DATE"); +CREATE UNIQUE INDEX "ISSUES_KEE" ON "ISSUES"("KEE"); +CREATE INDEX "ISSUES_PROJECT_UUID" ON "ISSUES"("PROJECT_UUID"); +CREATE INDEX "ISSUES_RESOLUTION" ON "ISSUES"("RESOLUTION"); +CREATE INDEX "ISSUES_UPDATED_AT" ON "ISSUES"("UPDATED_AT"); +CREATE INDEX "ISSUES_RULE_UUID" ON "ISSUES"("RULE_UUID"); diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v90/DropPrimaryKeyOnKeeColumnOfIssuesTableTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v90/DropPrimaryKeyOnKeeColumnOfIssuesTableTest/schema.sql new file mode 100644 index 00000000000..367c007159c --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v90/DropPrimaryKeyOnKeeColumnOfIssuesTableTest/schema.sql @@ -0,0 +1,37 @@ +CREATE TABLE "ISSUES"( + "KEE" VARCHAR(50) NOT NULL, + "RULE_UUID" VARCHAR(40), + "SEVERITY" VARCHAR(10), + "MANUAL_SEVERITY" BOOLEAN NOT NULL, + "MESSAGE" VARCHAR(4000), + "LINE" INTEGER, + "GAP" DOUBLE, + "STATUS" VARCHAR(20), + "RESOLUTION" VARCHAR(20), + "CHECKSUM" VARCHAR(1000), + "REPORTER" VARCHAR(255), + "ASSIGNEE" VARCHAR(255), + "AUTHOR_LOGIN" VARCHAR(255), + "ACTION_PLAN_KEY" VARCHAR(50), + "ISSUE_ATTRIBUTES" VARCHAR(4000), + "EFFORT" INTEGER, + "CREATED_AT" BIGINT, + "UPDATED_AT" BIGINT, + "ISSUE_CREATION_DATE" BIGINT, + "ISSUE_UPDATE_DATE" BIGINT, + "ISSUE_CLOSE_DATE" BIGINT, + "TAGS" VARCHAR(4000), + "COMPONENT_UUID" VARCHAR(50), + "PROJECT_UUID" VARCHAR(50), + "LOCATIONS" BLOB, + "ISSUE_TYPE" TINYINT, + "FROM_HOTSPOT" BOOLEAN +); +ALTER TABLE "ISSUES" ADD CONSTRAINT "PK_ISSUES" PRIMARY KEY("KEE"); +CREATE INDEX "ISSUES_ASSIGNEE" ON "ISSUES"("ASSIGNEE"); +CREATE INDEX "ISSUES_COMPONENT_UUID" ON "ISSUES"("COMPONENT_UUID"); +CREATE INDEX "ISSUES_CREATION_DATE" ON "ISSUES"("ISSUE_CREATION_DATE"); +CREATE INDEX "ISSUES_PROJECT_UUID" ON "ISSUES"("PROJECT_UUID"); +CREATE INDEX "ISSUES_RESOLUTION" ON "ISSUES"("RESOLUTION"); +CREATE INDEX "ISSUES_UPDATED_AT" ON "ISSUES"("UPDATED_AT"); +CREATE INDEX "ISSUES_RULE_UUID" ON "ISSUES"("RULE_UUID"); -- 2.39.5