From cba54f24e8ff46b0777facf46e4a16659a600cc7 Mon Sep 17 00:00:00 2001
From: Simon Brandhof <simon.brandhof@sonarsource.com>
Date: Wed, 18 Jan 2017 15:17:19 +0100
Subject: [PATCH] SONAR-8642 fix MSSQL collation in CleanUsurperRootComponents

---
 .../v564/CleanUsurperRootComponents.java      | 29 ++++++++++++++-----
 .../v564/CleanUsurperRootComponentsTest.java  | 24 +++++++++++++++
 2 files changed, 46 insertions(+), 7 deletions(-)

diff --git a/sonar-db/src/main/java/org/sonar/db/version/v564/CleanUsurperRootComponents.java b/sonar-db/src/main/java/org/sonar/db/version/v564/CleanUsurperRootComponents.java
index 148b9de4338..e183a47ea59 100644
--- a/sonar-db/src/main/java/org/sonar/db/version/v564/CleanUsurperRootComponents.java
+++ b/sonar-db/src/main/java/org/sonar/db/version/v564/CleanUsurperRootComponents.java
@@ -19,8 +19,11 @@
  */
 package org.sonar.db.version.v564;
 
+import com.google.common.annotations.VisibleForTesting;
 import java.sql.SQLException;
 import org.sonar.db.Database;
+import org.sonar.db.dialect.Dialect;
+import org.sonar.db.dialect.MsSql;
 import org.sonar.db.version.BaseDataChange;
 import org.sonar.db.version.MassUpdate;
 
@@ -40,15 +43,15 @@ public class CleanUsurperRootComponents extends BaseDataChange {
     cleanSnapshotWithIncorrectRoot(context);
   }
 
-  private static void fixSnapshotScopeAndQualifier(Context context) throws SQLException {
+  private void fixSnapshotScopeAndQualifier(Context context) throws SQLException {
     MassUpdate massUpdate = context.prepareMassUpdate();
-    massUpdate.select("select" +
+    massUpdate.select(fixSqlConditions(getDb().getDialect(), "select" +
       " sn.id,p.scope,p.qualifier" +
       " from" +
       " snapshots sn, projects p" +
       " where" +
       " p.id = sn.project_id" +
-      " and (p.qualifier<>sn.qualifier or p.scope<>sn.scope)");
+      " and (p.qualifier %s <> sn.qualifier %s or p.scope %s <> sn.scope %s)"));
     massUpdate.update("update snapshots set scope=?,qualifier=? where id=?");
     massUpdate.rowPluralName("snapshots with inconsistent scope or qualifier");
     massUpdate.execute((row, update) -> {
@@ -64,15 +67,27 @@ public class CleanUsurperRootComponents extends BaseDataChange {
     });
   }
 
-  private static void cleanUsurperRootComponents(Context context) throws SQLException {
+  /**
+   * Replace placeholders {@code "%s"} so that comparisons of VARCHAR columns
+   * do not fail on MsSQL when column collations are different.
+   */
+  @VisibleForTesting
+  static String fixSqlConditions(Dialect dialect, String sql) {
+    if (MsSql.ID.equals(dialect.getId())) {
+      return sql.replaceAll("%s", "collate database_default");
+    }
+    return sql.replaceAll("%s", "");
+  }
+
+  private void cleanUsurperRootComponents(Context context) throws SQLException {
     MassUpdate massUpdate = context.prepareMassUpdate();
-    massUpdate.select("select p.id,p.uuid from projects p " +
+    massUpdate.select(fixSqlConditions(getDb().getDialect(), "select p.id,p.uuid from projects p " +
       " where" +
-      " p.project_uuid = p.uuid" +
+      " p.project_uuid %s = p.uuid %s" +
       " and not (" +
       " p.scope = 'PRJ'" +
       " and p.qualifier in ('TRK', 'VW', 'DEV')" +
-      " )");
+      " )"));
     massUpdate.update("delete from duplications_index where snapshot_id in (select id from snapshots where project_id=?)");
     massUpdate.update("delete from project_measures where project_id=?");
     massUpdate.update("delete from ce_activity where component_uuid=?");
diff --git a/sonar-db/src/test/java/org/sonar/db/version/v564/CleanUsurperRootComponentsTest.java b/sonar-db/src/test/java/org/sonar/db/version/v564/CleanUsurperRootComponentsTest.java
index e361ef14b75..061e03fd5f5 100644
--- a/sonar-db/src/test/java/org/sonar/db/version/v564/CleanUsurperRootComponentsTest.java
+++ b/sonar-db/src/test/java/org/sonar/db/version/v564/CleanUsurperRootComponentsTest.java
@@ -30,6 +30,11 @@ import org.sonar.api.resources.Qualifiers;
 import org.sonar.api.resources.Scopes;
 import org.sonar.api.utils.System2;
 import org.sonar.db.DbTester;
+import org.sonar.db.dialect.Dialect;
+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.Long.parseLong;
 import static java.lang.String.valueOf;
@@ -174,6 +179,25 @@ public class CleanUsurperRootComponentsTest {
       .forEach(s -> assertThat(db.countRowsOfTable(s)).describedAs("table " + s).isEqualTo(0));
   }
 
+  @Test
+  public void SELECT_sql_should_ignore_collations_on_mssql() {
+    String sql = CleanUsurperRootComponents.fixSqlConditions(new MsSql(), "select * from foo where a %s = b %s");
+
+    assertThat(sql).isEqualTo("select * from foo where a collate database_default = b collate database_default");
+  }
+
+  @Test
+  public void SELECT_sql_should_not_contain_mssql_hints_on_non_mssql() {
+    assertSqlIsSanitizedOnNonMssql(new Oracle());
+    assertSqlIsSanitizedOnNonMssql(new PostgreSql());
+    assertSqlIsSanitizedOnNonMssql(new MySql());
+  }
+
+  private static void assertSqlIsSanitizedOnNonMssql(Dialect dialect) {
+    String sql = CleanUsurperRootComponents.fixSqlConditions(dialect, "select * from foo where a %s = b %s");
+    assertThat(sql).isEqualTo("select * from foo where a  = b ");
+  }
+
   private void insertDuplicationsIndex(long snapshotId) {
     db.executeInsert(
       "duplications_index",
-- 
2.39.5