# -------------------------------------------------------------------------------------
# lib::sql::Table
# -------------------------------------------------------------------------------------
#
#       Author : Jean-Michel Hiver (jhiver@cisedi.com).
#    Copyright : (c) Jean-Michel Hiver, 2000.
# 
#      Unauthorized modification, use, reuse, distribution or redistribution
#      of this module is stricly forbidden.
#
#    Description:
#
#      A unique interface that is designed to get rid of SQL as much as possible.
#      Performs necessary checks on the database and throw Exceptions for operations
#      that would undoubtely fail but uses a driver system for all database SQL
#      accesses.
#
#      See the POD documentation for more details.
#
# -------------------------------------------------------------------------------------
package lib::sql::Table;
use Data::Dumper;
use lib::Exception;
use lib::sql::Condition;
use lib::sql::Query;
use strict;
use vars qw /$LOADED $DATABASE @QUERY_STACK $QUERY_STACK_MAX/;

$LOADED = 0;
$DATABASE    = {};
@QUERY_STACK = ();
$QUERY_STACK_MAX = 20;


##
# $obj->driver;
# -------------
#   Imports into the current namespace the driver specific
# subroutines.
##
sub driver
{
    my $class  = shift;
    my $driver = shift;
    if ($driver eq "MySQL")
    {
	require lib::sql::MySQL;
	no strict;
	for (keys %lib::sql::MySQL::)
	{
	    *$_ = $lib::sql::MySQL::{$_} if ($_ !~ /[A-Z]+/);
	}
	use strict;
    }
    if ($driver eq "PostgreSQL")
    {
	require lib::sql::PostgreSQL;
	no strict;
	for (keys %lib::sql::PostgreSQL::)
	{
	    *$_ = $lib::sql::PostgreSQL::{$_} if ($_ !~ /[A-Z]+/);
	}
	use strict;
    }
    if ($driver eq "Disk")
    {
	require lib::sql::Disk;
	no strict;
	for (keys %lib::sql::Disk::)
	{
	    *$_ = $lib::sql::Disk::{$_} if ($_ !~ /[A-Z]+/);
	}
	use strict;
    }
}


#### ### ## #  M E T A D A T A  M E T H O D S  # ## ### ####


##
# __PACKAGE__->new ( name     => $table_name,
#                    pk       => [ $name1 ],
#                    cols     => [ { name => $name1, type => $type1 },
#                                  { name => $name2, type => $type2 } ],
#                    unique   => { $name1 => [ $col1, $col2 ] }
#                    index    => { $name2 => [ $col2 ] }
#                    fk       => { foreign_table => { source_col => target_col } }
#                    ai       => TRUE / FALSE
#         );
# ------------------------------------------------------
#   Constructs a new lib::sql::Table object.
#   Only the name attribute is mandatory all the time.
##
sub new
{
    my $class = shift;
    $class    = ref $class || $class;

    my $args  = { @_ };

    # initialize and blesses $self
    my $self  = bless { name   => undef,
			cols   => [],
			unique => {},
			index  => {},
			fk     => {},
			ai     => undef
		      }, $class;

    $self->{bless_into} = $args->{bless_into} if (exists $args->{bless_into});
    
    # sets the table name
    $self->{name} = $args->{name};
    (defined $self->{name}) or throw (new lib::Exception ( code => "NO_NAME_ATTRIBUTE",
							   info => "Cannot construct $class without a name attribute" ) );

    # sets the columns
    $args->{cols} ||= [];
    foreach my $col (@{$args->{cols}}) { $self->cols ($col->{name}, $col->{type}) }

    # sets the primary key
    $args->{pk} ||= [];
    $self->pk ($args->{pk});

    # sets the uniques
    $args->{unique} ||= {};
    foreach my $unique_name (keys %{$args->{unique}}) { $self->unique ($unique_name, $args->{unique}->{$unique_name}) }

    # sets the indexes
    $args->{index} ||= {};
    foreach my $index_name (keys %{$args->{index}}) { $self->index ($index_name, $args->{index}->{$index_name}) }

    # sets the foreign keys
    $args->{fk} ||= {};
    foreach my $foreign_name (keys %{$args->{fk}}) { $self->fk ($foreign_name, $args->{fk}->{$foreign_name}) }
    
    # sets the auto-increment
    $args->{ai} ||= 0;
    $self->ai ($args->{ai});
    
    $self->{selectbox} = $args->{selectbox} || undef;
    $DATABASE->{$self->name} = $self;
    return $self;
}


