From 22e78b5ffa9ddde3186c8a74e5771317d213d601 Mon Sep 17 00:00:00 2001 From: Pedro Amorim Date: Wed, 9 Nov 2022 16:55:20 -0100 Subject: [PATCH] Bug 32154: Missing primary key on erm_user_roles table The aforementioned TestBuilder should display "Koha::ERM::UserRole", not be empty. This patch adds a primary key to the erm_user_roles table and fixes that. Also includes atomicupdate file to update database schema as well as updates to kohastructure.sql provided by Jonathan Druart. Looked into the vue files but didn't find anywhere or a need to include the new primary key in any of the requests as there is no request of users by role_user_id being made. Erm users are being created/updated as a relationship through licenceses/agreements. Furthermore, when editing a role of an existing user, this new primary key is already being supplied embeded in the license/agreement. Test plan: Add users to license and agreement Run the cypress tests Confirm that the change fix the test that was failing in TestBuilder.t (another one may still fail however) Signed-off-by: Jonathan Druart Signed-off-by: Martin Renvoize Signed-off-by: Tomas Cohen Arazi --- 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..582a1c42ae --- /dev/null +++ b/installer/data/mysql/atomicupdate/erm.pl @@ -0,0 +1,18 @@ +use Modern::Perl; + +return { + bug_number => "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 beb769937e..6d343fd56f 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -2880,13 +2880,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.39.5