From 0bb51f581c58018a6fd9549644b91d3f9e9c9fa8 Mon Sep 17 00:00:00 2001 From: Kyle M Hall Date: Fri, 22 Feb 2019 08:46:34 -0500 Subject: [PATCH] Bug 14697: Update database --- .../data/mysql/atomicupdate/bug_14697.perl | 52 +++++++++++++++++++ installer/data/mysql/kohastructure.sql | 29 +++++++++++ installer/data/mysql/sysprefs.sql | 3 ++ 3 files changed, 84 insertions(+) create mode 100644 installer/data/mysql/atomicupdate/bug_14697.perl diff --git a/installer/data/mysql/atomicupdate/bug_14697.perl b/installer/data/mysql/atomicupdate/bug_14697.perl new file mode 100644 index 0000000000..af52398c00 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_14697.perl @@ -0,0 +1,52 @@ +$DBversion = 'XXX'; # will be replaced by the RM +if( CheckVersion( $DBversion ) ) { + if( !TableExists( 'return_claims' ) ) { + $dbh->do(q{ + CREATE TABLE IF NOT EXISTS return_claims ( + id int(11) auto_increment, + itemnumber int(11) NOT NULL, + issue_id int(11) NULL DEFAULT NULL, + old_issue_id int(11) NULL DEFAULT NULL, + borrowernumber int(11) NOT NULL, + notes MEDIUMTEXT DEFAULT NULL, + created_on TIMESTAMP NULL, + created_by int(11) NULL DEFAULT NULL, + updated_on TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP, + updated_by int(11) NULL DEFAULT NULL, + resolution varchar(80) NULL DEFAULT NULL, + resolved_on TIMESTAMP NULL DEFAULT NULL, + resolved_by int(11) NULL DEFAULT NULL, + PRIMARY KEY (`id`), + KEY `itemnumber` (`itemnumber`), + CONSTRAINT `rc_issues_ibfk` FOREIGN KEY (`issue_id`) REFERENCES `issues` (`issue_id`) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT `rc_old_issues_ibfk` FOREIGN KEY (`old_issue_id`) REFERENCES `issues` (`issue_id`) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT `rc_items_ibfk` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `rc_borrowers_ibfk` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `rc_created_by_ibfk` FOREIGN KEY (`created_by`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT `rc_updated_by_ibfk` FOREIGN KEY (`updated_by`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT `rc_resolved_by_ibfk` FOREIGN KEY (`resolved_by`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + }); + } + + $dbh->do(q{ + INSERT INTO systempreferences ( `variable`, `value`, `options`, `explanation`, `type` ) VALUES + ('ClaimReturnedChargeFee', 'ask', 'ask|charge|no_charge', 'Controls whether or not a lost item fee is charged for return claims', 'Choice'), + ('ClaimReturnedLostValue', '', '', 'Sets the LOST AV value that represents "Claims returned" as a lost value', 'Free'), + ('ClaimReturnedWarningThreshold', '', '', 'Sets the number of return claims past which the librarian will be warned the patron has many return claims', 'Integer'); + }); + + $dbh->do(q{ + INSERT INTO `authorised_values` ( category, authorised_value, lib, ) + VALUES + ( + 'RETURN_CLAIM_RESOLUTION', 'RET_BY_PATRON', 'Returned by patron' + ), + ( + 'RETURN_CLAIM_RESOLUTION', 'FOUND_IN_LIB', 'Found in library', + ); + }); + + SetVersion( $DBversion ); + print "Upgrade to $DBversion done (Bug 14697 - Extend and enhance 'Claims returned' lost status)\n"; +} diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 62effe991e..2eea999308 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -4278,6 +4278,35 @@ shortcut_keys varchar(80) NOT NULL, PRIMARY KEY (shortcut_name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +-- +-- Table structure for table `return_claims` +-- + +CREATE TABLE IF NOT EXISTS return_claims ( + id int(11) auto_increment, + itemnumber int(11) NOT NULL, + issue_id int(11) NULL DEFAULT NULL, + old_issue_id int(11) NULL DEFAULT NULL, + borrowernumber int(11) NOT NULL, + notes MEDIUMTEXT DEFAULT NULL, + created_on TIMESTAMP NULL, + created_by int(11) NULL DEFAULT NULL, + updated_on TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP, + updated_by int(11) NULL DEFAULT NULL, + resolution varchar(80) NULL DEFAULT NULL, + resolved_on TIMESTAMP NULL DEFAULT NULL, + resolved_by int(11) NULL DEFAULT NULL, + PRIMARY KEY (`id`), + KEY `itemnumber` (`itemnumber`), + CONSTRAINT `rc_issues_ibfk` FOREIGN KEY (`issue_id`) REFERENCES `issues` (`issue_id`) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT `rc_old_issues_ibfk` FOREIGN KEY (`old_issue_id`) REFERENCES `issues` (`issue_id`) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT `rc_items_ibfk` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `rc_borrowers_ibfk` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `rc_created_by_ibfk` FOREIGN KEY (`created_by`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT `rc_updated_by_ibfk` FOREIGN KEY (`updated_by`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT `rc_resolved_by_ibfk` FOREIGN KEY (`resolved_by`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; diff --git a/installer/data/mysql/sysprefs.sql b/installer/data/mysql/sysprefs.sql index a0d1132d25..eb1c35871e 100644 --- a/installer/data/mysql/sysprefs.sql +++ b/installer/data/mysql/sysprefs.sql @@ -118,6 +118,9 @@ INSERT INTO systempreferences ( `variable`, `value`, `options`, `explanation`, ` ('CircControl','ItemHomeLibrary','PickupLibrary|PatronLibrary|ItemHomeLibrary','Specify the agency that controls the circulation and fines policy','Choice'), ('CircSidebar','0',NULL,'Activate or deactivate the navigation sidebar on all Circulation pages','YesNo'), ('ClaimsBccCopy','0','','Bcc the ClaimAcquisition and ClaimIssues alerts','YesNo'), +('ClaimReturnedChargeFee', 'ask', 'ask|charge|no_charge', 'Controls whether or not a lost item fee is charged for return claims', 'Choice'), +('ClaimReturnedLostValue', '', '', 'Sets the LOST AV value that represents "Claims returned" as a lost value', 'Free'), +('ClaimReturnedWarningThreshold', '', '', 'Sets the number of return claims past which the librarian will be warned the patron has many return claims', 'Integer'), ('Coce','0', NULL, 'If on, enables cover retrieval from the configured Coce server', 'YesNo'), ('CoceHost', '', NULL, 'Coce server URL', 'Free'), ('CoceProviders', '', 'aws,gb,ol', 'Coce providers', 'multiple'), -- 2.20.1 (Apple Git-117)