Attachment 125200 Details for Bug 28854 – Bug 28854: Database update

[patch] Bug 28854: Database update

Bug-28854-Database-update.patch (text/plain), 6.29 KB, created by Martin Renvoize (ashimema) on 2021-09-23 14:40:14 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Martin Renvoize (ashimema)

Created: 2021-09-23 14:40:14 UTC

Size: 6.29 KB

patch

obsolete

>From aa18f89907ae10bb66d1c2149e0aa3e0011d885f Mon Sep 17 00:00:00 2001
>From: Martin Renvoize <martin.renvoize@ptfs-europe.com>
>Date: Thu, 23 Sep 2021 08:28:15 +0100
>Subject: [PATCH] Bug 28854: Database update
>
>https://bugs.koha-community.org/show_bug.cgi?id=24454
>---
> .../data/mysql/atomicupdate/bug_28854.perl    | 65 +++++++++++++++++++
> installer/data/mysql/kohastructure.sql        | 37 +++++++++++
> 2 files changed, 102 insertions(+)
> create mode 100644 installer/data/mysql/atomicupdate/bug_28854.perl
>
>diff --git a/installer/data/mysql/atomicupdate/bug_28854.perl b/installer/data/mysql/atomicupdate/bug_28854.perl
>new file mode 100644
>index 00000000000..5175621d68f
>--- /dev/null
>+++ b/installer/data/mysql/atomicupdate/bug_28854.perl
>@@ -0,0 +1,65 @@
>+use Modern::Perl;
>+
>+return {
>+    bug_number => "28854",
>+    description => "Item bundles support",
>+    up => sub {
>+        my ($args) = @_;
>+        my ($dbh, $out) = @$args{qw(dbh out)};
>+
>+        if( !TableExists( 'item_bundles' ) ) {
>+            $dbh->do(q{
>+                CREATE TABLE `item_bundles` (
>+                  `item` int(11) NOT NULL,
>+                  `host` int(11) NOT NULL,
>+                  PRIMARY KEY (`host`, `item`),
>+                  UNIQUE KEY `item_bundles_uniq_1` (`item`),
>+                  CONSTRAINT `item_bundles_ibfk_1` FOREIGN KEY (`item`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
>+                  CONSTRAINT `item_bundles_ibfk_2` FOREIGN KEY (`host`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
>+                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
>+            });
>+        }
>+        say $out "item_bundles table added";
>+
>+        if( !TableExists( 'items_lost_issue' ) ) {
>+            $dbh->do(q{
>+                CREATE TABLE `items_lost_issue` (
>+                  `id` int(11) NOT NULL AUTO_INCREMENT,
>+                  `itemnumber` int(11) NOT NULL,
>+                  `issue_id` int(11) DEFAULT NULL,
>+                  `created_on` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
>+                  PRIMARY KEY (`id`),
>+                  UNIQUE KEY `itemnumber` (`itemnumber`),
>+                  KEY `issue_id` (`issue_id`),
>+                  CONSTRAINT `items_lost_issue_ibfk_1` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
>+                  CONSTRAINT `items_lost_issue_ibfk_2` FOREIGN KEY (`issue_id`) REFERENCES `old_issues` (`issue_id`) ON DELETE SET NULL ON UPDATE CASCADE
>+                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
>+            });
>+        }
>+        say $out "items_list_issue table added";
>+    
>+        my ($lost_val) = $dbh->selectrow_array( "SELECT MAX(authorised_value) FROM authorised_values WHERE category = 'LOST'", {} );
>+        $lost_val++;
>+    
>+        $dbh->do(qq{
>+           INSERT INTO authorised_values (category,authorised_value,lib) VALUES ('LOST',$lost_val,'Missing from bundle')
>+        });
>+        say $out "Missing from bundle LOST AV added";
>+    
>+        my ($nfl_val) = $dbh->selectrow_array( "SELECT MAX(authorised_value) FROM authorised_values WHERE category = 'NOT_LOAN'", {} );
>+        $nfl_val++;
>+    
>+        $dbh->do(qq{
>+           INSERT INTO authorised_values (category,authorised_value,lib) VALUES ('NOT_LOAN',$nfl_val,'Added to bundle')
>+        });
>+        say $out "Added to bundle NOT_LOAN AV added";
>+    
>+        $dbh->do(qq{
>+            INSERT IGNORE INTO systempreferences( `variable`, `value`, `options`, `explanation`, `type` )
>+            VALUES
>+              ( 'BundleLostValue', $lost_val, '', 'Sets the LOST AV value that represents "Missing from bundle" as a lost value', 'Free' ),
>+              ( 'BundleNotLoanValue', $nfl_val, '', 'Sets the NOT_LOAN AV value that represents "Added to bundle" as a not for loan value', 'Free')
>+        });
>+        say $out "System preferences added and set";
>+    }
>+}
>diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
>index f9e5c6453ee..a67f3368d65 100644
>--- a/installer/data/mysql/kohastructure.sql
>+++ b/installer/data/mysql/kohastructure.sql
>@@ -3063,6 +3063,23 @@ CREATE TABLE `items` (
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
> /*!40101 SET character_set_client = @saved_cs_client */;
> 
>+--
>+-- Table structure for table item_bundles
>+--
>+
>+DROP TABLE IF EXISTS `item_bundles`;
>+/*!40101 SET @saved_cs_client     = @@character_set_client */;
>+/*!40101 SET character_set_client = utf8 */;
>+CREATE TABLE `item_bundles` (
>+  `item` int(11) NOT NULL,
>+  `host` int(11) NOT NULL,
>+  PRIMARY KEY (`host`, `item`),
>+  UNIQUE KEY `item_bundles_uniq_1` (`item`),
>+  CONSTRAINT `item_bundles_ibfk_1` FOREIGN KEY (`item`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
>+  CONSTRAINT `item_bundles_ibfk_2` FOREIGN KEY (`host`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
>+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
>+/*!40101 SET character_set_client = @saved_cs_client */;
>+
> --
> -- Table structure for table `items_last_borrower`
> --
>@@ -3083,6 +3100,26 @@ CREATE TABLE `items_last_borrower` (
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
> /*!40101 SET character_set_client = @saved_cs_client */;
> 
>+--
>+-- Table structure for table `items_lost_issue`
>+--
>+
>+DROP TABLE IF EXISTS `items_lost_issue`;
>+/*!40101 SET @saved_cs_client     = @@character_set_client */;
>+/*!40101 SET character_set_client = utf8 */;
>+CREATE TABLE `items_lost_issue` (
>+  `id` int(11) NOT NULL AUTO_INCREMENT,
>+  `itemnumber` int(11) NOT NULL,
>+  `issue_id` int(11) DEFAULT NULL,
>+  `created_on` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
>+  PRIMARY KEY (`id`),
>+  UNIQUE KEY `itemnumber` (`itemnumber`),
>+  KEY `issue_id` (`issue_id`),
>+  CONSTRAINT `items_lost_issue_ibfk_1` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
>+  CONSTRAINT `items_lost_issue_ibfk_2` FOREIGN KEY (`issue_id`) REFERENCES `old_issues` (`issue_id`) ON DELETE SET NULL ON UPDATE CASCADE
>+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
>+/*!40101 SET character_set_client = @saved_cs_client */;
>+
> --
> -- Table structure for table `items_search_fields`
> --
>-- 
>2.20.1

Actions: View | Diff | Splinter Review

Attachments on bug 28854: 124319 | 124498 | 124616 | 125200 | 125201 | 125202 | 125203 | 125204 | 125205 | 125206 | 126103 | 126104 | 126105 | 126106 | 126107 | 126108 | 126109 | 126110 | 126148 | 126149 | 126150 | 126151 | 126152 | 126153 | 126154 | 126155 | 126166 | 126167 | 126168 | 126169 | 126170 | 126171 | 126172 | 126173 | 127498 | 127499 | 127500 | 127501 | 127502 | 127503 | 127504 | 127505 | 127659 | 127660 | 127661 | 127662 | 127663 | 127664 | 127665 | 127666 | 127829 | 127830 | 127831 | 127832 | 127833 | 127834 | 127835 | 127836 | 127837 | 127838 | 130313 | 130314 | 130315 | 130316 | 130317 | 130318 | 130319 | 130320 | 130321 | 130322 | 130323 | 130324 | 130325 | 130326 | 130327 | 130328 | 130329 | 130330 | 130331 | 130332 | 130456 | 132703 | 132704 | 132705 | 132706 | 132707 | 132708 | 132709 | 132710 | 132711 | 132712 | 132713 | 132714 | 132715 | 132716 | 132717 | 132718 | 132719 | 132720 | 132721 | 132722 | 132723 | 135019 | 135020 | 135021 | 135022 | 135023 | 135024 | 135025 | 135026 | 135027 | 135028 | 135029 | 135030 | 135031 | 135032 | 135033 | 135034 | 135035 | 135036 | 135037 | 136226 | 136227 | 136228 | 136229 | 136230 | 136231 | 136232 | 136233 | 136234 | 136235 | 136236 | 136237 | 136238 | 136239 | 136240 | 136241 | 136242 | 136243 | 136244 | 136249 | 136253 | 136254 | 136264 | 136265 | 136266 | 136267 | 136268 | 136269 | 136270 | 136271 | 136272 | 136273 | 136274 | 136275 | 136276 | 136277 | 136278 | 136279 | 136280 | 136281 | 136282 | 136283 | 136284 | 136285 | 136287 | 136340 | 136341 | 136342 | 136343 | 136344 | 136345 | 136346 | 136347 | 136348 | 136349 | 136350 | 136351 | 136352 | 136353 | 136354 | 136355 | 136356 | 136357 | 136358 | 136359 | 136360 | 136361 | 136362 | 136363 | 136364 | 136365 | 136366 | 136367 | 136368 | 136369 | 136370 | 136371 | 136498 | 136529 | 136530 | 136613 | 136614 | 136615 | 136616 | 136617 | 136618 | 136619 | 136620 | 136621 | 136622 | 136623 | 136624 | 136625 | 136626 | 136627 | 136628 | 136629 | 136630 | 136631 | 136632 | 136633 | 136634 | 136635 | 136636 | 136637 | 136638 | 136639 | 136640 | 136641 | 136642 | 136643 | 136644 | 136645 | 136646 | 136647 | 136648 | 136649 | 136650 | 136651 | 136652 | 136654 | 136655 | 136806 | 136809 | 136811 | 136814 | 136817 | 136821 | 136824 | 136826 | 136829 | 136832 | 136835 | 136838 | 136841 | 136843 | 136846 | 136849 | 136850 | 136851 | 136852 | 136853 | 136854 | 136855 | 136856 | 136857 | 136858 | 136859 | 136860 | 136861 | 136862 | 136863 | 136864 | 136865 | 136866 | 136867 | 136868 | 136869 | 136870 | 136871 | 136872 | 136873 | 136874 | 136875 | 136876 | 136877 | 136905 | 136906 | 136907 | 136908 | 136909 | 136910 | 136911 | 136912 | 136913 | 136914 | 136915 | 136916 | 136917 | 136918 | 136919 | 136920 | 136921 | 136922 | 136923 | 136924 | 136925 | 136926 | 136927 | 136928 | 136929 | 136930 | 136931 | 136932 | 136933 | 136934 | 136935 | 136936 | 136937 | 136938 | 136939 | 136940 | 136941 | 136942 | 136943 | 136944 | 136945 | 136946 | 136947 | 136949 | 136978 | 136979 | 136980 | 136981 | 136982 | 136985 | 136988 | 136989 | 136990 | 136991 | 136992 | 136993 | 136994 | 136995 | 136996 | 136997 | 136998 | 136999 | 137000 | 137001 | 137002 | 137003 | 137004 | 137005 | 137006 | 137007 | 137008 | 137009 | 137010 | 137011 | 137012 | 137013 | 137014 | 137015 | 137016 | 137017 | 137018 | 137019 | 137020 | 137021 | 137022 | 137461 | 137462 | 137463 | 137464 | 137465 | 137466 | 137467 | 137468 | 137469 | 137470 | 137471 | 137472 | 137473 | 137474 | 137475 | 137476 | 137477 | 137478 | 137479 | 137480 | 137481 | 137482 | 137483 | 137484 | 137485 | 137486 | 137487 | 137488 | 137489 | 137490 | 137491 | 137492 | 137493 | 137494 | 137495 | 137496 | 137497 | 137498 | 137499 | 137500 | 137501 | 137503 | 137506 | 137524 | 137525 | 137527 | 137528 | 137529 | 137530 | 137531 | 137532 | 137533 | 137534 | 137535 | 137536 | 137537 | 137538 | 137540 | 137541 | 137542 | 137543 | 137544 | 137545 | 137546 | 137547 | 137548 | 137549 | 137550 | 137551 | 137552 | 137553 | 137554 | 137555 | 137556 | 137557 | 137558 | 137559 | 137560 | 137561 | 137562 | 137563 | 137564 | 137565 | 137566 | 137567 | 137568 | 137569 | 137570 | 137571 | 137684 | 137685 | 139347