rendered paste bodypackage Scoop2::DBH;
use strict;
use Apache::DBI;
our $AUTOLOAD;
my $DEBUG = 0;
my $DB_EXPLAIN = 0;
my $CACHE_ONLY_DEBUG = 0;
$Scoop2::COUNT_DEBUG = 0;
$Scoop2::DB_QUERY_COUNT = 0;
$Scoop2::DB_CACHE_HITS = 0;
$Scoop2::DB_CACHE_MISSES = 0;
$Scoop2::DB_NOCACHE = 0;
sub new{
my $pkg = shift;
my %args = @_;
my $class = ref($pkg) || $pkg;
my $self = bless {}, $class;
warnings::warnif('debugging',"A new ".ref( $self )." Created and ready to go.");
$self->{scoop} = Scoop2->instance();
$self->{data_source_live}="DBI:".$self->{scoop}->config('db_type').
":dbname=".$self->{scoop}->config('db_name').
";host=".$self->{scoop}->config('db_host');
# $self->{data_source_archive}="DBI:".$self->{scoop}->config('archive_db_type').
# ":dbname=".$self->{scoop}->config('archive_db_name').
# ";host=".$self->{scoop}->config('archive_db_host');
$self->{db_down}=0;
$self->{dbh_live} = $self->SUPER::connect($self->{data_source_live}, $self->{scoop}->config('db_user'), $self->{scoop}->config('db_pass')) ||
warn "(Scoop2::Database_Connect) Can't connect to database! $@\n", $self->{db_down}++;
if($self->{scoop}->config('archive_db')){
my $self->{dbh_archive} = $self->SUPER::connect($self->{data_source_archive}, $self->{scoop}->config('archive_db_user'), $self->{scoop}->config('archive_db_pass')) ||
warn "(Scoop2::Database_Connect) Can't connect to database! $@\n", $self->{db_down}++;
}
if($self->{db_down}){
$self->{scoop}->apache->status(502);
$self->{scoop}->{HEADER_ONLY} = 1;
$self->{scoop}->apache->send_http_header;
undef $Scoop2::_instance;
exit;
}
return $self; # Return it!
}
sub db_select {
my $self = shift;
$self->{args} = shift;
$self->{archive}=0;
$Scoop2::DB_QUERY_COUNT++ if ($Scoop2::COUNT_DEBUG);
$self->{query} = "SELECT";
$self->{query} .= " DISTINCT" if ($self->{args}->{DISTINCT});
$self->{table} = $self->modify_for_shared_db($self->{args}{FROM});
if ($self->{args}->{ARCHIVE}) {
$self->{archive} = 1;
$self->{dbh}=$self->{dbh_archive};
} else {
$self->{archive} = 0;
$self->{dbh}=$self->{dbh_live};
}
return('0E0',undef) unless ($self->{dbh});
$self->{table} =~ s/^\s*(.*)\s*$/$1/;
$self->{query} .= " ". $self->{args}->{WHAT}." FROM ".$self->{table};
my $resource = 'sql_'.$self->{table};
my $element = 'SQL_DATA_'.$self->{table};
$self->{query}.=" WHERE ".$self->{args}{WHERE} if($self->{args}{WHERE});
$self->{query}.=" GROUP BY ".$self->{args}{GROUP_BY} if($self->{args}{GROUP_BY});
$self->{query}.=" ORDER BY ".$self->{args}{ORDER_BY} if($self->{args}{ORDER_BY});
if ($self->{args}{LIMIT}) {
$self->{query}.=" LIMIT";
$self->{query}.=" ".$self->{args}{OFFSET}."," if($self->{args}{OFFSET});
$self->{query}.=" ".$self->{args}{LIMIT};
}
warn "in db_select: Query is: ".$self->{query} if ($self->{args}{DEBUG} || $DEBUG);
# Don't cache if we're doing a join, for now.
$self->{args}{NOCACHE} = 1 if ($self->{table} =~ /,/);
$self->{args}{NOCACHE} = 1 if ($self->{table} =~ m/join/i);
$self->{args}{NOCACHE} = 1 unless $self->{scoop}->var('use_db_cache');
my ($sth, $rv) = undef;
my $cached = {};
$cached->{data} = [];
$cached->{name} = '';
if($DB_EXPLAIN){
my $explain;
my $sthexp = $self->SUPER::prepare("EXPLAIN ".$self->{query});
my $rvexp = $sthexp->execute();
my @values=$sthexp->fetchrow_array;
for('table','type','possible_keys','key','key_len','ref','rows','extra'){
$explain.="$_: ".shift(@values)."\n";
} warn "Query: ".$self->{query}."\n$explain"; $sthexp->finish();
}
if (!$self->{args}{NOCACHE} &&
($cached = $self->{scoop}->cache->fetch_data({resource => $resource,
element => $element, item => $self->{query}}))) {
$Scoop2::DB_CACHE_HITS++;
$rv = ($#{$cached->{data}}+1);
$rv = '0E0' if ($rv == 0);
} else {
($self->{args}{NOCACHE}) ? $Scoop2::DB_NOCACHE++ : $Scoop2::DB_CACHE_MISSES++;
warn "in db_select: not found in cache. Running query" if ($DEBUG);
warn "Cache miss! ($resource".'::'.$self->{query}.")" if ($CACHE_ONLY_DEBUG);
$sth = $self->SUPER::prepare($self->{query});
$rv = $sth->execute();
if ($self->{args}{NOCACHE}) {
unless($rv){
my ($package, $filename, $line) = caller;
warn "<<ERROR>> <<".$self->{scoop}->{REMOTE_IP}.">> in db_select: $DBI::errstr (Query is $self->{query}) : Error in $filename line $line"
}
return ($rv, $sth);
}
my $size = 0;
my $line;
while ($line = $sth->fetchrow_hashref()) {
foreach my $key (keys %{$line}) {
$size += length($line->{$key});
}
push @{$cached->{data}}, $line;
}
$cached->{name} = $sth->{NAME};
$sth->finish();
warn "Size is $size\n" if $DEBUG;
# Cache this stuff
warn "Caching data for ".$element.'::'.$self->{query} if ($CACHE_ONLY_DEBUG);
$self->{scoop}->cache->cache_data({resource=>$resource, element=>$element,
item => $self->{query}, data => $cached, size => $size});
}
my $scoop_sth = Scoop2::DBH::Statement->new($cached->{data}, $cached->{name});
$cached = {};
warn "Made Scoop sth. Returning." if $DEBUG;
my ($package, $filename, $line) = caller;
warn "<<ERROR>> <<".$self->{scoop}->{REMOTE_IP}.">> in db_select: $DBI::errstr (Query is $self->{query}) : Error in $filename line $line" unless ($rv);
return ($rv, $scoop_sth);
}
sub db_wipe_table_cache {
my $self = shift;
my $table = shift;
$table =~ s/^\s*(.*)\s*$/$1/;
my $resource = 'sql_'.$table;
my $element = 'SQL_DATA_'.$table;
# Clear the table's whole cache
$self->{scoop}->cache->clear({resource => $resource, element => $element});
return;
}
sub db_update {
my $self = shift;
$self->{args} = shift;
$Scoop2::DB_QUERY_COUNT++ if ($Scoop2::COUNT_DEBUG);
$self->{args}{NOCACHE} = 1 unless $self->{scoop}->var('use_db_cache');
$self->{args}{WHAT}=$self->modify_for_shared_db($self->{args}{WHAT});
$self->{query} = "UPDATE ".$self->{args}{WHAT}." SET ".$self->{args}{SET};
$self->{query} .= " WHERE ".$self->{args}{WHERE} if($self->{args}{WHERE});
$self->{query} .= " LIMIT ".$self->{args}{LIMIT} if($self->{args}{LIMIT});
warn "in db_update: Query is: ".$self->{query} if($self->{args}{DEBUG} || $DEBUG);
if ($self->{args}->{ARCHIVE}) {
$self->{archive} = 1;
$self->{dbh}=$self->{dbh_archive};
} else {
$self->{archive} = 0;
$self->{dbh}=$self->{dbh_live};
}
return('0E0',undef) unless ($self->{dbh});
my $sth = $self->SUPER::prepare($self->{query});
my $rv = $sth->execute();
my ($package, $filename, $line) = caller;
warn "<<ERROR>> in db_update: $DBI::errstr (Query is $self->{query}) : Error in $filename line $line" unless $rv;
$self->db_wipe_table_cache($self->{args}{WHAT}) if ($rv && !$self->{args}{NOCACHE});
return ($rv, $sth);
}
sub db_insert {
my $self = shift;
$self->{args} = shift;
$Scoop2::DB_QUERY_COUNT++ if ($Scoop2::COUNT_DEBUG);
$self->{args}{NOCACHE} = 1 unless $self->{scoop}->var('use_db_cache');
$self->{args}{INTO}=$self->modify_for_shared_db($self->{args}{INTO});
$self->{query}= "INSERT INTO ".$self->{args}{INTO};
$self->{query} .= " (".$self->{args}{COLS}.")" if($self->{args}{COLS});
$self->{query} .= " VALUES (".$self->{args}{VALUES}.")";
warn "in db_insert: Query is: ".$self->{query} if($self->{args}{DEBUG} || $DEBUG);
if ($self->{args}->{ARCHIVE}) {
$self->{archive} = 1;
$self->{dbh}=$self->{dbh_archive};
} else {
$self->{archive} = 0;
$self->{dbh}=$self->{dbh_live};
}
return('0E0',undef) unless ($self->{dbh});
my $sth = $self->SUPER::prepare($self->{query});
my $rv = $sth->execute();
my ($package, $filename, $line) = caller;
warn "<<ERROR>> <<".$self->{REMOTE_IP}.">> in db_insert: $DBI::errstr (Query is $self->{query}) : Error in $filename line $line" unless $rv;
$self->db_wipe_table_cache($self->{args}{INTO}) if ($rv && !$self->{args}{NOCACHE});
return ($rv, $sth);
}
sub db_delete {
my $self = shift;
$self->{query} = shift;
$Scoop2::DB_QUERY_COUNT++ if ($Scoop2::COUNT_DEBUG);
$self->{args}{NOCACHE} = 1 unless $self->{scoop}->var('use_db_cache');
$self->{args}{FROM}=$self->modify_for_shared_db($self->{args}{FROM});
$self->{query} = "DELETE FROM ".$self->{args}{FROM};
$self->{query} .= " WHERE ".$self->{args}{WHERE} if($self->{args}{WHERE});
$self->{query} .= " LIMIT ".$self->{args}{LIMIT} if($self->{args}{LIMIT});
warn "in db_delete: Query is: ".$self->{query} if($self->{args}{DEBUG} || $DEBUG);
if ($self->{args}->{ARCHIVE}) {
$self->{archive} = 1;
$self->{dbh}=$self->{dbh_archive};
} else {
$self->{archive} = 0;
$self->{dbh}=$self->{dbh_live};
}
return('0E0',undef) unless ($self->{dbh});
my $sth = $self->SUPER::prepare($self->{query});
my $rv = $sth->execute();
my ($package, $filename, $line) = caller;
warn "<<ERROR>> in db_delete: $DBI::errstr (Query is $self->{query}) : Error in $filename line $line" unless $rv;
$self->db_wipe_table_cache($self->{args}{FROM}) if ($rv && !$self->{args}{NOCACHE});
return ($rv, $sth);
}
sub db_lock_tables {
my $self = shift;
$self->{args} = shift;
$Scoop2::DB_QUERY_COUNT++ if ($Scoop2::COUNT_DEBUG);
$self->{query} = "LOCK TABLES ";
while (my($k, $v) = each %{ $self->{args} }) {
$self->{query} .= "$k $v" unless $k eq "DEBUG";
}
warn "in db_lock_tables: Query is: ".$self->{query} if($self->{args}{DEBUG} || $DEBUG);
if ($self->{args}->{ARCHIVE}) {
$self->{archive} = 1;
$self->{dbh}=$self->{dbh_archive};
} else {
$self->{archive} = 0;
$self->{dbh}=$self->{dbh_live};
}
return('0E0',undef) unless ($self->{dbh});
my $sth = $self->SUPER::prepare($self->{query});
my $rv = $sth->execute();
warn "<<ERROR>> in db_lock_tables: $DBI::errstr (Query is: ".$self->{query} unless $rv;
return ($rv, $sth);
}
sub modify_for_shared_db{
my $self=shift;
my $queryfrag=shift;
warn("Determining Shared Database Usage Status for '$queryfrag'") if $DEBUG;
return $queryfrag unless $self->{scoop}->var('use_shared_db');
warn("We're using a Shared Database. Proceeding to Replacement") if $DEBUG;
my $shared_db_name=$self->{scoop}->var('shared_db_name');
for(split(/\s+/,$self->{scoop}->var('shared_db_tables'))){
warn("Checking for Instances of '$_' for Replacement") if $DEBUG;
$queryfrag=~s/(\b$_\b)/$shared_db_name\.$1/ig;
}
warn("Returning Updated Query Fragment for Shared DB: '$queryfrag'") if $DEBUG;
return $queryfrag;
}
sub db_unlock_tables {
my $self = shift;
$self->{args} = shift;
$Scoop2::DB_QUERY_COUNT++ if ($Scoop2::COUNT_DEBUG);
$self->{query} = "UNLOCK TABLES";
warn "in db_unlock_tables: Query is: ".$self->{query} if($self->{args}{DEBUG} || $DEBUG);
if ($self->{args}->{ARCHIVE}) {
$self->{archive} = 1;
$self->{dbh}=$self->{dbh_archive};
} else {
$self->{archive} = 0;
$self->{dbh}=$self->{dbh_live};
}
return('0E0',undef) unless ($self->{dbh});
my $sth = $self->SUPER::prepare($self->{query});
my $rv = $sth->execute();
warn "<<ERROR>> in db_unlock_tables: $DBI::errstr\n Query is: ". $self->{query} unless $rv;
return ($rv, $sth);
}
# Add functions for transactions. Can only be used with versions of mysql
# that use transactions, of course.
sub db_start_transaction {
my $self = shift;
$self->{args} = shift;
unless ($self->{scoop}->config('mysql_version') =~ /^4/) {
return "Transactions not supported in this version of MySQL.\n";
}
$Scoop2::DB_QUERY_COUNT++ if ($Scoop2::COUNT_DEBUG);
$self->{query} = "START TRANSACTION";
warn "in db_start_transaction: Query is: ".$self->{query} if($self->{args}{DEBUG} || $DEBUG);
if ($self->{args}->{ARCHIVE}) {
$self->{archive} = 1;
$self->{dbh}=$self->{dbh_archive};
} else {
$self->{archive} = 0;
$self->{dbh}=$self->{dbh_live};
}
return('0E0',undef) unless ($self->{dbh});
my $sth = $self->SUPER::prepare($self->{query});
my $rv = $sth->execute();
warn "<<ERROR>> in db_start_transaction: $DBI::errstr\n Query is: ". $self->{query} unless $rv;
return ($rv, $sth);
}
sub db_commit {
my $self = shift;
$self->{args} = shift;
unless ($self->{scoop}->config('mysql_version') =~ /^4/) {
warn("Transactions not supported in this version of MySQL");
return "Transactions not supported in this version of MySQL.\n";
}
$Scoop2::DB_QUERY_COUNT++ if ($Scoop2::COUNT_DEBUG);
$self->{query} = "COMMIT";
warn "in db_commit: Query is: ".$self->{query} if($self->{args}{DEBUG} || $DEBUG);
if ($self->{args}->{ARCHIVE}) {
$self->{archive} = 1;
$self->{dbh}=$self->{dbh_archive};
} else {
$self->{archive} = 0;
$self->{dbh}=$self->{dbh_live};
}
return('0E0',undef) unless ($self->{dbh});
my $sth = $self->SUPER::prepare($self->{query});
my $rv = $sth->execute();
warn "<<ERROR>> in db_commit: $DBI::errstr\n Query is: ". $self->{query} unless $rv;
return ($rv, $sth);
}
sub db_rollback {
my $self = shift;
$self->{args} = shift;
unless ($self->{scoop}->config('mysql_version') =~ /^4/) {
warn("Transactions not supported in this version of MySQL");
return "Transactions not supported in this version of MySQL.\n";
}
$Scoop2::DB_QUERY_COUNT++ if ($Scoop2::COUNT_DEBUG);
$self->{query} = "ROLLBACK";
warn "in db_rollback: Query is: ".$self->{query} if($self->{args}{DEBUG} || $DEBUG);
if ($self->{args}->{ARCHIVE}) {
$self->{archive} = 1;
$self->{dbh}=$self->{dbh_archive};
} else {
$self->{archive} = 0;
$self->{dbh}=$self->{dbh_live};
}
return('0E0',undef) unless ($self->{dbh});
my $sth = $self->SUPER::prepare($self->{query});
my $rv = $sth->execute();
warn "<<ERROR>> in db_rollback: $DBI::errstr\n Query is: ". $self->{query} unless $rv;
return ($rv, $sth);
}
sub db_date_sub {
my $self = shift;
my $left = shift;
my $right = shift;
if (lc($self->{scoop}->config('DBType')) eq "mysql") {
return "DATE_SUB(($left), INTERVAL $right)";
} else {
return "$left + INTERVAL '$right'";
}
}
sub db_date_add {
my $self = shift;
my $left = shift;
my $right = shift;
if (lc($self->{scoop}->config('DBType')) eq "mysql") {
return "DATE_ADD(($left), INTERVAL $right)";
} else {
return "$left + INTERVAL '$right'";
}
}
sub AUTOLOAD {
my $self = shift;
my ($meth) = $AUTOLOAD =~ /(\w+)$/;
return $self->{dbh_live}->$meth(@_);
}
sub DESTROY {
my $self = shift;
warnings::warnif('debugging',"<<Another ".ref( $self )." bites the dust>>");
# remove these references so that everything is cleaned up properly
undef $self->{scoop};
}
1;