Attachment 92407 Details for Bug 14697 – Bug 14697: Update database

[patch] Bug 14697: Update database

Bug-14697-Update-database.patch (text/plain), 7.38 KB, created by Kyle M Hall (khall) on 2019-08-21 18:45:45 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Kyle M Hall (khall)

Created: 2019-08-21 18:45:45 UTC

Size: 7.38 KB

patch

obsolete

>From 0bb51f581c58018a6fd9549644b91d3f9e9c9fa8 Mon Sep 17 00:00:00 2001
>From: Kyle M Hall <kyle@bywatersolutions.com>
>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)

Actions: View | Diff | Splinter Review

Attachments on bug 14697: 92407 | 92408 | 92409 | 92410 | 92962 | 92963 | 92964 | 92965 | 93005 | 93006 | 93007 | 93008 | 93012 | 93013 | 93014 | 93015 | 93017 | 93038 | 93039 | 93040 | 93041 | 93042 | 93061 | 93062 | 93063 | 93064 | 93065 | 93952 | 93953 | 93954 | 93955 | 93956 | 93957 | 93959 | 94842 | 94843 | 94844 | 94845 | 94846 | 94847 | 94848 | 94849 | 94850 | 94851 | 94852 | 94853 | 94854 | 94857 | 94877 | 94878