Attach submitter identity to change messages about successful submits

This helps to annotate who submitted a change into the project, by
making the system generated "Change has been successfully merged"
message to be authored by the user who pressed the submit button,
rather than by the system.

This SQL updates records created before 4503f01f15a793773744a40
("Record who submitted a change in the change message") was live.

Signed-off-by: Shawn O. Pearce <sop@google.com>

1 files changed, 19 insertions, 0 deletions

diff --git a/src/main/webapp/WEB-INF/sql/upgrade008_009.sql b/src/main/webapp/WEB-INF/sql/upgrade008_009.sql
index 562cf0a5f6..135fea6d38 100644
--- a/src/main/webapp/WEB-INF/sql/upgrade008_009.sql
+++ b/src/main/webapp/WEB-INF/sql/upgrade008_009.sql
@@ -6,4 +6,23 @@ UPDATE projects SET submit_type = 'M'; -- MERGE_IF_NECESSARY
 ALTER TABLE projects ALTER COLUMN submit_type SET DEFAULT ' ';
 ALTER TABLE projects ALTER COLUMN submit_type SET NOT NULL;
 
+UPDATE change_messages
+SET author_id = (SELECT a.account_id FROM change_approvals a
+                 WHERE a.change_id = change_messages.change_id
+                   AND a.category_id = 'SUBM'
+                   AND a.value = 1)
+WHERE author_id IS NULL
+ AND message LIKE '% has been successfully merged %'
+AND 'M' = (SELECT c.status FROM changes c
+           WHERE c.change_id = change_messages.change_id)
+AND 1 = (SELECT COUNT(a.account_id) FROM change_approvals a
+         WHERE a.change_id = change_messages.change_id
+           AND a.category_id = 'SUBM'
+           AND a.value = 1)
+AND written_on >= (SELECT MAX(a.granted)
+                   FROM change_approvals a
+                   WHERE a.change_id = change_messages.change_id
+                     AND a.category_id = 'SUBM'
+                     AND a.value = 1);
+
 UPDATE schema_version SET version_nbr = 9;