From 5fc7cf7a424b569e5166ad8730ec4589818ca2eb Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Fri, 29 Apr 2022 09:57:26 +0200 Subject: [PATCH] Bug 30650: DB changes Sponsored-by: Association KohaLa - https://koha-fr.org/ --- .../data/mysql/atomicupdate/bug_30650.pl | 92 +++++++++++++++++++ installer/data/mysql/kohastructure.sql | 82 +++++++++++++++++ 2 files changed, 174 insertions(+) create mode 100755 installer/data/mysql/atomicupdate/bug_30650.pl diff --git a/installer/data/mysql/atomicupdate/bug_30650.pl b/installer/data/mysql/atomicupdate/bug_30650.pl new file mode 100755 index 00000000000..9b5f9dbdf28 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_30650.pl @@ -0,0 +1,92 @@ +use Modern::Perl; + +return { + bug_number => "30650", + description => "Curbside pickup tables", + up => sub { + my ($args) = @_; + my ( $dbh, $out ) = @$args{qw(dbh out)}; + unless ( TableExists('curbside_pickup_policy') ) { + $dbh->do( + q{ + CREATE TABLE `curbside_pickup_policy` ( + `id` int(11) NOT NULL auto_increment, + `branchcode` varchar(10) NOT NULL, + `enabled` TINYINT(1) NOT NULL DEFAULT 0, + `pickup_interval` INT(2) NOT NULL DEFAULT 0, + `patrons_per_interval` INT(2) NOT NULL DEFAULT 0, + `patron_scheduled_pickup` TINYINT(1) NOT NULL DEFAULT 0, + `sunday_start_hour` INT(2) NULL DEFAULT NULL, + `sunday_start_minute` INT(2) NULL DEFAULT NULL, + `sunday_end_hour` INT(2) NULL DEFAULT NULL, + `sunday_end_minute` INT(2) NULL DEFAULT NULL, + `monday_start_hour` INT(2) NULL DEFAULT NULL, + `monday_start_minute` INT(2) NULL DEFAULT NULL, + `monday_end_hour` INT(2) NULL DEFAULT NULL, + `monday_end_minute` INT(2) NULL DEFAULT NULL, + `tuesday_start_hour` INT(2) NULL DEFAULT NULL, + `tuesday_start_minute` INT(2) NULL DEFAULT NULL, + `tuesday_end_hour` INT(2) NULL DEFAULT NULL, + `tuesday_end_minute` INT(2) NULL DEFAULT NULL, + `wednesday_start_hour` INT(2) NULL DEFAULT NULL, + `wednesday_start_minute` INT(2) NULL DEFAULT NULL, + `wednesday_end_hour` INT(2) NULL DEFAULT NULL, + `wednesday_end_minute` INT(2) NULL DEFAULT NULL, + `thursday_start_hour` INT(2) NULL DEFAULT NULL, + `thursday_start_minute` INT(2) NULL DEFAULT NULL, + `thursday_end_hour` INT(2) NULL DEFAULT NULL, + `thursday_end_minute` INT(2) NULL DEFAULT NULL, + `friday_start_hour` INT(2) NULL DEFAULT NULL, + `friday_start_minute` INT(2) NULL DEFAULT NULL, + `friday_end_hour` INT(2) NULL DEFAULT NULL, + `friday_end_minute` INT(2) NULL DEFAULT NULL, + `saturday_start_hour` INT(2) NULL DEFAULT NULL, + `saturday_start_minute` INT(2) NULL DEFAULT NULL, + `saturday_end_hour` INT(2) NULL DEFAULT NULL, + `saturday_end_minute` INT(2) NULL DEFAULT NULL, + PRIMARY KEY (`id`), + UNIQUE KEY (`branchcode`), + FOREIGN KEY (branchcode) REFERENCES branches(branchcode) ON DELETE CASCADE ON UPDATE CASCADE + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + } + ); + } + unless ( TableExists('curbside_pickups') ) { + + $dbh->do( + q{ + CREATE TABLE `curbside_pickups` ( + `id` int(11) NOT NULL auto_increment, + `borrowernumber` int(11) NOT NULL, + `branchcode` varchar(10) NOT NULL, + `scheduled_pickup_datetime` datetime NOT NULL, + `staged_datetime` datetime NULL DEFAULT NULL, + `staged_by` int(11) NULL DEFAULT NULL, + `arrival_datetime` datetime NULL DEFAULT NULL, + `delivered_datetime` datetime NULL DEFAULT NULL, + `delivered_by` int(11) NULL DEFAULT NULL, + `notes` text NULL DEFAULT NULL, + PRIMARY KEY (`id`), + FOREIGN KEY (branchcode) REFERENCES branches(branchcode) ON DELETE CASCADE ON UPDATE CASCADE, + FOREIGN KEY (borrowernumber) REFERENCES borrowers(borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE, + FOREIGN KEY (staged_by) REFERENCES borrowers(borrowernumber) ON DELETE SET NULL ON UPDATE CASCADE + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + } + ); + } + unless ( TableExists('curbside_pickup_issues') ) { + $dbh->do( + q{ + CREATE TABLE `curbside_pickup_issues` ( + `id` int(11) NOT NULL auto_increment, + `curbside_pickup_id` int(11) NOT NULL, + `issue_id` int(11) NOT NULL, + `reserve_id` int(11) NOT NULL, + PRIMARY KEY (`id`), + FOREIGN KEY (curbside_pickup_id) REFERENCES curbside_pickups(id) ON DELETE CASCADE ON UPDATE CASCADE + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + } + ); + } + } + } diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 0d009afe59a..7c6eccc9774 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -2272,6 +2272,88 @@ CREATE TABLE `creator_templates` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; +-- +-- Table structure for table `curbside_pickup_policy` +-- + +DROP TABLE IF EXISTS `curbside_pickup_policy`; +CREATE TABLE `curbside_pickup_policy` ( + `id` int(11) NOT NULL auto_increment, + `branchcode` varchar(10) NOT NULL, + `enabled` TINYINT(1) NOT NULL DEFAULT 0, + `pickup_interval` INT(2) NOT NULL DEFAULT 0, + `patrons_per_interval` INT(2) NOT NULL DEFAULT 0, + `patron_scheduled_pickup` TINYINT(1) NOT NULL DEFAULT 0, + `sunday_start_hour` INT(2) NULL DEFAULT NULL, + `sunday_start_minute` INT(2) NULL DEFAULT NULL, + `sunday_end_hour` INT(2) NULL DEFAULT NULL, + `sunday_end_minute` INT(2) NULL DEFAULT NULL, + `monday_start_hour` INT(2) NULL DEFAULT NULL, + `monday_start_minute` INT(2) NULL DEFAULT NULL, + `monday_end_hour` INT(2) NULL DEFAULT NULL, + `monday_end_minute` INT(2) NULL DEFAULT NULL, + `tuesday_start_hour` INT(2) NULL DEFAULT NULL, + `tuesday_start_minute` INT(2) NULL DEFAULT NULL, + `tuesday_end_hour` INT(2) NULL DEFAULT NULL, + `tuesday_end_minute` INT(2) NULL DEFAULT NULL, + `wednesday_start_hour` INT(2) NULL DEFAULT NULL, + `wednesday_start_minute` INT(2) NULL DEFAULT NULL, + `wednesday_end_hour` INT(2) NULL DEFAULT NULL, + `wednesday_end_minute` INT(2) NULL DEFAULT NULL, + `thursday_start_hour` INT(2) NULL DEFAULT NULL, + `thursday_start_minute` INT(2) NULL DEFAULT NULL, + `thursday_end_hour` INT(2) NULL DEFAULT NULL, + `thursday_end_minute` INT(2) NULL DEFAULT NULL, + `friday_start_hour` INT(2) NULL DEFAULT NULL, + `friday_start_minute` INT(2) NULL DEFAULT NULL, + `friday_end_hour` INT(2) NULL DEFAULT NULL, + `friday_end_minute` INT(2) NULL DEFAULT NULL, + `saturday_start_hour` INT(2) NULL DEFAULT NULL, + `saturday_start_minute` INT(2) NULL DEFAULT NULL, + `saturday_end_hour` INT(2) NULL DEFAULT NULL, + `saturday_end_minute` INT(2) NULL DEFAULT NULL, + `patron_scheduled_pickup` TINYINT(1) NOT NULL DEFAULT 0, + PRIMARY KEY (`id`), + UNIQUE KEY (`branchcode`), + FOREIGN KEY (branchcode) REFERENCES branches(branchcode) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + +-- +-- Table structure for table `curbside_pickups` +-- + +DROP TABLE IF EXISTS `curbside_pickups`; +CREATE TABLE `curbside_pickups` ( + `id` int(11) NOT NULL auto_increment, + `borrowernumber` int(11) NOT NULL, + `branchcode` varchar(10) NOT NULL, + `scheduled_pickup_datetime` datetime NOT NULL, + `staged_datetime` datetime NULL DEFAULT NULL, + `staged_by` int(11) NULL DEFAULT NULL, + `arrival_datetime` datetime NULL DEFAULT NULL, + `delivered_datetime` datetime NULL DEFAULT NULL, + `delivered_by` int(11) NULL DEFAULT NULL, + `notes` text NULL DEFAULT NULL, + PRIMARY KEY (`id`), + FOREIGN KEY (branchcode) REFERENCES branches(branchcode) ON DELETE CASCADE ON UPDATE CASCADE, + FOREIGN KEY (borrowernumber) REFERENCES borrowers(borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE, + FOREIGN KEY (staged_by) REFERENCES borrowers(borrowernumber) ON DELETE SET NULL ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + +-- +-- Table structure for table `curbside_pickup_issues` +-- + +DROP TABLE IF EXISTS `curbside_pickup_issues`; +CREATE TABLE `curbside_pickup_issues` ( + `id` int(11) NOT NULL auto_increment, + `curbside_pickup_id` int(11) NOT NULL, + `issue_id` int(11) NOT NULL, + `reserve_id` int(11) NOT NULL, + PRIMARY KEY (`id`), + FOREIGN KEY (curbside_pickup_id) REFERENCES curbside_pickups(id) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + -- -- Table structure for table `currency` -- -- 2.25.1