#++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
#.IDENTIFICATION  pg95_dbi.pl
#.LANGUAGE	  pgperl95 script
#
#.PURPOSE	  Database Interface to Postgres.
#
#		  &dbi_connect( $host, $port, $database, $user, $pswd );
#		  &dbi_dosql( "... POSTQUEL commands ...");
#		  %row = &dbi_nextrow;
#		  &dbi_disconnect;
#		  &dbi_rowcount( $rows );
#		  &dbi_dateformat( $format );
#
#.AUTHOR	  J. Douglas Dunlop [EOL/ISTS]   <dunlop@eol.ists.ca>
#
#.VERSION	0.0	Adapted from sy_dbi.pl by  Bo Frese Rasmussen
#		1.0	29/03-1995	Creation
#		1.1	15/05-1995	Converted to use postgres95
#		1.2     03/05-2005	Get it works with postgresql 8. and 
#                                       Pg.pm 1.9.0 (oleg@sai.msu.su)
#------------------------------------------------------------------------------	
package WDB_DatabaseInterface;
use Pg;

$PGRES_EMPTY_QUERY    = 0 ; # these are from libpq-fe.h
$PGRES_COMMAND_OK     = 1 ;
$PGRES_TUPLES_OK      = 2 ;
$PGRES_COPY_OUT       = 3 ;
$PGRES_COPY_IN        = 4 ;
$PGRES_BAD_RESPONSE   = 5 ;
$PGRES_NONFATAL_ERROR = 6 ;
$PGRES_FATAL_ERROR    = 7 ;

# Wrappers to work with new Pg.pm 1.9.0 ( Oleg Bartunov, oleg@sai.msu.su)
# 
sub PQerrorMessage {
  return $_[0]->errorMessage;
}

sub PQclear {
  $_[0] = '';
}

sub PQexec {
  return $conn->exec($_[1]);
}

sub PQresultStatus {
  my ($res) = shift;
  return $res->resultStatus;
}

sub PQntuples {
  return $_[0]->ntuples;
}

sub PQnfields {
  return $_[0]->nfields;
}

sub PQfname {
  return $_[0]->fname($_[1]);
}

sub PQgetvalue {
  return $_[0]->getvalue($_[1],$_[2]);
}

sub PQfinish {
  undef $_[0];
}

#++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
#.PURPOSE	Connects to a database.
#
#.REMARKS	This function must be called before any of the other functions
#		in this package.
#		It logs in to the given database server on the given port and 
#               connects to database.
#
#.RETURNS	Dies on error !
#------------------------------------------------------------------------------
sub main'dbi_connect
{
    local(  $host, $port, $database, $user, $pswd ) = @_;
    
    init_handler();      # Initialize a handler to tidy up aborts
    
    $host = $ENV{'PGHOST'} if !$host;
    $port = $ENV{'PGPORT'} if !$port;
##x    $conn = PQsetdb ($host, $port,'','',$database);
    $conn = Pg::connectdb("dbname=$database");

##x    if (PQstatus($conn)) {
    if ($conn->status eq PGRES_CONNECTION_BAD) {
      print "Failed to connect to database '$database' on host '$host' at port '$port'\n";
      die;
    }
}

