cgi-bin/show_members.pl

   1 #!/usr/bin/perl
   2
   3 use strict;
   4 use CGI;
   5 use DBI;
   6 use Template;
   7
   8
   9
  10 use vars qw($dbhost $dbname $dbuser $dbpass $dbport $sort_by);
  11
  12
  13 require "$ENV{BFConfDir}/BuildFarmWeb.pl";
  14 #require "BuildFarmWeb.pl";
  15
  16 my $query = new CGI;
  17 my %sort_ok = ('name' => 'lower(name)' ,
  18                'owner' => 'lower(owner_email)',
  19                'os' => 'lower(operating_system), os_version',
  20                'compiler' => 'lower(compiler), compiler_version' ,
  21                'arch' => 'lower(architecture)' );
  22 $sort_by = $query->param('sort_by');$sort_by =~ s/[^a-zA-Z0-9_ -]//g;
  23 $sort_by = $sort_ok{$sort_by} || $sort_ok{name};
  24
  25 my $dsn="dbi:Pg:dbname=$dbname";
  26 $dsn .= ";host=$dbhost" if $dbhost;
  27 $dsn .= ";port=$dbport" if $dbport;
  28
  29 my $db = DBI->connect($dsn,$dbuser,$dbpass);
  30
  31 # there is possibly some redundancy in this query, but it makes
  32 # a lot of the processing simpler.
  33
  34 my $statement = <<EOS;
  35
  36   select name, operating_system, os_version, compiler, compiler_version, owner_email,
  37     architecture as arch, ARRAY(
  38                                 select branch || ':' ||
  39                                        extract(days from now() - latest_snapshot)
  40                                 from build_status_latest l
  41                                 where l.sysname = s.name
  42                                 order by branch <> 'HEAD', branch desc
  43                                 ) as branches
  44   from buildsystems s
  45   where status = 'approved'
  46   order by $sort_by
  47
  48 EOS
  49 ;
  50
  51 my $statrows=[];
  52 my $sth=$db->prepare($statement);
  53 $sth->execute;
  54 while (my $row = $sth->fetchrow_hashref)
  55 {
  56     $row->{branches} =~ s/^\{(.*)\}$/$1/;
  57     $row->{owner_email} =~ s/\@/ [ a t ] /;
  58     push(@$statrows,$row);
  59 }
  60 $sth->finish;
  61
  62
  63 $db->disconnect;
  64
  65 # use Data::Dumper; print "Content-Type: text/plain\n\n",Dumper($statrows),"VERSION: ",$DBD::Pg::VERSION,"\n"; exit;
  66
  67
  68 my $template = new Template({});
  69
  70 print "Content-Type: text/html\n\n";
  71
  72 $template->process(\*DATA,{statrows=>$statrows});
  73
  74 exit;
  75
  76
  77 __DATA__
  78 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
  79         "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
  80 <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
  81 <head>
  82         <meta http-equiv="content-type" content="text/html; charset=utf-8" />
  83         <title>PostgreSQL BuildFarm Members</title>
  84         <link rel="icon" type="image/png" href="/elephant-icon.png" />
  85         <link rel="stylesheet" rev="stylesheet" href="/inc/pgbf.css" charset="utf-8" />
  86         <style type="text/css"><!--
  87         li#members a { color:rgb(17,45,137); background: url(/inc/b/r.png) no-repeat 100% -20px; }
  88         li#members { background: url(/inc/b/l.png) no-repeat 0% -20px; }
  89         --></style>
  90    </style>
  91 </head>
  92 <body class="members">
  93 <div id="wrapper">
  94 <div id="banner">
  95 <a href="/index.html"><img src="/inc/pgbuildfarm-banner.png" alt="PostgreSQL BuildFarm" width="800" height="73" /></a>
  96 <div id="nav">
  97 <ul>
  98     <li id="home"><a href="/index.html" title="PostgreSQL BuildFarm Home">Home</a></li>
  99     <li id="status"><a href="/cgi-bin/show_status.pl" title="Current results">Status</a></li>
 100     <li id="members"><a href="/cgi-bin/show_members.pl" title="Platforms tested">Members</a></li>
 101     <li id="register"><a href="/cgi-bin/register-form.pl" title="Join PostgreSQL BuildFarm">Register</a></li>
 102     <li id="pgfoundry"><a href="http://pgfoundry.org/projects/pgbuildfarm/">PGFoundry</a></li>
 103 </ul>
 104 </div><!-- nav -->
 105 </div><!-- banner -->
 106 <div id="main">
 107 <h1>PostgreSQL BuildFarm Members</h1>
 108     <p>Click branch links to see build history. Click the heading links to resort the list. Select members by checkbox and hit the button at the bottom to create a status custom filter.</p>
 109     <form name="filter" method="GET" action="/cgi-bin/show_status.pl">
 110     <table cellspacing="0">
 111     <tr>
 112     <td>&nbsp;</td>
 113     <th><a href="/cgi-bin/show_members.pl?sort_by=name">Name</a><br /><a href="/cgi-bin/show_members.pl?sort_by=owner">Owner</a></th>
 114     <th><a href="/cgi-bin/show_members.pl?sort_by=os">OS / Version</a></th>
 115     <th><a href="/cgi-bin/show_members.pl?sort_by=compiler">Compiler / Version</a></th>
 116     <th><a href="/cgi-bin/show_members.pl?sort_by=arch">Arch</a></th>
 117     <th>Branches reported on<br />(most recent report)</th>
 118     </tr>
 119 [% alt = true %]
 120 [% FOREACH row IN statrows ;
 121     have_recent = 0;
 122     FOREACH branch_days IN row.branches.split(',') ;
 123        branch_fields = branch_days.split(':');
 124        branch_day = branch_fields.1;
 125        IF branch_day < 365 ; have_recent = 1; END;
 126     END;
 127  IF have_recent ;
 128 %]    <tr [%- IF alt %]class="alt"[% END -%]>
 129     [% alt = ! alt %]
 130     <td><input type="checkbox" name="member" value="[% row.name %]" /></td>
 131     <td>[% row.name %]<br />[% row.owner_email %]</td>
 132     <td>[% row.operating_system %]<br />[% row.os_version %]</td>
 133     <td>[% row.compiler %]<br />[% row.compiler_version %]</td>
 134     <td>[% row.arch %]</td>
 135     <td class="branch">[% IF ! row.branches ; '&nbsp;' ; END -%]
 136     <ul>
 137     [%-
 138        FOREACH branch_days IN row.branches.split(',') ;
 139        branch_fields = branch_days.split(':');
 140        branch = branch_fields.0;
 141        branch_day = branch_fields.1;
 142        IF branch_day < 365 ;
 143     %]<li><a
 144     href="show_history.pl?nm=[% row.name %]&amp;br=[% branch %]"
 145     title="History"
 146     >[% branch %]</a>&nbsp;([% branch_day %]&nbsp;days&nbsp;ago)</li>[% END; END %]</ul></td>
 147     </tr>
 148 [% END; END %]
 149     </table>
 150     <input type="submit" value="Make Filter" />
 151     </form>
 152     </div><!-- main -->
 153 <hr />
 154 <p style="text-align: center;">
 155 Hosting for the PostgreSQL Buildfarm is generously
 156 provided by:
 157 <a href="http://www.commandprompt.com">CommandPrompt,
 158 The PostgreSQL Company</a>
 159 </p>
 160     </div><!-- wrapper -->
 161   </body>
 162 </html>
 163
 164
 165
 166
 167
 168
 169
 170