Attachment 75542 Details for Bug 20813 – Bug 20813: Update db schema

[patch] Bug 20813: Update db schema

Bug-20813-Update-db-schema.patch (text/plain), 9.18 KB, created by Kyle M Hall (khall) on 2018-05-24 13:45:24 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Kyle M Hall (khall)

Created: 2018-05-24 13:45:24 UTC

Size: 9.18 KB

patch

obsolete

>From 857f4c4b3a2232f6240c050c71af503f4c900fbc Mon Sep 17 00:00:00 2001
>From: Kyle M Hall <kyle@bywatersolutions.com>
>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)

Actions: View | Diff | Splinter Review

Attachments on bug 20813: 75542 | 75543 | 75544 | 75545 | 124834 | 124835 | 124836 | 126280 | 126281 | 126282 | 126283 | 126284 | 126285 | 126287 | 126288 | 126289