From fd170b6ab7cf20cf99c9168b3934aab465d90c61 Mon Sep 17 00:00:00 2001 From: Jonathan Druart 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 Signed-off-by: Laurence Rault Signed-off-by: Marcel de Rooy Signed-off-by: Tomas Cohen Arazi --- .../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 0000000000..2817d9776e --- /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 822b222cfc..ecc9b72fdc 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -4798,6 +4798,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 9e4fac8903..ce27c5f61a 100644 --- a/installer/data/mysql/mandatory/sysprefs.sql +++ b/installer/data/mysql/mandatory/sysprefs.sql @@ -582,6 +582,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 b9905e2f5e..8e9fad2526 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.39.5