/SQLShell.pm

  1package SQLShell;
  2use strict;
  3use Carp;
  4use DBI;
  5# For some advanced uses you may need PostgreSQL type values:
  6use DBD::Pg qw(:pg_types);
  7
  8use base qw[ISQLDriver];
  9use utf8;
 10
 11BEGIN {
 12	our $VERSION   = '0.04';
 13	our $AUTHORITY = 'freelance:SLANT';
 14}
 15
 16my $Count = 0;
 17
 18sub new {
 19  my $proto = shift;                 # ????????? ??? ?????? ??? ????????? ?? ??????
 20  my $class = ref($proto) || $proto; # ???? ?????????, ?? ????? ?? ???? ??? ??????
 21  $Count++;							 # ?????????? ?????????? ???????????? ??????????? ??????	
 22  my $self  = {};
 23  $self->{DB_TRACE} =[];			 # ????? ??????? ?????????? ? ?? log_level,filename
 24  $self->{OPTIONS} = [];			 # ?????? ?? ?????????? ?????? ??????
 25  $self->{DBH}=undef;				 # Pointer to database handler  	
 26  bless($self, $class);              # ?????? ????? ??????? bless
 27  return $self;
 28}
 29
 30sub just_do { 
 31	my ($self,$func,$param)=@_;
 32    if (defined $self->{DBH}){
 33    	my $p =undef;
 34    	foreach my $item(@$param){
 35     		#$item ="\'$item\'";
 36     		$item =$self->{DBH}->quote($item);     		
 37     	}
 38     	$p= join(",",@$param);
 39     	my $q="SELECT $func($p)";
 40     	
 41     	#$self->{DBH}->begin_work();
 42    	my $sth = $self->{DBH}->prepare($q);
 43        $sth->execute() || croak("SQLShell.pm:: Can't execute sql query. Error $self->{DBH}->errstr\n"); 
 44        #$self->{DBH}->commit();
 45        return $sth->fetchall_arrayref();# Returns pointer to array of result
 46    }else{
 47    	croak("SQLShell.pm:: Can't execute sql query. Database not connected \n");
 48    }
 49}
 50
 51sub debug {
 52 my $self = shift;
 53  if (@_) { 
 54  	@{ $self->{DB_TRACE} } = @_;
 55  	#TODO Add validation log_level must from 1 to 7
 56  	if ($self->{DB_TRACE}->[0] < 0){
 57  		$self->{DB_TRACE}->[0]=0;
 58  	}
 59	if ($self->{DB_TRACE}->[0] > 7){
 60  		$self->{DB_TRACE}->[0]=7;
 61  	}
 62	if ((scalar(@{$self->{DB_TRACE}})== 2)&& defined $self->{DBH}){
 63  		$self->{DBH}->trace($self->{DB_TRACE}->[0],$self->{DB_TRACE}->[1]);
 64  	}	
 65	#TODO check file name or not what's the question ?
 66  }
 67  return $self->{DB_TRACE};
 68}
 69
 70sub disconnect {
 71	my ($self) = @_;
 72	$self->{DBH}->disconnect() if defined($self->{DBH});
 73}
 74sub connect{
 75	my $self = shift;
 76	if (scalar(@{$self->{OPTIONS}})== 7){
 77		my ($dbname,$username,$password,$dbhost,$dbport,$dboptions,$dbtty)=@{$self->{OPTIONS}};
 78		#TODO ???????? ????? ??????? ??? - ??????? ??????? ? ???? ?? ????????? postgres ????? ??????? ????? ????
 79		$self->{DBH} = DBI->connect("dbi:Pg:dbname=$dbname;host=$dbhost;port=$dbport;options=$dboptions",$username,$password,
 80									{PrintError => 0, RaiseError => 1,AutoCommit => 1});
 81		#Test connection
 82		croak("SQLShell.pm::connect Erorr $DBI::errstr\n") unless (defined $self->{DBH});
 83  		#????? ???? ???? ? ??????? DD.MM.YYYY, ? ?? ??? ?? ????????? ? ??????? ISO ??? ??? ? ????? ???? ??????? ?????.
 84		$self->{DBH}->do("SET DATESTYLE TO GERMAN");
 85	}else{
 86		croak("SQLShell.pm::connect Can't connect to BD. becouse arument's list not full");
 87	}
 88}
 89
 90sub select {
 91    my($self, $fields,$table, $cond) = @_;
 92    #TODO add check $fields,$table,$cond
 93    if (defined $self->{DBH}){
 94    	#$self->{DBH}->begin_work();
 95    	my $q="SELECT $fields FROM $table WHERE $cond";
 96    	my $sth = $self->{DBH}->prepare($q);
 97        my $rv = $sth->execute();
 98        #$self->{DBH}->commit();
 99         if (!defined $rv) {
100         	my $e="SQLShell.pm:: When execute query $q:".$self->{DBH}->errstr."\n";
101            croak ($e);
102        }else{
103            return $sth->fetchall_arrayref();# Returns pointer to array of result
104        }
105    }else{
106    	croak("SQLShell.pm:: Can't execute sql query. Database not connected \n");
107    }
108}
109#TODO need function for packet's data insert, update(use ??? and prepare)
110sub _doexec{# Private function !!!
111	my ($self,$query)=@_;
112    if (defined $self->{DBH}){
113    	#$self->{DBH}->begin_work();
114    	my $rv = $self->{DBH}->do($query);
115        #$self->{DBH}->commit();
116         if (!defined $rv) {
117         	my $e="SQLShell.pm:: When execute query $query\n:".$self->{DBH}->errstr."\n";
118            croak ($e);
119        }else{
120            return $rv;# Returns count records
121        }
122    }else{
123    	croak("SQLShell.pm:: Can't execute sql query. Database not connected \n");
124    }
125}
126
127sub delete{
128	my($self,$table,$cond) = @_;
129	#TODO add check $table,$cond
130	my $q="DELETE FROM $table WHERE $cond";
131	return $self->_doexec($q);
132}
133sub update{
134	my($self,$table,$field,$value,$cond) = @_;
135	#TODO add check $table, $fields,$values,$cond
136	my $q="UPDATE $table SET $field=$self->{DBH}->quote($value) WHERE $cond";
137    return $self->_doexec($q);
138}
139
140sub insert {
141    my($self, $table, $fields,$values) = @_;
142    #TODO add check $table, $fields,$values
143    my $q="INSERT INTO $table ($fields) VALUES (E$values)";
144    return $self->_doexec($q);
145}
146
147sub options {
148  my $self = shift;# 
149  if (@_) { @{ $self->{OPTIONS} } = @_ }
150  return $self->{OPTIONS};
151}
152
153sub DESTROY {
154  $Count--;
155}
156sub total {
157  return $Count;
158}
159
160END{
161	#Dirty hack, but reliably!!! 
162	SQLShell::disconnect();
163}
1641;  # ??? ??????? require ??? use