File Coverage

blib/lib/DBIx/Mysql/InformationSchema.pm

Criterion	Covered	Total	%
statement	7	9	77.7
branch			n/a
condition			n/a
subroutine	3	3	100.0
pod			n/a
total	10	12	83.3

package DBIx::Mysql::InformationSchema;

use strict;
use warnings;
use DBI;

our $VERSION = '0.04';




#
# Get a new object, not connected to start.
#

sub new {
    my $this = shift;
    my $class = ref($this) || $this;
    my $self = {};
    bless $self,$class;
    $self->{STATUS} = 0;
    $self->{DBH} = '';
    return $self;
}


# connectdb()
# Connect to the INFORMATION_SCHEMA database.
# You can simply put your own database handle into $obj->{DBH} if you prefer.


sub connectdb {
    my $thisthing = shift or return undef;

    my $user     = shift ;   # user,pass,host and port are all actually optional...
    my $password = shift ;   # the db might allow open access, right?

    my $host = shift;
    my $port = shift;


    my $mysql_information_view = 'information_schema';
    my $dsn = join ':','DBI','mysql', $mysql_information_view;

    if ($host) {
	$port = 3306 unless $port;
	$dsn = $dsn."\@".$host.':'.$port;
    }



    if ($thisthing->{DBH} = DBI->connect($dsn,  $user, $password )) {
	$thisthing->{STATUS} = "connected";
    } else {
	$thisthing->{DBIERROR} = $DBI::errstr;
	$thisthing->{STATUS} = 0;  # false but defined.
    }
}


# given a particular value - return the value for that db,table,column
# value is one of the

sub column_info_value {
    my $thisthing = shift or return undef;
    my $options = shift ;

    my $db     = $thisthing->{DBH}->quote($options->{DB});
    my $table  = $thisthing->{DBH}->quote($options->{TABLE});
    my $column = $thisthing->{DBH}->quote($options->{COLUMN});
    my $value =  $thisthing->{DBH}->quote($options->{VALUE});

    my $sql = "SELECT * from COLUMNS where TABLE_SCHEMA = $db AND TABLE_NAME  = $table AND COLUMN_NAME = $column";

    my $sth = $thisthing->{DBH}->prepare($sql);
    $sth->execute;

    # this should only return one row.
    my $href = $sth->fetchrow_hashref;
    return $href->{$value};
}


#
# Column_table_rows
#

sub column_table_rows {
    my $thisthing = shift or return undef;

    my $sql = "SELECT * from COLUMNS limit 1";
    my $sth = $thisthing->{DBH}->prepare($sql);
    $sth->execute;

    my $href = $sth->fetchrow_hashref;
    return keys %{$href};

}





#
# Return a list of all the  databases.
#
sub databases {

    my $thisthing = shift;
    my @rows;
    my $sth = $thisthing->{DBH}->prepare("SELECT DISTINCT TABLE_SCHEMA from TABLES");
    $sth->execute;
    while (my $row  =  $sth->fetchrow_array) {
	push @rows,$row;
    }
    return (@rows);
}




# Return a list of all the tables for a given database

sub tables {
    my $thisthing = shift or return undef;

    my $db = shift  or return undef;
    $db     = $thisthing->{DBH}->quote($db);

    my $sql = "SELECT DISTINCT TABLE_NAME from COLUMNS where TABLE_SCHEMA = $db";

    my $sth = $thisthing->{DBH}->prepare($sql);
    $sth->execute;
    my @tables;
    while (my $row  =  $sth->fetchrow_array) {
	push @tables,$row;
    }
    return (@tables);
}



# column_info returns a hash ref for the one row.
#  metadata for db,table and column.


sub column_info {

    my $thisthing = shift or return undef;

    my $db     = shift or return undef;
    my $table  = shift or return undef;
    my $column = shift or return undef;

    $db     = $thisthing->{DBH}->quote($db);
    $table  = $thisthing->{DBH}->quote($table);
    $column = $thisthing->{DBH}->quote($column);


    my $sql = "SELECT * from COLUMNS where TABLE_SCHEMA = $db AND TABLE_NAME  = $table AND COLUMN_NAME = $column";

    my $sth = $thisthing->{DBH}->prepare($sql);
    $sth->execute;

    return $sth->fetchrow_hashref;
}




#
# Given a db and a table,, return  alist of columns
#

sub columns {
    my $thisthing = shift or return undef;

    my $db     = shift or return undef;
    my $table  = shift or return undef;

    $db    = $thisthing->{DBH}->quote($db);
    $table = $thisthing->{DBH}->quote($table);

    my $sql = "SELECT DISTINCT COLUMN_NAME from COLUMNS where TABLE_SCHEMA = $db AND TABLE_NAME  = $table";

    my $sth = $thisthing->{DBH}->prepare($sql);
    $sth->execute;
    my @rows;
    while (my $row  =  $sth->fetchrow_array) {
	push @rows,$row;
    }
    return (@rows);
}




# information_schema_tables
#
#  Returns a list of tables in the information_schema view.


sub information_schema_tables {
    my $thisthing = shift or return undef;

    my $sql = "select TABLE_NAME from TABLES where TABLE_SCHEMA='information_schema' ";

    my $sth = $thisthing->{DBH}->prepare($sql);
    $sth->execute;

    my @tables;
    while (my $table = $sth->fetchrow_array) {
	push @tables,$table;
    }
    return (@tables);
}



sub table_constraints {
    my $thisthing = shift or return undef;
    my $sql = "SELECT * from TABLE_CONTRAINTS";
    my $sth = $thisthing->{DBH}->prepare($sql);
    $sth->execute;

    my @results;

    while (my $row = $sth->fetchrow_hashref) {
	push @results,$row;
    }
    return (@results);
}








sub disconnect {
    my $thisthing = shift or return undef;
    $thisthing->{DBH}->disconnect();

}


sub dbonline {
    my $thisthing = shift;
    if ($thisthing->{DBH}->{Driver}->{Name} =~ m/^mysql$/) {
	return 1;
    } else {
	return undef;
    }

}






1;
__END__