From 617fc22158f64e1b177686001c809df10f82b491 Mon Sep 17 00:00:00 2001 From: Andrew Isherwood Date: Tue, 1 Oct 2019 14:58:22 +0100 Subject: [PATCH] Bug 23681: Add debarment_types This patch adds the table debarment_types and changes borrower_debarments.type to be a foreign key to it --- .../bug_23681_add_debarment_types.perl | 33 ++++++++++++++++++++++ .../mysql/en/mandatory/patron_restrictions.sql | 5 ++++ .../mysql/en/mandatory/patron_restrictions.txt | 1 + installer/data/mysql/kohastructure.sql | 14 +++++++-- 4 files changed, 51 insertions(+), 2 deletions(-) create mode 100644 installer/data/mysql/atomicupdate/bug_23681_add_debarment_types.perl create mode 100644 installer/data/mysql/en/mandatory/patron_restrictions.sql create mode 100644 installer/data/mysql/en/mandatory/patron_restrictions.txt diff --git a/installer/data/mysql/atomicupdate/bug_23681_add_debarment_types.perl b/installer/data/mysql/atomicupdate/bug_23681_add_debarment_types.perl new file mode 100644 index 0000000000..4fc862c954 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_23681_add_debarment_types.perl @@ -0,0 +1,33 @@ +$DBversion = 'XXX'; + +if ( CheckVersion( $DBversion ) ) { + + if ( !TableExists( 'debarment_types' ) ) { + $dbh->do( q| + CREATE TABLE debarment_types ( + code varchar(50) NOT NULL PRIMARY KEY, + display_text text NOT NULL, + ronly tinyint(1) NOT NULL DEFAULT 0, + dflt tinyint(1) NOT NULL DEFAULT 0 + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + | ); + $dbh->do( q| + INSERT INTO debarment_types (code, display_text, ronly, dflt) VALUES + ('MANUAL', 'Manual', 1, 1), + ('OVERDUES', 'Overdues', 1, 0), + ('SUSPENSION', 'Suspension', 1, 0), + ('DISCHARGE', 'Discharge', 1, 0); + |); + } + $dbh->do( q| + ALTER TABLE borrower_debarments + MODIFY COLUMN type varchar(50) NOT NULL + | ); + $dbh->do( q| + ALTER TABLE borrower_debarments + ADD CONSTRAINT borrower_debarments_ibfk_2 FOREIGN KEY (type) REFERENCES debarment_types(code) ON DELETE NO ACTION ON UPDATE CASCADE; + | ); + + SetVersion( $DBversion ); + print "Upgrade to $DBversion done (Bug 23681 - Add debarment_types)\n"; +} diff --git a/installer/data/mysql/en/mandatory/patron_restrictions.sql b/installer/data/mysql/en/mandatory/patron_restrictions.sql new file mode 100644 index 0000000000..a6eedf27a9 --- /dev/null +++ b/installer/data/mysql/en/mandatory/patron_restrictions.sql @@ -0,0 +1,5 @@ +INSERT INTO debarment_types (code, display_text, ronly, dflt) VALUES + ('MANUAL', 'Manual', 1, 1), + ('OVERDUES', 'Overdues', 1, 0), + ('SUSPENSION', 'Suspension', 1, 0), + ('DISCHARGE', 'Discharge', 1, 0); diff --git a/installer/data/mysql/en/mandatory/patron_restrictions.txt b/installer/data/mysql/en/mandatory/patron_restrictions.txt new file mode 100644 index 0000000000..b17a7c1270 --- /dev/null +++ b/installer/data/mysql/en/mandatory/patron_restrictions.txt @@ -0,0 +1 @@ +Default Koha system patron restriction types diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index ecef309dab..6c5e8980ac 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -1614,6 +1614,14 @@ CREATE TABLE `borrower_attributes` ( -- values of custom patron fields known as ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS `debarment_types`; +CREATE TABLE debarment_types ( + code varchar(50) NOT NULL PRIMARY KEY, + display_text text NOT NULL, + ronly tinyint(1) NOT NULL DEFAULT 0, + dflt tinyint(1) NOT NULL DEFAULT 0 +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + -- -- Table structure for table `borrower_debarments` -- @@ -1623,7 +1631,7 @@ CREATE TABLE borrower_debarments ( -- tracks restrictions on the patron's record borrower_debarment_id int(11) NOT NULL AUTO_INCREMENT, -- unique key for the restriction borrowernumber int(11) NOT NULL, -- foreign key for borrowers.borrowernumber for patron who is restricted expiration date DEFAULT NULL, -- expiration date of the restriction - `type` enum('SUSPENSION','OVERDUES','MANUAL','DISCHARGE') NOT NULL DEFAULT 'MANUAL', -- type of restriction + `type` varchar(50) NOT NULL, -- type of restriction, FK to debarment_types.code `comment` MEDIUMTEXT, -- comments about the restriction manager_id int(11) DEFAULT NULL, -- foreign key for borrowers.borrowernumber for the librarian managing the restriction created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- date the restriction was added @@ -1631,7 +1639,9 @@ CREATE TABLE borrower_debarments ( -- tracks restrictions on the patron's record PRIMARY KEY (borrower_debarment_id), KEY borrowernumber (borrowernumber), CONSTRAINT `borrower_debarments_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) - ON DELETE CASCADE ON UPDATE CASCADE + ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `borrower_debarments_ibfk_2` FOREIGN KEY (`type`) REFERENCES `debarment_types` (`code`) + ON DELETE NO ACTION ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- 2.11.0