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, $primkey, $key, [$type]);
83 my ($table, $primkey, $key, $type) = @_;
84 my $query = "SELECT $primkey,$key FROM $table WHERE $key IS NOT NULL";
87 my $sth = $dbh->prepare($query);
90 &ERROR("GetCol: execute: '$query'");
91 # &ERROR("GetCol => $DBI::errstr");
96 if (defined $type and $type == 1) {
97 while (my @row = $sth->fetchrow_array) {
98 # reverse it to make it easier to count.
99 $retval{$row[1]}{$row[0]} = 1;
102 while (my @row = $sth->fetchrow_array) {
103 $retval{$row[0]} = $row[1];
113 # Usage: &dbGetColInfo($table);
117 my $query = "SHOW COLUMNS from $table";
120 my $sth = $dbh->prepare($query);
122 if (!$sth->execute) {
123 &ERROR("GRI => '$query'");
124 &ERROR("GRI => $DBI::errstr");
130 while (my @row = $sth->fetchrow_array) {
131 push(@cols, $row[0]);
139 # Usage: &dbSet($table, $primhash_ref, $hash_ref);
141 my ($table, $phref, $href) = @_;
142 my $where = join(' AND ', map {
143 $_."=".&dbQuote($phref->{$_})
147 my $result = &dbGet($table, join(',', keys %{$phref}), $where);
150 foreach (keys %{$href}) {
152 push(@vals, &dbQuote($href->{$_}) );
155 if (!@keys or !@vals) {
156 &WARN("dbset: keys or vals is NULL.");
161 if (defined $result) {
163 for(my$i=0; $i<scalar @keys; $i++) {
164 push(@keyval, $keys[$i]."=".$vals[$i] );
167 $query = "UPDATE $table SET ".
168 join(' AND ', @keyval).
171 foreach (keys %{$phref}) {
173 push(@vals, &dbQuote($phref->{$_}) );
176 $query = sprintf("INSERT INTO $table (%s) VALUES (%s)",
177 join(',',@keys), join(',',@vals) );
180 &dbRaw("Set", $query);
186 # Usage: &dbUpdate($table, $primkey, $primval, %hash);
188 my ($table, $primkey, $primval, %hash) = @_;
191 foreach (keys %hash) {
192 push(@array, "$_=".&dbQuote($hash{$_}) );
195 &dbRaw("Update", "UPDATE $table SET ".join(', ', @array).
196 " WHERE $primkey=".&dbQuote($primval)
203 # Usage: &dbInsert($table, $primkey, %hash);
205 my ($table, $primkey, %hash, $delay) = @_;
210 &DEBUG("dbI: delay => $delay");
214 foreach (keys %hash) {
216 push(@vals, &dbQuote($hash{$_}));
219 &dbRaw("Insert($table)", "INSERT $p INTO $table (".join(',',@keys).
220 ") VALUES (".join(',',@vals).")"
227 # Usage: &dbReplace($table, %hash);
229 my ($table, %hash) = @_;
232 foreach (keys %hash) {
233 if (s/^-//) { # as is.
235 push(@vals, $hash{'-'.$_});
238 push(@vals, &dbQuote($hash{$_}));
243 &DEBUG("REPLACE INTO $table (".join(',',@keys).
244 ") VALUES (". join(',',@vals). ")" );
247 &dbRaw("Replace($table)", "REPLACE INTO $table (".join(',',@keys).
248 ") VALUES (". join(',',@vals). ")"
255 # Usage: &dbSetRow($table, @values);
257 my ($table, @values, $delay) = @_;
258 my $p = ($delay) ? " DELAYED " : "";
264 return &dbRaw("SetRow", "INSERT $p INTO $table VALUES (".
265 join(",", @values) .")" );
269 # Usage: &dbDel($table, $primkey, $primval, [$key]);
271 my ($table, $primkey, $primval, $key) = @_;
273 &dbRaw("Del", "DELETE FROM $table WHERE $primkey=".
280 # Usage: &dbRaw($prefix,$rawquery);
282 my ($prefix,$query) = @_;
285 if (!($sth = $dbh->prepare($query))) {
286 &ERROR("Raw($prefix): $DBI::errstr");
290 # &DEBUG("query => '$query'.");
293 if (!$sth->execute) {
294 &ERROR("Raw($prefix): => '$query'");
295 # $DBI::errstr is printed as warning automatically.
305 # Usage: &dbRawReturn($rawquery);
310 my $sth = $dbh->prepare($query);
312 &ERROR("RawReturn => '$query'.") unless $sth->execute;
313 while (my @row = $sth->fetchrow_array) {
314 push(@retval, $row[0]);
321 ####################################################################
322 ##### Misc DBI stuff...
326 # Usage: &countKeys($table, [$col]);
328 my ($table, $col) = @_;
331 return (&dbRawReturn("SELECT count($col) FROM $table"))[0];
334 # Usage: &sumKey($table, $col);
336 my ($table, $col) = @_;
338 return (&dbRawReturn("SELECT sum($col) FROM $table"))[0];
342 # Usage: &getKeys($table,$primkey);
344 my ($table,$primkey) = @_;
347 my $query = "SELECT $primkey FROM $table";
348 my $sth = $dbh->prepare($query);
351 &WARN("ERROR: getKeys($query)") unless $sth->execute;
353 while (my @row = $sth->fetchrow_array) {
354 push(@retval, $row[0]);
362 # Usage: &randKey($table, $select);
364 my ($table, $select) = @_;
365 my $rand = int(rand(&countKeys($table) - 1));
366 my $query = "SELECT $select FROM $table LIMIT $rand,1";
368 my $sth = $dbh->prepare($query);
370 &WARN("randKey($query)") unless $sth->execute;
371 my @retval = $sth->fetchrow_array;
378 # Usage: &deleteTable($table);
380 &dbRaw("deleteTable($_[0])", "DELETE FROM $_[0]");
383 # Usage: &searchTable($table, $select, $key, $str);
385 my($table, $select, $key, $str) = @_;
389 # allow two types of wildcards.
390 if ($str =~ /^\^(.*)\$$/) {
391 &DEBUG("searchTable: should use dbGet(), heh.");
394 $str .= "%" if ($str =~ s/^\^//);
395 $str = "%".$str if ($str =~ s/\$$//);
396 $str = "%".$str."%" if ($str eq $origStr); # el-cheapo fix.
400 $str =~ s/\?/\_/g; # '.' should be supported, too.
403 my $query = "SELECT $select FROM $table WHERE $key LIKE ".
405 my $sth = $dbh->prepare($query);
407 &WARN("Search($query)") unless $sth->execute;
409 while (my @row = $sth->fetchrow_array) {
410 push(@results, $row[0]);
417 ####################################################################
418 ##### Factoid related stuff...
422 # Usage: &getFactInfo($faqtoid, type);
424 return &dbGet("factoids", $_[1], "factoid_key='$_[0]'");
428 # Usage: &getFactoid($faqtoid);
430 return &getFactInfo($_[0], "factoid_value");
434 # Usage: &delFactoid($faqtoid);
438 &dbDel("factoids", "factoid_key",$faqtoid);
439 &status("DELETED '$faqtoid'");
445 return unless (&IsParam("SQLDebug"));
447 return if (!fileno SQLDEBUG);
449 print SQLDEBUG $_[0]."\n";
454 my(@path) = (".","..","../..");
459 my $file = "$_/setup/$table.sql";
460 &DEBUG("dbCT: file => $file");
461 next unless ( -f $file );
478 &dbRaw("create($table)", $data);
484 # retrieve a list of db's from the server.
486 foreach ($dbh->func('_ListTables')) {
491 if (!scalar keys %db) {
492 &status("Creating database $param{'DBName'}...");
493 $query = "CREATE DATABASE $param{'DBName'}";
494 &dbRaw("create(db $param{'DBName'})", $query);
497 foreach ("factoids", "freshmeat", "rootwarn", "seen", "stats",
499 next if (exists $db{$_});
500 &status(" creating new table $_...");