Bugzilla – Attachment 124834 Details for
Bug 20813
Revamp user permissions system
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 20813: Update db schema
Bug-20813-Update-db-schema.patch (text/plain), 8.75 KB, created by
Kyle M Hall (khall)
on 2021-09-13 12:59:34 UTC
(
hide
)
Description:
Bug 20813: Update db schema
Filename:
MIME Type:
Creator:
Kyle M Hall (khall)
Created:
2021-09-13 12:59:34 UTC
Size:
8.75 KB
patch
obsolete
>From d296a9cbaa7d7f66d468cc09d66cd06eae422178 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 > >--- > .../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 02cf864ddd..f1ef34612c 100644 >--- a/installer/data/mysql/kohastructure.sql >+++ b/installer/data/mysql/kohastructure.sql >@@ -4068,11 +4068,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 */; > >@@ -5155,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.1 (Apple Git-130)
<b>You cannot view the attachment while viewing its details because your browser does not support IFRAMEs. <a href="attachment.cgi?id=124834">View the attachment on a separate page</a>.</b>
View Attachment As Diff
View Attachment As Raw
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