2 # db_mysql.pl: MySQL database frontend.
4 # Version: v0.2c (19991224)
10 if (&IsParam("useStrict")) { use strict; }
13 # &openDB($dbname, $sqluser, $sqlpass, $nofail);
15 my ($db, $user, $pass, $no_fail) = @_;
16 my $dsn = "DBI:mysql:$db";
18 if (exists $param{'SQLHost'} and $param{'SQLHost'}) {
19 $dsn .= ":$param{SQLHost}";
20 $hoststr = " to $param{'SQLHost'}";
22 $dbh = DBI->connect($dsn, $user, $pass);
25 &status("Opened MySQL connection$hoststr");
27 &ERROR("cannot connect$hoststr.");
28 &ERROR("since mysql is not available, shutting down bot!");
40 return 0 unless ($dbh);
43 $hoststr = " to $param{'SQLHost'}" if (exists $param{'SQLHost'});
45 &status("Closed MySQL connection$hoststr.");
52 # Usage: &dbQuote($str);
54 return $dbh->quote($_[0]);
58 # Usage: &dbGet($table, $select, $where);
60 my ($table, $select, $where) = @_;
61 my $query = "SELECT $select FROM $table";
62 $query .= " WHERE $where" if ($where);
64 if (!defined $select or $select =~ /^\s*$/) {
65 &WARN("dbGet: select == NULL.");
69 if (!defined $table or $table =~ /^\s*$/) {
70 &WARN("dbGet: table == NULL.");
75 if (!($sth = $dbh->prepare($query))) {
76 &ERROR("Get: prepare: $DBI::errstr");
82 &ERROR("Get: execute: '$query'");
87 my @retval = $sth->fetchrow_array;
91 if (scalar @retval > 1) {
93 } elsif (scalar @retval == 1) {
101 # Usage: &dbGetCol($table, $select, $where, [$type]);
103 my ($table, $select, $where, $type) = @_;
104 my $query = "SELECT $select FROM $table";
105 $query .= " WHERE ".$where if ($where);
108 my $sth = $dbh->prepare($query);
110 if (!$sth->execute) {
111 &ERROR("GetCol: execute: '$query'");
116 if (defined $type and $type == 2) {
117 &DEBUG("dbgetcol: type 2!");
118 while (my @row = $sth->fetchrow_array) {
119 $retval{$row[0]} = join(':', $row[1..$#row]);
121 &DEBUG("dbgetcol: count => ".scalar(keys %retval) );
123 } elsif (defined $type and $type == 1) {
124 while (my @row = $sth->fetchrow_array) {
125 # reverse it to make it easier to count.
126 if (scalar @row == 2) {
127 $retval{$row[1]}{$row[0]} = 1;
128 } elsif (scalar @row == 3) {
129 $retval{$row[1]}{$row[0]} = 1;
131 # what to do if there's only one or more than 3?
135 while (my @row = $sth->fetchrow_array) {
136 $retval{$row[0]} = $row[1];
146 # Usage: &dbGetColNiceHash($table, $select, $where);
147 sub dbGetColNiceHash {
148 my ($table, $select, $where) = @_;
150 my $query = "SELECT $select FROM $table";
151 $query .= " WHERE ".$where if ($where);
154 my $sth = $dbh->prepare($query);
156 if (!$sth->execute) {
157 &ERROR("GetColNiceHash: execute: '$query'");
158 # &ERROR("GetCol => $DBI::errstr");
163 %retval = %{ $sth->fetchrow_hashref() };
171 # Usage: &dbGetColInfo($table);
175 my $query = "SHOW COLUMNS from $table";
178 my $sth = $dbh->prepare($query);
180 if (!$sth->execute) {
181 &ERROR("GRI => '$query'");
182 &ERROR("GRI => $DBI::errstr");
188 while (my @row = $sth->fetchrow_array) {
189 push(@cols, $row[0]);
197 # Usage: &dbSet($table, $primhash_ref, $hash_ref);
198 # Note: dbSet does dbQuote.
200 my ($table, $phref, $href) = @_;
201 my $where = join(' AND ', map {
202 $_."=".&dbQuote($phref->{$_})
206 if (!defined $phref) {
207 &WARN("dbset: phref == NULL.");
211 if (!defined $href) {
212 &WARN("dbset: href == NULL.");
216 if (!defined $table) {
217 &WARN("dbset: table == NULL.");
221 my $result = &dbGet($table, join(',', keys %{$phref}), $where);
224 foreach (keys %{$href}) {
226 push(@vals, &dbQuote($href->{$_}) );
229 if (!@keys or !@vals) {
230 &WARN("dbset: keys or vals is NULL.");
235 if (defined $result) {
237 for(my$i=0; $i<scalar @keys; $i++) {
238 push(@keyval, $keys[$i]."=".$vals[$i] );
241 $query = "UPDATE $table SET ".
242 join(' AND ', @keyval).
245 foreach (keys %{$phref}) {
247 push(@vals, &dbQuote($phref->{$_}) );
250 $query = sprintf("INSERT INTO $table (%s) VALUES (%s)",
251 join(',',@keys), join(',',@vals) );
254 &dbRaw("Set", $query);
260 # Usage: &dbUpdate($table, $primkey, $primval, %hash);
261 # Note: dbUpdate does dbQuote.
263 my ($table, $primkey, $primval, %hash) = @_;
266 foreach (keys %hash) {
267 push(@array, "$_=".&dbQuote($hash{$_}) );
270 &dbRaw("Update", "UPDATE $table SET ".join(', ', @array).
271 " WHERE $primkey=".&dbQuote($primval)
278 # Usage: &dbInsert($table, $primkey, %hash);
279 # Note: dbInsert does dbQuote.
281 my ($table, $primkey, %hash, $delay) = @_;
286 &DEBUG("dbI: delay => $delay");
290 foreach (keys %hash) {
292 push(@vals, &dbQuote($hash{$_}));
295 &dbRaw("Insert($table)", "INSERT $p INTO $table (".join(',',@keys).
296 ") VALUES (".join(',',@vals).")"
303 # Usage: &dbReplace($table, $key, %hash);
304 # Note: dbReplace does optional dbQuote.
306 my ($table, $key, %hash) = @_;
309 foreach (keys %hash) {
310 if (s/^-//) { # as is.
312 push(@vals, $hash{'-'.$_});
315 push(@vals, &dbQuote($hash{$_}));
320 &DEBUG("REPLACE INTO $table (".join(',',@keys).
321 ") VALUES (". join(',',@vals). ")" );
324 &dbRaw("Replace($table)", "REPLACE INTO $table (".join(',',@keys).
325 ") VALUES (". join(',',@vals). ")"
332 # Usage: &dbSetRow($table, $vref, $delay);
333 # Note: dbSetRow does dbQuote.
335 my ($table, $vref, $delay) = @_;
336 my $p = ($delay) ? " DELAYED " : "";
338 # see 'perldoc perlreftut'
340 foreach (@{ $vref }) {
341 push(@values, &dbQuote($_) );
344 if (!scalar @values) {
345 &WARN("dbSetRow: values array == NULL.");
349 return &dbRaw("SetRow", "INSERT $p INTO $table VALUES (".
350 join(",", @values) .")" );
354 # Usage: &dbDel($table, $primkey, $primval, [$key]);
355 # Note: dbDel does dbQuote
357 my ($table, $primkey, $primval, $key) = @_;
359 &dbRaw("Del", "DELETE FROM $table WHERE $primkey=".
366 # Usage: &dbRaw($prefix,$rawquery);
368 my ($prefix,$query) = @_;
371 if (!($sth = $dbh->prepare($query))) {
372 &ERROR("Raw($prefix): $DBI::errstr");
376 # &DEBUG("query => '$query'.");
379 if (!$sth->execute) {
380 &ERROR("Raw($prefix): => '$query'");
381 # $DBI::errstr is printed as warning automatically.
391 # Usage: &dbRawReturn($rawquery);
396 my $sth = $dbh->prepare($query);
398 &ERROR("RawReturn => '$query'.") unless $sth->execute;
399 while (my @row = $sth->fetchrow_array) {
400 push(@retval, $row[0]);
407 ####################################################################
408 ##### Misc DBI stuff...
412 # Usage: &countKeys($table, [$col]);
414 my ($table, $col) = @_;
417 return (&dbRawReturn("SELECT count($col) FROM $table"))[0];
420 # Usage: &sumKey($table, $col);
422 my ($table, $col) = @_;
424 return (&dbRawReturn("SELECT sum($col) FROM $table"))[0];
428 # Usage: &randKey($table, $select);
430 my ($table, $select) = @_;
431 my $rand = int(rand(&countKeys($table) - 1));
432 my $query = "SELECT $select FROM $table LIMIT $rand,1";
434 my $sth = $dbh->prepare($query);
436 &WARN("randKey($query)") unless $sth->execute;
437 my @retval = $sth->fetchrow_array;
444 # Usage: &deleteTable($table);
446 &dbRaw("deleteTable($_[0])", "DELETE FROM $_[0]");
450 # Usage: &searchTable($table, $select, $key, $str);
451 # Note: searchTable does dbQuote.
453 my($table, $select, $key, $str) = @_;
457 # allow two types of wildcards.
458 if ($str =~ /^\^(.*)\$$/) {
459 &DEBUG("searchTable: should use dbGet(), heh.");
462 $str .= "%" if ($str =~ s/^\^//);
463 $str = "%".$str if ($str =~ s/\$$//);
464 $str = "%".$str."%" if ($str eq $origStr); # el-cheapo fix.
468 $str =~ s/\?/\_/g; # '.' should be supported, too.
471 my $query = "SELECT $select FROM $table WHERE $key LIKE ".
473 my $sth = $dbh->prepare($query);
475 if (!$sth->execute) {
476 &WARN("Search($query)");
480 while (my @row = $sth->fetchrow_array) {
481 push(@results, $row[0]);
490 my(@path) = ($bot_data_dir, ".","..","../..");
495 my $file = "$_/setup/$table.sql";
496 &DEBUG("dbCT: file => $file");
497 next unless ( -f $file );
499 &DEBUG("dbCT: found!!!");
514 &dbRaw("createTable($table)", $data);
520 my $database_exists = 0;
521 foreach ( &dbRawReturn("SHOW DATABASES") ) {
522 $database_exists++ if ($_ eq $param{'DBName'});
525 unless ($database_exists) {
526 &status("Creating database $param{DBName}...");
527 $query = "CREATE DATABASE $param{DBName}";
528 &dbRaw("create(db $param{DBName})", $query);
531 # retrieve a list of db's from the server.
533 foreach ($dbh->func('_ListTables')) {
538 if (!scalar keys %db) {
539 # &status("Creating database $param{'DBName'}...");
540 # $query = "CREATE DATABASE $param{'DBName'}";
541 # &dbRaw("create(db $param{'DBName'})", $query);
544 foreach ("factoids", "freshmeat", "rootwarn", "seen", "stats",
546 next if (exists $db{$_});
547 &status(" creating new table $_...");