sub create_all { foreach my $table (values %{$lib::sql::Table::DATABASE}) { $table->create } }
sub drop_all   { foreach my $table (values %{$lib::sql::Table::DATABASE}) { $table->drop } }


##
# $__PACKAGE__->table ('A_Table');
# --------------------------------
#   Returns the table object with 'A Table'
#   name.
##
sub table
{
    my $class = shift;
    $class = ref $class || $class;
    
    if    (@_ == 0) { return keys %{$DATABASE}     }
    elsif (@_ == 1) { return $DATABASE->{shift()}  }
    else            { $DATABASE->{shift()} = shift }
}


##
# __PACKAGE__->save_state;
# ------------------------
#   This method saves the database state in a file
#   called database.def.
#
# __PACKAGE__->save_state ($driver, $file);
# -----------------------------------------
#   This method saves the database state in a file
#   called $file.
##
sub save_state
{
    my $class = shift;
    if (ref $class)
    {
	my $self = $class;
	my $file = shift;
	$class = ref $class;
	$file .= '/' . $self->name . '.def';
	open FP, ">$file";
	print FP Dumper ($self);
	close FP;
    }
    else
    {
	my $dir = shift || 'defs';
	for ($class->table) { $DATABASE->{$_}->save_state ($dir) }
    }
}


##
# __PACKAGE__->load_state;
# ------------------------
#   This method loads the database state from database.def
#   and initializes all the objects.
#
# __PACKAGE__->load_state ($file);
# --------------------------------
#   This method loads the database state from $file
#   and initializes all the objects.
##
sub load_state
{
    my $class = shift;
    my $dir = shift || 'defs';
    
    $LOADED and return;
    
    # try to load driver first
    my $path = "$dir/driver.pl";
    open FP, "<$path" or
	throw (new lib::Exception ( code => 'CANNOT_LOAD_STATE',
				    info => "Can't read-open $path" ) );
    my $data = join '', <FP>;
    close FP;
    eval $data;
    ($@) and
	throw (new lib::Exception ( code => 'CANNOT_LOAD_STATE',
				    info => $@ ) );
    
    # loads each definition file
    opendir DD, $dir or
	throw (new lib::Exception ( code => 'CANNOT_LOAD_STATE',
					info => "Can't open $dir directory" ) );
    my @files = readdir (DD);
    closedir DD;
    
    while (my $file = shift (@files))
    {
	$file eq '.'  and next;
	$file eq '..' and next;
	
	my $path = $dir . '/' . $file;
	(-d $path) and next;
	
	if ($file =~ /\.def$/)
	{
	    open FP, "<$path" or
		throw (new lib::Exception ( code => 'CANNOT_LOAD_STATE',
					    info => "Cannot read-open $path" ) );
	    my $data = join '', <FP>;
	    close FP;
	    
	    my $VAR1;
	    eval $data;
	    ($@) and
		throw (new lib::Exception ( code => 'CANNOT_LOAD_STATE',
					    info => $@ ) );
	    $DATABASE->{$VAR1->name} = $VAR1;
	}
    }
    
    $LOADED = 1;
}


##
# $obj->name;
# -----------
#   Return this object's name.
##
sub name { my $self = shift; return $self->{name} }


##
# $obj->not_null;
# ---------------
#   Returns all the column names which are
#   not null.
#
# $obj->not_null ($col1, $col2, ..., $coln);
# ------------------------------------------
#   Sets the columns which have to be not
#   null.
##
sub not_null
{
    my $self = shift;
    if (@_ == 0)
    {
	my @res = ();
	foreach my $col ($self->cols)
	{
	    if ($self->cols ($col)->not_null)
	    {
		push @res, $col;
	    }
	}
	return wantarray ? @res : \@res;
    }
    else
    {
	for (@_)
	{
	    $self->cols ($_) or throw (new lib::Exception ( code => "NO_SUCH_COLUMN",
							    info => "column $_ does not exist" ) );
	}
	my $set = { map { $_ => 1 } @_ };
	foreach my $col ($self->cols)
	{
	    my $type = $self->cols ($col);
	    if ($set->{$col}) { $type->not_null (1) }
	    else              { $type->not_null (0) }
	}
    }
}


