package DBConnection;
# Version: $Id: DBConnection.pm 34 2011-03-01 09:46:00Z gsi $

=head1 NAME

DBConnection - a helper/wrapper for MySQL (DBI) database connections

=head1 SYNOPSYS

 # USE: installed in /software/perllibs
 use lib '/software/perllibs';
 use DBConnection;

 # CONNECT
 my $db = DBConnection->new(host=>'localhost', db=>'cgdb', user=>'', password=>'');
 $db->init || die 'Cannot connect to database';

 # DISCONNECT
 $db->close;

 # SINGLE SELECT
 $db->do("SELECT * FROM taxonomy WHERE Taxon_ID > 1720");
 while (my @r = $db->fetch_row) {
     print join(', ',@r)."\n";
 }

 # MULTIPLE SELECT AND NUMROWS
 my $sth1 = $db->do("SELECT * FROM taxonomy WHERE Taxon_ID > 1720") || print $db->error."\n";
 print $db->query." retrieved ".$db->num_rows." tuples.\n";

 my $sth2 = $db->do("SELECT * FROM taxonomy WHERE Taxon_ID < 6") || print $db->error."\n";
 print $db->query." retrieved ".$db->num_rows($sth2)." tuples.\n";

 while (my @r = $db->fetch_row($sth1)) {
     print join(', ',@r)."\n";
 }

 # DUMP
 $db->dump_select("SELECT * FROM taxonomy WHERE Taxon_ID > 1720");

 # INSERT
 $db->do("INSERT INTO taxonomy VALUES(100000,'Roland',10,1)") || print "Error: ".$db->error."\n";

 # UPDATE
 $db->do("UPDATE taxonomy SET Taxon_Name='Laurent' WHERE Taxon_ID=100000");

 # DELETE
 $db->do("DELETE FROM taxonomy WHERE Taxon_ID=100000");

 ###########
 # BONUSES #
 ###########

 # FETCH WHOLE TABLE IN MEMORY
 my $rows = $db->fetch_table("SELECT * FROM table");

 # INSERT
 my $h = { id=>$db->quote($pathway), name=>$db->quote($name), comment=>$db->quote($comment) };
 $db->insert('pathway', $h);

 # EXISTS
 if (! $db->exists('pathway', { id => $db->quote('1CMET2-PWY') } ) ) { ... }
 
=cut

use strict;

BEGIN {
	use Exporter   ();
	our @EXPORT      = qw(init select fetch_row num_rows error);
	our @EXPORT_OK   = qw(
		host		db		user		password
		dbh		sth	query		error
			   );
	our %EXPORT_TAGS = ( FIELDS => [ @EXPORT_OK, @EXPORT ] );
}
our @EXPORT_OK;

# Class::Struct forbids use of @ISA
sub import { goto &Exporter::import }

use Class::Struct qw(struct);
struct 'DBConnection' => [
	host        => '$',
	db				=> '$',
	user			=> '$',
	password		=> '$',
	dbh			=> '$',
	sth			=> '$',
	query       => '$',
];

use DBI;

=head1 Functions

=cut

sub init {
	my $self = shift;
	my $dbh = DBI->connect("DBI:mysql:database=".$self->db.";host=".$self->host,$self->user,$self->password) || return 0;
	$self->dbh($dbh);
	return $self;
}

sub close {
	my $self = shift;
	$self->dbh->disconnect();
}

=head2 query

Sets or gets the query.

	# SET
	$db->query(SOME SQL);
	$db->do(SOME SQL);

	# GET
	print $db->query;

=cut

=head2 error

Returns the last error string from MySQL.

	print $db->error;

=cut

sub error {
	return shift->dbh->errstr;
}

sub select($) {
	my ($self, $query) = @_;
	$self->query($query);
	my $sth = $self->dbh->prepare($query);
	$sth->execute;
	$self->sth($sth);
	return $self->sth;
}

sub num_rows {
	my $self = shift;
	if (@_) {
		return shift->rows;
	}
	else {
			return $self->sth->rows;
	}
}

=head2 fetch_row

Returns the next row (as an array) of the last query or the specified statement handle ($sth)

	my $sth = $db->do("SELECT id, name FROM table");

	my @row = $db->fetch_row;
	my ($id, $name) = $db->fecth_row($sth);

=cut

sub fetch_row {
	my $self = shift;
	if (@_) {
		return shift->fetchrow_array;
	}
	else {
			return $self->sth->fetchrow_array;
	}	
}

=head2 fetch_hash

Returns the next row (as a hash) of the last query or the specified statement handle ($sth)

	my $sth = $db->do("SELECT id, name FROM table");

	my %h = $db->fetch_hash;
	my %h = $db->fecth_hash($sth);

	print $h->{'name'};

=cut

sub fetch_hash {
	my $self = shift;
	if (@_) {
		return shift->fetchrow_hashref;
	}
	else {
			return $self->sth->fetchrow_hashref;
	}	
}

