From ec3c7a85c978a31a4404dfd9fad487bc74a4434c Mon Sep 17 00:00:00 2001 From: Nick Clemens 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)< 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 Signed-off-by: Katrin Fischer Signed-off-by: Martin Renvoize Signed-off-by: Fridolin Somers --- .../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