From ea6e4464c8d6bf09bf748d1219d98a736689c9cb Mon Sep 17 00:00:00 2001 From: Duarte Meneses Date: Thu, 7 Jan 2021 15:28:26 -0600 Subject: [PATCH] SONAR-14323 Optimize DB migrations from 7.9 LTS to 8.X LTS --- .../main/java/org/sonar/db/DatabaseUtils.java | 24 ++++++ .../java/org/sonar/db/DatabaseUtilsTest.java | 40 ++++++++++ .../org/sonar/db/DatabaseUtilsTest/sql.sql | 4 + .../db/migration/version/v84/DbVersion84.java | 4 +- .../issuechanges/CopyIssueChangesTable.java | 75 ++++++++++++------- .../issuechanges/DropIssueChangesTable.java | 3 +- ...dPrimaryKeyOnUuidForIssueChangesTable.java | 8 +- .../v85/CreateTmpIssueChangesTable.java | 39 ++++++---- .../db/migration/version/v85/DbVersion85.java | 3 +- .../version/v85/DropIssueChangesTable.java | 42 +++++++++++ .../RenameTmpIssueChangesToIssueChanges.java | 14 +++- ...dexOnIssueKeyForIssueChangesTableTest.java | 7 ++ ...AddIndexOnKeeForIssueChangesTableTest.java | 7 ++ ...xOnProjectUuidOnIssueChangesTableTest.java | 7 ++ ...maryKeyOnUuidForIssueChangesTableTest.java | 9 +++ .../v85/CreateTmpIssueChangesTableTest.java | 8 ++ .../v85/DropIssueChangesTableTest.java | 58 ++++++++++++++ ...ssueKeyNotNullOnIssueChangesTableTest.java | 7 ++ ...ectUuidNotNullOnIssueChangesTableTest.java | 8 ++ ...akeUuidNotNullOnIssueChangesTableTest.java | 8 ++ ...nameTmpIssueChangesToIssueChangesTest.java | 9 +++ .../CopyIssueChangesTableTest/schema.sql | 39 ++++++++++ .../v85/DropIssueChangesTableTest/schema.sql | 39 ++++++++++ 23 files changed, 410 insertions(+), 52 deletions(-) create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v85/DropIssueChangesTable.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/DropIssueChangesTableTest.java create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v85/DropIssueChangesTableTest/schema.sql diff --git a/server/sonar-db-core/src/main/java/org/sonar/db/DatabaseUtils.java b/server/sonar-db-core/src/main/java/org/sonar/db/DatabaseUtils.java index 4f579f486f2..2d566ee3ede 100644 --- a/server/sonar-db-core/src/main/java/org/sonar/db/DatabaseUtils.java +++ b/server/sonar-db-core/src/main/java/org/sonar/db/DatabaseUtils.java @@ -351,6 +351,30 @@ public class DatabaseUtils { } } + public static boolean tableColumnExists(Connection connection, String tableName, String columnName) { + try { + return columnExists(connection, tableName.toLowerCase(Locale.US), columnName) + || columnExists(connection, tableName.toUpperCase(Locale.US), columnName); + } catch (SQLException e) { + throw wrapSqlException(e, "Can not check that column %s exists", columnName); + } + } + + private static boolean columnExists(Connection connection, String tableName, String columnName) throws SQLException { + String schema = getSchema(connection); + try (ResultSet rs = connection.getMetaData().getColumns(connection.getCatalog(), schema, tableName, null)) { + while (rs.next()) { + for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) { + String name = rs.getString(i); + if (columnName.equalsIgnoreCase(name)) { + return true; + } + } + } + return false; + } + } + @CheckForNull private static String getSchema(Connection connection) { String schema = null; diff --git a/server/sonar-db-core/src/test/java/org/sonar/db/DatabaseUtilsTest.java b/server/sonar-db-core/src/test/java/org/sonar/db/DatabaseUtilsTest.java index 92b20692e32..21ae0cbba43 100644 --- a/server/sonar-db-core/src/test/java/org/sonar/db/DatabaseUtilsTest.java +++ b/server/sonar-db-core/src/test/java/org/sonar/db/DatabaseUtilsTest.java @@ -85,6 +85,46 @@ public class DatabaseUtilsTest { } } + @Test + public void find_column_with_lower_case_table_name_and_upper_case_column_name() throws SQLException { + String tableName = "tablea"; + String columnName = "COLUMNA"; + try (Connection connection = dbTester.openConnection()) { + assertThat(DatabaseUtils.tableColumnExists(connection, tableName, columnName)).isTrue(); + assertThat(DatabaseUtils.tableColumnExists(connection, tableName.toLowerCase(Locale.US), columnName)).isTrue(); + } + } + @Test + public void find_column_with_upper_case_table_name_and_upper_case_column_name() throws SQLException { + String tableName = "TABLEA"; + String columnName = "COLUMNA"; + try (Connection connection = dbTester.openConnection()) { + assertThat(DatabaseUtils.tableColumnExists(connection, tableName, columnName)).isTrue(); + assertThat(DatabaseUtils.tableColumnExists(connection, tableName.toLowerCase(Locale.US), columnName)).isTrue(); + } + } + + @Test + public void find_column_with_lower_case_table_name_and_lower_case_column_name() throws SQLException { + String tableName = "tablea"; + String columnName = "columna"; + try (Connection connection = dbTester.openConnection()) { + assertThat(DatabaseUtils.tableColumnExists(connection, tableName, columnName)).isTrue(); + assertThat(DatabaseUtils.tableColumnExists(connection, tableName.toLowerCase(Locale.US), columnName)).isTrue(); + } + } + + @Test + public void find_column_with_upper_case_table_name_and_lower_case_column_name() throws SQLException { + String tableName = "TABLEA"; + String columnName = "columna"; + try (Connection connection = dbTester.openConnection()) { + assertThat(DatabaseUtils.tableColumnExists(connection, tableName, columnName)).isTrue(); + assertThat(DatabaseUtils.tableColumnExists(connection, tableName, columnName.toLowerCase(Locale.US))).isTrue(); + assertThat(DatabaseUtils.tableColumnExists(connection, tableName.toLowerCase(Locale.US), columnName.toLowerCase(Locale.US))).isTrue(); + } + } + @Test public void should_close_connection() throws Exception { try (Connection connection = dbTester.openConnection()) { diff --git a/server/sonar-db-core/src/test/resources/org/sonar/db/DatabaseUtilsTest/sql.sql b/server/sonar-db-core/src/test/resources/org/sonar/db/DatabaseUtilsTest/sql.sql index ec4b8782e31..e0fbf26800c 100644 --- a/server/sonar-db-core/src/test/resources/org/sonar/db/DatabaseUtilsTest/sql.sql +++ b/server/sonar-db-core/src/test/resources/org/sonar/db/DatabaseUtilsTest/sql.sql @@ -2,6 +2,10 @@ CREATE TABLE "schema_migrations" ( "version" VARCHAR(256) NOT NULL ); +CREATE TABLE "TABLEA" ( + "COLUMNA" VARCHAR(256) NOT NULL +); + CREATE INDEX UPPER_CASE_NAME ON schema_migrations (version); CREATE INDEX lower_case_name ON schema_migrations (version); diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/DbVersion84.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/DbVersion84.java index 0e81cd6ac0d..8e4a77256da 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/DbVersion84.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/DbVersion84.java @@ -331,6 +331,7 @@ import org.sonar.server.platform.db.migration.version.v84.usertokens.DropIdColum import org.sonar.server.platform.db.migration.version.v84.usertokens.DropPrimaryKeyOnIdColumnOfUserTokensTable; import org.sonar.server.platform.db.migration.version.v84.usertokens.MakeUserTokensUuidNotNullable; import org.sonar.server.platform.db.migration.version.v84.usertokens.PopulateUserTokensUuid; +import org.sonar.server.platform.db.migration.version.v85.AddIndexOnProjectUuidOnIssueChangesTable; public class DbVersion84 implements DbVersion { @Override @@ -446,6 +447,7 @@ public class DbVersion84 implements DbVersion { .add(3479, "Add index on 'ISSUE_KEY' of 'ISSUE_CHANGES' table", AddIndexOnIssueKeyOfIssueChangesTable.class) .add(3480, "Add index on 'KEE' of 'ISSUE_CHANGES' table", AddIndexOnKeeOfIssueChangesTable.class) .add(3481, "Add primary key on 'UUID' column of 'ISSUE_CHANGES' table", AddPrimaryKeyOnUuidColumnOfIssueChangesTable.class) + .add(3482, "Add index on 'project_uuid' for table 'ISSUE_CHANGES'", AddIndexOnProjectUuidOnIssueChangesTable.class) // Migration of QUALITY_GATE_CONDITIONS table .add(3483, "Add 'UUID' column on 'QUALITY_GATE_CONDITIONS' table", AddUuidColumnToQualityGateConditionsTable.class) @@ -782,7 +784,7 @@ public class DbVersion84 implements DbVersion { .add(3804, "Populate 'need_issue_sync' of 'project_branches'", PopulateProjectBranchesNeedIssueSync.class) .add(3805, "Make 'need_issue_sync' of 'project_branches' not null", MakeProjectBranchesNeedIssueSyncNonNull.class) - .add(3806, "Drop local webhooks", DropLocalWebhooks.class) + .add(3806, "Drop local webhooks", DropLocalWebhooks.class) // Migration of ALM_SETTINGS table .add(3807, "Add columns 'CLIENT_ID' and 'CLIENT_SECRET' to 'ALM_SETTINGS' table", AddClientIdAndClientSecretColumns.class) diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/issuechanges/CopyIssueChangesTable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/issuechanges/CopyIssueChangesTable.java index 126e096bda2..8f09a04aeb2 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/issuechanges/CopyIssueChangesTable.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/issuechanges/CopyIssueChangesTable.java @@ -21,42 +21,61 @@ package org.sonar.server.platform.db.migration.version.v84.issuechanges; import java.sql.SQLException; import org.sonar.db.Database; -import org.sonar.server.platform.db.migration.sql.CreateTableAsBuilder; +import org.sonar.db.dialect.MsSql; +import org.sonar.server.platform.db.migration.def.VarcharColumnDef; +import org.sonar.server.platform.db.migration.sql.AlterColumnsBuilder; import org.sonar.server.platform.db.migration.step.DdlChange; -import static org.sonar.server.platform.db.migration.def.BigIntegerColumnDef.newBigIntegerColumnDefBuilder; -import static org.sonar.server.platform.db.migration.def.ClobColumnDef.newClobColumnDefBuilder; import static org.sonar.server.platform.db.migration.def.VarcharColumnDef.newVarcharColumnDefBuilder; public class CopyIssueChangesTable extends DdlChange { + private static final String COPY_NAME = "issue_changes_copy"; + public CopyIssueChangesTable(Database db) { super(db); } - @Override public void execute(Context context) throws SQLException { - CreateTableAsBuilder builder = new CreateTableAsBuilder(getDialect(), "issue_changes_copy", "issue_changes") - // this will cause the following changes: - // * Add UUID with values in ID casted to varchar - .addColumnWithCast(newVarcharColumnDefBuilder().setColumnName("uuid").setLimit(40).setIsNullable(false).build(), "id") - .addColumn(newVarcharColumnDefBuilder().setColumnName("kee").setLimit(50).build()) - .addColumn(newVarcharColumnDefBuilder().setColumnName("issue_key").setLimit(50).setIsNullable(false).build()) - .addColumn(newVarcharColumnDefBuilder().setColumnName("user_login").setLimit(255).build()) - .addColumn(newVarcharColumnDefBuilder().setColumnName("change_type").setLimit(20).build()) - .addColumn(newClobColumnDefBuilder().setColumnName("change_data").build()) - .addColumn(newBigIntegerColumnDefBuilder().setColumnName("created_at").build()) - .addColumn(newBigIntegerColumnDefBuilder().setColumnName("updated_at").build()) - .addColumn(newBigIntegerColumnDefBuilder().setColumnName("issue_change_creation_date").build()); - context.execute(builder.build()); - /* - "UUID VARCHAR(40) NOT NULL", - "KEE VARCHAR(50)", - "ISSUE_KEY VARCHAR(50) NOT NULL", - "USER_LOGIN VARCHAR(255)", - "CHANGE_TYPE VARCHAR(20)", - "CHANGE_DATA CLOB(2147483647)", - "CREATED_AT BIGINT", - "UPDATED_AT BIGINT", - "ISSUE_CHANGE_CREATION_DATE BIGINT" - */ + @Override + public void execute(Context context) throws SQLException { + + String query; + if (getDatabase().getDialect().getId().equals(MsSql.ID)) { + query = "select cast (ic.id AS VARCHAR(40)) AS uuid, ic.kee, ic.issue_key, ic.user_login, ic.change_type, " + + "ic.change_data, ic.created_at, ic.updated_at, ic.issue_change_creation_date, i.project_uuid " + + "INTO issue_changes_copy " + + "FROM issue_changes AS ic inner join issues i on i.kee = ic.issue_key"; + } else { + query = "create table issue_changes_copy " + + "(uuid, kee, issue_key, user_login, change_type, change_data, created_at, updated_at, issue_change_creation_date, project_uuid)" + + "as (" + + "SELECT cast (ic.id AS VARCHAR(40)) AS uuid, ic.kee, ic.issue_key, ic.user_login, ic.change_type, ic.change_data, ic.created_at, ic.updated_at, " + + "ic.issue_change_creation_date, i.project_uuid " + + "FROM issue_changes ic " + + "inner join issues i on i.kee = ic.issue_key " + + ")"; + } + + context.execute(query); + context.execute(new AlterColumnsBuilder(getDialect(), COPY_NAME).updateColumn( + newVarcharColumnDefBuilder() + .setColumnName("project_uuid") + .setIsNullable(false) + .setDefaultValue(null) + .setLimit(VarcharColumnDef.UUID_VARCHAR_SIZE) + .build()).build()); + context.execute(new AlterColumnsBuilder(getDialect(), COPY_NAME).updateColumn( + newVarcharColumnDefBuilder() + .setColumnName("issue_key") + .setIsNullable(false) + .setDefaultValue(null) + .setLimit(VarcharColumnDef.UUID_VARCHAR_SIZE) + .build()).build()); + context.execute(new AlterColumnsBuilder(getDialect(), COPY_NAME).updateColumn( + newVarcharColumnDefBuilder() + .setColumnName("uuid") + .setIsNullable(false) + .setDefaultValue(null) + .setLimit(VarcharColumnDef.UUID_SIZE) + .build()).build()); } } diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/issuechanges/DropIssueChangesTable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/issuechanges/DropIssueChangesTable.java index 19bbc774fe8..7b6b1420934 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/issuechanges/DropIssueChangesTable.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/issuechanges/DropIssueChangesTable.java @@ -29,7 +29,8 @@ public class DropIssueChangesTable extends DdlChange { super(db); } - @Override public void execute(Context context) throws SQLException { + @Override + public void execute(Context context) throws SQLException { context.execute(new DropTableBuilder(getDialect(), "issue_changes").build()); } } diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v85/AddPrimaryKeyOnUuidForIssueChangesTable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v85/AddPrimaryKeyOnUuidForIssueChangesTable.java index 648885623d1..37feefda2a8 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v85/AddPrimaryKeyOnUuidForIssueChangesTable.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v85/AddPrimaryKeyOnUuidForIssueChangesTable.java @@ -19,9 +19,11 @@ */ package org.sonar.server.platform.db.migration.version.v85; +import java.sql.Connection; import java.sql.SQLException; import org.sonar.db.Database; import org.sonar.server.platform.db.migration.sql.AddPrimaryKeyBuilder; +import org.sonar.db.DatabaseUtils; import org.sonar.server.platform.db.migration.step.DdlChange; public class AddPrimaryKeyOnUuidForIssueChangesTable extends DdlChange { @@ -34,6 +36,10 @@ public class AddPrimaryKeyOnUuidForIssueChangesTable extends DdlChange { @Override public void execute(Context context) throws SQLException { - context.execute(new AddPrimaryKeyBuilder(TABLE, "uuid").build()); + try (Connection connection = getDatabase().getDataSource().getConnection()) { + if (!DatabaseUtils.indexExistsIgnoreCase(TABLE, "issue_changes_project_uuid", connection)) { + context.execute(new AddPrimaryKeyBuilder(TABLE, "uuid").build()); + } + } } } diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v85/CreateTmpIssueChangesTable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v85/CreateTmpIssueChangesTable.java index 415cf7ca4f0..6fd09ceaab2 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v85/CreateTmpIssueChangesTable.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v85/CreateTmpIssueChangesTable.java @@ -19,8 +19,10 @@ */ package org.sonar.server.platform.db.migration.version.v85; +import java.sql.Connection; import java.sql.SQLException; import org.sonar.db.Database; +import org.sonar.db.DatabaseUtils; import org.sonar.db.dialect.MsSql; import org.sonar.server.platform.db.migration.step.DdlChange; @@ -32,23 +34,28 @@ public class CreateTmpIssueChangesTable extends DdlChange { @Override public void execute(Context context) throws SQLException { + try (Connection connection = getDatabase().getDataSource().getConnection()) { + if (DatabaseUtils.tableColumnExists(connection, "issue_changes", "project_uuid")) { + // This migration might already have been done in v84 (#3476) if using SQ >= 8.7. + return; + } + String query; + if (getDatabase().getDialect().getId().equals(MsSql.ID)) { + query = "SELECT ic.uuid, ic.kee, ic.issue_key, ic.user_login, ic.change_type, " + + "ic.change_data, ic.created_at, ic.updated_at, ic.issue_change_creation_date, i.project_uuid " + + "INTO tmp_issue_changes " + + "FROM issue_changes AS ic inner join issues i on i.kee = ic.issue_key"; + } else { + query = "create table tmp_issue_changes " + + "(uuid, kee, issue_key, user_login, change_type, change_data, created_at, updated_at, issue_change_creation_date, project_uuid)" + + "as (" + + "SELECT ic.uuid, ic.kee, ic.issue_key, ic.user_login, ic.change_type, ic.change_data, ic.created_at, ic.updated_at, ic.issue_change_creation_date, i.project_uuid " + + "FROM issue_changes ic " + + "inner join issues i on i.kee = ic.issue_key " + + ")"; + } - String query; - if (getDatabase().getDialect().getId().equals(MsSql.ID)) { - query = "SELECT ic.uuid, ic.kee, ic.issue_key, ic.user_login, ic.change_type, " + - "ic.change_data, ic.created_at, ic.updated_at, ic.issue_change_creation_date, i.project_uuid " + - "INTO tmp_issue_changes " + - "FROM issue_changes AS ic inner join issues i on i.kee = ic.issue_key"; - } else { - query = "create table tmp_issue_changes " + - "(uuid, kee, issue_key, user_login, change_type, change_data, created_at, updated_at, issue_change_creation_date, project_uuid)" + - "as (" + - "SELECT ic.uuid, ic.kee, ic.issue_key, ic.user_login, ic.change_type, ic.change_data, ic.created_at, ic.updated_at, ic.issue_change_creation_date, i.project_uuid " + - "FROM issue_changes ic " + - "inner join issues i on i.kee = ic.issue_key " + - ")"; + context.execute(query); } - - context.execute(query); } } diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v85/DbVersion85.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v85/DbVersion85.java index 81edee1886a..01fcb2a77a0 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v85/DbVersion85.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v85/DbVersion85.java @@ -21,7 +21,6 @@ package org.sonar.server.platform.db.migration.version.v85; import org.sonar.server.platform.db.migration.step.MigrationStepRegistry; import org.sonar.server.platform.db.migration.version.DbVersion; -import org.sonar.server.platform.db.migration.version.v84.issuechanges.DropIssueChangesTable; public class DbVersion85 implements DbVersion { @@ -34,6 +33,7 @@ public class DbVersion85 implements DbVersion { .add(4003, "Drop unused variation values columns in 'project_measures' table", DropUnusedVariationsInProjectMeasures.class) .add(4004, "Drop unused periods in 'snapshots' table", DropUnusedPeriodsInSnapshots.class) .add(4005, "Drop orphan favorites from 'properties' table", DropOrphanFavoritesFromProperties.class) + .add(4006, "create 'tmp_issue_changes' table", CreateTmpIssueChangesTable.class) .add(4007, "drop 'issue_changes' table", DropIssueChangesTable.class) .add(4008, "rename 'tmp_issue_changes' table to 'issue_changes'", RenameTmpIssueChangesToIssueChanges.class) @@ -44,6 +44,7 @@ public class DbVersion85 implements DbVersion { .add(4013, "add index on 'issue_key' for table 'issue_changes'", AddIndexOnIssueKeyForIssueChangesTable.class) .add(4014, "add index on 'kee' for table 'issue_changes'", AddIndexOnKeeForIssueChangesTable.class) .add(4015, "add index on 'project_uuid' for table 'issue_changes'", AddIndexOnProjectUuidOnIssueChangesTable.class) + .add(4016, "Add 'type' column to 'plugins' table", AddTypeToPlugins.class) .add(4017, "Populate 'type' column in 'plugins' table", PopulateTypeInPlugins.class) .add(4018, "Alter 'type' column in 'plugins' to not nullable", AlterTypeInPluginNotNullable.class) diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v85/DropIssueChangesTable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v85/DropIssueChangesTable.java new file mode 100644 index 00000000000..22aab062fb4 --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v85/DropIssueChangesTable.java @@ -0,0 +1,42 @@ +/* + * 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.v85; + +import java.sql.Connection; +import java.sql.SQLException; +import org.sonar.db.Database; +import org.sonar.db.DatabaseUtils; +import org.sonar.server.platform.db.migration.sql.DropTableBuilder; +import org.sonar.server.platform.db.migration.step.DdlChange; + +public class DropIssueChangesTable extends DdlChange { + public DropIssueChangesTable(Database db) { + super(db); + } + + @Override + public void execute(Context context) throws SQLException { + try (Connection connection = getDatabase().getDataSource().getConnection()) { + if (DatabaseUtils.tableExists("tmp_issue_changes", connection)) { + context.execute(new DropTableBuilder(getDialect(), "issue_changes").build()); + } + } + } +} diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v85/RenameTmpIssueChangesToIssueChanges.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v85/RenameTmpIssueChangesToIssueChanges.java index 0ed5b022131..2800416a523 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v85/RenameTmpIssueChangesToIssueChanges.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v85/RenameTmpIssueChangesToIssueChanges.java @@ -19,8 +19,10 @@ */ package org.sonar.server.platform.db.migration.version.v85; +import java.sql.Connection; import java.sql.SQLException; import org.sonar.db.Database; +import org.sonar.db.DatabaseUtils; import org.sonar.server.platform.db.migration.sql.RenameTableBuilder; import org.sonar.server.platform.db.migration.step.DdlChange; @@ -32,9 +34,13 @@ public class RenameTmpIssueChangesToIssueChanges extends DdlChange { @Override public void execute(Context context) throws SQLException { - context.execute(new RenameTableBuilder(getDialect()) - .setName("tmp_issue_changes").setNewName("issue_changes") - .setAutoGeneratedId(false) - .build()); + try (Connection connection = getDatabase().getDataSource().getConnection()) { + if (DatabaseUtils.tableExists("tmp_issue_changes", connection)) { + context.execute(new RenameTableBuilder(getDialect()) + .setName("tmp_issue_changes").setNewName("issue_changes") + .setAutoGeneratedId(false) + .build()); + } + } } } diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/AddIndexOnIssueKeyForIssueChangesTableTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/AddIndexOnIssueKeyForIssueChangesTableTest.java index aeaeca3cb56..7337bb06dd6 100644 --- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/AddIndexOnIssueKeyForIssueChangesTableTest.java +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/AddIndexOnIssueKeyForIssueChangesTableTest.java @@ -39,4 +39,11 @@ public class AddIndexOnIssueKeyForIssueChangesTableTest { underTest.execute(); db.assertIndex(TABLE_NAME, INDEX_NAME, "issue_key"); } + + @Test + public void migration_is_reentrant() throws SQLException { + underTest.execute(); + underTest.execute(); + db.assertIndex(TABLE_NAME, INDEX_NAME, "issue_key"); + } } diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/AddIndexOnKeeForIssueChangesTableTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/AddIndexOnKeeForIssueChangesTableTest.java index 8b7c7e2d31f..f4ebe62acbe 100644 --- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/AddIndexOnKeeForIssueChangesTableTest.java +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/AddIndexOnKeeForIssueChangesTableTest.java @@ -39,4 +39,11 @@ public class AddIndexOnKeeForIssueChangesTableTest { underTest.execute(); db.assertIndex(TABLE_NAME, INDEX_NAME, "kee"); } + + @Test + public void migration_is_reentrant() throws SQLException { + underTest.execute(); + underTest.execute(); + db.assertIndex(TABLE_NAME, INDEX_NAME, "kee"); + } } diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/AddIndexOnProjectUuidOnIssueChangesTableTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/AddIndexOnProjectUuidOnIssueChangesTableTest.java index 2e32cf3b1c2..e47019251dc 100644 --- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/AddIndexOnProjectUuidOnIssueChangesTableTest.java +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/AddIndexOnProjectUuidOnIssueChangesTableTest.java @@ -40,4 +40,11 @@ public class AddIndexOnProjectUuidOnIssueChangesTableTest { db.assertIndex(TABLE_NAME, INDEX_NAME, "project_uuid"); } + @Test + public void migration_is_reentrant() throws SQLException { + underTest.execute(); + underTest.execute(); + db.assertIndex(TABLE_NAME, INDEX_NAME, "project_uuid"); + } + } diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/AddPrimaryKeyOnUuidForIssueChangesTableTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/AddPrimaryKeyOnUuidForIssueChangesTableTest.java index d3d0b85e00a..25f8681a9ab 100644 --- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/AddPrimaryKeyOnUuidForIssueChangesTableTest.java +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/AddPrimaryKeyOnUuidForIssueChangesTableTest.java @@ -41,6 +41,15 @@ public class AddPrimaryKeyOnUuidForIssueChangesTableTest { db.assertPrimaryKey("issue_changes", "pk_issue_changes", "uuid"); } + @Test + public void skip_if_project_uuid_index_exists() throws SQLException { + db.executeDdl("create index issue_changes_project_uuid on issue_changes ( issue_key)"); + + underTest.execute(); + + db.assertNoPrimaryKey("issue_changes"); + } + @Test public void migration_is_not_re_entrant() throws SQLException { underTest.execute(); diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/CreateTmpIssueChangesTableTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/CreateTmpIssueChangesTableTest.java index 25fc79ad719..96267985c85 100644 --- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/CreateTmpIssueChangesTableTest.java +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/CreateTmpIssueChangesTableTest.java @@ -41,6 +41,14 @@ public class CreateTmpIssueChangesTableTest { private CreateTmpIssueChangesTable underTest = new CreateTmpIssueChangesTable(dbTester.database()); + @Test + public void skip_if_project_uuid_column_exists() throws SQLException { + dbTester.executeDdl("ALTER TABLE issue_changes ADD project_uuid VARCHAR"); + + underTest.execute(); + dbTester.assertTableDoesNotExist(TABLE_NAME); + } + @Test public void table_has_been_created() throws SQLException { underTest.execute(); diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/DropIssueChangesTableTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/DropIssueChangesTableTest.java new file mode 100644 index 00000000000..73ad5876042 --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/DropIssueChangesTableTest.java @@ -0,0 +1,58 @@ +/* + * 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.v85; + +import java.sql.SQLException; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.db.CoreDbTester; +import org.sonar.server.platform.db.migration.step.MigrationStep; + +public class DropIssueChangesTableTest { + @Rule + public CoreDbTester db = CoreDbTester.createForSchema(DropIssueChangesTableTest.class, "schema.sql"); + + private MigrationStep underTest = new DropIssueChangesTable(db.database()); + + @Test + public void dont_drop_if_tmp_table_doesnt_exist() throws SQLException { + db.executeDdl("drop table tmp_issue_changes"); + underTest.execute(); + db.assertTableExists("issue_changes"); + } + + @Test + public void execute() throws SQLException { + db.assertTableExists("issue_changes"); + underTest.execute(); + db.assertTableDoesNotExist("issue_changes"); + } + + @Test + public void migration_is_re_entrant() throws SQLException { + db.assertTableExists("issue_changes"); + + underTest.execute(); + + // re-entrant + underTest.execute(); + db.assertTableDoesNotExist("issue_changes"); + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/MakeIssueKeyNotNullOnIssueChangesTableTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/MakeIssueKeyNotNullOnIssueChangesTableTest.java index 7aee044b6a9..ca4bb48285b 100644 --- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/MakeIssueKeyNotNullOnIssueChangesTableTest.java +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/MakeIssueKeyNotNullOnIssueChangesTableTest.java @@ -41,4 +41,11 @@ public class MakeIssueKeyNotNullOnIssueChangesTableTest { db.assertColumnDefinition("issue_changes", "issue_key", VARCHAR, 50, false); } + @Test + public void migration_is_reentrant() throws SQLException { + underTest.execute(); + underTest.execute(); + + db.assertColumnDefinition("issue_changes", "issue_key", VARCHAR, 50, false); + } } diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/MakeProjectUuidNotNullOnIssueChangesTableTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/MakeProjectUuidNotNullOnIssueChangesTableTest.java index de962d7cb9b..84b9170e80f 100644 --- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/MakeProjectUuidNotNullOnIssueChangesTableTest.java +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/MakeProjectUuidNotNullOnIssueChangesTableTest.java @@ -40,4 +40,12 @@ public class MakeProjectUuidNotNullOnIssueChangesTableTest { db.assertColumnDefinition("issue_changes", "project_uuid", VARCHAR, 50, false); } + + @Test + public void migration_is_reentrant() throws SQLException { + underTest.execute(); + underTest.execute(); + + db.assertColumnDefinition("issue_changes", "project_uuid", VARCHAR, 50, false); + } } diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/MakeUuidNotNullOnIssueChangesTableTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/MakeUuidNotNullOnIssueChangesTableTest.java index 1c843e71d87..0e8af215179 100644 --- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/MakeUuidNotNullOnIssueChangesTableTest.java +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/MakeUuidNotNullOnIssueChangesTableTest.java @@ -41,4 +41,12 @@ public class MakeUuidNotNullOnIssueChangesTableTest { db.assertColumnDefinition("issue_changes", "uuid", VARCHAR, 40, false); } + @Test + public void migration_is_reentrant() throws SQLException { + underTest.execute(); + underTest.execute(); + + db.assertColumnDefinition("issue_changes", "uuid", VARCHAR, 40, false); + } + } diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/RenameTmpIssueChangesToIssueChangesTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/RenameTmpIssueChangesToIssueChangesTest.java index 462e12f01c4..bd9e206d468 100644 --- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/RenameTmpIssueChangesToIssueChangesTest.java +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/RenameTmpIssueChangesToIssueChangesTest.java @@ -38,6 +38,15 @@ public class RenameTmpIssueChangesToIssueChangesTest { private final RenameTmpIssueChangesToIssueChanges underTest = new RenameTmpIssueChangesToIssueChanges(dbTester.database()); + @Test + public void only_rename_if_tmp_table_exists() throws SQLException { + dbTester.executeDdl("drop table " + OLD_TABLE_NAME); + underTest.execute(); + + dbTester.assertTableDoesNotExist(OLD_TABLE_NAME); + dbTester.assertTableDoesNotExist(NEW_TABLE_NAME); + } + @Test public void table_has_been_renamed() throws SQLException { underTest.execute(); diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v84/issuechanges/CopyIssueChangesTableTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v84/issuechanges/CopyIssueChangesTableTest/schema.sql index 8b3247ab396..7854927f304 100644 --- a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v84/issuechanges/CopyIssueChangesTableTest/schema.sql +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v84/issuechanges/CopyIssueChangesTableTest/schema.sql @@ -11,3 +11,42 @@ CREATE TABLE "ISSUE_CHANGES"( ); CREATE INDEX "ISSUE_CHANGES_ISSUE_KEY" ON "ISSUE_CHANGES"("ISSUE_KEY"); CREATE INDEX "ISSUE_CHANGES_KEE" ON "ISSUE_CHANGES"("KEE"); + +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/v85/DropIssueChangesTableTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v85/DropIssueChangesTableTest/schema.sql new file mode 100644 index 00000000000..f39bedb9f15 --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v85/DropIssueChangesTableTest/schema.sql @@ -0,0 +1,39 @@ +CREATE TABLE "ISSUE_CHANGES"( + "ID" BIGINT NOT NULL AUTO_INCREMENT (1,1), + "KEE" VARCHAR(50), + "ISSUE_KEY" VARCHAR(50) NOT NULL, + "USER_LOGIN" VARCHAR(255), + "CHANGE_TYPE" VARCHAR(20), + "CHANGE_DATA" CLOB(2147483647), + "CREATED_AT" BIGINT, + "UPDATED_AT" BIGINT, + "ISSUE_CHANGE_CREATION_DATE" BIGINT +); +CREATE INDEX "ISSUE_CHANGES_ISSUE_KEY" ON "ISSUE_CHANGES"("ISSUE_KEY"); +CREATE INDEX "ISSUE_CHANGES_KEE" ON "ISSUE_CHANGES"("KEE"); + +CREATE TABLE "TMP_ISSUE_CHANGES"( + "UUID" VARCHAR(40), + "KEE" VARCHAR(50), + "ISSUE_KEY" VARCHAR(50), + "USER_LOGIN" VARCHAR(255), + "CHANGE_TYPE" VARCHAR(20), + "CHANGE_DATA" CLOB(2147483647), + "CREATED_AT" BIGINT, + "UPDATED_AT" BIGINT, + "PROJECT_UUID" VARCHAR(50), + "ISSUE_CHANGE_CREATION_DATE" BIGINT +); + +CREATE TABLE "ISSUE_CHANGES_COPY"( + "UUID" VARCHAR(40) NOT NULL, + "KEE" VARCHAR(50), + "ISSUE_KEY" VARCHAR(50) NOT NULL, + "USER_LOGIN" VARCHAR(255), + "CHANGE_TYPE" VARCHAR(20), + "CHANGE_DATA" CLOB(2147483647), + "CREATED_AT" BIGINT, + "UPDATED_AT" BIGINT, + "ISSUE_CHANGE_CREATION_DATE" BIGINT +); + -- 2.39.5