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!");
29 return 0 unless ($dbh);
31 &status("Closed MySQL connection to $param{'SQLHost'}.");
37 # Usage: &dbQuote($str);
39 return $dbh->quote($_[0]);
43 # Usage: &dbGet($table, $select, $where);
45 my ($table, $select, $where) = @_;
46 my $query = "SELECT $select FROM $table";
47 $query .= " WHERE $where" if ($where);
49 if (!defined $select) {
50 &WARN("dbGet: select == NULL. table => $table");
55 if (!($sth = $dbh->prepare($query))) {
56 &ERROR("Get: prepare: $DBI::errstr");
62 &ERROR("Get: execute: '$query'");
67 my @retval = $sth->fetchrow_array;
71 if (scalar @retval > 1) {
73 } elsif (scalar @retval == 1) {
81 # Usage: &dbGetCol($table, $select, $where, [$type]);
83 my ($table, $select, $where, $type) = @_;
84 my $query = "SELECT $select FROM $table";
85 $query .= " WHERE ".$where if ($where);
88 &DEBUG("dbGetCol: query => '$query'.");
90 my $sth = $dbh->prepare($query);
93 &ERROR("GetCol: execute: '$query'");
94 # &ERROR("GetCol => $DBI::errstr");
99 if (defined $type and $type == 2) {
100 &DEBUG("dbgetcol: type 2!");
101 while (my @row = $sth->fetchrow_array) {
102 $retval{$row[0]} = join(':', $row[1..$#row]);
104 &DEBUG("dbgetcol: count => ".scalar(keys %retval) );
106 } elsif (defined $type and $type == 1) {
107 while (my @row = $sth->fetchrow_array) {
108 # reverse it to make it easier to count.
109 if (scalar @row == 2) {
110 $retval{$row[1]}{$row[0]} = 1;
111 } elsif (scalar @row == 3) {
112 $retval{$row[1]}{$row[0]} = 1;
114 # what to do if there's only one or more than 3?
118 while (my @row = $sth->fetchrow_array) {
119 $retval{$row[0]} = $row[1];
129 # Usage: &dbGetColInfo($table);
133 my $query = "SHOW COLUMNS from $table";
136 my $sth = $dbh->prepare($query);
138 if (!$sth->execute) {
139 &ERROR("GRI => '$query'");
140 &ERROR("GRI => $DBI::errstr");
146 while (my @row = $sth->fetchrow_array) {
147 push(@cols, $row[0]);
155 # Usage: &dbSet($table, $primhash_ref, $hash_ref);
157 my ($table, $phref, $href) = @_;
158 my $where = join(' AND ', map {
159 $_."=".&dbQuote($phref->{$_})
163 my $result = &dbGet($table, join(',', keys %{$phref}), $where);
166 foreach (keys %{$href}) {
168 push(@vals, &dbQuote($href->{$_}) );
171 if (!@keys or !@vals) {
172 &WARN("dbset: keys or vals is NULL.");
177 if (defined $result) {
179 for(my$i=0; $i<scalar @keys; $i++) {
180 push(@keyval, $keys[$i]."=".$vals[$i] );
183 $query = "UPDATE $table SET ".
184 join(' AND ', @keyval).
187 foreach (keys %{$phref}) {
189 push(@vals, &dbQuote($phref->{$_}) );
192 $query = sprintf("INSERT INTO $table (%s) VALUES (%s)",
193 join(',',@keys), join(',',@vals) );
196 &dbRaw("Set", $query);
202 # Usage: &dbUpdate($table, $primkey, $primval, %hash);
204 my ($table, $primkey, $primval, %hash) = @_;
207 foreach (keys %hash) {
208 push(@array, "$_=".&dbQuote($hash{$_}) );
211 &dbRaw("Update", "UPDATE $table SET ".join(', ', @array).
212 " WHERE $primkey=".&dbQuote($primval)
219 # Usage: &dbInsert($table, $primkey, %hash);
221 my ($table, $primkey, %hash, $delay) = @_;
226 &DEBUG("dbI: delay => $delay");
230 foreach (keys %hash) {
232 push(@vals, &dbQuote($hash{$_}));
235 &dbRaw("Insert($table)", "INSERT $p INTO $table (".join(',',@keys).
236 ") VALUES (".join(',',@vals).")"
243 # Usage: &dbReplace($table, %hash);
245 my ($table, %hash) = @_;
248 foreach (keys %hash) {
249 if (s/^-//) { # as is.
251 push(@vals, $hash{'-'.$_});
254 push(@vals, &dbQuote($hash{$_}));
259 &DEBUG("REPLACE INTO $table (".join(',',@keys).
260 ") VALUES (". join(',',@vals). ")" );
263 &dbRaw("Replace($table)", "REPLACE INTO $table (".join(',',@keys).
264 ") VALUES (". join(',',@vals). ")"
271 # Usage: &dbSetRow($table, @values);
273 my ($table, @values, $delay) = @_;
274 my $p = ($delay) ? " DELAYED " : "";
280 return &dbRaw("SetRow", "INSERT $p INTO $table VALUES (".
281 join(",", @values) .")" );
285 # Usage: &dbDel($table, $primkey, $primval, [$key]);
287 my ($table, $primkey, $primval, $key) = @_;
289 &dbRaw("Del", "DELETE FROM $table WHERE $primkey=".
296 # Usage: &dbRaw($prefix,$rawquery);
298 my ($prefix,$query) = @_;
301 if (!($sth = $dbh->prepare($query))) {
302 &ERROR("Raw($prefix): $DBI::errstr");
306 # &DEBUG("query => '$query'.");
309 if (!$sth->execute) {
310 &ERROR("Raw($prefix): => '$query'");
311 # $DBI::errstr is printed as warning automatically.
321 # Usage: &dbRawReturn($rawquery);
326 my $sth = $dbh->prepare($query);
328 &ERROR("RawReturn => '$query'.") unless $sth->execute;
329 while (my @row = $sth->fetchrow_array) {
330 push(@retval, $row[0]);
337 ####################################################################
338 ##### Misc DBI stuff...
342 # Usage: &countKeys($table, [$col]);
344 my ($table, $col) = @_;
347 return (&dbRawReturn("SELECT count($col) FROM $table"))[0];
350 # Usage: &sumKey($table, $col);
352 my ($table, $col) = @_;
354 return (&dbRawReturn("SELECT sum($col) FROM $table"))[0];
358 # Usage: &getKeys($table,$primkey);
360 my ($table,$primkey) = @_;
363 my $query = "SELECT $primkey FROM $table";
364 my $sth = $dbh->prepare($query);
367 &WARN("ERROR: getKeys($query)") unless $sth->execute;
369 while (my @row = $sth->fetchrow_array) {
370 push(@retval, $row[0]);
378 # Usage: &randKey($table, $select);
380 my ($table, $select) = @_;
381 my $rand = int(rand(&countKeys($table) - 1));
382 my $query = "SELECT $select FROM $table LIMIT $rand,1";
384 my $sth = $dbh->prepare($query);
386 &WARN("randKey($query)") unless $sth->execute;
387 my @retval = $sth->fetchrow_array;
394 # Usage: &deleteTable($table);
396 &dbRaw("deleteTable($_[0])", "DELETE FROM $_[0]");
399 # Usage: &searchTable($table, $select, $key, $str);
401 my($table, $select, $key, $str) = @_;
405 # allow two types of wildcards.
406 if ($str =~ /^\^(.*)\$$/) {
407 &DEBUG("searchTable: should use dbGet(), heh.");
410 $str .= "%" if ($str =~ s/^\^//);
411 $str = "%".$str if ($str =~ s/\$$//);
412 $str = "%".$str."%" if ($str eq $origStr); # el-cheapo fix.
416 $str =~ s/\?/\_/g; # '.' should be supported, too.
419 my $query = "SELECT $select FROM $table WHERE $key LIKE ".
421 my $sth = $dbh->prepare($query);
423 &WARN("Search($query)") unless $sth->execute;
425 while (my @row = $sth->fetchrow_array) {
426 push(@results, $row[0]);
433 ####################################################################
434 ##### Factoid related stuff...
438 # Usage: &getFactInfo($faqtoid, type);
440 return &dbGet("factoids", $_[1], "factoid_key='$_[0]'");
444 # Usage: &getFactoid($faqtoid);
446 return &getFactInfo($_[0], "factoid_value");
450 # Usage: &delFactoid($faqtoid);
454 &dbDel("factoids", "factoid_key",$faqtoid);
455 &status("DELETED '$faqtoid'");
461 return unless (&IsParam("SQLDebug"));
463 return if (!fileno SQLDEBUG);
465 print SQLDEBUG $_[0]."\n";
470 my(@path) = (".","..","../..");
475 my $file = "$_/setup/$table.sql";
476 &DEBUG("dbCT: file => $file");
477 next unless ( -f $file );
494 &dbRaw("create($table)", $data);
500 my $database_exists = 0;
501 foreach (&dbRawReturn("SHOW DATABASES")) {
502 $database_exists++ if ($_ eq $param{'DBName'});
505 unless ($database_exists) {
506 &status("Creating database $param{DBName}...");
507 $query = "CREATE DATABASE $param{DBName}";
508 &dbRaw("create(db $param{DBName})", $query);
511 # retrieve a list of db's from the server.
513 foreach ($dbh->func('_ListTables')) {
518 if (!scalar keys %db) {
519 # &status("Creating database $param{'DBName'}...");
520 # $query = "CREATE DATABASE $param{'DBName'}";
521 # &dbRaw("create(db $param{'DBName'})", $query);
524 foreach ("factoids", "freshmeat", "rootwarn", "seen", "stats",
526 next if (exists $db{$_});
527 &status(" creating new table $_...");