From 16c0c807710622e39deae10cb0a4b880af8e9ef3 Mon Sep 17 00:00:00 2001 From: =?utf8?q?S=C3=A9bastien=20Lesaint?= Date: Mon, 29 May 2017 11:32:11 +0200 Subject: [PATCH] SONAR-9334 make EVENTS.COMPONENT_UUID not nullable and clean orphans --- .../org/sonar/db/version/schema-h2.ddl | 2 +- .../org/sonar/db/purge/PurgeCommandsTest.java | 2 +- .../shouldDeleteResource.xml | 4 +- .../db/migration/version/v65/DbVersion65.java | 6 +- .../v65/DropIndexEventsComponentUuid.java | 40 ++++++ .../MakeEventsComponentUuidNotNullable.java | 46 +++++++ .../v65/PopulateEventsComponentUuid.java | 83 +++++++++++ .../v65/RecreateIndexEventsComponentUuid.java | 49 +++++++ .../version/v65/DbVersion65Test.java | 2 +- .../v65/DropIndexEventsComponentUuidTest.java | 45 ++++++ ...akeEventsComponentUuidNotNullableTest.java | 69 ++++++++++ .../v65/PopulateEventsComponentUuidTest.java | 129 ++++++++++++++++++ .../RecreateIndexEventsComponentUuidTest.java | 44 ++++++ .../events.sql | 15 ++ .../events.sql | 15 ++ .../events_and_snapshots.sql | 44 ++++++ .../events.sql | 14 ++ 17 files changed, 603 insertions(+), 6 deletions(-) create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v65/DropIndexEventsComponentUuid.java create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v65/MakeEventsComponentUuidNotNullable.java create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v65/PopulateEventsComponentUuid.java create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v65/RecreateIndexEventsComponentUuid.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/DropIndexEventsComponentUuidTest.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/MakeEventsComponentUuidNotNullableTest.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/PopulateEventsComponentUuidTest.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/RecreateIndexEventsComponentUuidTest.java create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v65/DropIndexEventsComponentUuidTest/events.sql create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v65/MakeEventsComponentUuidNotNullableTest/events.sql create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v65/PopulateEventsComponentUuidTest/events_and_snapshots.sql create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v65/RecreateIndexEventsComponentUuidTest/events.sql diff --git a/server/sonar-db-core/src/main/resources/org/sonar/db/version/schema-h2.ddl b/server/sonar-db-core/src/main/resources/org/sonar/db/version/schema-h2.ddl index 6ed8be21276..2e487af0391 100644 --- a/server/sonar-db-core/src/main/resources/org/sonar/db/version/schema-h2.ddl +++ b/server/sonar-db-core/src/main/resources/org/sonar/db/version/schema-h2.ddl @@ -172,7 +172,7 @@ CREATE TABLE "EVENTS" ( "UUID" VARCHAR(40) NOT NULL, "NAME" VARCHAR(400), "ANALYSIS_UUID" VARCHAR(50) NOT NULL, - "COMPONENT_UUID" VARCHAR(50), + "COMPONENT_UUID" VARCHAR(50) NOT NULL, "CATEGORY" VARCHAR(50), "EVENT_DATE" BIGINT NOT NULL, "CREATED_AT" BIGINT NOT NULL, diff --git a/server/sonar-db-dao/src/test/java/org/sonar/db/purge/PurgeCommandsTest.java b/server/sonar-db-dao/src/test/java/org/sonar/db/purge/PurgeCommandsTest.java index c2586d6c7b3..2a33ea8c06d 100644 --- a/server/sonar-db-dao/src/test/java/org/sonar/db/purge/PurgeCommandsTest.java +++ b/server/sonar-db-dao/src/test/java/org/sonar/db/purge/PurgeCommandsTest.java @@ -90,7 +90,7 @@ public class PurgeCommandsTest { assertThat(dbTester.countRowsOfTable("projects")).isZero(); assertThat(dbTester.countRowsOfTable("snapshots")).isEqualTo(1); - assertThat(dbTester.countRowsOfTable("events")).isEqualTo(2); + assertThat(dbTester.countRowsOfTable("events")).isZero(); assertThat(dbTester.countRowsOfTable("issues")).isZero(); assertThat(dbTester.countRowsOfTable("issue_changes")).isZero(); } diff --git a/server/sonar-db-dao/src/test/resources/org/sonar/db/purge/PurgeCommandsTest/shouldDeleteResource.xml b/server/sonar-db-dao/src/test/resources/org/sonar/db/purge/PurgeCommandsTest/shouldDeleteResource.xml index 1a269703e3c..2d521a1fded 100644 --- a/server/sonar-db-dao/src/test/resources/org/sonar/db/purge/PurgeCommandsTest/shouldDeleteResource.xml +++ b/server/sonar-db-dao/src/test/resources/org/sonar/db/purge/PurgeCommandsTest/shouldDeleteResource.xml @@ -58,7 +58,7 @@ s.uuid"); + massUpdate.update("update events set component_uuid = ? where id = ?"); + massUpdate.rowPluralName("events without component_uuid"); + massUpdate.execute(PopulateEventsComponentUuid::handlePopulate); + } + + private static boolean handlePopulate(Select.Row row, SqlStatement update) throws SQLException { + long id = row.getLong(1); + String projectUuid = row.getString(2); + + update.setString(1, projectUuid); + update.setLong(2, id); + + return true; + } + + private static void deleteOrphans(Context context) throws SQLException { + MassUpdate massUpdate = context.prepareMassUpdate(); + massUpdate.select("select id from events e where" + + " e.component_uuid is null" + + " or not exists (select id from snapshots s where s.uuid = e.analysis_uuid)"); + massUpdate.update("delete from events where id = ?"); + massUpdate.rowPluralName("delete orphan events"); + massUpdate.execute(PopulateEventsComponentUuid::handleDelete); + } + + private static boolean handleDelete(Select.Row row, SqlStatement update) throws SQLException { + long id = row.getLong(1); + + update.setLong(1, id); + + return true; + } +} diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v65/RecreateIndexEventsComponentUuid.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v65/RecreateIndexEventsComponentUuid.java new file mode 100644 index 00000000000..71d91b84a3f --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v65/RecreateIndexEventsComponentUuid.java @@ -0,0 +1,49 @@ +/* + * SonarQube + * Copyright (C) 2009-2017 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.v65; + +import java.sql.SQLException; +import org.sonar.db.Database; +import org.sonar.server.platform.db.migration.def.VarcharColumnDef; +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.def.VarcharColumnDef.newVarcharColumnDefBuilder; + +public class RecreateIndexEventsComponentUuid extends DdlChange { + + public RecreateIndexEventsComponentUuid(Database db) { + super(db); + } + + @Override + public void execute(Context context) throws SQLException { + context.execute(new CreateIndexBuilder(getDialect()) + .setTable("events") + .setName("events_component_uuid") + .setUnique(false) + .addColumn(newVarcharColumnDefBuilder() + .setColumnName("component_uuid") + .setLimit(VarcharColumnDef.UUID_VARCHAR_SIZE) + .setIsNullable(false) + .build()) + .build()); + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/DbVersion65Test.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/DbVersion65Test.java index fa2bfdb7dcb..1ff63d12cb9 100644 --- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/DbVersion65Test.java +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/DbVersion65Test.java @@ -35,6 +35,6 @@ public class DbVersion65Test { @Test public void verify_migration_count() { - verifyMigrationCount(underTest, 3); + verifyMigrationCount(underTest, 7); } } diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/DropIndexEventsComponentUuidTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/DropIndexEventsComponentUuidTest.java new file mode 100644 index 00000000000..dda3a877137 --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/DropIndexEventsComponentUuidTest.java @@ -0,0 +1,45 @@ +/* + * SonarQube + * Copyright (C) 2009-2017 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.v65; + +import java.sql.SQLException; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.db.CoreDbTester; + +public class DropIndexEventsComponentUuidTest { + private static final String TABLE_EVENTS = "events"; + private static final String INDEX_EVENTS_COMPONENT_UUID = "events_component_uuid"; + + @Rule + public CoreDbTester db = CoreDbTester.createForSchema(DropIndexEventsComponentUuidTest.class, "events.sql"); + + private DropIndexEventsComponentUuid underTest = new DropIndexEventsComponentUuid(db.database()); + + @Test + public void execute_drops_index_EVENTS_COMPONENT_UUID() throws SQLException { + db.assertIndex(TABLE_EVENTS, INDEX_EVENTS_COMPONENT_UUID, "component_uuid"); + + underTest.execute(); + + db.assertIndexDoesNotExist(TABLE_EVENTS, INDEX_EVENTS_COMPONENT_UUID); + } +} + diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/MakeEventsComponentUuidNotNullableTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/MakeEventsComponentUuidNotNullableTest.java new file mode 100644 index 00000000000..d762665a1ee --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/MakeEventsComponentUuidNotNullableTest.java @@ -0,0 +1,69 @@ +/* + * SonarQube + * Copyright (C) 2009-2017 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.v65; + +import java.sql.SQLException; +import java.sql.Types; +import java.util.Random; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.db.CoreDbTester; + +import static org.apache.commons.lang.RandomStringUtils.randomAlphabetic; + +public class MakeEventsComponentUuidNotNullableTest { + @Rule + public CoreDbTester db = CoreDbTester.createForSchema(MakeEventsComponentUuidNotNullableTest.class, "events.sql"); + + private final Random random = new Random(); + private MakeEventsComponentUuidNotNullable underTest = new MakeEventsComponentUuidNotNullable(db.database()); + + @Test + public void execute_makes_column_component_uuid_not_nullable_on_empty_table() throws SQLException { + underTest.execute(); + + verifyColumn(); + } + + @Test + public void execute_makes_column_component_uuid_not_nullable_on_populated_table() throws SQLException { + insertEvent(); + insertEvent(); + insertEvent(); + + underTest.execute(); + + verifyColumn(); + } + + private void verifyColumn() { + db.assertColumnDefinition("events", "component_uuid", Types.VARCHAR, 50, false); + } + + private void insertEvent() { + db.executeInsert( + "events", + "UUID", randomAlphabetic(5), + "ANALYSIS_UUID", randomAlphabetic(5), + "COMPONENT_UUID", randomAlphabetic(5), + "EVENT_DATE", random.nextInt(), + "CREATED_AT", random.nextInt()); + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/PopulateEventsComponentUuidTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/PopulateEventsComponentUuidTest.java new file mode 100644 index 00000000000..031ca960217 --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/PopulateEventsComponentUuidTest.java @@ -0,0 +1,129 @@ +/* + * SonarQube + * Copyright (C) 2009-2017 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.v65; + +import java.sql.SQLException; +import java.util.Random; +import java.util.stream.Stream; +import javax.annotation.Nullable; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.db.CoreDbTester; + +import static org.apache.commons.lang.RandomStringUtils.randomAlphabetic; +import static org.assertj.core.api.Assertions.assertThat; + +public class PopulateEventsComponentUuidTest { + private static final String TABLE_EVENTS = "events"; + + @Rule + public CoreDbTester db = CoreDbTester.createForSchema(PopulateEventsComponentUuidTest.class, "events_and_snapshots.sql"); + + private final Random random = new Random(); + + private PopulateEventsComponentUuid underTest = new PopulateEventsComponentUuid(db.database()); + + @Test + public void execute_has_no_effect_if_table_is_empty() throws SQLException { + underTest.execute(); + } + + @Test + public void execute_deletes_all_events_if_snapshots_is_empty() throws SQLException { + insertEvent(randomAlphabetic(5), null); + insertEvent(randomAlphabetic(5), randomAlphabetic(10)); + + underTest.execute(); + + assertThat(getAllEventUuids()).isEmpty(); + } + + @Test + public void execute_populates_components_uuid_of_events_which_analysis_exists() throws SQLException { + String componentUuid = "foo"; + String existingAnalysisUuid = insertSnapshot(componentUuid); + String missingAnalysisUuid = randomAlphabetic(5); + String uuid = insertEvent(existingAnalysisUuid, null); + insertEvent(missingAnalysisUuid, null); + + underTest.execute(); + + assertThat(getAllEventUuids()).containsOnly(uuid); + assertThat(getComponentUuidOf(uuid)).isEqualTo(componentUuid); + } + + @Test + public void execute_fixes_inconsistent_component_uuid_of_event_which_analysis_exists() throws SQLException { + String analysisUuid1 = insertSnapshot("foo"); + String analysisUuid2 = insertSnapshot("bar"); + String eventUuid1 = insertEvent(analysisUuid1, "foo"); + String eventUuid2 = insertEvent(analysisUuid2, "moh"); + + underTest.execute(); + + assertThat(getAllEventUuids()).containsOnly(eventUuid1, eventUuid2); + assertThat(getComponentUuidOf(eventUuid1)).isEqualTo("foo"); + assertThat(getComponentUuidOf(eventUuid2)).isEqualTo("bar"); + } + + @Test + public void execute_deletes_events_without_component_uuid_which_analysis_does_not_exist() throws SQLException { + String analysisUuid1 = insertSnapshot("foo"); + String eventUuid1 = insertEvent(analysisUuid1, "foo"); + insertEvent(randomAlphabetic(3), "moh"); + insertEvent(randomAlphabetic(3), null); + + underTest.execute(); + + assertThat(getAllEventUuids()).containsOnly(eventUuid1); + } + + private String insertSnapshot(String componentUuid) { + String uuid = randomAlphabetic(10); + db.executeInsert( + "snapshots", + "UUID", uuid, + "COMPONENT_UUID", componentUuid, + "STATUS", "U", + "ISLAST", String.valueOf(true)); + return uuid; + } + + private String insertEvent(String analysisUuid, @Nullable String componentUuid) { + String uuid = randomAlphabetic(5); + db.executeInsert( + TABLE_EVENTS, + "UUID", uuid, + "ANALYSIS_UUID", analysisUuid, + "COMPONENT_UUID", componentUuid, + "EVENT_DATE", random.nextInt(), + "CREATED_AT", random.nextInt()); + return uuid; + } + + private Stream getAllEventUuids() { + return db.select("select uuid as \"UUID\" from events").stream().map(row -> (String) row.get("UUID")); + } + + private String getComponentUuidOf(String eventUuid) { + return (String) db.selectFirst("select component_uuid as \"COMPONENT_UUID\" from events where uuid = '" + eventUuid + "'") + .get("COMPONENT_UUID"); + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/RecreateIndexEventsComponentUuidTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/RecreateIndexEventsComponentUuidTest.java new file mode 100644 index 00000000000..f294cc7e64e --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/RecreateIndexEventsComponentUuidTest.java @@ -0,0 +1,44 @@ +/* + * SonarQube + * Copyright (C) 2009-2017 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.v65; + +import java.sql.SQLException; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.db.CoreDbTester; + +public class RecreateIndexEventsComponentUuidTest { + private static final String TABLE_EVENTS = "events"; + private static final String INDEX_EVENTS_COMPONENT_UUID = "events_component_uuid"; + + @Rule + public CoreDbTester db = CoreDbTester.createForSchema(RecreateIndexEventsComponentUuidTest.class, "events.sql"); + + private RecreateIndexEventsComponentUuid underTest = new RecreateIndexEventsComponentUuid(db.database()); + + @Test + public void execute_adds_index_EVENTS_COMPONENT_UUID() throws SQLException { + db.assertIndexDoesNotExist(TABLE_EVENTS, INDEX_EVENTS_COMPONENT_UUID); + + underTest.execute(); + + db.assertIndex(TABLE_EVENTS, INDEX_EVENTS_COMPONENT_UUID, "component_uuid"); + } +} diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v65/DropIndexEventsComponentUuidTest/events.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v65/DropIndexEventsComponentUuidTest/events.sql new file mode 100644 index 00000000000..a4d0d1f235d --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v65/DropIndexEventsComponentUuidTest/events.sql @@ -0,0 +1,15 @@ +CREATE TABLE "EVENTS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "UUID" VARCHAR(40) NOT NULL, + "NAME" VARCHAR(400), + "ANALYSIS_UUID" VARCHAR(50) NOT NULL, + "COMPONENT_UUID" VARCHAR(50), + "CATEGORY" VARCHAR(50), + "EVENT_DATE" BIGINT NOT NULL, + "CREATED_AT" BIGINT NOT NULL, + "DESCRIPTION" VARCHAR(4000), + "EVENT_DATA" VARCHAR(4000) +); +CREATE INDEX "EVENTS_ANALYSIS" ON "EVENTS" ("ANALYSIS_UUID"); +CREATE INDEX "EVENTS_COMPONENT_UUID" ON "EVENTS" ("COMPONENT_UUID"); +CREATE UNIQUE INDEX "EVENTS_UUID" ON "EVENTS" ("UUID"); diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v65/MakeEventsComponentUuidNotNullableTest/events.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v65/MakeEventsComponentUuidNotNullableTest/events.sql new file mode 100644 index 00000000000..a4d0d1f235d --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v65/MakeEventsComponentUuidNotNullableTest/events.sql @@ -0,0 +1,15 @@ +CREATE TABLE "EVENTS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "UUID" VARCHAR(40) NOT NULL, + "NAME" VARCHAR(400), + "ANALYSIS_UUID" VARCHAR(50) NOT NULL, + "COMPONENT_UUID" VARCHAR(50), + "CATEGORY" VARCHAR(50), + "EVENT_DATE" BIGINT NOT NULL, + "CREATED_AT" BIGINT NOT NULL, + "DESCRIPTION" VARCHAR(4000), + "EVENT_DATA" VARCHAR(4000) +); +CREATE INDEX "EVENTS_ANALYSIS" ON "EVENTS" ("ANALYSIS_UUID"); +CREATE INDEX "EVENTS_COMPONENT_UUID" ON "EVENTS" ("COMPONENT_UUID"); +CREATE UNIQUE INDEX "EVENTS_UUID" ON "EVENTS" ("UUID"); diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v65/PopulateEventsComponentUuidTest/events_and_snapshots.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v65/PopulateEventsComponentUuidTest/events_and_snapshots.sql new file mode 100644 index 00000000000..1aff12a80e3 --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v65/PopulateEventsComponentUuidTest/events_and_snapshots.sql @@ -0,0 +1,44 @@ +CREATE TABLE "EVENTS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "UUID" VARCHAR(40) NOT NULL, + "NAME" VARCHAR(400), + "ANALYSIS_UUID" VARCHAR(50) NOT NULL, + "COMPONENT_UUID" VARCHAR(50), + "CATEGORY" VARCHAR(50), + "EVENT_DATE" BIGINT NOT NULL, + "CREATED_AT" BIGINT NOT NULL, + "DESCRIPTION" VARCHAR(4000), + "EVENT_DATA" VARCHAR(4000) +); +CREATE INDEX "EVENTS_ANALYSIS" ON "EVENTS" ("ANALYSIS_UUID"); +CREATE INDEX "EVENTS_COMPONENT_UUID" ON "EVENTS" ("COMPONENT_UUID"); +CREATE UNIQUE INDEX "EVENTS_UUID" ON "EVENTS" ("UUID"); + +CREATE TABLE "SNAPSHOTS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "UUID" VARCHAR(50) NOT NULL, + "CREATED_AT" BIGINT, + "BUILD_DATE" BIGINT, + "COMPONENT_UUID" VARCHAR(50) NOT NULL, + "STATUS" VARCHAR(4) NOT NULL DEFAULT 'U', + "PURGE_STATUS" INTEGER, + "ISLAST" BOOLEAN NOT NULL DEFAULT FALSE, + "VERSION" VARCHAR(500), + "PERIOD1_MODE" VARCHAR(100), + "PERIOD1_PARAM" VARCHAR(100), + "PERIOD1_DATE" BIGINT, + "PERIOD2_MODE" VARCHAR(100), + "PERIOD2_PARAM" VARCHAR(100), + "PERIOD2_DATE" BIGINT, + "PERIOD3_MODE" VARCHAR(100), + "PERIOD3_PARAM" VARCHAR(100), + "PERIOD3_DATE" BIGINT, + "PERIOD4_MODE" VARCHAR(100), + "PERIOD4_PARAM" VARCHAR(100), + "PERIOD4_DATE" BIGINT, + "PERIOD5_MODE" VARCHAR(100), + "PERIOD5_PARAM" VARCHAR(100), + "PERIOD5_DATE" BIGINT +); +CREATE INDEX "SNAPSHOT_COMPONENT" ON "SNAPSHOTS" ("COMPONENT_UUID"); +CREATE UNIQUE INDEX "ANALYSES_UUID" ON "SNAPSHOTS" ("UUID"); diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v65/RecreateIndexEventsComponentUuidTest/events.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v65/RecreateIndexEventsComponentUuidTest/events.sql new file mode 100644 index 00000000000..08f78db2d76 --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v65/RecreateIndexEventsComponentUuidTest/events.sql @@ -0,0 +1,14 @@ +CREATE TABLE "EVENTS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "UUID" VARCHAR(40) NOT NULL, + "NAME" VARCHAR(400), + "ANALYSIS_UUID" VARCHAR(50) NOT NULL, + "COMPONENT_UUID" VARCHAR(50), + "CATEGORY" VARCHAR(50), + "EVENT_DATE" BIGINT NOT NULL, + "CREATED_AT" BIGINT NOT NULL, + "DESCRIPTION" VARCHAR(4000), + "EVENT_DATA" VARCHAR(4000) +); +CREATE INDEX "EVENTS_ANALYSIS" ON "EVENTS" ("ANALYSIS_UUID"); +CREATE UNIQUE INDEX "EVENTS_UUID" ON "EVENTS" ("UUID"); -- 2.39.5