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'}.");
28 return 0 unless ($dbh);
30 &status("Closed MySQL connection to $param{'SQLHost'}.");
36 # Usage: &dbQuote($str);
38 return $dbh->quote($_[0]);
42 # Usage: &dbGet($table, $primkey, $primval, $select);
44 my ($table, $primkey, $primval, $select) = @_;
45 my $query = "SELECT $select FROM $table WHERE $primkey=".
49 if (!($sth = $dbh->prepare($query))) {
50 &ERROR("Get: $DBI::errstr");
56 &ERROR("Get => '$query'");
57 &ERROR("Get => $DBI::errstr");
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 => '$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, $primkey, $primval, $key, $val);
136 my ($table, $primkey, $primval, $key, $val) = @_;
139 my $result = &dbGet($table,$primkey,$primval,$primkey);
140 if (defined $result) {
141 $query = "UPDATE $table SET $key=".&dbQuote($val).
142 " WHERE $primkey=".&dbQuote($primval);
144 $query = "INSERT INTO $table ($primkey,$key) VALUES (".
145 &dbQuote($primval).",".&dbQuote($val).")";
148 &dbRaw("Set", $query);
154 # Usage: &dbUpdate($table, $primkey, $primval, %hash);
156 my ($table, $primkey, $primval, %hash) = @_;
159 foreach (keys %hash) {
160 push(@array, "$_=".&dbQuote($hash{$_}) );
163 &dbRaw("Update", "UPDATE $table SET ".join(', ', @array).
164 " WHERE $primkey=".&dbQuote($primval)
171 # Usage: &dbInsert($table, $primkey, %hash);
173 my ($table, $primkey, %hash, $delay) = @_;
178 &DEBUG("dbI: delay => $delay");
182 foreach (keys %hash) {
184 push(@vals, &dbQuote($hash{$_}));
187 &dbRaw("Insert($table)", "INSERT $p INTO $table (".join(',',@keys).
188 ") VALUES (".join(',',@vals).")"
195 # Usage: &dbReplace($table, $primkey, $primval, %hash);
197 my ($table, $primkey, $primval, %hash) = @_;
200 foreach (keys %hash) {
202 push(@vals, &dbQuote($hash{$_}));
205 &dbRaw("Replace($table)", "REPLACE INTO $table (".join(',',@keys).
206 ") VALUES (". join(',',@vals). ")"
213 # Usage: &dbSetRow($table, @values);
215 my ($table, @values, $delay) = @_;
216 my $p = ($delay) ? " DELAYED " : "";
222 return &dbRaw("SetRow", "INSERT $p INTO $table VALUES (".
223 join(",", @values) .")" );
227 # Usage: &dbDel($table, $primkey, $primval, [$key]);
229 my ($table, $primkey, $primval, $key) = @_;
231 &dbRaw("Del", "DELETE FROM $table WHERE $primkey=".
238 # Usage: &dbRaw($prefix,$rawquery);
240 my ($prefix,$query) = @_;
243 if (!($sth = $dbh->prepare($query))) {
244 &ERROR("Raw($prefix): $DBI::errstr");
249 if (!$sth->execute) {
250 &ERROR("Raw($prefix): => '$query'");
251 # &ERROR("Raw($prefix): $DBI::errstr");
261 # Usage: &dbRawReturn($rawquery);
266 my $sth = $dbh->prepare($query);
268 &ERROR("RawReturn => '$query'.") unless $sth->execute;
269 while (my @row = $sth->fetchrow_array) {
270 push(@retval, $row[0]);
277 ####################################################################
278 ##### Misc DBI stuff...
282 # Usage: &countKeys($table);
286 return (&dbRawReturn("SELECT count(*) FROM $table"))[0];
290 # Usage: &getKeys($table,$primkey);
292 my ($table,$primkey) = @_;
295 my $query = "SELECT $primkey FROM $table";
296 my $sth = $dbh->prepare($query);
299 &WARN("ERROR: getKeys($query)") unless $sth->execute;
301 while (my @row = $sth->fetchrow_array) {
302 push(@retval, $row[0]);
310 # Usage: &randKey($table, $select);
312 my ($table, $select) = @_;
313 my $rand = int(rand(&countKeys($table) - 1));
314 my $query = "SELECT $select FROM $table LIMIT $rand,1";
316 my $sth = $dbh->prepare($query);
318 &WARN("randKey($query)") unless $sth->execute;
319 my @retval = $sth->fetchrow_array;
326 # Usage: &deleteTable($table);
328 &dbRaw("deleteTable($_[0])", "DELETE FROM $_[0]");
331 # Usage: &searchTable($table, $select, $key, $str);
333 my($table, $select, $key, $str) = @_;
337 # allow two types of wildcards.
338 if ($str =~ /^\^(.*)\$$/) {
339 &DEBUG("searchTable: should use dbGet(), heh.");
342 $str .= "%" if ($str =~ s/^\^//);
343 $str = "%".$str if ($str =~ s/\$$//);
344 $str = "%".$str."%" if ($str eq $origStr); # el-cheapo fix.
348 $str =~ s/\?/\_/g; # '.' should be supported, too.
351 my $query = "SELECT $select FROM $table WHERE $key LIKE ".
353 my $sth = $dbh->prepare($query);
355 &WARN("Search($query)") unless $sth->execute;
357 while (my @row = $sth->fetchrow_array) {
358 push(@results, $row[0]);
365 ####################################################################
366 ##### Factoid related stuff...
370 # Usage: &getFactInfo($faqtoid, type);
372 return &dbGet("factoids", "factoid_key", $_[0], $_[1]);
376 # Usage: &getFactoid($faqtoid);
378 return &getFactInfo($_[0], "factoid_value");
382 # Usage: &delFactoid($faqtoid);
386 &dbDel("factoids", "factoid_key",$faqtoid);
387 &status("DELETED '$faqtoid'");
393 return unless (&IsParam("SQLDebug"));
395 return if (!fileno SQLDEBUG);
397 print SQLDEBUG $_[0]."\n";