@Override void handle(Connection connection, DatabaseCharsetChecker.State state) throws SQLException { // PostgreSQL does not have concept of case-sensitive collation. Only charset ("encoding" in postgresql terminology) // must be verified. expectUtf8AsDefault(connection); if (state == DatabaseCharsetChecker.State.UPGRADE || state == DatabaseCharsetChecker.State.STARTUP) { // no need to check columns on fresh installs... as they are not supposed to exist! expectUtf8Columns(connection); } }
List<ColumnDef> getColumnDefs(Connection connection) throws SQLException { return sqlExecutor.select(connection, ColumnDef.SELECT_COLUMNS + "FROM [INFORMATION_SCHEMA].[COLUMNS] " + "WHERE collation_name is not null " + "ORDER BY table_name,column_name", ColumnDef.ColumnDefRowConverter.INSTANCE); }
@Override void handle(Connection connection, DatabaseCharsetChecker.State state) throws SQLException { expectCaseSensitiveDefaultCollation(connection); if (state == DatabaseCharsetChecker.State.UPGRADE || state == DatabaseCharsetChecker.State.STARTUP) { repairColumns(connection); } }
private void repairCaseInsensitiveColumn(Connection connection, ColumnDef column) throws SQLException { String csCollation = toCaseSensitive(column.getCollation()); String nullability = column.isNullable() ? "NULL" : "NOT NULL"; String type = column.getDataType().equalsIgnoreCase(TYPE_LONGTEXT) ? TYPE_LONGTEXT : format("%s(%d)", column.getDataType(), column.getSize()); String alterSql = format("ALTER TABLE %s MODIFY %s %s CHARACTER SET '%s' COLLATE '%s' %s", column.getTable(), column.getColumn(), type, column.getCharset(), csCollation, nullability); LOGGER.info("Changing collation of column [{}.{}] from {} to {} | sql={}", column.getTable(), column.getColumn(), column.getCollation(), csCollation, alterSql); getSqlExecutor().executeDdl(connection, alterSql); }
@Test public void upgrade_checks_that_columns_are_CS_AS() throws SQLException { answerDefaultCollation("Latin1_General_CS_AS"); answerColumnDefs( new ColumnDef(TABLE_ISSUES, COLUMN_KEE, "Latin1_General", "Latin1_General_CS_AS", "varchar", 10, false), new ColumnDef(TABLE_PROJECTS, COLUMN_NAME, "Latin1_General", "Latin1_General_CS_AS", "varchar", 10, false)); // do not fail underTest.handle(connection, DatabaseCharsetChecker.State.UPGRADE); }
@Test public void repair_case_insensitive_column() throws Exception { answerColumnDef( new ColumnDef(TABLE_ISSUES, COLUMN_KEE, "big5_chinese", "big5_chinese_ci", "varchar", 10, false), new ColumnDef(TABLE_PROJECTS, COLUMN_NAME, "latin1", "latin1_swedish_ci", "varchar", 10, false)); underTest.handle(connection, DatabaseCharsetChecker.State.UPGRADE); verify(sqlExecutor).executeDdl(connection, "ALTER TABLE issues MODIFY kee varchar(10) CHARACTER SET 'big5_chinese' COLLATE 'big5_bin' NOT NULL"); verify(sqlExecutor).executeDdl(connection, "ALTER TABLE projects MODIFY name varchar(10) CHARACTER SET 'latin1' COLLATE 'latin1_bin' NOT NULL"); }
@CheckForNull public final String selectSingleString(Connection connection, String sql) throws SQLException { String[] cols = selectSingleRow(connection, sql, new SqlExecutor.StringsConverter(1)); return cols == null ? null : cols[0]; }
@Test public void upgrade_verifies_that_columns_are_utf8_and_case_sensitive() throws Exception { answerColumnDef( new ColumnDef(TABLE_ISSUES, COLUMN_KEE, "utf8", "utf8_bin", "varchar", 10, false), new ColumnDef(TABLE_PROJECTS, COLUMN_NAME, "utf8", "utf8_bin", "varchar", 10, false)); // all columns are utf8 underTest.handle(connection, DatabaseCharsetChecker.State.UPGRADE); }
@Test public void fresh_install_verifies_that_default_collation_is_CS_AS() throws SQLException { answerDefaultCollation("Latin1_General_CS_AS"); underTest.handle(connection, DatabaseCharsetChecker.State.FRESH_INSTALL); verify(metadata).getDefaultCollation(connection); }
@Test public void fresh_install_supports_al32utf8() throws Exception { answerCharset("AL32UTF8"); underTest.handle(connection, DatabaseCharsetChecker.State.FRESH_INSTALL); }
@Override void handle(Connection connection, DatabaseCharsetChecker.State state) throws SQLException { // all the VARCHAR columns have always been created with UTF8 charset on mysql // (since SonarQube 2.12 to be precise). The default charset does not require // to be UTF8. It is not used. No need to verify it. // Still if a column has been accidentally created with a case-insensitive collation, // then we can repair it by moving to the same case-sensitive collation. That should // never occur. if (state == DatabaseCharsetChecker.State.UPGRADE) { repairCaseInsensitiveColumns(connection); } }
public String getDefaultCharset(Connection connection) throws SQLException { return sqlExecutor.selectSingleString(connection, "select pg_encoding_to_char(encoding) from pg_database where datname = current_database()"); } }
@Test public void upgrade_repairs_CI_AI_columns() throws SQLException { answerDefaultCollation("Latin1_General_CS_AS"); answerColumnDefs( new ColumnDef(TABLE_ISSUES, COLUMN_KEE, "Latin1_General", "Latin1_General_CS_AS", "varchar", 10, false), new ColumnDef(TABLE_PROJECTS, COLUMN_NAME, "Latin1_General", "Latin1_General_CI_AI", "varchar", 10, false)); underTest.handle(connection, DatabaseCharsetChecker.State.UPGRADE); verify(sqlExecutor).executeDdl(connection, "ALTER TABLE projects ALTER COLUMN name varchar(10) COLLATE Latin1_General_CS_AS NOT NULL"); }
@Test public void fresh_install_verifies_utf8_charset() throws Exception { answerCharset("UTF8"); underTest.handle(connection, DatabaseCharsetChecker.State.FRESH_INSTALL); }
@Test public void support_the_max_size_of_varchar_column() throws Exception { answerDefaultCollation("Latin1_General_CS_AS"); // returned size is -1 answerColumnDefs(new ColumnDef(TABLE_PROJECTS, COLUMN_NAME, "Latin1_General", "Latin1_General_CI_AI", "nvarchar", -1, false)); answerIndices(); underTest.handle(connection, DatabaseCharsetChecker.State.UPGRADE); verify(sqlExecutor).executeDdl(connection, "ALTER TABLE projects ALTER COLUMN name nvarchar(max) COLLATE Latin1_General_CS_AS NOT NULL"); }
/** * SONAR-7988 */ @Test public void fix_Latin1_CS_AS_columns_created_in_5_x() throws SQLException { answerDefaultCollation("SQL_Latin1_General_CP1_CS_AS"); answerColumnDefs(new ColumnDef(TABLE_PROJECTS, COLUMN_NAME, "Latin1_General", "Latin1_General_CS_AS", "nvarchar", 10, false)); underTest.handle(connection, DatabaseCharsetChecker.State.UPGRADE); verify(sqlExecutor).executeDdl(connection, "ALTER TABLE projects ALTER COLUMN name nvarchar(10) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL"); }
@Test public void do_not_repair_system_tables_of_sql_azure() throws Exception { answerDefaultCollation("Latin1_General_CS_AS"); answerColumnDefs(new ColumnDef("sys.sysusers", COLUMN_NAME, "Latin1_General", "Latin1_General_CI_AI", "varchar", 10, false)); underTest.handle(connection, DatabaseCharsetChecker.State.UPGRADE); verify(sqlExecutor, never()).executeDdl(any(Connection.class), anyString()); }
@Test @UseDataProvider("combinationsOfCsAsAndSuffix") public void repair_case_insensitive_accent_insensitive_combinations_with_or_without_suffix(String collation, String expectedCollation) throws Exception { answerDefaultCollation("Latin1_General_CS_AS"); answerColumnDefs(new ColumnDef(TABLE_ISSUES, COLUMN_KEE, "Latin1_General", collation, "varchar", 10, false)); underTest.handle(connection, DatabaseCharsetChecker.State.UPGRADE); verify(sqlExecutor).executeDdl(connection, "ALTER TABLE issues ALTER COLUMN kee varchar(10) COLLATE " + expectedCollation + " NOT NULL"); }
@Test @UseDataProvider("combinationOfBinAndSuffix") public void do_not_repair_if_collation_contains_BIN(String collation) throws Exception { answerDefaultCollation("Latin1_General_CS_AS"); answerColumnDefs(new ColumnDef(TABLE_PROJECTS, COLUMN_NAME, "Latin1_General", collation, "varchar", 10, false)); underTest.handle(connection, DatabaseCharsetChecker.State.UPGRADE); verify(sqlExecutor, never()).executeDdl(any(Connection.class), anyString()); }
@Test @UseDataProvider("combinationOfBin2AndSuffix") public void do_not_repair_if_collation_contains_BIN2(String collation) throws Exception { answerDefaultCollation("Latin1_General_CS_AS"); answerColumnDefs(new ColumnDef(TABLE_PROJECTS, COLUMN_NAME, "Latin1_General", collation, "varchar", 10, false)); underTest.handle(connection, DatabaseCharsetChecker.State.UPGRADE); verify(sqlExecutor, never()).executeDdl(any(Connection.class), anyString()); }