From f45f743ac0d44dd98b82a20d406b3c18631a900b Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Fri, 20 May 2022 11:08:49 +0200 Subject: [PATCH] Bug 32030: ERM - eHoldings - DB Signed-off-by: Jonathan Field Signed-off-by: Martin Renvoize Signed-off-by: Kyle M Hall Signed-off-by: Tomas Cohen Arazi --- installer/data/mysql/atomicupdate/erm.pl | 84 +++++++++++++++++++++++- installer/data/mysql/kohastructure.sql | 80 ++++++++++++++++++++++ 2 files changed, 163 insertions(+), 1 deletion(-) diff --git a/installer/data/mysql/atomicupdate/erm.pl b/installer/data/mysql/atomicupdate/erm.pl index b164cb3b7d..0682cf84de 100755 --- a/installer/data/mysql/atomicupdate/erm.pl +++ b/installer/data/mysql/atomicupdate/erm.pl @@ -183,5 +183,87 @@ return { ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; }); } - }, + + unless ( TableExists('erm_packages') ) { + $dbh->do(q{ + CREATE TABLE `erm_packages` ( + `package_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key', + `vendor_id` INT(11) DEFAULT NULL COMMENT 'foreign key to aqbooksellers', + `name` VARCHAR(255) NOT NULL COMMENT 'name of the package', + `external_package_id` VARCHAR(255) DEFAULT NULL COMMENT 'External key', + `package_type` VARCHAR(80) DEFAULT NULL COMMENT 'type of the package', + `content_type` VARCHAR(80) DEFAULT NULL COMMENT 'type of the package', + `created_on` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'date of creation of the package', + CONSTRAINT `erm_packages_ibfk_1` FOREIGN KEY (`vendor_id`) REFERENCES `aqbooksellers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, + PRIMARY KEY(`package_id`) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + }); + } + + unless ( TableExists('erm_packages_agreements') ) { + $dbh->do(q{ + CREATE TABLE `erm_packages_agreements` ( + `package_id` INT(11) NOT NULL COMMENT 'link to the package', + `agreement_id` INT(11) NOT NULL COMMENT 'link to the agreement', + CONSTRAINT `erm_packages_agreements_ibfk_1` FOREIGN KEY (`package_id`) REFERENCES `erm_packages` (`package_id`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `erm_packages_agreements_ibfk_2` FOREIGN KEY (`agreement_id`) REFERENCES `erm_agreements` (`agreement_id`) ON DELETE CASCADE ON UPDATE CASCADE + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + }); + } + + unless ( TableExists('erm_eholdings') ) { + $dbh->do(q{ + CREATE TABLE `erm_eholdings` ( + `eholding_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key', + `publication_title` VARCHAR(255) DEFAULT NULL, + `print_identifier` VARCHAR(255) DEFAULT NULL, + `online_identifier` VARCHAR(255) DEFAULT NULL, + `date_first_issue_online` VARCHAR(255) DEFAULT NULL, + `num_first_vol_online` VARCHAR(255) DEFAULT NULL, + `num_first_issue_online` VARCHAR(255) DEFAULT NULL, + `date_last_issue_online` VARCHAR(255) DEFAULT NULL, + `num_last_vol_online` VARCHAR(255) DEFAULT NULL, + `num_last_issue_online` VARCHAR(255) DEFAULT NULL, + `title_url` VARCHAR(255) DEFAULT NULL, + `first_author` VARCHAR(255) DEFAULT NULL, + `title_id` VARCHAR(255) DEFAULT NULL, + `embargo_info` VARCHAR(255) DEFAULT NULL, + `coverage_depth` VARCHAR(255) DEFAULT NULL, + `notes` VARCHAR(255) DEFAULT NULL, + `publisher_name` VARCHAR(255) DEFAULT NULL, + `publication_type` VARCHAR(255) DEFAULT NULL, + `date_monograph_published_print` VARCHAR(255) DEFAULT NULL, + `date_monograph_published_online` VARCHAR(255) DEFAULT NULL, + `monograph_volume` VARCHAR(255) DEFAULT NULL, + `monograph_edition` VARCHAR(255) DEFAULT NULL, + `first_editor` VARCHAR(255) DEFAULT NULL, + `parent_publication_title_id` VARCHAR(255) DEFAULT NULL, + `preceeding_publication_title_id` VARCHAR(255) DEFAULT NULL, + `access_type` VARCHAR(255) DEFAULT NULL, + PRIMARY KEY(`eholding_id`) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + }); + } + unless ( TableExists('erm_eholdings_packages') ) { + $dbh->do(q{ + CREATE TABLE `erm_eholdings_packages` ( + `eholding_id` INT(11) NOT NULL, + `package_id` INT(11) NOT NULL, + `started_on` DATE, + `ended_on` DATE, + `proxy` VARCHAR(80) DEFAULT NULL, + CONSTRAINT `erm_eholdings_packages_ibfk_1` FOREIGN KEY (`eholding_id`) REFERENCES `erm_eholdings` (`eholding_id`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `erm_eholdings_packages_ibfk_2` FOREIGN KEY (`package_id`) REFERENCES `erm_packages` (`package_id`) ON DELETE CASCADE ON UPDATE CASCADE + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + }); + } + + unless ( column_exists('aqbooksellers', 'external_id') ) { + $dbh->do(q{ + ALTER TABLE `aqbooksellers` + ADD COLUMN `external_id` VARCHAR(255) DEFAULT NULL + AFTER `deliverytime` + }); + } + } }; diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index adaab982fb..fc22324df6 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -418,6 +418,7 @@ CREATE TABLE `aqbooksellers` ( `discount` float(6,4) DEFAULT NULL COMMENT 'discount offered on all items ordered from this vendor', `fax` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'vendor fax number', `deliverytime` int(11) DEFAULT NULL COMMENT 'vendor delivery time', + `external_id` VARCHAR(255) DEFAULT NULL COMMENT 'external id of the vendor', PRIMARY KEY (`id`), KEY `listprice` (`listprice`), KEY `invoiceprice` (`invoiceprice`), @@ -2891,6 +2892,85 @@ CREATE TABLE `erm_agreement_documents` ( PRIMARY KEY(`document_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +-- +-- Table structure for table `erm_packages` +-- + +DROP TABLE IF EXISTS `erm_packages`; +CREATE TABLE `erm_packages` ( + `package_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key', + `vendor_id` INT(11) DEFAULT NULL COMMENT 'foreign key to aqbooksellers', + `name` VARCHAR(255) NOT NULL COMMENT 'name of the package', + `external_package_id` VARCHAR(255) DEFAULT NULL COMMENT 'External key', + `package_type` VARCHAR(80) DEFAULT NULL COMMENT 'type of the package', + `content_type` VARCHAR(80) DEFAULT NULL COMMENT 'type of the package', + `created_on` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'date of creation of the package', + CONSTRAINT `erm_packages_ibfk_1` FOREIGN KEY (`vendor_id`) REFERENCES `aqbooksellers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, + PRIMARY KEY(`package_id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + +-- +-- Table structure for table `erm_packages_agreements` +-- + +DROP TABLE IF EXISTS `erm_packages_agreements`; +CREATE TABLE `erm_packages_agreements` ( + `package_id` INT(11) NOT NULL COMMENT 'link to the package', + `agreement_id` INT(11) NOT NULL COMMENT 'link to the agreement', + CONSTRAINT `erm_packages_agreements_ibfk_1` FOREIGN KEY (`package_id`) REFERENCES `erm_packages` (`package_id`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `erm_packages_agreements_ibfk_2` FOREIGN KEY (`agreement_id`) REFERENCES `erm_agreements` (`agreement_id`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + +-- +-- Table structure for table `erm_eholdings` +-- + +DROP TABLE IF EXISTS `erm_eholdings`; +CREATE TABLE `erm_eholdings` ( + `eholding_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key', + `publication_title` VARCHAR(255) DEFAULT NULL, + `print_identifier` VARCHAR(255) DEFAULT NULL, + `online_identifier` VARCHAR(255) DEFAULT NULL, + `date_first_issue_online` VARCHAR(255) DEFAULT NULL, + `num_first_vol_online` VARCHAR(255) DEFAULT NULL, + `num_first_issue_online` VARCHAR(255) DEFAULT NULL, + `date_last_issue_online` VARCHAR(255) DEFAULT NULL, + `num_last_vol_online` VARCHAR(255) DEFAULT NULL, + `num_last_issue_online` VARCHAR(255) DEFAULT NULL, + `title_url` VARCHAR(255) DEFAULT NULL, + `first_author` VARCHAR(255) DEFAULT NULL, + `title_id` VARCHAR(255) DEFAULT NULL, + `embargo_info` VARCHAR(255) DEFAULT NULL, + `coverage_depth` VARCHAR(255) DEFAULT NULL, + `notes` VARCHAR(255) DEFAULT NULL, + `publisher_name` VARCHAR(255) DEFAULT NULL, + `publication_type` VARCHAR(255) DEFAULT NULL, + `date_monograph_published_print` VARCHAR(255) DEFAULT NULL, + `date_monograph_published_online` VARCHAR(255) DEFAULT NULL, + `monograph_volume` VARCHAR(255) DEFAULT NULL, + `monograph_edition` VARCHAR(255) DEFAULT NULL, + `first_editor` VARCHAR(255) DEFAULT NULL, + `parent_publication_title_id` VARCHAR(255) DEFAULT NULL, + `preceeding_publication_title_id` VARCHAR(255) DEFAULT NULL, + `access_type` VARCHAR(255) DEFAULT NULL, + PRIMARY KEY(`eholding_id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + +-- +-- Table structure for table `erm_eholdings_packages` +-- + +DROP TABLE IF EXISTS `erm_eholdings_packages`; +CREATE TABLE `erm_eholdings_packages` ( + `eholding_id` INT(11) NOT NULL, + `package_id` INT(11) NOT NULL, + `started_on` DATE, + `ended_on` DATE, + `proxy` VARCHAR(80) DEFAULT NULL, + CONSTRAINT `erm_eholdings_packages_ibfk_1` FOREIGN KEY (`eholding_id`) REFERENCES `erm_eholdings` (`eholding_id`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `erm_eholdings_packages_ibfk_2` FOREIGN KEY (`package_id`) REFERENCES `erm_packages` (`package_id`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + -- -- Table structure for table `export_format` -- -- 2.20.1