2 # db_pgsql.pl: PostgreSQL database frontend.
4 # Version: v0.2 (20010908)
8 if (&IsParam("useStrict")) { use strict; }
11 my $connectstr="dbi:Pg:dbname=$param{DBName};";
12 $connectstr.=";host=$param{SQLHost}" if(defined $param{'SQLHost'});
13 $dbh = DBI->connect($connectstr, $param{'SQLUser'}, $param{'SQLPass'});
16 &status("Opened pgSQL connection".
17 (exists $param{'SQLHost'} ? " to ".$param{'SQLHost'} : ""));
19 &ERROR("cannot connect to $param{'SQLHost'}.");
20 &ERROR("pgSQL: ".$dbh->errstr);
31 return 0 unless ($dbh);
33 &status("Closed pgSQL connection.");
40 # Usage: &dbQuote($str);
42 return $dbh->quote($_[0]);
50 # Usage: &dbGet($table, $select, $where);
52 my ($table, $select, $where) = @_;
53 my $query = "SELECT $select FROM $table";
54 $query .= " WHERE $where" if ($where);
56 if (!defined $select) {
57 &WARN("dbGet: select == NULL. table => $table");
62 if (!($sth = $dbh->prepare($query))) {
63 &ERROR("Get: prepare: $DBI::errstr");
69 &ERROR("Get: execute: '$query'");
74 my @retval = $sth->fetchrow_array;
78 if (scalar @retval > 1) {
80 } elsif (scalar @retval == 1) {
88 # Usage: &dbGetCol($table, $select, $where, [$type]);
90 my ($table, $select, $where, $type) = @_;
91 my $query = "SELECT $select FROM $table";
92 $query .= " WHERE ".$where if ($where);
95 my $sth = $dbh->prepare($query);
98 &ERROR("GetCol: execute: '$query'");
103 if (defined $type and $type == 2) {
104 &DEBUG("dbgetcol: type 2!");
105 while (my @row = $sth->fetchrow_array) {
106 $retval{$row[0]} = join(':', $row[1..$#row]);
108 &DEBUG("dbgetcol: count => ".scalar(keys %retval) );
110 } elsif (defined $type and $type == 1) {
111 while (my @row = $sth->fetchrow_array) {
112 # reverse it to make it easier to count.
113 if (scalar @row == 2) {
114 $retval{$row[1]}{$row[0]} = 1;
115 } elsif (scalar @row == 3) {
116 $retval{$row[1]}{$row[0]} = 1;
118 # what to do if there's only one or more than 3?
121 while (my @row = $sth->fetchrow_array) {
122 $retval{$row[0]} = $row[1];
132 # Usage: &dbGetColNiceHash($table, $select, $where);
133 sub dbGetColNiceHash {
134 my ($table, $select, $where) = @_;
136 my $query = "SELECT $select FROM $table";
137 $query .= " WHERE ".$where if ($where);
140 &DEBUG("dbGetColNiceHash: query => '$query'.");
142 my $sth = $dbh->prepare($query);
144 if (!$sth->execute) {
145 &ERROR("GetColNiceHash: execute: '$query'");
146 # &ERROR("GetCol => $DBI::errstr");
151 %retval = %{ $sth->fetchrow_hashref() };
158 # Usage: &dbGetColInfo($table);
162 my $query = "SELECT * FROM $table LIMIT 1;";
163 # my $query = "SHOW COLUMNS from $table";
166 my $sth = $dbh->prepare($query);
168 if (!$sth->execute) {
169 &ERROR("GRI => '$query'");
170 &ERROR("GRI => $DBI::errstr");
175 %retval = %{ $sth->fetchrow_hashref() };
182 # Usage: &dbSet($table, $primhash_ref, $hash_ref);
183 # Note: dbSet does dbQuote.
185 my ($table, $phref, $href) = @_;
186 my $where = join(' AND ', map {
187 $_."=".&dbQuote($phref->{$_})
191 my $result = &dbGet($table, join(',', keys %{$phref}), $where);
194 foreach (keys %{$href}) {
196 push(@vals, &dbQuote($href->{$_}) );
199 if (!@keys or !@vals) {
200 &WARN("dbset: keys or vals is NULL.");
205 if (defined $result) {
207 for(my$i=0; $i<scalar @keys; $i++) {
208 push(@keyval, $keys[$i]."=".$vals[$i] );
211 $query = "UPDATE $table SET ".
212 join(' AND ', @keyval).
215 foreach (keys %{$phref}) {
217 push(@vals, &dbQuote($phref->{$_}) );
220 $query = sprintf("INSERT INTO $table (%s) VALUES (%s)",
221 join(',',@keys), join(',',@vals) );
224 &dbRaw("Set", $query);
230 # Usage: &dbUpdate($table, $primkey, $primval, %hash);
232 my ($table, $primkey, $primval, %hash) = @_;
235 foreach (keys %hash) {
236 push(@array, "$_=".&dbQuote($hash{$_}) );
239 &dbRaw("Update", "UPDATE $table SET ".join(', ', @array).
240 " WHERE $primkey=".&dbQuote($primval)
247 # Usage: &dbInsert($table, $primkey, $primval, %hash);
249 my ($table, $primkey, $primval, %hash) = @_;
252 foreach (keys %hash) {
254 push(@vals, &dbQuote($hash{$_}));
257 &dbRaw("Insert($table)", "INSERT INTO $table (".join(',',@keys).
258 ") VALUES (".join(',',@vals).")"
265 # Usage: &dbReplace($table, $key, %hash);
266 # Note: dbReplace does optional dbQuote.
268 my ($table, $key, %hash) = @_;
270 my $where = "WHERE $key=".&dbQuote($hash{$key});
271 my $squery = "SELECT $key FROM $table $where;";
272 my $iquery = "INSERT INTO $table ";
273 my $uquery = "UPDATE $table SET ";
275 foreach (keys %hash) {
276 if (s/^-//) { # as is.
278 push(@vals, $hash{'-'.$_});
281 push(@vals, &dbQuote($hash{$_}));
283 $uquery .= "$keys[-1] = $vals[-1], ";
285 $uquery = ~s/, $/ $where;/;
286 $iquery .= "(". join(',',@keys) .") VALUES (". join(',',@vals) .");";
288 &DEBUG($squery) if (0);
290 if(&dbRawReturn($squery)) {
291 &dbRaw("Replace($table)", $uquery);
293 &dbRaw("Replace($table)", $iquery);
300 ##### MADE REDUNDANT BY LEAR.
301 # Usage: &dbSetRow($table, $vref, $delay);
302 # Note: dbSetRow does dbQuote.
304 my ($table, $vref, $delay) = @_;
305 my $p = ($delay) ? " DELAYED " : "";
307 # see 'perldoc perlreftut'
309 foreach (@{ $vref }) {
310 push(@values, &dbQuote($_) );
313 if (!scalar @values) {
314 &WARN("dbSetRow: values array == NULL.");
318 return &dbRaw("SetRow", "INSERT $p INTO $table VALUES (".
319 join(",", @values) .")" );
323 # Usage: &dbDel($table, $primkey, $primval, [$key]);
324 # Note: dbDel does dbQuote
326 my ($table, $primkey, $primval, $key) = @_;
328 &dbRaw("Del", "DELETE FROM $table WHERE $primkey=".
335 # Usage: &dbRaw($prefix,$rawquery);
337 my ($prefix,$query) = @_;
340 if (!($sth = $dbh->prepare($query))) {
341 &ERROR("Raw($prefix): $DBI::errstr");
346 if (!$sth->execute) {
347 &ERROR("Raw($prefix): => '$query'");
357 # Usage: &dbRawReturn($rawquery);
362 my $sth = $dbh->prepare($query);
364 &ERROR("RawReturn => '$query'.") unless $sth->execute;
365 while (my @row = $sth->fetchrow_array) {
366 push(@retval, $row[0]);
373 ####################################################################
374 ##### Misc DBI stuff...
378 # Usage: &countKeys($table, [$col]);
380 my ($table, $col) = @_;
383 return (&dbRawReturn("SELECT count($col) FROM $table"))[0];
386 # Usage: &sumKey($table, $col);
388 my ($table, $col) = @_;
390 return (&dbRawReturn("SELECT sum($col) FROM $table"))[0];
394 # Usage: &randKey($table, $select);
396 my ($table, $select) = @_;
397 my $rand = int(rand(&countKeys($table) - 1));
398 my $query = "SELECT $select FROM $table LIMIT 1,$rand";
400 my $sth = $dbh->prepare($query);
402 &WARN("randKey($query)") unless $sth->execute;
403 my @retval = $sth->fetchrow_array;
410 # Usage: &deleteTable($table);
412 &dbRaw("deleteTable($_[0])", "DELETE FROM $_[0]");
416 # Usage: &searchTable($table, $select, $key, $str);
417 # Note: searchTable does dbQuote.
419 my($table, $select, $key, $str) = @_;
423 # allow two types of wildcards.
424 if ($str =~ /^\^(.*)\$$/) {
425 &DEBUG("searchTable: should use dbGet(), heh.");
428 $str .= "%" if ($str =~ s/^\^//);
429 $str = "%".$str if ($str =~ s/\$$//);
430 $str = "%".$str."%" if ($str eq $origStr); # el-cheapo fix.
434 $str =~ s/\?/\_/g; # '.' should be supported, too.
437 my $query = "SELECT $select FROM $table WHERE $key LIKE ".
439 my $sth = $dbh->prepare($query);
441 if (!$sth->execute) {
442 &WARN("Search($query)");
446 while (my @row = $sth->fetchrow_array) {
447 push(@results, $row[0]);
454 ####################################################################
455 ##### Factoid related stuff...
459 # Usage: &getFactInfo($faqtoid, $type);
460 # Note: getFactInfo does dbQuote
462 return &dbGet("factoids", $_[1], "factoid_key=".&dbQuote($_[0]) );
466 # Usage: &getFactoid($faqtoid);
468 return &getFactInfo($_[0], "factoid_value");
472 # Usage: &delFactoid($faqtoid);
476 &dbDel("factoids", "factoid_key",$faqtoid);
477 &status("DELETED '$faqtoid'");
485 &FIXME("pgsql: checkTables(@_);");