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 to $param{'SQLHost'}");
18 &ERROR("cannot connect to $param{'SQLHost'}.");
19 &ERROR("pgSQL: ".$dbh->errstr);
30 return 0 unless ($dbh);
32 &status("Closed pgSQL connection to $param{'SQLHost'}.");
39 # Usage: &dbQuote($str);
41 return $dbh->quote($_[0]);
49 # Usage: &dbGet($table, $select, $where);
51 my ($table, $select, $where) = @_;
52 my $query = "SELECT $select FROM $table";
53 $query .= " WHERE $where" if ($where);
55 if (!defined $select) {
56 &WARN("dbGet: select == NULL. table => $table");
61 if (!($sth = $dbh->prepare($query))) {
62 &ERROR("Get: prepare: $DBI::errstr");
68 &ERROR("Get: execute: '$query'");
73 my @retval = $sth->fetchrow_array;
77 if (scalar @retval > 1) {
79 } elsif (scalar @retval == 1) {
87 # Usage: &dbGetCol($table, $select, $where, [$type]);
89 my ($table, $select, $where, $type) = @_;
90 my $query = "SELECT $select FROM $table";
91 $query .= " WHERE ".$where if ($where);
94 my $sth = $dbh->prepare($query);
97 &ERROR("GetCol: execute: '$query'");
102 if (defined $type and $type == 2) {
103 &DEBUG("dbgetcol: type 2!");
104 while (my @row = $sth->fetchrow_array) {
105 $retval{$row[0]} = join(':', $row[1..$#row]);
107 &DEBUG("dbgetcol: count => ".scalar(keys %retval) );
109 } elsif (defined $type and $type == 1) {
110 while (my @row = $sth->fetchrow_array) {
111 # reverse it to make it easier to count.
112 if (scalar @row == 2) {
113 $retval{$row[1]}{$row[0]} = 1;
114 } elsif (scalar @row == 3) {
115 $retval{$row[1]}{$row[0]} = 1;
117 # what to do if there's only one or more than 3?
120 while (my @row = $sth->fetchrow_array) {
121 $retval{$row[0]} = $row[1];
131 # Usage: &dbGetColNiceHash($table, $select, $where);
132 sub dbGetColNiceHash {
133 my ($table, $select, $where) = @_;
135 my $query = "SELECT $select FROM $table";
136 $query .= " WHERE ".$where if ($where);
139 &DEBUG("dbGetColNiceHash: query => '$query'.");
141 my $sth = $dbh->prepare($query);
143 if (!$sth->execute) {
144 &ERROR("GetColNiceHash: execute: '$query'");
145 # &ERROR("GetCol => $DBI::errstr");
150 # todo: get column names, do $hash{$primkey}{blah} = ...
151 while (my @row = $sth->fetchrow_array) {
152 # todo: reverse it to make it easier to count.
161 # Usage: &dbGetColInfo($table);
165 # my $query = "SELECT * FROM $table LIMIT 1;";
166 my $query = "SHOW COLUMNS from $table";
169 my $sth = $dbh->prepare($query);
171 if (!$sth->execute) {
172 &ERROR("GRI => '$query'");
173 &ERROR("GRI => $DBI::errstr");
179 %retval=%{$sth->fetchrow_hashref()};
184 while (my @row = $sth->fetchrow_array) {
185 push(@cols, $row[0]);
193 # Usage: &dbSet($table, $primhash_ref, $hash_ref);
194 # Note: dbSet does dbQuote.
196 my ($table, $phref, $href) = @_;
197 my $where = join(' AND ', map {
198 $_."=".&dbQuote($phref->{$_})
202 my $result = &dbGet($table, join(',', keys %{$phref}), $where);
205 foreach (keys %{$href}) {
207 push(@vals, &dbQuote($href->{$_}) );
210 if (!@keys or !@vals) {
211 &WARN("dbset: keys or vals is NULL.");
216 if (defined $result) {
218 for(my$i=0; $i<scalar @keys; $i++) {
219 push(@keyval, $keys[$i]."=".$vals[$i] );
222 $query = "UPDATE $table SET ".
223 join(' AND ', @keyval).
226 foreach (keys %{$phref}) {
228 push(@vals, &dbQuote($phref->{$_}) );
231 $query = sprintf("INSERT INTO $table (%s) VALUES (%s)",
232 join(',',@keys), join(',',@vals) );
235 &dbRaw("Set", $query);
241 # Usage: &dbUpdate($table, $primkey, $primval, %hash);
243 my ($table, $primkey, $primval, %hash) = @_;
246 foreach (keys %hash) {
247 push(@array, "$_=".&dbQuote($hash{$_}) );
250 &dbRaw("Update", "UPDATE $table SET ".join(', ', @array).
251 " WHERE $primkey=".&dbQuote($primval)
258 # Usage: &dbInsert($table, $primkey, $primval, %hash);
260 my ($table, $primkey, $primval, %hash, $delay) = @_;
265 &DEBUG("dbI: delay => $delay");
269 foreach (keys %hash) {
271 push(@vals, &dbQuote($hash{$_}));
274 &dbRaw("Insert($table)", "INSERT $p INTO $table (".join(',',@keys).
275 ") VALUES (".join(',',@vals).")"
282 # Usage: &dbReplace($table, %hash);
283 # Note: dbReplace does optional dbQuote.
285 my ($table, %hash) = @_;
287 my $iquery = "INSERT INTO $table ";
288 my $uquery = "UPDATE $table SET ";
290 foreach (keys %hash) {
291 if (s/^-//) { # as is.
293 push(@vals, $hash{'-'.$_});
296 push(@vals, &dbQuote($hash{$_}));
298 $uquery .= "$keys[-1] = $vals[-1], ";
301 $iquery .= "(". join(',',@keys) .") VALUES (". join(',',@vals) .");";
303 &DEBUG($query) if (0);
305 if(!&dbRaw("Replace($table)", $iquery)) {
306 &dbRaw("Replace($table)", $uquery);
313 # Usage: &dbSetRow($table, $vref, $delay);
314 # Note: dbSetRow does dbQuote.
316 my ($table, $vref, $delay) = @_;
317 my $p = ($delay) ? " DELAYED " : "";
319 # see 'perldoc perlreftut'
321 foreach (@{ $vref }) {
322 push(@values, &dbQuote($_) );
325 if (!scalar @values) {
326 &WARN("dbSetRow: values array == NULL.");
330 return &dbRaw("SetRow", "INSERT $p INTO $table VALUES (".
331 join(",", @values) .")" );
335 # Usage: &dbDel($table, $primkey, $primval, [$key]);
336 # Note: dbDel does dbQuote
338 my ($table, $primkey, $primval, $key) = @_;
340 &dbRaw("Del", "DELETE FROM $table WHERE $primkey=".
347 # Usage: &dbRaw($prefix,$rawquery);
349 my ($prefix,$query) = @_;
352 if (!($sth = $dbh->prepare($query))) {
353 &ERROR("Raw($prefix): $DBI::errstr");
358 if (!$sth->execute) {
359 &ERROR("Raw($prefix): => '$query'");
369 # Usage: &dbRawReturn($rawquery);
374 my $sth = $dbh->prepare($query);
376 &ERROR("RawReturn => '$query'.") unless $sth->execute;
377 while (my @row = $sth->fetchrow_array) {
378 push(@retval, $row[0]);
385 ####################################################################
386 ##### Misc DBI stuff...
390 # Usage: &countKeys($table, [$col]);
392 my ($table, $col) = @_;
395 return (&dbRawReturn("SELECT count($col) FROM $table"))[0];
398 # Usage: &sumKey($table, $col);
400 my ($table, $col) = @_;
402 return (&dbRawReturn("SELECT sum($col) FROM $table"))[0];
406 # Usage: &randKey($table, $select);
408 my ($table, $select) = @_;
409 my $rand = int(rand(&countKeys($table) - 1));
410 my $query = "SELECT $select FROM $table LIMIT $rand,1";
412 my $sth = $dbh->prepare($query);
414 &WARN("randKey($query)") unless $sth->execute;
415 my @retval = $sth->fetchrow_array;
422 # Usage: &deleteTable($table);
424 &dbRaw("deleteTable($_[0])", "DELETE FROM $_[0]");
428 # Usage: &searchTable($table, $select, $key, $str);
429 # Note: searchTable does dbQuote.
431 my($table, $select, $key, $str) = @_;
435 # allow two types of wildcards.
436 if ($str =~ /^\^(.*)\$$/) {
437 &DEBUG("searchTable: should use dbGet(), heh.");
440 $str .= "%" if ($str =~ s/^\^//);
441 $str = "%".$str if ($str =~ s/\$$//);
442 $str = "%".$str."%" if ($str eq $origStr); # el-cheapo fix.
446 $str =~ s/\?/\_/g; # '.' should be supported, too.
449 my $query = "SELECT $select FROM $table WHERE $key LIKE ".
451 my $sth = $dbh->prepare($query);
453 if (!$sth->execute) {
454 &WARN("Search($query)");
458 while (my @row = $sth->fetchrow_array) {
459 push(@results, $row[0]);
466 ####################################################################
467 ##### Factoid related stuff...
471 # Usage: &getFactInfo($faqtoid, $type);
472 # Note: getFactInfo does dbQuote
474 return &dbGet("factoids", $_[1], "factoid_key=".&dbQuote($_[0]) );
478 # Usage: &getFactoid($faqtoid);
480 return &getFactInfo($_[0], "factoid_value");
484 # Usage: &delFactoid($faqtoid);
488 &dbDel("factoids", "factoid_key",$faqtoid);
489 &status("DELETED '$faqtoid'");
497 &FIXME("pgsql: checkTables(@_);");