From bc76a9469a0c0c9157e458f507a3090504af92e7 Mon Sep 17 00:00:00 2001 From: Jacek Date: Fri, 3 Dec 2021 16:20:03 +0100 Subject: [PATCH] SONAR-15780 Migrate 'image' column type to 'varbinary(max)' in MSSQL --- .../MigrationConfigurationModule.java | 2 + .../db/migration/version/v93/DbVersion93.java | 31 +++++++ .../v93/FixUsageOfDeprecatedColumnsMsSQL.java | 60 ++++++++++++++ .../version/v93/DbVersion93Test.java | 47 +++++++++++ .../FixUsageOfDeprecatedColumnsMsSQLTest.java | 83 +++++++++++++++++++ server/sonar-docs/package.json | 2 +- 6 files changed, 224 insertions(+), 1 deletion(-) create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v93/DbVersion93.java create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v93/FixUsageOfDeprecatedColumnsMsSQL.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v93/DbVersion93Test.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v93/FixUsageOfDeprecatedColumnsMsSQLTest.java diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/MigrationConfigurationModule.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/MigrationConfigurationModule.java index beb3929e55f..c754396cf1d 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/MigrationConfigurationModule.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/MigrationConfigurationModule.java @@ -30,6 +30,7 @@ import org.sonar.server.platform.db.migration.version.v00.DbVersion00; import org.sonar.server.platform.db.migration.version.v90.DbVersion90; import org.sonar.server.platform.db.migration.version.v91.DbVersion91; import org.sonar.server.platform.db.migration.version.v92.DbVersion92; +import org.sonar.server.platform.db.migration.version.v93.DbVersion93; public class MigrationConfigurationModule extends Module { @Override @@ -40,6 +41,7 @@ public class MigrationConfigurationModule extends Module { DbVersion90.class, DbVersion91.class, DbVersion92.class, + DbVersion93.class, // migration steps MigrationStepRegistryImpl.class, diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v93/DbVersion93.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v93/DbVersion93.java new file mode 100644 index 00000000000..b3ca24d032a --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v93/DbVersion93.java @@ -0,0 +1,31 @@ +/* + * 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.v93; + +import org.sonar.server.platform.db.migration.step.MigrationStepRegistry; +import org.sonar.server.platform.db.migration.version.DbVersion; + +public class DbVersion93 implements DbVersion { + @Override + public void addSteps(MigrationStepRegistry registry) { + registry + .add(6201, "Fix usage of deprecated column in MSSQL", FixUsageOfDeprecatedColumnsMsSQL.class); + } +} diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v93/FixUsageOfDeprecatedColumnsMsSQL.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v93/FixUsageOfDeprecatedColumnsMsSQL.java new file mode 100644 index 00000000000..2e839863802 --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v93/FixUsageOfDeprecatedColumnsMsSQL.java @@ -0,0 +1,60 @@ +/* + * 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.v93; + +import java.sql.Connection; +import java.sql.ResultSet; +import java.sql.SQLException; +import org.sonar.db.Database; +import org.sonar.db.dialect.MsSql; +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.BlobColumnDef.newBlobColumnDefBuilder; + +public class FixUsageOfDeprecatedColumnsMsSQL extends DdlChange { + + public FixUsageOfDeprecatedColumnsMsSQL(Database db) { + super(db); + } + + @Override + public void execute(Context context) throws SQLException { + if (!MsSql.ID.equals(getDatabase().getDialect().getId())) { + return; + } + + try (Connection c = getDatabase().getDataSource().getConnection(); + var ps = c.prepareStatement( + "SELECT table_name, column_name FROM [INFORMATION_SCHEMA].[COLUMNS] WHERE data_type = ?")) { + ps.setString(1, "image"); + ResultSet rs = ps.executeQuery(); + while (rs.next()) { + var tableName = rs.getString(1); + var columnName = rs.getString(2); + context.execute( + new AlterColumnsBuilder(getDatabase().getDialect(), tableName) + .updateColumn(newBlobColumnDefBuilder().setColumnName(columnName).build()) + .build()); + } + } + + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v93/DbVersion93Test.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v93/DbVersion93Test.java new file mode 100644 index 00000000000..cb8cac21eba --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v93/DbVersion93Test.java @@ -0,0 +1,47 @@ +/* + * 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.v93; + +import org.junit.Test; + +import static org.assertj.core.api.Assertions.assertThat; +import static org.sonar.server.platform.db.migration.version.DbVersionTestUtils.verifyMigrationNotEmpty; +import static org.sonar.server.platform.db.migration.version.DbVersionTestUtils.verifyMinimumMigrationNumber; + +public class DbVersion93Test { + + private final DbVersion93 underTest = new DbVersion93(); + + @Test + public void verify_no_support_component() { + assertThat(underTest.getSupportComponents()).isEmpty(); + } + + @Test + public void migrationNumber_starts_at_6101() { + verifyMinimumMigrationNumber(underTest, 6201); + } + + @Test + public void verify_migration_count() { + verifyMigrationNotEmpty(underTest); + } + +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v93/FixUsageOfDeprecatedColumnsMsSQLTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v93/FixUsageOfDeprecatedColumnsMsSQLTest.java new file mode 100644 index 00000000000..284a14dbf51 --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v93/FixUsageOfDeprecatedColumnsMsSQLTest.java @@ -0,0 +1,83 @@ +/* + * 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.v93; + +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import javax.sql.DataSource; +import org.junit.Test; +import org.sonar.db.Database; +import org.sonar.db.dialect.MsSql; +import org.sonar.db.dialect.PostgreSql; +import org.sonar.server.platform.db.migration.step.DdlChange.Context; + +import static java.util.List.of; +import static org.assertj.core.api.Assertions.assertThatCode; +import static org.mockito.ArgumentMatchers.anyString; +import static org.mockito.Mockito.mock; +import static org.mockito.Mockito.times; +import static org.mockito.Mockito.verify; +import static org.mockito.Mockito.when; + +public class FixUsageOfDeprecatedColumnsMsSQLTest { + + @Test + public void skip_if_not_mssql() { + var databaseMock = mock(Database.class); + when(databaseMock.getDialect()).thenReturn(new PostgreSql()); + var underTest = new FixUsageOfDeprecatedColumnsMsSQL(databaseMock); + var contextMock = mock(Context.class); + + assertThatCode(() -> underTest.execute(contextMock)) + .doesNotThrowAnyException(); + verify(databaseMock, times(0)).getDataSource(); + } + + @Test + public void execute_alter_table_if_columns_with_deprecated_type_exist() throws SQLException { + var databaseMock = mock(Database.class); + var dataSourceMock = mock(DataSource.class); + var connectionMock = mock(Connection.class); + var prepareStatementMock = mock(PreparedStatement.class); + var resultSetMock = mock(ResultSet.class); + + when(databaseMock.getDialect()).thenReturn(new MsSql()); + when(databaseMock.getDataSource()).thenReturn(dataSourceMock); + when(dataSourceMock.getConnection()).thenReturn(connectionMock); + when(connectionMock.prepareStatement(anyString())).thenReturn(prepareStatementMock); + when(prepareStatementMock.executeQuery()).thenReturn(resultSetMock); + + when(resultSetMock.next()).thenReturn(true, true, true, true, false); + when(resultSetMock.getString(1)).thenReturn("file_sources", "issues", "notifications", "project_measures"); + when(resultSetMock.getString(2)).thenReturn("binary_data", "locations", "data", "measure_data"); + + var underTest = new FixUsageOfDeprecatedColumnsMsSQL(databaseMock); + var contextMock = mock(Context.class); + + assertThatCode(() -> underTest.execute(contextMock)) + .doesNotThrowAnyException(); + verify(contextMock, times(1)).execute(of("ALTER TABLE file_sources ALTER COLUMN binary_data VARBINARY(MAX) NULL")); + verify(contextMock, times(1)).execute(of("ALTER TABLE issues ALTER COLUMN locations VARBINARY(MAX) NULL")); + verify(contextMock, times(1)).execute(of("ALTER TABLE notifications ALTER COLUMN data VARBINARY(MAX) NULL")); + verify(contextMock, times(1)).execute(of("ALTER TABLE project_measures ALTER COLUMN measure_data VARBINARY(MAX) NULL")); + } +} diff --git a/server/sonar-docs/package.json b/server/sonar-docs/package.json index e8a80e609d1..7dde29b1131 100644 --- a/server/sonar-docs/package.json +++ b/server/sonar-docs/package.json @@ -111,4 +111,4 @@ } }, "packageManager": "yarn@3.0.2" -} \ No newline at end of file +} -- 2.39.5