From: Andrew Dunstan <andrew@dunslane.net>
Date: Sun, 8 Aug 2010 23:29:06 +0000 (+0000)
Subject: use materialised view in soap query
X-Git-Url: https://git.exim.org/buildfarm-server.git/commitdiff_plain/629605c84e0c904ff84b787f40afd920abb428f1

use materialised view in soap query
---

diff --git a/cgi-bin/show_status_soap.pl b/cgi-bin/show_status_soap.pl
index e51db59..5d12800 100755
--- a/cgi-bin/show_status_soap.pl
+++ b/cgi-bin/show_status_soap.pl
@@ -38,32 +38,8 @@ sub get_status
     my $statement =<<EOS;
 
 
-    select (now() at time zone 'GMT')::timestamp(0) - snapshot as when_ago,
-      	sysname, snapshot, b.status, stage, branch, build_flags,
-      	operating_system, coalesce(b.os_version,s.os_version) as os_version,
-      	compiler, 
-        coalesce(b.compiler_version, s.compiler_version) as compiler_version, 
-	architecture 
-    from buildsystems s, 
-        (
-	select distinct on (bs.sysname, bs.branch, bs.report_time) 
-               sysname, snapshot, status, stage, branch, build_flags,
-               report_time ,compiler_version, os_version
-        from build_status bs
-             left join 
-             personality p
-             on (p.name = bs.sysname and p.effective_date <= bs.report_time)
-        order by bs.sysname, bs.branch, bs.report_time, 
-                 p.effective_date is null, p.effective_date desc
-        ) as b
-	natural join 
-       	(select sysname, branch, max(snapshot) as snapshot
-        from build_status
-        group by sysname, branch
-    	having max(snapshot) > now() - '30 days'::interval
-       	) m
-    where name = sysname
-        and s.status = 'approved'
+    select (now() at time zone 'GMT')::timestamp(0) - snapshot as when_ago, dsh.*
+    from dashboard_mat dsh
     order by branch = 'HEAD' desc, 
         branch desc, 
         snapshot desc