perfSONAR_PS::DB::SQL::Base - A class that provides data access for SQL databases
Index
Code Index:
NAME
perfSONAR_PS::DB::SQL::Base - A class that provides data access for SQL databases
DESCRIPTION
This module provides access to the relevant DBI wrapped methods given the relevant
contact points for the database. Any service specific SQL DB handler should ingerit from this class.
SYNOPSIS
package perfSONAR_PS::DB::SQL::PingER;
# extend from this class
use perfSONAR_PS::DB::SQL::Base;
use base perfSONAR_PS::DB::SQL::Base;
1;
METHODS
new
constructor accepts hashref as parameter
returns object
init
accepts hashref as single parameter and initializes object
returns
0 = if everything is okay
-1 = somethign went wrong
connect
opens a connection to the database or reuses the cached one
Returns
0 = if everything is okay
-1 = somethign went wrong
closeDB
closes the connection to the database
Returns
0 = if everything is okay
-1 = somethign went wrong
alive
checks if db handle is alive
Returns
0 = if everything is okay
-1 = somethign went wrong
getFromTable
accepts single hash param with keys:
query => Rose::DB::Object::QueryBuilder type of query,
table => table name
validate => table validation HASHref constant
index => name of the indexing key for result
returns result as hashref keyd by $index
or undef and sets ERRORMSG
updateTable
accepts singel hashref param with keys:
set => hashref { for example 'ip_name' => $ip_name, 'ip_number' => $ip_number },
table => table_name
where => where clause ( formatted as Rose::DB::Object query )
returns
0 if OK
-1 = somethign went wrong
insertTable
accepts single hashref with keys:
insert => { 'ip_name' => $ip_name, 'ip_number' => $ip_number },
table => table_name
returns
0 or last inserted id number
-1 = somethign went wrong
createTable
create table from template - second parameter is the name of the template table ( must exist )
returns
0 - evrything is OK
-1 - something is wrong
validateQuery
vlaidated supplied query parameters against defined fields for particular table
optional arg $required is a hash of the required table fields
returns
0 = if everything is okay
-1 = somethign went wrong
fixTimestamp
fix timestamp
returns
fixed timestamp or undef if its not parseable
booleanToInt
converts any boolean arg to 0/1 int
tableExists
check presence of the table
Returns
0 = no table
1 = table exists
package perfSONAR_PS::DB::SQL::Base;
use warnings;
use strict;
use DBI;
use Data::Dumper;
use version; our $VERSION = 0.09;
use English '-no_match_vars';
use Scalar::Util qw(blessed);
use Log::Log4perl qw( get_logger );
use perfSONAR_PS::DB::SQL::QueryBuilder qw(build_select build_where_clause);
use constant CLASSPATH => 'perfSONAR_PS::DB::SQL::Base';
use constant PARAMS => qw(driver database handle ERRORMSG LOGGER host port username password attributes);
use fields (PARAMS);
no strict 'refs';
foreach my $key (PARAMS) {
*{__PACKAGE__ . "::$key"} = sub { my $obj = shift; my $a = shift;
if($a) {
($key eq 'ERRORMSG')?$obj->{"$key"} .= " \n $a":$obj->{"$key"} = $a;
}
return $obj->{"$key"}};
}
use strict;
sub new {
my $that = shift;
my $param = shift;
my $logger = get_logger( __PACKAGE__ );
my $class = ref($that) || $that;
my $self = fields::new($class );
## injecting accessor/mutator for each field
$self->LOGGER($logger);
$self->attributes( {RaiseError => 1} );
if($param) {
if( $self->init($param) == -1 ) {
$self->LOGGER()->error($self->ERRORMSG);
return;
}
}
return $self;
}
sub DESTROY {
my $self = shift;
$self->closeDB;
$self->SUPER::DESTROY if $self->can("SUPER::DESTROY");
return;
}
sub init {
my ($self, $param) = @_;
if($param) {
if(ref($param) ne 'HASH') {
$self->ERRORMSG("ONLY hash ref accepted as param " . $param );
return -1;
}
$self->{LOGGER}->debug("Parsing parameters: " . (join " : ", keys %{$param}));
foreach my $key (PARAMS) {
$self->{$key} = $param->{$key} if exists $param->{$key}; ###
}
return 0;
} else {
$self->ERRORMSG(" Undefined parameter ");
return -1;
}
}
sub openDB {
my ($self, $param) = @_;
if($param && $self->init($param) == -1 ) {
return -1;
}
eval {
$self->handle(DBI->connect_cached("DBI:" . $self->driver . ":dbname=" . $self->database .
($self->host?";host=" . $self->host:'') .
($self->port?";port=" . $self->port:'') , $self->username, $self->password, $self->attributes))
or die $DBI::errstr;
};
if ($EVAL_ERROR) {
$self->ERRORMSG("Connect DB error \"" . $EVAL_ERROR . "\"." );
return -1;
}
return 0;
}
sub closeDB {
my $self = shift;
# close database connection
# destroy objects classes in memory
eval {
$self->handle->disconnect if $self->handle && ref($self->handle) =~ /DBI/;
};
if ($EVAL_ERROR) {
$self->ERRORMSG("Close DB error \"" . $EVAL_ERROR . "\"." );
return -1;
}
return 0;
}
sub alive {
my $self = shift;
# close database connection
# destroy objects classes in memory
my $ping = -1;
eval {
$ping = 0 if($self->handle && ref($self->handle) =~ /DBI/ && $self->handle->ping);
};
if ($EVAL_ERROR) {
$self->ERRORMSG("Ping DB error \"" . $EVAL_ERROR . "\"." );
return -1;
}
return $ping;
}
sub getFromTable {
my ($self, $param) = @_;
unless( $param && ref($param) eq 'HASH' && $param->{table} &&
( ($param->{query} && ref($param->{query}) eq 'ARRAY') ||
($param->{validate} && ref($param->{validate}) eq 'HASH')
)
) {
$self->ERRORMSG("getFromTable requires single HASH ref parameter with required query key as ARRAY ref ");
return -1;
}
my $results = -1;
my @array_of_names = ();
#
# if validation hash is not provided then we should use query itself for the list of columns
#
if(defined $param->{validate} && ref($param->{validate}) eq 'HASH') {
@array_of_names = keys %{$param->{validate}};
} else {
my $param_sz = scalar @{$param->{query}};
for(my $i=0;$i<$param_sz;$i+=2) {
push @array_of_names ,$param->{query}->[$i] if $param->{query}->[$i];
}
}
unless( @array_of_names ) {
$self->ERRORMSG(" getFromTable failed, someting misssing for " . Dumper $param);
return -1;
}
my $stringified_names = join ", ", @array_of_names;
eval {
my $sql_query = build_select({ dbh => $self->handle,
select => $stringified_names,
tables => [$param->{table}],
query => $param->{query},
query_is_sql => 1,
columns => { $param->{table} => \@array_of_names},
limit => ($param->{limit}?$param->{limit}:'1000000'), ## i am pretty sure that 1 mil of records is more than enough
});
$self->LOGGER->debug(" SQL:: $sql_query ");
$self->openDB if !($self->alive == 0);
my $sth = $self->handle->prepare($sql_query);
$results = $self->handle->selectall_hashref($sth, $param->{index});
$self->LOGGER->debug(" RESULTS dump:: " . Dumper $results);
die $DBI::errstr if $DBI::err;
};
if ($EVAL_ERROR) {
$self->ERRORMSG("getFromTable failed with error \"" . $EVAL_ERROR . "\"." );
}
return $results;
}
sub updateTable{
my ($self, $param) = @_;
unless($param && ref($param) eq 'HASH' && $param->{table} &&
($param->{set} && ref($param->{set}) eq 'HASH') &&
($param->{validate} && ref($param->{validate}) eq 'HASH')
&& $param->{where} && ref($param->{where}) eq 'ARRAY') {
$self->ERRORMSG("updateTable requires single HASH ref parameter with required set,validate and where keys ");
return -1;
}
my $stringified_names = '';
my @array_of_names = keys %{$param->{validate}};
foreach my $key (@array_of_names ) {
$stringified_names .= " $key='" . $param->{set}->{$key} ."'," if defined $param->{set}->{$key};
}
my $query_sql = build_where_clause({
dbh => $self->handle,
tables => [$param->{table}],
query => $param->{where},
query_is_sql => 1,
table_aliases => 0,
columns => { $param->{table} => \@array_of_names},
});
chop $stringified_names if $stringified_names;
$self->LOGGER->debug(" SQL:: $query_sql ");
eval {
$self->openDB if !($self->alive == 0);
my $sth = $self->handle->prepare("update " .$param->{table} . " set $stringified_names where $query_sql ");
$sth->execute() or die $DBI::errstr;
};
if ($EVAL_ERROR) {
$self->ERRORMSG("updateTable failed with error \"" . $EVAL_ERROR . "\"." );
return -1;
}
return 0;
}
sub insertTable{
my ($self, $param ) = @_;
unless( $param && ref($param) eq 'HASH' && $param->{table} && $param->{insert} && ref($param->{insert}) eq 'HASH') {
$self->ERRORMSG("uinsertTable requires single HASH ref parameter with required insert key as HASH ref ");
return -1;
}
my $stringified_names = '';
my $stringified_values = '';
my $rv = 0;
foreach my $key (keys %{$param->{insert}}) {
if(defined $param->{insert}->{$key}) {
$stringified_names .= "$key,";
$stringified_values .= "'". $param->{insert}->{$key}."',";
}
}
if($stringified_names && $stringified_values) {
chop $stringified_names;
chop $stringified_values;
eval {
$self->openDB if !($self->alive == 0);
my $query_sql = "insert into ". $param->{table} ." ($stringified_names) values ($stringified_values)";
$self->LOGGER->debug(" SQL:: $query_sql ");
$self->handle->do($query_sql);
# return last serial number of the newly inserted row or integer primary key
$rv = ($self->driver =~ /mysql/i)?$self->handle->{q{mysql_insertid}}:
($self->driver =~ /sqlite/i)?$self->handle->func("last_insert_rowid"):
$self->handle->last_insert_id(undef, undef, $param->{table}, undef);
};
if ($EVAL_ERROR) {
$self->ERRORMSG("insertTable failed with error \"" . $EVAL_ERROR . "\"." );
return -1;
}
} else {
$self->ERRORMSG("insertTable failed because of empty list of parameters" );
return -1;
}
return $rv;
}
sub createTable {
my ($self, $table, $template) = @_;
eval {
$self->LOGGER->debug( "creating new data table $table from template table=$template");
# create the database table if necessary
$self->openDB if !($self->alive == 0);
$self->handle->do( "CREATE TABLE $table AS SELECT * FROM $template" );
};
if ( $EVAL_ERROR ) {
$self->ERRORMSG(" Failed to create $table ");
return -1;
}
return 0;
}
sub validateQuery {
my ($self, $params, $valid_const, $required) = @_;
foreach my $key (keys %{$params}) {
unless(defined $valid_const->{$key}) {
$self->ERRORMSG(" Field $key is unknown for DB schema");
return -1;
}
delete $required->{$key} if $required && $required->{$key} && defined $params->{$key};
}
return -1 if $required && scalar %{$required};
return 0;
}
sub fixTimestamp {
my ($self, $timestamp) = @_;
# remap the timestamp if it's not valid
if ($timestamp) {
if( $timestamp =~ m/^\d+$/) {
return $timestamp;
} elsif ($timestamp =~ s/^(\d+)\.\d+$/$1/ ) {
return $timestamp;
}
}
$self->ERRORMSG( "Timestamp $timestamp format unparseable or its undef");
return;
}
sub booleanToInt {
my ($self, $param) = @_;
if ( defined $param) {
if ( $param eq 'false' || !$param) {
return 0;
} else {
return 1;
}
}
return;
}
sub tableExists {
my ($self, $table) = @_;
my $result = undef;
eval {
$self->LOGGER->debug( "...testing presence of the table $table");
# create the database table if necessary
$self->openDB if !($self->alive == 0);
# next line will fail if table does not exist
($result) = $self->handle->selectrow_array("select * from $table where 1=0 " );
};
$EVAL_ERROR?return 0:return 1;
}
1;