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 my $sth = $dbh->prepare($query);
91 &ERROR("GetCol: execute: '$query'");
92 # &ERROR("GetCol => $DBI::errstr");
97 if (defined $type and $type == 2) {
98 &DEBUG("dbgetcol: type 2!");
99 while (my @row = $sth->fetchrow_array) {
100 $retval{$row[0]} = join(':', $row[1..$#row]);
102 &DEBUG("dbgetcol: count => ".scalar(keys %retval) );
104 } elsif (defined $type and $type == 1) {
105 while (my @row = $sth->fetchrow_array) {
106 # reverse it to make it easier to count.
107 if (scalar @row == 2) {
108 $retval{$row[1]}{$row[0]} = 1;
109 } elsif (scalar @row == 3) {
110 $retval{$row[1]}{$row[0]} = 1;
112 # what to do if there's only one or more than 3?
116 while (my @row = $sth->fetchrow_array) {
117 $retval{$row[0]} = $row[1];
127 # Usage: &dbGetColInfo($table);
131 my $query = "SHOW COLUMNS from $table";
134 my $sth = $dbh->prepare($query);
136 if (!$sth->execute) {
137 &ERROR("GRI => '$query'");
138 &ERROR("GRI => $DBI::errstr");
144 while (my @row = $sth->fetchrow_array) {
145 push(@cols, $row[0]);
153 # Usage: &dbSet($table, $primhash_ref, $hash_ref);
155 my ($table, $phref, $href) = @_;
156 my $where = join(' AND ', map {
157 $_."=".&dbQuote($phref->{$_})
161 my $result = &dbGet($table, join(',', keys %{$phref}), $where);
164 foreach (keys %{$href}) {
166 push(@vals, &dbQuote($href->{$_}) );
169 if (!@keys or !@vals) {
170 &WARN("dbset: keys or vals is NULL.");
175 if (defined $result) {
177 for(my$i=0; $i<scalar @keys; $i++) {
178 push(@keyval, $keys[$i]."=".$vals[$i] );
181 $query = "UPDATE $table SET ".
182 join(' AND ', @keyval).
185 foreach (keys %{$phref}) {
187 push(@vals, &dbQuote($phref->{$_}) );
190 $query = sprintf("INSERT INTO $table (%s) VALUES (%s)",
191 join(',',@keys), join(',',@vals) );
194 &dbRaw("Set", $query);
200 # Usage: &dbUpdate($table, $primkey, $primval, %hash);
202 my ($table, $primkey, $primval, %hash) = @_;
205 foreach (keys %hash) {
206 push(@array, "$_=".&dbQuote($hash{$_}) );
209 &dbRaw("Update", "UPDATE $table SET ".join(', ', @array).
210 " WHERE $primkey=".&dbQuote($primval)
217 # Usage: &dbInsert($table, $primkey, %hash);
219 my ($table, $primkey, %hash, $delay) = @_;
224 &DEBUG("dbI: delay => $delay");
228 foreach (keys %hash) {
230 push(@vals, &dbQuote($hash{$_}));
233 &dbRaw("Insert($table)", "INSERT $p INTO $table (".join(',',@keys).
234 ") VALUES (".join(',',@vals).")"
241 # Usage: &dbReplace($table, %hash);
243 my ($table, %hash) = @_;
246 foreach (keys %hash) {
247 if (s/^-//) { # as is.
249 push(@vals, $hash{'-'.$_});
252 push(@vals, &dbQuote($hash{$_}));
257 &DEBUG("REPLACE INTO $table (".join(',',@keys).
258 ") VALUES (". join(',',@vals). ")" );
261 &dbRaw("Replace($table)", "REPLACE INTO $table (".join(',',@keys).
262 ") VALUES (". join(',',@vals). ")"
269 # Usage: &dbSetRow($table, @values);
271 my ($table, @values, $delay) = @_;
272 my $p = ($delay) ? " DELAYED " : "";
278 return &dbRaw("SetRow", "INSERT $p INTO $table VALUES (".
279 join(",", @values) .")" );
283 # Usage: &dbDel($table, $primkey, $primval, [$key]);
285 my ($table, $primkey, $primval, $key) = @_;
287 &dbRaw("Del", "DELETE FROM $table WHERE $primkey=".
294 # Usage: &dbRaw($prefix,$rawquery);
296 my ($prefix,$query) = @_;
299 if (!($sth = $dbh->prepare($query))) {
300 &ERROR("Raw($prefix): $DBI::errstr");
304 # &DEBUG("query => '$query'.");
307 if (!$sth->execute) {
308 &ERROR("Raw($prefix): => '$query'");
309 # $DBI::errstr is printed as warning automatically.
319 # Usage: &dbRawReturn($rawquery);
324 my $sth = $dbh->prepare($query);
326 &ERROR("RawReturn => '$query'.") unless $sth->execute;
327 while (my @row = $sth->fetchrow_array) {
328 push(@retval, $row[0]);
335 ####################################################################
336 ##### Misc DBI stuff...
340 # Usage: &countKeys($table, [$col]);
342 my ($table, $col) = @_;
345 return (&dbRawReturn("SELECT count($col) FROM $table"))[0];
348 # Usage: &sumKey($table, $col);
350 my ($table, $col) = @_;
352 return (&dbRawReturn("SELECT sum($col) FROM $table"))[0];
356 # Usage: &getKeys($table,$primkey);
358 my ($table,$primkey) = @_;
361 my $query = "SELECT $primkey FROM $table";
362 my $sth = $dbh->prepare($query);
365 &WARN("ERROR: getKeys($query)") unless $sth->execute;
367 while (my @row = $sth->fetchrow_array) {
368 push(@retval, $row[0]);
376 # Usage: &randKey($table, $select);
378 my ($table, $select) = @_;
379 my $rand = int(rand(&countKeys($table) - 1));
380 my $query = "SELECT $select FROM $table LIMIT $rand,1";
382 my $sth = $dbh->prepare($query);
384 &WARN("randKey($query)") unless $sth->execute;
385 my @retval = $sth->fetchrow_array;
392 # Usage: &deleteTable($table);
394 &dbRaw("deleteTable($_[0])", "DELETE FROM $_[0]");
397 # Usage: &searchTable($table, $select, $key, $str);
399 my($table, $select, $key, $str) = @_;
403 # allow two types of wildcards.
404 if ($str =~ /^\^(.*)\$$/) {
405 &DEBUG("searchTable: should use dbGet(), heh.");
408 $str .= "%" if ($str =~ s/^\^//);
409 $str = "%".$str if ($str =~ s/\$$//);
410 $str = "%".$str."%" if ($str eq $origStr); # el-cheapo fix.
414 $str =~ s/\?/\_/g; # '.' should be supported, too.
417 my $query = "SELECT $select FROM $table WHERE $key LIKE ".
419 my $sth = $dbh->prepare($query);
421 &WARN("Search($query)") unless $sth->execute;
423 while (my @row = $sth->fetchrow_array) {
424 push(@results, $row[0]);
431 ####################################################################
432 ##### Factoid related stuff...
436 # Usage: &getFactInfo($faqtoid, type);
438 return &dbGet("factoids", $_[1], "factoid_key='$_[0]'");
442 # Usage: &getFactoid($faqtoid);
444 return &getFactInfo($_[0], "factoid_value");
448 # Usage: &delFactoid($faqtoid);
452 &dbDel("factoids", "factoid_key",$faqtoid);
453 &status("DELETED '$faqtoid'");
459 return unless (&IsParam("SQLDebug"));
461 return if (!fileno SQLDEBUG);
463 print SQLDEBUG $_[0]."\n";
468 my(@path) = (".","..","../..");
473 my $file = "$_/setup/$table.sql";
474 &DEBUG("dbCT: file => $file");
475 next unless ( -f $file );
492 &dbRaw("create($table)", $data);
498 # retrieve a list of db's from the server.
500 foreach ($dbh->func('_ListTables')) {
505 if (!scalar keys %db) {
506 &status("Creating database $param{'DBName'}...");
507 $query = "CREATE DATABASE $param{'DBName'}";
508 &dbRaw("create(db $param{'DBName'})", $query);
511 foreach ("factoids", "freshmeat", "rootwarn", "seen", "stats",
513 next if (exists $db{$_});
514 &status(" creating new table $_...");