X-Git-Url: https://git.donarmstrong.com/?a=blobdiff_plain;f=src%2Fdbi.pl;h=c7400b00a7517324eb8ed3e3919032b728242af2;hb=41ce7955811bfc6e0ae64d4b45fd7cfd7520d55a;hp=5071404081f3196205d72c7fd966e71c7bde0e3c;hpb=f7cae48a17d6decd0a9bd997188271daa0a885b1;p=infobot.git

diff --git a/src/dbi.pl b/src/dbi.pl
index 5071404..c7400b0 100644
--- a/src/dbi.pl
+++ b/src/dbi.pl
@@ -14,12 +14,61 @@ use vars qw($dbh $shm $bot_data_dir);
 
 package main;
 
+eval {
+     # This wrapper's sole purpose in life is to keep the dbh connection open.
+     package Bloot::DBI;
+
+     # These are DBI methods which do not require an active DB
+     # connection. [Eg, don't check to see if the database is working
+     # by pinging it for these methods.]
+     my %no_ping;
+     @no_ping{qw(ping err err_str quote disconnect clone)} = (1) x 6;
+     sub new {
+	  my $class = shift;
+	  my $dbh = shift;
+	  return undef unless $dbh;
+	  $class = ref($class) if ref($class);
+	  my $self = {dbh=>$dbh};
+	  bless $self, $class;
+	  return $self;
+     }
+
+     our $AUTOLOAD;
+     sub AUTOLOAD {
+	  my $method = $AUTOLOAD;
+	  my $self = shift;
+	  die "Undefined subroutine $method called" unless defined $self;
+	  ($method) = $method =~ /([^\:]+)$/;
+	  unshift @_, $self->{dbh};
+	  return undef if not defined $self->{dbh};
+	  goto &{$self->{dbh}->can($method)} if exists $no_ping{$method} and $no_ping{$method};
+	  my $ping_count = 0;
+	  while (++$ping_count < 10){
+	       last if $self->{dbh}->ping;
+	       $self->{dbh}->disconnect;
+	       $self->{dbh} = $self->{dbh}->clone;
+	  }
+	  if ($ping_count >=10 and not $self->{dbh}->ping){
+	       &ERROR("Tried real hard but was unable to reconnect");
+	       return undef;
+	  }
+	  $_[0] = $self->{dbh};
+	  my $coderef = $self->{dbh}->can($method);
+	  goto &$coderef if defined $coderef;
+	  # Dumb DBI doesn't have a can method for some
+	  # functions. Like func.
+	  shift;
+	  return eval "\$self->{dbh}->$method(\@_)" or die $@;
+     }
+     1;
+};
+
 #####
 # &sqlOpenDB($dbname, $dbtype, $sqluser, $sqlpass, $nofail);
 sub sqlOpenDB {
     my ($db, $type, $user, $pass, $no_fail) = @_;
     # this is a mess. someone fix it, please.
-    if ($type =~ /^SQLite$/i) {
+    if ($type =~ /^SQLite(2)?$/i) {
 	$db = "dbname=$db.sqlite";
     } elsif ($type =~ /^pg/i) {
 	$db = "dbname=$db";
@@ -34,7 +83,7 @@ sub sqlOpenDB {
 	$hoststr = " to $param{'SQLHost'}";
     }
     # SQLite ignores $user and $pass
-    $dbh    = DBI->connect($dsn, $user, $pass);
+    $dbh    = Bloot::DBI->new(DBI->connect($dsn, $user, $pass));
 
     if ($dbh && !$dbh->err) {
 	&status("Opened $type connection$hoststr");
@@ -514,12 +563,12 @@ sub sumKey {
 # Usage: &randKey($table, $select);
 sub randKey {
     my ($table, $select) = @_;
-    my $rand	= int(rand(&countKeys($table) - 1));
-    my $query	= "SELECT $select FROM $table LIMIT $rand,1";
-    if ($param{DBType} =~ /^pg/i) {
-	$query =~ s/$rand,1/1,$rand/;
+    my $rand	= int(rand(&countKeys($table)));
+    my $query	= "SELECT $select FROM $table LIMIT 1 OFFSET $rand";
+    if ($param{DBType} =~ /^mysql$/i) {
+	# WARN: only newer MySQL supports "LIMIT limit OFFSET offset"
+	$query = "SELECT $select FROM $table LIMIT $rand,1";
     }
-
     my $sth	= $dbh->prepare($query);
     &SQLDebug($query);
     &WARN("randKey($query)") unless $sth->execute;
@@ -622,26 +671,24 @@ sub checkTables {
 	}
 
 	# retrieve a list of db's from the server.
-	foreach ($dbh->func('_ListTables')) {
-	    $db{$_} = 1;
-	}
+	my @tables = map {s/^\`//; s/\`$//; $_;} $dbh->func('_ListTables');
+	if ($#tables == -1){
+	    @tables = $dbh->tables;
+        }
+	&status("Tables: ".join(',',@tables));
+	@db{@tables} = (1) x @tables;
 
-    } elsif ($param{DBType} =~ /^SQLite$/i) {
+    } elsif ($param{DBType} =~ /^SQLite(2)?$/i) {
 
 	# retrieve a list of db's from the server.
 	foreach ( &sqlRawReturn("SELECT name FROM sqlite_master WHERE type='table'") ) {
 	    $db{$_} = 1;
 	}
 
-	# create database.
-	if (!scalar keys %db) {
-	    &status("Creating database $param{'DBName'}...");
-	    my $query = "CREATE DATABASE $param{'DBName'}";
-	    &sqlRaw("create(db $param{'DBName'})", $query);
-	}
+	# create database not needed for SQLite
     }
 
-    foreach ( qw(factoids factoidsmisc rootwarn seen stats botmail) ) {
+    foreach ( qw(botmail connections factoids rootwarn seen stats onjoin) ) {
 	if (exists $db{$_}) {
 	    $cache{has_table}{$_} = 1;
 	    next;