View | Details | Raw Unified | Return to bug 41246
Collapse All | Expand All

(-)a/installer/data/mysql/atomicupdate/bug_41246.pl (+87 lines)
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
};
(-)a/installer/data/mysql/kohastructure.sql (-1 / +2 lines)
Lines 5207-5212 CREATE TABLE `overduerules` ( Link Here
5207
  `debarred3` tinyint(1) DEFAULT 0 COMMENT 'is the patron restricted when the third notice is sent (1 for yes, 0 for no)',
5207
  `debarred3` tinyint(1) DEFAULT 0 COMMENT 'is the patron restricted when the third notice is sent (1 for yes, 0 for no)',
5208
  PRIMARY KEY (`overduerules_id`),
5208
  PRIMARY KEY (`overduerules_id`),
5209
  UNIQUE KEY `overduerules_branch_cat` (`branchcode`,`categorycode`)
5209
  UNIQUE KEY `overduerules_branch_cat` (`branchcode`,`categorycode`)
5210
  CONSTRAINT `overduerules_ibfk_branchcode` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
5211
  CONSTRAINT `overduerules_ibfk_categorycode` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
5210
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
5212
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
5211
/*!40101 SET character_set_client = @saved_cs_client */;
5213
/*!40101 SET character_set_client = @saved_cs_client */;
5212
5214
5213
- 

Return to bug 41246