=head2 quote

Escape a string for its inclusion in a query, for example.

	$db->do('SELECT * FROM table WHERE id='.$db->quote($id).');

=cut

sub quote {
	my ($self, $value) = @_;
	return $self->dbh->quote($value);
}	

sub do($) {
	my ($self, $query) = @_;
	$self->query($query);
	if (uc($query) =~ /^\s*SELECT/) {
		my $sth = $self->dbh->prepare($query);
		$sth->execute;
		$self->sth($sth);
		return $self->sth;
	}
	else {
		return $self->dbh->do($query);
	}
}

=head2 dump_select

Outputs (via print) the result of the passed query. A field (default ', '), and a row (default "\n") separator can be specified.

	$db->dump_select("SELECT * FROM table");

	# SPECIFY COLUMN SEPARATOR
	$db->dump_select(SELECT * FROM table", "\t");

	# SPECIFIY COLUMN AND ROW SEPARATOR
	$db->dump_select(SELECT * FROM table", "\t", "\n");

=cut

sub dump_select($) {
	my ($self,$query, $fieldSep, $rowSep) = @_;
	$fieldSep = ', ' unless $fieldSep;
	$rowSep   = "\n" unless $rowSep;
	$self->do($query);
	while (my @r = $self->fetch_row) { print join($fieldSep,@r).$rowSep; }
}

=head2 extract_matrix

DOCUMENTATION TO DO

=cut

sub extract_matrix {
	my ($self,%args) = @_;
	my $row_field = $args{row_field};
	my $col_field = $args{column_field};
	my $val_field = undef;
	$val_field = $args{value_field} if defined $args{value_field};
	my $table = $args{table};
	my $query = "SELECT $row_field, $col_field";
	$query.=", $val_field" if defined $val_field;
	$query.=" FROM $table ORDER BY $row_field, $col_field";
	$self->do($query);
	my @rows;
	my %row_idx;
	my %col_idx;
	my $last_row = undef;
	my $i=0;
	my $j=0;
	while (my ($row_id,$col_id,$val) = $self->fetch_row) {
		if (!defined $last_row) { # 1st row ever
			$last_row = $row_id;
			$row_idx{$row_id} = $i;
			my @cols = ();
			$rows[$i] = \@cols;
		} 
		elsif ($last_row ne $row_id) { # new row
			$row_idx{$last_row} = $i;
			$last_row = $row_id;
			$i++;
			$row_idx{$row_id} = $i;
			my @cols = ();
			$rows[$i] = \@cols;
		}
		if (!defined $col_idx{$col_id}) {
			$col_idx{$col_id} = $j++;
		}
		my @cols = @{$rows[$row_idx{$row_id}]};
		$cols[$col_idx{$col_id}] = $val || 1;
		$rows[$row_idx{$row_id}] = \@cols;
	}	
	return (\%row_idx, \%col_idx, \@rows);
}

=head2 fetch_all

Returns a ref to an array of values (first column of the passed select statement).

	my $array_ref = $db->fetch_all("SELECT id FROM table");

=cut

sub fetch_all {
	my ($self) = @_;
	my $sth = $self->sth;
	if (@_) { $sth = shift; }
	my @res;
	while (my ($v) = $sth->fetch_row) { push @res, $v; }
	return \@res;
}

=head2 fetch_table

Returns a ref to an array of rows (arrays)

	my $array_ref = $db->fetch_table("SELECT * FROM table");

=cut

sub fetch_table {
	my ($self, $query) = @_;
	$self->do($query);
	my @res;
	while (my @row = $self->fetch_row) { push @res, \@row; }
	return \@res;
}

=head2 insert

Inserts the specified fields with the provided values into the specified table OR does nothing if they were already present.

An error will occur though if trying to insert with an existing primary key with different values.

	my $h = { id => $db->quote($pathway), name => $db->quote($name), comment => $db->quote($comment) };
	$db->insert('pathway', $h);

=cut

sub insert {
	my ($self, $table, $h) = @_;
	my @where;
	foreach (keys %$h) { push @where, $_.'='.$h->{$_}; }
	$self->do("SELECT * FROM $table WHERE ".join(' AND ', @where));
	$self->do("INSERT INTO $table (".join(",", keys %$h).") VALUES (".join(",", values %$h).")") unless $self->num_rows > 0;
}

=head2 exists

Returns the number of rows having the specified keys=values

	my $h = { id => $db->quote($pathway), name => $db->quote($name), comment => $db->quote($comment) };
	if (!$db->exists('pathway', {id=>$db->quote($id)} )) { ... }

=cut

sub exists {
	my ($self, $table, $h) = @_;
	my @where;
	foreach (keys %$h) { push @where, $_.'='.$h->{$_}; }
	$self->do("SELECT * FROM $table WHERE ".join(' AND ', @where));
	return $self->num_rows;
}


1;