Update schemas 115,139,144 to ignore entries not in the 'accounts' table

Creating user refs for accounts available in the tables
'account_diff_preferences', account_project_watches and
account_external_ids tables, but not in 'accounts' table
is undesirable. Such entries must be ignored as they point
towards data inconsistency.

Updates to schemas 115,139 and 144 impact the upgrade of
Gerrit to versions 2.12, 2.14 and 2.15 respectively.

Change-Id: Iaac3fb67ff7ae0ff19ad72599bab6dbfaf164a1f

3 files changed, 10 insertions, 5 deletions

diff --git a/java/com/google/gerrit/server/schema/Schema_115.java b/java/com/google/gerrit/server/schema/Schema_115.java
index 70bc9215f4..d04723b2ac 100644
--- a/java/com/google/gerrit/server/schema/Schema_115.java
+++ b/java/com/google/gerrit/server/schema/Schema_115.java
@@ -72,7 +72,10 @@ public class Schema_115 extends SchemaVersion {
   protected void migrateData(ReviewDb db, UpdateUI ui) throws OrmException, SQLException {
     Map<Account.Id, DiffPreferencesInfo> imports = new HashMap<>();
     try (Statement stmt = ((JdbcSchema) db).getConnection().createStatement();
-        ResultSet rs = stmt.executeQuery("SELECT * FROM account_diff_preferences")) {
+        ResultSet rs =
+            stmt.executeQuery(
+                "SELECT * FROM account_diff_preferences JOIN accounts ON "
+                    + "account_diff_preferences.id=accounts.account_id")) {
       Set<String> availableColumns = getColumns(rs);
       while (rs.next()) {
         Account.Id accountId = new Account.Id(rs.getInt("id"));
diff --git a/java/com/google/gerrit/server/schema/Schema_139.java b/java/com/google/gerrit/server/schema/Schema_139.java
index cdde7e4b22..cd018b7cbe 100644
--- a/java/com/google/gerrit/server/schema/Schema_139.java
+++ b/java/com/google/gerrit/server/schema/Schema_139.java
@@ -81,7 +81,7 @@ public class Schema_139 extends SchemaVersion {
         ResultSet rs =
             stmt.executeQuery(
                 "SELECT "
-                    + "account_id, "
+                    + "account_project_watches.account_id, "
                     + "project_name, "
                     + "filter, "
                     + "notify_abandoned_changes, "
@@ -89,7 +89,8 @@ public class Schema_139 extends SchemaVersion {
                     + "notify_new_changes, "
                     + "notify_new_patch_sets, "
                     + "notify_submitted_changes "
-                    + "FROM account_project_watches")) {
+                    + "FROM account_project_watches "
+                    + "JOIN accounts ON account_project_watches.account_id=accounts.account_id")) {
       while (rs.next()) {
         Account.Id accountId = new Account.Id(rs.getInt(1));
         ProjectWatch.Builder b =
diff --git a/java/com/google/gerrit/server/schema/Schema_144.java b/java/com/google/gerrit/server/schema/Schema_144.java
index bb0cbcafdb..7a00749e01 100644
--- a/java/com/google/gerrit/server/schema/Schema_144.java
+++ b/java/com/google/gerrit/server/schema/Schema_144.java
@@ -63,11 +63,12 @@ public class Schema_144 extends SchemaVersion {
         ResultSet rs =
             stmt.executeQuery(
                 "SELECT "
-                    + "account_id, "
+                    + "account_external_ids.account_id, "
                     + "email_address, "
                     + "password, "
                     + "external_id "
-                    + "FROM account_external_ids")) {
+                    + "FROM account_external_ids "
+                    + "JOIN accounts ON account_external_ids.account_id=accounts.account_id")) {
       while (rs.next()) {
         Account.Id accountId = new Account.Id(rs.getInt(1));
         String email = rs.getString(2);