From 495efa415d00cb69266a30cf38a4920ec3517a14 Mon Sep 17 00:00:00 2001
From: Pedro Amorim <pedro.amorim@ptfs-europe.com>
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