2 # db_mysql.pl: MySQL database frontend.
4 # Version: v0.2c (19991224)
10 if (&IsParam("useStrict")) { use strict; }
13 my ($db, $user, $pass) = @_;
14 my $dsn = "DBI:mysql:$db:$param{'SQLHost'}";
15 $dbh = DBI->connect($dsn, $user, $pass);
18 &status("Opened MySQL connection to $param{'SQLHost'}");
20 &ERROR("cannot connect to $param{'SQLHost'}.");
21 &ERROR("since mysql is not available, shutting down bot!");
31 return 0 unless ($dbh);
33 &status("Closed MySQL connection to $param{'SQLHost'}.");
40 # Usage: &dbQuote($str);
42 return $dbh->quote($_[0]);
46 # Usage: &dbGet($table, $select, $where);
48 my ($table, $select, $where) = @_;
49 my $query = "SELECT $select FROM $table";
50 $query .= " WHERE $where" if ($where);
52 if (!defined $select) {
53 &WARN("dbGet: select == NULL. table => $table");
58 if (!($sth = $dbh->prepare($query))) {
59 &ERROR("Get: prepare: $DBI::errstr");
65 &ERROR("Get: execute: '$query'");
70 my @retval = $sth->fetchrow_array;
74 if (scalar @retval > 1) {
76 } elsif (scalar @retval == 1) {
84 # Usage: &dbGetCol($table, $select, $where, [$type]);
86 my ($table, $select, $where, $type) = @_;
87 my $query = "SELECT $select FROM $table";
88 $query .= " WHERE ".$where if ($where);
91 &DEBUG("dbGetCol: query => '$query'.");
93 my $sth = $dbh->prepare($query);
96 &ERROR("GetCol: execute: '$query'");
97 # &ERROR("GetCol => $DBI::errstr");
102 if (defined $type and $type == 2) {
103 &DEBUG("dbgetcol: type 2!");
104 while (my @row = $sth->fetchrow_array) {
105 $retval{$row[0]} = join(':', $row[1..$#row]);
107 &DEBUG("dbgetcol: count => ".scalar(keys %retval) );
109 } elsif (defined $type and $type == 1) {
110 while (my @row = $sth->fetchrow_array) {
111 # reverse it to make it easier to count.
112 if (scalar @row == 2) {
113 $retval{$row[1]}{$row[0]} = 1;
114 } elsif (scalar @row == 3) {
115 $retval{$row[1]}{$row[0]} = 1;
117 # what to do if there's only one or more than 3?
121 while (my @row = $sth->fetchrow_array) {
122 $retval{$row[0]} = $row[1];
132 # Usage: &dbGetColNiceHash($table, $select, $where);
133 sub dbGetColNiceHash {
134 my ($table, $select, $where) = @_;
136 my $query = "SELECT $select FROM $table";
137 $query .= " WHERE ".$where if ($where);
140 &DEBUG("dbGetColNiceHash: query => '$query'.");
142 my $sth = $dbh->prepare($query);
144 if (!$sth->execute) {
145 &ERROR("GetColNiceHash: execute: '$query'");
146 # &ERROR("GetCol => $DBI::errstr");
151 # todo: get column names, do $hash{$primkey}{blah} = ...
152 while (my @row = $sth->fetchrow_array) {
153 # reverse it to make it easier to count.
162 # Usage: &dbGetColInfo($table);
166 my $query = "SHOW COLUMNS from $table";
169 my $sth = $dbh->prepare($query);
171 if (!$sth->execute) {
172 &ERROR("GRI => '$query'");
173 &ERROR("GRI => $DBI::errstr");
179 while (my @row = $sth->fetchrow_array) {
180 push(@cols, $row[0]);
188 # Usage: &dbSet($table, $primhash_ref, $hash_ref);
189 # Note: dbSet does dbQuote.
191 my ($table, $phref, $href) = @_;
192 my $where = join(' AND ', map {
193 $_."=".&dbQuote($phref->{$_})
197 my $result = &dbGet($table, join(',', keys %{$phref}), $where);
200 foreach (keys %{$href}) {
202 push(@vals, &dbQuote($href->{$_}) );
205 if (!@keys or !@vals) {
206 &WARN("dbset: keys or vals is NULL.");
211 if (defined $result) {
213 for(my$i=0; $i<scalar @keys; $i++) {
214 push(@keyval, $keys[$i]."=".$vals[$i] );
217 $query = "UPDATE $table SET ".
218 join(' AND ', @keyval).
221 foreach (keys %{$phref}) {
223 push(@vals, &dbQuote($phref->{$_}) );
226 $query = sprintf("INSERT INTO $table (%s) VALUES (%s)",
227 join(',',@keys), join(',',@vals) );
230 &dbRaw("Set", $query);
236 # Usage: &dbUpdate($table, $primkey, $primval, %hash);
237 # Note: dbUpdate does dbQuote.
239 my ($table, $primkey, $primval, %hash) = @_;
242 foreach (keys %hash) {
243 push(@array, "$_=".&dbQuote($hash{$_}) );
246 &dbRaw("Update", "UPDATE $table SET ".join(', ', @array).
247 " WHERE $primkey=".&dbQuote($primval)
254 # Usage: &dbInsert($table, $primkey, %hash);
255 # Note: dbInsert does dbQuote.
257 my ($table, $primkey, %hash, $delay) = @_;
262 &DEBUG("dbI: delay => $delay");
266 foreach (keys %hash) {
268 push(@vals, &dbQuote($hash{$_}));
271 &dbRaw("Insert($table)", "INSERT $p INTO $table (".join(',',@keys).
272 ") VALUES (".join(',',@vals).")"
279 # Usage: &dbReplace($table, %hash);
280 # Note: dbReplace does optional dbQuote.
282 my ($table, %hash) = @_;
285 foreach (keys %hash) {
286 if (s/^-//) { # as is.
288 push(@vals, $hash{'-'.$_});
291 push(@vals, &dbQuote($hash{$_}));
296 &DEBUG("REPLACE INTO $table (".join(',',@keys).
297 ") VALUES (". join(',',@vals). ")" );
300 &dbRaw("Replace($table)", "REPLACE INTO $table (".join(',',@keys).
301 ") VALUES (". join(',',@vals). ")"
308 # Usage: &dbSetRow($table, $vref, $delay);
309 # Note: dbSetRow does dbQuote.
311 my ($table, $vref, $delay) = @_;
312 my $p = ($delay) ? " DELAYED " : "";
314 # see 'perldoc perlreftut'
316 foreach (@{ $vref }) {
317 push(@values, &dbQuote($_) );
320 if (!scalar @values) {
321 &WARN("dbSetRow: values array == NULL.");
325 return &dbRaw("SetRow", "INSERT $p INTO $table VALUES (".
326 join(",", @values) .")" );
330 # Usage: &dbDel($table, $primkey, $primval, [$key]);
331 # Note: dbDel does dbQuote
333 my ($table, $primkey, $primval, $key) = @_;
335 &dbRaw("Del", "DELETE FROM $table WHERE $primkey=".
342 # Usage: &dbRaw($prefix,$rawquery);
344 my ($prefix,$query) = @_;
347 if (!($sth = $dbh->prepare($query))) {
348 &ERROR("Raw($prefix): $DBI::errstr");
352 # &DEBUG("query => '$query'.");
355 if (!$sth->execute) {
356 &ERROR("Raw($prefix): => '$query'");
357 # $DBI::errstr is printed as warning automatically.
367 # Usage: &dbRawReturn($rawquery);
372 my $sth = $dbh->prepare($query);
374 &ERROR("RawReturn => '$query'.") unless $sth->execute;
375 while (my @row = $sth->fetchrow_array) {
376 push(@retval, $row[0]);
383 ####################################################################
384 ##### Misc DBI stuff...
388 # Usage: &countKeys($table, [$col]);
390 my ($table, $col) = @_;
393 return (&dbRawReturn("SELECT count($col) FROM $table"))[0];
396 # Usage: &sumKey($table, $col);
398 my ($table, $col) = @_;
400 return (&dbRawReturn("SELECT sum($col) FROM $table"))[0];
404 # Usage: &randKey($table, $select);
406 my ($table, $select) = @_;
407 my $rand = int(rand(&countKeys($table) - 1));
408 my $query = "SELECT $select FROM $table LIMIT $rand,1";
410 my $sth = $dbh->prepare($query);
412 &WARN("randKey($query)") unless $sth->execute;
413 my @retval = $sth->fetchrow_array;
420 # Usage: &deleteTable($table);
422 &dbRaw("deleteTable($_[0])", "DELETE FROM $_[0]");
426 # Usage: &searchTable($table, $select, $key, $str);
427 # Note: searchTable does dbQuote.
429 my($table, $select, $key, $str) = @_;
433 # allow two types of wildcards.
434 if ($str =~ /^\^(.*)\$$/) {
435 &DEBUG("searchTable: should use dbGet(), heh.");
438 $str .= "%" if ($str =~ s/^\^//);
439 $str = "%".$str if ($str =~ s/\$$//);
440 $str = "%".$str."%" if ($str eq $origStr); # el-cheapo fix.
444 $str =~ s/\?/\_/g; # '.' should be supported, too.
447 my $query = "SELECT $select FROM $table WHERE $key LIKE ".
449 my $sth = $dbh->prepare($query);
451 if (!$sth->execute) {
452 &WARN("Search($query)");
456 while (my @row = $sth->fetchrow_array) {
457 push(@results, $row[0]);
464 ####################################################################
465 ##### Factoid related stuff...
469 # Usage: &getFactInfo($faqtoid, type);
470 # Note: getFactInfo does dbQuote
472 return &dbGet("factoids", $_[1], "factoid_key=".&dbQuote($_[0]) );
476 # Usage: &getFactoid($faqtoid);
478 return &getFactInfo($_[0], "factoid_value");
482 # Usage: &delFactoid($faqtoid);
486 &dbDel("factoids", "factoid_key",$faqtoid);
487 &status("DELETED '$faqtoid'");
493 return unless (&IsParam("SQLDebug"));
495 return unless (fileno SQLDEBUG);
497 print SQLDEBUG $_[0]."\n";
502 my(@path) = (".","..","../..");
507 my $file = "$_/setup/$table.sql";
508 &DEBUG("dbCT: file => $file");
509 next unless ( -f $file );
511 &DEBUG("dbCT: found!!!");
526 &dbRaw("createTable($table)", $data);
532 my $database_exists = 0;
533 foreach ( &dbRawReturn("SHOW DATABASES") ) {
534 $database_exists++ if ($_ eq $param{'DBName'});
537 unless ($database_exists) {
538 &status("Creating database $param{DBName}...");
539 $query = "CREATE DATABASE $param{DBName}";
540 &dbRaw("create(db $param{DBName})", $query);
543 # retrieve a list of db's from the server.
545 foreach ($dbh->func('_ListTables')) {
550 if (!scalar keys %db) {
551 # &status("Creating database $param{'DBName'}...");
552 # $query = "CREATE DATABASE $param{'DBName'}";
553 # &dbRaw("create(db $param{'DBName'})", $query);
556 foreach ("factoids", "freshmeat", "rootwarn", "seen", "stats",
558 next if (exists $db{$_});
559 &status(" creating new table $_...");