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

[patch] Bug 14697: Update database

Bug-14697-Update-database.patch (text/plain), 7.26 KB, created by Kyle M Hall (khall) on 2019-10-29 19:20:17 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Kyle M Hall (khall)

Created: 2019-10-29 19:20:17 UTC

Size: 7.26 KB

patch

obsolete

>From 7c87785c48804e06679fab96490bb2f014470647 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
>
>This patch introduces the new table and related sysprefs.
>
>Signed-off-by: Andrew Fuerste-Henry <andrew@bywatersolutions.com>
>Signed-off-by: Lisette Scheer <lisetteslatah@gmail.com>
>Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
>
>Signed-off-by: Andrew Fuerste-Henry <andrew@bywatersolutions.com>
>
>Signed-off-by: Lisette Scheer <lisetteslatah@gmail.com>
>---
> .../data/mysql/atomicupdate/bug_14697.perl    | 46 +++++++++++++++++++
> installer/data/mysql/kohastructure.sql        | 28 +++++++++++
> installer/data/mysql/sysprefs.sql             |  3 ++
> 3 files changed, 77 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..e9d8f11946
>--- /dev/null
>+++ b/installer/data/mysql/atomicupdate/bug_14697.perl
>@@ -0,0 +1,46 @@
>+$DBversion = 'XXX'; # will be replaced by the RM
>+if( CheckVersion( $DBversion ) ) {
>+    if( !TableExists( 'return_claims' ) ) {
>+        $dbh->do(q{
>+            CREATE TABLE return_claims (
>+                id int(11) auto_increment,
>+                itemnumber int(11) NOT NULL,
>+                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`),
>+                UNIQUE( issue_id ),
>+                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 IGNORE 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 IGNORE 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 ecef309dab..a0d03ad385 100644
>--- a/installer/data/mysql/kohastructure.sql
>+++ b/installer/data/mysql/kohastructure.sql
>@@ -4419,6 +4419,34 @@ CREATE TABLE itemtypes_branches( -- association table between authorised_values
>     FOREIGN KEY (branchcode) REFERENCES branches(branchcode) ON DELETE CASCADE
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
> 
>+--
>+-- Table structure for table `return_claims`
>+--
>+
>+DROP TABLE IF EXISTS `return_claims`;
>+CREATE TABLE return_claims (
>+    id int(11) auto_increment,
>+    itemnumber int(11) NOT NULL,
>+    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`),
>+    UNIQUE( issue_id ),
>+    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 11aa53d1dc..990124bf4d 100644
>--- a/installer/data/mysql/sysprefs.sql
>+++ b/installer/data/mysql/sysprefs.sql
>@@ -120,6 +120,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'),
> ('CoceHost', '', NULL, 'Coce server URL', 'Free'),
> ('CoceProviders', '', 'aws,gb,ol', 'Coce providers', 'multiple'),
> ('COinSinOPACResults','1','','If ON, use COinS in OPAC search results page.  NOTE: this can slow down search response time significantly','YesNo'),
>-- 
>2.21.0 (Apple Git-122)

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