#++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
#.PURPOSE	Prepares and executes a POSTQUEL statement, with error check.
#
#.REMARKS	Dies on error !
#
#.RETURNS	Return value of dbresults 
#------------------------------------------------------------------------------
sub main'dbi_dosql 
{
    local($query) = @_;

    $curr_row = 0;                           # setup for dbi_nextrow
    
    $result = PQexec($conn, $query);          # the query
    $r_status = PQresultStatus($result);

    if ($r_status != $PGRES_COMMAND_OK && $r_status != $PGRES_TUPLES_OK) {
      $errormsg = PQerrorMessage($conn) if $conn;
      PQclear($result) if $result;
      PQfinish($conn)  if $conn;
      die("$0: PQexec: the query $query produced the error $errormsg\n");
    }
    $num_tuples = PQntuples($result);
    $num_fields = PQnfields($result);
}
#++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
#.PURPOSE       Defines the fieldnames to be returned from dbi_getnextrow
#
#.REMARKS       If this function is defined the query sent to dbi_dosql
#               should not use the
#                 'select <col_name> <col_label>, <col2_name> <col2_label>, ...'
#               notation but rather the simple
#                 'select <col_name>, <col2_name>, ...'
#               notation and then call dbi_fieldnames with the names of the
#               labels to use :
#                 &dbi_fieldnames( '<col_label>', '<col2_label>', ...);
#               &dbi_getnextrow will then return an associative array with the
#               <col_label>'s as key.
#
#.RETURNS       nothing
#------------------------------------------------------------------------------
sub main'dbi_fieldnames
{
    @fields = @_;
}
#++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
#.PURPOSE	Gets the next row from a previous select (dbi_dosql).
#
#.REMARKS	After a dbi_dosql("select ... ") call, this function can be 
#		called repeatetly to retrieve all rows of the query.
#
#		Example :
#			&dbi_dosql("SELECT field FROM class WHERE field > 4");
#			while( %row = &dbi_nextrow  ) {
#			    print $row{'columnname'};
#			    ...
#			}
#
#.RETURNS	An associative array (keyed on the column name) of formatted 
#		data, based on the datatype of the corresponding columns.
#------------------------------------------------------------------------------
sub main'dbi_nextrow
{
#    $num_tuples = PQntuples($result);
#    $num_fields = PQnfields($result);
    if ($curr_row >= $num_tuples) {
       foreach $key (keys %row) {
          delete $row{$key};            # prevents returning previous row 
       }
    }
    else {
       for ($nfield=0 ; $nfield<$num_fields ; $nfield++) {
          $fname = PQfname($result,$nfield);
          $row{$fname} = PQgetvalue($result,$curr_row,$nfield);
       }
    }
    $curr_row += 1;
    return %row;
}

#++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
#.PURPOSE	Get the number of rows matched
#
#.REMARKS	After doing a dbi_dosql then this is the number of rows
#		that matched the selection criteria.
#
#.RETURNS	number of rows matched
#------------------------------------------------------------------------------
sub main'dbi_rowsmatched
{
    return $num_tuples;
} 
#++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
#.PURPOSE	Disconnects the current database connection
#
#.REMARKS	After this function is called there are no current database 
#		connection => no other function from this package can be 
#		called before a new dbi_connect call.
#
#.RETURNS	nothing.
#------------------------------------------------------------------------------
sub main'dbi_disconnect
{
    PQfinish($conn);
}

#++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
#.PURPOSE	Sets the maximim number of rows returned from a query.
#
#.REMARKS	Causes Postgres server to stop processing the query ( select, 
#		insert update, delete) after the specified number of rows 
#		are affected.
#		
#		PORTING NOTE: This function is added for efficiency only. 
#		When used with WDB it can safely be ignored ( leave an
#		empty function body : {} )
#
#.RETURNS	nothing.
#------------------------------------------------------------------------------
sub main'dbi_rowcount
{
    # not an option in postgres95
}

#++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
#.PURPOSE	Sets the default output format for dates.
#
#.REMARKS	Sets the order of the date parts month/day/year for entering
#		datetime data. Valid arguments are mdy, dmy, ymd, ydm, myd, dym.
#
#.RETURNS	nothing.
#------------------------------------------------------------------------------
sub main'dbi_dateformat
{
	# fixed as mdy in postgres95
}

#++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
#.PURPOSE	Error handler code.
#
#.REMARKS	Close database if HUP, INT, or QUIT signal is recieved.
#
#.RETURNS	exits after doing PQfinish
#-----------------------------------------------------------------------------
sub init_handler {
    $SIG{'HUP'} = 'handler';
    $SIG{'INT'} = 'handler';
    $SIG{'QUIT'} = 'handler';
}

sub handler {  # 1st argument is signal name
    local($sig) = @_;
    print "Caught a SIG$sig--shutting down connection to Postgres95.\n";
    PQclear($result) if $result;
    PQfinish($conn) if $conn;
    exit(0);
}
#------------------------------------------------------------------------------
1;