package DB_TableLOB;

# Author : Mira Kaloper 
# Date   : March 2002


# This class is based on DB_Table Object, except that it does not read LOB 
# columns but instead a separate method is written to handle this.
# This object should replace DB_Table object, sinc eit works for LOB and 
# "regular" data.
# The DB_Table object is an abstract base class that can be easily
# subclassed, to produce objects, which correspond to a table in a
# database, allowing retrieval of a single row from the database, and
# provides AUTOLOADed methods for retrieval of data from those columns
# in that table, as well as updating those data, deleting those data,
# and also creating new rows in tables.

####################################################################
#
# KNOWN ISSUES / TO DO
#
####################################################################

# 1. Not all methods have been thoroughly tested against all tables
# 2. Can probably be further optimized
# 3. Not recommended if retrieving 100's of rows
# 4. Should there be a notion of semi-private methods, that should
#    only be used by sub-classes, as opposed to public method, which
#    can be used by Object clients?
# 5. The default schema is the PROD schema - you should overide the schema
#    method if you are querying a different schema.

# DESIGN ISSUES
#
# 1. DB_Table has a lot of methods that are really specific to Oracle
#    tables.  These should be removed, and put into an ORACLE_TABLE
#    subclass that inherits from DB_Table.
#
# 2. The schema method should not be implemented in DB_Table, instead
#    DB_Table should have a stub method, that has to be overridden by
#    a subclass that actually corresponds to an table in an actual database
#    instance, such as SMD_Table, for which a schema is an appropriate piece
#    of information.

use strict;
use Carp;
use vars '$AUTOLOAD';


####################################################################
#
# Class Globals
#
####################################################################

my %columnsArray; # this hash will hold what columns are valid for a table
my %columnsHash;  # this hash will store the columns names for a table 
                  # in a hash for quick look up

my %select;  # this hash will hold the select string for a table

my %modifiedConstructor; # this hash will indicate whether we've modified
                         # the allowedConstructor for a Class, so we only
                         # do it once for any particular class

####################################################################
#
#   CONSTRUCTOR AND ASSOCIATED METHODS
#
####################################################################

####################################################################
sub new{
####################################################################
# This constructor method will get the initial data for a row from the
# database for the relevant table, given the values for a supplied set
# of columns which define a unique key.  This method acts as the
# constructor for all objects which inherit from DB_Table.

    my $self = {};

    my $table = shift;
    
    bless $self, $table;
    
    my (%args) = @_;


    $self->{'_dbh'} = $args{'dbh'} || die "No database handle was provided to $table constructor\n";
    
    delete $args{'dbh'}; # only want to leave unique identifiers for row

    # check if it is a case insensitive search
    if ($args{'insensitive'}) {
    	$self->{'_insensitive'} = 1;
	delete $args{'insensitive'}; 
    }
    
    # first check that the supplied arguments can be used
    # to construct an object for the self table

    $self->_initCheck(%args);

    # then initialize - note if initialization is unsuccessful
    # $self will be undef - which is what we will return

    $self->_init(%args);

    # lastly, check that we received a row of data, by seeing
    # that we have data for the constructor columns.
    # assumes that keys are not nullable
    
    foreach my $keyColumn (keys %args){ 
	$self->$keyColumn() || undef ($self); # undefine object if no entry
	last if !defined $self;	# can stop if we've undef'd the object
    }

    return ($self)

}

#####################################################################
sub _initCheck{
#####################################################################
# This private method wraps around two methods whose sum effect is to
# check that the arguments supplied to the construcor are valid to
# construct an object for the Class's table

    my ($self, %args) = @_;

    # alphabetize columns name used for object construction

    $self->_checkInstantiateKey(%args);

    # check if the used columns form an allowed constructor

    $self->_checkAllowedConstructorKey;

}

#####################################################################
sub _checkInstantiateKey{
#####################################################################
# This private method simply looks to see what columns have been
# specified to use as a key to get data from the database, and puts
# them in alphabetical order.  This is so that they can be
# subsequently checked against what constructors are allowed.

    my ($self, %args) = @_;

    my @keyColumns;

    foreach my $keyColumn (sort {lc($a) cmp lc($b)} keys %args){

	if(!$keyColumn || !$args{$keyColumn}){
	    croak "No unique identifier was provided OR No value was provided for $keyColumn";	    
	}
	
	push (@keyColumns, lc($keyColumn)); # make keyColumns in order
	
    }
    
    $self->{'_keyColumns'} = [ @keyColumns ];
    
}

##############################################################
sub _checkAllowedConstructorKey{
##############################################################
# This private method checks to see if the supplied arguments to the
# constructor may be used to construct an object or not 

    my ($self) = @_;

    my $table = ref($self);

    no strict "refs"; # turn off strict to access allowedConstructors

    my $var = $table."::allowedConstructors"; # this is how we access the allowed constructors

    # first, if we haven't already for the Object's table, we need to
    # rearrange the allowedConstructor hash, so that any composite
    # keys are recorded in alphabetical order

    if(!exists($modifiedConstructor{$table})){
	
	foreach (keys %{$var}){

	    my @primaryKeys = sort (split(/\:/, lc($_)));
	    $$var{join (":", @primaryKeys)} = undef;
	    
	}

	$modifiedConstructor{$table} = 1;

    }
	
    # now check if the supplied constructor is allowed

    my @keyColumns = @{$self->{'_keyColumns'}};
    
    if (!exists(${$var}{lc(join(":", @keyColumns))})){

	croak "@keyColumns is not an allowed constructor for a $table object\n";
        	
    }

    use strict "refs"; # turn it back on!
        
}

########################################################################
sub _init{
########################################################################
# This private method initializes the object

    my ($self, %args) = @_;

    # first create a hash which will hold changed column and values if
    # a client subsequently wants to update attributes of the object
    # within the database
	
    $self->{'_changeColumns'} = {};

    # next actually retrieve the data from the database, and put it
    # into the object.

    $self->_getData(%args);

}

####################################################################
sub _getData{
####################################################################
# This private method gets all the data for a single row in the
# database.  If there is no corresponding row in the database, then
# the entire object is undef'd
    
    my ($self, %args) = @_;
    
    my $table = ref($self);

    # first we have to find out what columns exist for the table,
    # if we don't already know
    
    if (!exists($columnsArray{$table})){
	
	$columnsArray{$table} = $self->getLCColumnNamesArrayRef; # store for future use
	$select{$table} = join (", ", @{$columnsArray{$table}}); # store for future use
	
	# now make quick look up for column, which will be used in AUTOLOAD

	foreach my $column (@{$columnsArray{$table}}){

	    $columnsHash{$table}{$column} = undef;
	    
	}

    }
    
    # now retrieve the data
    
    my $dataRef = $self->_getRowDataByKey(%args);
    
    # then put data into object hash
    # I'd also really like to create the accessor methods 
    # that are currently autoloaded here, but it never seems to work
    
    for (my $i = 0; $i < @{$columnsArray{$table}}; $i++){

	$self->{"_".$columnsArray{$table}->[$i]} = $$dataRef[$i];

    }
    
}

