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";
- $type = "Pg";
+ $type = 'Pg';
}
my $dsn = "DBI:$type:$db";
- my $hoststr = "";
+ my $hoststr = '';
# SQLHost should be unset for SQLite
if (exists $param{'SQLHost'} and $param{'SQLHost'}) {
- $dsn .= ":$param{SQLHost}";
+ # PostgreSQL requires ";" and keyword 'host'. See perldoc Pg -- troubled
+ if ($type eq 'Pg') {
+ $dsn .= ";host=$param{SQLHost}";
+ } else {
+ $dsn .= ":$param{SQLHost}";
+ }
$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");
return 0 unless ($dbh);
my $x = $param{SQLHost};
- my $hoststr = ($x) ? " to $x" : "";
+ my $hoststr = ($x) ? " to $x" : '';
&status("Closed DBI connection$hoststr.");
$dbh->disconnect();
return;
}
- if (!defined $data_href or ref($data_href) ne "HASH") {
+ if (!defined $data_href or ref($data_href) ne 'HASH') {
&WARN("sqlSet: data_href == NULL.");
return;
}
sub sqlUpdate {
my ($table, $data_href, $where_href) = @_;
- if (!defined $data_href or ref($data_href) ne "HASH") {
+ if (!defined $data_href or ref($data_href) ne 'HASH') {
&WARN("sqlSet: data_href == NULL.");
return 0;
}
my $where = &hashref2where($where_href) if ($where_href);
my $update = &hashref2update($data_href) if ($data_href);
- &sqlRaw("Update", "UPDATE $table SET $update WHERE $where");
+ &sqlRaw('Update', "UPDATE $table SET $update WHERE $where");
return 1;
}
# Usage: &sqlInsert($table, $data_href, $other);
sub sqlInsert {
my ($table, $data_href, $other) = @_;
- # note: if $other == 1, add "DELAYED" to function instead.
+ # note: if $other == 1, add 'DELAYED' to function instead.
+ # note: ^^^ doesnt actually do anything lol. Need code to s/1/DELAYED/ below -- troubled
- if (!defined $data_href or ref($data_href) ne "HASH") {
+ if (!defined $data_href or ref($data_href) ne 'HASH') {
&WARN("sqlInsert: data_href == NULL.");
return;
}
&sqlRaw("Insert($table)", sprintf(
"INSERT %s INTO %s (%s) VALUES (%s)",
- ($other || ""), $table, join(',',@k), join(',',@v)
+ ($other || ''), $table, join(',',@k), join(',',@v)
) );
return 1;
}
#####
-# Usage: &sqlReplace($table, $data_href);
+# Usage: &sqlReplace($table, $data_href, [$pkey]);
sub sqlReplace {
- my ($table, $data_href) = @_;
+ my ($table, $data_href, $pkey) = @_;
- if (!defined $data_href or ref($data_href) ne "HASH") {
+ if (!defined $data_href or ref($data_href) ne 'HASH') {
&WARN("sqlReplace: data_href == NULL.");
return;
}
return;
}
- &sqlRaw("Replace($table)", sprintf(
- "REPLACE INTO %s (%s) VALUES (%s)",
- $table, join(',',@k), join(',',@v)
- ) );
+
+ if ($param{'DBType'} =~ /^pgsql$/i) {
+ # OK, heres the scoop. There is currently no REPLACE INTO in Pgsql.
+ # However, the bot already seems to search for factoids before insert
+ # anyways. Perhaps we could change this to a generic INSERT INTO so
+ # we can skip the seperate sql? -- troubled to: TimRiker
+ # PGSql syntax: UPDATE table SET key = 'value', key2 = 'value2' WHERE key = 'value'
+
+# &sqlRaw("Replace($table)", sprintf(
+# "INSERT INTO %s (%s) VALUES (%s)",
+# $table, join(',',@k), join(',',@v)
+# ));
+ &WARN("DEBUG: ($pkey = ) " . sprintf(
+ "REPLACE INTO %s (%s) VALUES (%s)",
+ $table, join(',',@k), join(',',@v)
+ ));
+
+ } else {
+ &sqlRaw("Replace($table)", sprintf(
+ "REPLACE INTO %s (%s) VALUES (%s)",
+ $table, join(',',@k), join(',',@v)
+ ));
+ }
return 1;
}
sub sqlDelete {
my ($table, $where_href) = @_;
- if (!defined $where_href or ref($where_href) ne "HASH") {
+ if (!defined $where_href or ref($where_href) ne 'HASH') {
&WARN("sqlDelete: where_href == NULL.");
return;
}
my $where = &hashref2where($where_href);
- &sqlRaw("Delete", "DELETE FROM $table WHERE $where");
+ &sqlRaw('Delete', "DELETE FROM $table WHERE $where");
return 1;
}
return;
}
- if (ref($href) ne "HASH") {
+ if (ref($href) ne 'HASH') {
&WARN("hashref2where: href is not HASH ref (href => $href)");
return;
}
sub hashref2update {
my ($href) = @_;
- if (ref($href) ne "HASH") {
+ if (ref($href) ne 'HASH') {
&WARN("hashref2update: href is not HASH ref.");
return;
}
sub hashref2array {
my ($href) = @_;
- if (ref($href) ne "HASH") {
+ if (ref($href) ne 'HASH') {
&WARN("hashref2update: href is not HASH ref.");
return;
}
# Usage: &countKeys($table, [$col]);
sub countKeys {
my ($table, $col) = @_;
- $col ||= "*";
+ $col ||= '*';
return (&sqlRawReturn("SELECT count($col) FROM $table"))[0];
}
}
sub sqlCreateTable {
- my($table) = @_;
+ my($table, $dbtype) = @_;
my(@path) = ($bot_data_dir, ".","..","../..");
my $found = 0;
my $data;
+ $dbtype = lc $dbtype;
foreach (@path) {
- my $file = "$_/setup/$table.sql";
+ my $file = "$_/setup/$dbtype/$table.sql";
next unless ( -f $file );
open(IN, $file);
while (<IN>) {
chop;
+ next if $_ =~ /^--/;
$data .= $_;
}
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'") ) {
}
# create database not needed for SQLite
+
+ } elsif ($param{DBType} =~ /^pgsql$/i) {
+ # $sql_showDB = SQL to select the DB list
+ # $sql_showTBL = SQL to select all tables for the current connection
+
+ my $sql_showDB = "SELECT datname FROM pg_database";
+ my $sql_showTBL = "SELECT tablename FROM pg_tables \
+ WHERE schemaname = 'public'";
+
+ foreach ( &sqlRawReturn($sql_showDB) ) {
+ $database_exists++ if ($_ eq $param{'DBName'});
+ }
+
+ unless ($database_exists) {
+ &status("Creating PostgreSQL database $param{'DBName'}");
+ &status("(actually, not really, please read the INSTALL file)");
+ }
+
+ # retrieve a list of db's from the server. This code is from mysql above, please check -- troubled
+ my @tables = map {s/^\`//; s/\`$//; $_;} &sqlRawReturn($sql_showTBL);
+ if ($#tables == -1){
+ @tables = $dbh->tables;
+ }
+ &status("Tables: ".join(',',@tables));
+ @db{@tables} = (1) x @tables;
}
- foreach ( qw(botmail connections factoids rootwarn seen stats) ) {
+ foreach ( qw(botmail connections factoids rootwarn seen stats onjoin) ) {
if (exists $db{$_}) {
$cache{has_table}{$_} = 1;
next;
$cache{create_table}{$_} = 1;
- &sqlCreateTable($_);
+ &sqlCreateTable($_, $param{DBType});
}
}
1;
+
+# vim:ts=4:sw=4:expandtab:tw=80
projects / infobot.git / blobdiff