#!/bin/perl -w

use strict;

use DBI;
use XML::Twig;

my $file= shift;

my $dbh= connect_to_db();

my $twig= new XML::Twig( twig_roots => 
                           { rel_table => \&create_table },
                         twig_print_outside_roots => 1,
                       );

$twig->parsefile( $file);

$dbh->disconnect();
exit;



# connect to the data base
sub connect_to_db
  { my $driver = "mysql";
    my $dsn = "DBI:$driver:database=test;";
    my $dbh = DBI->connect($dsn, 'test', '', {AutoCommit=>1});
    my $drh = DBI->install_driver($driver);
    return( $dbh);
  }


sub create_table
  { my( $twig, $rel_table)= @_;
    my $query= $rel_table->att( 'query');

    # prepare the select
    my $select= $query;
    unless ($select=~ /;$/) { $select.= ";"; } # I always forget the ; 
    my $sth= $dbh->prepare( $select);

    print "<table border=\"1\">\n";

    $sth->execute();

    # print column titles
    print "<tr>";
    my $field= $sth->{NAME};
    for( my $i=0; $i<=$#{$field}; $i++)
      { print "<td><strong>" . ucfirst( $field->[$i]) . "</strong></td>"; }
    print "</tr>\n";

    my $row;
    while( $row= $sth->fetchrow_arrayref())
      { # print each row
        print "<tr>";
        for( my $i=0; $i<=$#{$row}; $i++)
          { my $value= $row->[$i];
            # format prices
            $value=~ s/^(\d*)(\d\d)$/\$$1.$2/ if( $field->[$i] eq 'price');
            print "<td>$value</td>";
          }
    print "</tr>\n";
      }

    print "</table>\n";
  }