&status("Opened MySQL connection to $param{'SQLHost'}");
} else {
&ERROR("cannot connect to $param{'SQLHost'}.");
+ &ERROR("since mysql is not available, shutting down bot!");
&shutdown();
&closePID();
exit 1;
}
#####
-# Usage: &dbGet($table, $primkey, $primval, $select);
+# Usage: &dbGet($table, $select, $where);
sub dbGet {
- my ($table, $primkey, $primval, $select) = @_;
- my $query = "SELECT $select FROM $table WHERE $primkey=".
- &dbQuote($primval);
+ my ($table, $select, $where) = @_;
+ my $query = "SELECT $select FROM $table";
+ $query .= " WHERE $where" if ($where);
my $sth;
if (!($sth = $dbh->prepare($query))) {
- &ERROR("Get: $DBI::errstr");
+ &ERROR("Get: prepare: $DBI::errstr");
return;
}
&SQLDebug($query);
if (!$sth->execute) {
- &ERROR("Get => '$query'");
- &ERROR("Get => $DBI::errstr");
+ &ERROR("Get: execute: '$query'");
$sth->finish;
return 0;
}
my $sth = $dbh->prepare($query);
&SQLDebug($query);
if (!$sth->execute) {
- &ERROR("GetCol => '$query'");
- &ERROR("GetCol => $DBI::errstr");
+ &ERROR("GetCol: execute: '$query'");
+# &ERROR("GetCol => $DBI::errstr");
$sth->finish;
return;
}
}
####
-# Usage: &dbGetRowInfo($table);
-sub dbGetRowInfo {
+# Usage: &dbGetColInfo($table);
+sub dbGetColInfo {
my ($table) = @_;
my $query = "SHOW COLUMNS from $table";
}
#####
-# Usage: &dbSet($table, $primkey, $primval, $key, $val);
+# Usage: &dbSet($table, $primhash_ref, $hash_ref);
sub dbSet {
- my ($table, $primkey, $primval, $key, $val) = @_;
- my $query;
+ my ($table, $phref, $href) = @_;
+ my $where = join(' AND ', map {
+ $_."=".&dbQuote($phref->{$_})
+ } keys %{$phref}
+ );
+
+ my $result = &dbGet($table, join(',', keys %{$phref}), $where);
+
+ my(@keys,@vals);
+ foreach (keys %{$href}) {
+ push(@keys, $_);
+ push(@vals, &dbQuote($href->{$_}) );
+ }
+
+ if (!@keys or !@vals) {
+ &WARN("dbset: keys or vals is NULL.");
+ return;
+ }
- my $result = &dbGet($table,$primkey,$primval,$primkey);
+ my $query;
if (defined $result) {
- $query = "UPDATE $table SET $key=".&dbQuote($val).
- " WHERE $primkey=".&dbQuote($primval);
+ my @keyval;
+ for(my$i=0; $i<scalar @keys; $i++) {
+ push(@keyval, $keys[$i]."=".$vals[$i] );
+ }
+
+ $query = "UPDATE $table SET ".
+ join(' AND ', @keyval).
+ " WHERE ".$where;
} else {
- $query = "INSERT INTO $table ($primkey,$key) VALUES (".
- &dbQuote($primval).",".&dbQuote($val).")";
+ foreach (keys %{$phref}) {
+ push(@keys, $_);
+ push(@vals, &dbQuote($phref->{$_}) );
+ }
+
+ $query = sprintf("INSERT INTO $table (%s) VALUES (%s)",
+ join(',',@keys), join(',',@vals) );
}
&dbRaw("Set", $query);
#####
# Usage: &dbInsert($table, $primkey, %hash);
sub dbInsert {
- my ($table, $primkey, %hash) = @_;
+ my ($table, $primkey, %hash, $delay) = @_;
my (@keys, @vals);
+ my $p = "";
+
+ if ($delay) {
+ &DEBUG("dbI: delay => $delay");
+ $p = " DELAYED";
+ }
foreach (keys %hash) {
push(@keys, $_);
push(@vals, &dbQuote($hash{$_}));
}
- &dbRaw("Insert($table)", "INSERT INTO $table (".join(',',@keys).
+ &dbRaw("Insert($table)", "INSERT $p INTO $table (".join(',',@keys).
") VALUES (".join(',',@vals).")"
);
return 1;
}
+#####
+# Usage: &dbReplace($table, %hash);
+sub dbReplace {
+ my ($table, %hash) = @_;
+ my (@keys, @vals);
+
+ foreach (keys %hash) {
+ if (s/^-//) { # as is.
+ push(@keys, $_);
+ push(@vals, $hash{'-'.$_});
+ } else {
+ push(@keys, $_);
+ push(@vals, &dbQuote($hash{$_}));
+ }
+ }
+
+ &dbRaw("Replace($table)", "REPLACE INTO $table (".join(',',@keys).
+ ") VALUES (". join(',',@vals). ")"
+ );
+
+ return 1;
+}
+
#####
# Usage: &dbSetRow($table, @values);
-sub dbSetRow {
- my ($table, @values) = @_;
+sub dbSetRow ($@$) {
+ my ($table, @values, $delay) = @_;
+ my $p = ($delay) ? " DELAYED " : "";
foreach (@values) {
$_ = &dbQuote($_);
}
- return &dbRaw("SetRow", "INSERT INTO $table VALUES (".
+ return &dbRaw("SetRow", "INSERT $p INTO $table VALUES (".
join(",", @values) .")" );
}
return 0;
}
+# &DEBUG("query => '$query'.");
+
&SQLDebug($query);
if (!$sth->execute) {
&ERROR("Raw($prefix): => '$query'");
- &ERROR("Raw($prefix): $DBI::errstr");
+ # $DBI::errstr is printed as warning automatically.
$sth->finish;
return 0;
}
#####
#####
-# Usage: &countKeys($table);
+# Usage: &countKeys($table, [$col]);
sub countKeys {
- my ($table) = @_;
+ my ($table, $col) = @_;
+ $col ||= "*";
+
+ return (&dbRawReturn("SELECT count($col) FROM $table"))[0];
+}
+
+# Usage: &sumKey($table, $col);
+sub sumKey {
+ my ($table, $col) = @_;
- return (&dbRawReturn("SELECT count(*) FROM $table"))[0];
+ return (&dbRawReturn("SELECT sum($col) FROM $table"))[0];
}
##### NOT USED.
#####
# Usage: &getFactInfo($faqtoid, type);
sub getFactInfo {
- return &dbGet("factoids", "factoid_key", $_[0], $_[1]);
+ return &dbGet("factoids", $_[1], "factoid_key='$_[0]'");
}
#####
print SQLDEBUG $_[0]."\n";
}
+sub dbCreateTable {
+ my($table) = @_;
+ my(@path) = (".","..","../..");
+ my $found = 0;
+ my $data;
+
+ foreach (@path) {
+ my $file = "$_/setup/$table.sql";
+ &DEBUG("dbCT: file => $file");
+ next unless ( -f $file );
+
+ &DEBUG("found!!!");
+
+ open(IN, $file);
+ while (<IN>) {
+ chop;
+ $data .= $_;
+ }
+
+ $found++;
+ last;
+ }
+
+ if (!$found) {
+ return 0;
+ } else {
+ &dbRaw("create($table)", $data);
+ return 1;
+ }
+}
+
+sub checkTables {
+ # retrieve a list of db's from the server.
+ my %db;
+ foreach ($dbh->func('_ListTables')) {
+ $db{$_} = 1;
+ }
+
+ # create database.
+ if (!scalar keys %db) {
+ &status("Creating database $param{'DBName'}...");
+ $query = "CREATE DATABASE $param{'DBName'}";
+ &dbRaw("create(db $param{'DBName'})", $query);
+ }
+
+ foreach ("factoids", "freshmeat", "rootwarn", "seen", "stats",
+ ) {
+ next if (exists $db{$_});
+ &status(" creating new table $_...");
+
+ &dbCreateTable($_);
+ }
+}
+
1;