SONAR-7549 SONAR-6171 verifies UTF8 charset and case-sensitive collation

8 files changed, 530 insertions, 241 deletions

diff --git a/sonar-db/src/main/java/org/sonar/db/CollationChecker.java b/sonar-db/src/main/java/org/sonar/db/CollationChecker.java
deleted file mode 100644
index b09d9233c3b..00000000000
--- a/sonar-db/src/main/java/org/sonar/db/CollationChecker.java
+++ /dev/null
@@ -1,241 +0,0 @@
-/*
- * SonarQube
- * Copyright (C) 2009-2016 SonarSource SA
- * mailto:contact 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.db;
-
-import com.google.common.annotations.VisibleForTesting;
-import com.google.common.base.Joiner;
-import java.sql.Connection;
-import java.sql.ResultSet;
-import java.sql.SQLException;
-import java.sql.Statement;
-import java.util.ArrayList;
-import java.util.List;
-import javax.annotation.CheckForNull;
-import org.apache.commons.lang.StringUtils;
-import org.picocontainer.Startable;
-import org.sonar.api.utils.MessageException;
-import org.sonar.api.utils.log.Loggers;
-import org.sonar.db.dialect.H2;
-import org.sonar.db.dialect.MsSql;
-import org.sonar.db.dialect.MySql;
-import org.sonar.db.dialect.Oracle;
-import org.sonar.db.dialect.PostgreSql;
-
-import static java.lang.String.format;
-import static org.apache.commons.lang.StringUtils.containsIgnoreCase;
-import static org.apache.commons.lang.StringUtils.endsWithIgnoreCase;
-
-/**
- * SONAR-6171
- * Check that database has UTF8 character set and case-sensitive collation.
- * As obviously tables must be checked after being created, this component
- * must not be executed at the same time as {@link DatabaseChecker}.
- */
-public class CollationChecker implements Startable {
-
-  private static final String UTF8 = "utf8";
-  private final Database db;
-  private final StatementExecutor statementExecutor;
-
-  public CollationChecker(Database db) {
-    this(db, new StatementExecutor());
-  }
-
-  @VisibleForTesting
-  CollationChecker(Database db, StatementExecutor statementExecutor) {
-    this.db = db;
-    this.statementExecutor = statementExecutor;
-  }
-
-  @Override
-  public void start() {
-    try {
-      Loggers.get(getClass()).info("Verify database collation");
-      check();
-    } catch (SQLException e) {
-      throw new IllegalStateException(e);
-    }
-  }
-
-  @Override
-  public void stop() {
-    // nothing to do
-  }
-
-  private void check() throws SQLException {
-    try (Connection connection = db.getDataSource().getConnection()) {
-      switch (db.getDialect().getId()) {
-        case H2.ID:
-          // nothing to check
-          break;
-        case Oracle.ID:
-          checkOracle(connection);
-          break;
-        case PostgreSql.ID:
-          checkPostgreSql(connection);
-          break;
-        case MySql.ID:
-          checkMySql(connection);
-          break;
-        case MsSql.ID:
-          checkMsSql(connection);
-          break;
-        default:
-          throw new IllegalArgumentException("Database not supported: " + db.getDialect().getId());
-      }
-    }
-  }
-
-  /**
-   * Oracle does not allow to override character set on tables. Only global charset is verified.
-   */
-  private void checkOracle(Connection connection) throws SQLException {
-    String charset = selectSingleCell(connection, "select value from nls_database_parameters where parameter='NLS_CHARACTERSET'");
-    String sort = selectSingleCell(connection, "select value from nls_database_parameters where parameter='NLS_SORT'");
-    if (!containsIgnoreCase(charset, UTF8) || !"BINARY".equalsIgnoreCase(sort)) {
-      throw MessageException.of(format("Oracle must be have UTF8 charset and BINARY sort. NLS_CHARACTERSET is %s and NLS_SORT is %s.", charset, sort));
-    }
-  }
-
-  /**
-   * PostgreSQL does not support case-insensitive collations. Only character set must be verified.
-   */
-  private void checkPostgreSql(Connection connection) throws SQLException {
-    // Character set is defined globally and can be overridden on each column.
-    // This request returns all VARCHAR columns. Collation may be empty.
-    // Examples:
-    // issues | key | ''
-    // projects | name | utf8
-    List<String[]> rows = 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", 3);
-    boolean mustCheckGlobalCollation = false;
-    List<String> errors = new ArrayList<>();
-    for (String[] row : rows) {
-      if (StringUtils.isBlank(row[2])) {
-        mustCheckGlobalCollation = true;
-      } else if (!containsIgnoreCase(row[2], UTF8)) {
-        errors.add(format("%s.%s", row[0], row[1]));
-      }
-    }
-
-    if (mustCheckGlobalCollation) {
-      String charset = selectSingleCell(connection, "SELECT pg_encoding_to_char(encoding) FROM pg_database WHERE datname = current_database()");
-      if (!containsIgnoreCase(charset, UTF8)) {
-        throw MessageException.of(format("Database charset is %s. It must be UTF8.", charset));
-      }
-    }
-    if (!errors.isEmpty()) {
-      throw MessageException.of(format("Database columns [%s] must have UTF8 charset.", Joiner.on(", ").join(errors)));
-    }
-  }
-
-  /**
-   * Check VARCHAR columns
-   */
-  private void checkMySql(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<String[]> rows = select(connection,
-      "SELECT table_name, column_name, character_set_name, collation_name " +
-        "FROM INFORMATION_SCHEMA.columns " +
-        "WHERE table_schema=database() and character_set_name is not null and collation_name is not null", 4 /* columns */);
-    List<String> errors = new ArrayList<>();
-    for (String[] row : rows) {
-      if (!containsIgnoreCase(row[2], UTF8) || endsWithIgnoreCase(row[3], "_ci")) {
-        errors.add(format("%s.%s", row[0], row[1]));
-      }
-    }
-    if (!errors.isEmpty()) {
-      throw MessageException.of(format("UTF8 charset and case-sensitive collation are required for database columns [%s]", Joiner.on(", ").join(errors)));
-    }
-  }
-
-  private void checkMsSql(Connection connection) throws SQLException {
-    // All VARCHAR columns are returned. No need to check database general collation.
-    // Example of row:
-    // issues | kee | Latin1_General_CS_AS
-    List<String[]> rows = select(connection,
-      "SELECT table_name, column_name, collation_name " +
-        "FROM [INFORMATION_SCHEMA].[COLUMNS] " +
-        "WHERE collation_name is not null " +
-        "ORDER BY table_name,column_name", 3 /* columns */);
-    List<String> errors = new ArrayList<>();
-    for (String[] row : rows) {
-      if (!endsWithIgnoreCase(row[2], "_CS_AS")) {
-        errors.add(row[0] + "." + row[1]);
-      }
-    }
-    if (!errors.isEmpty()) {
-      throw MessageException.of(format("Case-sensitive and accent-sensitive charset (CS_AS) is required for database columns [%s]", Joiner.on(", ").join(errors)));
-    }
-  }
-
-  @CheckForNull
-  private String selectSingleCell(Connection connection, String sql) throws SQLException {
-    String[] cols = selectSingleRow(connection, sql, 1);
-    return cols == null ? null : cols[0];
-  }
-
-  @CheckForNull
-  private String[] selectSingleRow(Connection connection, String sql, int columns) throws SQLException {
-    List<String[]> rows = select(connection, sql, columns);
-    if (rows.isEmpty()) {
-      return null;
-    }
-    if (rows.size() == 1) {
-      return rows.get(0);
-    }
-    throw new IllegalStateException("Expecting only one result for [" + sql + "]");
-  }
-
-  private List<String[]> select(Connection connection, String sql, int columns) throws SQLException {
-    return statementExecutor.executeQuery(connection, sql, columns);
-  }
-
-  @VisibleForTesting
-  static class StatementExecutor {
-    List<String[]> executeQuery(Connection connection, String sql, int columns) throws SQLException {
-      Statement stmt = null;
-      ResultSet rs = null;
-      try {
-        stmt = connection.createStatement();
-        rs = stmt.executeQuery(sql);
-        List<String[]> result = new ArrayList<>();
-        while (rs.next()) {
-          String[] row = new String[columns];
-          for (int i = 0; i < columns; i++) {
-            row[i] = DatabaseUtils.getString(rs, i + 1);
-          }
-          result.add(row);
-        }
-        return result;
-
-      } finally {
-        DatabaseUtils.closeQuietly(rs);
-        DatabaseUtils.closeQuietly(stmt);
-      }
-    }
-  }
-
-}
diff --git a/sonar-db/src/main/java/org/sonar/db/charset/CharsetHandler.java b/sonar-db/src/main/java/org/sonar/db/charset/CharsetHandler.java
new file mode 100644
index 00000000000..a3aceb0660c
--- /dev/null
+++ b/sonar-db/src/main/java/org/sonar/db/charset/CharsetHandler.java
@@ -0,0 +1,90 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2016 SonarSource SA
+ * mailto:contact 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.db.charset;
+
+import com.google.common.annotations.VisibleForTesting;
+import java.sql.Connection;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.ArrayList;
+import java.util.List;
+import javax.annotation.CheckForNull;
+import org.sonar.db.DatabaseUtils;
+
+abstract class CharsetHandler {
+
+  protected static final String UTF8 = "utf8";
+
+  private final SelectExecutor selectExecutor;
+
+  protected CharsetHandler(SelectExecutor selectExecutor) {
+    this.selectExecutor = selectExecutor;
+  }
+
+  abstract void handle(Connection connection, boolean enforceUtf8) throws SQLException;
+
+  @CheckForNull
+  protected final String selectSingleCell(Connection connection, String sql) throws SQLException {
+    String[] cols = selectSingleRow(connection, sql, 1);
+    return cols == null ? null : cols[0];
+  }
+
+  @CheckForNull
+  protected final String[] selectSingleRow(Connection connection, String sql, int columns) throws SQLException {
+    List<String[]> rows = select(connection, sql, columns);
+    if (rows.isEmpty()) {
+      return null;
+    }
+    if (rows.size() == 1) {
+      return rows.get(0);
+    }
+    throw new IllegalStateException("Expecting only one result for [" + sql + "]");
+  }
+
+  protected final List<String[]> select(Connection connection, String sql, int columns) throws SQLException {
+    return selectExecutor.executeQuery(connection, sql, columns);
+  }
+
+  @VisibleForTesting
+  static class SelectExecutor {
+    List<String[]> executeQuery(Connection connection, String sql, int columns) throws SQLException {
+      Statement stmt = null;
+      ResultSet rs = null;
+      try {
+        stmt = connection.createStatement();
+        rs = stmt.executeQuery(sql);
+        List<String[]> result = new ArrayList<>();
+        while (rs.next()) {
+          String[] row = new String[columns];
+          for (int i = 0; i < columns; i++) {
+            row[i] = DatabaseUtils.getString(rs, i + 1);
+          }
+          result.add(row);
+        }
+        return result;
+
+      } finally {
+        DatabaseUtils.closeQuietly(rs);
+        DatabaseUtils.closeQuietly(stmt);
+      }
+    }
+  }
+}
diff --git a/sonar-db/src/main/java/org/sonar/db/charset/DatabaseCharsetChecker.java b/sonar-db/src/main/java/org/sonar/db/charset/DatabaseCharsetChecker.java
new file mode 100644
index 00000000000..de54bcaf7c0
--- /dev/null
+++ b/sonar-db/src/main/java/org/sonar/db/charset/DatabaseCharsetChecker.java
@@ -0,0 +1,82 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2016 SonarSource SA
+ * mailto:contact 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.db.charset;
+
+import com.google.common.annotations.VisibleForTesting;
+import java.sql.Connection;
+import java.sql.SQLException;
+import org.sonar.db.Database;
+import org.sonar.db.dialect.H2;
+import org.sonar.db.dialect.MsSql;
+import org.sonar.db.dialect.MySql;
+import org.sonar.db.dialect.Oracle;
+import org.sonar.db.dialect.PostgreSql;
+
+/**
+ * On fresh installations, checks that all db columns are UTF8. On all installations on MySQL or MSSQL,
+ * whatever fresh or upgrade, fixes case-insensitive columns by converting them to
+ * case-sensitive.
+ *
+ * See SONAR-6171 and SONAR-7549
+ */
+public class DatabaseCharsetChecker {
+
+  private final Database db;
+  private final CharsetHandler.SelectExecutor selectExecutor;
+
+  public DatabaseCharsetChecker(Database db) {
+    this(db, new CharsetHandler.SelectExecutor());
+  }
+
+  @VisibleForTesting
+  DatabaseCharsetChecker(Database db, CharsetHandler.SelectExecutor selectExecutor) {
+    this.db = db;
+    this.selectExecutor = selectExecutor;
+  }
+
+  public void check(boolean enforceUtf8) {
+    try {
+      try (Connection connection = db.getDataSource().getConnection()) {
+        switch (db.getDialect().getId()) {
+          case H2.ID:
+            // nothing to check
+            break;
+          case Oracle.ID:
+            new OracleCharsetHandler(selectExecutor).handle(connection, enforceUtf8);
+            break;
+          case PostgreSql.ID:
+            new PostgresCharsetHandler(selectExecutor).handle(connection, enforceUtf8);
+            break;
+          case MySql.ID:
+            new MysqlCharsetHandler(selectExecutor).handle(connection, enforceUtf8);
+            break;
+          case MsSql.ID:
+            new MssqlCharsetHandler(selectExecutor).handle(connection, enforceUtf8);
+            break;
+          default:
+            throw new IllegalArgumentException("Database not supported: " + db.getDialect().getId());
+        }
+      }
+    } catch (SQLException e) {
+      throw new IllegalStateException(e);
+    }
+  }
+
+}
diff --git a/sonar-db/src/main/java/org/sonar/db/charset/MssqlCharsetHandler.java b/sonar-db/src/main/java/org/sonar/db/charset/MssqlCharsetHandler.java
new file mode 100644
index 00000000000..a844e739cad
--- /dev/null
+++ b/sonar-db/src/main/java/org/sonar/db/charset/MssqlCharsetHandler.java
@@ -0,0 +1,64 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2016 SonarSource SA
+ * mailto:contact 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.db.charset;
+
+import com.google.common.base.Joiner;
+import java.sql.Connection;
+import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.List;
+import org.sonar.api.utils.MessageException;
+import org.sonar.api.utils.log.Loggers;
+
+import static java.lang.String.format;
+import static org.apache.commons.lang.StringUtils.endsWithIgnoreCase;
+
+class MssqlCharsetHandler extends CharsetHandler {
+
+  protected MssqlCharsetHandler(SelectExecutor selectExecutor) {
+    super(selectExecutor);
+  }
+
+  @Override
+  void handle(Connection connection, boolean enforceUtf8) throws SQLException {
+    Loggers.get(getClass()).info("Verify that database collation is case-sensitive and accent-sensitive");
+    checkCollation(connection);
+  }
+
+  private void checkCollation(Connection connection) throws SQLException {
+    // All VARCHAR columns are returned. No need to check database general collation.
+    // Example of row:
+    // issues | kee | Latin1_General_CS_AS
+    List<String[]> rows = select(connection,
+      "SELECT table_name, column_name, collation_name " +
+        "FROM [INFORMATION_SCHEMA].[COLUMNS] " +
+        "WHERE collation_name is not null " +
+        "ORDER BY table_name,column_name", 3 /* columns */);
+    List<String> errors = new ArrayList<>();
+    for (String[] row : rows) {
+      if (!endsWithIgnoreCase(row[2], "_CS_AS")) {
+        errors.add(row[0] + "." + row[1]);
+      }
+    }
+    if (!errors.isEmpty()) {
+      throw MessageException.of(format("Case-sensitive and accent-sensitive collation (CS_AS) is required for database columns [%s]", Joiner.on(", ").join(errors)));
+    }
+  }
+}
diff --git a/sonar-db/src/main/java/org/sonar/db/charset/MysqlCharsetHandler.java b/sonar-db/src/main/java/org/sonar/db/charset/MysqlCharsetHandler.java
new file mode 100644
index 00000000000..7fda519b8ca
--- /dev/null
+++ b/sonar-db/src/main/java/org/sonar/db/charset/MysqlCharsetHandler.java
@@ -0,0 +1,138 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2016 SonarSource SA
+ * mailto:contact 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.db.charset;
+
+import com.google.common.annotations.VisibleForTesting;
+import com.google.common.base.Joiner;
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.List;
+import org.apache.commons.lang.StringUtils;
+import org.sonar.api.utils.MessageException;
+import org.sonar.api.utils.log.Loggers;
+
+import static java.lang.String.format;
+import static org.apache.commons.lang.StringUtils.containsIgnoreCase;
+import static org.apache.commons.lang.StringUtils.endsWithIgnoreCase;
+import static org.sonar.db.DatabaseUtils.closeQuietly;
+
+class MysqlCharsetHandler extends CharsetHandler {
+
+  private final CollationEditor collationEditor;
+
+  protected MysqlCharsetHandler(SelectExecutor selectExecutor) {
+    this(selectExecutor, new CollationEditor());
+  }
+
+  @VisibleForTesting
+  MysqlCharsetHandler(SelectExecutor selectExecutor, CollationEditor editor) {
+    super(selectExecutor);
+    this.collationEditor = editor;
+  }
+
+  @Override
+  void handle(Connection connection, boolean enforceUtf8) throws SQLException {
+    String message = "Verify that database collation is case-sensitive";
+    if (enforceUtf8) {
+      message = "Verify that database collation is UTF8";
+    }
+    Loggers.get(getClass()).info(message);
+    checkCollation(connection, enforceUtf8);
+  }
+
+  private void checkCollation(Connection connection, boolean enforceUtf8) throws SQLException {
+    // All VARCHAR columns are returned. No need to check database general collation.
+    // Example of row:
+    // issues | kee | utf8 | utf8_bin
+    List<String[]> rows = select(connection,
+      "SELECT table_name, column_name, character_set_name, collation_name " +
+        "FROM INFORMATION_SCHEMA.columns " +
+        "WHERE table_schema=database() and character_set_name is not null and collation_name is not null", 4 /* columns */);
+    List<String> utf8Errors = new ArrayList<>();
+    for (String[] row : rows) {
+      String table = row[0];
+      String column = row[1];
+      String charset = row[2];
+      String collation = row[3];
+      if (enforceUtf8 && !containsIgnoreCase(charset, UTF8)) {
+        utf8Errors.add(format("%s.%s", table, column));
+      } else if (endsWithIgnoreCase(collation, "_ci")) {
+        repairCaseInsensitiveColumn(connection, table, column, collation);
+      }
+    }
+    if (!utf8Errors.isEmpty()) {
+      throw MessageException.of(format("UTF8 case-sensitive collation is required for database columns [%s]", Joiner.on(", ").join(utf8Errors)));
+    }
+  }
+
+  private void repairCaseInsensitiveColumn(Connection connection, String table, String column, String ciCollation)
+    throws SQLException {
+    String csCollation = toCaseSensitive(ciCollation);
+    Loggers.get(getClass()).info("Changing collation of column [{}.{}] from {} to {}", table, column, ciCollation, csCollation);
+    collationEditor.alter(connection, table, column, csCollation);
+  }
+
+  @VisibleForTesting
+  static String toCaseSensitive(String caseInsensitiveCollation) {
+    // example: big5_chinese_ci becomes big5_bin
+    return StringUtils.substringBefore(caseInsensitiveCollation, "_") + "_bin";
+  }
+
+  @VisibleForTesting
+  static class CollationEditor {
+    void alter(Connection connection, String table, String column, String csCollation) throws SQLException {
+      String charset;
+      String dataType;
+      boolean isNullable;
+      int length;
+      PreparedStatement stmt = null;
+      ResultSet rs = null;
+      try {
+        stmt = connection.prepareStatement("SELECT character_set_name, data_type, is_nullable, character_maximum_length " +
+          "FROM INFORMATION_SCHEMA.columns " +
+          "WHERE table_schema=database() and table_name=? and column_name=?");
+        stmt.setString(1, table);
+        stmt.setString(2, column);
+        rs = stmt.executeQuery();
+        rs.next();
+        charset = rs.getString(1);
+        dataType = rs.getString(2);
+        isNullable = rs.getBoolean(3);
+        length = rs.getInt(4);
+      } finally {
+        closeQuietly(stmt);
+        closeQuietly(rs);
+      }
+
+      try {
+        String nullability = isNullable ? "NULL" : "NOT NULL";
+        String alter = format("ALTER TABLE %s MODIFY %s %s(%d) CHARACTER SET '%s' COLLATE '%s' %s",
+          table, column, dataType, length, charset, csCollation, nullability);
+        stmt = connection.prepareStatement(alter);
+        stmt.executeUpdate();
+      } finally {
+        closeQuietly(stmt);
+      }
+    }
+  }
+}
diff --git a/sonar-db/src/main/java/org/sonar/db/charset/OracleCharsetHandler.java b/sonar-db/src/main/java/org/sonar/db/charset/OracleCharsetHandler.java
new file mode 100644
index 00000000000..225ae159743
--- /dev/null
+++ b/sonar-db/src/main/java/org/sonar/db/charset/OracleCharsetHandler.java
@@ -0,0 +1,52 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2016 SonarSource SA
+ * mailto:contact 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.db.charset;
+
+import java.sql.Connection;
+import java.sql.SQLException;
+import org.sonar.api.utils.MessageException;
+import org.sonar.api.utils.log.Loggers;
+
+import static java.lang.String.format;
+import static org.apache.commons.lang.StringUtils.containsIgnoreCase;
+
+class OracleCharsetHandler extends CharsetHandler {
+
+  protected OracleCharsetHandler(SelectExecutor selectExecutor) {
+    super(selectExecutor);
+  }
+
+  @Override
+  public void handle(Connection connection, boolean enforceUtf8) throws SQLException {
+    // Oracle does not allow to override character set on tables. Only global charset is verified.
+    if (enforceUtf8) {
+      Loggers.get(getClass()).info("Verify that database charset is UTF8");
+      checkUtf8(connection);
+    }
+  }
+
+  private void checkUtf8(Connection connection) throws SQLException {
+    String charset = selectSingleCell(connection, "select value from nls_database_parameters where parameter='NLS_CHARACTERSET'");
+    String sort = selectSingleCell(connection, "select value from nls_database_parameters where parameter='NLS_SORT'");
+    if (!containsIgnoreCase(charset, UTF8) || !"BINARY".equalsIgnoreCase(sort)) {
+      throw MessageException.of(format("Oracle must be have UTF8 charset and BINARY sort. NLS_CHARACTERSET is %s and NLS_SORT is %s.", charset, sort));
+    }
+  }
+}
diff --git a/sonar-db/src/main/java/org/sonar/db/charset/PostgresCharsetHandler.java b/sonar-db/src/main/java/org/sonar/db/charset/PostgresCharsetHandler.java
new file mode 100644
index 00000000000..f7917fb0f80
--- /dev/null
+++ b/sonar-db/src/main/java/org/sonar/db/charset/PostgresCharsetHandler.java
@@ -0,0 +1,80 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2016 SonarSource SA
+ * mailto:contact 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.db.charset;
+
+import com.google.common.base.Joiner;
+import java.sql.Connection;
+import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.List;
+import org.apache.commons.lang.StringUtils;
+import org.sonar.api.utils.MessageException;
+import org.sonar.api.utils.log.Loggers;
+
+import static java.lang.String.format;
+import static org.apache.commons.lang.StringUtils.containsIgnoreCase;
+
+class PostgresCharsetHandler extends CharsetHandler {
+
+  protected PostgresCharsetHandler(SelectExecutor selectExecutor) {
+    super(selectExecutor);
+  }
+
+  @Override
+  void handle(Connection connection, boolean enforceUtf8) throws SQLException {
+    // PostgreSQL does not support case-insensitive collations. Only charset must be verified.
+    if (enforceUtf8) {
+      Loggers.get(getClass()).info("Verify that database collation supports UTF8");
+      checkUtf8(connection);
+    }
+  }
+
+  private void checkUtf8(Connection connection) throws SQLException {
+    // Character set is defined globally and can be overridden on each column.
+    // This request returns all VARCHAR columns. Collation may be empty.
+    // Examples:
+    // issues | key | ''
+    // projects | name | utf8
+    List<String[]> rows = 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", 3);
+    boolean mustCheckGlobalCollation = false;
+    List<String> errors = new ArrayList<>();
+    for (String[] row : rows) {
+      if (StringUtils.isBlank(row[2])) {
+        mustCheckGlobalCollation = true;
+      } else if (!containsIgnoreCase(row[2], UTF8)) {
+        errors.add(format("%s.%s", row[0], row[1]));
+      }
+    }
+
+    if (mustCheckGlobalCollation) {
+      String charset = selectSingleCell(connection, "SELECT pg_encoding_to_char(encoding) FROM pg_database WHERE datname = current_database()");
+      if (!containsIgnoreCase(charset, UTF8)) {
+        throw MessageException.of(format("Database collation is %s. It must support UTF8.", charset));
+      }
+    }
+    if (!errors.isEmpty()) {
+      throw MessageException.of(format("Database columns [%s] must support UTF8 collation.", Joiner.on(", ").join(errors)));
+    }
+  }
+}
diff --git a/sonar-db/src/main/java/org/sonar/db/charset/package-info.java b/sonar-db/src/main/java/org/sonar/db/charset/package-info.java
new file mode 100644
index 00000000000..25a4faf160e
--- /dev/null
+++ b/sonar-db/src/main/java/org/sonar/db/charset/package-info.java
@@ -0,0 +1,24 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2016 SonarSource SA
+ * mailto:contact 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.
+ */
+@ParametersAreNonnullByDefault
+package org.sonar.db.charset;
+
+import javax.annotation.ParametersAreNonnullByDefault;
+