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

(-)a/installer/data/mysql/atomicupdate/permissions.sql (-1 / +71 lines)
Lines 1-50 Link Here
1
use Modern::Perl;
2
3
return {
4
    bug_number => "20813",
5
    description => "Revamp user permissions system",
6
    up => sub {
7
        my ($args) = @_;
8
        my ($dbh, $out) = @$args{qw(dbh out)};
9
10
        $dbh->do(q{
1
ALTER TABLE user_permissions
11
ALTER TABLE user_permissions
2
    DROP FOREIGN KEY user_permissions_ibfk_2,
12
    DROP FOREIGN KEY user_permissions_ibfk_2,
3
    DROP index user_permissions_ibfk_2,
13
    DROP index user_permissions_ibfk_2,
4
    DROP COLUMN module_bit;
14
    DROP COLUMN module_bit;
15
        });
5
16
17
        $dbh->do(q{
6
ALTER TABLE permissions
18
ALTER TABLE permissions
7
    DROP PRIMARY KEY,
19
    DROP PRIMARY KEY,
8
    DROP FOREIGN KEY permissions_ibfk_1,
20
    DROP FOREIGN KEY permissions_ibfk_1,
9
    ADD PRIMARY KEY ( code ),
21
    ADD PRIMARY KEY ( code ),
10
    ADD COLUMN `parent` VARCHAR(64) NULL DEFAULT NULL FIRST,
22
    ADD COLUMN `parent` VARCHAR(64) NULL DEFAULT NULL FIRST,
11
    ADD KEY parent ( parent );
23
    ADD KEY parent ( parent );
24
        });
12
25
26
        $dbh->do(q{
13
ALTER TABLE permissions ADD FOREIGN KEY ( parent ) REFERENCES permissions ( code );
27
ALTER TABLE permissions ADD FOREIGN KEY ( parent ) REFERENCES permissions ( code );
28
        });
14
29
30
        $dbh->do(q{
15
ALTER TABLE user_permissions ADD CONSTRAINT user_permissions_ibfk_2 FOREIGN KEY (code) REFERENCES permissions(code);
31
ALTER TABLE user_permissions ADD CONSTRAINT user_permissions_ibfk_2 FOREIGN KEY (code) REFERENCES permissions(code);
32
        });
16
33
34
        $dbh->do(q{
17
INSERT INTO permissions ( parent, code, description ) VALUES ( NULL, 'superlibrarian', 'Access to all librarian functions' );
35
INSERT INTO permissions ( parent, code, description ) VALUES ( NULL, 'superlibrarian', 'Access to all librarian functions' );
36
        });
18
37
38
        $dbh->do(q{
19
INSERT INTO permissions ( parent, code, description )
39
INSERT INTO permissions ( parent, code, description )
20
SELECT 'superlibrarian', flag, flagdesc FROM userflags
40
SELECT 'superlibrarian', flag, flagdesc FROM userflags
21
 WHERE flag != 'superlibrarian';
41
 WHERE flag != 'superlibrarian';
42
        });
22
43
44
        $dbh->do(q{
23
UPDATE permissions LEFT JOIN userflags ON ( userflags.bit = permissions.module_bit ) SET parent = flag WHERE flag != 'superlibrarian';
45
UPDATE permissions LEFT JOIN userflags ON ( userflags.bit = permissions.module_bit ) SET parent = flag WHERE flag != 'superlibrarian';
46
        });
24
47
48
        $dbh->do(q{
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';
49
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'superlibrarian'   FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ),  -1, 1 ) = '1';
50
        });
51
        $dbh->do(q{
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';
52
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'circulate'        FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ),  -2, 1 ) = '1';
53
        });
54
        $dbh->do(q{
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';
55
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'catalogue'        FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ),  -3, 1 ) = '1';
56
        });
57
        $dbh->do(q{
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';
58
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'parameters'       FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ),  -4, 1 ) = '1';
59
        });
60
        $dbh->do(q{
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';
61
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'borrowers'        FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ),  -5, 1 ) = '1';
62
        });
63
        $dbh->do(q{
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';
64
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'permissions'      FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ),  -6, 1 ) = '1';
65
        });
66
        $dbh->do(q{
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';
67
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'reserveforothers' FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ),  -7, 1 ) = '1';
68
        });
69
        $dbh->do(q{
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';
70
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'editcatalogue'    FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ),  -8, 1 ) = '1';
71
        });
72
        $dbh->do(q{
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';
73
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'updatecharges'    FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ),  -9, 1 ) = '1';
74
        });
75
        $dbh->do(q{
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';
76
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'acquisition'      FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -10, 1 ) = '1';
77
        });
78
        $dbh->do(q{
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';
79
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'management'       FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -11, 1 ) = '1';
80
        });
81
        $dbh->do(q{
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';
82
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'tools'            FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -12, 1 ) = '1';
83
        });
84
        $dbh->do(q{
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';
85
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'editauthorities'  FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -13, 1 ) = '1';
86
        });
87
        $dbh->do(q{
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';
88
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'serials'          FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -14, 1 ) = '1';
89
        });
90
        $dbh->do(q{
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';
91
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'reports'          FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -15, 1 ) = '1';
92
        });
93
        $dbh->do(q{
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';
94
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'staffaccess'      FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -16, 1 ) = '1';
95
        });
96
        $dbh->do(q{
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';
97
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'coursereserves'   FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -17, 1 ) = '1';
98
        });
99
        $dbh->do(q{
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';
100
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'plugins'          FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -18, 1 ) = '1';
101
        });
102
        $dbh->do(q{
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';
103
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'lists'            FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -19, 1 ) = '1';
104
        });
105
        $dbh->do(q{
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';
106
INSERT INTO user_permissions ( borrowernumber, code ) SELECT borrowernumber, 'clubs'            FROM borrowers WHERE  substring( lpad( conv( borrowers.flags, 10, 2 ), 21, '0' ), -20, 1 ) = '1';
107
        });
45
108
109
        $dbh->do(q{
46
ALTER TABLE permissions DROP COLUMN module_bit;
110
ALTER TABLE permissions DROP COLUMN module_bit;
111
        });
47
112
113
        $dbh->do(q{
48
ALTER TABLE borrowers DROP COLUMN flags;
114
ALTER TABLE borrowers DROP COLUMN flags;
115
        });
49
116
117
        $dbh->do(q{
50
DROP TABLE userflags;
118
DROP TABLE userflags;
51
- 
119
        });
120
    },
121
}

Return to bug 20813