#!/usr/bin/perl
package dictyBase_Table;
#####################################################################
# Author : Shuai Weng
# Date   : July 2001
#
# This class is the subclass of DB_Table. It contains dictyBase
# specified methods.
#
# See documentation for usage details:
#
# http:///usr/local/dicty/www_dictybase/db/lib/staff/dictyBase/programmer/dictyBase_Table.html
#
#####################################################################
use strict;
use DBI;
use vars qw (@ISA);
use lib "/usr/local/dicty/www_dictybase/db/lib/common/Objects";
use DB_Table;
@ISA = qw (DB_Table); 

####################################################################
sub new {
####################################################################
    my ($class, %args) = @_;
    $args{'insensitive'} = 1;
    $class->SUPER::new(%args);
}

####################################################################
sub getRow {
####################################################################
#### This method returns a specifed row info delimited by '\t'.
#### It will be used for deleted_row in delete_log table.
    my ($self) = @_;
    my $sql = "SELECT *
               FROM   ".$self->schema.".".ref($self)."
               WHERE  ".$self->{'_whereClause'};
    my $sth = $self->dbh->prepare($sql); 
    $sth->execute( @{ $self->{'_whereValue'} } );
    my $row = join("[:]", $sth->fetchrow());
    $sth->finish;
    return $row;
}


####################################################################
sub getRowOld {
####################################################################
#### This method returns a specifed row info delimited by '\t'.
#### It will be used for deleted_row in delete_log table.
    my ($self) = @_;
    my $sql = "SELECT *
               FROM   ".$self->schema.".".ref($self)."
               WHERE  ".$self->{'_whereClause'};
    my $sth = $self->dbh->prepare($sql); 
    $sth->execute( @{ $self->{'_whereValue'} } );
    my $row = join("\t", $sth->fetchrow());
    $sth->finish;
    return $row;
}


####################################################################
sub getRow2 {
####################################################################
#### This method returns a specifed row info delimited by '\t'.
#### It will be used for deleted_row in delete_log table.
    my ($self) = @_;
    my $sql = "SELECT *
               FROM   ".$self->schema.".".ref($self)."
               WHERE  ".$self->{'_whereClause'};
    my $sth = $self->dbh->prepare($sql); 
    $sth->execute( @{ $self->{'_whereValue'} } );
    my $row = join("::", $sth->fetchrow());
    $sth->finish;
    return $row;
}



####################################################################
1; #################################################################
####################################################################


=pod

=head1 Name

dictyBase_Table.pm

=head1 Descrition 

This perl object(dictyBase_Table) acts as an abstract base class for any object that corresponds to a table in dictyBase/dictyBase database. The object provides generic methods that can be used to select, update, insert or delete a single row in a specified table in the database. Note that dictyBase_Table will not actually commit changes and do any error checks. That is responsibility of client interface to do the EVAL check and commit or rollback the transactions. The methods and examples are as follows:


=head1 Instance Methods: 


=head2 Constructor

The new method is the constructor, and its exact form will depend on the subclass for which an Object is being instantiated. However there are some general guidelines that are always applicable. First, a valid database handle must be provided to the constructor. Second, values for columns which make up either the primary key, or a unique key, must be passed to the constructor. For example,
if we want to create a Locus object, where the primary key is locus_no, and locus_name is the unique key. You can use one of the following syntaxes to instantiate a New Locus Object which is a subclass of dictyBase_Table object.

    my $locusObj = Locus->new(dbh=>$dbh,
                              locus_no=>$locusNo);

OR 

    my $locusObj = Locus->new(dbh=>$dbh,
                              locus_name=>$locusNm);


Any table in the database can subclass dictyBase_Table, which also allows object construction when there is a compound primary, or unique key, For instance, if you were instantiating an object to correspond to a row in the Locus_gp table, you would do it : 

    my $locusGpObj = Locus_gp->new(dbh=>$dbh,
                                   locus_no=>$locusNo,
                                   gene_product_no=>$gpNo);

If you try to create an object with columns that do form a key, but the values that you pass do not correspond to a row in the table, then the
constructor will return undef, and it is up to the client to test for this. 
For example :

    if (!$locusObj) {
	print "The locus_name/locus_no is not found in database.", "\n";
    }

    

=head2 Accessor Methods 


dictyBase_Table allows subclasses to retrieve data for any given column, simply using the column name, and this is case insensitive.

For example, if we have created a Locus object, as described above, we can then retrieve data from it like so: 

    print "locus_name = ", $locusObj->locus_name, "\n";
    print "chromosome = ", $locusObj->chromosome, "\n";
    print "description = ", $locusObj->description, "\n";

This will work for any column in the table. Note that while this does have the advantage that the subclassing objects need not implement anything for data retrieval, it does have the disadvantage that if a table column name changes, then all client programs require changing too. 


=head2 Update Method 


Any column in a table, with the exception of those that form the primary key of the table, or the created_by, date_created and date_modified columns (if they exist), may be updated through method calls to the object. Update calls take the form of: 

       $object->updateColumn($newValue);

for instance, we could update the description of a locus thus: 

       $locusObj->updateDescription("new desc here");

You may update as many column for the object as you like, but these changes are not made in the database until you call the enterUpdates method. In addition, as mentioned above, dictyBase_Table will not actually commit the update - that is the responsibility of the client interface. For delete_log and update_log objects, the enterUpdate method also expects, as a single argument, the userid of the user making the updates, which will be recorded in the modified_by column.The other objects do not require any parameters. Thus if we were updating a few columns for a locus , we may have the following: 

        my $locusObj = Locus->new(dbh=>$dbh,
                                  locus_name=>$locusNm);

        $locusObj->updateGenetic_poistion($cm);
        $locusObj->updateChromosome($chrnum);

        eval { $locusObj->enterUpdates; };

        if ($@){
            print "An error occurred : $@\n";
            $dbh->rollback;
        }
        else {
	    print "The locus table has been updated for locus_name = '$locusNm'", "\n";
	    $dbh->commit;
	}

If you have called some update methods, but not yet entered them, you may roll them back, eg: 

        $locusObj->updateGenetic_position($cm);
        $locusObj->updateChromosome($chrnum);

        $locusObj->rollbackUpdate;

if enterUpdates is now called, no changes will be made in the database. Note that rollbackUpdate is ONLY effective if called BEFORE enterUpdates. 



=head2 Delete Method 


A row corresponding to an instantiated object can be deleted simply by calling the delete method. Thus to delete a row in the locus table, you could simply: 

    eval{ $locusObj->delete; };
    if ($@) {
	print "An error occurred when deleting locus entry for locus_name='$locusNm' from database. See error message below", "\n", $@, "\n";
	$dbh->rollback;
    }
    else {
	print "The locus entry for locus_name = '$locusNm' has been deleted from database", "\n";
	$dbh->commit;
    }



=head1 Class Methods

=head2 Insert Method

The class method 'Insert' can be used to insert a new row into database.
To insert a new locus row into locus table, you simply:

    eval {
	Locus->Insert(dbh=>$dbh,
		      literals=>{locus_no=>'CGM_DDB.locusno_seq.nextval'},
		      binds=>{locus_name=>$locusNm,
			      chromosome=>$chrnum,
			      description=>$desc});
    };
    if ($@) {
	print "An error occurred ...$@", "\n";
	$dbh->rollback;
    }
    else {
	print "....", "\n";
	$dbh->commit;
    }


=head1 Author

Shuai Weng

shuai@genome.stanford.edu

=cut