2 # db_mysql.pl: MySQL database frontend.
3 # Author: xk <xk@leguin.openprojects.net>
4 # Version: v0.2c (19991224)
10 if (&IsParam("useStrict")) { use strict; }
13 my $dsn = "DBI:mysql:$param{'DBName'}:$param{'SQLHost'}";
14 $dbh = DBI->connect($dsn, $param{'SQLUser'}, $param{'SQLPass'});
17 &status("Opened MySQL connection to $param{'SQLHost'}");
19 &ERROR("cannot connect to $param{'SQLHost'}.");
28 &WARN("closeDB: connection already closed?");
32 &status("Closed MySQL connection to $param{'SQLHost'}.");
38 # Usage: &dbQuote($str);
40 return $dbh->quote($_[0]);
44 # Usage: &dbGet($table, $primkey, $primval, $select);
46 my ($table, $primkey, $primval, $select) = @_;
47 my $query = "SELECT $select FROM $table WHERE $primkey=".
51 if (!($sth = $dbh->prepare($query))) {
52 &ERROR("Get: $DBI::errstr");
57 &ERROR("Get => '$query'");
58 &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);
83 &ERROR("GetCol => '$query'") unless $sth->execute;
85 if (defined $type and $type == 1) {
86 while (my @row = $sth->fetchrow_array) {
87 # reverse it to make it easier to count.
88 $retval{$row[1]}{$row[0]} = 1;
91 while (my @row = $sth->fetchrow_array) {
92 $retval{$row[0]} = $row[1];
102 # Usage: &dbGetRowInfo($table);
104 &DEBUG("STUB: dbGetRowInfo().");
108 # Usage: &dbSet($table, $primkey, $primval, $key, $val);
110 my ($table, $primkey, $primval, $key, $val) = @_;
113 my $result = &dbGet($table,$primkey,$primval,$primkey);
114 if (defined $result) {
115 $query = "UPDATE $table SET $key=".&dbQuote($val).
116 " WHERE $primkey=".&dbQuote($primval);
118 $query = "INSERT INTO $table ($primkey,$key) VALUES (".
119 &dbQuote($primval).",".&dbQuote($val).")";
122 &dbRaw("Set", $query);
128 # Usage: &dbUpdate($table, $primkey, $primval, %hash);
130 my ($table, $primkey, $primval, %hash) = @_;
133 foreach (keys %hash) {
134 push(@array, "$_=".&dbQuote($hash{$_}) );
137 &dbRaw("Update", "UPDATE $table SET ".join(', ', @array).
138 " WHERE $primkey=".&dbQuote($primval)
145 # Usage: &dbInsert($table, $primkey, %hash);
147 my ($table, $primkey, %hash) = @_;
150 foreach (keys %hash) {
152 push(@vals, &dbQuote($hash{$_}));
155 &dbRaw("Insert($table)", "INSERT INTO $table (".join(',',@keys).
156 ") VALUES (".join(',',@vals).")"
163 # Usage: &dbSetRow($table, @values);
165 my ($table, @values) = @_;
171 return &dbRaw("SetRow", "INSERT INTO $table VALUES (".
172 join(",", @values) .")" );
176 # Usage: &dbDel($table, $primkey, $primval, [$key]);
178 my ($table, $primkey, $primval, $key) = @_;
180 &dbRaw("Del", "DELETE FROM $table WHERE $primkey=".
187 # Usage: &dbRaw($prefix,$rawquery);
189 my ($prefix,$query) = @_;
192 if (!($sth = $dbh->prepare($query))) {
193 &ERROR("Raw($prefix): $DBI::errstr");
197 if (!$sth->execute) {
198 &ERROR("Raw($prefix): => '$query'");
199 &ERROR("Raw($prefix): $DBI::errstr");
208 # Usage: &dbRawReturn($rawquery);
213 my $sth = $dbh->prepare($query);
214 &ERROR("RawReturn => '$query'.") unless $sth->execute;
215 while (my @row = $sth->fetchrow_array) {
216 push(@retval, $row[0]);
223 ####################################################################
224 ##### Misc DBI stuff...
228 # Usage: &countKeys($table);
232 return (&dbRawReturn("SELECT count(*) FROM $table"))[0];
236 # Usage: &getKeys($table,$primkey);
238 my ($table,$primkey) = @_;
241 my $query = "SELECT $primkey FROM $table";
242 my $sth = $dbh->prepare($query);
245 while (my @row = $sth->fetchrow_array) {
246 push(@retval, $row[0]);
254 # Usage: &randKey($table, $select);
256 my ($table, $select) = @_;
257 my $rand = int(rand(&countKeys($table) - 1));
258 my $query = "SELECT $select FROM $table LIMIT $rand,1";
260 my $sth = $dbh->prepare($query);
262 my @retval = $sth->fetchrow_array;
269 # Usage: &deleteTable($table);
271 &dbRaw("deleteTable($_[0])", "DELETE FROM $_[0]");
274 # Usage: &searchTable($table, $select, $key, $str);
276 my($table, $select, $key, $str) = @_;
280 # allow two types of wildcards.
281 if ($str =~ /^\^(.*)\$$/) {
282 &DEBUG("searchTable: should use dbGet(), heh.");
285 $str .= "%" if ($str =~ s/^\^//);
286 $str = "%".$str if ($str =~ s/\$$//);
287 $str = "%".$str."%" if ($str eq $origStr); # el-cheapo fix.
291 $str =~ s/\?/\_/g; # '.' should be supported, too.
294 my $query = "SELECT $select FROM $table WHERE $key LIKE ".
296 my $sth = $dbh->prepare($query);
299 while (my @row = $sth->fetchrow_array) {
300 push(@results, $row[0]);
307 ####################################################################
308 ##### Factoid related stuff...
312 # Usage: &getFactInfo($faqtoid, type);
314 return &dbGet("factoids", "factoid_key", $_[0], $_[1]);
318 # Usage: &getFactoid($faqtoid);
320 return &getFactInfo($_[0], "factoid_value");
324 # Usage: &delFactoid($faqtoid);
328 &dbDel("factoids", "factoid_key",$faqtoid);
329 &status("DELETED $faqtoid");