#!/usr/bin/perl
#
# Yousef Rabah - started April 7th, 2004
# 
# DB.pm - script for accessing ADB database
# some reference and strucutre from my work @ webdb
#
# Using DBI
#
# Packageing it to db, will be used in people.pm and people.pl
#
# Functions are :
#
# -- connectDB()
#   Its job is to connect to the postgreSQL database called ADB
#
# -- disconnectDB($)
#   Its job is to disconnect from ADB. Uses disconnect function
#
# -- SQLselect($)
#   Its job is to select rows from ADB and return them. It would 
#   prepare SQL statement and run or execute it. It will then fetch
#   the rows and put them in array of rows. The return is of the 
#   status and the array of rows. This will be mainly used i
#   in people.pm and people.pl.
#
# -- SQLinsert($) 
#   Its job is to insert rows back into the database ADB. It would 
#   prepare SQL statement and run or execute it. It will then 
#   disconnect the database and then the return is the status 0 if
#   it didnt put anything. 
#
#################################################################3

package db;


require Exporter;
@ISA = qw(Exporter);

@EXPORT = qw ( 
	       SQLselect
	       SQLinsert
	       );

use strict;
use DBI;

my $people = "people";


#  connectDB()
#   Its job is to connect to the postgreSQL database called ADB
sub connectDB(){
    
    my $dbh;
    my $dbname = "ADB";
    my $dbuser = "";
    my $dbpass = "";
    my @what;
    my @location;
    my $i = 0;
    #my $data_file = "/tmp/$ENV{'REMOTE_ADDR'}.dat";
    
    #Now connecting to the Database (DBI)
    $dbh = DBI->connect("dbi:Pg:dbname=$dbname") ||
	die "DB.pm::connectDB - Can't connect, $DBI::errstr";
    
    return ($dbh);
    
}

#  disconnectDB($)
#   Its job is to disconnect from ADB. Uses disconnect function
sub disconnectDB($){
    my $dbh = $_[0];

    $dbh->disconnect();

}


#  SQLselect($)
#   Its job is to select rows from ADB and return them. It would 
#   prepare SQL statement and run or execute it. It will then fetch
#   the rows and put them in array of rows. The return is of the 
#   status and the array of rows. This will be mainly used i
#   in people.pm and people.pl.
sub SQLselect($) {
    my $sql = $_[0];
    my $dbh;
    my $sth;
    my $status;
    my $count = 0;
    my @row;
    my @rows;
    my $arr_ref;

    $dbh = connectDB();    
    $sth = $dbh->prepare($sql) ||
	die "DB.pm::SQLselect - prepare error: ($sql) $DBI::errstr";

    $status = $sth->execute ||
          die "DB.pm::SQLselect execute error: ($sql) status=$status $DBI::errstr";

    while (@row = $sth->fetchrow_array) {
	$rows[$count] = [ @row ];
	$count++; 
    }


    disconnectDB($dbh);
    return($count, @rows);
}

#  SQLinsert($) 
#   Its job is to insert rows back into the database ADB. It would 
#   prepare SQL statement and run or execute it. It will then 
#   disconnect the database and then the return is the status 0 if
#   it didnt put anything. 
sub SQLinsert($) {
    my $sql = $_[0];
    my $dbh;
    my $sth;
    my $status;

  $dbh = connectDB();
    
    $sth = $dbh->prepare($sql) ||
	die "db.pm::SQLselect - prepare error: ($sql) $DBI::errstr";

    $status = $sth->execute ||
          die "DB.pm::SQLselect execute error: ($sql) status=$status $DBI::errstr";

    disconnectDB($dbh);  
    return ($status);

}