X-Git-Url: https://git.donarmstrong.com/?a=blobdiff_plain;f=src%2Fdbi.pl;h=76954e70366df98f4393f550be4c988f7fa5cd4a;hb=9b2ed52d2607731198d280a735dd8ba287533422;hp=f9f731930b5b39d9db54e997fe69506be77895f4;hpb=f3a7f67d07f3a8511e269ce55da6dc7d0c2087e6;p=infobot.git diff --git a/src/dbi.pl b/src/dbi.pl index f9f7319..76954e7 100644 --- a/src/dbi.pl +++ b/src/dbi.pl @@ -19,7 +19,7 @@ package main; 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"; @@ -30,7 +30,12 @@ sub sqlOpenDB { 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 @@ -88,12 +93,11 @@ sub sqlSelectMany { return; } - $query .= " WHERE" if (($where_href) || ($other)); if ($where_href) { my $where = &hashref2where($where_href); - $query .= " $where" if ($where); + $query .= " WHERE $where" if ($where); } - $query .= " $other" if $other; + $query .= " $other" if ($other); if (!($sth = $dbh->prepare($query))) { &ERROR("sqlSelectMany: prepare: $DBI::errstr"); @@ -101,10 +105,8 @@ sub sqlSelectMany { } &SQLDebug($query); - if (!$sth->execute) { - &ERROR("sqlSelectMany: execute: '$query'"); - return; - } + + return if (!$sth->execute); return $sth; } @@ -240,7 +242,8 @@ sub sqlSet { my $result = &sqlSelect($table, $k, $where_href); # &DEBUG("result is not defined :(") if (!defined $result); - if (1 or defined $result) { + # this was hardwired to use sqlUpdate. sqlite does not do inserts on sqlUpdate. + if (defined $result) { &sqlUpdate($table, $data_href, $where_href); } else { # hack. @@ -264,7 +267,7 @@ sub sqlUpdate { if (!defined $data_href or ref($data_href) ne "HASH") { &WARN("sqlSet: data_href == NULL."); - return; + return 0; } my $where = &hashref2where($where_href) if ($where_href); @@ -280,6 +283,7 @@ sub sqlUpdate { sub sqlInsert { my ($table, $data_href, $other) = @_; # 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") { &WARN("sqlInsert: data_href == NULL."); @@ -295,18 +299,16 @@ sub sqlInsert { return; } - &sqlRaw("Insert($table)", sprintf( + return &sqlRaw("Insert($table)", sprintf( "INSERT %s INTO %s (%s) VALUES (%s)", ($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") { &WARN("sqlReplace: data_href == NULL."); @@ -322,10 +324,29 @@ sub sqlReplace { 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; } @@ -516,12 +537,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; @@ -560,7 +581,7 @@ sub searchTable { $str =~ s/\*/%/g; # end of string fix. - my $query = "SELECT $select FROM $table WHERE $key LIKE ". + my $query = "SELECT $select FROM $table WHERE $key LIKE ". &sqlQuote($str); my $sth = $dbh->prepare($query); @@ -580,18 +601,20 @@ sub searchTable { } 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 () { chop; + next if $_ =~ /^--/; $data .= $_; } @@ -624,30 +647,61 @@ 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 + + } 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(factoids rootwarn seen stats botmail) ) { - next if (exists $db{$_}); + foreach ( qw(botmail connections factoids rootwarn seen stats onjoin) ) { + if (exists $db{$_}) { + $cache{has_table}{$_} = 1; + next; + } + &status("checkTables: creating new table $_..."); - &sqlCreateTable($_); + $cache{create_table}{$_} = 1; + + &sqlCreateTable($_, $param{DBType}); } }