View | Details | Raw Unified | Return to bug 20813
Collapse All | Expand All

(-)a/installer/data/mysql/atomicupdate/permissions.sql (+50 lines)
Line 0 Link Here
1
ALTER TABLE user_permissions
2
    DROP FOREIGN KEY user_permissions_ibfk_2,
3
    DROP index user_permissions_ibfk_2,
4
    DROP COLUMN module_bit;
5
6
ALTER TABLE permissions
7
    DROP PRIMARY KEY,
8
    DROP FOREIGN KEY permissions_ibfk_1,
9
    ADD PRIMARY KEY ( code ),
10
    ADD COLUMN `parent` VARCHAR(64) NULL DEFAULT NULL FIRST,
11
    ADD KEY parent ( parent );
12
13
ALTER TABLE permissions ADD FOREIGN KEY ( parent ) REFERENCES permissions ( code );
14
15
ALTER TABLE user_permissions ADD CONSTRAINT user_permissions_ibfk_2 FOREIGN KEY (code) REFERENCES permissions(code);
16
17
INSERT INTO permissions ( parent, code, description ) VALUES ( NULL, 'superlibrarian', 'Access to all librarian functions' );
18
19
INSERT INTO permissions ( parent, code, description )
20
SELECT 'superlibrarian', flag, flagdesc FROM userflags
21
 WHERE flag != 'superlibrarian';
22
23
UPDATE permissions LEFT JOIN userflags ON ( userflags.bit = permissions.module_bit ) SET parent = flag WHERE flag != 'superlibrarian';
24
25
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'superlibrarian'   FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ),  -1, 1 ) = '1';
26
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'circulate'        FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ),  -2, 1 ) = '1';
27
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'catalogue'        FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ),  -3, 1 ) = '1';
28
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'parameters'       FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ),  -4, 1 ) = '1';
29
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'borrowers'        FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ),  -5, 1 ) = '1';
30
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'permissions'      FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ),  -6, 1 ) = '1';
31
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'reserveforothers' FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ),  -7, 1 ) = '1';
32
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'editcatalogue'    FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ),  -8, 1 ) = '1';
33
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'updatecharges'    FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ),  -9, 1 ) = '1';
34
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'acquisition'      FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -10, 1 ) = '1';
35
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'management'       FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -11, 1 ) = '1';
36
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'tools'            FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -12, 1 ) = '1';
37
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'editauthorities'  FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -13, 1 ) = '1';
38
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'serials'          FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -14, 1 ) = '1';
39
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'reports'          FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -15, 1 ) = '1';
40
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'staffaccess'      FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -16, 1 ) = '1';
41
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'coursereserves'   FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -17, 1 ) = '1';
42
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'plugins'          FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -18, 1 ) = '1';
43
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'lists'            FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -19, 1 ) = '1';
44
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'clubs'            FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -20, 1 ) = '1';
45
46
ALTER TABLE permissions DROP COLUMN module_bit;
47
48
ALTER TABLE borrowers DROP COLUMN flags;
49
50
DROP TABLE userflags;
(-)a/installer/data/mysql/kohastructure.sql (-28 / +12 lines)
Lines 4068-4078 DROP TABLE IF EXISTS `permissions`; Link Here
4068
/*!40101 SET @saved_cs_client     = @@character_set_client */;
4068
/*!40101 SET @saved_cs_client     = @@character_set_client */;
4069
/*!40101 SET character_set_client = utf8 */;
4069
/*!40101 SET character_set_client = utf8 */;
4070
CREATE TABLE `permissions` (
4070
CREATE TABLE `permissions` (
4071
  `module_bit` int(11) NOT NULL DEFAULT 0,
4071
  `parent` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
4072
  `code` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
4072
  `code` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
4073
  `description` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
4073
  `description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
4074
  PRIMARY KEY (`module_bit`,`code`),
4074
  PRIMARY KEY (`code`),
4075
  CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`) ON DELETE CASCADE ON UPDATE CASCADE
4075
  KEY `parent` (`parent`),
4076
  CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`parent`) REFERENCES `permissions` (`code`)
4076
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
4077
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
4077
/*!40101 SET character_set_client = @saved_cs_client */;
4078
/*!40101 SET character_set_client = @saved_cs_client */;
4078
4079
Lines 5155-5184 CREATE TABLE `uploaded_files` ( Link Here
5155
DROP TABLE IF EXISTS `user_permissions`;
5156
DROP TABLE IF EXISTS `user_permissions`;
5156
/*!40101 SET @saved_cs_client     = @@character_set_client */;
5157
/*!40101 SET @saved_cs_client     = @@character_set_client */;
5157
/*!40101 SET character_set_client = utf8 */;
5158
/*!40101 SET character_set_client = utf8 */;
5159
DROP TABLE IF EXISTS `user_permissions`;
5158
CREATE TABLE `user_permissions` (
5160
CREATE TABLE `user_permissions` (
5159
  `borrowernumber` int(11) NOT NULL DEFAULT 0,
5161
  `borrowernumber` int(11) NOT NULL DEFAULT '0',
5160
  `module_bit` int(11) NOT NULL DEFAULT 0,
5162
  `code` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
5161
  `code` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
5162
  KEY `user_permissions_ibfk_1` (`borrowernumber`),
5163
  KEY `user_permissions_ibfk_1` (`borrowernumber`),
5163
  KEY `user_permissions_ibfk_2` (`module_bit`,`code`),
5164
  KEY `user_permissions_ibfk_2` (`code`),
5164
  CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
5165
  CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`code`) REFERENCES `permissions` (`code`),
5165
  CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
5166
  CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
5166
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
5167
/*!40101 SET character_set_client = @saved_cs_client */;
5168
5169
--
5170
-- Table structure for table `userflags`
5171
--
5172
5173
DROP TABLE IF EXISTS `userflags`;
5174
/*!40101 SET @saved_cs_client     = @@character_set_client */;
5175
/*!40101 SET character_set_client = utf8 */;
5176
CREATE TABLE `userflags` (
5177
  `bit` int(11) NOT NULL DEFAULT 0,
5178
  `flag` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
5179
  `flagdesc` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
5180
  `defaulton` int(11) DEFAULT NULL,
5181
  PRIMARY KEY (`bit`)
5182
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
5167
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
5183
/*!40101 SET character_set_client = @saved_cs_client */;
5168
/*!40101 SET character_set_client = @saved_cs_client */;
5184
5169
5185
- 

Return to bug 20813