dbDescribe.pl

Simple formatted output of sql describe(describe tables) for all tables in a db.

Question from this little exercise: Anyway to "pass" formats? Are they like filehandle refs?

#!/usr/local/bin/perl

use Date::Manip;
use DBI;

main();

sub main
{
    my ($table,$field,$type,$n,$key,$def,$ex) = @$row;
    my $dbase="dbName";
    my $user="userName";
    my $pass="passWord";

    my $db =    DBI->connect ("dbi:mysql:$dbase",
                "$user","$pass");

    my $firstLine="header - 1st line";
    my $secondLine="header - 2nd line"; 
    my $date = UnixDate("today","%B %e, %Y");

#############<FORMAT DEFS>
format STDOUT_TOP =
@<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
 $firstLine
@<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
$secondLine
Last Modified: @<<<<<<<<<<<<<<<<<<<<<<<
                $date
PAGE:  @<<<<<<
        $%
.

format TABLEHEADER =
----------------------------------------------------------------------
+---------
Field          Type               Null     Key        Default       Ex
+tra
----------------------------------------------------------------------
+---------
.
format STDOUT =
@<<<<<<<<<<<   @<<<<<<<<<<<<<     @<<<<<<< @<<<<<<<<  @>>>>>>  @||||||
+||||||||
 $field,        $type              $n       $key       $def     $ex
----------------------------------------------------------------------
+---------
.
format TABLE =
@|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
+||||||||||
 $table
.
format NEWLINE =

.
format ENDTABLE =
----------------------------------------------------------------------
+---------
.

#####################</FORMAT DEFS>

    my $std = select (STDOUT);
    my $tableRefs = getData ($db, "show tables");
    foreach my $tab (@$tableRefs)
    {
        $~=TABLE;
        $table = "TABLE: $$tab[0]";
        write TABLE;
        $~=NEWLINE;
        write NEWLINE;
        $~=TABLEHEADER;
        write TABLEHEADER;
        $~=STDOUT;
        my $rowRefs = getData ($db, "describe $$tab[0]");
        foreach my $row (@$rowRefs)
        {
            ($field,$type,$n,$key,$def,$ex) = @$row;
            write STDOUT;
        }

        $~=ENDTABLE;
        write ENDTABLE;
        $~=NEWLINE;
        write NEWLINE;
    }

    $db->disconnect();
}
sub getData
{
    my ($db,$sql)=@_;
    my $query=$db->prepare("$sql");
    $query->execute();
    my $rows = $query->fetchall_arrayref();
    $rows;
}
[download]

Comment on dbDescribe.pl Download Code

Replies are listed 'Best First'.
Re: dbDescribe.pl by rob_au (Abbot) on Jun 01, 2002 at 11:40 UTC
Anyway to "pass" formats? Yes, there is a way to store formatting output, one which I only found out about recently while flicking through a copy of Perl in a Nutshell. The perlfunc:formline function, normally only used internally, allows for formats to be built and returned in the format output accumulator, `$^A`. Normally, the contents of this variable are outputted to the currently selected filehandle via the `write` command, but this variable, `$^A`, can be read and reset directly. For example: `my @list = (1, 2); # the output format can be stored in a variable prior to # output my $format = '@<<<< @<<<<'; # format the contents of @list as per the format structure # defined in the variable $format, the output to be # stored in the format output accumulator, $^A formline $format, @list; print $^A, "\n"; $^A = "";` [download] Thus in this manner, through use of the `formline` function, both the output format and the subsequent formatted output can be retrieved and stored. For further information see perlfunc:formline and perlform. I had been intending to write an obfuscation based upon this trick, but ... oh well ... :-)	[reply] [d/l] [select]
Re: dbDescribe.pl by Beatnik (Parson) on Jun 01, 2002 at 11:14 UTC
This is specific for certain RDBMSs, like mysql. Solid (the stuff I use at school) doesn't do `show tables`, neither does it do `describe table_name`. Greetz Beatnik ... Quidquid perl dictum sit, altum viditur.	[reply] [d/l] [select]


Clear questions and runnable code get the best and fastest answer
	PerlMonks