From c87a24252f272406260a8e407600ace50885cf90 Mon Sep 17 00:00:00 2001 From: =?utf8?q?S=C3=A9bastien=20Lesaint?= Date: Wed, 1 Feb 2017 15:47:43 +0100 Subject: [PATCH] SONAR-8586 fix wrong query plan by injecting fake data on H2 into table PROJECT_MEASURES --- .../db/migration/AutoDbMigration.java | 30 ++++++++++- .../db/migration/AutoDbMigrationH2Test.java | 54 +++++++++++++++++++ .../db/migration/AutoDbMigrationTest.java | 3 +- .../schema_migrations.sql | 4 ++ 4 files changed, 88 insertions(+), 3 deletions(-) create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/AutoDbMigrationH2Test.java create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/AutoDbMigrationH2Test/schema_migrations.sql diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/AutoDbMigration.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/AutoDbMigration.java index 617d1a4fedb..11bd1b4a5d7 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/AutoDbMigration.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/AutoDbMigration.java @@ -21,6 +21,8 @@ package org.sonar.server.platform.db.migration; import com.google.common.annotations.VisibleForTesting; import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.SQLException; import org.apache.commons.dbutils.DbUtils; import org.apache.ibatis.session.SqlSession; import org.picocontainer.Startable; @@ -66,15 +68,39 @@ public class AutoDbMigration implements Startable { Connection connection = null; try (SqlSession session = dbClient.openSession(false)) { connection = session.getConnection(); - createSchema(connection, dbClient.getDatabase().getDialect().getId()); + createH2Schema(connection, dbClient.getDatabase().getDialect().getId()); } finally { DbUtils.closeQuietly(connection); } } @VisibleForTesting - protected void createSchema(Connection connection, String dialectId) { + protected void createH2Schema(Connection connection, String dialectId) { DdlUtils.createSchema(connection, dialectId, false); + hackFixForProjectMeasureTreeQueries(connection); + } + + /** + * see SONAR-8586 + */ + private static void hackFixForProjectMeasureTreeQueries(Connection connection) { + int metricId = 1; + try (PreparedStatement preparedStatement = connection.prepareStatement("insert into PROJECT_MEASURES (METRIC_ID,COMPONENT_UUID,ANALYSIS_UUID) values (?,?,?);")) { + for (int i = 1; i < 1000; i++) { + preparedStatement.setInt(1, metricId); + preparedStatement.setString(2, "foo_" + i); + preparedStatement.setString(3, "bar_" + i); + preparedStatement.addBatch(); + if (i % 250 == 0) { + preparedStatement.executeBatch(); + connection.commit(); + } + } + preparedStatement.executeBatch(); + connection.commit(); + } catch (SQLException e) { + throw new RuntimeException("Failed to insert fake rows into table PROJECT_MEASURES", e); + } } @Override diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/AutoDbMigrationH2Test.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/AutoDbMigrationH2Test.java new file mode 100644 index 00000000000..6a31a6a2446 --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/AutoDbMigrationH2Test.java @@ -0,0 +1,54 @@ +/* + * SonarQube + * Copyright (C) 2009-2016 SonarSource SA + * mailto:contact 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; + +import org.junit.Rule; +import org.junit.Test; +import org.sonar.api.platform.ServerUpgradeStatus; +import org.sonar.api.utils.System2; +import org.sonar.db.DbClient; +import org.sonar.db.DbTester; +import org.sonar.server.platform.db.migration.engine.MigrationEngine; + +import static org.assertj.core.api.Assertions.assertThat; +import static org.mockito.Mockito.mock; +import static org.mockito.Mockito.verifyNoMoreInteractions; +import static org.mockito.Mockito.when; + +public class AutoDbMigrationH2Test { + @Rule + public DbTester dbTester = DbTester.createForSchema(System2.INSTANCE, AutoDbMigrationH2Test.class, "schema_migrations.sql"); + + private DbClient dbClient = dbTester.getDbClient(); + private ServerUpgradeStatus serverUpgradeStatus = mock(ServerUpgradeStatus.class); + private MigrationEngine migrationEngine = mock(MigrationEngine.class); + + private AutoDbMigration underTest = new AutoDbMigration(serverUpgradeStatus, dbClient, migrationEngine); + + @Test + public void start_creates_fake_rows_in_project_measures_to_fix_sql_plan_of_measure_tree_queries() { + when(serverUpgradeStatus.isFreshInstall()).thenReturn(true); + + underTest.start(); + + verifyNoMoreInteractions(migrationEngine); + assertThat(dbTester.countRowsOfTable("project_measures")).isEqualTo(999); + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/AutoDbMigrationTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/AutoDbMigrationTest.java index 093d6541ad9..f571036e4cc 100644 --- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/AutoDbMigrationTest.java +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/AutoDbMigrationTest.java @@ -58,7 +58,8 @@ public class AutoDbMigrationTest { private AutoDbMigration noRealH2Creation = spy(new AutoDbMigration(serverUpgradeStatus, dbClient, migrationEngine) { @Override - protected void createSchema(Connection connection, String dialectId) { + protected void createH2Schema(Connection connection, String dialectId) { + // do nothing } }); diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/AutoDbMigrationH2Test/schema_migrations.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/AutoDbMigrationH2Test/schema_migrations.sql new file mode 100644 index 00000000000..aad2a0750a7 --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/AutoDbMigrationH2Test/schema_migrations.sql @@ -0,0 +1,4 @@ +CREATE TABLE "SCHEMA_MIGRATIONS" ( + "VERSION" VARCHAR(256) NOT NULL +); +CREATE INDEX "UNIQUE_SCHEMA_MIGRATIONS" ON "SCHEMA_MIGRATIONS" ("VERSION"); -- 2.39.5