OLD | NEW |
(Empty) | |
| 1 -- Copyright 2016 The Chromium Authors. All Rights Reserved. |
| 2 -- |
| 3 -- Use of this source code is governed by a BSD-style |
| 4 -- license that can be found in the LICENSE file or at |
| 5 -- https://developers.google.com/open-source/licenses/bsd |
| 6 |
| 7 |
| 8 DROP PROCEDURE IF EXISTS InspectStatusCase; |
| 9 DROP PROCEDURE IF EXISTS CleanupStatusCase; |
| 10 DROP PROCEDURE IF EXISTS InspectLabelCase; |
| 11 DROP PROCEDURE IF EXISTS CleanupLabelCase; |
| 12 DROP PROCEDURE IF EXISTS InspectPermissionCase; |
| 13 DROP PROCEDURE IF EXISTS CleanupPermissionCase; |
| 14 DROP PROCEDURE IF EXISTS InspectComponentCase; |
| 15 DROP PROCEDURE IF EXISTS CleanupComponentCase; |
| 16 DROP PROCEDURE IF EXISTS CleanupCase; |
| 17 |
| 18 delimiter // |
| 19 |
| 20 CREATE PROCEDURE InspectStatusCase(IN in_pid SMALLINT UNSIGNED) |
| 21 BEGIN |
| 22 DECLARE done INT DEFAULT FALSE; |
| 23 |
| 24 DECLARE c_id INT; |
| 25 DECLARE c_pid SMALLINT UNSIGNED; |
| 26 DECLARE c_status VARCHAR(80) BINARY; |
| 27 |
| 28 DECLARE curs CURSOR FOR SELECT id, project_id, status FROM StatusDef WHERE pro
ject_id=in_pid AND rank IS NOT NULL ORDER BY rank; |
| 29 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; |
| 30 |
| 31 OPEN curs; |
| 32 |
| 33 wks_loop: LOOP |
| 34 FETCH curs INTO c_id, c_pid, c_status; |
| 35 IF done THEN |
| 36 LEAVE wks_loop; |
| 37 END IF; |
| 38 |
| 39 -- This is the canonical capitalization of the well-known status. |
| 40 SELECT c_status AS 'Processing:'; |
| 41 |
| 42 -- Alternate forms are a) in the same project, and b) spelled the same, |
| 43 -- but c) not the same exact status. |
| 44 DROP TEMPORARY TABLE IF EXISTS alt_ids; |
| 45 CREATE TEMPORARY TABLE alt_ids (id INT); |
| 46 INSERT INTO alt_ids SELECT id FROM StatusDef WHERE project_id=c_pid AND stat
us COLLATE UTF8_GENERAL_CI LIKE c_status AND id!=c_id; |
| 47 SELECT status AS 'Alternate forms:' FROM StatusDef WHERE id IN (SELECT id FR
OM alt_ids); |
| 48 SELECT id AS 'Offending issues:' FROM Issue WHERE status_id IN (SELECT id FR
OM alt_ids); |
| 49 END LOOP; |
| 50 |
| 51 CLOSE curs; |
| 52 END; |
| 53 // |
| 54 |
| 55 CREATE PROCEDURE CleanupStatusCase(IN in_pid SMALLINT UNSIGNED) |
| 56 BEGIN |
| 57 DECLARE done INT DEFAULT FALSE; |
| 58 |
| 59 DECLARE c_id INT; |
| 60 DECLARE c_pid SMALLINT UNSIGNED; |
| 61 DECLARE c_status VARCHAR(80) BINARY; |
| 62 |
| 63 DECLARE curs CURSOR FOR SELECT id, project_id, status FROM StatusDef WHERE pro
ject_id=in_pid AND rank IS NOT NULL ORDER BY rank; |
| 64 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; |
| 65 |
| 66 OPEN curs; |
| 67 |
| 68 wks_loop: LOOP |
| 69 FETCH curs INTO c_id, c_pid, c_status; |
| 70 IF done THEN |
| 71 LEAVE wks_loop; |
| 72 END IF; |
| 73 |
| 74 SELECT c_status AS 'Processing:'; |
| 75 DROP TEMPORARY TABLE IF EXISTS alt_ids; |
| 76 CREATE TEMPORARY TABLE alt_ids (id INT); |
| 77 INSERT INTO alt_ids SELECT id FROM StatusDef WHERE project_id=c_pid AND stat
us COLLATE UTF8_GENERAL_CI LIKE c_status AND id!=c_id; |
| 78 |
| 79 -- Fix offending issues first, to avoid foreign key constraints. |
| 80 UPDATE Issue SET status_id=c_id WHERE status_id IN (SELECT id FROM alt_ids); |
| 81 |
| 82 -- Then remove the alternate status definitions. |
| 83 DELETE FROM StatusDef WHERE id IN (SELECT id FROM alt_ids); |
| 84 END LOOP; |
| 85 |
| 86 CLOSE curs; |
| 87 END; |
| 88 // |
| 89 |
| 90 CREATE PROCEDURE InspectLabelCase(IN in_pid SMALLINT UNSIGNED) |
| 91 BEGIN |
| 92 DECLARE done INT DEFAULT FALSE; |
| 93 |
| 94 DECLARE c_id INT; |
| 95 DECLARE c_pid SMALLINT UNSIGNED; |
| 96 DECLARE c_label VARCHAR(80) BINARY; |
| 97 |
| 98 DECLARE curs CURSOR FOR SELECT id, project_id, label FROM LabelDef WHERE proje
ct_id=in_pid AND rank IS NOT NULL ORDER BY rank; |
| 99 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; |
| 100 |
| 101 OPEN curs; |
| 102 |
| 103 wkl_loop: LOOP |
| 104 FETCH curs INTO c_id, c_pid, c_label; |
| 105 IF done THEN |
| 106 LEAVE wkl_loop; |
| 107 END IF; |
| 108 |
| 109 -- This is the canonical capitalization of the well-known label. |
| 110 SELECT c_label AS 'Processing:'; |
| 111 |
| 112 -- Alternate forms are a) in the same project, and b) spelled the same, |
| 113 -- but c) not the same exact label. |
| 114 DROP TEMPORARY TABLE IF EXISTS alt_ids; |
| 115 CREATE TEMPORARY TABLE alt_ids (id INT); |
| 116 INSERT INTO alt_ids SELECT id FROM LabelDef WHERE project_id=c_pid AND label
COLLATE UTF8_GENERAL_CI LIKE c_label AND id!=c_id; |
| 117 SELECT label AS 'Alternate forms:' FROM LabelDef WHERE id IN (SELECT id FROM
alt_ids); |
| 118 SELECT issue_id AS 'Offending issues:' FROM Issue2Label WHERE label_id IN (S
ELECT id FROM alt_ids); |
| 119 END LOOP; |
| 120 |
| 121 CLOSE curs; |
| 122 END; |
| 123 // |
| 124 |
| 125 CREATE PROCEDURE CleanupLabelCase(IN in_pid SMALLINT UNSIGNED) |
| 126 BEGIN |
| 127 DECLARE done INT DEFAULT FALSE; |
| 128 |
| 129 DECLARE c_id INT; |
| 130 DECLARE c_pid SMALLINT UNSIGNED; |
| 131 DECLARE c_label VARCHAR(80) BINARY; |
| 132 |
| 133 DECLARE curs CURSOR FOR SELECT id, project_id, label FROM LabelDef WHERE proje
ct_id=in_pid AND rank IS NOT NULL ORDER BY rank; |
| 134 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; |
| 135 |
| 136 OPEN curs; |
| 137 |
| 138 wkl_loop: LOOP |
| 139 FETCH curs INTO c_id, c_pid, c_label; |
| 140 IF done THEN |
| 141 LEAVE wkl_loop; |
| 142 END IF; |
| 143 |
| 144 SELECT c_label AS 'Processing:'; |
| 145 DROP TEMPORARY TABLE IF EXISTS alt_ids; |
| 146 CREATE TEMPORARY TABLE alt_ids (id INT); |
| 147 INSERT INTO alt_ids SELECT id FROM LabelDef WHERE project_id=c_pid AND label
COLLATE UTF8_GENERAL_CI LIKE c_label AND id!=c_id; |
| 148 |
| 149 -- Fix offending issues first, to avoid foreign key constraints. |
| 150 -- DELETE after UPDATE IGNORE to catch issues with two spellings. |
| 151 UPDATE IGNORE Issue2Label SET label_id=c_id WHERE label_id IN (SELECT id FRO
M alt_ids); |
| 152 DELETE FROM Issue2Label WHERE label_id IN (SELECT id FROM alt_ids); |
| 153 |
| 154 -- Then remove the alternate label definitions. |
| 155 DELETE FROM LabelDef WHERE id IN (SELECT id FROM alt_ids); |
| 156 END LOOP; |
| 157 |
| 158 CLOSE curs; |
| 159 END; |
| 160 // |
| 161 |
| 162 CREATE PROCEDURE InspectPermissionCase(IN in_pid SMALLINT UNSIGNED) |
| 163 BEGIN |
| 164 DECLARE done INT DEFAULT FALSE; |
| 165 |
| 166 DECLARE c_id INT; |
| 167 DECLARE c_pid SMALLINT UNSIGNED; |
| 168 DECLARE c_label VARCHAR(80) BINARY; |
| 169 |
| 170 -- This crazy query takes the Actions table (defined below) and combines it |
| 171 -- with the set of all permissions granted in the project to construct a list |
| 172 -- of all possible Restrict-Action-Permission labels. It then combines that |
| 173 -- with LabelDef to see which ones are actually used (whether or not they are |
| 174 -- also defined as well-known labels). |
| 175 DECLARE curs CURSOR FOR SELECT LabelDef.id, LabelDef.project_id, RapDef.label
FROM ( |
| 176 SELECT DISTINCT CONCAT_WS('-', 'Restrict', Actions.action, ExtraPerm.perm) |
| 177 AS label FROM ExtraPerm, Actions where ExtraPerm.project_id=16) AS RapDef |
| 178 LEFT JOIN LabelDef |
| 179 ON BINARY RapDef.label = BINARY LabelDef.label |
| 180 WHERE LabelDef.project_id=in_pid; |
| 181 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; |
| 182 |
| 183 DROP TEMPORARY TABLE IF EXISTS Actions; |
| 184 CREATE TEMPORARY TABLE Actions (action VARCHAR(80)); |
| 185 INSERT INTO Actions (action) VALUES ('View'), ('EditIssue'), ('AddIssueComment
'), ('DeleteIssue'), ('ViewPrivateArtifact'); |
| 186 |
| 187 OPEN curs; |
| 188 |
| 189 perm_loop: LOOP |
| 190 FETCH curs INTO c_id, c_pid, c_label; |
| 191 IF done THEN |
| 192 LEAVE perm_loop; |
| 193 END IF; |
| 194 |
| 195 -- This is the canonical capitalization of the permission. |
| 196 SELECT c_label AS 'Processing:'; |
| 197 |
| 198 -- Alternate forms are a) in the same project, and b) spelled the same, |
| 199 -- but c) not the same exact label. |
| 200 DROP TEMPORARY TABLE IF EXISTS alt_ids; |
| 201 CREATE TEMPORARY TABLE alt_ids (id INT); |
| 202 INSERT INTO alt_ids SELECT id FROM LabelDef WHERE project_id=c_pid AND label
COLLATE UTF8_GENERAL_CI LIKE c_label AND id!=c_id; |
| 203 SELECT label AS 'Alternate forms:' FROM LabelDef WHERE id IN (SELECT id FROM
alt_ids); |
| 204 SELECT issue_id AS 'Offending issues:' FROM Issue2Label WHERE label_id IN (S
ELECT id FROM alt_ids); |
| 205 END LOOP; |
| 206 |
| 207 CLOSE curs; |
| 208 END; |
| 209 // |
| 210 |
| 211 CREATE PROCEDURE CleanupPermissionCase(IN in_pid SMALLINT UNSIGNED) |
| 212 BEGIN |
| 213 DECLARE done INT DEFAULT FALSE; |
| 214 |
| 215 DECLARE c_id INT; |
| 216 DECLARE c_pid SMALLINT UNSIGNED; |
| 217 DECLARE c_label VARCHAR(80) BINARY; |
| 218 |
| 219 -- This crazy query takes the Actions table (defined below) and combines it |
| 220 -- with the set of all permissions granted in the project to construct a list |
| 221 -- of all possible Restrict-Action-Permission labels. It then combines that |
| 222 -- with LabelDef to see which ones are actually used (whether or not they are |
| 223 -- also defined as well-known labels). |
| 224 DECLARE curs CURSOR FOR SELECT LabelDef.id, LabelDef.project_id, RapDef.label
FROM ( |
| 225 SELECT DISTINCT CONCAT_WS('-', 'Restrict', Actions.action, ExtraPerm.perm) |
| 226 AS label FROM ExtraPerm, Actions where ExtraPerm.project_id=16) AS RapDef |
| 227 LEFT JOIN LabelDef |
| 228 ON BINARY RapDef.label = BINARY LabelDef.label |
| 229 WHERE LabelDef.project_id=in_pid; |
| 230 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; |
| 231 |
| 232 DROP TEMPORARY TABLE IF EXISTS Actions; |
| 233 CREATE TEMPORARY TABLE Actions (action VARCHAR(80)); |
| 234 INSERT INTO Actions (action) VALUES ('View'), ('EditIssue'), ('AddIssueComment
'), ('DeleteIssue'), ('ViewPrivateArtifact'); |
| 235 |
| 236 OPEN curs; |
| 237 |
| 238 perm_loop: LOOP |
| 239 FETCH curs INTO c_id, c_pid, c_label; |
| 240 IF done THEN |
| 241 LEAVE perm_loop; |
| 242 END IF; |
| 243 |
| 244 -- This is the canonical capitalization of the permission. |
| 245 SELECT c_label AS 'Processing:'; |
| 246 |
| 247 -- Alternate forms are a) in the same project, and b) spelled the same, |
| 248 -- but c) not the same exact label. |
| 249 DROP TEMPORARY TABLE IF EXISTS alt_ids; |
| 250 CREATE TEMPORARY TABLE alt_ids (id INT); |
| 251 INSERT INTO alt_ids SELECT id FROM LabelDef WHERE project_id=c_pid AND label
COLLATE UTF8_GENERAL_CI LIKE c_label AND id!=c_id; |
| 252 |
| 253 -- Fix offending issues first, to avoid foreign key constraings. |
| 254 -- DELETE after UPDATE IGNORE to catch issues with two spellings. |
| 255 UPDATE IGNORE Issue2Label SET label_id=c_id WHERE label_id IN (SELECT id FRO
M alt_ids); |
| 256 DELETE FROM Issue2Label WHERE label_id IN (SELECT id FROM alt_ids); |
| 257 |
| 258 -- Then remove the alternate label definitions. |
| 259 DELETE FROM LabelDef WHERE id IN (SELECT id FROM alt_ids); |
| 260 END LOOP; |
| 261 |
| 262 CLOSE curs; |
| 263 |
| 264 -- Remove ExtraPerm rows where the user isn't a member of the project. |
| 265 DELETE FROM ExtraPerm WHERE project_id=in_pid AND user_id NOT IN ( |
| 266 SELECT user_id FROM User2Project WHERE project_id=in_pid); |
| 267 END; |
| 268 // |
| 269 |
| 270 CREATE PROCEDURE InspectComponentCase(IN in_pid SMALLINT UNSIGNED) |
| 271 BEGIN |
| 272 DECLARE done INT DEFAULT FALSE; |
| 273 |
| 274 DECLARE c_id INT; |
| 275 DECLARE c_pid SMALLINT UNSIGNED; |
| 276 DECLARE c_path VARCHAR(80) BINARY; |
| 277 |
| 278 DECLARE curs CURSOR FOR SELECT id, project_id, path FROM ComponentDef WHERE pr
oject_id=in_pid AND docstring IS NOT NULL ORDER BY path; |
| 279 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; |
| 280 |
| 281 OPEN curs; |
| 282 |
| 283 wks_loop: LOOP |
| 284 FETCH curs INTO c_id, c_pid, c_path; |
| 285 IF done THEN |
| 286 LEAVE wks_loop; |
| 287 END IF; |
| 288 |
| 289 -- This is the canonical capitalization of the component path. |
| 290 SELECT c_path AS 'Processing:'; |
| 291 |
| 292 -- Alternate forms are a) in the same project, and b) spelled the same, |
| 293 -- but c) not the same exact path. |
| 294 DROP TEMPORARY TABLE IF EXISTS alt_ids; |
| 295 CREATE TEMPORARY TABLE alt_ids (id INT); |
| 296 INSERT INTO alt_ids SELECT id FROM ComponentDef WHERE project_id=c_pid AND p
ath COLLATE UTF8_GENERAL_CI LIKE c_path AND id!=c_id; |
| 297 SELECT path AS 'Alternate forms:' FROM ComponentDef WHERE id IN (SELECT id F
ROM alt_ids); |
| 298 SELECT issue_id AS 'Offending issues:' FROM Issue2Component WHERE component_
id IN (SELECT id FROM alt_ids); |
| 299 END LOOP; |
| 300 |
| 301 CLOSE curs; |
| 302 END; |
| 303 // |
| 304 |
| 305 CREATE PROCEDURE CleanupComponentCase(IN in_pid SMALLINT UNSIGNED) |
| 306 BEGIN |
| 307 DECLARE done INT DEFAULT FALSE; |
| 308 |
| 309 DECLARE c_id INT; |
| 310 DECLARE c_pid SMALLINT UNSIGNED; |
| 311 DECLARE c_path VARCHAR(80) BINARY; |
| 312 |
| 313 DECLARE curs CURSOR FOR SELECT id, project_id, path FROM ComponentDef WHERE pr
oject_id=in_pid AND docstring IS NOT NULL ORDER BY path; |
| 314 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; |
| 315 |
| 316 OPEN curs; |
| 317 |
| 318 wks_loop: LOOP |
| 319 FETCH curs INTO c_id, c_pid, c_path; |
| 320 IF done THEN |
| 321 LEAVE wks_loop; |
| 322 END IF; |
| 323 |
| 324 SELECT c_path AS 'Processing:'; |
| 325 DROP TEMPORARY TABLE IF EXISTS alt_ids; |
| 326 CREATE TEMPORARY TABLE alt_ids (id INT); |
| 327 INSERT INTO alt_ids SELECT id FROM ComponentDef WHERE project_id=c_pid AND p
ath COLLATE UTF8_GENERAL_CI LIKE c_path AND id!=c_id; |
| 328 |
| 329 -- Fix offending issues first, to avoid foreign key constraints. |
| 330 -- DELETE after UPDATE IGNORE to catch issues with two spellings. |
| 331 UPDATE IGNORE Issue2Component SET component_id=c_id WHERE component_id IN (S
ELECT id FROM alt_ids); |
| 332 DELETE FROM Issue2Component WHERE component_id IN (SELECT id FROM alt_ids); |
| 333 |
| 334 -- Then remove the alternate path definitions. |
| 335 DELETE FROM ComponentDef WHERE id IN (SELECT id FROM alt_ids); |
| 336 END LOOP; |
| 337 |
| 338 CLOSE curs; |
| 339 END; |
| 340 // |
| 341 |
| 342 |
| 343 CREATE PROCEDURE CleanupCase(IN in_pid SMALLINT UNSIGNED) |
| 344 BEGIN |
| 345 CALL CleanupStatusCase(in_pid); |
| 346 CALL CleanupLabelCase(in_pid); |
| 347 CALL CleanupPermissionCase(in_pid); |
| 348 CALL CleanupComponentCase(in_pid); |
| 349 END; |
| 350 // |
| 351 |
| 352 |
| 353 delimiter ; |
OLD | NEW |