Data Base Viewer

[mirod]

Yes, I know about DBIx-HTMLView, but I just needed a quick script that would allow me to view the result of queries on a local db. Storing the queries for later re-use is a nice plus too. Queries are displayed sorted on the number of times they have been used.

So here it is, let me know if it can be done in half as many lines, and if it has glaring flaws.

#!/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 wr
+itable
my $pwd_file = '/web/data/db.pwd';                        # must be re
+adable

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

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( 'tit
+le')); 
        die "Title already used" if( grep {$_->{title} eq $title} @que
+ries);
        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_us
+ed => '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, $p
+assword);
    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_title
+s);
    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, -colu
+mns=>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 manua
+lly 
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 t
+he database

=back

=head1 AUTHOR

Michel Rodriguez <[e-mail]>

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

=head1 LICENSE

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

[merlyn]

You might take a look at Template::Plugin::DBI.

-- Randal L. Schwartz, Perl hacker
Be sure to read my standard disclaimer if this is a reply.