Project import generated by Copybara.
GitOrigin-RevId: d9e9e3fb4e31372ec1fb43b178994ca78fa8fe70
diff --git a/tools/normalize-casing.sql b/tools/normalize-casing.sql
new file mode 100644
index 0000000..139593e
--- /dev/null
+++ b/tools/normalize-casing.sql
@@ -0,0 +1,353 @@
+-- Copyright 2016 The Chromium Authors. All Rights Reserved.
+--
+-- Use of this source code is governed by a BSD-style
+-- license that can be found in the LICENSE file or at
+-- https://developers.google.com/open-source/licenses/bsd
+
+
+DROP PROCEDURE IF EXISTS InspectStatusCase;
+DROP PROCEDURE IF EXISTS CleanupStatusCase;
+DROP PROCEDURE IF EXISTS InspectLabelCase;
+DROP PROCEDURE IF EXISTS CleanupLabelCase;
+DROP PROCEDURE IF EXISTS InspectPermissionCase;
+DROP PROCEDURE IF EXISTS CleanupPermissionCase;
+DROP PROCEDURE IF EXISTS InspectComponentCase;
+DROP PROCEDURE IF EXISTS CleanupComponentCase;
+DROP PROCEDURE IF EXISTS CleanupCase;
+
+delimiter //
+
+CREATE PROCEDURE InspectStatusCase(IN in_pid SMALLINT UNSIGNED)
+BEGIN
+ DECLARE done INT DEFAULT FALSE;
+
+ DECLARE c_id INT;
+ DECLARE c_pid SMALLINT UNSIGNED;
+ DECLARE c_status VARCHAR(80) BINARY;
+
+ DECLARE curs CURSOR FOR SELECT id, project_id, status FROM StatusDef WHERE project_id=in_pid AND rank IS NOT NULL ORDER BY rank;
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
+
+ OPEN curs;
+
+ wks_loop: LOOP
+ FETCH curs INTO c_id, c_pid, c_status;
+ IF done THEN
+ LEAVE wks_loop;
+ END IF;
+
+ -- This is the canonical capitalization of the well-known status.
+ SELECT c_status AS 'Processing:';
+
+ -- Alternate forms are a) in the same project, and b) spelled the same,
+ -- but c) not the same exact status.
+ DROP TEMPORARY TABLE IF EXISTS alt_ids;
+ CREATE TEMPORARY TABLE alt_ids (id INT);
+ INSERT INTO alt_ids SELECT id FROM StatusDef WHERE project_id=c_pid AND status COLLATE UTF8_GENERAL_CI LIKE c_status AND id!=c_id;
+ SELECT status AS 'Alternate forms:' FROM StatusDef WHERE id IN (SELECT id FROM alt_ids);
+ SELECT id AS 'Offending issues:' FROM Issue WHERE status_id IN (SELECT id FROM alt_ids);
+ END LOOP;
+
+ CLOSE curs;
+END;
+//
+
+CREATE PROCEDURE CleanupStatusCase(IN in_pid SMALLINT UNSIGNED)
+BEGIN
+ DECLARE done INT DEFAULT FALSE;
+
+ DECLARE c_id INT;
+ DECLARE c_pid SMALLINT UNSIGNED;
+ DECLARE c_status VARCHAR(80) BINARY;
+
+ DECLARE curs CURSOR FOR SELECT id, project_id, status FROM StatusDef WHERE project_id=in_pid AND rank IS NOT NULL ORDER BY rank;
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
+
+ OPEN curs;
+
+ wks_loop: LOOP
+ FETCH curs INTO c_id, c_pid, c_status;
+ IF done THEN
+ LEAVE wks_loop;
+ END IF;
+
+ SELECT c_status AS 'Processing:';
+ DROP TEMPORARY TABLE IF EXISTS alt_ids;
+ CREATE TEMPORARY TABLE alt_ids (id INT);
+ INSERT INTO alt_ids SELECT id FROM StatusDef WHERE project_id=c_pid AND status COLLATE UTF8_GENERAL_CI LIKE c_status AND id!=c_id;
+
+ -- Fix offending issues first, to avoid foreign key constraints.
+ UPDATE Issue SET status_id=c_id WHERE status_id IN (SELECT id FROM alt_ids);
+
+ -- Then remove the alternate status definitions.
+ DELETE FROM StatusDef WHERE id IN (SELECT id FROM alt_ids);
+ END LOOP;
+
+ CLOSE curs;
+END;
+//
+
+CREATE PROCEDURE InspectLabelCase(IN in_pid SMALLINT UNSIGNED)
+BEGIN
+ DECLARE done INT DEFAULT FALSE;
+
+ DECLARE c_id INT;
+ DECLARE c_pid SMALLINT UNSIGNED;
+ DECLARE c_label VARCHAR(80) BINARY;
+
+ DECLARE curs CURSOR FOR SELECT id, project_id, label FROM LabelDef WHERE project_id=in_pid AND rank IS NOT NULL ORDER BY rank;
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
+
+ OPEN curs;
+
+ wkl_loop: LOOP
+ FETCH curs INTO c_id, c_pid, c_label;
+ IF done THEN
+ LEAVE wkl_loop;
+ END IF;
+
+ -- This is the canonical capitalization of the well-known label.
+ SELECT c_label AS 'Processing:';
+
+ -- Alternate forms are a) in the same project, and b) spelled the same,
+ -- but c) not the same exact label.
+ DROP TEMPORARY TABLE IF EXISTS alt_ids;
+ CREATE TEMPORARY TABLE alt_ids (id INT);
+ 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;
+ SELECT label AS 'Alternate forms:' FROM LabelDef WHERE id IN (SELECT id FROM alt_ids);
+ SELECT issue_id AS 'Offending issues:' FROM Issue2Label WHERE label_id IN (SELECT id FROM alt_ids);
+ END LOOP;
+
+ CLOSE curs;
+END;
+//
+
+CREATE PROCEDURE CleanupLabelCase(IN in_pid SMALLINT UNSIGNED)
+BEGIN
+ DECLARE done INT DEFAULT FALSE;
+
+ DECLARE c_id INT;
+ DECLARE c_pid SMALLINT UNSIGNED;
+ DECLARE c_label VARCHAR(80) BINARY;
+
+ DECLARE curs CURSOR FOR SELECT id, project_id, label FROM LabelDef WHERE project_id=in_pid AND rank IS NOT NULL ORDER BY rank;
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
+
+ OPEN curs;
+
+ wkl_loop: LOOP
+ FETCH curs INTO c_id, c_pid, c_label;
+ IF done THEN
+ LEAVE wkl_loop;
+ END IF;
+
+ SELECT c_label AS 'Processing:';
+ DROP TEMPORARY TABLE IF EXISTS alt_ids;
+ CREATE TEMPORARY TABLE alt_ids (id INT);
+ 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;
+
+ -- Fix offending issues first, to avoid foreign key constraints.
+ -- DELETE after UPDATE IGNORE to catch issues with two spellings.
+ UPDATE IGNORE Issue2Label SET label_id=c_id WHERE label_id IN (SELECT id FROM alt_ids);
+ DELETE FROM Issue2Label WHERE label_id IN (SELECT id FROM alt_ids);
+
+ -- Then remove the alternate label definitions.
+ DELETE FROM LabelDef WHERE id IN (SELECT id FROM alt_ids);
+ END LOOP;
+
+ CLOSE curs;
+END;
+//
+
+CREATE PROCEDURE InspectPermissionCase(IN in_pid SMALLINT UNSIGNED)
+BEGIN
+ DECLARE done INT DEFAULT FALSE;
+
+ DECLARE c_id INT;
+ DECLARE c_pid SMALLINT UNSIGNED;
+ DECLARE c_label VARCHAR(80) BINARY;
+
+ -- This complex query takes the Actions table (defined below) and combines it
+ -- with the set of all permissions granted in the project to construct a list
+ -- of all possible Restrict-Action-Permission labels. It then combines that
+ -- with LabelDef to see which ones are actually used (whether or not they are
+ -- also defined as well-known labels).
+ DECLARE curs CURSOR FOR SELECT LabelDef.id, LabelDef.project_id, RapDef.label FROM (
+ SELECT DISTINCT CONCAT_WS('-', 'Restrict', Actions.action, ExtraPerm.perm)
+ AS label FROM ExtraPerm, Actions where ExtraPerm.project_id=16) AS RapDef
+ LEFT JOIN LabelDef
+ ON BINARY RapDef.label = BINARY LabelDef.label
+ WHERE LabelDef.project_id=in_pid;
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
+
+ DROP TEMPORARY TABLE IF EXISTS Actions;
+ CREATE TEMPORARY TABLE Actions (action VARCHAR(80));
+ INSERT INTO Actions (action) VALUES ('View'), ('EditIssue'), ('AddIssueComment'), ('DeleteIssue'), ('ViewPrivateArtifact');
+
+ OPEN curs;
+
+ perm_loop: LOOP
+ FETCH curs INTO c_id, c_pid, c_label;
+ IF done THEN
+ LEAVE perm_loop;
+ END IF;
+
+ -- This is the canonical capitalization of the permission.
+ SELECT c_label AS 'Processing:';
+
+ -- Alternate forms are a) in the same project, and b) spelled the same,
+ -- but c) not the same exact label.
+ DROP TEMPORARY TABLE IF EXISTS alt_ids;
+ CREATE TEMPORARY TABLE alt_ids (id INT);
+ 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;
+ SELECT label AS 'Alternate forms:' FROM LabelDef WHERE id IN (SELECT id FROM alt_ids);
+ SELECT issue_id AS 'Offending issues:' FROM Issue2Label WHERE label_id IN (SELECT id FROM alt_ids);
+ END LOOP;
+
+ CLOSE curs;
+END;
+//
+
+CREATE PROCEDURE CleanupPermissionCase(IN in_pid SMALLINT UNSIGNED)
+BEGIN
+ DECLARE done INT DEFAULT FALSE;
+
+ DECLARE c_id INT;
+ DECLARE c_pid SMALLINT UNSIGNED;
+ DECLARE c_label VARCHAR(80) BINARY;
+
+ -- This complex query takes the Actions table (defined below) and combines
+ -- it with the set of all permissions granted in the project to construct a
+ -- list of all possible Restrict-Action-Permission labels. It then combines
+ -- that with LabelDef to see which ones are actually used (whether or not
+ -- they are also defined as well-known labels).
+ DECLARE curs CURSOR FOR SELECT LabelDef.id, LabelDef.project_id, RapDef.label FROM (
+ SELECT DISTINCT CONCAT_WS('-', 'Restrict', Actions.action, ExtraPerm.perm)
+ AS label FROM ExtraPerm, Actions where ExtraPerm.project_id=16) AS RapDef
+ LEFT JOIN LabelDef
+ ON BINARY RapDef.label = BINARY LabelDef.label
+ WHERE LabelDef.project_id=in_pid;
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
+
+ DROP TEMPORARY TABLE IF EXISTS Actions;
+ CREATE TEMPORARY TABLE Actions (action VARCHAR(80));
+ INSERT INTO Actions (action) VALUES ('View'), ('EditIssue'), ('AddIssueComment'), ('DeleteIssue'), ('ViewPrivateArtifact');
+
+ OPEN curs;
+
+ perm_loop: LOOP
+ FETCH curs INTO c_id, c_pid, c_label;
+ IF done THEN
+ LEAVE perm_loop;
+ END IF;
+
+ -- This is the canonical capitalization of the permission.
+ SELECT c_label AS 'Processing:';
+
+ -- Alternate forms are a) in the same project, and b) spelled the same,
+ -- but c) not the same exact label.
+ DROP TEMPORARY TABLE IF EXISTS alt_ids;
+ CREATE TEMPORARY TABLE alt_ids (id INT);
+ 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;
+
+ -- Fix offending issues first, to avoid foreign key constraings.
+ -- DELETE after UPDATE IGNORE to catch issues with two spellings.
+ UPDATE IGNORE Issue2Label SET label_id=c_id WHERE label_id IN (SELECT id FROM alt_ids);
+ DELETE FROM Issue2Label WHERE label_id IN (SELECT id FROM alt_ids);
+
+ -- Then remove the alternate label definitions.
+ DELETE FROM LabelDef WHERE id IN (SELECT id FROM alt_ids);
+ END LOOP;
+
+ CLOSE curs;
+
+ -- Remove ExtraPerm rows where the user isn't a member of the project.
+ DELETE FROM ExtraPerm WHERE project_id=in_pid AND user_id NOT IN (
+ SELECT user_id FROM User2Project WHERE project_id=in_pid);
+END;
+//
+
+CREATE PROCEDURE InspectComponentCase(IN in_pid SMALLINT UNSIGNED)
+BEGIN
+ DECLARE done INT DEFAULT FALSE;
+
+ DECLARE c_id INT;
+ DECLARE c_pid SMALLINT UNSIGNED;
+ DECLARE c_path VARCHAR(80) BINARY;
+
+ DECLARE curs CURSOR FOR SELECT id, project_id, path FROM ComponentDef WHERE project_id=in_pid AND docstring IS NOT NULL ORDER BY path;
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
+
+ OPEN curs;
+
+ wks_loop: LOOP
+ FETCH curs INTO c_id, c_pid, c_path;
+ IF done THEN
+ LEAVE wks_loop;
+ END IF;
+
+ -- This is the canonical capitalization of the component path.
+ SELECT c_path AS 'Processing:';
+
+ -- Alternate forms are a) in the same project, and b) spelled the same,
+ -- but c) not the same exact path.
+ DROP TEMPORARY TABLE IF EXISTS alt_ids;
+ CREATE TEMPORARY TABLE alt_ids (id INT);
+ INSERT INTO alt_ids SELECT id FROM ComponentDef WHERE project_id=c_pid AND path COLLATE UTF8_GENERAL_CI LIKE c_path AND id!=c_id;
+ SELECT path AS 'Alternate forms:' FROM ComponentDef WHERE id IN (SELECT id FROM alt_ids);
+ SELECT issue_id AS 'Offending issues:' FROM Issue2Component WHERE component_id IN (SELECT id FROM alt_ids);
+ END LOOP;
+
+ CLOSE curs;
+END;
+//
+
+CREATE PROCEDURE CleanupComponentCase(IN in_pid SMALLINT UNSIGNED)
+BEGIN
+ DECLARE done INT DEFAULT FALSE;
+
+ DECLARE c_id INT;
+ DECLARE c_pid SMALLINT UNSIGNED;
+ DECLARE c_path VARCHAR(80) BINARY;
+
+ DECLARE curs CURSOR FOR SELECT id, project_id, path FROM ComponentDef WHERE project_id=in_pid AND docstring IS NOT NULL ORDER BY path;
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
+
+ OPEN curs;
+
+ wks_loop: LOOP
+ FETCH curs INTO c_id, c_pid, c_path;
+ IF done THEN
+ LEAVE wks_loop;
+ END IF;
+
+ SELECT c_path AS 'Processing:';
+ DROP TEMPORARY TABLE IF EXISTS alt_ids;
+ CREATE TEMPORARY TABLE alt_ids (id INT);
+ INSERT INTO alt_ids SELECT id FROM ComponentDef WHERE project_id=c_pid AND path COLLATE UTF8_GENERAL_CI LIKE c_path AND id!=c_id;
+
+ -- Fix offending issues first, to avoid foreign key constraints.
+ -- DELETE after UPDATE IGNORE to catch issues with two spellings.
+ UPDATE IGNORE Issue2Component SET component_id=c_id WHERE component_id IN (SELECT id FROM alt_ids);
+ DELETE FROM Issue2Component WHERE component_id IN (SELECT id FROM alt_ids);
+
+ -- Then remove the alternate path definitions.
+ DELETE FROM ComponentDef WHERE id IN (SELECT id FROM alt_ids);
+ END LOOP;
+
+ CLOSE curs;
+END;
+//
+
+
+CREATE PROCEDURE CleanupCase(IN in_pid SMALLINT UNSIGNED)
+BEGIN
+ CALL CleanupStatusCase(in_pid);
+ CALL CleanupLabelCase(in_pid);
+ CALL CleanupPermissionCase(in_pid);
+ CALL CleanupComponentCase(in_pid);
+END;
+//
+
+
+delimiter ;