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 1617-1623 CREATE TABLE `borrowers` ( -- this table includes information about your patrons Link Here
1617
  `relationship` varchar(100) default NULL, -- used for children to include the relationship to their guarantor
1617
  `relationship` varchar(100) default NULL, -- used for children to include the relationship to their guarantor
1618
  `sex` varchar(1) default NULL, -- patron/borrower's gender
1618
  `sex` varchar(1) default NULL, -- patron/borrower's gender
1619
  `password` varchar(60) default NULL, -- patron/borrower's Bcrypt encrypted password
1619
  `password` varchar(60) default NULL, -- patron/borrower's Bcrypt encrypted password
1620
  `flags` int(11) default NULL, -- will include a number associated with the staff member's permissions
1621
  `userid` varchar(75) default NULL, -- patron/borrower's opac and/or staff client log in
1620
  `userid` varchar(75) default NULL, -- patron/borrower's opac and/or staff client log in
1622
  `opacnote` LONGTEXT, -- a note on the patron/borrower's account that is visible in the OPAC and staff client
1621
  `opacnote` LONGTEXT, -- a note on the patron/borrower's account that is visible in the OPAC and staff client
1623
  `contactnote` varchar(255) default NULL, -- a note related to the patron/borrower's alternate address
1622
  `contactnote` varchar(255) default NULL, -- a note related to the patron/borrower's alternate address
Lines 2231-2249 CREATE TABLE `tags_index` ( -- a weighted list of all tags and where they are us Link Here
2231
        REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2230
        REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2232
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2231
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2233
2232
2234
--
2235
-- Table structure for table `userflags`
2236
--
2237
2238
DROP TABLE IF EXISTS `userflags`;
2239
CREATE TABLE `userflags` (
2240
  `bit` int(11) NOT NULL default 0,
2241
  `flag` varchar(30) default NULL,
2242
  `flagdesc` varchar(255) default NULL,
2243
  `defaulton` int(11) default NULL,
2244
  PRIMARY KEY  (`bit`)
2245
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2246
2247
--
2233
--
2248
-- Table structure for table `virtualshelves`
2234
-- Table structure for table `virtualshelves`
2249
--
2235
--
Lines 2429-2440 CREATE TABLE language_script_mapping ( Link Here
2429
2415
2430
DROP TABLE IF EXISTS `permissions`;
2416
DROP TABLE IF EXISTS `permissions`;
2431
CREATE TABLE `permissions` (
2417
CREATE TABLE `permissions` (
2432
  `module_bit` int(11) NOT NULL DEFAULT 0,
2418
  `parent` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
2433
  `code` varchar(64) NOT NULL DEFAULT '',
2419
  `code` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
2434
  `description` varchar(255) DEFAULT NULL,
2420
  `description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
2435
  PRIMARY KEY  (`module_bit`, `code`),
2421
  PRIMARY KEY (`code`),
2436
  CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2422
  KEY `parent` (`parent`),
2437
    ON DELETE CASCADE ON UPDATE CASCADE
2423
  CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`parent`) REFERENCES `permissions` (`code`)
2438
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2424
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2439
2425
2440
--
2426
--
Lines 2457-2469 CREATE TABLE `serialitems` ( Link Here
2457
2443
2458
DROP TABLE IF EXISTS `user_permissions`;
2444
DROP TABLE IF EXISTS `user_permissions`;
2459
CREATE TABLE `user_permissions` (
2445
CREATE TABLE `user_permissions` (
2460
  `borrowernumber` int(11) NOT NULL DEFAULT 0,
2446
  `borrowernumber` int(11) NOT NULL DEFAULT '0',
2461
  `module_bit` int(11) NOT NULL DEFAULT 0,
2447
  `code` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
2462
  `code` varchar(64) DEFAULT NULL,
2448
  KEY `user_permissions_ibfk_1` (`borrowernumber`),
2463
  CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2449
  KEY `user_permissions_ibfk_2` (`code`),
2464
    ON DELETE CASCADE ON UPDATE CASCADE,
2450
  CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`code`) REFERENCES `permissions` (`code`),
2465
  CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2451
  CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2466
    ON DELETE CASCADE ON UPDATE CASCADE
2467
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2452
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2468
2453
2469
--
2454
--
2470
- 

Return to bug 20813