From d41a9d2bb2da29bdc8414cfa426772e8a308ffb5 Mon Sep 17 00:00:00 2001
From: Tomas Cohen Arazi <tomascohen@theke.io>
Date: Mon, 25 Apr 2022 10:09:41 -0300
Subject: [PATCH] Bug 30449: (QA follow-up) Report back on problematic
 situations

Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Signed-off-by: Fridolin Somers <fridolin.somers@biblibre.com>

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
---
 .../data/mysql/atomicupdate/bug_30449.pl      | 42 +++++++++++++++++--
 1 file changed, 38 insertions(+), 4 deletions(-)

diff --git a/installer/data/mysql/atomicupdate/bug_30449.pl b/installer/data/mysql/atomicupdate/bug_30449.pl
index 316f4016fa..b9c2f73eb5 100755
--- a/installer/data/mysql/atomicupdate/bug_30449.pl
+++ b/installer/data/mysql/atomicupdate/bug_30449.pl
@@ -13,11 +13,45 @@ return {
                 $dbh->do( q|ALTER TABLE borrower_attribute_types DROP INDEX category_code_fk| );
             }
         }
-        if( !foreign_key_exists('borrower_attribute_types', 'borrower_attribute_types_ibfk_1') ) {
-            if( !index_exists('borrower_attribute_types', 'category_code') ) {
-                $dbh->do( q|ALTER TABLE borrower_attribute_types ADD INDEX category_code (category_code)| );
+
+        if (
+            !foreign_key_exists(
+                'borrower_attribute_types', 'borrower_attribute_types_ibfk_1'
+            )
+          )
+        {
+
+            my $sth = $dbh->prepare(
+                q{
+                SELECT category_code
+                FROM borrower_attribute_types
+                WHERE category_code NOT IN (SELECT categorycode FROM categories);
+            }
+            );
+
+            $sth->execute;
+
+            my @invalid_categories;
+            while ( my $row = $sth->fetchrow_arrayref() ) {
+                push( @invalid_categories, $row->[0] );
+            }
+
+            if (@invalid_categories) {
+                die "The 'borrower_attribute_types' table contains "
+                  . "references to invalid category codes: "
+                  . join( ', ', @invalid_categories );
+            }
+
+            if ( !index_exists( 'borrower_attribute_types', 'category_code' ) )
+            {
+                $dbh->do(q|
+                    ALTER TABLE borrower_attribute_types ADD INDEX category_code (category_code)
+                |);
             }
-            $dbh->do( q|ALTER TABLE borrower_attribute_types ADD CONSTRAINT borrower_attribute_types_ibfk_1 FOREIGN KEY (`category_code`) REFERENCES `categories` (`categorycode`)| );
+            $dbh->do(q|
+                ALTER TABLE borrower_attribute_types
+                    ADD CONSTRAINT borrower_attribute_types_ibfk_1 FOREIGN KEY (`category_code`) REFERENCES `categories` (`categorycode`)
+            |);
         }
     },
 };
-- 
2.39.5