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";
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
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");
}
&SQLDebug($query);
- if (!$sth->execute) {
- &ERROR("sqlSelectMany: execute: '$query'");
- return;
- }
+
+ return if (!$sth->execute);
return $sth;
}
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.
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);
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.");
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.");
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;
}
# 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;
$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);
}
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";
- &DEBUG("dbCT: table => '$table', file => '$file'");
+ my $file = "$_/setup/$dbtype/$table.sql";
next unless ( -f $file );
- &DEBUG("dbCT: found!!!");
-
open(IN, $file);
while (<IN>) {
chop;
+ next if $_ =~ /^--/;
$data .= $_;
}
}
# 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});
}
}
projects / infobot.git / blobdiff