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