if (&IsParam("useStrict")) { use strict; }
+#####
+# &openDB($dbname, $sqluser, $sqlpass, $nofail);
sub openDB {
- my ($db, $user, $pass) = @_;
- my $dsn = "DBI:mysql:$db:$param{'SQLHost'}";
+ my ($db, $user, $pass, $no_fail) = @_;
+ my $dsn = "DBI:mysql:$db";
+ my $hoststr = "";
+ if (exists $param{'SQLHost'} and $param{'SQLHost'}) {
+ $dsn .= ":$param{SQLHost}";
+ $hoststr = " to $param{'SQLHost'}";
+ }
$dbh = DBI->connect($dsn, $user, $pass);
if ($dbh) {
- &status("Opened MySQL connection to $param{'SQLHost'}");
+ &status("Opened MySQL connection$hoststr");
} else {
- &ERROR("cannot connect to $param{'SQLHost'}.");
+ &ERROR("cannot connect$hoststr.");
&ERROR("since mysql is not available, shutting down bot!");
- &shutdown();
&closePID();
+ &closeSHM($shm);
+ &closeLog();
+
+ return if ($no_fail);
+
exit 1;
}
}
sub closeDB {
return 0 unless ($dbh);
- &status("Closed MySQL connection to $param{'SQLHost'}.");
+ my $hoststr = "";
+ $hoststr = " to $param{'SQLHost'}" if (exists $param{'SQLHost'});
+
+ &status("Closed MySQL connection$hoststr.");
$dbh->disconnect();
+
return 1;
}
# Usage: &dbGet($table, $select, $where);
sub dbGet {
my ($table, $select, $where) = @_;
- my $query = "SELECT $select FROM $table";
+ my $query = "SELECT $select FROM $table";
$query .= " WHERE $where" if ($where);
+ if (!defined $select or $select =~ /^\s*$/) {
+ &WARN("dbGet: select == NULL.");
+ return;
+ }
+
+ if (!defined $table or $table =~ /^\s*$/) {
+ &WARN("dbGet: table == NULL.");
+ return;
+ }
+
my $sth;
if (!($sth = $dbh->prepare($query))) {
&ERROR("Get: prepare: $DBI::errstr");
}
#####
-# Usage: &dbGetCol($table, $primkey, $key, [$type]);
+# Usage: &dbGetCol($table, $select, $where, [$type]);
sub dbGetCol {
- my ($table, $primkey, $key, $type) = @_;
- my $query = "SELECT $primkey,$key FROM $table WHERE $key IS NOT NULL";
+ my ($table, $select, $where, $type) = @_;
+ my $query = "SELECT $select FROM $table";
+ $query .= " WHERE ".$where if ($where);
my %retval;
my $sth = $dbh->prepare($query);
&SQLDebug($query);
if (!$sth->execute) {
&ERROR("GetCol: execute: '$query'");
-# &ERROR("GetCol => $DBI::errstr");
$sth->finish;
return;
}
- if (defined $type and $type == 1) {
+ if (defined $type and $type == 2) {
+ &DEBUG("dbgetcol: type 2!");
+ while (my @row = $sth->fetchrow_array) {
+ $retval{$row[0]} = join(':', $row[1..$#row]);
+ }
+ &DEBUG("dbgetcol: count => ".scalar(keys %retval) );
+
+ } elsif (defined $type and $type == 1) {
while (my @row = $sth->fetchrow_array) {
# reverse it to make it easier to count.
- $retval{$row[1]}{$row[0]} = 1;
+ if (scalar @row == 2) {
+ $retval{$row[1]}{$row[0]} = 1;
+ } elsif (scalar @row == 3) {
+ $retval{$row[1]}{$row[0]} = 1;
+ }
+ # what to do if there's only one or more than 3?
}
+
} else {
while (my @row = $sth->fetchrow_array) {
$retval{$row[0]} = $row[1];
return %retval;
}
+#####
+# Usage: &dbGetColNiceHash($table, $select, $where);
+sub dbGetColNiceHash {
+ my ($table, $select, $where) = @_;
+ $select ||= "*";
+ my $query = "SELECT $select FROM $table";
+ $query .= " WHERE ".$where if ($where);
+ my %retval;
+
+ my $sth = $dbh->prepare($query);
+ &SQLDebug($query);
+ if (!$sth->execute) {
+ &ERROR("GetColNiceHash: execute: '$query'");
+# &ERROR("GetCol => $DBI::errstr");
+ $sth->finish;
+ return;
+ }
+
+ %retval = %{ $sth->fetchrow_hashref() };
+
+ $sth->finish;
+
+ return %retval;
+}
+
####
# Usage: &dbGetColInfo($table);
sub dbGetColInfo {
#####
# Usage: &dbSet($table, $primhash_ref, $hash_ref);
+# Note: dbSet does dbQuote.
sub dbSet {
my ($table, $phref, $href) = @_;
my $where = join(' AND ', map {
} keys %{$phref}
);
+ if (!defined $phref) {
+ &WARN("dbset: phref == NULL.");
+ return;
+ }
+
+ if (!defined $href) {
+ &WARN("dbset: href == NULL.");
+ return;
+ }
+
+ if (!defined $table) {
+ &WARN("dbset: table == NULL.");
+ return;
+ }
+
my $result = &dbGet($table, join(',', keys %{$phref}), $where);
my(@keys,@vals);
#####
# Usage: &dbUpdate($table, $primkey, $primval, %hash);
+# Note: dbUpdate does dbQuote.
sub dbUpdate {
my ($table, $primkey, $primval, %hash) = @_;
my (@array);
#####
# Usage: &dbInsert($table, $primkey, %hash);
+# Note: dbInsert does dbQuote.
sub dbInsert {
my ($table, $primkey, %hash, $delay) = @_;
my (@keys, @vals);
}
#####
-# Usage: &dbReplace($table, %hash);
+# Usage: &dbReplace($table, $key, %hash);
+# Note: dbReplace does optional dbQuote.
sub dbReplace {
- my ($table, %hash) = @_;
+ my ($table, $key, %hash) = @_;
my (@keys, @vals);
foreach (keys %hash) {
push(@vals, $hash{'-'.$_});
} else {
push(@keys, $_);
- push(@vals, &dbQuote($hash{$_}));
+ push(@vals, &dbQuote( $hash{$_} ));
}
}
+ if (0) {
+ &DEBUG("REPLACE INTO $table (".join(',',@keys).
+ ") VALUES (". join(',',@vals). ")" );
+ }
+
&dbRaw("Replace($table)", "REPLACE INTO $table (".join(',',@keys).
") VALUES (". join(',',@vals). ")"
);
}
#####
-# Usage: &dbSetRow($table, @values);
+# Usage: &dbSetRow($table, $vref, $delay);
+# Note: dbSetRow does dbQuote.
sub dbSetRow ($@$) {
- my ($table, @values, $delay) = @_;
+ my ($table, $vref, $delay) = @_;
my $p = ($delay) ? " DELAYED " : "";
- foreach (@values) {
- $_ = &dbQuote($_);
+ # see 'perldoc perlreftut'
+ my @values;
+ foreach (@{ $vref }) {
+ push(@values, &dbQuote($_) );
+ }
+
+ if (!scalar @values) {
+ &WARN("dbSetRow: values array == NULL.");
+ return;
}
return &dbRaw("SetRow", "INSERT $p INTO $table VALUES (".
#####
# Usage: &dbDel($table, $primkey, $primval, [$key]);
+# Note: dbDel does dbQuote
sub dbDel {
my ($table, $primkey, $primval, $key) = @_;
return (&dbRawReturn("SELECT sum($col) FROM $table"))[0];
}
-##### NOT USED.
-# Usage: &getKeys($table,$primkey);
-sub getKeys {
- my ($table,$primkey) = @_;
- my @retval;
-
- my $query = "SELECT $primkey FROM $table";
- my $sth = $dbh->prepare($query);
-
- &SQLDebug($query);
- &WARN("ERROR: getKeys($query)") unless $sth->execute;
-
- while (my @row = $sth->fetchrow_array) {
- push(@retval, $row[0]);
- }
- $sth->finish;
-
- return @retval;
-}
-
#####
# Usage: &randKey($table, $select);
sub randKey {
&dbRaw("deleteTable($_[0])", "DELETE FROM $_[0]");
}
+#####
# Usage: &searchTable($table, $select, $key, $str);
+# Note: searchTable does dbQuote.
sub searchTable {
my($table, $select, $key, $str) = @_;
my $origStr = $str;
}
$str =~ s/\_/\\_/g;
- $str =~ s/\?/\_/g; # '.' should be supported, too.
+ $str =~ s/\?/_/g; # '.' should be supported, too.
+ $str =~ s/\*/%/g; # for mysql.
# end of string fix.
my $query = "SELECT $select FROM $table WHERE $key LIKE ".
&dbQuote($str);
my $sth = $dbh->prepare($query);
+
&SQLDebug($query);
- &WARN("Search($query)") unless $sth->execute;
+ if (!$sth->execute) {
+ &WARN("Search($query)");
+ return;
+ }
while (my @row = $sth->fetchrow_array) {
push(@results, $row[0]);
return @results;
}
-####################################################################
-##### Factoid related stuff...
-#####
-
-#####
-# Usage: &getFactInfo($faqtoid, type);
-sub getFactInfo {
- return &dbGet("factoids", $_[1], "factoid_key='$_[0]'");
-}
-
-#####
-# Usage: &getFactoid($faqtoid);
-sub getFactoid {
- return &getFactInfo($_[0], "factoid_value");
-}
-
-#####
-# Usage: &delFactoid($faqtoid);
-sub delFactoid {
- my ($faqtoid) = @_;
-
- &dbDel("factoids", "factoid_key",$faqtoid);
- &status("DELETED '$faqtoid'");
-
- return 1;
-}
-
-sub SQLDebug {
- return unless (&IsParam("SQLDebug"));
-
- return if (!fileno SQLDEBUG);
-
- print SQLDEBUG $_[0]."\n";
-}
-
sub dbCreateTable {
my($table) = @_;
- my(@path) = (".","..","../..");
+ my(@path) = ($bot_data_dir, ".","..","../..");
my $found = 0;
my $data;
&DEBUG("dbCT: file => $file");
next unless ( -f $file );
- &DEBUG("found!!!");
+ &DEBUG("dbCT: found!!!");
open(IN, $file);
while (<IN>) {
if (!$found) {
return 0;
} else {
- &dbRaw("create($table)", $data);
+ &dbRaw("createTable($table)", $data);
return 1;
}
}
sub checkTables {
+ my $database_exists = 0;
+ foreach ( &dbRawReturn("SHOW DATABASES") ) {
+ $database_exists++ if ($_ eq $param{'DBName'});
+ }
+
+ unless ($database_exists) {
+ &status("Creating database $param{DBName}...");
+ $query = "CREATE DATABASE $param{DBName}";
+ &dbRaw("create(db $param{DBName})", $query);
+ }
+
# retrieve a list of db's from the server.
my %db;
foreach ($dbh->func('_ListTables')) {
# create database.
if (!scalar keys %db) {
- &status("Creating database $param{'DBName'}...");
- $query = "CREATE DATABASE $param{'DBName'}";
- &dbRaw("create(db $param{'DBName'})", $query);
+# &status("Creating database $param{'DBName'}...");
+# $query = "CREATE DATABASE $param{'DBName'}";
+# &dbRaw("create(db $param{'DBName'})", $query);
}
foreach ("factoids", "freshmeat", "rootwarn", "seen", "stats",
projects / infobot.git / blobdiff