SELECT id, name, xp, lat, long FROM monks ORDER BY lat LIMIT 25 ##

##

Benchmark: timing 50 iterations of DBI and CGI, DBI and Print, DBI and TT2, XML and TT2/Simple, XML and TT2/XPath, XML and XSLT...
DBI and CGI:  5 wallclock secs ( 2.02 usr +  0.02 sys =  2.04 CPU) @ 24.51/s (n=50)
DBI and Print:  2 wallclock secs ( 0.23 usr +  0.02 sys =  0.25 CPU) @ 200.00/s (n=50)
            (warning: too few iterations for a reliable count)
DBI and TT2: 13 wallclock secs (10.91 usr +  0.07 sys = 10.98 CPU) @  4.55/s (n=50)
XML and TT2/Simple: 56 wallclock secs (43.87 usr +  0.23 sys = 44.10 CPU) @  1.13/s (n=50)
XML and TT2/XPath: 154 wallclock secs (144.17 usr +  0.73 sys = 144.90 CPU) @  0.35/s (n=50)
XML and XSLT: 27 wallclock secs (24.23 usr +  0.11 sys = 24.34 CPU) @  2.05/s (n=50)

XML and TT2/XPath  0.345/s  --      -70%  -83%  -92%  -99%  -100%
XML and TT2/Simple  1.13/s  229%     --   -45%  -75%  -95%  -99%
XML and XSLT        2.05/s  495%     81%   --   -55%  -92%  -99%
DBI and TT2         4.55/s 1220%    302%  122%  --    -81%  -98%
DBI and CGI         24.5/s 7003%   2062% 1093%  438%   --   -88%
DBI and Print        200/s 57860% 17540% 9636% 4292%  716%  --

##

##

               XSLT Transform
DBI -->  XML -----------------> XHTML

##

##

CGI-->userinfo -----|
                    |         Data Pruning XSLT
DBI--> DataXML -----+--> XML ------------------> XML2
                                                  |
                                  XHTML Transform |
                                                  |
                                                  V
                                                XHTML

##

##

#!/usr/bin/perl -w

use strict;

use DBI;
use CGI qw/-no_xhtml :standard/;
use XML::Generator::DBI;
use XML::Handler::YAWriter;
use XML::LibXML;
use XML::LibXSLT;
use Template;
use Data::Dumper;
use Benchmark qw( cmpthese );

$Template::Config::STASH = 'Template::Stash';

my $dbh = DBI->connect( "dbi:Pg:dbname=monksdb", "", "" ) 
  or die $DBI::errstr;

my $query = "SELECT id, name, xp, lat, long FROM monks ORDER BY lat LIMIT 25";
my $sth = $dbh->prepare( $query ) or die $DBI::errstr;


my $ya = XML::Handler::YAWriter->new( AsString => 1 );

my $generator = XML::Generator::DBI->new(
					 Handler => $ya,
					 dbh => $dbh,
					 RowElement => "monk"
					);

my $tt2 = Template->new;
my $tt2_nonXML = "template1.tt2";
my $tt2_XML = "template2.tt2";
my $tt2_XPath = "template3.tt2";

my $parser = new XML::LibXML;
my $xslt = new XML::LibXSLT;
my $sheet = "xslt_sheet.xsl";
my $slt = $parser->parse_file( $sheet );
my $stylesheet = $xslt->parse_stylesheet( $slt );


open FILE, ">/dev/null" or die "Cannot write out: $!";
my $target = \*FILE;

cmpthese( 50, 
	  {
	   "DBI and Print" => \&generate_from_straight_dbi_and_print,
	   "DBI and CGI" => \&generate_from_straight_dbi_and_cgi,
	   "DBI and TT2" => \&generate_from_straight_dbi_and_tt2,
	   "XML and TT2/Simple" => \&generate_from_xml_and_tt2_and_xmlsimple,
	   "XML and TT2/XPath" => \&generate_from_xml_and_tt2_and_xpath,
	   "XML and XSLT" => \&generate_from_xml_and_xslt
	   }
);

close FILE;

# Here, we use straight DBI calls and print calls to mark up 
# the table

