add msid to bug_status VIEW

[debbugs.git] / sql / debbugs_schema.sql

diff --git a/sql/debbugs_schema.sql b/sql/debbugs_schema.sql
index f015d452d8727aac43e67b4bce6f5653bfa91993..5e375949ad64949c970b1702a193b7efd86ea6da 100644 (file)
--- a/sql/debbugs_schema.sql
+++ b/sql/debbugs_schema.sql
@@ -1,4 +1,4 @@
-
+-- -*- mode: sql; sql-product: postgres; -*-
 DROP TABLE bug_status_cache CASCADE;
 DROP VIEW bug_package CASCADE;
 DROP VIEW binary_versions CASCADE;
@@ -573,6 +573,8 @@ CREATE VIEW bug_status --(id,bug_num,tags,subject,
                     JOIN src_pkg sp ON bsp.src_pkg=sp.id
                      WHERE bsp.bug=b.id) AS package
         ) AS package,
+       (SELECT msgid FROM message m LEFT JOIN bug_message bm ON bm.message=m.id
+               WHERE bm.bug=b.id ORDER BY m.sent_date ASC limit 1) AS message_id,
        b.submitter_full AS originator,
        EXTRACT(EPOCH FROM b.log_modified) AS log_modified,
        EXTRACT(EPOCH FROM b.creation) AS date,