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);
50 if (!($sth = $dbh->prepare($query))) {
51 &ERROR("Get: prepare: $DBI::errstr");
57 &ERROR("Get: execute: '$query'");
62 my @retval = $sth->fetchrow_array;
66 if (scalar @retval > 1) {
68 } elsif (scalar @retval == 1) {
76 # Usage: &dbGetCol($table, $primkey, $key, [$type]);
78 my ($table, $primkey, $key, $type) = @_;
79 my $query = "SELECT $primkey,$key FROM $table WHERE $key IS NOT NULL";
82 my $sth = $dbh->prepare($query);
85 &ERROR("GetCol: execute: '$query'");
86 # &ERROR("GetCol => $DBI::errstr");
91 if (defined $type and $type == 1) {
92 while (my @row = $sth->fetchrow_array) {
93 # reverse it to make it easier to count.
94 $retval{$row[1]}{$row[0]} = 1;
97 while (my @row = $sth->fetchrow_array) {
98 $retval{$row[0]} = $row[1];
108 # Usage: &dbGetColInfo($table);
112 my $query = "SHOW COLUMNS from $table";
115 my $sth = $dbh->prepare($query);
117 if (!$sth->execute) {
118 &ERROR("GRI => '$query'");
119 &ERROR("GRI => $DBI::errstr");
125 while (my @row = $sth->fetchrow_array) {
126 push(@cols, $row[0]);
134 # Usage: &dbSet($table, $primhash_ref, $hash_ref);
136 my ($table, $phref, $href) = @_;
137 my $where = join(' AND ', map {
138 $_."=".&dbQuote($phref->{$_})
142 my $result = &dbGet($table, join(',', keys %{$phref}), $where);
145 foreach (keys %{$href}) {
147 push(@vals, &dbQuote($href->{$_}) );
150 if (!@keys or !@vals) {
151 &WARN("dbset: keys or vals is NULL.");
156 if (defined $result) {
158 for(my$i=0; $i<scalar @keys; $i++) {
159 push(@keyval, $keys[$i]."=".$vals[$i] );
162 $query = "UPDATE $table SET ".
163 join(' AND ', @keyval).
166 foreach (keys %{$phref}) {
168 push(@vals, &dbQuote($phref->{$_}) );
171 $query = sprintf("INSERT INTO $table (%s) VALUES (%s)",
172 join(',',@keys), join(',',@vals) );
175 &dbRaw("Set", $query);
181 # Usage: &dbUpdate($table, $primkey, $primval, %hash);
183 my ($table, $primkey, $primval, %hash) = @_;
186 foreach (keys %hash) {
187 push(@array, "$_=".&dbQuote($hash{$_}) );
190 &dbRaw("Update", "UPDATE $table SET ".join(', ', @array).
191 " WHERE $primkey=".&dbQuote($primval)
198 # Usage: &dbInsert($table, $primkey, %hash);
200 my ($table, $primkey, %hash, $delay) = @_;
205 &DEBUG("dbI: delay => $delay");
209 foreach (keys %hash) {
211 push(@vals, &dbQuote($hash{$_}));
214 &dbRaw("Insert($table)", "INSERT $p INTO $table (".join(',',@keys).
215 ") VALUES (".join(',',@vals).")"
222 # Usage: &dbReplace($table, %hash);
224 my ($table, %hash) = @_;
227 foreach (keys %hash) {
228 if (s/^-//) { # as is.
230 push(@vals, $hash{'-'.$_});
233 push(@vals, &dbQuote($hash{$_}));
237 &dbRaw("Replace($table)", "REPLACE INTO $table (".join(',',@keys).
238 ") VALUES (". join(',',@vals). ")"
245 # Usage: &dbSetRow($table, @values);
247 my ($table, @values, $delay) = @_;
248 my $p = ($delay) ? " DELAYED " : "";
254 return &dbRaw("SetRow", "INSERT $p INTO $table VALUES (".
255 join(",", @values) .")" );
259 # Usage: &dbDel($table, $primkey, $primval, [$key]);
261 my ($table, $primkey, $primval, $key) = @_;
263 &dbRaw("Del", "DELETE FROM $table WHERE $primkey=".
270 # Usage: &dbRaw($prefix,$rawquery);
272 my ($prefix,$query) = @_;
275 if (!($sth = $dbh->prepare($query))) {
276 &ERROR("Raw($prefix): $DBI::errstr");
280 # &DEBUG("query => '$query'.");
283 if (!$sth->execute) {
284 &ERROR("Raw($prefix): => '$query'");
285 # $DBI::errstr is printed as warning automatically.
295 # Usage: &dbRawReturn($rawquery);
300 my $sth = $dbh->prepare($query);
302 &ERROR("RawReturn => '$query'.") unless $sth->execute;
303 while (my @row = $sth->fetchrow_array) {
304 push(@retval, $row[0]);
311 ####################################################################
312 ##### Misc DBI stuff...
316 # Usage: &countKeys($table, [$col]);
318 my ($table, $col) = @_;
321 return (&dbRawReturn("SELECT count($col) FROM $table"))[0];
324 # Usage: &sumKey($table, $col);
326 my ($table, $col) = @_;
328 return (&dbRawReturn("SELECT sum($col) FROM $table"))[0];
332 # Usage: &getKeys($table,$primkey);
334 my ($table,$primkey) = @_;
337 my $query = "SELECT $primkey FROM $table";
338 my $sth = $dbh->prepare($query);
341 &WARN("ERROR: getKeys($query)") unless $sth->execute;
343 while (my @row = $sth->fetchrow_array) {
344 push(@retval, $row[0]);
352 # Usage: &randKey($table, $select);
354 my ($table, $select) = @_;
355 my $rand = int(rand(&countKeys($table) - 1));
356 my $query = "SELECT $select FROM $table LIMIT $rand,1";
358 my $sth = $dbh->prepare($query);
360 &WARN("randKey($query)") unless $sth->execute;
361 my @retval = $sth->fetchrow_array;
368 # Usage: &deleteTable($table);
370 &dbRaw("deleteTable($_[0])", "DELETE FROM $_[0]");
373 # Usage: &searchTable($table, $select, $key, $str);
375 my($table, $select, $key, $str) = @_;
379 # allow two types of wildcards.
380 if ($str =~ /^\^(.*)\$$/) {
381 &DEBUG("searchTable: should use dbGet(), heh.");
384 $str .= "%" if ($str =~ s/^\^//);
385 $str = "%".$str if ($str =~ s/\$$//);
386 $str = "%".$str."%" if ($str eq $origStr); # el-cheapo fix.
390 $str =~ s/\?/\_/g; # '.' should be supported, too.
393 my $query = "SELECT $select FROM $table WHERE $key LIKE ".
395 my $sth = $dbh->prepare($query);
397 &WARN("Search($query)") unless $sth->execute;
399 while (my @row = $sth->fetchrow_array) {
400 push(@results, $row[0]);
407 ####################################################################
408 ##### Factoid related stuff...
412 # Usage: &getFactInfo($faqtoid, type);
414 return &dbGet("factoids", $_[1], "factoid_key='$_[0]'");
418 # Usage: &getFactoid($faqtoid);
420 return &getFactInfo($_[0], "factoid_value");
424 # Usage: &delFactoid($faqtoid);
428 &dbDel("factoids", "factoid_key",$faqtoid);
429 &status("DELETED '$faqtoid'");
435 return unless (&IsParam("SQLDebug"));
437 return if (!fileno SQLDEBUG);
439 print SQLDEBUG $_[0]."\n";
444 my(@path) = (".","..","../..");
449 my $file = "$_/setup/$table.sql";
450 &DEBUG("dbCT: file => $file");
451 next unless ( -f $file );
468 &dbRaw("create($table)", $data);
474 # retrieve a list of db's from the server.
476 foreach ($dbh->func('_ListTables')) {
481 if (!scalar keys %db) {
482 &status("Creating database $param{'DBName'}...");
483 $query = "CREATE DATABASE $param{'DBName'}";
484 &dbRaw("create(db $param{'DBName'})", $query);
487 foreach ("factoids", "freshmeat", "rootwarn", "seen", "stats",
489 next if (exists $db{$_});
490 &status(" creating new table $_...");