Base.pm

#-----------------------------------------------------------------------------
# John Hogarty
#
# Description: Base class for database backed data related sub-classes
#
#-----------------------------------------------------------------------------
package OMX::Data::DB::Base;

use strict;
use warnings FATAL => qw(all);
use Sys::Hostname;
use DBI;
#-----------------------------------------------------------------------------
# construct and return object, set defaults
#-----------------------------------------------------------------------------
sub new
{
    my $type  = shift();
    my $class   = ref($type) || $type;
    my $self  = {};
    my %args  = @_;

    $self->{dbh}             = undef;
    $self->{is_connected}    = 0;
    $self->{auto_disconnect} = 1;

    bless  ($self, $class);

    if(defined($args{debug}))
    {
        print ref($self), ":new\n" if ($args{debug});
        $self->debug($args{debug});
    }

    if (defined($args{auto_disconnect}))
    {
        $self->auto_disconnect($args{auto_disconnect});
    }

    $self->setup_attrs;

    return $self;
}
#-----------------------------------------------------------------------------
# setup_attrs
#-----------------------------------------------------------------------------  
sub setup_attrs
{
    my $self = shift;
    print ref($self), ":setup_attrs: hostname:", hostname, "\n" if $self->debug;
  
    if (hostname =~ /(**OMMITED**)/) 
    {
        $ENV{ORACLE_HOME} = "/usr/lib/oracle/11.2/client64";
        $ENV{PATH}        = "$ENV{PATH}:/usr/lib/oracle/11.2/client64";
    }
    else 
    {
        $ENV{ORACLE_HOME} = "/oracle/product/8.1.6";
        $ENV{PATH}        = "$ENV{PATH}:/oracle/product/8.1.6/bin";
    }
}
#-----------------------------------------------------------------------------
# connect:  connect to the DB
#-----------------------------------------------------------------------------  
sub connect
{
    my $self = shift;
    print ref($self), ":connect\n" if $self->debug;

    my ($dbh, $pass);

    $pass = $self->password_hash;

    if($self->is_connected)
    {
        print ref($self), ":connect: already connected!\n" if $self->debug;
        return $self->dbh;
    }

    eval
    {
        $dbh = DBI->connect("dbi:Oracle:$pass->{dpdb}", $pass->{dpid}, $pass->{dppass}, { AutoCommit => 1, RaiseError => 1, PrintError => 0 });
        $self->dbh($dbh);
        $self->is_connected(1);
    };

    if($@)
    {
        die ref($self), ":connect: $@";
    } 
}
#-----------------------------------------------------------------------------
# password
#-----------------------------------------------------------------------------  
sub password_hash
{
    my $self = shift; 
    print ref($self), ":password_hash\n" if $self->debug;

    my ($info, %pw_hash);

    if(hostname !~ /**OMITTED**/)
    {
        print ref($self), ":set_dbi_:production, host: ", hostname,"\n" if $self->debug;
        $info = `/path/to/.file/pwd_tool DB ID PASS`;
    }
    else
    {
        print ref($self), ":set_dbi_:experimental, host: ", hostname,"\n" if $self->debug;
        $info = `/path/to/.file/pwd PDB_exp  ID_exp PASS_exp`;
    }
    
    my ($dsn, $user, $password) = split(/,/, $info);

    $pw_hash{dpdb}   = $dsn;
    $pw_hash{dpid}   = $user;
    $pw_hash{dppass} = $password;

    $self->{password_hash} = \%pw_hash;
    return $self->{password_hash};
}
#-----------------------------------------------------------------------------
# dbh - database handle for the connection
#-----------------------------------------------------------------------------  
sub dbh
{
    my $self = shift;
    if (@_) 
    {   
        $self->{dbh} = shift;
        print ref($self), ":dbh: ", $self->{dbh}, "\n" if $self->debug;
    }
    return $self->{dbh};
}
#-----------------------------------------------------------------------------
# query_by_sql
#-----------------------------------------------------------------------------
sub query_by_sql
{
    my $self = shift;
    my $sql = shift;

    print ref($self), ":query_by_sql\n" if $self->debug;

    my ($dbh, $sth, $records);

    if(!($self->is_connected))
    {
        $self->connect
    }

    eval
    {
        $dbh = $self->dbh;
        $sth = $dbh->prepare($sql);

        $sth->execute;
        $records = $sth->fetchall_arrayref({});
    };

    if($@)
    {
        die ref($self), ":query_by_sql: $@";
    }

    $self->disconnect if $self->auto_disconnect;
    
    return $records;
    
}
#-----------------------------------------------------------------------------
# disconnect:
#-----------------------------------------------------------------------------  
sub disconnect
{
    my $self = shift;
    print ref($self), ":disconnect\n" if $self->debug;

    if(!($self->is_connected))
    {
        print ref($self), ":disconnect: already disconnect!\n" if $self->debug;
        return;
    }

    my $dbh = $self->dbh;

    if(!defined($dbh))
    {
        print ref($self), ":database handle undefined\n" if $self->debug;
        return;
    }

    eval
    {
        $dbh->disconnect;
    };

    if($@)
    {
        print ref($self), ":disconnect: error disconnecting: $@";
    } 
    else
    {
        print ref($self), ":disconnect: success\n" if $self->debug;
    }

    $self->is_connected(0);
    $self->{dbh} = undef;   
}
#-----------------------------------------------------------------------------
# is_connected
#-----------------------------------------------------------------------------  
sub is_connected
{
    my $self = shift;
    
    if (@_) 
    {   
        my $is_connected    = shift;
        $self->{is_connected}   = $is_connected;

        print ref($self), ":is_connected: $is_connected\n" if $self->debug;
    }
    return $self->{is_connected};
}
#-----------------------------------------------------------------------------
# auto_disconnect:  
#-----------------------------------------------------------------------------  
sub auto_disconnect
{
    my $self = shift;
    if (@_) 
    {   
        my $value = shift;
        print ref($self), ":auto_disconnect: $value\n" if $self->debug;
        $self->{auto_disconnect} = $value;
    }
    return $self->{auto_disconnect};
}
#-----------------------------------------------------------------------------  
# DESTROY
#-----------------------------------------------------------------------------  
sub DESTROY
{
    my $self = shift;

    if(!($self->auto_disconnect))
    {
        if($self->is_connected)
        {
            die ref($self), ":destroy: please call disconnect before terminating\n" if $self->debug;
        }
    }

}
#-----------------------------------------------------------------------------
# debug
#-----------------------------------------------------------------------------  
sub debug
{
    my $self = shift;
    if (@_) 
    { 
        my $value = shift;
        $self->{debug} = $value;
        print ref($self), ":debug: ", $self->{debug}, "\n" if ($value > 0);
    }
    return $self->{debug};
}
#-----------------------------------------------------------------------------
# display for debugging
#-----------------------------------------------------------------------------
sub display
{
    my $self = shift;
    my @keys = keys %$self;
    for my $attribute ( @keys ) 
    {
        if( !( defined( $self->{$attribute})))
        { 
            printf "%-20s = %-30s\n", $attribute, "undef", "\n";
        }
        else
        {
            printf "%-20s = %-30s\n", $attribute, $self->{$attribute}, "\n";
        }
    }
    print "\n";
} 

return 1;