From f6360e2108715faf8caa689a9546e711b154bb2e Mon Sep 17 00:00:00 2001 From: Kyle M Hall Date: Thu, 14 Oct 2021 12:24:57 +0000 Subject: [PATCH] Bug 20813: Update atomic update to new format --- .../{permissions.sql => permissions.pl} | 71 +++++++++++++++++++ 1 file changed, 71 insertions(+) rename installer/data/mysql/atomicupdate/{permissions.sql => permissions.pl} (80%) mode change 100644 => 100755 diff --git a/installer/data/mysql/atomicupdate/permissions.sql b/installer/data/mysql/atomicupdate/permissions.pl old mode 100644 new mode 100755 similarity index 80% rename from installer/data/mysql/atomicupdate/permissions.sql rename to installer/data/mysql/atomicupdate/permissions.pl index c2cacab842b..9066bcdb116 --- a/installer/data/mysql/atomicupdate/permissions.sql +++ b/installer/data/mysql/atomicupdate/permissions.pl @@ -1,50 +1,121 @@ +use Modern::Perl; + +return { + bug_number => "20813", + description => "Revamp user permissions system", + up => sub { + my ($args) = @_; + my ($dbh, $out) = @$args{qw(dbh out)}; + + $dbh->do(q{ ALTER TABLE user_permissions DROP FOREIGN KEY user_permissions_ibfk_2, DROP index user_permissions_ibfk_2, DROP COLUMN module_bit; + }); + $dbh->do(q{ ALTER TABLE permissions DROP PRIMARY KEY, DROP FOREIGN KEY permissions_ibfk_1, ADD PRIMARY KEY ( code ), ADD COLUMN `parent` VARCHAR(64) NULL DEFAULT NULL FIRST, ADD KEY parent ( parent ); + }); + $dbh->do(q{ ALTER TABLE permissions ADD FOREIGN KEY ( parent ) REFERENCES permissions ( code ); + }); + $dbh->do(q{ ALTER TABLE user_permissions ADD CONSTRAINT user_permissions_ibfk_2 FOREIGN KEY (code) REFERENCES permissions(code); + }); + $dbh->do(q{ INSERT INTO permissions ( parent, code, description ) VALUES ( NULL, 'superlibrarian', 'Access to all librarian functions' ); + }); + $dbh->do(q{ INSERT INTO permissions ( parent, code, description ) SELECT 'superlibrarian', flag, flagdesc FROM userflags WHERE flag != 'superlibrarian'; + }); + $dbh->do(q{ UPDATE permissions LEFT JOIN userflags ON ( userflags.bit = permissions.module_bit ) SET parent = flag WHERE flag != 'superlibrarian'; + }); + $dbh->do(q{ INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'superlibrarian' FROM borrowers WHERE substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -1, 1 ) = '1'; + }); + $dbh->do(q{ INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'circulate' FROM borrowers WHERE substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -2, 1 ) = '1'; + }); + $dbh->do(q{ INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'catalogue' FROM borrowers WHERE substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -3, 1 ) = '1'; + }); + $dbh->do(q{ INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'parameters' FROM borrowers WHERE substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -4, 1 ) = '1'; + }); + $dbh->do(q{ INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'borrowers' FROM borrowers WHERE substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -5, 1 ) = '1'; + }); + $dbh->do(q{ INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'permissions' FROM borrowers WHERE substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -6, 1 ) = '1'; + }); + $dbh->do(q{ INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'reserveforothers' FROM borrowers WHERE substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -7, 1 ) = '1'; + }); + $dbh->do(q{ INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'editcatalogue' FROM borrowers WHERE substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -8, 1 ) = '1'; + }); + $dbh->do(q{ INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'updatecharges' FROM borrowers WHERE substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -9, 1 ) = '1'; + }); + $dbh->do(q{ INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'acquisition' FROM borrowers WHERE substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -10, 1 ) = '1'; + }); + $dbh->do(q{ INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'management' FROM borrowers WHERE substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -11, 1 ) = '1'; + }); + $dbh->do(q{ INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'tools' FROM borrowers WHERE substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -12, 1 ) = '1'; + }); + $dbh->do(q{ INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'editauthorities' FROM borrowers WHERE substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -13, 1 ) = '1'; + }); + $dbh->do(q{ INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'serials' FROM borrowers WHERE substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -14, 1 ) = '1'; + }); + $dbh->do(q{ INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'reports' FROM borrowers WHERE substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -15, 1 ) = '1'; + }); + $dbh->do(q{ INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'staffaccess' FROM borrowers WHERE substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -16, 1 ) = '1'; + }); + $dbh->do(q{ INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'coursereserves' FROM borrowers WHERE substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -17, 1 ) = '1'; + }); + $dbh->do(q{ INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'plugins' FROM borrowers WHERE substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -18, 1 ) = '1'; + }); + $dbh->do(q{ INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'lists' FROM borrowers WHERE substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -19, 1 ) = '1'; + }); + $dbh->do(q{ INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'clubs' FROM borrowers WHERE substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -20, 1 ) = '1'; + }); + $dbh->do(q{ ALTER TABLE permissions DROP COLUMN module_bit; + }); + $dbh->do(q{ ALTER TABLE borrowers DROP COLUMN flags; + }); + $dbh->do(q{ DROP TABLE userflags; + }); + }, +} -- 2.30.2