From 83732b5ae1f583c202fa4d85091ed0866c9c4330 Mon Sep 17 00:00:00 2001 From: Kyle M Hall Date: Thu, 31 Aug 2017 10:29:34 -0400 Subject: [PATCH] Bug 20813: Update db schema --- .../data/mysql/atomicupdate/permissions.pl | 121 ++++++++++++++++++ installer/data/mysql/kohastructure.sql | 42 ++---- 2 files changed, 133 insertions(+), 30 deletions(-) create mode 100755 installer/data/mysql/atomicupdate/permissions.pl diff --git a/installer/data/mysql/atomicupdate/permissions.pl b/installer/data/mysql/atomicupdate/permissions.pl new file mode 100755 index 00000000000..9066bcdb116 --- /dev/null +++ b/installer/data/mysql/atomicupdate/permissions.pl @@ -0,0 +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; + }); + }, +} diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 95a3735906c..544bcd8b161 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -1274,7 +1274,6 @@ CREATE TABLE `borrower_modifications` ( `relationship` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `sex` varchar(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `password` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `flags` int(11) DEFAULT NULL, `userid` varchar(75) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `opacnote` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL, `contactnote` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, @@ -1392,7 +1391,6 @@ CREATE TABLE `borrowers` ( `relationship` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'used for children to include the relationship to their guarantor', `sex` varchar(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s gender', `password` varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s Bcrypt encrypted password', - `flags` int(11) DEFAULT NULL COMMENT 'will include a number associated with the staff member''s permissions', `userid` varchar(75) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s opac and/or staff interface log in', `opacnote` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'a note on the patron/borrower''s account that is visible in the OPAC and staff interface', `contactnote` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'a note related to the patron/borrower''s alternate address', @@ -2399,7 +2397,6 @@ CREATE TABLE `deletedborrowers` ( `relationship` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'used for children to include the relationship to their guarantor', `sex` varchar(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s gender', `password` varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s encrypted password', - `flags` int(11) DEFAULT NULL COMMENT 'will include a number associated with the staff member''s permissions', `userid` varchar(75) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s opac and/or staff interface log in', `opacnote` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'a note on the patron/borrower''s account that is visible in the OPAC and staff interface', `contactnote` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'a note related to the patron/borrower''s alternate address', @@ -4069,11 +4066,12 @@ DROP TABLE IF EXISTS `permissions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `permissions` ( - `module_bit` int(11) NOT NULL DEFAULT 0, - `code` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', - `description` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - PRIMARY KEY (`module_bit`,`code`), - CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`) ON DELETE CASCADE ON UPDATE CASCADE + `parent` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL, + `code` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', + `description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, + PRIMARY KEY (`code`), + KEY `parent` (`parent`), + CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`parent`) REFERENCES `permissions` (`code`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; @@ -5158,30 +5156,14 @@ CREATE TABLE `uploaded_files` ( DROP TABLE IF EXISTS `user_permissions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; +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, + `borrowernumber` int(11) NOT NULL DEFAULT '0', + `code` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL, 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, - CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`) ON DELETE CASCADE ON UPDATE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -/*!40101 SET character_set_client = @saved_cs_client */; - --- --- Table structure for table `userflags` --- - -DROP TABLE IF EXISTS `userflags`; -/*!40101 SET @saved_cs_client = @@character_set_client */; -/*!40101 SET character_set_client = utf8 */; -CREATE TABLE `userflags` ( - `bit` int(11) NOT NULL DEFAULT 0, - `flag` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `flagdesc` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `defaulton` int(11) DEFAULT NULL, - PRIMARY KEY (`bit`) + KEY `user_permissions_ibfk_2` (`code`), + CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`code`) REFERENCES `permissions` (`code`), + CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- 2.30.2