@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); } }
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))); } }
@VisibleForTesting @CheckForNull CharsetHandler getHandler(Dialect dialect) { switch (dialect.getId()) { case H2.ID: // nothing to check return null; case Oracle.ID: return new OracleCharsetHandler(sqlExecutor); case PostgreSql.ID: return new PostgresCharsetHandler(sqlExecutor, new PostgresMetadataReader(sqlExecutor)); case MySql.ID: return new MysqlCharsetHandler(sqlExecutor); case MsSql.ID: return new MssqlCharsetHandler(sqlExecutor, new MssqlMetadataReader(sqlExecutor)); default: throw new IllegalArgumentException("Database not supported: " + dialect.getId()); } } }
@Test public void column_charset_can_be_empty() throws Exception { answerDefaultCharset("utf8"); answerColumns(asList( new String[] {TABLE_ISSUES, COLUMN_KEE, "utf8"}, new String[] {TABLE_PROJECTS, COLUMN_NAME, "" /* unset -> uses db collation */})); // no error underTest.handle(connection, DatabaseCharsetChecker.State.UPGRADE); }
@VisibleForTesting @CheckForNull CharsetHandler getHandler(Dialect dialect) { switch (dialect.getId()) { case H2.ID: // nothing to check return null; case Oracle.ID: return new OracleCharsetHandler(sqlExecutor); case PostgreSql.ID: return new PostgresCharsetHandler(sqlExecutor, new PostgresMetadataReader(sqlExecutor)); case MySql.ID: return new MysqlCharsetHandler(sqlExecutor); case MsSql.ID: return new MssqlCharsetHandler(sqlExecutor, new MssqlMetadataReader(sqlExecutor)); default: throw new IllegalArgumentException("Database not supported: " + dialect.getId()); } } }
@Test public void fresh_install_verifies_that_default_charset_is_utf8() throws SQLException { answerDefaultCharset("utf8"); underTest.handle(connection, DatabaseCharsetChecker.State.FRESH_INSTALL); // no errors, charset has been verified verify(metadata).getDefaultCharset(same(connection)); verifyZeroInteractions(sqlExecutor); }
@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); } }
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 upgrade_verifies_that_default_charset_and_columns_are_utf8() throws Exception { answerDefaultCharset("utf8"); answerColumns(asList( new String[] {TABLE_ISSUES, COLUMN_KEE, "utf8"}, new String[] {TABLE_PROJECTS, COLUMN_NAME, "utf8"})); underTest.handle(connection, DatabaseCharsetChecker.State.UPGRADE); // no errors, charsets have been verified verify(metadata).getDefaultCharset(same(connection)); }
@Test public void regular_startup_verifies_that_default_charset_and_columns_are_utf8() throws Exception { answerDefaultCharset("utf8"); answerColumns(asList( new String[] {TABLE_ISSUES, COLUMN_KEE, "utf8"}, new String[] {TABLE_PROJECTS, COLUMN_NAME, "utf8"})); underTest.handle(connection, DatabaseCharsetChecker.State.STARTUP); // no errors, charsets have been verified verify(metadata).getDefaultCharset(same(connection)); }
@Test public void upgrade_fails_if_non_utf8_column() throws Exception { // default charset is ok but two columns are not answerDefaultCharset("utf8"); answerColumns(asList( new String[] {TABLE_ISSUES, COLUMN_KEE, "utf8"}, new String[] {TABLE_PROJECTS, COLUMN_KEE, "latin"}, new String[] {TABLE_PROJECTS, COLUMN_NAME, "latin"})); expectedException.expect(MessageException.class); expectedException.expectMessage("Database columns [projects.kee, projects.name] must have UTF8 charset."); underTest.handle(connection, DatabaseCharsetChecker.State.UPGRADE); }
@Test public void upgrade_fails_if_default_charset_is_not_utf8() throws Exception { answerDefaultCharset("latin"); answerColumns( Arrays.<String[]>asList(new String[] {TABLE_ISSUES, COLUMN_KEE, "utf8"})); expectedException.expect(MessageException.class); expectedException.expectMessage("Database charset is latin. It must support UTF8."); underTest.handle(connection, DatabaseCharsetChecker.State.UPGRADE); }