Attachment 125374 Details for Bug 29125 – Bug 29125: Add primary key to user_permissions table

[patch] Bug 29125: Add primary key to user_permissions table

Bug-29125-Add-primary-key-to-userpermissions-table.patch (text/plain), 2.53 KB, created by Nick Clemens (kidclamp) on 2021-09-27 18:38:16 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Nick Clemens (kidclamp)

Created: 2021-09-27 18:38:16 UTC

Size: 2.53 KB

patch

obsolete

>From c0510cce51b4c75faaeadd328bbcbf7753677661 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 29125: Add primary key to user_permissions table
>
>---
> .../update_user_permissions_table.pl          | 26 +++++++++++++++++++
> installer/data/mysql/kohastructure.sql        |  1 +
> 2 files changed, 27 insertions(+)
> 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..6d732720e1
>--- /dev/null
>+++ b/installer/data/mysql/atomicupdate/update_user_permissions_table.pl
>@@ -0,0 +1,26 @@
>+use Modern::Perl;
>+
>+return {
>+    bug_number => "BUG_NUMBER",
>+    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 "Drop any previously created keys";
>+            $dbh->do(q{
>+                ALTER TABLE user_permissions ADD CONSTRAINT PK_borrowernumber_module_code PRIMARY KEY (borrowernumber,module_bit,code)
>+            });
>+            say $out "Add a primary key to user_permissions on borrowernumber, module_bit, code";
>+        }
>+        # Print useful stuff here
>+        say $out "Update is going well so far";
>+    },
>+}
>diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
>index f9e5c6453e..14a17eeda7 100644
>--- a/installer/data/mysql/kohastructure.sql
>+++ b/installer/data/mysql/kohastructure.sql
>@@ -5161,6 +5161,7 @@ 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,
>+  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.20.1

Actions: View | Diff | Splinter Review

Attachments on bug 29125: 125374 | 125375 | 125376 | 125979