##
# $obj->referencing_tables;
# -------------------------
#   Returns all the table names matching the tables that
#   reference $self.
##
sub referencing_tables
{
    my $self = shift;
    my $name = shift || $self->name;

    my %result = ();
    foreach my $table_name (keys %{$DATABASE})
    {
	foreach my $referenced_table ($DATABASE->{$table_name}->fk)
	{
	    if ($referenced_table eq $name)
	    {
		$result{$table_name} = 1;
		last;
	    }
	}
    }
    return wantarray ? keys %result : [ keys %result ];
}


##
# $obj->pk;
# ---------
#   Returns the primary key as an array or arrayref.
#
# $obj->pk ($array or $arrayref);
# -------------------------------
#   Sets the primary key.
##
sub pk
{
    my $self = shift;
    if (@_ == 0) { return wantarray ? @{$self->{pk}} : $self->{pk} }
    else
    {
	# if there is only one argument, then it should be an
	# array ref with the list of all the columns that have
	# to be part of the primary key.
	if (@_ == 1)
	{
	    my $arg = shift;

	    # if it's a reference, then it must be an array ref.
	    # if is's not a reference, then it must be a scalar
	    # (non-composite primary key)
	    if (ref $arg)
	    {
		# let us check that it's an array
		(ref $arg eq "ARRAY") or
		    throw (new lib::Exception ( code => "BAD_ARGUMENTS",
					        info => "argument for PK is not an ARRAY reference" ) );

		my @pk = @{$arg};

		# let us check that each column exists and is not null
		foreach my $pk (@pk)
		{
		    my $col = $self->cols ($pk);
		    unless (defined $col)
		    {
			throw (new lib::Exception ( code => "NO_SUCH_COLUMN",
						    info => "$pk column does not exist" ) );
		    }
		    unless ($col->not_null)
		    {
			throw (new lib::Exception ( code => "BAD_COLUMN_TYPE",
						    info => "$pk must be defined as not null" ) );
		    }
		}
		
		# if everything is ok then we set the new primary key
		$self->{pk} = $arg;
	    }
	    else { return $self->pk ( [ $arg ] ) }
	}
	else { return $self->pk (\@_) }
    }
    return 1;
}


##
# $obj->ai;
# ---------
#   Returns wether the pk is auto-increment or not
#
# $obj->ai ($boolean);
# --------------------
#   Sets auto-increment for the table primary key
#   provided that it has only one field that can
#   be auto-incremented.
##
sub ai
{
    my $self = shift;
    if (@_ == 0) { return $self->{ai} }
    elsif (@_ == 1)
    {
	my $boolean = shift;
	my @pk      = $self->pk;

	if ($boolean)
	{
	    unless (@pk == 1)
	    {
		# makes sure that the key ain't composite
		throw (new lib::Exception ( code => "AI_ON_COMPOSITE_PK",
					    info => $self->name . " cannot be auto-increment because its pk is composite." ) );
	    }
	    
	    # makes sure that the type can be auto_incremented
	    my $pk_type = $self->cols ($pk[0]);
	    
	    if ($pk_type->isa ("lib::sql::type::AbstractInt")) { $self->{ai} = 1 }
	    else
	    {
		throw (new lib::Exception ( code => "TYPE_NOT_AUTO_INCREMENTABLE",
					    info => $self->name . "cannot be auto-increment because $pk[0] is not auto-incrementable" ) );
	    }
	}
	else { $self->{ai} = undef }
    }
    else
    {
	throw (new lib::Exception ( code => "BAD_ARGUMENTS", 
				    info => "ai takes one boolean argument only" ) );
    }
}