sub generate_from_straight_dbi_and_print {
#  my $target = shift;

  $sth->execute() or die $DBI::errstr;

  print $target "Content-Type: text/html\n\n";
  print $target "\n";
  my $colorrow = 0;
  while ( my ( $id, $name, $xp, $lat, $long ) = $sth->fetchrow_array() ) {
    $colorrow = !$colorrow;
    my $color = ( $colorrow ) ? "#FFFFFF" : "#D0D0FF";
    print $target <
      
ROW
;
  }
  print $target "$id
      $name
      $xp
      $lat
      $long
    ";

}

# Here, we group the results as to make it easier for CGI 
# to print out (avoiding large HERE docs...)

sub generate_from_straight_dbi_and_cgi {
#  my $target = shift;

  $sth->execute() or die $DBI::errstr;

  my @data;
  while ( my @row = $sth->fetchrow_array() ) { push @data, \@row; }

  my $colorrow = 0;
  print $target 
    header('text/html'),
    start_html,
	table( 
	      map { $colorrow = !$colorrow; 
		    my $color = ( $colorrow ) ? "#FFFFFF" : "#D0D0FF";
		    Tr( 
		       td( {-bgcolor=>$color}, $_ )
		      )
		  } @data
	     ),
    end_html;
}

# Here, we pass the results to Template Toolkit for printing

sub generate_from_straight_dbi_and_tt2 {
#  my $target = shift;

  $sth->execute() or die $DBI::errstr;

  my @data;
  while ( my @row = $sth->fetchrow_array() ) { push @data, \@row; }

  print $target header;
  $tt2->process( $tt2_nonXML, { monks => \@data }, $target ) or
    die $tt2->error(),"\n";
}

# Use TT2 again, but now pass it XML and use the XPath module
# for parsing

sub generate_from_xml_and_tt2_and_xmlsimple {
#  my $target = shift;
  my $xml = $generator->execute( $query );

  print $target header;
  $tt2->process( $tt2_XML, { results => $xml }, $target ) or
    die $tt2->error(), "\n";
}

# Use TT2 again, but now pass it XML and use the XPath module
# for parsing

sub generate_from_xml_and_tt2_and_xpath {
#  my $target = shift;
  my $xml = $generator->execute( $query );


  print $target header;
  $tt2->process( $tt2_XPath, { results => $xml }, $target ) or
    die $tt2->error(), "\n";
}

# Use LibXML/LibXSLT to parse the results

sub generate_from_xml_and_xslt {
#  my $target = shift;
  my $xml = $generator->execute( $query );

  print $target header;

  my $source = $parser->parse_string( $xml );
  
  my $results = $stylesheet->transform( $source );
  print $target $stylesheet->output_string( $results );
}

##

##

[% colorrow = 0 %] 



[% FOREACH monkinfo = monks %]

[% colorrow = !colorrow %]
[% IF colorrow %]
    [% color = "#FFFFFF" %]
[% ELSE %]
    [% color = "#D0D0FF" %]
[% END %]
[% FOREACH item = monkinfo %]

[% END %]

[% END %]

[% item %]




##

##

[% USE xml = XML.Simple( results ) %] 
[% xml %]
[% colorrow = 0 %] 



[% orderedmonks = xml.select.monk.sort(keys.lat) %]
[% FOREACH monkinfo = orderedmonks %]

[% colorrow = !colorrow %]
[% IF colorrow %]
    [% color = "#FFFFFF" %]
[% ELSE %]
    [% color = "#D0D0FF" %]
[% END %]

[% END %]

[% xml.select.monk.$monkinfo.id %]


[% monkinfo %]


[% xml.select.monk.$monkinfo.xp %]


[% xml.select.monk.$monkinfo.lat %]


[% xml.select.monk.$monkinfo.long %]





##

##

[% USE xpath= XML.XPath( results ) %] 
[% colorrow = 0 %] 



[% FOREACH monk = xpath.findnodes('/database/select/monk') %]

[% colorrow = !colorrow %]
[% IF colorrow %]
    [% color = "#FFFFFF" %]
[% ELSE %]
    [% color = "#D0D0FF" %]
[% END %]

[% END %]

[% xpath.find('id',monk) %]


[% xpath.find('name',monk) %]


[% xpath.find('xp',monk) %]


[% xpath.find('lat',monk) %]


[% xpath.find('long',monk) %]





##