Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister
 
PerlMonks  

dbDescribe.pl

by abaxaba (Hermit)
on Jun 01, 2002 at 10:59 UTC ( #170903=snippet: print w/ replies, xml ) Need Help??

Description: 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;
}

Comment on dbDescribe.pl
Download Code
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.
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 = "";

    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 ... :-)

     

Back to Snippets Section

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: snippet [id://170903]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others avoiding work at the Monastery: (12)
As of 2014-12-25 05:12 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (159 votes), past polls