From 495efa415d00cb69266a30cf38a4920ec3517a14 Mon Sep 17 00:00:00 2001 From: Pedro Amorim Date: Wed, 9 Nov 2022 11:53:10 -0100 Subject: [PATCH] Bug 32030: Add missing primary key to erm_user_roles table --- api/v1/swagger/definitions/erm_user_role.yaml | 3 +++ installer/data/mysql/atomicupdate/erm.pl | 18 ++++++++++++++++++ installer/data/mysql/kohastructure.sql | 4 +++- 3 files changed, 24 insertions(+), 1 deletion(-) create mode 100644 installer/data/mysql/atomicupdate/erm.pl diff --git a/api/v1/swagger/definitions/erm_user_role.yaml b/api/v1/swagger/definitions/erm_user_role.yaml index 7256542ce0..1f84f6516c 100644 --- a/api/v1/swagger/definitions/erm_user_role.yaml +++ b/api/v1/swagger/definitions/erm_user_role.yaml @@ -1,6 +1,9 @@ --- type: object properties: + user_role_id: + type: integer + description: Internal user_role identifier agreement_id: type: - integer diff --git a/installer/data/mysql/atomicupdate/erm.pl b/installer/data/mysql/atomicupdate/erm.pl new file mode 100644 index 0000000000..67dca6d692 --- /dev/null +++ b/installer/data/mysql/atomicupdate/erm.pl @@ -0,0 +1,18 @@ +use Modern::Perl; + +return { + bug_number => "BUG_32030", + description => "Add primary key to erm_user_roles", + up => sub { + my ($args) = @_; + my ($dbh, $out) = @$args{qw(dbh out)}; + + unless ( column_exists('erm_user_roles', 'user_role_id') ) { + $dbh->do(q{ + ALTER TABLE `erm_user_roles` + ADD COLUMN `user_role_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key' FIRST, + ADD PRIMARY KEY(`user_role_id`); + }); + } + } +}; diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 54ed708304..33e2980576 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -2865,13 +2865,15 @@ CREATE TABLE `erm_agreement_periods` ( DROP TABLE IF EXISTS `erm_user_roles`; CREATE TABLE `erm_user_roles` ( + `user_role_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key', `agreement_id` INT(11) NULL COMMENT 'link to the agreement', `license_id` INT(11) NULL COMMENT 'link to the license', `user_id` INT(11) NOT NULL COMMENT 'link to the user', `role` VARCHAR(80) NOT NULL COMMENT 'role of the user', CONSTRAINT `erm_user_roles_ibfk_1` FOREIGN KEY (`agreement_id`) REFERENCES `erm_agreements` (`agreement_id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `erm_user_roles_ibfk_2` FOREIGN KEY (`license_id`) REFERENCES `erm_licenses` (`license_id`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `erm_user_roles_ibfk_3` FOREIGN KEY (`user_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE + CONSTRAINT `erm_user_roles_ibfk_3` FOREIGN KEY (`user_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, + PRIMARY KEY(`user_role_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- 2.25.1