From ec3c7a85c978a31a4404dfd9fad487bc74a4434c Mon Sep 17 00:00:00 2001
From: Nick Clemens <nick@bywatersolutions.com>
Date: Mon, 27 Sep 2021 18:34:17 +0000
Subject: [PATCH] Bug 30060: Add primary key to user_permissions table

Without primary key DBIC is failing with something like:

GET /api/v1/suggestions/managers: unhandled exception (DBIx::Class::Exception)<<DBIx::Class::ResultSource::RowParser::_resolve_collapse(): Unable to calculate a definitive collapse co
lumn set for UserPermission (last member of the Borrower -> user_permissions chain): fetch more unique non-nullable columns at /kohadevbox/koha/Koha/Objects.pm line 394>> Koha::REST::Plugin::Exceptions::__ANON__ /
kohadevbox/koha/Koha/REST/Plugin/Exceptions.pm (73)

Test plan:
Execute the atomic update and confirm that the new PK has been added to the user_permissions table.

Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Signed-off-by: Katrin Fischer <katrin.fischer.83@web.de>
Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Signed-off-by: Fridolin Somers <fridolin.somers@biblibre.com>
---
 .../update_user_permissions_table.pl          | 24 +++++++++++++++++++
 installer/data/mysql/kohastructure.sql        |  3 ++-
 2 files changed, 26 insertions(+), 1 deletion(-)
 create mode 100755 installer/data/mysql/atomicupdate/update_user_permissions_table.pl

diff --git a/installer/data/mysql/atomicupdate/update_user_permissions_table.pl b/installer/data/mysql/atomicupdate/update_user_permissions_table.pl
new file mode 100755
index 0000000000..c455ab6bbc
--- /dev/null
+++ b/installer/data/mysql/atomicupdate/update_user_permissions_table.pl
@@ -0,0 +1,24 @@
+use Modern::Perl;
+
+return {
+    bug_number => "30060",
+    description => "Update user_permissions to add primary key and remove null option from code column",
+    up => sub {
+        my ($args) = @_;
+        my ($dbh, $out) = @$args{qw(dbh out)};
+        unless(
+            primary_key_exists( 'user_permissions', 'borrowernumber') &&
+            primary_key_exists( 'user_permissions', 'module_bit' ) &&
+            primary_key_exists( 'user_permissions', 'code')
+        ){
+            $dbh->do(q{
+                ALTER TABLE user_permissions DROP INDEX IF EXISTS `PRIMARY`
+            });
+            say $out "Dropped any previously created primary key";
+            $dbh->do(q{
+                ALTER TABLE user_permissions ADD CONSTRAINT PK_borrowernumber_module_code PRIMARY KEY (borrowernumber,module_bit,code)
+            });
+            say $out "Added a primary key on user_permissions on borrowernumber, module_bit, code";
+        }
+    },
+}
diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index 4411ae7604..34a6a9665d 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -5188,7 +5188,8 @@ DROP TABLE IF EXISTS `user_permissions`;
 CREATE TABLE `user_permissions` (
   `borrowernumber` int(11) NOT NULL DEFAULT 0,
   `module_bit` int(11) NOT NULL DEFAULT 0,
-  `code` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
+  `code` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
+  PRIMARY KEY (`borrowernumber`,`module_bit`,`code`),
   KEY `user_permissions_ibfk_1` (`borrowernumber`),
   KEY `user_permissions_ibfk_2` (`module_bit`,`code`),
   CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
-- 
2.39.5