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 (-31 / +12 lines)
Lines 1274-1280 CREATE TABLE `borrower_modifications` ( Link Here
1274
  `relationship` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
1274
  `relationship` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
1275
  `sex` varchar(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
1275
  `sex` varchar(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
1276
  `password` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
1276
  `password` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
1277
  `flags` int(11) DEFAULT NULL,
1278
  `userid` varchar(75) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
1277
  `userid` varchar(75) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
1279
  `opacnote` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
1278
  `opacnote` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
1280
  `contactnote` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
1279
  `contactnote` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
Lines 1392-1398 CREATE TABLE `borrowers` ( Link Here
1392
  `relationship` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'used for children to include the relationship to their guarantor',
1391
  `relationship` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'used for children to include the relationship to their guarantor',
1393
  `sex` varchar(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s gender',
1392
  `sex` varchar(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s gender',
1394
  `password` varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s Bcrypt encrypted password',
1393
  `password` varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s Bcrypt encrypted password',
1395
  `flags` int(11) DEFAULT NULL COMMENT 'will include a number associated with the staff member''s permissions',
1396
  `userid` varchar(75) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s opac and/or staff interface log in',
1394
  `userid` varchar(75) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s opac and/or staff interface log in',
1397
  `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',
1395
  `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',
1398
  `contactnote` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'a note related to the patron/borrower''s alternate address',
1396
  `contactnote` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'a note related to the patron/borrower''s alternate address',
Lines 2399-2405 CREATE TABLE `deletedborrowers` ( Link Here
2399
  `relationship` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'used for children to include the relationship to their guarantor',
2397
  `relationship` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'used for children to include the relationship to their guarantor',
2400
  `sex` varchar(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s gender',
2398
  `sex` varchar(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s gender',
2401
  `password` varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s encrypted password',
2399
  `password` varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s encrypted password',
2402
  `flags` int(11) DEFAULT NULL COMMENT 'will include a number associated with the staff member''s permissions',
2403
  `userid` varchar(75) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s opac and/or staff interface log in',
2400
  `userid` varchar(75) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s opac and/or staff interface log in',
2404
  `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',
2401
  `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',
2405
  `contactnote` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'a note related to the patron/borrower''s alternate address',
2402
  `contactnote` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'a note related to the patron/borrower''s alternate address',
Lines 4069-4079 DROP TABLE IF EXISTS `permissions`; Link Here
4069
/*!40101 SET @saved_cs_client     = @@character_set_client */;
4066
/*!40101 SET @saved_cs_client     = @@character_set_client */;
4070
/*!40101 SET character_set_client = utf8 */;
4067
/*!40101 SET character_set_client = utf8 */;
4071
CREATE TABLE `permissions` (
4068
CREATE TABLE `permissions` (
4072
  `module_bit` int(11) NOT NULL DEFAULT 0,
4069
  `parent` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
4073
  `code` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
4070
  `code` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
4074
  `description` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
4071
  `description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
4075
  PRIMARY KEY (`module_bit`,`code`),
4072
  PRIMARY KEY (`code`),
4076
  CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`) ON DELETE CASCADE ON UPDATE CASCADE
4073
  KEY `parent` (`parent`),
4074
  CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`parent`) REFERENCES `permissions` (`code`)
4077
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
4075
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
4078
/*!40101 SET character_set_client = @saved_cs_client */;
4076
/*!40101 SET character_set_client = @saved_cs_client */;
4079
4077
Lines 5158-5187 CREATE TABLE `uploaded_files` ( Link Here
5158
DROP TABLE IF EXISTS `user_permissions`;
5156
DROP TABLE IF EXISTS `user_permissions`;
5159
/*!40101 SET @saved_cs_client     = @@character_set_client */;
5157
/*!40101 SET @saved_cs_client     = @@character_set_client */;
5160
/*!40101 SET character_set_client = utf8 */;
5158
/*!40101 SET character_set_client = utf8 */;
5159
DROP TABLE IF EXISTS `user_permissions`;
5161
CREATE TABLE `user_permissions` (
5160
CREATE TABLE `user_permissions` (
5162
  `borrowernumber` int(11) NOT NULL DEFAULT 0,
5161
  `borrowernumber` int(11) NOT NULL DEFAULT '0',
5163
  `module_bit` int(11) NOT NULL DEFAULT 0,
5162
  `code` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
5164
  `code` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
5165
  KEY `user_permissions_ibfk_1` (`borrowernumber`),
5163
  KEY `user_permissions_ibfk_1` (`borrowernumber`),
5166
  KEY `user_permissions_ibfk_2` (`module_bit`,`code`),
5164
  KEY `user_permissions_ibfk_2` (`code`),
5167
  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`),
5168
  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
5169
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
5170
/*!40101 SET character_set_client = @saved_cs_client */;
5171
5172
--
5173
-- Table structure for table `userflags`
5174
--
5175
5176
DROP TABLE IF EXISTS `userflags`;
5177
/*!40101 SET @saved_cs_client     = @@character_set_client */;
5178
/*!40101 SET character_set_client = utf8 */;
5179
CREATE TABLE `userflags` (
5180
  `bit` int(11) NOT NULL DEFAULT 0,
5181
  `flag` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
5182
  `flagdesc` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
5183
  `defaulton` int(11) DEFAULT NULL,
5184
  PRIMARY KEY (`bit`)
5185
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
5167
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
5186
/*!40101 SET character_set_client = @saved_cs_client */;
5168
/*!40101 SET character_set_client = @saved_cs_client */;
5187
5169
5188
- 

Return to bug 20813