@@ -, +, @@ --- .../bug_23681_add_debarment_types.perl | 33 +++++++++++++++++++ .../en/mandatory/patron_restrictions.sql | 5 +++ .../en/mandatory/patron_restrictions.txt | 1 + installer/data/mysql/kohastructure.sql | 13 ++++++-- 4 files changed, 50 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 --- a/installer/data/mysql/atomicupdate/bug_23681_add_debarment_types.perl +++ a/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"; +} --- a/installer/data/mysql/en/mandatory/patron_restrictions.sql +++ a/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); --- a/installer/data/mysql/en/mandatory/patron_restrictions.txt +++ a/installer/data/mysql/en/mandatory/patron_restrictions.txt @@ -0,0 +1, @@ +Default Koha system patron restriction types --- a/installer/data/mysql/kohastructure.sql +++ a/installer/data/mysql/kohastructure.sql @@ -1184,14 +1184,15 @@ CREATE TABLE `borrower_debarments` ( `borrower_debarment_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'unique key for the restriction', `borrowernumber` int(11) NOT NULL COMMENT 'foreign key for borrowers.borrowernumber for patron who is restricted', `expiration` date DEFAULT NULL COMMENT 'expiration date of the restriction', - `type` enum('SUSPENSION','OVERDUES','MANUAL','DISCHARGE') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'MANUAL' COMMENT 'type of restriction', + `type` varchar(50) NOT NULL COMMENT 'type of restriction, FK to debarment_types.code', `comment` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'comments about the restriction', `manager_id` int(11) DEFAULT NULL COMMENT 'foreign key for borrowers.borrowernumber for the librarian managing the restriction', `created` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'date the restriction was added', `updated` timestamp NULL DEFAULT NULL COMMENT 'date the restriction was updated', 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 + CONSTRAINT `borrower_debarments_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) 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; /*!40101 SET character_set_client = @saved_cs_client */; @@ -2026,6 +2027,14 @@ CREATE TABLE `collections_tracking` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; +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 `columns_settings` -- --