X-Git-Url: https://git.donarmstrong.com/?p=debbugs.git;a=blobdiff_plain;f=sql%2Fdebbugs_schema.sql;fp=sql%2Fdebbugs_schema.sql;h=3a75bac8b3b7af8767e3349461fd45c347535954;hp=3eaeee69fa70ab60568489733acf84e3fe96d086;hb=b1252b6797aa6a79d00a32165fb2fa8fb1bd9318;hpb=06424150844462de782ae112aa26c80dfa8d9401

diff --git a/sql/debbugs_schema.sql b/sql/debbugs_schema.sql
index 3eaeee6..3a75bac 100644
--- a/sql/debbugs_schema.sql
+++ b/sql/debbugs_schema.sql
@@ -406,6 +406,7 @@ CREATE TABLE bug_binpackage (
        bin_pkg INT NOT NULL REFERENCES bin_pkg ON UPDATE CASCADE ON DELETE CASCADE
 );
 CREATE UNIQUE INDEX bug_binpackage_id_pkg ON bug_binpackage(bug,bin_pkg);
+CREATE UNIQUE INDEX bug_binpackage_bin_pkg_bug_idx ON bug_binpackage(bin_pkg,bug);
 INSERT INTO table_comments VALUES ('bug_binpackage','Bug <-> binary package mapping');
 INSERT INTO column_comments VALUES ('bug_binpackage','bug','Bug id (matches bug)');
 INSERT INTO column_comments VALUES ('bug_binpackage','bin_pkg','Binary package id (matches bin_pkg)');
@@ -652,6 +653,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 affects
         ) AS affects,
+	(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,