#!/usr/bin/perl -w -T
use strict;

use DBI;
use CGI ':standard';
use CGI::Carp 'fatalsToBrowser';
use Data::Denter;

#############################################################################
#                                                                           #
#                        OPTIONS                                            #
#                                                                           #
#############################################################################

my $server   = 'localhost';
my $db       = 'db';
my $username = 'db_user';

my $stored_queries_file= "/web/data/browse_db.sq";        # must be writable
my $pwd_file = '/web/data/db.pwd';                        # must be readable

my $dbh      = DBI->connect("dbi:mysql:$db:$server", $username, $password);

my $title_each         = 20; # title is display every $title_each row
                             # set to 0 to display title only once

#############################################################################
#                                                                           #
#                       END OF OPTIONS                                      #
#                                                                           #
#############################################################################

# array of hashes { title / query / nb_used }
my @queries= read_stored_queries( $stored_queries_file);

print header, start_html( -title => "DB Browser"),
      h1( "DB Browser"), hr();


my( $query, $title);

if( $query= param( 'new_query'))
  { if( param( 'save'))
      { die "Missing title for new query"  unless( $title= param( 'title')); 
        die "Title already used" if( grep {$_->{title} eq $title} @queries);
        push @queries, { title =>  $title, query => $query, nb_used => 1};
	$queries[0]->{nb_used}++;
      }
    else
      { $title= param( 'title') || 'untitled'; }
    die "wrong query /$query/" unless( $query=~ m{^\s*SELECT}i);
  }
elsif( my $query_nb= param( 'query_nb'))
  { die "unknown query" unless( $queries[$query_nb]);
    $queries[$query_nb]->{nb_used}++;
    $queries[0]->{nb_used}++;
    ($title, $query)= ($queries[$query_nb]->{title},$queries[$query_nb]->{query});
  }

print_results( $title, $query) if( $query);

print search_box( @queries);

print end_html();

write_stored_queries( $stored_queries_file, @queries);

exit;

sub read_stored_queries
  { my $file= shift;
    init_stored_queries( $file) unless( -f $file and ! -z $file);
    open( STORED, "<$file") or die "cannot open (read) $file : $!";
    local $/=undef;
    my @queries= Undent( <STORED>);
    close STORED;
    return @queries;
  }

sub write_stored_queries
  { my( $file, @queries)= @_;
    open( STORED, ">$file") or die "cannot open (write) $file : $!";
    print STORED Indent( @queries);
    close STORED;
  }  

sub init_stored_queries
  { my $file= shift;
    write_stored_queries( $file, { title => 'none', query => '', nb_used => '1'});
  }
     

sub print_results
  { my( $title, $query)= @_;
    print h2( $title);
    print p( b( "Query: "), escapeHTML( $query));

    open( PWD, "<$pwd_file") or die "cannot open pwd file $pwd_file: $!";
    my $password = <PWD>; chomp $password;
    close PWD;

    my $dbh      = DBI->connect("dbi:mysql:$db:$server", $username, $password);
    my $sth      = $dbh->prepare( $query);

    $sth->execute();
   
    print qq{<table border="1">\n};
    my $column_titles= $sth->{'NAME'};
    my $table_title= Tr( map { td( b(escapeHTML($_))) } @$column_titles);
    my $row_nb=0;
    while( my $record= $sth->fetchrow_arrayref)
      { unless( $row_nb % $title_each) { print $table_title; }
	$row_nb++;
        print Tr( map { td( escapeHTML($_) ) } @$record);
      }
    print qq{</table>};
    print p( b($row_nb), "results");
  }

sub search_box
  { my( %queries)= @_;
    my @values= 0..$#queries;
    my $i=0;   
    my %labels= map { $i++ => $_->{title} } @queries;
    return h1( "Search Box"),
	   start_form,
           h2( "Stored Query"),
           popup_menu(-name=>'query_nb', -values=> \@values, -labels => \%labels, -default => 0), br(),
           h2("New Query"),
	   table( Tr( td( b( "Title: ")), td( textfield('title'))),
	          Tr( td( {valign => "top"}, b(" Query: ")), 
                      td( textarea(-name=>'new_query', -rows=>5, -columns=>50))
	            ),
		),
           checkbox( -name=>'save'), br(),
           reset, defaults(), submit, end_form, hr();
  }

			
__END__

=head1 NAME

browse_db.cgi

=head1 DESCRIPTION

this cgi script displays the result of queries to a data base. 

queries can be saved and re-used later. 

queries are saved by Data::Denter and thus the query file can be manually 
edited if need be. 

=head1 SYNOPSYS

copy this script in a CGI directory and make it executable by the web server

You will need 2 files:

=over 4

=item *

a password file, which holds the password for the DB (must be readable by the server)

=item *

a file to store the queries (the server must have write access to it)

=back

=head1 PRE-REQUISITE

=over 4

=item *

A web server

=item *

A database

=item *

Perl modules: CGI, DBI, Data::Denter (available from CPAN), a DBD to the database

=back

=head1 AUTHOR

Michel Rodriguez <mirod@xmltwig.com>

Copyright (c)2000-2002 Michel Rodriguez. All rights reserved.

=head1 LICENSE

This code is free software; you can redistribute it and/or modify it under the same terms as Perl itself.