From 4000d244f77a2f99acfc8e8e58571ee13a8a6d77 Mon Sep 17 00:00:00 2001
From: Simon Brandhof <simon.brandhof@sonarsource.com>
Date: Thu, 7 Sep 2017 22:02:47 +0200
Subject: SONAR-9763 remove double join in DB migration 1267

---
 .../v60/PopulateAnalysisUuidOnMeasures.java        | 35 +++++++++++++++-------
 1 file changed, 24 insertions(+), 11 deletions(-)

(limited to 'server/sonar-db-migration/src/main/java/org/sonar')

diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v60/PopulateAnalysisUuidOnMeasures.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v60/PopulateAnalysisUuidOnMeasures.java
index fd213140d08..c7eca704768 100644
--- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v60/PopulateAnalysisUuidOnMeasures.java
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v60/PopulateAnalysisUuidOnMeasures.java
@@ -20,11 +20,13 @@
 package org.sonar.server.platform.db.migration.version.v60;
 
 import java.sql.SQLException;
+import java.util.HashMap;
+import java.util.Map;
 import org.sonar.db.Database;
+import org.sonar.server.platform.db.migration.step.DataChange;
 import org.sonar.server.platform.db.migration.step.MassUpdate;
 import org.sonar.server.platform.db.migration.step.Select;
 import org.sonar.server.platform.db.migration.step.SqlStatement;
-import org.sonar.server.platform.db.migration.step.DataChange;
 
 public class PopulateAnalysisUuidOnMeasures extends DataChange {
 
@@ -34,32 +36,43 @@ public class PopulateAnalysisUuidOnMeasures extends DataChange {
 
   @Override
   public void execute(Context context) throws SQLException {
+    Map<Long, String> rootSnapshotUuids = loadRootSnapshotUuids(context);
+
+
     MassUpdate massUpdate = context.prepareMassUpdate();
     // mysql can take hours if the 2 requests are merged into a single one
-    massUpdate.select("select distinct m.snapshot_id as sId, root_snapshots.uuid as rootUuid " +
+    massUpdate.select("select distinct m.snapshot_id as sId, s.root_snapshot_id as rootSid " +
       "from project_measures m " +
-      "inner join snapshots s on m.snapshot_id=s.id " +
-      "inner join snapshots root_snapshots on s.root_snapshot_id = root_snapshots.id " +
+      "inner join snapshots s on m.snapshot_id = s.id " +
       "where m.analysis_uuid is null " +
       "union " +
-      "select distinct m.snapshot_id as sId, root_snapshots.uuid as rootUuid " +
+      "select distinct m.snapshot_id as sId, s.root_snapshot_id as rootSid " +
       "from project_measures m " +
       "inner join snapshots s on m.snapshot_id=s.id " +
-      "inner join snapshots root_snapshots on s.root_snapshot_id is null and s.id = root_snapshots.id " +
       "where m.analysis_uuid is null"
     );
-    massUpdate.update("update project_measures set analysis_uuid=? where snapshot_id=? and analysis_uuid is null");
+    massUpdate.update("update project_measures set analysis_uuid=? where snapshot_id = ? and analysis_uuid is null");
     massUpdate.rowPluralName("measures");
-    massUpdate.execute(PopulateAnalysisUuidOnMeasures::handle);
+    massUpdate.execute((row, update) -> handleRow(row, update, rootSnapshotUuids));
+  }
+
+  private static Map<Long, String> loadRootSnapshotUuids(Context context) throws SQLException {
+    Map<Long, String> snapshotUuidsByIds = new HashMap<>();
+    context.prepareSelect("select distinct id, uuid from snapshots where depth=0")
+      .scroll(row -> snapshotUuidsByIds.put(row.getLong(1), row.getString(2)));
+    return snapshotUuidsByIds;
   }
 
-  private static boolean handle(Select.Row row, SqlStatement update) throws SQLException {
+  private static boolean handleRow(Select.Row row, SqlStatement update, Map<Long, String> rootSnapshotUuids) throws SQLException {
     long snapshotId = row.getLong(1);
-    String analysisUuid = row.getString(2);
+    Long rootSnapshotId = row.getNullableLong(2);
+    String analysisUuid = rootSnapshotUuids.get(rootSnapshotId == null ? snapshotId : rootSnapshotId);
+    if (analysisUuid == null) {
+      return false;
+    }
 
     update.setString(1, analysisUuid);
     update.setLong(2, snapshotId);
-
     return true;
   }
 
-- 
cgit v1.2.3