perl で db tableのカラム定義をcasual & dynamic に確認

したい場合、 DBI で、 desc ～ や、 show create table ～ くらいしかないの?

#!/usr/local/bin/perl
use strict;
use utf8;
use DBI;
use Data::Dumper;

my $DB_CONF =
    {host=>'localhost',
     port=> 3306,
     db_name=> 'ないしょ',
     db_user=> 'ないしょ',
     db_pass=> '',
     db_opt=>
     {AutoCommit=> 0,
      PrintError=> 0,
      RaiseError=> 1,
      ShowErrorStatement=>  1,
      AutoInactiveDestroy=> 1,
      mysql_enable_utf8=> 1,
      mysql_auto_reconnect=> 0},
     client_encoding=> 'utf8'
    };



main(@ARGV);

sub main {

    my $dbh = connect_db($DB_CONF);

    my $tbl_name = 'h_juchuu';

    print_desc_tbl($dbh, $tbl_name);

    print_show_create_tbl($dbh, $tbl_name);

    $dbh->disconnect();
}


sub print_show_create_tbl {
    my ($dbh, $tbl_name) = @_;

    my $sql = "show create table $tbl_name";
    my $sth = $dbh->prepare($sql);
    unless( $sth->execute() ){
        die $sth->errstr;
    }

    while( my $row = $sth->fetchrow_hashref){
        print $row->{'Create Table'},"\n";
    }
}


sub print_desc_tbl {
    my ($dbh, $tbl_name) = @_;

    my $sql = "desc $tbl_name";
    my $sth = $dbh->prepare($sql);
    unless( $sth->execute() ){
        die $sth->errstr;
    }

    while( my $row = $sth->fetchrow_hashref){
        print join("\t",$row->{Field},$row->{Key},$row->{Type},),"\n";
    }
}


sub connect_db {
    my ($db_conf) = @_;

    my $dbh
        = DBI->connect(
        "DBI:mysql:database=$db_conf->{db_name};host=$db_conf->{host}",
        $db_conf->{db_user}, $db_conf->{db_pass}, $db_conf->{db_opt} );
    $dbh->do("SET NAMES $db_conf->{client_encoding}")
        or die "cannot set encoding";
    return $dbh;
}

1;
__END__