##
# $obj->unique;
# -------------
#   Return all the indexes names for that Table.
#
# $obj->unique ($name);
# ---------------------
#   Return the array of columns that are indexed for
#   that unique.
#
# $obj->unique ($name, $arrayref);
# --------------------------------
#   Sets the unique index with $name to that $arrayref.
#
# $obj->unique ($name, $col1, ..., $coln);
# ----------------------------------------
#   Same thing.
##
sub unique
{
    my $self = shift;
    if (@_ == 0) { return wantarray ? keys %{$self->{unique}} : [ $self->unique ] }
    elsif (@_ == 1)
    {
	my $name  = shift;
	my $array = $self->{unique}->{$name};
	$array ||= [];
	return wantarray ? @{$array} : $array;
    }
    elsif (@_ == 2)
    {
	my $name  = shift;
	my $array = shift;

	# convert the argument to an array if necessary
	$array    = [ $array ] unless (ref $array eq "ARRAY");
	
	$self->{unique}->{$name} = $array;
    }
    else
    {
	my $name = shift;
	return $self->unique ($name, \@_);
    }
}


##
# $obj->index;
# ------------
#   Return all the indexes names for that Table.
#
# $obj->index ($name);
# --------------------
#   Return the array of columns that are indexed for
#   that index.
#
# $obj->index ($name, $arrayref);
# -------------------------------
#   Sets the index index with $name to that $arrayref.
#
# $obj->index ($name, $col1, ..., $coln);
# ---------------------------------------
#   Same thing.
##
sub index
{
    my $self = shift;
    if (@_ == 0) { return wantarray ? keys %{$self->{index}} : [ $self->index ] }
    elsif (@_ == 1)
    {
	my $name  = shift;
	my $array = $self->{index}->{$name};
	$array ||= [];
	return wantarray ? @{$array} : $array;
    }
    elsif (@_ == 2)
    {
	my $name  = shift;
	my $array = shift;
	
	# convert the argument to an array if necessary
	$array    = [ $array ] unless (ref $array eq "ARRAY");

	$self->{index}->{$name} = $array;
    }
    else
    {
	my $name = shift;
	return $self->index ($name, \@_);
    }
}


##
# $obj->fk;
# ---------
#   Returns all the table names that this table
#   references.
#
# $obj->fk ($foreign_table)
# -------------------------
#   Returns all the $self <-> $foreign table column
#   mapping as a hash or hashref depending upon the
#   context.
#
# $obj->fk ($foreign_table, $mapping);
# ------------------------------------
#   Sets the mapping from $self to $foreign_table.
##
sub fk
{
    my $self = shift;
    if (@_ == 0) { return wantarray ? keys %{$self->{fk}} : [ $self->{fk} ] }
    elsif (@_ == 1)
    {
	if (wantarray) { return %{$self->fk (shift())} }
	else           { return $self->{fk}->{shift()} }
    }
    else
    {
	my $foreign_name = shift;
	my $foreign = $self->table ($foreign_name) or
	    throw (new lib::Exception ( code => "NO_SUCH_TABLE",
					info =>
					"Cannot map to $foreign_name because $foreign_name " .
					"does not exist" ) );
	my $mapping = undef;
	if (ref $_[0]) { $mapping = shift  }
	else           { $mapping = { @_ } }
	
	# check that the mapping is correct
	foreach my $source_col (keys %{$mapping})
	{
	    my $target_col = $mapping->{$source_col};
	    
	    my $source_type = $self->cols ($source_col) or
		throw (new lib::Exception ( code => "NO_SOURCE_COLUMN",
					    info => "Source column $source_col does not exist" ) );
	    my $target_type = $foreign->cols ($target_col) or
		throw (new lib::Exception ( code => "NO_TARGET_COLUMN",
					    info => "Target column $target_col does not exist" ) );
	    ($source_type->equals ($target_type)) or
		throw (new lib::Exception ( code => "TYPES_NOT_EQUAL",
					    info =>
					    "$source_col and $target_col have different " .
					    "column types" ) );
	}

	# if the mapping is correct sets it
	$self->{fk}->{$foreign_name} = {};
	foreach my $key (keys %{$mapping})
	{
	    $self->{fk}->{$foreign_name}->{$key} = $mapping->{$key};
	}
    }
}


