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 |
} |