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'}.");
29 &WARN("closeDB: connection already closed?");
33 &status("Closed MySQL connection to $param{'SQLHost'}.");
39 # Usage: &dbQuote($str);
41 return $dbh->quote($_[0]);
45 # Usage: &dbGet($table, $primkey, $primval, $select);
47 my ($table, $primkey, $primval, $select) = @_;
48 my $query = "SELECT $select FROM $table WHERE $primkey=".
52 if (!($sth = $dbh->prepare($query))) {
53 &ERROR("Get: $DBI::errstr");
59 &ERROR("Get => '$query'");
60 &ERROR("Get => $DBI::errstr");
61 &SQLDebug($DBI::errstr);
65 my @retval = $sth->fetchrow_array;
69 if (scalar @retval > 1) {
71 } elsif (scalar @retval == 1) {
79 # Usage: &dbGetCol($table, $primkey, $key, [$type]);
81 my ($table, $primkey, $key, $type) = @_;
82 my $query = "SELECT $primkey,$key FROM $table WHERE $key IS NOT NULL";
85 my $sth = $dbh->prepare($query);
88 &ERROR("GetCol => '$query'");
89 &SQLDebug($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: &dbGetRowInfo($table);
111 &DEBUG("STUB: dbGetRowInfo().");
115 # Usage: &dbSet($table, $primkey, $primval, $key, $val);
117 my ($table, $primkey, $primval, $key, $val) = @_;
120 my $result = &dbGet($table,$primkey,$primval,$primkey);
121 if (defined $result) {
122 $query = "UPDATE $table SET $key=".&dbQuote($val).
123 " WHERE $primkey=".&dbQuote($primval);
125 $query = "INSERT INTO $table ($primkey,$key) VALUES (".
126 &dbQuote($primval).",".&dbQuote($val).")";
129 &dbRaw("Set", $query);
135 # Usage: &dbUpdate($table, $primkey, $primval, %hash);
137 my ($table, $primkey, $primval, %hash) = @_;
140 foreach (keys %hash) {
141 push(@array, "$_=".&dbQuote($hash{$_}) );
144 &dbRaw("Update", "UPDATE $table SET ".join(', ', @array).
145 " WHERE $primkey=".&dbQuote($primval)
152 # Usage: &dbInsert($table, $primkey, %hash);
154 my ($table, $primkey, %hash) = @_;
157 foreach (keys %hash) {
159 push(@vals, &dbQuote($hash{$_}));
162 &dbRaw("Insert($table)", "INSERT INTO $table (".join(',',@keys).
163 ") VALUES (".join(',',@vals).")"
170 # Usage: &dbSetRow($table, @values);
172 my ($table, @values) = @_;
178 return &dbRaw("SetRow", "INSERT INTO $table VALUES (".
179 join(",", @values) .")" );
183 # Usage: &dbDel($table, $primkey, $primval, [$key]);
185 my ($table, $primkey, $primval, $key) = @_;
187 &dbRaw("Del", "DELETE FROM $table WHERE $primkey=".
194 # Usage: &dbRaw($prefix,$rawquery);
196 my ($prefix,$query) = @_;
199 if (!($sth = $dbh->prepare($query))) {
200 &ERROR("Raw($prefix): $DBI::errstr");
205 if (!$sth->execute) {
206 &ERROR("Raw($prefix): => '$query'");
207 &ERROR("Raw($prefix): $DBI::errstr");
208 &SQLDebug($DBI::errstr);
217 # Usage: &dbRawReturn($rawquery);
222 my $sth = $dbh->prepare($query);
224 &ERROR("RawReturn => '$query'.") unless $sth->execute;
225 while (my @row = $sth->fetchrow_array) {
226 push(@retval, $row[0]);
233 ####################################################################
234 ##### Misc DBI stuff...
238 # Usage: &countKeys($table);
242 return (&dbRawReturn("SELECT count(*) FROM $table"))[0];
246 # Usage: &getKeys($table,$primkey);
248 my ($table,$primkey) = @_;
251 my $query = "SELECT $primkey FROM $table";
252 my $sth = $dbh->prepare($query);
255 &WARN("ERROR: getKeys($query)") unless $sth->execute;
257 while (my @row = $sth->fetchrow_array) {
258 push(@retval, $row[0]);
266 # Usage: &randKey($table, $select);
268 my ($table, $select) = @_;
269 my $rand = int(rand(&countKeys($table) - 1));
270 my $query = "SELECT $select FROM $table LIMIT $rand,1";
272 my $sth = $dbh->prepare($query);
274 &WARN("randKey($query)") unless $sth->execute;
275 my @retval = $sth->fetchrow_array;
282 # Usage: &deleteTable($table);
284 &dbRaw("deleteTable($_[0])", "DELETE FROM $_[0]");
287 # Usage: &searchTable($table, $select, $key, $str);
289 my($table, $select, $key, $str) = @_;
293 # allow two types of wildcards.
294 if ($str =~ /^\^(.*)\$$/) {
295 &DEBUG("searchTable: should use dbGet(), heh.");
298 $str .= "%" if ($str =~ s/^\^//);
299 $str = "%".$str if ($str =~ s/\$$//);
300 $str = "%".$str."%" if ($str eq $origStr); # el-cheapo fix.
304 $str =~ s/\?/\_/g; # '.' should be supported, too.
307 my $query = "SELECT $select FROM $table WHERE $key LIKE ".
309 my $sth = $dbh->prepare($query);
311 &WARN("Search($query)") unless $sth->execute;
313 while (my @row = $sth->fetchrow_array) {
314 push(@results, $row[0]);
321 ####################################################################
322 ##### Factoid related stuff...
326 # Usage: &getFactInfo($faqtoid, type);
328 return &dbGet("factoids", "factoid_key", $_[0], $_[1]);
332 # Usage: &getFactoid($faqtoid);
334 return &getFactInfo($_[0], "factoid_value");
338 # Usage: &delFactoid($faqtoid);
342 &dbDel("factoids", "factoid_key",$faqtoid);
343 &status("DELETED $faqtoid");
349 return unless (&IsParam("SQLDebug"));
351 print SQLDEBUG $_[0]."\n";