##
# $obj->cols;
# -----------
#   Returns all the column names, in the order that
#   they were created in.
#
# $obj->cols ($name);
# -------------------
#   Returns the column type for $name, undef if no
#   such column is defined.
#
# $obj->cols ($name, $type);
# --------------------------
#   Changes $type for $name, or appends column
#   $name, $type if $name is not yet defined,
#   or removes $name if $type is undefined.
##
sub cols
{
    my $self = shift;

    # returns the column names list
    if (@_ == 0) { return wantarray ? map { $_->{name} } @{$self->{cols}} : [ $self->cols ] }

    # returns the type for a given column
    if (@_ == 1)
    {
	my $col_name = shift;
	foreach my $col (@{$self->{cols}})
	{
	    return $col->{type} if ($col->{name} eq $col_name);
	}
	return; # undef if not found
    }

    # sets, modifies or deletes a column
    if (@_ == 2)
    {
	my $col_name = shift;
	my $col_type = shift;

	my $nb_cols  = @{$self->{cols}};

	# for all the columns that are defined
	for (my $i=0; $i < $nb_cols; $i++)
	{
	    my $col = $self->{cols}->[$i];

	    # if the column names are the same, then
	    if ($col->{name} eq $col_name)
	    {
		# if the type is defined then we wanna
		# change it.
		if (defined $col_type) { $col->{type} = $col_type }
		
		# else we wanna remove that column from
		# the column list
		else { splice (@{$self->{cols}}, $i, 1) }
	    }
	}

	# if none of the column matched the column name, then it
	# means that we wanna add a column.
	{
	    # if the type is not defined, then we must throw
	    # an exception
	    (defined $col_type)
		or throw (new lib::Exception ( code   => "TYPE_IS_NOT_DEFINED", 
					       info   => "Cannot add a column with undefined type" ) );
	    
	    # appends the column
	    push @{$self->{cols}}, { name => $col_name, type => $col_type }; 
	}
    }
    else
    {
	throw (new lib::Exception ( code   => "BAD_ARGUMENTS",
				    info   => "Cannot this subroutine with " . scalar @_ . " arguments" ) );
    }
    return 1;
}


##
# __PACKAGE__->query_stack;
# -------------------------
#   Returns the QUERY_STACK.
#
# __PACKAGE__->query_stack (LIST);
# --------------------------------
#   Pushes all these stacks to the current QUERY_STACK
#   global array.
##
sub query_stack
{
    my $class = shift;
    $class = ref $class || $class;

    if (@_ == 0) { return @QUERY_STACK   }
    else
    {
	while (@_)
	{
	    if (scalar @QUERY_STACK >= $QUERY_STACK_MAX) { shift (@QUERY_STACK) }
	    push @QUERY_STACK, shift;
	}
    }
}



#### ### ## # M E T H O D  W R A P P E R S # ## ### ####


##
# $obj->get ($id);
# ----------------
#   Returns the record which ID is $id, or undef if no records
#   are found.
#
# $obj->get ($condition);
# -----------------------
#   Returns the first record that matches $condition
##
sub get
{
    my $self = shift;
    if (@_ == 1)
    {
	if (ref $_[0])
	{
	    return $self->search (@_)->next
	}
	else
	{
	    my @pk = $self->pk;
	    (@pk != 1) and
		throw (new lib::Exception ( code => 'CANNOT_GET',
					    info => $self ) );
	    my $pk = $pk[0];
	    return $self->get ($pk => shift);
	}
    }
    else { return $self->search (@_)->next }
}


##
# $obj->count;
# ------------
#   Returns the number of records that this table handles.
#
# $obj->count ($condition);
# -------------------------
#   Returns the number of records that matches $condition.
##
sub count
{
    my $self = shift;
    if (@_)
    {
	my $condition = new lib::sql::Condition (@_);
	my $query = $self->select ( cols => [ qw /COUNT(*)/ ], where => $condition );
	return $query->next->{"COUNT(*)"};
    }
    else
    {
	my $query = $self->select ( cols => [ qw /COUNT(*)/ ] );
	return $query->next->{"COUNT(*)"};
    }
}


##
# $obj->search ($condition);
# --------------------------
#   Same as select, but selects all the columns from a given table.
##
sub search
{
    my $self = shift;
    if (@_ == 1) { return $self->select ( where => shift )  } 
    else         { return $self->select ( where => { @_ } ) }
}


