package SQLShell;

use strict;

use Carp;

use DBI;

# For some advanced uses you may need PostgreSQL type values:

use DBD::Pg qw(:pg_types);



use base qw[ISQLDriver];

use utf8;



BEGIN {

	our $VERSION   = '0.04';

	our $AUTHORITY = 'freelance:SLANT';

}



my $Count = 0;



sub new {

  my $proto = shift;                 # ????????? ??? ?????? ??? ????????? ?? ??????

  my $class = ref($proto) || $proto; # ???? ?????????, ?? ????? ?? ???? ??? ??????

  $Count++;							 # ?????????? ?????????? ???????????? ??????????? ??????	

  my $self  = {};

  $self->{DB_TRACE} =[];			 # ????? ??????? ?????????? ? ?? log_level,filename

  $self->{OPTIONS} = [];			 # ?????? ?? ?????????? ?????? ??????

  $self->{DBH}=undef;				 # Pointer to database handler  	

  bless($self, $class);              # ?????? ????? ??????? bless

  return $self;

}



sub just_do { 

	my ($self,$func,$param)=@_;

    if (defined $self->{DBH}){

    	my $p =undef;

    	foreach my $item(@$param){

     		#$item ="\'$item\'";

     		$item =$self->{DBH}->quote($item);     		

     	}

     	$p= join(",",@$param);

     	my $q="SELECT $func($p)";

     	

     	#$self->{DBH}->begin_work();

    	my $sth = $self->{DBH}->prepare($q);

        $sth->execute() || croak("SQLShell.pm:: Can't execute sql query. Error $self->{DBH}->errstr\n"); 

        #$self->{DBH}->commit();

        return $sth->fetchall_arrayref();# Returns pointer to array of result

    }else{

    	croak("SQLShell.pm:: Can't execute sql query. Database not connected \n");

    }

}



sub debug {

 my $self = shift;

  if (@_) { 

  	@{ $self->{DB_TRACE} } = @_;

  	#TODO Add validation log_level must from 1 to 7

  	if ($self->{DB_TRACE}->[0] < 0){

  		$self->{DB_TRACE}->[0]=0;

  	}

	if ($self->{DB_TRACE}->[0] > 7){

  		$self->{DB_TRACE}->[0]=7;

  	}

	if ((scalar(@{$self->{DB_TRACE}})== 2)&& defined $self->{DBH}){

  		$self->{DBH}->trace($self->{DB_TRACE}->[0],$self->{DB_TRACE}->[1]);

  	}	

	#TODO check file name or not what's the question ?

  }

  return $self->{DB_TRACE};

}



sub disconnect {

	my ($self) = @_;

	$self->{DBH}->disconnect() if defined($self->{DBH});

}

sub connect{

	my $self = shift;

	if (scalar(@{$self->{OPTIONS}})== 7){

		my ($dbname,$username,$password,$dbhost,$dbport,$dboptions,$dbtty)=@{$self->{OPTIONS}};

		#TODO ???????? ????? ??????? ??? - ??????? ??????? ? ???? ?? ????????? postgres ????? ??????? ????? ????

		$self->{DBH} = DBI->connect("dbi:Pg:dbname=$dbname;host=$dbhost;port=$dbport;options=$dboptions",$username,$password,

									{PrintError => 0, RaiseError => 1,AutoCommit => 1});

		#Test connection

		croak("SQLShell.pm::connect Erorr $DBI::errstr\n") unless (defined $self->{DBH});

  		#????? ???? ???? ? ??????? DD.MM.YYYY, ? ?? ??? ?? ????????? ? ??????? ISO ??? ??? ? ????? ???? ??????? ?????.

		$self->{DBH}->do("SET DATESTYLE TO GERMAN");

	}else{

		croak("SQLShell.pm::connect Can't connect to BD. becouse arument's list not full");

	}

}



sub select {

    my($self, $fields,$table, $cond) = @_;

    #TODO add check $fields,$table,$cond

    if (defined $self->{DBH}){

    	#$self->{DBH}->begin_work();

    	my $q="SELECT $fields FROM $table WHERE $cond";

    	my $sth = $self->{DBH}->prepare($q);

        my $rv = $sth->execute();

        #$self->{DBH}->commit();

         if (!defined $rv) {

         	my $e="SQLShell.pm:: When execute query $q:".$self->{DBH}->errstr."\n";

            croak ($e);

        }else{

            return $sth->fetchall_arrayref();# Returns pointer to array of result

        }

    }else{

    	croak("SQLShell.pm:: Can't execute sql query. Database not connected \n");

    }

}

#TODO need function for packet's data insert, update(use ??? and prepare)

sub _doexec{# Private function !!!

	my ($self,$query)=@_;

    if (defined $self->{DBH}){

    	#$self->{DBH}->begin_work();

    	my $rv = $self->{DBH}->do($query);

        #$self->{DBH}->commit();

         if (!defined $rv) {

         	my $e="SQLShell.pm:: When execute query $query\n:".$self->{DBH}->errstr."\n";

            croak ($e);

        }else{

            return $rv;# Returns count records

        }

    }else{

    	croak("SQLShell.pm:: Can't execute sql query. Database not connected \n");

    }

}



sub delete{

	my($self,$table,$cond) = @_;

	#TODO add check $table,$cond

	my $q="DELETE FROM $table WHERE $cond";

	return $self->_doexec($q);

}

sub update{

	my($self,$table,$field,$value,$cond) = @_;

	#TODO add check $table, $fields,$values,$cond

	my $q="UPDATE $table SET $field=$self->{DBH}->quote($value) WHERE $cond";

    return $self->_doexec($q);

}



sub insert {

    my($self, $table, $fields,$values) = @_;

    #TODO add check $table, $fields,$values

    my $q="INSERT INTO $table ($fields) VALUES (E$values)";

    return $self->_doexec($q);

}



sub options {

  my $self = shift;# 

  if (@_) { @{ $self->{OPTIONS} } = @_ }

  return $self->{OPTIONS};

}



sub DESTROY {

  $Count--;

}

sub total {

  return $Count;

}



END{

	#Dirty hack, but reliably!!! 

	SQLShell::disconnect();

}

1;  # ??? ??????? require ??? use