Attachment 154172 Details for Bug 30708 – Bug 30708: DB changes

[patch] Bug 30708: DB changes

Bug-30708-DB-changes.patch (text/plain), 16.09 KB, created by Jonathan Druart on 2023-08-03 08:22:36 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Jonathan Druart

Created: 2023-08-03 08:22:36 UTC

Size: 16.09 KB

patch

obsolete

>From 46385e3d572b0a8163d13ab74f20216f4dbb61c2 Mon Sep 17 00:00:00 2001
>From: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
>Date: Fri, 7 Apr 2023 14:50:43 +0200
>Subject: [PATCH] Bug 30708: DB changes
>
>Sponsored-by: BULAC - http://www.bulac.fr/
>Signed-off-by: BULAC - http://www.bulac.fr/
>Signed-off-by: Heather Hernandez <heather_hernandez@nps.gov>
>Signed-off-by: Laurence Rault <laurence.rault@biblibre.com>
>---
> .../data/mysql/atomicupdate/bug_30708.pl      | 112 ++++++++++++++++++
> installer/data/mysql/kohastructure.sql        |  92 ++++++++++++++
> installer/data/mysql/mandatory/sysprefs.sql   |   3 +
> installer/data/mysql/mandatory/userflags.sql  |   3 +-
> 4 files changed, 209 insertions(+), 1 deletion(-)
> create mode 100755 installer/data/mysql/atomicupdate/bug_30708.pl
>
>diff --git a/installer/data/mysql/atomicupdate/bug_30708.pl b/installer/data/mysql/atomicupdate/bug_30708.pl
>new file mode 100755
>index 00000000000..2817d9776e7
>--- /dev/null
>+++ b/installer/data/mysql/atomicupdate/bug_30708.pl
>@@ -0,0 +1,112 @@
>+use Modern::Perl;
>+
>+return {
>+    bug_number => "30708",
>+    description => "Add a preservation module",
>+    up => sub {
>+        my ($args) = @_;
>+        my ($dbh, $out) = @$args{qw(dbh out)};
>+        $dbh->do(q{
>+            INSERT IGNORE INTO systempreferences (`variable`, `value`, `options`, `explanation`, `type` )
>+            VALUES ('PreservationModule', '0', NULL, 'Enable the preservation module', 'YesNo')
>+        });
>+        say $out "Added new system preference 'PreservationModule'";
>+
>+        $dbh->do(q{
>+            INSERT IGNORE INTO systempreferences (`variable`, `value`, `options`, `explanation`, `type` )
>+            VALUES ('PreservationNotForLoanWaitingListIn', '', '', 'Not for loan to apply to items added to the preservation waiting list', 'TextArea')
>+        });
>+        say $out "Added new system preference 'PreservationNotForLoanWaitingListIn'";
>+
>+        $dbh->do(q{
>+            INSERT IGNORE INTO systempreferences (`variable`, `value`, `options`, `explanation`, `type` )
>+            VALUES ('PreservationNotForLoanDefaultTrainIn', '', '', 'Not for loan to apply to items removed from the preservation waiting list', 'TextArea')
>+        });
>+        say $out "Added new system preference 'PreservationNotForLoanDefaultTrainIn'";
>+
>+        $dbh->do(q{
>+            INSERT IGNORE INTO userflags (bit, flag, flagdesc, defaulton)
>+            VALUES (30, 'preservation', 'Manage preservation module', 0)
>+        });
>+        say $out "Added new permission 'preservation'";
>+
>+        unless ( TableExists('preservation_processings') ) {
>+            $dbh->do(q{
>+                CREATE TABLE `preservation_processings` (
>+                  `processing_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
>+                  `name` varchar(80) NOT NULL COMMENT 'name of the processing',
>+                  PRIMARY KEY (`processing_id`)
>+                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
>+            });
>+            say $out "Added new table 'preservation_processings'";
>+        }
>+
>+        unless ( TableExists('preservation_trains') ) {
>+            $dbh->do(q{
>+                CREATE TABLE `preservation_trains` (
>+                  `train_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
>+                  `name` varchar(80) NOT NULL COMMENT 'name of the train',
>+                  `description` varchar(255) NULL COMMENT 'description of the train',
>+                  `default_processing_id` int(11) NULL COMMENT 'default processing, link to preservation_processings.processing_id',
>+                  `not_for_loan` varchar(80) NOT NULL DEFAULT 0 COMMENT 'NOT_LOAN authorised value to apply toitem added to this train',
>+                  `created_on` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'creation date',
>+                  `closed_on` datetime DEFAULT NULL COMMENT 'closing date',
>+                  `sent_on` datetime DEFAULT NULL COMMENT 'sending date',
>+                  `received_on` datetime DEFAULT NULL COMMENT 'receiving date',
>+                  PRIMARY KEY (`train_id`),
>+                  CONSTRAINT `preservation_trains_ibfk_1` FOREIGN KEY (`default_processing_id`) REFERENCES `preservation_processings` (`processing_id`) ON DELETE SET NULL ON UPDATE CASCADE
>+                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
>+            });
>+            say $out "Added new table 'preservation_trains'";
>+        }
>+
>+        unless ( TableExists('preservation_processing_attributes') ) {
>+            $dbh->do(q{
>+                CREATE TABLE `preservation_processing_attributes` (
>+                  `processing_attribute_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
>+                  `processing_id` int(11) NOT NULL COMMENT 'link to the processing',
>+                  `name` varchar(80) NOT NULL COMMENT 'name of the processing attribute',
>+                  `type` enum('authorised_value', 'free_text', 'db_column') NOT NULL COMMENT 'Type of the processing attribute',
>+                  `option_source` varchar(80) NULL COMMENT 'source of the possible options for this attribute',
>+                  PRIMARY KEY (`processing_attribute_id`),
>+                  CONSTRAINT `preservation_processing_attributes_ibfk_1` FOREIGN KEY (`processing_id`) REFERENCES `preservation_processings` (`processing_id`) ON DELETE CASCADE ON UPDATE CASCADE
>+                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
>+            });
>+            say $out "Added new table 'preservation_processing_attributes'";
>+        }
>+
>+        unless ( TableExists('preservation_trains_items') ) {
>+            $dbh->do(q{
>+                CREATE TABLE `preservation_trains_items` (
>+                  `train_item_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
>+                  `train_id` int(11) NOT NULL COMMENT 'link with preservation_train',
>+                  `item_id` int(11) NOT NULL COMMENT 'link with items',
>+                  `processing_id` int(11) NULL COMMENT 'specific processing for this item',
>+                  `user_train_item_id` int(11) NOT NULL COMMENT 'train item id for this train, starts from 1',
>+                  `added_on` datetime DEFAULT NULL COMMENT 'added date',
>+                  PRIMARY KEY (`train_item_id`),
>+                  UNIQUE KEY (`train_id`,`item_id`),
>+                  CONSTRAINT `preservation_item_ibfk_1` FOREIGN KEY (`train_id`) REFERENCES `preservation_trains` (`train_id`) ON DELETE CASCADE ON UPDATE CASCADE,
>+                  CONSTRAINT `preservation_item_ibfk_2` FOREIGN KEY (`item_id`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
>+                  CONSTRAINT `preservation_item_ibfk_3` FOREIGN KEY (`processing_id`) REFERENCES `preservation_processings` (`processing_id`) ON DELETE SET NULL ON UPDATE CASCADE
>+                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
>+            });
>+            say $out "Added new table 'preservation_trains_items'";
>+        }
>+
>+        unless ( TableExists('preservation_processing_attributes_items') ) {
>+            $dbh->do(q{
>+                CREATE TABLE `preservation_processing_attributes_items` (
>+                  `processing_attribute_id` int(11) NOT NULL COMMENT 'link with preservation_processing_attributes',
>+                  `train_item_id` int(11) NOT NULL COMMENT 'link with preservation_trains_items',
>+                  `value` varchar(255) NULL COMMENT 'value for this attribute',
>+                  PRIMARY KEY (`processing_attribute_id`,`train_item_id`),
>+                  CONSTRAINT `preservation_processing_attributes_items_ibfk_1` FOREIGN KEY (`processing_attribute_id`) REFERENCES `preservation_processing_attributes` (`processing_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
>+                  CONSTRAINT `preservation_processing_attributes_items_ibfk_2` FOREIGN KEY (`train_item_id`) REFERENCES `preservation_trains_items` (`train_item_id`) ON DELETE CASCADE ON UPDATE CASCADE
>+                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
>+            });
>+            say $out "Added new table 'preservation_processing_attributes_items'";
>+        }
>+
>+    },
>+};
>diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
>index c1f800fe7f6..72216761975 100644
>--- a/installer/data/mysql/kohastructure.sql
>+++ b/installer/data/mysql/kohastructure.sql
>@@ -4763,6 +4763,98 @@ CREATE TABLE `plugin_methods` (
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
> /*!40101 SET character_set_client = @saved_cs_client */;
> 
>+--
>+-- Table structure for table `preservation_trains`
>+--
>+
>+DROP TABLE IF EXISTS `preservation_trains`;
>+/*!40101 SET @saved_cs_client     = @@character_set_client */;
>+/*!40101 SET character_set_client = utf8 */;
>+CREATE TABLE `preservation_trains` (
>+  `train_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
>+  `name` varchar(80) NOT NULL COMMENT 'name of the train',
>+  `description` varchar(255) NULL COMMENT 'description of the train',
>+  `default_processing_id` int(11) NULL COMMENT 'default processing, link to preservation_processings.processing_id',
>+  `not_for_loan` varchar(80) NOT NULL DEFAULT 0 COMMENT 'NOT_LOAN authorised value to apply toitem added to this train',
>+  `created_on` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'creation date',
>+  `closed_on` datetime DEFAULT NULL COMMENT 'closing date',
>+  `sent_on` datetime DEFAULT NULL COMMENT 'sending date',
>+  `received_on` datetime DEFAULT NULL COMMENT 'receiving date',
>+  PRIMARY KEY (`train_id`),
>+  CONSTRAINT `preservation_trains_ibfk_1` FOREIGN KEY (`default_processing_id`) REFERENCES `preservation_processings` (`processing_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 `preservation_processings`
>+--
>+
>+DROP TABLE IF EXISTS `preservation_processings`;
>+/*!40101 SET @saved_cs_client     = @@character_set_client */;
>+/*!40101 SET character_set_client = utf8 */;
>+CREATE TABLE `preservation_processings` (
>+  `processing_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
>+  `name` varchar(80) NOT NULL COMMENT 'name of the processing',
>+  PRIMARY KEY (`processing_id`)
>+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
>+/*!40101 SET character_set_client = @saved_cs_client */;
>+
>+--
>+-- Table structure for table `preservation_processing_attributes`
>+--
>+
>+DROP TABLE IF EXISTS `preservation_processing_attributes`;
>+/*!40101 SET @saved_cs_client     = @@character_set_client */;
>+/*!40101 SET character_set_client = utf8 */;
>+CREATE TABLE `preservation_processing_attributes` (
>+  `processing_attribute_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
>+  `processing_id` int(11) NOT NULL COMMENT 'link to the processing',
>+  `name` varchar(80) NOT NULL COMMENT 'name of the processing attribute',
>+  `type` enum('authorised_value', 'free_text', 'db_column') NOT NULL COMMENT 'Type of the processing attribute',
>+  `option_source` varchar(80) NULL COMMENT 'source of the possible options for this attribute',
>+  PRIMARY KEY (`processing_attribute_id`),
>+  CONSTRAINT `preservation_processing_attributes_ibfk_1` FOREIGN KEY (`processing_id`) REFERENCES `preservation_processings` (`processing_id`) 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 `preservation_processing_attributes_items`
>+--
>+
>+DROP TABLE IF EXISTS `preservation_processing_attributes_items`;
>+/*!40101 SET @saved_cs_client     = @@character_set_client */;
>+/*!40101 SET character_set_client = utf8 */;
>+CREATE TABLE `preservation_processing_attributes_items` (
>+  `processing_attribute_id` int(11) NOT NULL COMMENT 'link with preservation_processing_attributes',
>+  `train_item_id` int(11) NOT NULL COMMENT 'link with preservation_trains_items',
>+  `value` varchar(255) NULL COMMENT 'value for this attribute',
>+  PRIMARY KEY (`processing_attribute_id`,`train_item_id`),
>+  CONSTRAINT `preservation_processing_attributes_items_ibfk_1` FOREIGN KEY (`processing_attribute_id`) REFERENCES `preservation_processing_attributes` (`processing_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
>+  CONSTRAINT `preservation_processing_attributes_items_ibfk_2` FOREIGN KEY (`train_item_id`) REFERENCES `preservation_trains_items` (`train_item_id`) 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 `preservation_trains_items`
>+--
>+
>+DROP TABLE IF EXISTS `preservation_trains_items`;
>+/*!40101 SET @saved_cs_client     = @@character_set_client */;
>+/*!40101 SET character_set_client = utf8 */;
>+CREATE TABLE `preservation_trains_items` (
>+  `train_item_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
>+  `train_id` int(11) NOT NULL COMMENT 'link with preservation_train',
>+  `item_id` int(11) NOT NULL COMMENT 'link with items',
>+  `processing_id` int(11) NULL COMMENT 'specific processing for this item',
>+  `added_on` datetime DEFAULT NULL COMMENT 'added date',
>+  PRIMARY KEY (`train_item_id`),
>+  UNIQUE KEY (`train_id`,`item_id`),
>+  CONSTRAINT `preservation_item_ibfk_1` FOREIGN KEY (`train_id`) REFERENCES `preservation_trains` (`train_id`) ON DELETE CASCADE ON UPDATE CASCADE,
>+  CONSTRAINT `preservation_item_ibfk_2` FOREIGN KEY (`item_id`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
>+  CONSTRAINT `preservation_item_ibfk_3` FOREIGN KEY (`processing_id`) REFERENCES `preservation_processings` (`processing_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 `printers_profile`
> --
>diff --git a/installer/data/mysql/mandatory/sysprefs.sql b/installer/data/mysql/mandatory/sysprefs.sql
>index 30197591a8b..fba10587777 100644
>--- a/installer/data/mysql/mandatory/sysprefs.sql
>+++ b/installer/data/mysql/mandatory/sysprefs.sql
>@@ -576,6 +576,9 @@ INSERT INTO systempreferences ( `variable`, `value`, `options`, `explanation`, `
> ('PhoneNotification','0',NULL,'If ON, enables generation of phone notifications to be sent by plugins','YesNo'),
> ('PrefillGuaranteeField', 'phone,email,streetnumber,address,city,state,zipcode,country', NULL, 'Prefill these fields in guarantee member entry form from guarantor patron record', 'Multiple'),
> ('PrefillItem','0','','When a new item is added, should it be prefilled with last created item values?','YesNo'),
>+('PreservationModule', '0', NULL, 'Enable the preservation module', 'YesNo'),
>+('PreservationNotForLoanDefaultTrainIn', '', '', 'Not for loan to apply to items removed from the preservation waiting list', 'TextArea'),
>+('PreservationNotForLoanWaitingListIn', '', '', 'Not for loan to apply to items added to the preservation waiting list', 'TextArea'),
> ('PreserveSerialNotes','1','','When a new "Expected" issue is generated, should it be prefilled with last created issue notes?','YesNo'),
> ('previousIssuesDefaultSortOrder','asc','asc|desc','Specify the sort order of Previous Issues on the circulation page','Choice'),
> ('PrintNoticesMaxLines','0','','If greater than 0, sets the maximum number of lines an overdue notice will print. If the number of items is greater than this number, the notice will end with a warning asking the borrower to check their online account for a full list of overdue items.','Integer'),
>diff --git a/installer/data/mysql/mandatory/userflags.sql b/installer/data/mysql/mandatory/userflags.sql
>index b9905e2f5e7..8e9fad25260 100644
>--- a/installer/data/mysql/mandatory/userflags.sql
>+++ b/installer/data/mysql/mandatory/userflags.sql
>@@ -26,5 +26,6 @@ INSERT INTO userflags (bit, flag, flagdesc, defaulton) VALUES
> (26, 'problem_reports', 'Manage problem reports', 0),
> (27, 'recalls', 'Recalls', 0),
> (28, 'erm', 'Manage electronic resources', 0),
>-(29, 'loggedinlibrary', 'Change logged in library', 0)
>+(29, 'loggedinlibrary', 'Change logged in library', 0),
>+(30, 'preservation', 'Preservation module', 0)
> ;
>-- 
>2.25.1

Actions: View | Diff | Splinter Review

Attachments on bug 30708: 134715 | 134716 | 134732 | 134733 | 134734 | 134736 | 134737 | 134738 | 134782 | 134783 | 134784 | 134785 | 152939 | 152940 | 152941 | 152942 | 152943 | 152944 | 152945 | 152946 | 152947 | 152948 | 152949 | 152950 | 152951 | 152952 | 152953 | 152954 | 152955 | 152956 | 152957 | 152958 | 152959 | 152960 | 152961 | 152962 | 152963 | 152964 | 152965 | 152966 | 152967 | 152968 | 152969 | 152970 | 153102 | 153576 | 154167 | 154168 | 154169 | 154170 | 154171 | 154172 | 154173 | 154174 | 154175 | 154176 | 154177 | 154178 | 154179 | 154180 | 154181 | 154182 | 154183 | 154184 | 154185 | 154186 | 154187 | 154188 | 154189 | 154190 | 154191 | 154192 | 154193 | 154194 | 154195 | 154196 | 154197 | 154198 | 154199 | 154200 | 154201 | 154202 | 154203 | 156592 | 156596 | 156597 | 156636 | 156637 | 156638 | 156639 | 156640 | 156641 | 156642 | 156643 | 156644 | 156645 | 156646 | 156647 | 156648 | 156649 | 156650 | 156651 | 156652 | 156653 | 156654 | 156655 | 156656 | 156657 | 156658 | 156659 | 156660 | 156661 | 156662 | 156663 | 156664 | 156665 | 156666 | 156667 | 156668 | 156669 | 156670 | 156671 | 156672 | 156673 | 156674 | 156675 | 157502 | 157503 | 157506 | 157508 | 157512 | 157514 | 157557