From 21610ec5f4310834637a91384a9763d137a9d851 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Wed, 20 Jul 2022 11:03:35 +0200 Subject: [PATCH] Bug 32030: Unique - DB alter table erm_agreement_licenses add unique key `erm_agreement_licenses_uniq` (`agreement_id`, `license_id`); alter table erm_eholdings_packages_agreements add unique key `erm_eholdings_packages_agreements_uniq` (`package_id`, `agreement_id`); alter table erm_eholdings_resources add UNIQUE KEY `erm_eholdings_resources_uniq` (`title_id`, `package_id`); 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 | 5 ++++- installer/data/mysql/kohastructure.sql | 5 ++++- 2 files changed, 8 insertions(+), 2 deletions(-) diff --git a/installer/data/mysql/atomicupdate/erm.pl b/installer/data/mysql/atomicupdate/erm.pl index a67ca3b03b..f4a96933a8 100755 --- a/installer/data/mysql/atomicupdate/erm.pl +++ b/installer/data/mysql/atomicupdate/erm.pl @@ -119,7 +119,8 @@ return { `uri` varchar(255) DEFAULT NULL COMMENT 'URI of the license', CONSTRAINT `erm_licenses_ibfk_1` FOREIGN KEY (`agreement_id`) REFERENCES `erm_agreements` (`agreement_id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `erm_licenses_ibfk_2` FOREIGN KEY (`license_id`) REFERENCES `erm_licenses` (`license_id`) ON DELETE CASCADE ON UPDATE CASCADE, - PRIMARY KEY(`agreement_license_id`) + PRIMARY KEY(`agreement_license_id`), + UNIQUE KEY `erm_agreement_licenses_uniq` (`agreement_id`, `license_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; }); } @@ -206,6 +207,7 @@ return { CREATE TABLE `erm_eholdings_packages_agreements` ( `package_id` INT(11) NOT NULL COMMENT 'link to the package', `agreement_id` INT(11) NOT NULL COMMENT 'link to the agreement', + UNIQUE KEY `erm_eholdings_packages_agreements_uniq` (`package_id`, `agreement_id`), CONSTRAINT `erm_eholdings_packages_agreements_ibfk_1` FOREIGN KEY (`package_id`) REFERENCES `erm_eholdings_packages` (`package_id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `erm_eholdings_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; @@ -257,6 +259,7 @@ return { `started_on` DATE, `ended_on` DATE, `proxy` VARCHAR(80) DEFAULT NULL, + UNIQUE KEY `erm_eholdings_resources_uniq` (`title_id`, `package_id`), CONSTRAINT `erm_eholdings_resources_ibfk_1` FOREIGN KEY (`title_id`) REFERENCES `erm_eholdings_titles` (`title_id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `erm_eholdings_resources_ibfk_2` FOREIGN KEY (`package_id`) REFERENCES `erm_eholdings_packages` (`package_id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `erm_eholdings_resources_ibfk_3` FOREIGN KEY (`vendor_id`) REFERENCES `aqbooksellers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 7674de170f..7eeb7f6404 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -2854,7 +2854,8 @@ CREATE TABLE `erm_agreement_licenses` ( `uri` varchar(255) DEFAULT NULL COMMENT 'URI of the license', CONSTRAINT `erm_licenses_ibfk_1` FOREIGN KEY (`agreement_id`) REFERENCES `erm_agreements` (`agreement_id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `erm_licenses_ibfk_2` FOREIGN KEY (`license_id`) REFERENCES `erm_licenses` (`license_id`) ON DELETE CASCADE ON UPDATE CASCADE, - PRIMARY KEY(`agreement_license_id`) + PRIMARY KEY(`agreement_license_id`), + UNIQUE KEY `erm_agreement_licenses_uniq` (`agreement_id`, `license_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- @@ -2918,6 +2919,7 @@ DROP TABLE IF EXISTS `erm_eholdings_packages_agreements`; CREATE TABLE `erm_eholdings_packages_agreements` ( `package_id` INT(11) NOT NULL COMMENT 'link to the package', `agreement_id` INT(11) NOT NULL COMMENT 'link to the agreement', + UNIQUE KEY `erm_eholdings_packages_agreements_uniq` (`package_id`, `agreement_id`), CONSTRAINT `erm_eholdings_packages_agreements_ibfk_1` FOREIGN KEY (`package_id`) REFERENCES `erm_eholdings_packages` (`package_id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `erm_eholdings_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; @@ -2972,6 +2974,7 @@ CREATE TABLE `erm_eholdings_resources` ( `started_on` DATE, `ended_on` DATE, `proxy` VARCHAR(80) DEFAULT NULL, + UNIQUE KEY `erm_eholdings_resources_uniq` (`title_id`, `package_id`), CONSTRAINT `erm_eholdings_resources_ibfk_1` FOREIGN KEY (`title_id`) REFERENCES `erm_eholdings_titles` (`title_id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `erm_eholdings_resources_ibfk_2` FOREIGN KEY (`package_id`) REFERENCES `erm_eholdings_packages` (`package_id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `erm_eholdings_resources_ibfk_3` FOREIGN KEY (`vendor_id`) REFERENCES `aqbooksellers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, -- 2.39.5