########################################################################
sub _getRowDataByKey{
########################################################################
# This private method actually retrieves the data from the database for
# the requested row in the requested table.

    my ($self, %args) = @_;

    # first of all we have to construct a 'where clause' from the
    # arguments that were supplied to the constructor

    my ($whereClause, @whereValues);

    my $table = ref($self);

    foreach my $keyColumn (keys %args){
	
	# if they want a case insensitive search, and it's non-numeric

	if ($args{$keyColumn} !~ /^[0-9]+$/i && $self->{'_insensitive'}) {
		$whereClause .= "AND upper($keyColumn) = ? ";
		push (@whereValues, uc($args{$keyColumn}));
	}else { # otherwise, an exact match is required
		$whereClause .= "AND $keyColumn = ? ";
		push (@whereValues, $args{$keyColumn}); 
	}
    }
    
    $whereClause =~ s/^AND//;
    
    # now store the clause, and the values, as these will
    # be reused either to update, or delete the row to which
    # the object corresponds

    $self->{'_whereClause'} = $whereClause;
    $self->{'_whereValue'} = [ @whereValues ];
    
    # now get the data

    my $sth = $self->dbh->prepare("SELECT $select{$table}
                                          FROM ".$self->schema.".$table
                                          WHERE $whereClause");

    $sth->execute(@whereValues);

    my $dataRef = $sth->fetchrow_arrayref;

    $sth->finish;

    return $dataRef;

}

####################################################################
#
#   CLASS METHODS
#
####################################################################



#####################################################################
sub Insert{
#####################################################################
# This Class method inserts a row of data into the oracle database, 
# in a table whose name is the same as the class.

# The client is required to pass in a valid database handle, which has
# permission to enter data into the table.  In addition the client
# needs to pass in two keys whose values are hash references.  Each
# reference will refer to a hash of column names, and their values.
# One key will be called 'literals', and is to be used for columns
# which will use literal entries like SYSDATE, or use values from
# Oracle sequences.  The other key will be called 'binds', and will be
# for columns whose values can be passed in as bind variables.  The
# client is expected to pass in values for all not nulable columns in
# one of these two hashes that are passed by reference.  No error
# checking is done by the class, thus it is up to the client to catch
# any errors, and deal with them appropriately #

#
# eg:
#
#  Usage: Experiment->Insert(dbh=>$dbh,
#                            literals=>{exptid=>'EXPTID_SEQ.NEXTVAL',
#                                       date_modified=>'SYSDATE',
#                                       date_created=>'SYSDATE'},
#                            binds=>{exptname=>$exptname,
#                                    organism=>$organism,
#                                    ... => ...});
#
#
#                           
#  Usage: Print->Insert(dbh=>$dbh,
#                       literals=>{printid=>'PRINTID_SEQ.NEXTVAL',
#                                  date_modified=>'SYSDATE',
#                                  date_created=>'SYSDATE'},
#                       binds=>{printname=>$printName,
#                               printer=>$printer,
#                               ... => ...});
#  
# etc.
  
    my ($class, %args) = @_;
    
    my $dbh         = $args{'dbh'} || die "A database handle must be passed into the Insert method";
    my $literalsRef = $args{'literals'};
    my $bindsRef    = $args{'binds'};
    
    my $table       = $args{'table'} || $class; # let user specify table

    my (@insertMarks, @values, @insertColumns, $column);

    # deal with literal columns

    foreach $column (keys %{$literalsRef}){
	push (@insertColumns, $column);
	push (@insertMarks, $$literalsRef{$column});
    }

    # add in bind columns

    foreach $column (keys %{$bindsRef}){
	push (@insertColumns, $column);
	push (@insertMarks, "?");
	push (@values, $$bindsRef{$column});
    }
    
    my $sth = $dbh->prepare("INSERT INTO ".$class->schema.".$table (".join(", ",@insertColumns).")
                                    VALUES (".join(", ", @insertMarks).")");

    #print "INSERT INTO ".$class->schema.".$table (".join(", ",@insertColumns).")
    #                                VALUES (".join(", ", @insertMarks).")";

    #print "values = ", @values;

    $sth->execute(@values);

    $sth->finish; 
                                                       
}

#######################################################################
sub GetNotNullableHashRef{
#######################################################################

# This class method returns a reference to a hash, which contains as
# keys all the columns names which are not nullable in a table.
# The column names are uppercased


#   Usage: Print->GetNotNullableHashRef(dbh=>$dbh);
#                      
# 
#   Usage: Experiment->GetNotNullableHashRef(dbh=>$dbh);
#
#
#######################################################################   

    my $class = shift;

    my (%args) = @_;

    my $dbh = $args{'dbh'} || die "A database handle must be passed into the GetNotNullableHashRef method";

    my $sth = $dbh->prepare("SELECT UPPER(COLUMN_NAME)
                                    FROM all_tab_columns
                                    WHERE OWNER = ?
                                    AND TABLE_NAME = ?
                                    AND NULLABLE = 'N'");

    $sth->execute($class->schema, uc($class));
    
    my ($column, %notNullable);
    
    while ($column = $sth->fetchrow){
	$notNullable{$column} = undef;
    }


    return \%notNullable;


}

####################################################################
sub GetValueCaseInSensitiveArrayRef{
####################################################################
#  This Class method returns an array reference, which points to
#  an array that contains all the case insensitive value which 
#  correspond to a column and value
#
#  Usage:
#
#  my $arrayRef = Experiment->GetValueCaseInSensitiveArrayRef(dbh=>$dbh,
#                                                     exptname=>$exptname);  
#
#  my $arrayRef = Category->GetValueCaseInSensitiveArrayRef(dbh=>$dbh,
#                                                   category=>$category);
#
##########################################################################

    my $class = shift;
    
    my (%args) = @_;
    
    my $dbh = $args{'dbh'} || die "A database handle must be passed into the GetValueCaseInSensitiveArrayRef method";

    delete $args{'dbh'}; # get rid of dbh from the hash
    
    my ($column, $value) = %args; # now get column and value;
    
    # probably want to abstract the following few lines into a
    # seperate method (like GetDataType) or something

    # check column type in the oracle database
    
    my $sth = $dbh->prepare("SELECT data_type
                             FROM all_tab_columns
                             WHERE owner = ?
                             AND table_name = ?
                             AND column_name = ?");
    
    $sth->execute($class->schema, $class, $column);
    
    my $dataType = $sth->fetchrow();
    
    $sth->finish;
    
    if($dataType eq 'NUMBER' || $dataType eq 'DATE'){
	die "The data type $column is not VARCHAR2 or CHAR and do not need to check case insensitive";
	
    }
    
    # get all the case insensitive data which correspond to a column and value  

    my ($columnValue, @columnValues);
    
    $sth = $dbh->prepare("SELECT DISTINCT($column)
                                 FROM ".$class->schema.".$class
                                 WHERE UPPER($column) = ?");
    
    $sth->execute(uc($value));
    
    while ($columnValue = $sth->fetchrow()){
	push(@columnValues, $columnValue);
	
    }

    $sth->finish;
    
    return \@columnValues;

}

##########################################################################
sub DistinctValuesByColumnArrayRef{
##########################################################################
#  This Class method returns an array reference, which points to
#  an array that contains all column value by particular column name
#
#  Usage:
#
#  my $arrayRef = Experiment->DistinctValuesByColumnArrayRef(dbh=>$dbh,
#                                                            column=>'printname',
#                                                            order_by=>'printname');  
#  OR
#
#  my $arrayRef = Category->DistinctValuesByColumnArrayRef(dbh=>$dbh,
#                                                          column=>'category');
#
#  
##########################################################################

    my $class = shift;
    
    my (%args) = @_;
    
    my $dbh = $args{'dbh'} || die "A database handle must be passed into the DistinctValuesByColumnArrayRef method";
    
    my $column = $args{'column'} || die "A column name must be passed into the DistinctValuesByColumnArrayRef method";
    
    if(!$class->ColumnExistsInTable(dbh=>$dbh,
				    column_name=>$column)){
    
	croak "The column $column does not exist in the $class table.\n"
	
    }
    
    # get all the column value correspond to a particular column  
    my ($columnValue, @columnValues);
    
    my $sql = "SELECT DISTINCT($column)
               FROM ".$class->schema.".$class";
    
    if(defined($args{'order_by'})){
	$sql .= " ORDER BY UPPER($column)";
	
    }
    my $sth = $dbh->prepare($sql);
    
    $sth->execute;
    
    while($columnValue = $sth->fetchrow()){
	push(@columnValues, $columnValue);
	
    }
    
    $sth->finish;
    
    return \@columnValues;
    
}

####################################################################
sub DumpTable{
####################################################################
# This class method allows for a table to be dumped either to a file,
# to a file handle, or otherwise to STDOUT.  In addition, the client
# may specify only certain columns to be dumped, and also provide a
# hash of filters, eg:
#
# Result->DumpTable(dbh=>$dbh,
#                   file=>$file,
#                   columns=>\@columns,
#                   filters=>{"exptid ="=>"10",
#                             "ch1 >"=>"150",
#                             "log_rat2n is not null"=>undef},
#                   order_by=>["log_rat2n desc", "spot asc"],
#                   cached=>1);
#
# Note that the hash of filters has the column name AND the operator
# as the key, and then the value to which the column contents is being
# prepared as the value.  If, as in the last filter, you are requesting
# something tthat doesn't exactly have a value (like NULL), you can put
# the whole phrase as the key, and undef as the value
#
# Experiment->DumpTable(dbh=>$dbh,
#                       handle=>\*OUT,
#                       seperator=>"<:>",
#                       eol=>":<>:",
#                       headings=>1);
#
# if no columns argument is provided, all columns will be dumped
# if no filters argument is provided, no filtering is done
# if no file, or handle argument is provided, then everything is printed to STDOUT
# if no column seperator is provided, then \t will be used
# if no record seperator is provided, then \n will be used
# if the value '1' is passed in for headings, then the first line
#    printed will be the column names, in the order that they are printed.
# if the value '1' is passed in for 'cached' then the sql statement
# used to select the data will be cached.  This is useful if you are
# going to re-execute the same statement many times, but with different
# values for the filters, eg dumping the result table for different
# experiment id's
# you may provide a list of columns by which to order the data
# 
# In addition, this method may be invoked on DB_Table itself (or indeed a subclass of it, but
# in this case, you must supply an additional 'table' argument:
#
# DB_Table->DumpTable(dbh=>$dbh,
#                     handle=>\*OUT,
#                     seperator=>"<:>",
#                     eol=>":<>:",
#                     headings=>1,
#                     table=>'Experiment');
#
# This is useful if you want to dump out many tables, and instead don't have
# to use a module for each one, but just pass in different table arguments.
# 
# eg :
#
# foreach $table (@tables){
#
#       DB_Table->DumpTable(dbh=>$dbh,
#                           table=>$table);
#

    my ($class, %args) = @_;

    my $dbh = $args{'dbh'} || die "You must supply a database hadle to DumpTable\n";

    my $seperator = $args{'seperator'} || "\t";
    my $eol       = $args{'eol'}       || "\n";

    my ($fh, $method, $rowRef);

    # set up where we're going to print the data

    if (defined($args{'file'})){

	open (OUT, ">$args{'file'}") || die "Can't open $args{'file'} : $!\n";

	$fh = \*OUT;

    }elsif (defined($args{'handle'})){

	$fh = $args{'handle'};

    }else{

	$fh = \*STDOUT;

    }

    my $columnsRef = $args{'columns'} || $class->GetLCColumnNamesArrayRef(dbh=>$dbh,
									  table=>$args{'table'});

    if ($args{'cached'} == 1){

	$method = "prepare";

    }else{

	$method = "prepare";

    }

    my ($statement, $valuesRef) = $class->_CreateDumpStatement(columns=>$columnsRef, 
							       filters=>$args{'filters'},
							       order_by=>$args{'order_by'}, 
							       table=>$args{'table'});

    my $sth = $dbh->$method($statement);

    if (@{$valuesRef}){ # we have some values to pass in the execute

	$sth->execute(@{$valuesRef});

    }else{

	$sth->execute;

    }

    defined ($args{'headings'}) && (print $fh join($seperator, @{$columnsRef}), $eol);

    while ($rowRef = $sth->fetchrow_arrayref){

	print $fh join($seperator, @{$rowRef}), $eol;

    }

    $sth->finish;

    defined($args{'file'}) && close (OUT);

}  
  

########################################################################
sub TableExists {
########################################################################
# This Class method returns a boolean to indicate if a table exists in
# the current schema with the passed in name.
#
#   Usage:
#
#   if(SMD_Table->TableExists(dbh=>$dbh,
#                             table_name=>'Experiment')){
#      #blah   
#   
#   }    
#
#   if(dictyBase_Table->TableExists(dbh=>$dbh,
#                             table_name=>'Locus')){
#
#      #blah
#   }
#  
 
    my ($class, %args) = @_; 
    
    my $dbh = $args{'dbh'} || die "A database handle must be passed into the TableExists method";
    
    my $table_name = $args{'table_name'} || die "A table name must be passed into the TableExists method";
    
    
    my $sth = $dbh->prepare("SELECT TABLE_NAME
                                    FROM all_tables
                                    WHERE OWNER = ?
                                    AND TABLE_NAME = ?");

    $sth->execute($class->schema, uc($table_name));
    
    my $tableNameExist = $sth->fetchrow;
            
    $sth->finish;

    return defined($tableNameExist);  


}


#######################################################################
sub ColumnExistsInTable {
#######################################################################
# This Class method returns a boolean to indicate whether the specified
# column exists in the specified table in the current schema.
#
#   Usage:
#
#   if(SMD_Table->ColumnExistsInTable(dbh=>$dbh,
#                                     table_name=>'Experiment',
#                                     column_name=>'exptid')){
#      #blah
#   }
#
#   if(Print->ColumnExistsInTable(dbh=>$dbh,
#                                 column_name=>'printname')){
#      #blah
#
#   }
#
#   if(Experiment->ColumnExistsInTable(dbh=>$dbh,
#                                      column_name=>'exptname')){
#      #blah
#
#   }
# 
# 
    my ($class, %args) = @_; 
    
    my $dbh = $args{'dbh'} || die "A database handle must be passed into the ColumnExistsInTable method";
 
    my $table_name = $args{'table_name'} || $class;

    
    if(!$class->TableExists(dbh=>$dbh,
                            table_name=>$table_name)){

       die "$table_name is not a valid table name.\n";

    }

    my $column_name = $args{'column_name'} || die "A column name must be passed into the ColumnExistsInTable method";

    
    my $sth = $dbh->prepare("SELECT column_name
                                    FROM all_tab_columns
                                    WHERE owner = ?
                                    AND table_name = ?
                                    AND column_name = ?");

    $sth->execute($class->schema, uc($table_name), uc($column_name));
    my $columnNameExist = $sth->fetchrow;
    $sth->finish;

    return defined($columnNameExist); 


}

  
####################################################################
sub _CreateDumpStatement{
####################################################################
# This private class method is used to create a statement that will
# be used to dump the contents (or part thereof) of a table

    my ($class, %args) = @_;

    my $columnsRef = $args{'columns'};
    my $filtersRef = $args{'filters'};
    my $orderByRef = $args{'order_by'};
    my $table      = $args{'table'} || $class; # allows the table to be an argument

    my $select = "SELECT ". join(", ", @{$columnsRef});
    my $from   = " FROM ".$class->schema.".".$table;

    my (@values, @filters, $where, $filter, $order);

    if (defined($filtersRef)){
	
	foreach $filter (keys %{$filtersRef}){

	    if (defined($$filtersRef{$filter})){
	    
		push (@filters, $filter." ?"); # use bind variables
		push (@values,  $$filtersRef{$filter});

	    }else{ # it's something like blah is not null

		push (@filters,  $filter);

	    }
	    
	}
	
	$where = " WHERE ". join(" AND ", @filters);
	
    }

    if (@{$orderByRef}){

	$order = " ORDER BY ". join(", ", @{$orderByRef});

    }
    
    return ($select.$from.$where.$order, \@values);
    
}

####################################################################
sub LoadTable{
####################################################################
# This class method allows for a file to be loaded into a database
# table, using DBI.  It expects to either get an array of columns, in
# the order that the data exist in the file, or for the file to
# contain the column names as the first line, which will be assumed if
# an array of columns is not passed in.  If a seperator, and
# end-of-line character are not passed in, then they will be assumed
# to be '\t' and '\n' respectively.
#
# examples:
#
# Experiment->LoadTable(dbh=>$dbh,
#                       file=>$file,
#                       columns=>\@columns,
#                       autocommit=>$rows);
##
# Experiment->LoadTable(dbh=>$dbh,
#                       file=>$file,
#                       seperator=>"<:>",
#                       eol=>":<>:",
#                       cached=>1,
#                       verbose=>$n);
#
# if no columns argument is provided, column names must be on the first line
# if no column seperator is provided, then \t will be used
# if no record seperator is provided, then \n will be used
# if the value '1' is passed in for 'cached' then the sql statement
# used to select the data will be cached.  This is useful if you are
# going to re-execute the same statement many times, but with
# different files.
#
# if no autocommit argument is provided, then the client will have to take
# care of committing all data.  If a value is provided, the commitment will
# occur after every $rows rows.  This is useful for large tables, where
# you may have a few million rows.  Obviously any rollback that the client
# may do could be partially ineffective, as some rows may already have been 
# committed.
# 
# If a verbose argument is provided, then a string will be printed every n rows
# to indicate progress.
#
# In addition, this method may be invoked on DB_Table itself (or indeed a subclass of it, but
# in this case, you must supply an additional 'table' argument:
#
# DB_Table->LoadTable(dbh=>$dbh,
#                     file=>$file,
#                     seperator=>"<:>",
#                     eol=>":<>:",
#                     table=>'Experiment');
#
# This is useful if you want to load many tables, and instead don't have
# to use a module for each one, but just pass in different table arguments.
# 
# eg :
#
# foreach $table (@tables){
#
#       DB_Table->LoadTable(dbh=>$dbh,
#                           table=>$table);
#
# }


    my ($class, %args) = @_;
    
    my $dbh  = $args{'dbh'} || die "You must supply a database handle to LoadTable\n";
    my $file = $args{'file'}|| die "You must supply a file name to LoadTable\n";

    my $oldEol    = $/; # just to be safe, remember what eol was
    $/            = $args{'eol'}       || "\n";
    my $seperator = $args{'seperator'} || "\t";

    my $commitRows = $args{'autocommit'} || 0; # default is no autocommit
    my $verboseRows = $args{'verbose'} || 0; # default is not verbose

    my (@columns, @values, $method);
    
    open (IN, $file) || die "Cannot open $file : $!\n";
    
    # set up the columns, and their order
    
    if (exists($args{'columns'})){
	
	@columns = @{$args{'columns'}};
	
    }else{
	
	my $line = <IN>;
	chomp $line;
	@columns = split($seperator, $line);
	
    }
    
    if (exists($args{'cached'}) && $args{'cached'} == 1){
	$method = "prepare";	
    }else{	
	$method = "prepare";	
    }
    
    my $statement = $class->_CreateLoadStatement(\@columns, $args{'table'});
    
    my $sth = $dbh->$method($statement);

    my $rows = 0;

    while (<IN>){

	chomp;
	@values = split($seperator, $_, -1);
	$sth->execute(@values);

	$rows++;

	if ($commitRows && !($rows%$commitRows)){ # if we've inserted enough for commit

	    $dbh->commit;

	}

	if ($verboseRows && !($rows%$verboseRows)){ # if we've inserted enough to give feedback

	    print "$rows rows inserted.\n";

	}

    }

    $sth->finish;

    close IN;

    $/ = $oldEol; # restore contents of eol seperator

}  
    
####################################################################
sub _CreateLoadStatement{
####################################################################
# This private class method is used to create a statement that will
# be used to load the contents of a file into a table

    my ($class, $columnsRef, $table) = @_;

    $table ||= $class; # use class if no table argument specified

    my $insert = "INSERT INTO ".$class->schema.".$table (". join(", ", @{$columnsRef}) .")";
    my $values = " VALUES (". "?, " x (scalar(@{$columnsRef}) - 1) ."?)";

    return ($insert.$values);
    
}


######################################################################
sub ColumnSizesHashRef{
######################################################################
#  This class method retrieves all column sizes for a given table,
#  and returns a reference to a hash containing those column names 
#  as keys and column widths as the values
#
#  Usage : my $columnSizeHashRef = Result->ColumnSizesHashRef(dbh=>$dbh);
#
#
#

   my ($class, %args) = @_;
   my $dbh = $args{'dbh'} || die "You must supply a database handle to ColumnSizesHashRef\n";

   my $table = $args{'table'} || $class;

   my %columnSizes;

   my $sth = $dbh->prepare("SELECT COLUMN_NAME, DATA_LENGTH, 
                                          DATA_TYPE, DATA_PRECISION, 
                                          DATA_SCALE
                                   FROM all_tab_columns
                                   WHERE OWNER = ?
                                   AND TABLE_NAME = ?");

   $sth->execute($class->schema, uc($table));

   while(my($column, $length, $type, $precision, $scale) = $sth->fetchrow){
      if($type ne "NUMBER"){
         $columnSizes{$column} = $length;

      }else{
         if ($scale){$precision++}; # take account of the decimal point
         $columnSizes{$column} = $precision;

      }

     

   }
   
   $sth->finish;

   return \%columnSizes;
   


}


####################################################################
sub GetLCColumnNamesArrayRef{
####################################################################
# This class method retrieves the column names for a given table,
# and returns a reference to an array containing those names.  The
# names are all in lower case
#
# Usage : my $columnsArrayRef = Result->GetLCColumnNamesArrayRef(dbh=>$dbh);

    my ($class, %args) = @_;

    my $dbh = $args{'dbh'} || die "You must supply a database handle to GetLCColumnNamesArrayRef\n";
    my $table = $args{'table'} || $class;

    return (&_lcColumnNamesArrayRef($dbh, $table));

}

####################################################################
sub GetUCColumnNamesArrayRef{
####################################################################
# This class method retrieves the column names for a given table,
# and returns a reference to an array containing those names.  The
# names are all in upper case
#
# Usage : my $columnsArrayRef = Result->GetUCColumnNamesArrayRef(dbh=>$dbh);

    my ($class, %args) = @_;

    my $dbh = $args{'dbh'} || die "You must supply a database handle to GetUCColumnNamesArrayRef\n";
    my $table = $args{'table'} || $class;
    
    my $arrayRef = &_lcColumnNamesArrayRef($dbh, $table);

    foreach my $column (@{$arrayRef}){

	$column = uc($column);

    }

    return $arrayRef;

}

####################################################################
#
# CLASS METHODS that will be used for table maintenance
#
####################################################################

####################################################################
sub Truncate{
####################################################################
# This class method executes the DDL to truncate a table.

# Usage: 
#
# DBEST->Truncate(dbh=>$dbh);
#
# SMD_Table->Truncate(dbh=>$dbh,
#                     table=>$table);
#

    my ($class, %args) = @_;
    
    my $dbh = $args{'dbh'} || die "You must supply a database handle to Truncate.\n";
    
    my $table = $args{'table'} || $class;
    
    if(!$class->TableExists(dbh=>$dbh,
                            table_name=>$table)){
	
	die "$table is not a valid table name.\n";
	
    }

    my $statement = "truncate table ".$class->schema.".$table drop storage";

    $dbh->do($statement);
    
}

####################################################################
sub Analyze{
####################################################################
# This class method executes the DDL to analyze a table.  If a percent
# is passed, statistics are estimated, otherwise statistics are
# computed.

    my ($class, %args) = @_;
    
    my $dbh = $args{'dbh'} || die "You must supply a database handle to Analyze.\n";
    
    my $table = $args{'table'} || $class;
    
    if(!$class->TableExists(dbh=>$dbh,
                            table_name=>$table)){
	
	die "$table is not a valid table name.\n";
	
    }
    
    my $percent = $args{'percent'};
    
    my $statement = "analyze table ".$class->schema.".$table";
    
    if ($percent) {
	$statement .= " estimate statistics sample $percent percent";
    }else{
	$statement .= " compute statistics";
    }

    $dbh->do($statement);
    
}

####################################################################
sub DisableReferencedForeignKeys{
####################################################################
# This class method disables all the referenced foreign keys for a
# table.
#
# Usage :
#
# DBEST->DisableReferencedForeignKeys(dbh=>$dbh);
#
# SMD_Table->DisableReferencedForeignKeys(dbh=>$dbh,
#                                         table=>$table);
#

    my ($class) = shift;
    
    $class->_alterReferencedForeignKeys(@_,
					action=>"disable");
    
}

######################################################################
sub EnableReferencedForeignKeys{
######################################################################
# This class method enables all the referenced foreign keys for a
# table.
#
# Usage :
#
# DBEST->EnableReferencedForeignKeys(dbh=>$dbh);
#
# SMD_Table->EnableReferencedForeignKeys(dbh=>$dbh,
#                                         table=>$table);
#

    my ($class) = shift;
    
    $class->_alterReferencedForeignKeys(@_,
					 action=>"enable");
    
}
					 
######################################################################
sub _alterReferencedForeignKeys{
######################################################################
# This private class method enables or disables all the referenced
# foreign keys for a table.
#
# Usage :
#
# $class->_alterReferencedForeignKeys(@_,
#                                      action=>$action);
#
# where $action maye be "enable" or "disable"
#

    my ($class, %args) = @_;
    
    my $dbh = $args{'dbh'} || die "You must supply a database handle.\n";
    
    my $table = $args{'table'} || $class;
    
    if(!$class->TableExists(dbh=>$dbh,
                            table_name=>$table)){
	
	die "$table is not a valid table name.\n";
	
    }

    my $action = $args{'action'} || die "An action must be passed to _modifyReferencedForeignKeys.\n";
    
    my $referencedFkNamesHashRef = $class->ReferencedFkNamesHashRef(dbh=>$dbh,
								    table=>$table);
    
    foreach my $refTable (keys %{$referencedFkNamesHashRef}){
	
	foreach my $constraint (@{$$referencedFkNamesHashRef{$refTable}}){

	    my $statement = "alter table ".$class->schema.".$refTable $action constraint $constraint";
	   
	    print $statement, "\n";
 
	    $dbh->do($statement);

	}

    }

}


####################################################################
sub ReferencedFkNamesHashRef{
####################################################################
# This routine populates a hash of arrays of referenced foreign keys
# associated with a table, where the key is the table name, and the
# value is an array of the foreign keys.  This hash is then returned
# by reference.  This routine is used to get the foreign key names
# that must be disabled before the table can be truncated.

# Usage : my $hashRef = DBEST->ReferencedFkNamesHashRef(dbh=>$dbh);

    my ($class, %args) = @_;

    my $dbh = $args{'dbh'} || die "You must supply a database handle to ReferencedFkNamesHashRef.\n";

    my $table = $args{'table'} || $class;

    $table = uc($table);

    if(!$class->TableExists(dbh=>$dbh,
                            table_name=>$table)){

	die "$table is not a valid table name.\n";

    }
    
    my $sth = $dbh->prepare("SELECT a.table_name, a.constraint_name  
                             FROM all_constraints a, all_cons_columns b, all_cons_columns c
                             WHERE a.constraint_name = b.constraint_name
                             AND a.owner = b.owner
                             AND c.constraint_name = a.r_constraint_name
                             AND b.position = c.position
                             AND a.constraint_type = 'R'
                             and c.owner = ?
                             and c.table_name = ?");

    $sth->execute($class->schema, $table);

    my %referencedFkNames;

    while (my($table_name, $constraint_name) = $sth->fetchrow_array) {

	push (@{$referencedFkNames{$table_name}}, $constraint_name);

    }

    $sth->finish;

    return \%referencedFkNames;   

}

####################################################################
sub MakeAllIndexesUnusable{
####################################################################
# This class method makes the indexes for a table unusable.  This is
# useful when wanting to load new data into a table via direct load
# in sqllder, but doesn't require the indexes to be dropped.
#
# Usage :
#
# DBEST->MakeAllIndexesUnusable(dbh=>$dbh);
#
# SMD_Table->MakeAllIndexesUnusable(dbh=>$dbh,
#                                   table=>$table);
#

    my ($class) = shift;

    $class->_alterAllIndexes(@_,
			     action=>"unusable");

}

####################################################################
sub RebuildAllIndexes{
####################################################################
# This class method rebuilds the indexes associated with
# a table.
#
# DBEST->RebuildAllIndexes(dbh=>$dbh);
#
# SMD_Table->RebuildAllIndexes(dbh=>$dbh,
#                              table=>$table);
#
    
    my ($class) = shift;
    
    $class->_alterAllIndexes(@_,
			     action=>"rebuild");

}

####################################################################
sub _alterAllIndexes{
####################################################################
# This class method can be used to make all indexes unusable on a table
# or to rebuild them

    my ($class, %args) = @_;

    my $dbh = $args{'dbh'} || die "You must supply a database handle.\n";

    my $table = $args{'table'} || $class;

    if(!$class->TableExists(dbh=>$dbh,
                            table_name=>$table)){

	die "$table is not a valid table name.\n";

    }

    my $action = $args{'action'} || die "You must supply an action to _alterAllIndexes.\n";

    my $indexesArrayRef = $class->AllIndexNamesArrayRef(dbh=>$dbh,
							table=>$table);

    foreach my $index (@{$indexesArrayRef}){

	if ($action eq "rebuild"){

	    $dbh->do("alter index ".$class->schema.".$index rebuild nologging");
	    
	}elsif ($action eq "unusable"){
	    
	    $dbh->do("alter index $index unusable");
	    
	}else{

	    die "$action is not supported in _alterIndexes.\n";

	}

    }

}   

####################################################################
sub AllIndexNamesArrayRef{
####################################################################
# This class method returns an array, by reference, of all indexes
# associated with a table and in the current schema.  It can either be
# called directly on a real table, or on a superclass, with a table
# argument
#
# Usage : 
#
# my $indexNamesArrayRef = Experiment->AllIndexNamesArrayRef(dbh=>$dbh);
#
# my $indexNamesArrayRef = SMD_Table->AllIndexNamesArrayRef(dbh=>$dbh,
#                                                        table=>'Experiment');
#

    my ($class, %args) = @_;

    my $dbh = $args{'dbh'} || die "You must supply a database handle to AllIndexNamesArrayRef.\n";

    my $table = $args{'table'} || $class;

    $table = uc($table);

    if(!$class->TableExists(dbh=>$dbh,
                            table_name=>$table)){

	die "$table is not a valid table name.\n";

    }

    return $dbh->selectcol_arrayref("SELECT index_name
                                     FROM all_indexes  
                                     WHERE owner = ?
                                     AND table_name = ?
                                     ORDER BY index_name", undef, $class->schema, $table);

}

####################################################################
#
#   INSTANCE METHODS
#
####################################################################

####################################################################
sub AUTOLOAD{
####################################################################
# The AUTOLOAD method is called by perl if a method canot be resolved.
# For objects that inherit from DB_Table, it will be used to handle
# all update methods.

# In addition, it will be used to handle all column accessors, which can
# be called in a case insensitive fashion

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

    my $table = ref($self);
    
    # get name of requested attribute (NB $AUTOLOAD contains method name),
    # check if it's an accessible attribute, and if so update it
 
    if ($AUTOLOAD =~ /.*::update(\w+)/ && exists($columnsHash{$table}->{lc($1)})){
    
	$self -> _updateStatement($1, $value);

	return;

    }elsif($AUTOLOAD =~ /.*::(\w+)/ && exists($columnsHash{$table}->{lc($1)})){

	my $attr_name = "_".lc($1);

	# add entry into symbol table for subsequent use of method

	no strict "refs";

	*{$AUTOLOAD} = sub { return $_[0]->{$attr_name} };

	use strict "refs";

	return $self->{$attr_name};

    }

    # if we get here, then the method couldn't be autoloaded

    confess "No such method: $AUTOLOAD";

}

#######################################################################
sub _updateStatement{
#######################################################################
# This private instance method stores the changed column and its new
# value in a hash for later use to prepare an oracle update statement

    my ($self, $column, $value) = @_;

    if ($self->_isPrimaryKey($column)){
       croak "The column $column you want to update is part of the primary key for the ", ref($self), " table.";
    }

    if (!$self->_isNULLable($column) && !defined($value)){
       croak "The column $column you want to update is NOT NULLABLE - you must provide a value.\n";
    }

    if (lc($column) eq "modified_by"){
	warn "You cannot update the modified_by column.  This is done for you\n";
	return;
    }

    if (lc($column) eq "date_modified"){
	warn "You cannot update the date_modified column.  This is done for you\n";
	return;
    }

    # if we get here, then the update should be good
    # do we want to check column size???
    ## --- put column and value in the hash %changeColumns

    $self->{'_changeColumns'}->{$column} = $value;   

}

######################################################################
sub _isPrimaryKey{
######################################################################
# This private method returns a boolean to indicate if a column form
# part of the primary key

    my ($self, $column) = @_;
    
    my $table = ref($self);

    my $sth = $self->dbh->prepare("SELECT column_name
                                          FROM all_cons_columns a, all_constraints b
                                          WHERE a.OWNER = b.OWNER
                                          AND a.TABLE_NAME = b.TABLE_NAME 
                                          AND a.CONSTRAINT_NAME = b.CONSTRAINT_NAME
                                          AND a.OWNER = ?
                                          AND a.TABLE_NAME = ?
                                          AND COLUMN_NAME = ?
                                          AND CONSTRAINT_TYPE ='P'");

    $sth->execute($self->schema, uc($table), uc($column));

    my $isPrimary = $sth->fetchrow;

    $sth->finish;

    return (defined($isPrimary));

}

######################################################################
sub _isNULLable{
######################################################################
# This private method returns a boolean to indicate whether a column
# is NULLABLE

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

    my $table = ref($self);

    my $sth = $self->dbh->prepare("SELECT NULLABLE
                                          FROM all_tab_columns
                                          WHERE OWNER = ?
                                          AND TABLE_NAME = ?
                                          AND COLUMN_NAME = ?");

    $sth->execute($self->schema, uc($table), uc($column));
     
    my $nullAble = $sth->fetchrow();
    
    $sth->finish;

    return ($nullAble eq "Y");

}

########################################################################
sub schema{
########################################################################
# This method returns the schema which should be queried for data.  It
# is hard-coded to be CGM_DDB.  To over-ride this behaviour, and query a
# different schema, your subclass should overide this method.  This is
# probably best done with a generic subclass, eg Tissue_Table could
# overide the method so the Tissue schema is queried, then all objects
# that subclass Tissue_table should simply work.

# Note that this can be used either as an instance, or a class method.

    return $ENV{'DBUSER'};

}

########################################################################
sub enterUpdates{
########################################################################
# This method enters all the updates that a client has requested, but
# does not commit them - this is the responsibility of the client.  In
# addition, it will update the DATE_MODIFIED to SYSDATE and the
# MODIFIED_BY to the passed in user id, if those columns exist

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

    my ($set_statement, @values);
    
    my $table = ref($self);
    
    # first construct the statement
    
    foreach my $column (keys %{$self->{'_changeColumns'}}){
	$set_statement .= ", $column = ?";
	push (@values, $self->{'_changeColumns'}{$column});
	
    }

    # check literals column such as 'DATE_MODIFIED', 'MODIFIED_BY'... 
    
    if (exists($columnsHash{$table}{'date_modified'})){
	$set_statement .= ", DATE_MODIFIED = SYSDATE";
    }

    if (exists($columnsHash{$table}{'modified_by'})){
	
	if (defined($user)){
	    $set_statement .= ", MODIFIED_BY = ?";
	    push(@values, $user);
	}else{
	    croak "You must supply a userid when updating a table with a modified_by column";
	}
    }

    # add in the rest of the bind variables

    push (@values, @{ $self->{'_whereValue'} });

    # fix up the set statement

    $set_statement =~ s/^\, //;

    # now let's do it!
    
    my $sth = $self->dbh->prepare("UPDATE ".$self->schema.".$table
                                          SET $set_statement
                                          WHERE $self->{'_whereClause'}");

    eval{
	
	my $rows = $sth->execute(@values);
	
	# this is a sanity check to safeguard against future bugs
	# that could creep into DB_Table - only 1 row should ever be
	# updated
	
	if($rows != 1){
	    die "$rows rows have updated for $table. Please check it.";
	}
		
    };
        
    if($@){

	die ($@); # simply propogate error to client
	
    }else{

	# we were successfull, so refresh some elements
	# of the object, and then empty the hash that records
	# unentered changes
	
	$self->_updateSelf($user);
	$self->_emptyChangesHash;

    }

    $sth->finish;

}

########################################################################
sub _updateSelf{
########################################################################
# This private method bring the object data up to date after a user has committed
# some updates for that row to the database.
   
    my ($self, $user) = @_;
    
    # fix up modified_by, and date_modified, if they exist
    
    exists ($self->{"_modified_by"}) && ($self->{"_modified_by"} = $user);
    
    if (exists ($self->{"_date_modified"})){ 
	$self->{"_date_modified"} = `date`;
	chomp $self->{"_date_modified"};
    }
	
    # now change the other columns

    foreach my $column (keys %{$self->{'_changeColumns'}}){
	
	$self->{"_".lc($column)} = $self->{'_changeColumns'}->{$column};
	
	
    }
 
}

########################################################################
sub rollbackUpdate{
########################################################################
#  This method allows a client to remove any changes that they made,
# providing that they haven't yet been committed
#
  
    $_[0]->_emptyChangesHash

}

########################################################################
sub _emptyChangesHash{
########################################################################
# This method simply removes all the changed column and their values
# in in the changeColumns hash

    my ($self) = @_;
   
    %{$self->{'_changeColumns'}} = ();

}

########################################################################
sub delete {
########################################################################
#  This method is used to delete the single row to which an object
#  pertains

    my ($self) = @_;

    my $table = ref($self);

    if(!$table){
       croak "Can't find object to support this delete method.";
    }

    my $sql = "DELETE FROM ".$self->schema.".$table
               WHERE $self->{'_whereClause'}";  
   
    my $sth = $self->dbh->prepare($sql); 
    
    $sth->execute(@ {$self->{'_whereValue'} });
 
    $sth->finish;

    # probably here we need to undef the object in some way, as it
    # is no longer reasonable to expect the object to do anything
    # further

}

#####################################################################
sub getLCColumnNamesArrayRef{
#####################################################################
# This method simply gets the names of the all the columns within a
# table, from which an object is contructed.  It returns a reference
# to an array with the column names.  Note the column names are ALWAYS
# lowercased

    my ($self) = @_;

    return (&_lcColumnNamesArrayRef($self->dbh, ref($self)));

}

#####################################################################
sub hasUnenteredUpdates{
#####################################################################
# This method returns a boolean to indicate if any updates have been 
# made to the object that have not yet been entered

    my ($self) = @_;

    return (scalar (keys %{$self->{'_changeColumns'}}) > 0);

}

sub dbh { $_[0]->{_dbh} } # returns the database handle belonging to the object 

#####################################################################
sub DESTROY{
#####################################################################
# The destroy method should check to see if they have any uncommitted
# changes, and at least print a warning if they do

    my $self = shift;

    if ($self->hasUncommittedUpdates){

	warn ("You had uncommitted updates in your ", ref($self), " object\n");

    }

}

#####################################################################
#
# Additional private subroutines
#
#
#####################################################################

#####################################################################
sub _lcColumnNamesArrayRef{
#####################################################################
# This private subroutine returns a reference to an array containing
# the names of columns for a particular table

    my ($dbh, $table) = @_;

    my (@columns, $column, $type);

    my $sth = $dbh->prepare("SELECT column_name, data_type
                                    FROM all_tab_columns
                                    where table_name = ?");

    $sth->execute(uc($table));

    while(($column, $type) = $sth->fetchrow){

        if ($type ne 'CLOB') {
          push (@columns, lc($column));
        }

    }

    $sth->finish;

    return \@columns;


}

1; # as ever, we should keep Perl happy

__END__

#####################################################################
#
#  POD Documentation from here on down
#
#####################################################################

=pod

=head1 Abstract

This perl library, DB_Table, acts as an abstract base class for any
object that that corresponds to a table in an oracle database, and
provides generic methods that may be used to retrieve, delete, create
or update a single row in a particular table in the database.  It is
important to note that DB_Table will NOT eval statements that affect
the database, nor will it actually commit changes.  Thus it is up to
clients of objects that subclass DB_Table to check for errors, and act
appropriately upon their detection.  This is the precise reason that
DB_Table forces the client to deal with errors - so that it can
decide how to handle them.  Examples will be given below.  The
following methods exists:

=head1 Instance Methods:

=head2 new

This 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.  eg, consider creation of an
experiment object, where the primary key is exptid, and unique keys
exist on the columns exptname and slidename.  If an Experiment object,
that subclasses DB_Table has been created (actually Experiment
subclasses SMD_Table, which itself is a subclass of DB_Table), the
following may be used to instantiate an object:

    my $experiment = Experiment->new(dbh=>$dbh,
				     exptid=>3);

OR

    my $experiment = Experiment->new(dbh=>$dbh,
				     slidename=>'y802a45');

OR

    my $experiment = Experiment->new(dbh=>$dbh,
				     exptname=>'Nitrogen starvation - 2 week');


Any of the above will instantiate the same experiment object.

Any table in the database can subclass DB_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 result table, you would do it thus:

    my $result = Result->new(dbh=>$dbh,
			     exptid=>3,
			     spot=>1);

thus you pass a value (which is named for the column to which it
corresponds) for each column that forms the key.  If you pass a
combination of columns that do not form a key, then the script will
die with an appropriate error message.  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.

In addition, there is an option which can be passed in called
'insensitive' which means that any text fields of the key being used
will be queried in a case insensitive fashion.  Note that if the
result of the query returns more than one row, only the first row
returned will be used to construct the object - thus this option
should be used with care.

eg 

   my $locus = Locus->new(dbh=>$dbh,
                          locus_name=>'act1',
			  insensitive=>1);

will still construct an object, even though the feature_name is really ACT1.

=head2 Retrieving data from the object

DB_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 an experiment object, as described above,
we can then retrieve data from it like so:

    print "exptid = ", $experiment->exptid, "\n";
    print "channel 1 description = ", $experiment->ch1desc, "\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.  Future versions of DB_Table (or a generic subclass thereof, such
as SMD_Table, or dictyBase_Table) may support an aliasing system, that will
allow old name to alias to new ones, to ease such a transition.  This
has not been implemented yet.

=head2 Updating data

Any column in a table, with the exception of those that form the
primary key of the table, or the modified_by 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 channel 1 description of an
experiment thus:

       $experiment->updateCh1desc("Stanford Universal Reference");

You may update as many columns 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, DB_Table will not actually
commit the update - that is the responsibility of the client.  If the
table to which an object corresponds contains a modified_by column,
the enterUpdate method will expect, as a single argument, the userid
of the user making the updates. If this column does not exist in the
table, then the argument is not required.  Thus if we were updating a
few columns for an experiment, we may have the following:

	my $experiment = Experiment->new(dbh=>$dbh,
			   		 exptid=>100);

        $experiment->updateCh1Desc("Stanford Universal Reference");
	$experiment->updateCh2Desc("Human prostate cancer from patient X");

	eval {

	     $experiment->enterUpdates($userid);
	     $dbh->commit;

	};

	if ($@){

	   print "An error occurred : $@\n";
	   $dbh->rollback;

	}

The enterUpdates method will enter $userid in the modified_by column,
and SYSDATE in the date_modified column, if these columns exist in the
table.

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


        $experiment->updateCh1Desc("Stanford Universal Reference");
        $experiment->updateCh2Desc("Human prostate cancer from patient X");

	$experiment->rollbackUpdate;


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

There is currently an issue with the updating:

Even after calling an update method, the object will still return the
old value for that column, until you call enterUpdates, eg:

        print $experiment->ch1desc, "\n";

prints: 'blah'

then:

	$experiment->updateCh1Desc("cool");

	print $experiment->ch1desc, "\n";

Still prints 'blah'

then:

	eval {

	     $experiment->enterUpdates;
	     $dbh->commit;

	};

	if (!@){

	     print $experiment->ch1desc, "\n";

	}

Now prints 'cool'

This may be fixed in a future version of DB_Table.

=head2 delete

A row corresponding to an instantiated object can be deleted simply by
calling the delete method *IF* the subclass supports the delete
method.  By default, a subclass should not support the delete method,
and instead should print an error that it is not supported.  Only if
the implementer of the subclassing object wants to gives object
clients capabilities to delete, should the programmer allow it to
work.  Thus to delete a row in the result table, you could simply:

       eval{

		$result->delete;
		$dbh->commit;

       };

etc.  This obviously demonstrates why you may not want to support a
delete method, as deleting results one at a time is not a good idea!

=head2 schema

This method returns the schema that should be queried or used.  It is
hard-coded to be be the PROD schema, but this can be over-ridden by a
subclass, either a generic subclass, such as Tissue_Table, or a single
object which is not further subclassed itself.  Note that this can be
used as both a class, or an instance method.  In future versions of
DB_Table, this may disappear from DB_Table itself, and simply become a
stub method, that must be overridden.  This would require that class
methods are never called off of DB_Table itself.

Usage:

	my $schema = DB_Table->schema;

	my $schema = $experiment->scheme;

=head2 getLCColumnNamesArrayRef

This method simply gets the names of the all the columns within a
table, from which an object is contructed.  It returns a reference to
an array with the column names.  Note the column names are ALWAYS
lowercased.

Usage:

	my $columnsArrayRef = $experiment->getLCColumnNamesArrayRef;

=head2 dbh

This method returns the objects database handle - it is most often
used in implementation of objects that subclass DB_Table

Usage, eg.:

       $self->dbh->prepare($sql);

=head1 Class Methods

=head2 Insert

This Class method inserts a row of data into the oracle database, in a
table whose name is the same as the class.

The client is required to pass in a valid database handle, which has
permission to enter data into the table.  In addition the client needs
to pass in two keys whose values are hash references.  Each reference
will refer to a hash of column names, and their values.  One key will
be called 'literals', and is to be used for columns which will use
literal entries like SYSDATE, or use values from Oracle sequences.
The other key will be called 'binds', and will be for columns whose
values can be passed in as bind variables.  The client is expected to
pass in values for all not nulable columns in one of these two hashes
that are passed by reference.  No error checking is done by the class,
thus it is up to the client to catch any errors, and deal with them
appropriately.


eg:

Usage: 

    Experiment->Insert(dbh=>$dbh,
		       literals=>{exptid=>'EXPTID_SEQ.NEXTVAL',
				  date_modified=>'SYSDATE',
				  date_created=>'SYSDATE'},
		       binds=>{exptname=>$exptname,
			       organism=>$organism,
			       ... => ...});


                           
Usage: 

    Print->Insert(dbh=>$dbh,
		  literals=>{printid=>'PRINTID_SEQ.NEXTVAL',
			     date_modified=>'SYSDATE',
			     date_created=>'SYSDATE'},
		  binds=>{printname=>$printName,
			  printer=>$printer,
			  ... => ...});
  
etc.

=head2 DumpTable

This class method allows for a table to be dumped either to a file, to
a file handle, or otherwise to STDOUT.  In addition, the client may
specify only certain columns to be dumped, and also provide a hash of
filters, eg:

    Result->DumpTable(dbh=>$dbh,
		      file=>$file,
		      columns=>\@columns,
		      filters=>{"exptid =" => "10",
				"ch1 >"    => "150",
				"log_rat2n is not null" => undef},
		      order_by=>["log_rat2n desc", "spot asc"],
		      cached=>1);

Note that the hash of filters has the column name AND the operator as
the key, and then the value to which the column contents is being
prepared as the value.  If, as in the last filter, you are requesting
something that does not exactly have a value (like NULL), you MUST put
the whole phrase as the key, and undef as the value.

Another example:

    Experiment->DumpTable(dbh=>$dbh,
			  handle=>\*OUT,
			  seperator=>"<:>",
			  eol=>":<>:",
			  headings=>1);

In addition, this method may be invoked on DB_Table itself (or indeed
a subclass of it), but in this case, you must supply an additional
'table' argument:

    DB_Table->DumpTable(dbh=>$dbh,
			handle=>\*OUT,
			seperator=>"<:>",
			eol=>":<>:",
			headings=>1,
			table=>'Experiment');

This is useful if you want to dump out many tables, and instead do not
have to use a module for each one, but just pass in different table
arguments.  eg :

    foreach $table (@tables){

	DB_Table->DumpTable(dbh=>$dbh,
			    table=>$table);

    }

Acceptable hash keys:

    dbh 	:   database handle

    columns	:   a reference to an array of columns to select, if no
		    columns argument is provided, then all columns
		    will be dumped.

    filters	:   a reference to a hash, that contains the desired filters
		    in the format described above.

    file	:   the name of the file that you want the data dumped to

    handle	:   a file handle, passed in by reference, where you want the
		    data dumped to.

If no file or handle argument are passed in, then the data will be
dumped to STDOUT.

    seperator   : the string to use to seperate columns in the output.  If
	          none is passed in, then tab, '\t', will be used.

    eol		: the string to use to seperate rows in the output.  If none
		  is passed in, the new line, '\n', will be used.

    headings    : if the value '1' is passed in for headings, then the first line
		  printed will be the column names, in the order that they
		  are printed.

    cached      : if the value '1' is passed in for 'cached' then the sql
		  statement used to select the data will be cached.  This is
		  useful if you are going to re-execute the same statement
		  many times, but with different values for the filters, eg
		  dumping the result table for different experiment ids.

    table       : if you invoke the DumpTable method from a class that
		  does not actually correspond to real table, eg
		  DB_Table, then you can supply the name of the table
		  that you really want dumped, using the table argument.

=head2 LoadTable

This class method allows for a file to be loaded into a database
table, using DBI, and acts as a counterpart for DumpTable.  It expects
to either get an array of columns, in the order that the data exist in
the file, or for the file to contain the column names as the first
line, which will be assumed if an array of columns is not passed in.
If a seperator, and end-of-line character are not passed in, then they
will be assumed to be '\t' and '\n' respectively.

examples:

    Experiment->LoadTable(dbh=>$dbh,
			  file=>$file,
			  columns=>\@columns);

    Experiment->LoadTable(dbh=>$dbh,
			  file=>$file,
			  seperator=>"<:>",
			  eol=>":<>:",
			  cached=>1);

if no columns argument is provided, column names must be on the first line
if no column seperator is provided, then \t will be used
if no record seperator is provided, then \n will be used
if the value '1' is passed in for 'cached' then the sql statement
used to select the data will be cached.  This is useful if you are
going to re-execute the same statement many times, but with
different files.

In addition, this method may be invoked on DB_Table itself (or indeed a subclass of it, but
in this case, you must supply an additional 'table' argument:

    DB_Table->LoadTable(dbh=>$dbh,
                        file=>$file,
			seperator=>"<:>",
			eol=>":<>:",
			table=>'Experiment');

This is useful if you want to load many tables, and instead do not have
to use a module for each one, but just pass in different table arguments.
 
 eg :

    foreach $table (@tables){

        DB_Table->LoadTable(dbh=>$dbh,
                            table=>$table);

    }


=head2 GetValueCaseInSensitiveArrayRef

This class method returns a reference to an array that contains all
matches to a query for a column, using case insensitive matching.
Thus:

    my $arrayRef = Experiment->GetValueCaseInSensitiveArrayRef(dbh=>$dbh,
							       exptname=>$exptname);

will do a case insensitive search in the experiment table (the calling
Class), on the exptname column, using the value $exptname, and return
a reference to an array containing any matches.  If there are no
matches, the reference to an array will still be returned, and it is
up to the client to check if any matches were found or not.

=head2 DistinctValuesByColumnArrayRef

This method returns a reference to an array that contains the distinct
values for a column in a table, which may be ordered by either the
same or another column.  This should be considered a semi-private
method, in that it should be used by subclassing objects, but not by
their clients - thus subclasses should provide more appropriately
named wrappers to this method.

General usage is as follows:

    my $arrayRef = Experiment->DistinctValuesByColumnArrayRef(dbh=>$dbh,
							      column=>'printname',
							      order_by=>'printname');  

where order_by is an optional argument.  Rather than having a client
call this method directly, a subclass should provide an appropriate
wrapper, for instance the Plate Object provides the following wrapper:

AllPlatePrefixesSortedArrayRef

which returns a reference to an array that contains all the
plateprefixes in case-insensitive sorted order.  It is used like this:

    my $prefixesArrayRef = Plate->AllPlatePrefixesSortedArrayRef(dbh=>$dbh);

and is implemented thus (initial set up left out):

    return (Plate->DistinctValuesByColumnArrayRef(dbh=>$dbh,
					          column=>'plateprefix',
					          order_by=>'plateprefix'));


By providing a wrapper function, it is obvious to the client of the
subclass exactly what they are getting, and makes the client code much
more readable.

=head2 GetNotNullableHashRef

This class method returns a reference to a hash, which contains as
keys all the columns names which are not nullable in a table.
The column names are uppercased

Usage: 

    my $notNullableHashRef = Print->GetNotNullableHashRef(dbh=>$dbh);

=head2 TableExists

This Class method returns a boolean to indicate if a table exists in
the current schema with the passed in name.


Usage:

	if(SMD_Table->TableExists(dbh=>$dbh,
		                  table_name=>'Experiment')){
		#blah   
   
	}

=head2 ColumnExistsInTable

This Class method returns a boolean to indicate whether the specified
column exists in the specified table in the current schema.

Usage:

	if(SMD_Table->ColumnExistsInTable(dbh=>$dbh,
		                          table_name=>'Experiment',
					  column_name=>'exptid')){
		#blah# 

	}

=head2 ColumnSizesHashRef

This class method retrieves all column sizes for a given table, and
returns a reference to a hash containing those column names as keys
and column widths as the values.  Because the information retrieved
from the database does not necessarily map directly to a column width,
this method does a little bit of massaging of the data:

if the DATA_TYPE is NUMBER, the width will be the DATA_LENGTH,
otherwise, the width will be the the PRECISION, or the PRECISION + 1,
if the column has an associated scale.

Usage : 

      my $columnSizeHashRef = Result->ColumnSizesHashRef(dbh=>$dbh);

=head2 GetLCColumnNamesArrayRef

This class method retrieves the column names for a given table, and
returns a reference to an array containing those names.  The names are
all in lower case

Usage : 

      my $columnsArrayRef = Result->GetLCColumnNamesArrayRef(dbh=>$dbh);

      my $columnsArrayRef = SMD_Table->GetLCColumnNamesArrayRef(dbh=>$dbh,
								table=>$table);

=head2 GetUCColumnNamesArrayRef

This method does exactly the same as GetLCColumnNamesArrayRef, except
the column names are upper cased.

=head1 Class Methods for table maintenance

NB these methods will likely be moved out of DB_Table to a subclass,
ORACLE_Table, at some future time.  They should only be used by people
experienced with database maintenance and administration.  It may be
that a specific user is required when creating the database handle,
who has permission to execute some of these methods, depending on how
your database is set up.

=head2 Truncate

This class method executes the DDL to truncate a table.

Usage:

	DBEST->Truncate(dbh=>$dbh);

	SMD_Table->Truncate(dbh=>$dbh,
	                     table=>$table);

=head2 Analyze

This class method executes the DDL to analyze a table.  If a percent
is passed, statistics are estimated, otherwise statistics are
computed.

Usage :	

      DBESTLib->Analyze(dbh=>$dbh);

      SMD_Table->Analyze(dbh=>$dbh,
			 table=>$table,
			 percent=>20);

=head2 DisableReferencedForeignKeys

This class method disables all the referenced foreign keys for a
table.

Usage :

      DBEST->DisableReferencedForeignKeys(dbh=>$dbh);

      SMD_Table->DisableReferencedForeignKeys(dbh=>$dbh,
		                              table=>$table);

=head2 EnableReferencedForeignKeys

This class method enables all the referenced foreign keys for a
table.

Usage :

      DBEST->EnableReferencedForeignKeys(dbh=>$dbh);

      SMD_Table->EnableReferencedForeignKeys(dbh=>$dbh,
		                             table=>$table);

=head2 ReferencedFkNamesHashRef

This class method populates a hash of arrays of referenced foreign keys
associated with a table, where the key is the table name, and the
value is an array of the foreign keys.  This hash is then returned
by reference.  This routine is used to get the foreign key names
that must be disabled before the table can be truncated.

Usage : 

      my $hashRef = DBEST->ReferencedFkNamesHashRef(dbh=>$dbh);

=head2 MakeAllIndexesUnusable

This class method makes the indexes for a table unusable.  This is
useful when wanting to load new data into a table via direct load
in sqllder, but does not require the indexes to be dropped.

Usage :

      DBEST->MakeAllIndexesUnusable(dbh=>$dbh);

      SMD_Table->MakeAllIndexesUnusable(dbh=>$dbh,
	                                table=>$table);

=head2 RebuildAllIndexes

This class method rebuilds the indexes associated with a table.

Usage :

      DBEST->RebuildAllIndexes(dbh=>$dbh);

      SMD_Table->RebuildAllIndexes(dbh=>$dbh,
	                           table=>$table);


=head2 AllIndexNamesArrayRef

This class method returns an array, by reference, of all indexes
associated with a table and in the current schema.  It can either be
called directly on a real table, or on a superclass, with a table
argument

Usage : 

      my $indexNamesArrayRef = Experiment->AllIndexNamesArrayRef(dbh=>$dbh);

      my $indexNamesArrayRef = SMD_Table->AllIndexNamesArrayRef(dbh=>$dbh,
			                                        table=>'Experiment');



=head1 AUTHORS

Gavin Sherlock

sherlock@genome.stanford.edu

Heng Jin

hjin@genome.stanford.edu

=cut