Project import generated by Copybara.
GitOrigin-RevId: d9e9e3fb4e31372ec1fb43b178994ca78fa8fe70
diff --git a/tools/backfill-issue-timestamps.sql b/tools/backfill-issue-timestamps.sql
new file mode 100644
index 0000000..0cc84a8
--- /dev/null
+++ b/tools/backfill-issue-timestamps.sql
@@ -0,0 +1,90 @@
+-- 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 BackfillIssueTimestampsChunk;
+DROP PROCEDURE IF EXISTS BackfillIssueTimestampsManyChunks;
+
+delimiter //
+
+CREATE PROCEDURE BackfillIssueTimestampsChunk(
+ IN in_pid SMALLINT UNSIGNED, IN in_chunk_size SMALLINT UNSIGNED)
+BEGIN
+
+ DECLARE done INT DEFAULT FALSE;
+
+ DECLARE c_issue_id INT;
+
+ DECLARE curs CURSOR FOR
+ SELECT id FROM Issue
+ WHERE project_id=in_pid
+ AND (owner_modified = 0 OR owner_modified IS NULL)
+ AND (status_modified = 0 OR status_modified IS NULL)
+ AND (component_modified = 0 OR component_modified IS NULL)
+ LIMIT in_chunk_size;
+
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
+ OPEN curs;
+
+ issue_loop: LOOP
+ FETCH curs INTO c_issue_id;
+ IF done THEN
+ LEAVE issue_loop;
+ END IF;
+
+ -- Indicate progress.
+ SELECT c_issue_id AS 'Processing:';
+
+ -- Set the fields to the largest timestamp of any relevant update.
+ UPDATE Issue
+ SET
+ owner_modified = (SELECT MAX(created)
+ FROM IssueUpdate
+ JOIN Comment ON IssueUpdate.comment_id = Comment.id
+ WHERE field = 'owner'
+ AND IssueUpdate.issue_id = c_issue_id),
+ status_modified = (SELECT MAX(created)
+ FROM IssueUpdate
+ JOIN Comment ON IssueUpdate.comment_id = Comment.id
+ WHERE field = 'status'
+ AND IssueUpdate.issue_id = c_issue_id),
+ component_modified = (SELECT MAX(created)
+ FROM IssueUpdate
+ JOIN Comment ON IssueUpdate.comment_id = Comment.id
+ WHERE field = 'component'
+ AND IssueUpdate.issue_id = c_issue_id)
+ WHERE id = c_issue_id;
+
+ -- If no update was found, use the issue opened timestamp.
+ UPDATE Issue SET owner_modified = opened
+ WHERE id = c_issue_id AND owner_modified IS NULL;
+
+ UPDATE Issue SET status_modified = opened
+ WHERE id = c_issue_id AND status_modified IS NULL;
+
+ UPDATE Issue SET component_modified = opened
+ WHERE id = c_issue_id AND component_modified IS NULL;
+
+ END LOOP;
+
+ CLOSE curs;
+END;
+
+CREATE PROCEDURE BackfillIssueTimestampsManyChunks(
+ IN in_pid SMALLINT UNSIGNED, IN in_num_chunks SMALLINT UNSIGNED)
+BEGIN
+ WHILE in_num_chunks > 0 DO
+ CALL BackfillIssueTimestampsChunk(in_pid, 1000);
+ SET in_num_chunks = in_num_chunks - 1;
+ END WHILE;
+END;
+
+
+//
+
+
+delimiter ;
+