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); }
public DatabaseCharsetChecker(Database db) { this(db, new SqlExecutor()); }
@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 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"); }
public String getDefaultCharset(Connection connection) throws SQLException { return sqlExecutor.selectSingleString(connection, "select pg_encoding_to_char(encoding) from pg_database where datname = current_database()"); } }
private void repairColumnCollation(Connection connection, ColumnDef column, String expectedCollation) throws SQLException { // 1. select the indices defined on this column List<ColumnIndex> indices = metadata.getColumnIndices(connection, column); // 2. drop indices for (ColumnIndex index : indices) { getSqlExecutor().executeDdl(connection, format("DROP INDEX %s.%s", column.getTable(), index.name)); } // 3. alter collation of column String nullability = column.isNullable() ? "NULL" : "NOT NULL"; String size = column.getSize() >= 0 ? String.valueOf(column.getSize()) : "max"; String alterSql = format("ALTER TABLE %s ALTER COLUMN %s %s(%s) COLLATE %s %s", column.getTable(), column.getColumn(), column.getDataType(), size, expectedCollation, nullability); LOGGER.info("Changing collation of column [{}.{}] from {} to {} | sql=", column.getTable(), column.getColumn(), column.getCollation(), expectedCollation, alterSql); getSqlExecutor().executeDdl(connection, alterSql); // 4. re-create indices for (ColumnIndex index : indices) { String uniqueSql = index.unique ? "UNIQUE" : ""; String createIndexSql = format("CREATE %s INDEX %s ON %s (%s)", uniqueSql, index.name, column.getTable(), index.csvColumns); getSqlExecutor().executeDdl(connection, createIndexSql); } }
String getDefaultCollation(Connection connection) throws SQLException { return sqlExecutor.selectSingleString(connection, "SELECT CONVERT(VARCHAR(128), DATABASEPROPERTYEX(DB_NAME(), 'Collation'))"); }
@CheckForNull public final <T> T selectSingleRow(Connection connection, String sql, SqlExecutor.RowConverter<T> rowConverter) throws SQLException { List<T> rows = select(connection, sql, rowConverter); if (rows.isEmpty()) { return null; } if (rows.size() == 1) { return rows.get(0); } throw new IllegalStateException("Expecting only one result for [" + sql + "]"); }
@Test public void upgrade_repairs_indexed_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)); answerIndices( new MssqlCharsetHandler.ColumnIndex("projects_name", false, "name"), // This index is on two columns. Note that it does not make sense for table "projects" ! new MssqlCharsetHandler.ColumnIndex("projects_login_and_name", true, "login,name")); underTest.handle(connection, DatabaseCharsetChecker.State.UPGRADE); verify(sqlExecutor).executeDdl(connection, "DROP INDEX projects.projects_name"); verify(sqlExecutor).executeDdl(connection, "DROP INDEX projects.projects_login_and_name"); verify(sqlExecutor).executeDdl(connection, "ALTER TABLE projects ALTER COLUMN name varchar(10) COLLATE Latin1_General_CS_AS NOT NULL"); verify(sqlExecutor).executeDdl(connection, "CREATE INDEX projects_name ON projects (name)"); verify(sqlExecutor).executeDdl(connection, "CREATE UNIQUE INDEX projects_login_and_name ON projects (login,name)"); }
private void expectUtf8(Connection connection) throws SQLException { // Oracle does not allow to override character set on tables. Only global charset is verified. String charset = getSqlExecutor().selectSingleString(connection, "select value from nls_database_parameters where parameter='NLS_CHARACTERSET'"); if (!containsIgnoreCase(charset, UTF8)) { throw MessageException.of(format("Oracle NLS_CHARACTERSET does not support UTF8: %s", charset)); } } }
@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]; }
public DatabaseCharsetChecker(Database db) { this(db, new SqlExecutor()); }
private void expectUtf8Columns(Connection connection) throws SQLException { // Charset is defined globally and can be overridden on each column. // This request returns all VARCHAR columns. Charset may be empty. // Examples: // issues | key | '' // projects | name | utf8 List<String[]> rows = getSqlExecutor().select(connection, "select table_name, column_name, collation_name " + "from information_schema.columns " + "where table_schema='public' " + "and udt_name='varchar' " + "order by table_name, column_name", new SqlExecutor.StringsConverter(3 /* columns returned by SELECT */)); Set<String> errors = new LinkedHashSet<>(); for (String[] row : rows) { if (!isBlank(row[2]) && !containsIgnoreCase(row[2], UTF8)) { errors.add(format("%s.%s", row[0], row[1])); } } if (!errors.isEmpty()) { throw MessageException.of(format("Database columns [%s] must have UTF8 charset.", Joiner.on(", ").join(errors))); } }
@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()); }
public String getDefaultCharset(Connection connection) throws SQLException { return sqlExecutor.selectSingleString(connection, "select pg_encoding_to_char(encoding) from pg_database where datname = current_database()"); } }
private void answerColumnDef(ColumnDef... columnDefs) throws SQLException { when(sqlExecutor.select(any(Connection.class), anyString(), eq(ColumnDef.ColumnDefRowConverter.INSTANCE))) .thenReturn(asList(columnDefs)); } }
@Test public void executeUpdate_executes_PreparedStatement() throws Exception { dbTester.executeInsert(USERS_DB_TABLE, LOGIN_DB_COLUMN, "the_login", NAME_DB_COLUMN, "the name", IS_ROOT_DB_COLUMN, false); try (Connection connection = dbTester.openConnection()) { underTest.executeDdl(connection, "update users set " + NAME_DB_COLUMN + "='new name' where " + LOGIN_DB_COLUMN + "='the_login'"); } Map<String, Object> row = dbTester.selectFirst("select " + NAME_DB_COLUMN + " from users where " + LOGIN_DB_COLUMN + "='the_login'"); assertThat(row).isNotEmpty(); assertThat(row.get("NAME")).isEqualTo("new name"); }
public String getDefaultCollation(Connection connection) throws SQLException { return sqlExecutor.selectSingleString(connection, "SELECT CONVERT(VARCHAR, DATABASEPROPERTYEX(DB_NAME(), 'Collation'))"); }
private void repairCaseInsensitiveColumns(Connection connection) throws SQLException { // All VARCHAR columns are returned. No need to check database general collation. // Example of row: // issues | kee | utf8 | utf8_bin List<ColumnDef> columns = getSqlExecutor().select(connection, ColumnDef.SELECT_COLUMNS + "FROM INFORMATION_SCHEMA.columns " + "WHERE table_schema=database() and character_set_name is not null and collation_name is not null", ColumnDef.ColumnDefRowConverter.INSTANCE); List<ColumnDef> invalidColumns = columns.stream() .filter(ColumnDef::isInSonarQubeTable) .filter(column -> endsWithIgnoreCase(column.getCollation(), "_ci")) .collect(Collectors.toList()); for (ColumnDef column : invalidColumns) { repairCaseInsensitiveColumn(connection, column); } }
@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()); }