##
# $obj->modify ($hash or $hashref);
# ---------------------------------
#   Modifies the record which primary key maches whatever
#   is specified in $hashref to the values of $hashref.
##
sub modify
{
    my $self = shift;
    my $modify = undef;
    if (ref $_[0]) { $modify = shift  }
    else           { $modify = { @_ } }
    $modify = $self->_to_hash ($modify);

    # if the current table has no primary keys, then
    # modify cannot be performed.
    my @pk = $self->pk;
    @pk or throw (new lib::Exception ( code => "NO_PRIMARY_KEY",
				       info =>
				       "The current record cannot be modified because " .
				       "its table has no pk." ) );
    
    # $modify may be a reference and is gonna be altered,
    # let us make a copy of it to work onto.
    $modify = { map { $_ => $modify->{$_} } keys %{$modify} };
    
    # builds the condition from the record and changes
    # the values.
    my $condition = { map { $_ => $modify->{$_} } @pk };

    # make sure that all the values in $condition are defined,
    # throw an exception otherwise.
    foreach my $field (keys %{$condition})
    {
	unless (defined $condition->{$field})
	{
	    throw (new lib::Exception ( code   => "INCOMPLETE_PK",
					info   => "One of the condition value is not defined for this modify: $field.") );
	}
    }
    
    return $self->update ($modify, $condition);
}


#### ### ## # D A T A  M E T H O D S # ## ### ####

##
# $obj->select ( { cols     => [ qw /col1 col2 col3/ ],
#                  where    => $condition,
#                  sort     => [ qw /col1 col2/ ],
#                  desc     => TRUE / FALSE,
#                  distinct => TRUE / FALSE,
#                  page     => [ $slice, $thickness ] }
# -----------------------------------------------------
#   Selects from the table. Condition can be a hash or a real condition
#   object.
##
sub select
{
    my $self  = shift;
    my $class = ref $self;
    
    my $args  = undef;
    if (ref $_[0] eq "CGI")     { $args = $self->_to_hash (shift()) }
    elsif (ref $_[0] eq "HASH") { $args = shift  }
    else                        { $args = { @_ } } 
    
    ### ## # the columns # ## ###
    my $cols = $args->{cols};
    
    ### ## # the condition # ## ###
    my $condition = undef;
    if (defined $args->{where})
    {
	# either it's a reference and thus a Condition
	if (ref $args->{where}) { $condition = $args->{where} }
	else
	{
	    # or it's a value for a pk and then we construct it
	    my $val = $args->{where};
	    my @pk = $self->pk;
	    if (@pk == 1)
	    {
		my $pk = shift (@pk);
		return $self->select ( cols => $cols, where => { $pk => $val } );
	    }
	    else
	    {
		# if the primary key is composite then we cannot build
		# the condition with a single value, raise an exception
		throw (new lib::Exception ( code => "COMPOSITE_PK" ) );
	    }
	}
    }
    else { $condition = new lib::sql::Condition }
    
    # converts the condition to a real lib::sql::Condition object
    if (ref $condition eq "CGI") { $condition = $self->_to_hash ($condition) }
    $condition = new lib::sql::Condition ($condition);
    
    ### ## # sort # ## ###
    my $sort = $args->{sort};
    if (defined $sort)
    {
	foreach my $col (@{$sort})
	{
	    unless (defined $self->cols ($col))
	    {
		throw (new lib::Exception ( code => "NO_SUCH_COLUMN",
					    info => "Cannot sort by $col: this column does not exist" ) );
	    }
	}
    }
    
    return $self->_select ($cols, $condition, $sort, $args->{distinct}, $args->{page}, $args->{desc});
}


##
# $obj->delete ($condition);
# --------------------------
#   Delete all the rows which match $condition.
#   Performs no foreign key checks.
##
sub delete
{
    my $self = shift;
    my $class = ref $self;
    my $condition = undef;
    if (ref $_[0] eq "CGI") { $condition = new lib::sql::Condition ($self->_to_hash (shift)) }
    else                    { $condition = new lib::sql::Condition ( @_ ) };
    my $condition_sql = $condition->to_sql;

    unless ($condition_sql)
    {
	throw (new lib::Exception ( code   => "NO_CONDITION",
				    info   => "delete cannot be called without a condition, use erase instead" ) );
    }

    $self->_delete ($condition);
}


