X-Git-Url: https://git.donarmstrong.com/?a=blobdiff_plain;f=sql%2Fdebbugs_schema.sql;h=5e375949ad64949c970b1702a193b7efd86ea6da;hb=f80106b5aa44579d927790ba9f1a60bde259dd81;hp=c091a6e5396d971aa76d54dc0fe0038b4b1eda7f;hpb=adf6bdf8be8e0b3da649c5ee113ce085e7b46560;p=debbugs.git

diff --git a/sql/debbugs_schema.sql b/sql/debbugs_schema.sql
index c091a6e..5e37594 100644
--- 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;
@@ -443,7 +443,10 @@ CREATE TABLE bug_status_cache (
        modified TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
        asof TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL
 );
-CREATE UNIQUE INDEX bug_status_cache_bug_suite_arch_idx ON bug_status_cache(bug,suite,arch);
+CREATE UNIQUE INDEX bug_status_cache_bug_suite_arch_idx ON
+       bug_status_cache(bug,suite,arch);
+CREATE UNIQUE INDEX bug_status_cache_bug_col_suite_col_arch_idx ON
+       bug_status_cache(bug,COALESCE(suite,0),COALESCE(arch,0));
 CREATE INDEX bug_status_cache_idx_bug ON bug_status_cache(bug);
 CREATE INDEX bug_status_cache_idx_status ON bug_status_cache(status);
 CREATE INDEX bug_status_cache_idx_arch ON bug_status_cache(arch);
@@ -570,10 +573,12 @@ 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,
-	b.log_modified AS log_modified,
-	b.creation AS date,
-	b.last_modified AS last_modified,
+	EXTRACT(EPOCH FROM b.log_modified) AS log_modified,
+	EXTRACT(EPOCH FROM b.creation) AS date,
+	EXTRACT(EPOCH FROM b.last_modified) AS last_modified,
 	b.done_full AS done,
 	string_agg(bb.blocks::text,' ' ORDER BY bb.blocks) AS blocks,
 	string_agg(bbb.bug::text,' ' ORDER BY bbb.bug) AS blockedby,