SQL::Entity::Table - Database table abstraction

    use SQL::Entity::Table;
    use'SQL::Entity::Column ':all';

    my $table = SQL::Entity::Table->new(
        name => 'emp'
        columns => [sql_column(name => 'empno')]
    );

    my ($sql) = $table->query;

    my $dept = SQL::Entity->new(
        name    => 'dept',
        alias   => 'd',
        columns => [
            sql_column(name => 'deptno'),
            sql_column(name => 'dname')
        ],
    );

    my $emp = SQL::Entity->new(
        name                  => 'emp',
        primary_key           => ['empno'],
        columns               => [
            sql_column(name => 'ename'),
            sql_column(name => 'empno'),
            sql_column(name => 'deptno')
        ],
    );

    $emp->add_to_one_relationships(sql_relationship(
        table     => $dept,
        condition => sql_cond($dept->column('deptno'), '=', $entity->column('deptno'))
    ));

package SQL::Entity::Table;

use warnings;
use strict;
use vars qw($VERSION);

$VERSION = 0.02;

use Abstract::Meta::Class ':all';
use Carp 'confess';
use SQL::Entity::Column;

has '$.name';

has '$.schema';

has '@.primary_key';

has '$.alias';

has '%.columns' => (
    item_accessor    => 'column',
    associated_class => 'SQL::Entity::Column',
    index_by         => 'id',
    the_other_end    => 'table',
);

has '%.lobs' => (
    item_accessor    => 'lob',
    associated_class => 'SQL::Entity::Column::LOB',
    index_by         => 'id',
    the_other_end    => 'table',
);

has '%.indexes' => (
    item_accessor    => '_index',
    associated_class => 'SQL::Entity::Index',
    index_by         => 'name',    
);

has '$.order_index';

sub initialise {
    my ($self) = @_;
    $self->set_alias($self->name) unless $self->alias;
}

sub unique_columns {
    my ($self) = @_;
    (grep { $_->unique } values %{$self->columns});
}

sub query {
    my ($self, $requested_columns, $condition, $bind_variables, $join_methods) = @_;
    $requested_columns ||=[];
    $bind_variables ||= [];
    $join_methods ||= {};
    my $where_clause = $self->where_clause($condition, $bind_variables, $join_methods);
    my $stmt = $self->select_clause($requested_columns, $join_methods)
    . $self->from_clause($join_methods)
    . $where_clause
    . $self->order_by_clause;
    wantarray ? ($stmt, $bind_variables) : $stmt;
}

sub count {
    my ($self, $condition, $bind_variables, $join_methods) = @_;
    $bind_variables ||= [];
    $join_methods ||= {};
    my $where_clause = $self->where_clause($condition, $bind_variables, $join_methods);
    my $stmt = "SELECT COUNT(*) AS count"
    . $self->from_clause($join_methods) 
    . $where_clause;
    wantarray ? ($stmt, $bind_variables) : $stmt;
}

sub from_clause {
    my ($self, $join_methods) = @_;
    "\nFROM "
    . $self->from_clause_params($join_methods)
}

sub from_clause_params {
    my ($self) = @_;
    my $schema = $self->schema;
    ($schema ? $schema . "." : "")
    . $self->name
    . $self->from_clause_alias;
}

sub from_clause_alias {
    my ($self) = @_;
    my $alias = $self->alias;
   ($alias  && $self->name ne $alias ? " $alias" : '')
}

sub select_clause {
    my ($self, $requested_columns, $join_methods) = @_;
    "SELECT "
    . $self->select_hint_clause
    . join ",\n  ", map { $_->as_string($self, $join_methods) } $self->selectable_columns($requested_columns);
}

sub selectable_columns {
    my ($self, $requested_columns) = @_;
    confess unless $requested_columns;
    my $columns = $self->columns;
    if(@$requested_columns) {
        return map { $columns->{$_} ? ($columns->{$_}) : () } @$requested_columns;
    }
    
    $self->columns ? (values %$columns) : (); 
}

sub insertable_columns {
    my ($self) = @_;
    my $query_columns = $self->query_columns;
    map {
        my $column = $query_columns->{$_};
        ($column->insertable ? $column : ()) }  keys %$query_columns;
}

sub updatable_columns {
    my ($self) = @_;
    my $query_columns = $self->query_columns;
    map {
        my $column = $query_columns->{$_};
        ($column->updatable ? $column : ()) }  keys %$query_columns;
}

sub query_columns {
    my ($self) = @_;   
    $self->columns;
}

sub where_clause {
    my ($self, $condition, $bind_variables, $join_methods) = @_;
    return "" unless $condition;
    confess "should have condition object"
        if ($condition && ref($condition) ne 'SQL::Entity::Condition');
    my %query_columns = $self->query_columns;
    "\nWHERE " .  $condition->as_string(\%query_columns, $bind_variables, $self, $join_methods);
    
}

sub index {
    my $self = shift;
    my $order_index = $self->order_index;
    unless ($order_index) {
        my $indexes = $self->indexes or return;
        ($order_index) = (keys %$indexes) or return;
    }
    $self->_index($order_index);
}

sub select_hint_clause {
    my ($self) = @_;
    ""
}

sub order_by_clause {
    my ($self) = @_;
    my $index = $self->index or return "";
    " ORDER BY " . $index->order_by_operand($self);
}


__END__