commit f7007ef2665238b66fdc5f152b353814242e54b2
author Kaushik Lingarkar <kaushikl@codeaurora.org> Wed Oct 13 10:35:14 2021 -0700
committer Kaushik Lingarkar <kaushikl@codeaurora.org> Thu Oct 14 11:34:38 2021 -0700
tree f6c5b4b46c05342f0559d0b75a2e3b1b43d2fc8b
parent cc190c7cd9ef73dcdfa336076dc4f4c2db08cebc

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

java/com/google/gerrit/server/schema/Schema_115.java

diff --git a/java/com/google/gerrit/server/schema/Schema_115.java b/java/com/google/gerrit/server/schema/Schema_115.java
index 70bc921..d04723b 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 @@
   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"));

java/com/google/gerrit/server/schema/Schema_139.java

diff --git a/java/com/google/gerrit/server/schema/Schema_139.java b/java/com/google/gerrit/server/schema/Schema_139.java
index cdde7e4..cd018b7 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 @@
         ResultSet rs =
             stmt.executeQuery(
                 "SELECT "
-                    + "account_id, "
+                    + "account_project_watches.account_id, "
                     + "project_name, "
                     + "filter, "
                     + "notify_abandoned_changes, "
@@ -89,7 +89,8 @@
                     + "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 =

java/com/google/gerrit/server/schema/Schema_144.java

diff --git a/java/com/google/gerrit/server/schema/Schema_144.java b/java/com/google/gerrit/server/schema/Schema_144.java
index bb0cbca..7a00749 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 @@
         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);