From 7497db8fdd7a19f2b21506c4602ea90190448fb2 Mon Sep 17 00:00:00 2001 From: Jacek Date: Thu, 28 Jul 2022 11:30:18 +0200 Subject: [PATCH] SONAR-17059 Verify collation check only on SonarQube tables --- .../charset/PostgresCharsetHandler.java | 16 +++++- .../charset/PostgresCharsetHandlerTest.java | 50 ++++++++++++++++--- 2 files changed, 56 insertions(+), 10 deletions(-) diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/charset/PostgresCharsetHandler.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/charset/PostgresCharsetHandler.java index 49cf9fdf97f..852f5071009 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/charset/PostgresCharsetHandler.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/charset/PostgresCharsetHandler.java @@ -26,8 +26,10 @@ import java.sql.SQLException; import java.util.LinkedHashSet; import java.util.List; import java.util.Set; +import java.util.stream.Collectors; import org.sonar.api.utils.MessageException; import org.sonar.api.utils.log.Loggers; +import org.sonar.db.version.SqTables; import static java.lang.String.format; import static java.util.Optional.ofNullable; @@ -69,12 +71,14 @@ class PostgresCharsetHandler extends CharsetHandler { // Examples: // issues | key | '' // projects | name | utf8 - var schema = ofNullable(connection.getSchema()).orElse("public"); + var sqTables = getSqTables(); + var schema = getSchema(connection); List rows = getSqlExecutor().select(connection, String.format("select table_name, column_name, collation_name " + "from information_schema.columns " + "where table_schema='%s' " + + "and table_name in (%s) " + "and udt_name='varchar' " + - "order by table_name, column_name", schema), new SqlExecutor.StringsConverter(3 /* columns returned by SELECT */)); + "order by table_name, column_name", schema, sqTables), new SqlExecutor.StringsConverter(3 /* columns returned by SELECT */)); Set errors = new LinkedHashSet<>(); for (String[] row : rows) { if (!isBlank(row[2]) && !containsIgnoreCase(row[2], UTF8)) { @@ -87,6 +91,14 @@ class PostgresCharsetHandler extends CharsetHandler { } } + private static String getSchema(Connection connection) throws SQLException { + return ofNullable(connection.getSchema()).orElse("public"); + } + + private static String getSqTables() { + return SqTables.TABLES.stream().map(s -> "'" + s + "'").collect(Collectors.joining(",")); + } + @VisibleForTesting PostgresMetadataReader getMetadata() { return metadata; diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/charset/PostgresCharsetHandlerTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/charset/PostgresCharsetHandlerTest.java index 49bc05d3562..719bbcb1efc 100644 --- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/charset/PostgresCharsetHandlerTest.java +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/charset/PostgresCharsetHandlerTest.java @@ -21,15 +21,18 @@ package org.sonar.server.platform.db.migration.charset; import java.sql.Connection; import java.sql.SQLException; -import java.util.Arrays; import java.util.List; +import java.util.stream.Collectors; import org.junit.Test; import org.sonar.api.utils.MessageException; +import org.sonar.db.version.SqTables; import static java.util.Arrays.asList; +import static org.assertj.core.api.Assertions.assertThatCode; import static org.assertj.core.api.Assertions.assertThatThrownBy; import static org.mockito.ArgumentMatchers.any; import static org.mockito.ArgumentMatchers.anyString; +import static org.mockito.ArgumentMatchers.eq; import static org.mockito.ArgumentMatchers.same; import static org.mockito.Mockito.mock; import static org.mockito.Mockito.verify; @@ -43,11 +46,10 @@ public class PostgresCharsetHandlerTest { private static final String COLUMN_KEE = "kee"; private static final String COLUMN_NAME = "name"; - - private SqlExecutor sqlExecutor = mock(SqlExecutor.class); - private Connection connection = mock(Connection.class); - private PostgresMetadataReader metadata = mock(PostgresMetadataReader.class); - private PostgresCharsetHandler underTest = new PostgresCharsetHandler(sqlExecutor, metadata); + private final SqlExecutor sqlExecutor = mock(SqlExecutor.class); + private final Connection connection = mock(Connection.class); + private final PostgresMetadataReader metadata = mock(PostgresMetadataReader.class); + private final PostgresCharsetHandler underTest = new PostgresCharsetHandler(sqlExecutor, metadata); @Test public void fresh_install_verifies_that_default_charset_is_utf8() throws SQLException { @@ -91,7 +93,35 @@ public class PostgresCharsetHandlerTest { new String[] {TABLE_PROJECTS, COLUMN_NAME, "" /* unset -> uses db collation */})); // no error - underTest.handle(connection, DatabaseCharsetChecker.State.UPGRADE); + assertThatCode(() -> underTest.handle(connection, DatabaseCharsetChecker.State.UPGRADE)) + .doesNotThrowAnyException(); + + verify(sqlExecutor).select(same(connection), eq("select table_name, column_name," + + " collation_name " + + "from information_schema.columns " + + "where table_schema='public' " + + "and table_name in (" + SqTables.TABLES.stream().map(s -> "'" + s + "'").collect(Collectors.joining(",")) + ") " + + "and udt_name='varchar' order by table_name, column_name"), any(SqlExecutor.StringsConverter.class)); + } + + @Test + public void schema_is_taken_into_account_when_selecting_columns() throws Exception { + answerDefaultCharset("utf8"); + answerSchema("test-schema"); + answerColumns(asList( + new String[] {TABLE_ISSUES, COLUMN_KEE, "utf8"}, + new String[] {TABLE_PROJECTS, COLUMN_NAME, "" /* unset -> uses db collation */})); + + // no error + assertThatCode(() -> underTest.handle(connection, DatabaseCharsetChecker.State.UPGRADE)) + .doesNotThrowAnyException(); + + verify(sqlExecutor).select(same(connection), eq("select table_name, column_name," + + " collation_name " + + "from information_schema.columns " + + "where table_schema='test-schema' " + + "and table_name in (" + SqTables.TABLES.stream().map(s -> "'" + s + "'").collect(Collectors.joining(",")) + ") " + + "and udt_name='varchar' order by table_name, column_name"), any(SqlExecutor.StringsConverter.class)); } @Test @@ -112,7 +142,7 @@ public class PostgresCharsetHandlerTest { public void upgrade_fails_if_default_charset_is_not_utf8() throws Exception { answerDefaultCharset("latin"); answerColumns( - Arrays.asList(new String[] {TABLE_ISSUES, COLUMN_KEE, "utf8"})); + List.of(new String[] {TABLE_ISSUES, COLUMN_KEE, "utf8"})); assertThatThrownBy(() -> underTest.handle(connection, DatabaseCharsetChecker.State.UPGRADE)) .isInstanceOf(MessageException.class) @@ -123,6 +153,10 @@ public class PostgresCharsetHandlerTest { when(metadata.getDefaultCharset(same(connection))).thenReturn(defaultCollation); } + private void answerSchema(String schema) throws SQLException { + when(connection.getSchema()).thenReturn(schema); + } + private void answerColumns(List firstRequest) throws SQLException { when(sqlExecutor.select(same(connection), anyString(), any(SqlExecutor.StringsConverter.class))).thenReturn(firstRequest); } -- 2.39.5