From 857f4c4b3a2232f6240c050c71af503f4c900fbc 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 --- installer/data/mysql/atomicupdate/permissions.sql | 50 +++++++++++++++++++++++ installer/data/mysql/kohastructure.sql | 39 ++++++------------ 2 files changed, 62 insertions(+), 27 deletions(-) create mode 100644 installer/data/mysql/atomicupdate/permissions.sql diff --git a/installer/data/mysql/atomicupdate/permissions.sql b/installer/data/mysql/atomicupdate/permissions.sql new file mode 100644 index 0000000000..c2cacab842 --- /dev/null +++ b/installer/data/mysql/atomicupdate/permissions.sql @@ -0,0 +1,50 @@ +ALTER TABLE user_permissions + DROP FOREIGN KEY user_permissions_ibfk_2, + DROP index user_permissions_ibfk_2, + DROP COLUMN module_bit; + +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 ); + +ALTER TABLE permissions ADD FOREIGN KEY ( parent ) REFERENCES permissions ( code ); + +ALTER TABLE user_permissions ADD CONSTRAINT user_permissions_ibfk_2 FOREIGN KEY (code) REFERENCES permissions(code); + +INSERT INTO permissions ( parent, code, description ) VALUES ( NULL, 'superlibrarian', 'Access to all librarian functions' ); + +INSERT INTO permissions ( parent, code, description ) +SELECT 'superlibrarian', flag, flagdesc FROM userflags + WHERE flag != 'superlibrarian'; + +UPDATE permissions LEFT JOIN userflags ON ( userflags.bit = permissions.module_bit ) SET parent = flag WHERE flag != 'superlibrarian'; + +INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'superlibrarian' FROM borrowers WHERE substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -1, 1 ) = '1'; +INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'circulate' FROM borrowers WHERE substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -2, 1 ) = '1'; +INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'catalogue' FROM borrowers WHERE substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -3, 1 ) = '1'; +INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'parameters' FROM borrowers WHERE substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -4, 1 ) = '1'; +INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'borrowers' FROM borrowers WHERE substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -5, 1 ) = '1'; +INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'permissions' FROM borrowers WHERE substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -6, 1 ) = '1'; +INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'reserveforothers' FROM borrowers WHERE substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -7, 1 ) = '1'; +INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'editcatalogue' FROM borrowers WHERE substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -8, 1 ) = '1'; +INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'updatecharges' FROM borrowers WHERE substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -9, 1 ) = '1'; +INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'acquisition' FROM borrowers WHERE substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -10, 1 ) = '1'; +INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'management' FROM borrowers WHERE substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -11, 1 ) = '1'; +INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'tools' FROM borrowers WHERE substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -12, 1 ) = '1'; +INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'editauthorities' FROM borrowers WHERE substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -13, 1 ) = '1'; +INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'serials' FROM borrowers WHERE substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -14, 1 ) = '1'; +INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'reports' FROM borrowers WHERE substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -15, 1 ) = '1'; +INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'staffaccess' FROM borrowers WHERE substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -16, 1 ) = '1'; +INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'coursereserves' FROM borrowers WHERE substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -17, 1 ) = '1'; +INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'plugins' FROM borrowers WHERE substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -18, 1 ) = '1'; +INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'lists' FROM borrowers WHERE substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -19, 1 ) = '1'; +INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'clubs' FROM borrowers WHERE substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -20, 1 ) = '1'; + +ALTER TABLE permissions DROP COLUMN module_bit; + +ALTER TABLE borrowers DROP COLUMN flags; + +DROP TABLE userflags; diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 909eda25aa..2e724920f2 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -1617,7 +1617,6 @@ CREATE TABLE `borrowers` ( -- this table includes information about your patrons `relationship` varchar(100) default NULL, -- used for children to include the relationship to their guarantor `sex` varchar(1) default NULL, -- patron/borrower's gender `password` varchar(60) default NULL, -- patron/borrower's Bcrypt encrypted password - `flags` int(11) default NULL, -- will include a number associated with the staff member's permissions `userid` varchar(75) default NULL, -- patron/borrower's opac and/or staff client log in `opacnote` LONGTEXT, -- a note on the patron/borrower's account that is visible in the OPAC and staff client `contactnote` varchar(255) default NULL, -- a note related to the patron/borrower's alternate address @@ -2231,19 +2230,6 @@ CREATE TABLE `tags_index` ( -- a weighted list of all tags and where they are us REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; --- --- Table structure for table `userflags` --- - -DROP TABLE IF EXISTS `userflags`; -CREATE TABLE `userflags` ( - `bit` int(11) NOT NULL default 0, - `flag` varchar(30) default NULL, - `flagdesc` varchar(255) default NULL, - `defaulton` int(11) default NULL, - PRIMARY KEY (`bit`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; - -- -- Table structure for table `virtualshelves` -- @@ -2429,12 +2415,12 @@ CREATE TABLE language_script_mapping ( DROP TABLE IF EXISTS `permissions`; CREATE TABLE `permissions` ( - `module_bit` int(11) NOT NULL DEFAULT 0, - `code` varchar(64) NOT NULL DEFAULT '', - `description` varchar(255) 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; -- @@ -2457,13 +2443,12 @@ CREATE TABLE `serialitems` ( 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) DEFAULT NULL, - 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 + `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` (`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; -- -- 2.15.1 (Apple Git-101)