|
Line 0
Link Here
|
|
|
1 |
use Modern::Perl; |
| 2 |
use Koha::Installer::Output qw(say_warning say_success say_info); |
| 3 |
|
| 4 |
return { |
| 5 |
bug_number => "41246", |
| 6 |
description => "Add foreign key constraints to overduerules table and cascade on delete", |
| 7 |
up => sub { |
| 8 |
my ($args) = @_; |
| 9 |
my ( $dbh, $out ) = @{$args}{qw(dbh out)}; |
| 10 |
|
| 11 |
# Check for and report orphaned branchcode records |
| 12 |
my $orphaned_branch = $dbh->selectrow_array( |
| 13 |
q{ |
| 14 |
SELECT COUNT(*) FROM overduerules |
| 15 |
WHERE branchcode != '' |
| 16 |
AND branchcode NOT IN (SELECT branchcode FROM branches) |
| 17 |
} |
| 18 |
); |
| 19 |
|
| 20 |
if ($orphaned_branch) { |
| 21 |
say_warning( |
| 22 |
$out, |
| 23 |
"Found $orphaned_branch orphaned overduerules records with invalid branchcode - removing" |
| 24 |
); |
| 25 |
$dbh->do( |
| 26 |
q{ |
| 27 |
DELETE FROM overduerules |
| 28 |
WHERE branchcode != '' |
| 29 |
AND branchcode NOT IN (SELECT branchcode FROM branches) |
| 30 |
} |
| 31 |
); |
| 32 |
} |
| 33 |
|
| 34 |
# Check for and report orphaned categorycode records |
| 35 |
my $orphaned_category = $dbh->selectrow_array( |
| 36 |
q{ |
| 37 |
SELECT COUNT(*) FROM overduerules |
| 38 |
WHERE categorycode != '' |
| 39 |
AND categorycode NOT IN (SELECT categorycode FROM categories) |
| 40 |
} |
| 41 |
); |
| 42 |
|
| 43 |
if ($orphaned_category) { |
| 44 |
say_warning( |
| 45 |
$out, |
| 46 |
"Found $orphaned_category orphaned overduerules records with invalid categorycode - removing" |
| 47 |
); |
| 48 |
$dbh->do( |
| 49 |
q{ |
| 50 |
DELETE FROM overduerules |
| 51 |
WHERE categorycode != '' |
| 52 |
AND categorycode NOT IN (SELECT categorycode FROM categories) |
| 53 |
} |
| 54 |
); |
| 55 |
} |
| 56 |
|
| 57 |
# Add foreign key for branchcode |
| 58 |
unless ( foreign_key_exists( 'overduerules', 'overduerules_ibfk_branchcode' ) ) { |
| 59 |
$dbh->do( |
| 60 |
q{ |
| 61 |
ALTER TABLE overduerules |
| 62 |
ADD CONSTRAINT overduerules_ibfk_branchcode |
| 63 |
FOREIGN KEY (branchcode) |
| 64 |
REFERENCES branches(branchcode) |
| 65 |
ON DELETE CASCADE |
| 66 |
ON UPDATE CASCADE |
| 67 |
} |
| 68 |
); |
| 69 |
say_success( $out, "Added foreign key constraint overduerules.branchcode -> branches.branchcode" ); |
| 70 |
} |
| 71 |
|
| 72 |
# Add foreign key for categorycode |
| 73 |
unless ( foreign_key_exists( 'overduerules', 'overduerules_ibfk_categorycode' ) ) { |
| 74 |
$dbh->do( |
| 75 |
q{ |
| 76 |
ALTER TABLE overduerules |
| 77 |
ADD CONSTRAINT overduerules_ibfk_categorycode |
| 78 |
FOREIGN KEY (categorycode) |
| 79 |
REFERENCES categories(categorycode) |
| 80 |
ON DELETE CASCADE |
| 81 |
ON UPDATE CASCADE |
| 82 |
} |
| 83 |
); |
| 84 |
say_success( $out, "Added foreign key constraint overduerules.categorycode -> categories.categorycode" ); |
| 85 |
} |
| 86 |
}, |
| 87 |
}; |