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, $primkey, $primval, $select);
45 my ($table, $primkey, $primval, $select) = @_;
46 my $query = "SELECT $select FROM $table WHERE $primkey=".
50 if (!($sth = $dbh->prepare($query))) {
51 &ERROR("Get: $DBI::errstr");
57 &ERROR("Get => '$query'");
58 &ERROR("Get => $DBI::errstr");
63 my @retval = $sth->fetchrow_array;
67 if (scalar @retval > 1) {
69 } elsif (scalar @retval == 1) {
77 # Usage: &dbGetCol($table, $primkey, $key, [$type]);
79 my ($table, $primkey, $key, $type) = @_;
80 my $query = "SELECT $primkey,$key FROM $table WHERE $key IS NOT NULL";
83 my $sth = $dbh->prepare($query);
86 &ERROR("GetCol => '$query'");
87 &ERROR("GetCol => $DBI::errstr");
92 if (defined $type and $type == 1) {
93 while (my @row = $sth->fetchrow_array) {
94 # reverse it to make it easier to count.
95 $retval{$row[1]}{$row[0]} = 1;
98 while (my @row = $sth->fetchrow_array) {
99 $retval{$row[0]} = $row[1];
109 # Usage: &dbGetColInfo($table);
113 my $query = "SHOW COLUMNS from $table";
116 my $sth = $dbh->prepare($query);
118 if (!$sth->execute) {
119 &ERROR("GRI => '$query'");
120 &ERROR("GRI => $DBI::errstr");
126 while (my @row = $sth->fetchrow_array) {
127 push(@cols, $row[0]);
135 # Usage: &dbSet($table, $primkey, $primval, $key, $val);
137 my ($table, $primkey, $primval, $key, $val) = @_;
140 my $result = &dbGet($table,$primkey,$primval,$primkey);
141 if (defined $result) {
142 $query = "UPDATE $table SET $key=".&dbQuote($val).
143 " WHERE $primkey=".&dbQuote($primval);
145 $query = "INSERT INTO $table ($primkey,$key) VALUES (".
146 &dbQuote($primval).",".&dbQuote($val).")";
149 &dbRaw("Set", $query);
155 # Usage: &dbUpdate($table, $primkey, $primval, %hash);
157 my ($table, $primkey, $primval, %hash) = @_;
160 foreach (keys %hash) {
161 push(@array, "$_=".&dbQuote($hash{$_}) );
164 &dbRaw("Update", "UPDATE $table SET ".join(', ', @array).
165 " WHERE $primkey=".&dbQuote($primval)
172 # Usage: &dbInsert($table, $primkey, %hash);
174 my ($table, $primkey, %hash, $delay) = @_;
179 &DEBUG("dbI: delay => $delay");
183 foreach (keys %hash) {
185 push(@vals, &dbQuote($hash{$_}));
188 &dbRaw("Insert($table)", "INSERT $p INTO $table (".join(',',@keys).
189 ") VALUES (".join(',',@vals).")"
196 # Usage: &dbReplace($table, $primkey, $primval, %hash);
198 my ($table, $primkey, $primval, %hash) = @_;
201 foreach (keys %hash) {
203 push(@vals, &dbQuote($hash{$_}));
206 &dbRaw("Replace($table)", "REPLACE INTO $table (".join(',',@keys).
207 ") VALUES (". join(',',@vals). ")"
214 # Usage: &dbSetRow($table, @values);
216 my ($table, @values, $delay) = @_;
217 my $p = ($delay) ? " DELAYED " : "";
223 return &dbRaw("SetRow", "INSERT $p INTO $table VALUES (".
224 join(",", @values) .")" );
228 # Usage: &dbDel($table, $primkey, $primval, [$key]);
230 my ($table, $primkey, $primval, $key) = @_;
232 &dbRaw("Del", "DELETE FROM $table WHERE $primkey=".
239 # Usage: &dbRaw($prefix,$rawquery);
241 my ($prefix,$query) = @_;
244 if (!($sth = $dbh->prepare($query))) {
245 &ERROR("Raw($prefix): $DBI::errstr");
250 if (!$sth->execute) {
251 &ERROR("Raw($prefix): => '$query'");
252 # &ERROR("Raw($prefix): $DBI::errstr");
262 # Usage: &dbRawReturn($rawquery);
267 my $sth = $dbh->prepare($query);
269 &ERROR("RawReturn => '$query'.") unless $sth->execute;
270 while (my @row = $sth->fetchrow_array) {
271 push(@retval, $row[0]);
278 ####################################################################
279 ##### Misc DBI stuff...
283 # Usage: &countKeys($table, [$col]);
285 my ($table, $col) = @_;
288 return (&dbRawReturn("SELECT count($col) FROM $table"))[0];
291 # Usage: &sumKey($table, $col);
293 my ($table, $col) = @_;
295 return (&dbRawReturn("SELECT sum($col) FROM $table"))[0];
299 # Usage: &getKeys($table,$primkey);
301 my ($table,$primkey) = @_;
304 my $query = "SELECT $primkey FROM $table";
305 my $sth = $dbh->prepare($query);
308 &WARN("ERROR: getKeys($query)") unless $sth->execute;
310 while (my @row = $sth->fetchrow_array) {
311 push(@retval, $row[0]);
319 # Usage: &randKey($table, $select);
321 my ($table, $select) = @_;
322 my $rand = int(rand(&countKeys($table) - 1));
323 my $query = "SELECT $select FROM $table LIMIT $rand,1";
325 my $sth = $dbh->prepare($query);
327 &WARN("randKey($query)") unless $sth->execute;
328 my @retval = $sth->fetchrow_array;
335 # Usage: &deleteTable($table);
337 &dbRaw("deleteTable($_[0])", "DELETE FROM $_[0]");
340 # Usage: &searchTable($table, $select, $key, $str);
342 my($table, $select, $key, $str) = @_;
346 # allow two types of wildcards.
347 if ($str =~ /^\^(.*)\$$/) {
348 &DEBUG("searchTable: should use dbGet(), heh.");
351 $str .= "%" if ($str =~ s/^\^//);
352 $str = "%".$str if ($str =~ s/\$$//);
353 $str = "%".$str."%" if ($str eq $origStr); # el-cheapo fix.
357 $str =~ s/\?/\_/g; # '.' should be supported, too.
360 my $query = "SELECT $select FROM $table WHERE $key LIKE ".
362 my $sth = $dbh->prepare($query);
364 &WARN("Search($query)") unless $sth->execute;
366 while (my @row = $sth->fetchrow_array) {
367 push(@results, $row[0]);
374 ####################################################################
375 ##### Factoid related stuff...
379 # Usage: &getFactInfo($faqtoid, type);
381 return &dbGet("factoids", "factoid_key", $_[0], $_[1]);
385 # Usage: &getFactoid($faqtoid);
387 return &getFactInfo($_[0], "factoid_value");
391 # Usage: &delFactoid($faqtoid);
395 &dbDel("factoids", "factoid_key",$faqtoid);
396 &status("DELETED '$faqtoid'");
402 return unless (&IsParam("SQLDebug"));
404 return if (!fileno SQLDEBUG);
406 print SQLDEBUG $_[0]."\n";
411 my(@path) = (".","..","../..");
416 my $file = "$_/setup/$table.sql";
417 &DEBUG("dbCT: file => $file");
418 next unless ( -f $file );
432 &dbRaw("create($table)", $data);
438 # retrieve a list of db's from the server.
440 foreach ($dbh->func('_ListTables')) {
445 if (!scalar keys %db) {
446 &status("Creating database $param{'DBName'}...");
447 $query = "CREATE DATABASE $param{'DBName'}";
448 &dbRaw("create(db $param{'DBName'})", $query);
451 foreach ("factoids", "freshmeat", "karma", "rootwarn", "seen",
453 next if (exists $db{$_});
454 &status(" creating new table $_...");