From c0510cce51b4c75faaeadd328bbcbf7753677661 Mon Sep 17 00:00:00 2001 From: Nick Clemens 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