Open Source Monorail

appengine/monorail/tools/normalize-casing.sql

Index: appengine/monorail/tools/normalize-casing.sql
diff --git a/appengine/monorail/tools/normalize-casing.sql b/appengine/monorail/tools/normalize-casing.sql
new file mode 100644
index 0000000000000000000000000000000000000000..daf711fb335cd4e36555891ed54a88aa3d8a7be0
--- /dev/null
+++ b/appengine/monorail/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 crazy 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 crazy 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 ;