Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
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 lurking in the Monastery: (9)
As of 2014-07-30 00:01 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (229 votes), past polls