#!/usr/bin/perl
#
# Module for connecting to postgreSQL database using Pg
# use:
# require "connectPg.pl";
# 
use DBD::Pg;
#
# $DBMSaddress, corpus name, user and password are defined in Links.pl!!!
# You can define all variables $DBMSaddress $DBMSname, $DBMSuser, $DBMSpassword
# before you 'require "connectPg.pl";'
#
($pghost, $pgport, $pgoptions, $pgtty, $dbname, $user, $password) =
        ("$DBMSaddress", '5432', '', '', $DBMSname, $DBMSuser, $DBMSpassword);  
$conn;

# Connecting to current user at a local "postmaster".
# That is, if you define a password, use the Login option, else, use the connectdb option.
unless($password)
{
	$conn = DBI->connect("dbi:Pg:dbname=$dbname;host=$DBMSaddress");
}
# Connecting to another user at another platform (fill in pghost and the password before calling connectPg.pl)
else
{
	$conn = DBI->connect(
	"dbi:Pg:dbname=$dbname;host=$DBMSaddress;port=$pgport;options='$pgoptions';tty='$pgtty'", 
	$user, $password);
};

if(!$conn || $conn->err)
{
    print STDERR << "ENDOFDEFAULT";
Connection Error:
DBI->connect("dbi:Pg:dbname=$dbname;host=$DBMSaddress;port=$pgport;options='$pgoptions';tty='$pgtty'", $user, <password>)
    
Default Values:
      parameter  environment variable  hard coded default
      --------------------------------------------------
      dbname     PGDATABASE            current userid
      host       PGHOST                localhost
      port       PGPORT                5432
      options    PGOPTIONS             ""
      tty        PGTTY                 ""
      username   PGUSER                current userid
      password   PGPASSWORD            ""
ENDOFDEFAULT
    die "connect: ", $conn->errorstr, " $!\n" if $conn;
};

#############################################################################
#
# EXAMPLE OF HOW TO USE Pg (assuming you 'require "ConnectPg.pl";' first)
#
#############################################################################
#
# Example function to extract values from records from a given table.
# All matching records are pushed onto a single flat list.
# NOTE: No security or sanity checks whatsoever are performed. DO NOT use
# this function unqualified in a HTTP/CGI environment. Unchecked CGI variables
# will allow internet-users to wreck havoc on your database/system
#
# $TableName	The name of the table, e.g., 'subjects'
# $IdentifyingWhere, 	The Where clause, e.g., "speaker~'F' and age > 30"
# $AttributeName1, ..., $AttributeNameN		The columnnames, e.g., ('height', 'weight', 'fl')
#
my $result;
sub getSQLrecordvalues	# ($TableName, $IdentifyingWhere, $AttributeName1, ..., $AttributeNameN) -> @AllAttributeValues
{
	my $TableName = shift || return ();
	my $IdentifyingWhere = shift || "";
	my @AttributeNames = @_;
	@AttributeNames = ('*') unless @AttributeNames;
	#
	# Construct SELECT statement
	my $SELECT = "SELECT \n\t";	# Select only distinct entries
	$SELECT .= join(', ', @AttributeNames);	# The atribute names to get
	$SELECT .= "\nFROM \n\t$TableName";	# The tablename (this could be table NAMES)
	if($IdentifyingWhere =~ /\S/)
	{
		$SELECT .= "\nWHERE\n\t";		# Conditions to selct the records
		$SELECT .= "$IdentifyingWhere\n";	# Which record. Only ONE record is used (the LAST)
	};
	# Get the record
	$result = $conn->exec("$SELECT;");

	# Signal an ERROR
	print STDERR $result->resultStatus, " ", $conn->errorMessage, 
		" executing: $SELECT\n" 
		if($result->resultStatus == PGRES_BAD_RESPONSE);

	# Extract the record
	my @ResultList = ();
	my @row;
	while(@row = $result->fetchrow)
	{
		push(@ResultList, @row);
	};
	
	# Ready
	return @ResultList;
};

# Get the names of all the attributes from the last getSQLrecordvalues SELECT
sub getSQLattributeNames	# () -> @AllAttributeNames
{
	my $field_number;
	my @field_names = ();
	for($field_number=0; $field_number < $result->nfields; ++$field_number)
	{
		push(@field_names, $result->fname($field_number));
	};
	
	# Ready
	return @field_names;
};


#
###############################################################################
#
# Copyright R.J.J.H. van Son © 2000, 2001
#
# Author Rob van Son
# Institute of Phonetic Sciences & ACLC
# University of Amsterdam
# Herengracht 338
# NL-1016CG Amsterdam, The Netherlands
# Email: Rob.van.Son@hum.uva.nl
#        ifa@hum.uva.nl
# WWW  : http://www.fon.hum.uva.nl/rob/
# mail:  Institute of Phonetic Sciences
#        University of Amsterdam
#        Herengracht 338
#        NL-1016CG Amsterdam
#        The Netherlands
#        tel +31 205252183
#        fax +31 205252197
#
# License for use and disclaimers
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation; either version 2
# of the License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
#
#
#######################################################
#
# Make require happy
1;