##
# $obj->delete_cascade ($condition);
# ----------------------------------
#   Delete all the rows which match $condition,
#   eventually cascading.
##
sub delete_cascade
{
    my $self = shift;
    my $class = ref $self;
    my $condition = undef;
    if (ref $_[0] eq "CGI") { $condition = new lib::sql::Condition ($self->_to_hash (shift)) }
    else                    { $condition = new lib::sql::Condition ( @_ ) };
    my $condition_sql = $condition->to_sql;
    
    unless ($condition_sql)
    {
	throw (new lib::Exception ( code   => "NO_CONDITION",
				    info   =>
				    "delete_cascade cannot be called without a condition, ".
				    "use erase_cascade instead" ) );
    }
    
    $self->_delete_cascade ($condition);
}


##
# $obj->insert ($hashref);
# ------------------------
#   Insert record represented by $hash. Returns the
#   inserted auto-increment value if any.
##
sub insert
{
    my $self = shift;
    my $class = ref $self;
    my $insert = undef;
    if (ref $_[0]) { $insert = shift  }
    else           { $insert = { @_ } }
    $insert = $self->_to_hash ($insert);
    
    # first of all, check that the primary key is defined
    # unless it's an auto-increment field.
    unless ($self->ai)
    {
	foreach my $pk ($self->pk)
	{
	    unless (defined $insert->{$pk})
	    {
		throw (new lib::Exception ( code => "INCOMPLETE_PK",
					    info => "Cannot insert this record: $pk is not defined." ) );
	    }
	}
    }

    # then, we need to check that the not_null fields are defined,
    # unless this field is the auto-increment field.
    my $ai = "";
    if ($self->ai) { $ai = $self->pk->[0] }
    foreach my $col ($self->not_null)
    {
	unless ($ai eq $col)
	{
	    unless (defined $insert->{$col})
	    {
		throw (new lib::Exception ( code => "NOT_NULL",
					    info => "Cannot insert this record: $col is not defined" ) );
	    }
	}
    }
    
    # for each unique index, check that there is no record that
    # would trigger an insert failure.
    
    # first for the primary key...
    unless ($self->ai)
    {
	my @pk     = $self->pk;
	my $search = { map { $_ => $insert->{$_} } @pk };
        if ($self->search ($search)->next)
	{
	    throw (new lib::Exception ( code => "RECORD_ALREADY_EXISTS",
					info => "Cannot insert this record: primary key already exists",
					info_table => $self,
					info_pk    => $search ) );
	}
    }
    
    # and then for anything else
    foreach my $unique ($self->unique)
    {
	my @uk = $self->unique ($unique);
	my $search = { map { $_ => $insert->{$_} } @uk };
	
        if ($self->search ($search)->next)
	{
	    throw (new lib::Exception ( code => "RECORD_ALREADY_EXISTS",
					info => "Cannot insert this record: $unique exists" ) );
	}
    }
    
    return $self->_insert ($insert);
}


##
# $obj->update ($hashref, $condition);
# ------------------------------------
#   Sets all the rows that maches $condition
#   to the values specified in $hashref, and
#   returns the number of columns modified.
##
sub update
{
    my $self = shift;
    my $class = ref $self;
    my $name = $self->name;
    my $hashref = shift;
    
    # makes sure that all the columns to update exist,
    # throw an exception otherwise.
    foreach my $update_col (keys %{$hashref})
    {
	unless (defined $self->cols ($update_col))
	{
	    throw (new lib::Exception ( code => "NO_SUCH_COLUMN",
					info => "$update_col does not exist in $name" ) );
	}
    }
    
    my $condition = new lib::sql::Condition (shift);
    $self->_update ($hashref, $condition);
}


#### ### ## # P R I V A T E  M E T H O D S # ## ### ####


# converts the reference that's being passed in to a hash
# if necessary and returns the converted element.
# this subroutine is also used by lib::cgi::Admin.pm, so
# don't remove.
sub _to_hash
{
    my $self = shift;
    my $in   = shift;

    if (ref $in and ref $in ne "HASH" and $in->isa ('CGI'))
    {
	my $res = {};
	foreach my $col ($self->cols)
	{
	    foreach my $param ($in->param)
	    {
		if ($col eq $param)
		{
	    	    my $val = $in->param ($col);
	    	    if (defined $val and $val eq "") { $val = undef }
	    	    $res->{$col} = $val;
		}
	    }
	}
	return $res;
    }
    return $in;
}


1;
