From 24d72567252303806fa7c0f9e1edf84123fe87e2 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

Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
---
 .../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.32.0