package Basset::DB::Table;

#Basset::DB::Table Copyright and (c) 2002, 2003, 2004, 2005, 2006 James A Thomason III
#Basset::DB::Table is distributed under the terms of the Perl Artistic License.

our $VERSION = '1.02';

=pod

=head1 NAME

Basset::DB::Table - used to define database tables, ways to load that data into memory
and build queries based upon the table information

=head1 AUTHOR

Jim Thomason, jim@jimandkoka.com

=head1 SYNOPSIS

For example,

 my $table = Basset::DB::Table->new(
	'name'				=> 'user',
	'primary_column'	=> 'id',
	'autogenerated'		=> 1,
	'definition'		=> {
		'id'		=> 'SQL_INTEGER',
		'username'	=> 'SQL_VARCHAR',
		'password'	=> 'SQL_VARCHAR',
		'name'		=> 'SQL_VARCHAR'
	}
 );

print $table->insert_query, "\n";
print $table->update_query, "\n";
print $table->delete_query, "\n";

=head1 DESCRIPTION

Basset::DB::Table provides an abstract and consistent location for defining database tables,
building queries based upon them, and so on. It is rarely (if ever) used directly in code, but is
used extensively in packages which subclass from Basset::Object::Persistent.

Any queries returned by the query methods are simply strings that must be prepared by DBI
in order bo be used.

=cut

use Basset::Object;
our @ISA = Basset::Object->pkg_for_type('object');

use strict;
use warnings;

=pod

=head1 ATTRIBUTES

=over

=cut

=pod

=item name

The name of the database table.

For example, if you're creating an object to reference the table "foo",

 $table->name('foo');

=cut

=pod

=begin btest name
$| = 1;
my $o = __PACKAGE__->new();
$test->ok($o, "Created object");
$test->ok($o->name('test name'), "Set name");
$test->is($o->name, 'test name', "retrieved name");
$test->is($o->name('test name 2'), 'test name 2', 're-set name');
$test->is($o->name, 'test name 2', 'retrieved reset name');

$test->is(scalar(__PACKAGE__->name('invalid name')), undef, 'Could not set name for class attribute');

=end btest

=cut

__PACKAGE__->add_attr('name');

=pod

=item primary_column

Stores the primary column or columns for this table. Either passed a single scalar or
an array ref.

  $table->primary_column('id'); 		 #id is the primary column
 $table2->primary_column(['id', 'name']) #id & name are the primary columns

It is recommended to access the primary columns of a table via the primary_cols method, since that
method will always return an array.

  $table->primary_cols   #returns ('id')
 $table2->primary_cols   #returns ('id', 'name')

  $table->primary_column #returns 'id'
 $table2->primary_column #returns ['id', 'name']

=cut

=pod

=begin btest primary_column

my $o = __PACKAGE__->new();
$test->ok($o, "Created object");
$test->ok($o->primary_column('id'), "Set primary column");
$test->is($o->primary_column, 'id', "retrieved primary column");
$test->is($o->primary_column('id2'), 'id2', 're-set primary column');
$test->is($o->primary_column, 'id2', 'retrieved reset primary column');

my $a = [qw(id id2)];
$test->ok($a, "created arrayref");
$test->is($a->[0], 'id', 'proper array element 0');
$test->is($a->[1], 'id2', 'proper array element 1');
$test->is($o->primary_column($a), $a, 'set primary column to arrayref');

$test->is(scalar(__PACKAGE__->primary_column('invalid name')), undef, 'Could not set primary column for class attribute');

=end btest

=cut


__PACKAGE__->add_attr('primary_column');

=pod

=item autogenerated

boolean flag, 1/0

Sometimes, you may have your database auto-generate a column value for you. If you are using
unique IDs for instance, it may be easier to have the database manage the auto-generation of new
unique IDs for you. Set this flag if that's the case.

 #in your db
 create table foo (id int unsigned not null primary key auto_generated);

 #in your code
 $table->name('foo');
 $table->primary_column('id');
 $table->autogenerated(1);

=cut

=pod

=begin btest autogenerated

my $o = __PACKAGE__->new();

$test->ok($o, "created object");
$test->is($o->autogenerated(1), 1, "set autogenerated");
$test->is($o->autogenerated(), 1, "accessed autogenerated");
$test->is($o->autogenerated(0), 0, "shut off autogenerated");
$test->is($o->autogenerated, 0, "accessed autogenerated");

$test->is(scalar(__PACKAGE__->autogenerated(1)), undef, "Could not set autogenerated for class");

=end btest

=cut


__PACKAGE__->add_attr('autogenerated');

=pod

=item definition

This is the actual definition of your table. It should be given a hashref, with the keys being
your column names, and the values being the sql_type as defined in DBI for that column.

 $table->definition(
 	{
 		'name'	=> 'SQL_VARCHAR',
 		'id'	=> 'SQL_INTEGER'
 	}
 );

Note that the type should be a quoted string containing the value, not the actual constant
defined in DBI. If there is no corresponding sql_type for your column (for a MySQL text column,
for example), then pass undef.

 $table->definition(
 	{
 		'name' => 'SQL_INTEGER',
 		'bigcomment' => undef
 	}
 );

Alternatively, if you happen to know the SQL type in advance, you can just pass that along.

 $table->definition(
 	{
 		'name' => SQL_INTEGER,	#if DBI was used here
 		'bigcomment' => undef
 	}
 );

 $table->definition(
 	{
 		'name' => 4,	#if you just know it's 4
 		'bigcomment' => undef
 	}
 );

You should always use the quoted version unless you've received the numeric type from an authoritative
source, such as having it returned from the database as the column type.

Alternatively, if you don't want to use a definition, you can explicitly tell the constructor your non primary columns

 $table = Basset::DB::Table->new(
 	'primary_column' => 'id',
 	'non_primary_columns' => [qw(name age serial_number)],
 );

That takes the place of using the definition. It does a discover call behind the scenes, but only looks for the columns
that you've specified, not everything in the table.

=cut

=pod

=begin btest definition

my $o = __PACKAGE__->new();
$test->ok($o, "Got object");

my $h = {'foo' => 'bar', 'baz' => 'yee'};
$test->ok($h, 'got hashref');
$test->is($h->{'foo'}, 'bar', 'foo is bar');
$test->is($h->{'baz'}, 'yee', 'baz is yee');
$test->is($o->definition($h), $h, "Set definition");
$test->is($o->definition(), $h, 'reset definition');
$test->is(scalar(__PACKAGE__->definition(1)), undef, 'Could not set definition for class');

=end btest

=cut


__PACKAGE__->add_attr('definition');

=pod

=item references

Naturally, since you're using a relational database, you're going to have tables referencing other tables. You can store
them in your Basset::DB::Table object inside the references parameter.

 $table->references(
 	{
 		'user_id'	=> 'user.id',
 		'food_type'	=> 'food.type',
 	}
 );

That says that the 'user_id' column in your table is a foreign key into the user table and references its id column. 'food_type'
is a foreign key into the food table and references its type column.

Any foreign keys referencing primary columns can be used to auto-join the tables in a multiselect_query.

=cut

=pod

=begin btest references

my $o = __PACKAGE__->new();
$test->ok($o, "Got object");

my $h = {'foo' => 'bar', 'baz' => 'yee'};
$test->ok($h, 'got hashref');
$test->is($h->{'foo'}, 'bar', 'foo is bar');
$test->is($h->{'baz'}, 'yee', 'baz is yee');
$test->is($o->references($h), $h, "Set references");
$test->is($o->references(), $h, 'reset references');
$test->is(scalar(__PACKAGE__->references(1)), undef, 'Could not set references for class');

=end btest

=cut

__PACKAGE__->add_attr('references');

=pod

=item extra_select

Okay, as of v1.01 (heh, I finally incremented a version number!) Basset::DB::Table has gotten a power boost. It's
now arbitrary out the ying-yang. Much more power in terms of what you can and cannot select, insert, update, etc.

The first of the new toys is extra_select.

Let's assume the following definition:

 $table->name('test');
 $table->definition(
 	{
 		'name' => 'SQL_INTEGER',
 		'bigcomment' => undef
 	}
 );

That means that if you called select_query on that table, you'd get back this:

 select test.bigcomment, test.name from test

Which is peachy and marvelous. You can now initialize your object with the values from 'name' and 'bigcomment'. But
what if you want more information from the database? Perhaps a value from a function, or some calculation upon the
columns? Up until now, you'd have to do that stuff externally in Perl. Either calculating things yourself, or calling
arbitrary_sql to get the data you need out of there.

No more. extra_select does what it sounds like, it allows you to pass in extra information to select. Takes a hashref.

 $table->extra_select(
 	{
 		'current_time' => 'NOW()'
 	}
 );

Now, if you called select_query, you'd get back:

 select test.bigcomment, test.name, NOW() as current_time from test

And voila. Instant extra information.

Keep in mind, naturally, that if you want that extra column you're getting out to *go* anywhere, that your object
must have a method by that name ("current_time" in this case). Otherwise, the data will be loaded and then silently forgotten.

If you're skipping ahead, you'll see that there are attributes called "db_write_translation", and "db_read_translation".
Use whichever thing is appropriate for you.

extra_select only affects select queries.

=cut

=begin btest extra_select

my $o = __PACKAGE__->new();
$test->ok($o, "Got object");

my $h = {'foo' => 'bar', 'baz' => 'yee'};
$test->ok($h, 'got hashref');
$test->is($h->{'foo'}, 'bar', 'foo is bar');
$test->is($h->{'baz'}, 'yee', 'baz is yee');
$test->is($o->extra_select($h), $h, "Set extra_select");
$test->is($o->extra_select(), $h, 'reset extra_select');
$test->is(scalar(__PACKAGE__->extra_select(1)), undef, 'Could not set extra_select for class');

=end btest

=cut

__PACKAGE__->add_attr('extra_select');

=pod

=item db_read_translation

=cut

=pod

New addition to the various things, since I finally thought of a use for it. The db_read_translation alters your columns as they
come back from the database. Takes a hash of the form I<column> => I<translation>

 $table->db_read_translation(
 	{
 		'name' => 'lower(name)'
 	}
 );

And that would change as follows:

 print $table->select_query; #prints select table.name as name from table

with the translation:

 print $table->select_query; #prints select lower(table.name) as name from table

Useful if you know at the database level that you'll need your data transformed in some fashion.

=cut

=begin btest db_read_translation

my $o = __PACKAGE__->new();
$test->ok($o, "Got object");

my $h = {'foo' => 'bar', 'baz' => 'yee'};
$test->ok($h, 'got hashref');
$test->is($h->{'foo'}, 'bar', 'foo is bar');
$test->is($h->{'baz'}, 'yee', 'baz is yee');
$test->is($o->db_read_translation($h), $h, "Set db_read_translation");
$test->is($o->db_read_translation(), $h, 'reset db_read_translation');
$test->is(scalar(__PACKAGE__->db_read_translation(1)), undef, 'Could not set db_read_translation for class');

=end btest

=cut

__PACKAGE__->add_attr(['db_read_translation', '_isa_translation_accessor']);

=pod

=item db_write_translation

This is the closest thing to an inverse method to extra_select. db_write_translation takes a hashref which decides how
to re-write your insert, update, replace, or delete queries. Or all of them. An example is easiest.

Let's assume the following definition:

 $table->name('test');
 $table->definition(
 	{
 		'name' => 'SQL_INTEGER',
 		'bigcomment' => undef,
 		'current_time' => 'SQL_DATETIME',
 	}
 );
update test set current_time = ?, bigcomment = ?, name = ?

Then, if you called update_query, you'd get back:

 update test set current_time = ?, bigcomment = ?, name = ?

And your update_bindables are:

 current_time, bigcomment, name, name

However, that wouldn't be setting current_time to the proper current time: it's just relaying through the value in the object.
So it's up to you, the programmer, to set it yourself.

 sub commit {
  my $self = shift;
  my ($sec,$min,$hour,$day,$mon,$year) = (localtime(time))[0..5];
  $mon++;
  $year+= 1900;
  $self->current_time("$year-$mon-$day $hour:$min:$sec");

  $self->SUPER::commit(@_);
 };

It works, it's effective, but it's a pain in the butt. More work for you. This is an instance where db_write_translation
can come in handy.

 $table->db_write_translation(
 	{
 		'current_time' => {
 			'A' => {
 				'val' => 'NOW()',
 				'binds' => 0
 			}
 		}
 	}
 );

Now, your update_query is:

 update test set current_time = NOW(), bigcomment = ?, name = ?

And your update_bindables are:

 bigcomment, name, name

Voila. You no longer need to worry about setting current_time, the db does it for you.

The hashref that db_write_translation uses is of a specific format:

 method => {
  query_type => {
   'val' => new_value
   'binds' => 0/1
  }
 }

"method" is obviously the name of the method that's being re-written.
"query_type" is the flag to indicate the type of query. "I" for insert, "U" for update, "D" for delete, "R" for replace,
or "A" for all.
"binds" is a boolean flag, 0 or 1. Set to 0 if you're inserting a new value that doesn't need a binded param, such as "NOW()".
Set it to 1 if you're inserting a new value that does need a binded param, such as "LCASE(?)" to insert the value in lower case.

And voila. When the query is constructed, internally it first looks for a re-write of the method for the given query type. If
it doesn't find one, it looks for a re-write of type "A" (all queries), if it doesn't find one of those, then it just leaves it
alone and preps the query to insert the value in as is, unchanged.

One useful example that I will include, is to make a column read-only:

 $table->db_write_translation(
 	{
 		$column => {
 			'U' => {
 				'val' => $column,
 				'binds' => 0
 			}
 		}
 	}
 );

That way, when an object is committed on an update, $column's value will not change.

Also, please note that return values are not quoted. So you can't use a db_write_translation to set a value that the database
wouldn't understand.

 'val' => 'some constant value'

will fail. Your query would become:

 update....set foo = some constant value...

which chokes, of course. Use a wrapper to alter the value you pass in at a higher level, or quote it yourself.
The db_write_translation only alters your actual SQL statement.

=cut

=begin btest db_write_translation

my $o = __PACKAGE__->new();
$test->ok($o, "Got object");

my $h = {'foo' => 'bar', 'baz' => 'yee'};
$test->ok($h, 'got hashref');
$test->is($h->{'foo'}, 'bar', 'foo is bar');
$test->is($h->{'baz'}, 'yee', 'baz is yee');
$test->is($o->db_write_translation($h), $h, "Set db_write_translation");
$test->is($o->db_write_translation(), $h, 'reset db_write_translation');
$test->is(scalar(__PACKAGE__->db_write_translation(1)), undef, 'Could not set db_write_translation for class');

=end btest

=cut

__PACKAGE__->add_attr(['db_write_translation', '_isa_translation_accessor']);

=pod

=item column_aliases

You can define different aliases for columns as they come out of your table.

 $table->select_columns('id');

 print $table->select_query; 	#prints select id from foo

 $table->column_aliases(
 	{
 		'id' => 'user_id'
 	}
 );

 print $table->select_query		#prints select id as user_id from foo

Note that Basset::Object::Persistent assumes that if you're aliasing a column, that the aliased value is your method name.
So in this case, any objects using that as a primary table would have a method name of 'user_id' that stores in the 'id' 
column in the table.

=cut

=pod

=begin btest column_aliases

my $aliases = {
	'able' => 'aliased_able',
	'baker' => 'aliased_baker',
	'charlie' => 'aliased_charlie',
	'delta' => 'aliased_delta'
};

my $o = __PACKAGE__->new();
$test->ok($o, "Created object");

$test->is($o->column_aliases($aliases), $aliases, "Set column aliases");
$test->is($o->column_aliases(), $aliases, "Got column aliases");

=end btest

=cut

__PACKAGE__->add_attr('column_aliases');

# internally stores all previously built queries for this table, for speed.
# caches are generated per table/query/columns
__PACKAGE__->add_attr('_cached_queries');

=pod

=begin btest _cached_queries

my $o = __PACKAGE__->new();
$test->ok($o, "Got object");
$test->is(scalar(__PACKAGE__->_cached_queries), undef, "could not call object method as class method");
$test->is(__PACKAGE__->errcode, "BO-08", "proper error code");
$test->is(ref($o->_cached_queries), 'HASH', '_cached_queries is hashref');
$test->is($o->_cached_queries('abc'), 'abc', 'set _cached_queries to abc');
$test->is($o->_cached_queries(), 'abc', 'read value of _cached_queries - abc');
my $h = {};
$test->ok($h, 'got hashref');
$test->is($o->_cached_queries($h), $h, 'set _cached_queries to hashref');
$test->is($o->_cached_queries(), $h, 'read value of _cached_queries  - hashref');
my $a = [];
$test->ok($a, 'got arrayref');
$test->is($o->_cached_queries($a), $a, 'set _cached_queries to arrayref');
$test->is($o->_cached_queries(), $a, 'read value of _cached_queries  - arrayref');

=end btest

=cut

# internally stores all previously built bindables for this table, for speed.
# caches are generated per table/query/columns
__PACKAGE__->add_attr('_cached_bindables');

=pod

=begin btest _cached_bindables

my $o = __PACKAGE__->new();
$test->ok($o, "Got object");
$test->is(scalar(__PACKAGE__->_cached_bindables), undef, "could not call object method as class method");
$test->is(__PACKAGE__->errcode, "BO-08", "proper error code");
$test->is(ref($o->_cached_bindables), 'HASH', '_cached_bindables is hashref');
$test->is($o->_cached_bindables('abc'), 'abc', 'set _cached_bindables to abc');
$test->is($o->_cached_bindables(), 'abc', 'read value of _cached_bindables - abc');
my $h = {};
$test->ok($h, 'got hashref');
$test->is($o->_cached_bindables($h), $h, 'set _cached_bindables to hashref');
$test->is($o->_cached_bindables(), $h, 'read value of _cached_bindables  - hashref');
my $a = [];
$test->ok($a, 'got arrayref');
$test->is($o->_cached_bindables($a), $a, 'set _cached_bindables to arrayref');
$test->is($o->_cached_bindables(), $a, 'read value of _cached_bindables  - arrayref');

=end btest

=cut


=pod

=item *_columns

 insert_columns
 update_columns
 delete_columns
 replace_columns
 select_columns

Normally, when you get back an insert_query, update_query, etc. from the various DB::Table methods here, all columns
in the table are included. You can use these methods to restrict the queries to only be called on particular methods.

 print $table->insert_query; 	#prints insert into foo (this, that, those) values (?,?,?) for example
 $table->insert_columns('this');
 print $table->insert-query; 	#prints insert into foo (this) values (?) for example

These methods are not thread-safe.

You also have a set of negative non_*_columns that do an inverse.

 print $table->insert_query; 	#prints insert into foo (this, that, those) values (?,?,?) for example
 $table->non_insert_columns('this');
 print $table->insert-query; 	#prints insert into foo (that, those) values (?,?,?) for example

You may also use both at the same time

 print $table->insert_query; 	#prints insert into foo (this, that, those) values (?,?,?) for example
 $table->insert_columns('that', 'those');
 $table->non_insert_columns('that');
 print $table->insert-query; 	#prints insert into foo (those) values (?,?) for example

=cut

=pod

=item last_insert_query

All databases grab the last inserted ID in a different fashion. last_insert_query allows us to specify the query we use to grab
the last inserted ID for a given insert. This should probably be specified in the conf file, but you can do it in the individual
modules, if you prefer. Note that this is a trickling class accessor, so you can re-define it as many times as you want, or 
just use the default specified for Basset::Object::Persistent.

Certain databases don't need differeing queries. MySQL, for instance, is happy with just "SELECT LAST_INSERT_ID()" defined for
the super class.

=cut

__PACKAGE__->add_attr('last_insert_query');

=pod

=begin btest last_insert_query

my $o = __PACKAGE__->new();
$test->ok($o, "got object");

$test->ok(! scalar(__PACKAGE__->last_insert_query), "Cannot call object method as class method");

$test->is($o->last_insert_query('foo'), 'foo', "set query to foo");
$test->is($o->last_insert_query(), 'foo', 'got insert query');

=end btest

=cut

=pod

=begin btest insert_columns

my $o = __PACKAGE__->new();
$test->ok($o, "Created object");
my $def = {
	'able' => 'SQL_INTEGER',
	'baker' => 'SQL_INTEGER',
	'charlie' => 'SQL_INTEGER',
	'delta' => 'SQL_INTEGER'
};

$test->is($o->definition($def), $def, "Set definition");
$test->is($o->definition, $def, "Got definition");

{
	my %icols = map {$_, 1} $o->insert_columns();

	$test->is($icols{'able'}, 1, 'able is an insert col');
	$test->is($icols{'baker'}, 1, 'baker is an insert col');
	$test->is($icols{'charlie'}, 1, 'charlie is an insert col');
	$test->is($icols{'delta'}, 1, 'delta is an insert col');	
	$test->is(scalar(keys %icols), 4, 'only 4 insert columns');
}

{
	my $icols = [qw(able charlie)];

	$test->is(join(',',$o->insert_columns($icols)), join(',',@$icols), "set new insert columns");
	$test->is(join(',',$o->insert_columns), join(',',@$icols), "got new insert columns");


	my %icols = map {$_, 1} $o->insert_columns();

	$test->is($icols{'able'}, 1, 'able is an insert col');
	$test->is($icols{'baker'}, undef, 'baker is not an insert col');
	$test->is($icols{'charlie'}, 1, 'charlie is an insert col');
	$test->is($icols{'delta'}, undef, 'delta is not an insert col');	
	$test->is(scalar(keys %icols), 2, 'only 2 insert columns');
}

{
	my $icols = [qw()];

	$o->insert_columns($icols);

	my %icols = map {$_, 1} $o->insert_columns();

	$test->is($icols{'able'}, 1, 'able is an insert col');
	$test->is($icols{'baker'}, 1, 'baker is an insert col');
	$test->is($icols{'charlie'}, 1, 'charlie is an insert col');
	$test->is($icols{'delta'}, 1, 'delta is an insert col');	
	$test->is(scalar(keys %icols), 4, '4 insert columns');
}

{
	$o->insert_columns(undef);
	my %icols = map {$_, 1} $o->insert_columns();

	$test->is($icols{'able'}, 1, 'able is an insert col');
	$test->is($icols{'baker'}, 1, 'baker is an insert col');
	$test->is($icols{'charlie'}, 1, 'charlie is an insert col');
	$test->is($icols{'delta'}, 1, 'delta is an insert col');	
	$test->is(scalar(keys %icols), 4, '4 insert columns');
}

$test->is(scalar($o->insert_columns(['junk'])), undef, 'could not insert unknown column');
$test->is($o->errcode, 'BDT-13', 'proper error code');

=end btest

=cut

=pod

=begin btest update_columns

my $o = __PACKAGE__->new();
$test->ok($o, "Created object");
my $def = {
	'able' => 'SQL_INTEGER',
	'baker' => 'SQL_INTEGER',
	'charlie' => 'SQL_INTEGER',
	'delta' => 'SQL_INTEGER'
};

$test->is($o->definition($def), $def, "Set definition");
$test->is($o->definition, $def, "Got definition");

{
	my %icols = map {$_, 1} $o->update_columns();

	$test->is($icols{'able'}, 1, 'able is an update col');
	$test->is($icols{'baker'}, 1, 'baker is an update col');
	$test->is($icols{'charlie'}, 1, 'charlie is an update col');
	$test->is($icols{'delta'}, 1, 'delta is an update col');	
	$test->is(scalar(keys %icols), 4, 'only 4 update columns');
}

{
	my $icols = [qw(able charlie)];

	$test->is(join(',',$o->update_columns($icols)), join(',',@$icols), "set new update columns");
	$test->is(join(',',$o->update_columns), join(',',@$icols), "got new update columns");


	my %icols = map {$_, 1} $o->update_columns();

	$test->is($icols{'able'}, 1, 'able is an update col');
	$test->is($icols{'baker'}, undef, 'baker is not an update col');
	$test->is($icols{'charlie'}, 1, 'charlie is an update col');
	$test->is($icols{'delta'}, undef, 'delta is not an update col');	
	$test->is(scalar(keys %icols), 2, 'only 2 update columns');
}

{
	my $icols = [qw()];

	$o->update_columns($icols);

	my %icols = map {$_, 1} $o->update_columns();

	$test->is($icols{'able'}, 1, 'able is an update col');
	$test->is($icols{'baker'}, 1, 'baker is an update col');
	$test->is($icols{'charlie'}, 1, 'charlie is an update col');
	$test->is($icols{'delta'}, 1, 'delta is an update col');	
	$test->is(scalar(keys %icols), 4, '4 update columns');
}

{
	$o->update_columns(undef);
	my %icols = map {$_, 1} $o->update_columns();

	$test->is($icols{'able'}, 1, 'able is an update col');
	$test->is($icols{'baker'}, 1, 'baker is an update col');
	$test->is($icols{'charlie'}, 1, 'charlie is an update col');
	$test->is($icols{'delta'}, 1, 'delta is an update col');	
	$test->is(scalar(keys %icols), 4, '4 update columns');
}

$test->is(scalar($o->update_columns(['junk'])), undef, 'could not update unknown column');
$test->is($o->errcode, 'BDT-13', 'proper error code');

=end btest

=cut

=pod

=begin btest delete_columns

my $o = __PACKAGE__->new();
$test->ok($o, "Created object");
my $def = {
	'able' => 'SQL_INTEGER',
	'baker' => 'SQL_INTEGER',
	'charlie' => 'SQL_INTEGER',
	'delta' => 'SQL_INTEGER'
};

$test->is($o->definition($def), $def, "Set definition");
$test->is($o->definition, $def, "Got definition");

{
	my %icols = map {$_, 1} $o->delete_columns();

	$test->is($icols{'able'}, 1, 'able is an delete col');
	$test->is($icols{'baker'}, 1, 'baker is an delete col');
	$test->is($icols{'charlie'}, 1, 'charlie is an delete col');
	$test->is($icols{'delta'}, 1, 'delta is an delete col');	
	$test->is(scalar(keys %icols), 4, 'only 4 delete columns');
}

{
	my $icols = [qw(able charlie)];

	$test->is(join(',',$o->delete_columns($icols)), join(',',@$icols), "set new delete columns");
	$test->is(join(',',$o->delete_columns), join(',',@$icols), "got new delete columns");


	my %icols = map {$_, 1} $o->delete_columns();

	$test->is($icols{'able'}, 1, 'able is an delete col');
	$test->is($icols{'baker'}, undef, 'baker is not an delete col');
	$test->is($icols{'charlie'}, 1, 'charlie is an delete col');
	$test->is($icols{'delta'}, undef, 'delta is not an delete col');	
	$test->is(scalar(keys %icols), 2, 'only 2 delete columns');
}

{
	my $icols = [qw()];

	$o->delete_columns($icols);

	my %icols = map {$_, 1} $o->delete_columns();

	$test->is($icols{'able'}, 1, 'able is an delete col');
	$test->is($icols{'baker'}, 1, 'baker is an delete col');
	$test->is($icols{'charlie'}, 1, 'charlie is an delete col');
	$test->is($icols{'delta'}, 1, 'delta is an delete col');	
	$test->is(scalar(keys %icols), 4, '4 delete columns');
}

{
	$o->delete_columns(undef);
	my %icols = map {$_, 1} $o->delete_columns();

	$test->is($icols{'able'}, 1, 'able is an delete col');
	$test->is($icols{'baker'}, 1, 'baker is an delete col');
	$test->is($icols{'charlie'}, 1, 'charlie is an delete col');
	$test->is($icols{'delta'}, 1, 'delta is an delete col');	
	$test->is(scalar(keys %icols), 4, '4 delete columns');
}

$test->is(scalar($o->delete_columns(['junk'])), undef, 'could not delete unknown column');
$test->is($o->errcode, 'BDT-13', 'proper error code');

=end btest

=cut

=pod

=begin btest replace_columns

my $o = __PACKAGE__->new();
$test->ok($o, "Created object");
my $def = {
	'able' => 'SQL_INTEGER',
	'baker' => 'SQL_INTEGER',
	'charlie' => 'SQL_INTEGER',
	'delta' => 'SQL_INTEGER'
};

$test->is($o->definition($def), $def, "Set definition");
$test->is($o->definition, $def, "Got definition");

{
	my %icols = map {$_, 1} $o->replace_columns();

	$test->is($icols{'able'}, 1, 'able is an replace col');
	$test->is($icols{'baker'}, 1, 'baker is an replace col');
	$test->is($icols{'charlie'}, 1, 'charlie is an replace col');
	$test->is($icols{'delta'}, 1, 'delta is an replace col');	
	$test->is(scalar(keys %icols), 4, 'only 4 replace columns');
}

{
	my $icols = [qw(able charlie)];

	$test->is(join(',',$o->replace_columns($icols)), join(',',@$icols), "set new replace columns");
	$test->is(join(',',$o->replace_columns), join(',',@$icols), "got new replace columns");


	my %icols = map {$_, 1} $o->replace_columns();

	$test->is($icols{'able'}, 1, 'able is an replace col');
	$test->is($icols{'baker'}, undef, 'baker is not an replace col');
	$test->is($icols{'charlie'}, 1, 'charlie is an replace col');
	$test->is($icols{'delta'}, undef, 'delta is not an replace col');	
	$test->is(scalar(keys %icols), 2, 'only 2 replace columns');
}

{
	my $icols = [qw()];

	$o->replace_columns($icols);

	my %icols = map {$_, 1} $o->replace_columns();

	$test->is($icols{'able'}, 1, 'able is an replace col');
	$test->is($icols{'baker'}, 1, 'baker is an replace col');
	$test->is($icols{'charlie'}, 1, 'charlie is an replace col');
	$test->is($icols{'delta'}, 1, 'delta is an replace col');	
	$test->is(scalar(keys %icols), 4, '4 replace columns');
}

{
	$o->replace_columns(undef);
	my %icols = map {$_, 1} $o->replace_columns();

	$test->is($icols{'able'}, 1, 'able is an replace col');
	$test->is($icols{'baker'}, 1, 'baker is an replace col');
	$test->is($icols{'charlie'}, 1, 'charlie is an replace col');
	$test->is($icols{'delta'}, 1, 'delta is an replace col');	
	$test->is(scalar(keys %icols), 4, '4 replace columns');
}

$test->is(scalar($o->replace_columns(['junk'])), undef, 'could not replace unknown column');
$test->is($o->errcode, 'BDT-13', 'proper error code');

=end btest

=cut

=pod

=begin btest select_columns

my $o = __PACKAGE__->new();
$test->ok($o, "Created object");
my $def = {
	'able' => 'SQL_INTEGER',
	'baker' => 'SQL_INTEGER',
	'charlie' => 'SQL_INTEGER',
	'delta' => 'SQL_INTEGER'
};

$test->is($o->definition($def), $def, "Set definition");
$test->is($o->definition, $def, "Got definition");

{
	my %icols = map {$_, 1} $o->select_columns();

	$test->is($icols{'able'}, 1, 'able is an select col');
	$test->is($icols{'baker'}, 1, 'baker is an select col');
	$test->is($icols{'charlie'}, 1, 'charlie is an select col');
	$test->is($icols{'delta'}, 1, 'delta is an select col');	
	$test->is(scalar(keys %icols), 4, 'only 4 select columns');
}

{
	my $icols = [qw(able charlie)];

	$test->is(join(',',$o->select_columns($icols)), join(',',@$icols), "set new select columns");
	$test->is(join(',',$o->select_columns), join(',',@$icols), "got new select columns");


	my %icols = map {$_, 1} $o->select_columns();

	$test->is($icols{'able'}, 1, 'able is an select col');
	$test->is($icols{'baker'}, undef, 'baker is not an select col');
	$test->is($icols{'charlie'}, 1, 'charlie is an select col');
	$test->is($icols{'delta'}, undef, 'delta is not an select col');	
	$test->is(scalar(keys %icols), 2, 'only 2 select columns');
}

{
	my $icols = [qw(able charlie)];
	my $nicols = [qw(able)];

	$test->is(join(',',$o->select_columns($icols)), join(',',@$icols), "set new select columns");
	$test->is(join(',',$o->select_columns), join(',',@$icols), "got new select columns");

	$test->is(join(',',$o->nonselect_columns($nicols)), join(',',@$nicols), "set new non-select columns");
	$test->is(join(',',$o->nonselect_columns), join(',',@$nicols), "got new non-select columns");


	my %icols = map {$_, 1} $o->select_columns();

	$test->is($icols{'able'}, undef, 'able is a select col');
	$test->is($icols{'baker'}, undef, 'baker is not an select col');
	$test->is($icols{'charlie'}, 1, 'charlie is an select col');
	$test->is($icols{'delta'}, undef, 'delta is not an select col');	
	$test->is(scalar(keys %icols), 1, 'only 1 select column');
}



{
	my $icols = [qw()];

	$o->select_columns($icols);
	$o->nonselect_columns([]);

	my %icols = map {$_, 1} $o->select_columns();

	$test->is($icols{'able'}, 1, 'able is an select col');
	$test->is($icols{'baker'}, 1, 'baker is an select col');
	$test->is($icols{'charlie'}, 1, 'charlie is an select col');
	$test->is($icols{'delta'}, 1, 'delta is an select col');	
	$test->is(scalar(keys %icols), 4, '4 select columns');
}

{
	$o->select_columns(undef);
	my %icols = map {$_, 1} $o->select_columns();

	$test->is($icols{'able'}, 1, 'able is an select col');
	$test->is($icols{'baker'}, 1, 'baker is an select col');
	$test->is($icols{'charlie'}, 1, 'charlie is an select col');
	$test->is($icols{'delta'}, 1, 'delta is an select col');	
	$test->is(scalar(keys %icols), 4, '4 select columns');
}

$test->is(scalar($o->select_columns(['junk'])), undef, 'could not select unknown column');
$test->is($o->errcode, 'BDT-13', 'proper error code');

=end btest

=cut

__PACKAGE__->add_attr(['insert_columns',  '_isa_column_list_accessor']);
__PACKAGE__->add_attr(['update_columns',  '_isa_column_list_accessor']);
__PACKAGE__->add_attr(['delete_columns',  '_isa_column_list_accessor']);
__PACKAGE__->add_attr(['replace_columns', '_isa_column_list_accessor']);
__PACKAGE__->add_attr(['select_columns',  '_isa_column_list_accessor']);

__PACKAGE__->add_attr(['noninsert_columns',  '_isa_column_list_accessor']);
__PACKAGE__->add_attr(['nonupdate_columns',  '_isa_column_list_accessor']);
__PACKAGE__->add_attr(['nondelete_columns',  '_isa_column_list_accessor']);
__PACKAGE__->add_attr(['nonreplace_columns', '_isa_column_list_accessor']);
__PACKAGE__->add_attr(['nonselect_columns',  '_isa_column_list_accessor']);

=pod

=begin btest _column_list_accessor

$test->ok("testing is implied", "testing is implied");

=end btest

=cut

sub _isa_column_list_accessor {
	my $pkg = shift;
	my $attr = shift;
	my $prop = shift;

	return sub {
		my $self	= shift;
	
		my $prefix = $self->system_prefix;
		(my $propname = $prop) =~ s/^$prefix//;
	
		if (@_) {
			foreach my $col (@{$_[0]}) {
				return $self->error("Cannot add $col for $propname - not a column", "BDT-13")
					unless $self->is_column($col);
			}
	
			$self->$prop(@_);
		}
	
		my $vals = $self->$prop() || [];
	
		my @vals = @$vals ? @$vals : $self->cols;
	
		#weed out our non-columns, if they were provided.
		if ($propname !~ /non/) {
			my $nonprop = $prefix . 'non' . $propname;
			my $nonvals = {map {$_, 1} @{$self->$nonprop() || []}};
	
			@vals = grep {! $nonvals->{$_}} @vals;
		}
	
		return @vals;
	}
}

sub _isa_translation_accessor {
	my $pkg = shift;
	my $attr = shift;
	my $prop = shift;

	return sub {
		my $self = shift;
		$self->_cached_queries({}) if @_;
		$self->_cached_bindables({}) if @_;
		return $self->$prop(@_);
	};
}


=pod

=begin btest init

my $o = __PACKAGE__->new();
$test->ok($o, "got object");
$test->is(ref $o->definition, 'HASH', 'definition initialized to hash');
$test->is(ref $o->extra_select, 'HASH', 'extra_select initialized to hash');
$test->is(ref $o->db_write_translation, 'HASH', 'db_write_translation initialized to hash');
$test->is(ref $o->db_read_translation, 'HASH', 'db_read_translation initialized to hash');
$test->is(ref $o->column_aliases, 'HASH', 'column_aliases initialized to hash');
$test->is(ref $o->references, 'HASH', 'references initialized to hash');

=end btest

=cut

#just a bubble-up initializer. Initializes some values and passes them through.
sub init {
	my $self = shift;

	my %init = (
		'definition'				=> {},
		'extra_select'				=> {},
		'db_write_translation'		=> {},
		'db_read_translation'		=> {},
		'column_aliases'			=> {},
		'references'				=> {},
		'_cached_queries'			=> {},
		'_cached_bindables'			=> {},
		'attributes_not_to_create'	=> [],
		'create_attributes'			=> 0,
		'last_insert_query'			=> 'SELECT LAST_INSERT_ID()',
		@_
	);

	if ($init{'discover'}) {
		$init{'definition'} = $self->discover_columns($init{'name'}) or return;
	} elsif ($init{'non_primary_columns'}) {
		my @primary = ref $init{'primary_column'} ? @{$init{'primary_column'}} : ($init{'primary_column'});
		$init{'definition'} = $self->discover_columns($init{'name'}, (@primary, @{$init{'non_primary_columns'}})) or return;
	}

	#$self->definition($init{'definition'});

	return $self->SUPER::init(
		'definition' => $init{'definition'},
		%init
	);
};

__PACKAGE__->add_attr('_attributes_to_create');
__PACKAGE__->add_attr('attributes_not_to_create');

__PACKAGE__->add_attr('create_attributes');

sub attributes_to_create {
	my $self = shift;
	if (@_) {
		$self->_attributes_to_create($_[0]);
	};
	
	my %not = map {$_, 1} @{$self->attributes_not_to_create};
	
	return grep {! $not{$_} } $self->alias_column($self->_attributes_to_create ? @{$self->_attributes_to_create} : $self->cols);
}

=pod

=back

=head1 METHODS

=over

=pod

=item cols

Returns the columns defined for this table, in an unspecified order

 my @cols = $table->cols();

=cut

=pod

=begin btest cols

my $o = __PACKAGE__->new();
$test->ok($o, "Created object");
my $def = {
	'able' => 'SQL_INTEGER',
	'baker' => 'SQL_VARCHAR',
	'charlie' => 'SQL_DATE',
	'delta' => 'SQL_UNKNOWN_TYPE'
};

$test->is($o->definition($def), $def, "Set definition");
$test->is($o->definition, $def, "Got definition");

my %cols = map {$_, 1} $o->cols();

$test->is(scalar(keys %cols), scalar(keys %$def), "proper number of columns");
$test->is($cols{'able'}, 1, 'able is column');
$test->is($cols{'baker'}, 1, 'baker is column');
$test->is($cols{'charlie'}, 1, 'charlie is column');
$test->is($cols{'delta'}, 1, 'delta is column');
$test->is($cols{'edgar'}, undef, 'edgar is not column');
$test->is($cols{'foxtrot'}, undef, 'foxtrot is not column');
$test->is($cols{'goat'}, undef, 'goat is not column');

=end btest

=cut

sub cols {
	my $self = shift;
	return keys %{$self->definition};
};

=item defs

Returns the column definitions defined for this table, in an unspecified order, but
the same order as the columns returned by cols

 my @defs = $table->defs();

=cut

=pod

=begin btest defs

my $o = __PACKAGE__->new();
$test->ok($o, "Created object");
my $def = {
	'able' => 'SQL_INTEGER',
	'baker' => 'SQL_VARCHAR',
	'charlie' => 'SQL_DATE',
	'delta' => 'SQL_UNKNOWN_TYPE'
};

$test->is($o->definition($def), $def, "Set definition");
$test->is($o->definition, $def, "Got definition");

my @cols = $o->cols();
my @defs = $o->defs();

$test->is(scalar(@defs), scalar(keys %$def), "proper number of definitions");
$test->is($defs[0], $o->definition->{$cols[0]}, "Definition matches column 0");
$test->is($defs[1], $o->definition->{$cols[1]}, "Definition matches column 1");
$test->is($defs[2], $o->definition->{$cols[2]}, "Definition matches column 2");
$test->is($defs[3], $o->definition->{$cols[3]}, "Definition matches column 3");

=end btest

=cut

sub defs {
	my $self = shift;
	return values %{$self->definition};
};

=pod

=item is_bindable

Fairly straightforward method, given a column and a query type, will tell you if the column is bindable.

 $table->is_bindable('U', 'foo'); #returns 1 or 0, whether or not 'foo' can be bound on an update.

Valid query types are 'U', 'I', 'R', 'D', 'S', and 'A'

=cut

=pod

=begin btest is_bindable

my $o = __PACKAGE__->new();
$test->ok($o, "Created object");
my $def = {
	'able' => 'SQL_INTEGER',
	'baker' => 'SQL_INTEGER',
	'charlie' => 'SQL_INTEGER',
	'delta' => 'SQL_INTEGER'
};

$test->is($o->definition($def), $def, "Set definition");
$test->is($o->definition, $def, "Got definition");

$test->is(scalar($o->is_bindable()), undef, 'Cannot bind w/o type');
$test->is($o->errcode, 'BDT-31', 'proper error code for is_bindable (type)');
$test->is(scalar($o->is_bindable('able')), undef, 'Cannot bind w/o col');
$test->is($o->errcode, 'BDT-30', 'proper error code for is_bindable (col)');

$test->is($o->is_bindable('I', 'able'), 1, 'able binds on insert');
$test->is($o->is_bindable('I', 'baker'), 1, 'baker binds on insert');
$test->is($o->is_bindable('I', 'charlie'), 1, 'charlie binds on insert');
$test->is($o->is_bindable('I', 'delta'), 1, 'delta binds on insert');

$test->is($o->is_bindable('U', 'able'), 1, 'able binds on update');
$test->is($o->is_bindable('U', 'baker'), 1, 'baker binds on update');
$test->is($o->is_bindable('U', 'charlie'), 1, 'charlie binds on update');
$test->is($o->is_bindable('U', 'delta'), 1, 'delta binds on update');

$test->is($o->is_bindable('R', 'able'), 1, 'able binds on replace');
$test->is($o->is_bindable('R', 'baker'), 1, 'baker binds on replace');
$test->is($o->is_bindable('R', 'charlie'), 1, 'charlie binds on replace');
$test->is($o->is_bindable('R', 'delta'), 1, 'delta binds on replace');

$test->is($o->is_bindable('D', 'able'), 1, 'able binds on delete');
$test->is($o->is_bindable('D', 'baker'), 1, 'baker binds on delete');
$test->is($o->is_bindable('D', 'charlie'), 1, 'charlie binds on delete');
$test->is($o->is_bindable('D', 'delta'), 1, 'delta binds on delete');

$test->is($o->is_bindable('S', 'able'), 1, 'able binds on select');
$test->is($o->is_bindable('S', 'baker'), 1, 'baker binds on select');
$test->is($o->is_bindable('S', 'charlie'), 1, 'charlie binds on select');
$test->is($o->is_bindable('S', 'delta'), 1, 'delta binds on select');

$test->is($o->is_bindable('A', 'able'), 1, 'able binds on all');
$test->is($o->is_bindable('A', 'baker'), 1, 'baker binds on all');
$test->is($o->is_bindable('A', 'charlie'), 1, 'charlie binds on all');
$test->is($o->is_bindable('A', 'delta'), 1, 'delta binds on all');

my $translator = 	{
		'able' => {
			'I' => {
				'val' => 'NOW()',
				'binds' => 0
			},
		},
		'baker' => {
			'U' => {
				'val' => 'NOW()',
				'binds' => 0,
			},
		},
		'charlie' => {
			'R' => {
				'val' => 'NOW()',
				'binds' => 0,
			},
		},
		'delta' => {
			'D' => {
				'val' => 'NOW()',
				'binds' => 0,
			},
		},
	};

$test->is($o->db_write_translation($translator), $translator, "Set translator");

$test->is($o->is_bindable('I', 'able'), 0, 'able does not bind on insert');
$test->is($o->is_bindable('I', 'baker'), 1, 'baker binds on insert');
$test->is($o->is_bindable('I', 'charlie'), 1, 'charlie binds on insert');
$test->is($o->is_bindable('I', 'delta'), 1, 'delta binds on insert');

$test->is($o->is_bindable('U', 'able'), 1, 'able binds on update');
$test->is($o->is_bindable('U', 'baker'), 0, 'baker does not bind on update');
$test->is($o->is_bindable('U', 'charlie'), 1, 'charlie binds on update');
$test->is($o->is_bindable('U', 'delta'), 1, 'delta binds on update');

$test->is($o->is_bindable('R', 'able'), 1, 'able binds on replace');
$test->is($o->is_bindable('R', 'baker'), 1, 'baker binds on replace');
$test->is($o->is_bindable('R', 'charlie'), 0, 'charlie does not bind on replace');
$test->is($o->is_bindable('R', 'delta'), 1, 'delta binds on replace');

$test->is($o->is_bindable('D', 'able'), 1, 'able binds on delete');
$test->is($o->is_bindable('D', 'baker'), 1, 'baker binds on delete');
$test->is($o->is_bindable('D', 'charlie'), 1, 'charlie binds on delete');
$test->is($o->is_bindable('D', 'delta'), 0, 'delta does not bind on delete');

$test->is($o->is_bindable('S', 'able'), 1, 'able binds on select');
$test->is($o->is_bindable('S', 'baker'), 1, 'baker binds on select');
$test->is($o->is_bindable('S', 'charlie'), 1, 'charlie binds on select');
$test->is($o->is_bindable('S', 'delta'), 1, 'delta binds on select');

$test->is($o->is_bindable('A', 'able'), 1, 'able binds on all');
$test->is($o->is_bindable('A', 'baker'), 1, 'baker binds on all');
$test->is($o->is_bindable('A', 'charlie'), 1, 'charlie binds on all');
$test->is($o->is_bindable('A', 'delta'), 1, 'delta binds on all');

my $translator2 = 	{
		'able' => {
			'S' => {
				'val' => 'NOW()',
				'binds' => 0
			},
		},
		'baker' => {
			'A' => {
				'val' => 'NOW()',
				'binds' => 0,
			},
		},
		'charlie' => {
			'I' => {
				'val' => 'NOW()',
				'binds' => 1,
			},
			'A' => {
				'val' => 'NOW()',
				'binds' => 0,
			},
		},
		'delta' => {
			'A' => {
				'val' => '?',
				'binds' => 1,
			}
		},
	};

$test->is($o->db_write_translation($translator2), $translator2, "Set translator again");

$test->is($o->is_bindable('I', 'able'), 1, 'able binds on insert');
$test->is($o->is_bindable('I', 'baker'), 0, 'baker does not bind on insert');
$test->is($o->is_bindable('I', 'charlie'), 1, 'charlie binds on insert');
$test->is($o->is_bindable('I', 'delta'), 1, 'delta binds on insert');

$test->is($o->is_bindable('U', 'able'), 1, 'able binds on update');
$test->is($o->is_bindable('U', 'baker'), 0, 'baker does not bind on update');
$test->is($o->is_bindable('U', 'charlie'), 0, 'charlie does not bind on update');
$test->is($o->is_bindable('U', 'delta'), 1, 'delta binds on update');

$test->is($o->is_bindable('R', 'able'), 1, 'able binds on replace');
$test->is($o->is_bindable('R', 'baker'), 0, 'baker does not bind replace');
$test->is($o->is_bindable('R', 'charlie'), 0, 'charlie does not bind on replace');
$test->is($o->is_bindable('R', 'delta'), 1, 'delta binds on replace');

$test->is($o->is_bindable('D', 'able'), 1, 'able binds on delete');
$test->is($o->is_bindable('D', 'baker'), 0, 'baker does not bind delete');
$test->is($o->is_bindable('D', 'charlie'), 0, 'charlie does not bind on delete');
$test->is($o->is_bindable('D', 'delta'), 1, 'delta binds on delete');

$test->is($o->is_bindable('S', 'able'), 0, 'able does not bind on select');
$test->is($o->is_bindable('S', 'baker'), 0, 'baker does not bind on select');
$test->is($o->is_bindable('S', 'charlie'), 0, 'charlie does not bind on select');
$test->is($o->is_bindable('S', 'delta'), 1, 'delta binds on select');

$test->is($o->is_bindable('A', 'able'), 1, 'able binds on all');
$test->is($o->is_bindable('A', 'baker'), 0, 'baker does not bind on all');
$test->is($o->is_bindable('A', 'charlie'), 0, 'charlie does not bind on all');
$test->is($o->is_bindable('A', 'delta'), 1, 'delta binds on all');

=end btest

=cut

sub is_bindable {
	my $self	= shift;
	my $type	= shift or return $self->error("Cannot check bindableness w/o type", "BDT-31");
	my $col		= shift or return $self->error("Cannot check bindableness w/o column", "BDT-30");

	my $db_write_translation = $self->db_write_translation;

	if (defined $db_write_translation->{$col}) {
		if (defined $db_write_translation->{$col}->{$type}){
			return $db_write_translation->{$col}->{$type}->{'binds'};
		}
		elsif (defined $db_write_translation->{$col}->{'A'}){
			return $db_write_translation->{$col}->{'A'}->{'binds'};
		}
	};
	return 1;
};

=pod

=item is_selectable

=cut

=pod

=begin btest is_selectable

my $o = __PACKAGE__->new();
$test->ok($o, "Created object");
my $def = {
	'able' => 'SQL_INTEGER',
	'baker' => 'SQL_INTEGER',
	'charlie' => 'SQL_INTEGER',
	'delta' => 'SQL_INTEGER'
};

$test->is($o->definition($def), $def, "Set definition");
$test->is($o->definition, $def, "Got definition");

$test->is(scalar($o->is_selectable), undef, 'Could not determine selectableness w/o value');
$test->is($o->errcode, 'BDT-44', 'proper error code');

$test->is($o->is_selectable('able'), 1, 'selects column');
$test->is($o->is_selectable('7'), 1, 'selects integer');
$test->is($o->is_selectable('7 as seven'), 1, 'selects aliased integer');
$test->is($o->is_selectable('98.6'), 1, 'selects float');
$test->is($o->is_selectable('98.6 as temp'), 1, 'selects aliased float');
$test->is($o->is_selectable('.778'), 1, 'selects decimal started float');
$test->is($o->is_selectable('.778 as small'), 1, 'selects decimal started float');
$test->is($o->is_selectable("'string'"), 1, 'selects single quoted string');
$test->is($o->is_selectable("'string' as alias"), 1, 'selects aliased single quoted string');
$test->is($o->is_selectable('"string"'), 1, 'selects double quoted string');
$test->is($o->is_selectable('"string" as alias'), 1, 'selects aliased double quoted string');
$test->is($o->is_selectable('NOW()'), 1, 'selects empty function');
$test->is($o->is_selectable('NOW() as "now"'), 1, 'selects aliased empty function');
$test->is($o->is_selectable('lc("able")'), 1, 'selects single arg function');
$test->is($o->is_selectable('lc("able") as "lc able"'), 1, 'selects aliased single arg function');
$test->is($o->is_selectable('lc("able")'), 1, 'selects double arg function');
$test->is($o->is_selectable('lc("able") as "lc able"'), 1, 'selects aliased double arg function');

=end btest

=cut

sub is_selectable {
	my $self = shift;

	my $value = shift or return $self->error("Cannot determine selectable-ness w/o value", "BDT-44");

	return 1 if $self->is_column($value);									#columns are selectable

	return 1 if $value =~ /^(\d+(\.\d+)?|\.\d+)(\s+(as|AS)\s*.+)?$/;		#numbers are selectable

	return 1 if $value =~ /^(['"]).*\1(\s+(as|AS)\s*.+)?$/;					#quoted strings are selectable

	return 1 if $value =~ /^[a-zA-Z]+\(.*\)(\s+(as|AS)\s*.+)?$/;			#functions are selectable

	return 0;

}

# this is used internally to do translations required by db_write_translation
#
# gets two args, $type and $col, and returns the 'val' in the hash if it is specified.
# otherwise, there is no change, so it returns a normal '?' placeholder.

=pod

=begin btest db_translate_write

my $o = __PACKAGE__->new();
$test->ok($o, "Created object");
my $def = {
	'able' => 'SQL_INTEGER',
	'baker' => 'SQL_INTEGER',
	'charlie' => 'SQL_INTEGER',
	'delta' => 'SQL_INTEGER'
};

$test->is($o->definition($def), $def, "Set definition");
$test->is($o->definition, $def, "Got definition");

$test->is(scalar($o->db_translate_write()), undef, 'Cannot db_translate_write w/o type');
$test->is($o->errcode, 'BDT-33', 'proper error code for db_translate_write (col)');
$test->is(scalar($o->db_translate_write('I')), undef, 'Cannot db_translate_write w/o cols');
$test->is($o->errcode, 'BDT-32', 'proper error code for db_translate_write (type)');

$test->is($o->db_translate_write('I', 'able'), '?', 'db_translate_write for able on insert');
$test->is($o->db_translate_write('I', 'baker'), '?', 'db_translate_write for baker on insert');
$test->is($o->db_translate_write('I', 'charlie'), '?', 'db_translate_write for charlie on insert');
$test->is($o->db_translate_write('I', 'delta'), '?', 'db_translate_write for delta on insert');

$test->is($o->db_translate_write('U', 'able'), '?', 'db_translate_write for able on update');
$test->is($o->db_translate_write('U', 'baker'), '?', 'db_translate_write for baker on update');
$test->is($o->db_translate_write('U', 'charlie'), '?', 'db_translate_write for charlie on update');
$test->is($o->db_translate_write('U', 'delta'), '?', 'db_translate_write for delta on update');

$test->is($o->db_translate_write('R', 'able'), '?', 'db_translate_write for able on replace');
$test->is($o->db_translate_write('R', 'baker'), '?', 'db_translate_write for baker on replace');
$test->is($o->db_translate_write('R', 'charlie'), '?', 'db_translate_write for charlie on replace');
$test->is($o->db_translate_write('R', 'delta'), '?', 'db_translate_write for delta on replace');

$test->is($o->db_translate_write('D', 'able'), '?', 'db_translate_write for able on delete');
$test->is($o->db_translate_write('D', 'baker'), '?', 'db_translate_write for baker on delete');
$test->is($o->db_translate_write('D', 'charlie'), '?', 'db_translate_write for charlie on delete');
$test->is($o->db_translate_write('D', 'delta'), '?', 'db_translate_write for delta on delete');

$test->is($o->db_translate_write('S', 'able'), '?', 'db_translate_write for able on select');
$test->is($o->db_translate_write('S', 'baker'), '?', 'db_translate_write for baker on select');
$test->is($o->db_translate_write('S', 'charlie'), '?', 'db_translate_write for charlie on select');
$test->is($o->db_translate_write('S', 'delta'), '?', 'db_translate_write for delta on select');

$test->is($o->db_translate_write('A', 'able'), '?', 'db_translate_write for able on all');
$test->is($o->db_translate_write('A', 'baker'), '?', 'db_translate_write for baker on all');
$test->is($o->db_translate_write('A', 'charlie'), '?', 'db_translate_write for charlie on all');
$test->is($o->db_translate_write('A', 'delta'), '?', 'db_translate_write for delta on all');

{
	my @insert_all = $o->db_translate_write('I', sort $o->cols);
	$test->is($insert_all[0], '?', 'able is ? on insert in multi pass');
	$test->is($insert_all[1], '?', 'baker is ? on insert in multi pass');
	$test->is($insert_all[2], '?', 'charlie is ? on insert in multi pass');
	$test->is($insert_all[3], '?', 'delta is ? on insert in multi pass');
	
	my @update_all = $o->db_translate_write('U', sort $o->cols);
	$test->is($update_all[0], '?', 'able is ? on update in multi pass');
	$test->is($update_all[1], '?', 'baker is ? on update in multi pass');
	$test->is($update_all[2], '?', 'charlie is ? on update in multi pass');
	$test->is($update_all[3], '?', 'delta is ? on update in multi pass');
	
	my @replace_all = $o->db_translate_write('R', sort $o->cols);
	$test->is($replace_all[0], '?', 'able is ? on replace in multi pass');
	$test->is($replace_all[1], '?', 'baker is ? on replace in multi pass');
	$test->is($replace_all[2], '?', 'charlie is ? on replace in multi pass');
	$test->is($replace_all[3], '?', 'delta is ? on replace in multi pass');
	
	my @delete_all = $o->db_translate_write('D', sort $o->cols);
	$test->is($delete_all[0], '?', 'able is ? on delete in multi pass');
	$test->is($delete_all[1], '?', 'baker is ? on delete in multi pass');
	$test->is($delete_all[2], '?', 'charlie is ? on delete in multi pass');
	$test->is($delete_all[3], '?', 'delta is ? on delete in multi pass');
	
	my @all_all = $o->db_translate_write('A', sort $o->cols);
	$test->is($all_all[0], '?', 'able is ? on all in multi pass');
	$test->is($all_all[1], '?', 'baker is ? on all in multi pass');
	$test->is($all_all[2], '?', 'charlie is ? on all in multi pass');
	$test->is($all_all[3], '?', 'delta is ? on all in multi pass');
}

my $translator = 	{
		'able' => {
			'I' => {
				'val' => 'ableprime',
				'binds' => 0
			},
		},
		'baker' => {
			'U' => {
				'val' => 'bakerprime',
				'binds' => 0,
			},
		},
		'charlie' => {
			'R' => {
				'val' => 'charlieprime',
				'binds' => 1,
			},
		},
		'delta' => {
			'D' => {
				'val' => 'deltaprime',
				'binds' => 0,
			},
		},
	};

$test->is($o->db_write_translation($translator), $translator, "Set translator");

$test->is($o->db_translate_write('I', 'able'), 'ableprime', 'db_translate_write for able on insert');
$test->is($o->db_translate_write('I', 'baker'), '?', 'db_translate_write for baker on insert');
$test->is($o->db_translate_write('I', 'charlie'), '?', 'db_translate_write for charlie on insert');
$test->is($o->db_translate_write('I', 'delta'), '?', 'db_translate_write for delta on insert');

$test->is($o->db_translate_write('U', 'able'), '?', 'db_translate_write for able on update');
$test->is($o->db_translate_write('U', 'baker'), 'bakerprime', 'db_translate_write for baker on update');
$test->is($o->db_translate_write('U', 'charlie'), '?', 'db_translate_write for charlie on update');
$test->is($o->db_translate_write('U', 'delta'), '?', 'db_translate_write for delta on update');

$test->is($o->db_translate_write('R', 'able'), '?', 'db_translate_write for able on replace');
$test->is($o->db_translate_write('R', 'baker'), '?', 'db_translate_write for baker on replace');
$test->is($o->db_translate_write('R', 'charlie'), 'charlieprime', 'db_translate_write for charlie on replace');
$test->is($o->db_translate_write('R', 'delta'), '?', 'db_translate_write for delta on replace');

$test->is($o->db_translate_write('D', 'able'), '?', 'db_translate_write for able on delete');
$test->is($o->db_translate_write('D', 'baker'), '?', 'db_translate_write for baker on delete');
$test->is($o->db_translate_write('D', 'charlie'), '?', 'db_translate_write for charlie on delete');
$test->is($o->db_translate_write('D', 'delta'), 'deltaprime', 'db_translate_write for delta on delete');

$test->is($o->db_translate_write('S', 'able'), '?', 'db_translate_write for able on select');
$test->is($o->db_translate_write('S', 'baker'), '?', 'db_translate_write for baker on select');
$test->is($o->db_translate_write('S', 'charlie'), '?', 'db_translate_write for charlie on select');
$test->is($o->db_translate_write('S', 'delta'), '?', 'db_translate_write for delta on select');

$test->is($o->db_translate_write('A', 'able'), '?', 'db_translate_write for able on all');
$test->is($o->db_translate_write('A', 'baker'), '?', 'db_translate_write for baker on all');
$test->is($o->db_translate_write('A', 'charlie'), '?', 'db_translate_write for charlie on all');
$test->is($o->db_translate_write('A', 'delta'), '?', 'db_translate_write for delta on all');

{
	my @insert_all = $o->db_translate_write('I', sort $o->cols);
	$test->is($insert_all[0], 'ableprime', 'able is ableprime on insert in multi pass');
	$test->is($insert_all[1], '?', 'baker is ? on insert in multi pass');
	$test->is($insert_all[2], '?', 'charlie is ? on insert in multi pass');
	$test->is($insert_all[3], '?', 'delta is ? on insert in multi pass');
	
	my @update_all = $o->db_translate_write('U', sort $o->cols);

	$test->is($update_all[0], '?', 'able is ? on update in multi pass');
	$test->is($update_all[1], 'bakerprime', 'baker is bakerprime on update in multi pass');
	$test->is($update_all[2], '?', 'charlie is ? on update in multi pass');
	$test->is($update_all[3], '?', 'delta is ? on update in multi pass');
	
	my @replace_all = $o->db_translate_write('R', sort $o->cols);
	$test->is($replace_all[0], '?', 'able is ? on replace in multi pass');
	$test->is($replace_all[1], '?', 'baker is ? on replace in multi pass');
	$test->is($replace_all[2], 'charlieprime', 'charlie is charlieprime on replace in multi pass');
	$test->is($replace_all[3], '?', 'delta is ? on replace in multi pass');
	
	my @delete_all = $o->db_translate_write('D', sort $o->cols);
	$test->is($delete_all[0], '?', 'able is ? on delete in multi pass');
	$test->is($delete_all[1], '?', 'baker is ? on delete in multi pass');
	$test->is($delete_all[2], '?', 'charlie is ? on delete in multi pass');
	$test->is($delete_all[3], 'deltaprime', 'delta is deltaprime on delete in multi pass');
	
	my @all_all = $o->db_translate_write('A', sort $o->cols);
	$test->is($all_all[0], '?', 'able is ? on all in multi pass');
	$test->is($all_all[1], '?', 'baker is ? on all in multi pass');
	$test->is($all_all[2], '?', 'charlie is ? on all in multi pass');
	$test->is($all_all[3], '?', 'delta is ? on all in multi pass');
}

my $translator2 = 	{
		'able' => {
			'S' => {
				'val' => 'ableselectorprime',
				'binds' => 0
			},
		},
		'baker' => {
			'A' => {
				'val' => 'bakerallprime',
				'binds' => 0,
			},
		},
		'charlie' => {
			'I' => {
				'val' => 'charlieinsertprime',
				'binds' => 1,
			},
			'A' => {
				'val' => 'charlieallprime',
				'binds' => 0,
			},
		},
	};

$test->is($o->db_write_translation($translator2), $translator2, "Set translator again");

$test->is($o->db_translate_write('I', 'able'), '?', 'db_translate_write for able on insert');
$test->is($o->db_translate_write('I', 'baker'), 'bakerallprime', 'db_translate_write for baker on insert');
$test->is($o->db_translate_write('I', 'charlie'), 'charlieinsertprime', 'db_translate_write for charlie on insert');
$test->is($o->db_translate_write('I', 'delta'), '?', 'db_translate_write for delta on insert');

$test->is($o->db_translate_write('U', 'able'), '?', 'db_translate_write for able on update');
$test->is($o->db_translate_write('U', 'baker'), 'bakerallprime', 'db_translate_write for baker on update');
$test->is($o->db_translate_write('U', 'charlie'), 'charlieallprime', 'db_translate_write for charlie on update');
$test->is($o->db_translate_write('U', 'delta'), '?', 'db_translate_write for delta on update');

$test->is($o->db_translate_write('R', 'able'), '?', 'db_translate_write for able on replace');
$test->is($o->db_translate_write('R', 'baker'), 'bakerallprime', 'db_translate_write for baker on replace');
$test->is($o->db_translate_write('R', 'charlie'), 'charlieallprime', 'db_translate_write for charlie on replace');
$test->is($o->db_translate_write('R', 'delta'), '?', 'db_translate_write for delta on replace');

$test->is($o->db_translate_write('D', 'able'), '?', 'db_translate_write for able on delete');
$test->is($o->db_translate_write('D', 'baker'), 'bakerallprime', 'db_translate_write for baker on delete');
$test->is($o->db_translate_write('D', 'charlie'), 'charlieallprime', 'db_translate_write for charlie on delete');
$test->is($o->db_translate_write('D', 'delta'), '?', 'db_translate_write for delta on delete');

$test->is($o->db_translate_write('S', 'able'), 'ableselectorprime', 'db_translate_write for able on select');
$test->is($o->db_translate_write('S', 'baker'), 'bakerallprime', 'db_translate_write for baker on select');
$test->is($o->db_translate_write('S', 'charlie'), 'charlieallprime', 'db_translate_write for charlie on select');
$test->is($o->db_translate_write('S', 'delta'), '?', 'db_translate_write for delta on select');

$test->is($o->db_translate_write('A', 'able'), '?', 'db_translate_write for able on all');
$test->is($o->db_translate_write('A', 'baker'), 'bakerallprime', 'db_translate_write for baker on all');
$test->is($o->db_translate_write('A', 'charlie'), 'charlieallprime', 'db_translate_write for charlie on all');
$test->is($o->db_translate_write('A', 'delta'), '?', 'db_translate_write for delta on all');

{
	my @insert_all = $o->db_translate_write('I', sort $o->cols);
	$test->is($insert_all[0], '?', 'able is ? on insert in multi pass');
	$test->is($insert_all[1], 'bakerallprime', 'baker is bakerallprime on insert in multi pass');
	$test->is($insert_all[2], 'charlieinsertprime', 'charlie is charlieinsertprime on insert in multi pass');
	$test->is($insert_all[3], '?', 'delta is ? on insert in multi pass');
	
	my @update_all = $o->db_translate_write('U', sort $o->cols);
	$test->is($update_all[0], '?', 'able is ? on update in multi pass');
	$test->is($update_all[1], 'bakerallprime', 'baker is bakerallprime on update in multi pass');
	$test->is($update_all[2], 'charlieallprime', 'charlie is charlieallprime on update in multi pass');
	$test->is($update_all[3], '?', 'delta is ? on update in multi pass');
	
	my @replace_all = $o->db_translate_write('R', sort $o->cols);
	$test->is($replace_all[0], '?', 'able is ? on replace in multi pass');
	$test->is($replace_all[1], 'bakerallprime', 'baker is bakerallprime on replace in multi pass');
	$test->is($replace_all[2], 'charlieallprime', 'charlie is charlieallprime on replace in multi pass');
	$test->is($replace_all[3], '?', 'delta is ? on replace in multi pass');
	
	my @delete_all = $o->db_translate_write('D', sort $o->cols);
	$test->is($delete_all[0], '?', 'able is ? on delete in multi pass');
	$test->is($delete_all[1], 'bakerallprime', 'baker is bakerallprime on delete in multi pass');
	$test->is($delete_all[2], 'charlieallprime', 'charlie is charlieallprime on delete in multi pass');
	$test->is($delete_all[3], '?', 'delta is ? on delete in multi pass');
	
	my @all_all = $o->db_translate_write('A', sort $o->cols);
	$test->is($all_all[0], '?', 'able is ? on all in multi pass');
	$test->is($all_all[1], 'bakerallprime', 'baker is bakerallprime on all in multi pass');
	$test->is($all_all[2], 'charlieallprime', 'charlie is charlieallprime on all in multi pass');
	$test->is($all_all[3], '?', 'delta is ? on all in multi pass');
}


=end btest

=cut

sub db_translate_write {
	my $self	= shift;
	my $type	= shift or return $self->error("Cannot do db_translate_write w/o type", "BDT-33");
	my @cols	= @_ or return $self->error("Cannot do db_translate_write w/o column", "BDT-32");

	my $db_write_translation = $self->db_write_translation;

	foreach my $col (@cols) {

		if (defined $db_write_translation->{$col}) {
			if (defined $db_write_translation->{$col}->{$type}){
				$col = $db_write_translation->{$col}->{$type}->{'val'};
			}
			elsif (defined $db_write_translation->{$col}->{'A'}){
				$col = $db_write_translation->{$col}->{'A'}->{'val'};
			} else {
				$col = '?';
			}
		} else {
			$col = '?';
		};
	}

	return wantarray ? @cols : $cols[0];
};

# this is used internally to do translations required by db_read_translation
#
# gets one argument, $col, and returns the 'val' in the hash if it is specified.
# otherwise, there is no change, so it returns the column

=pod

=begin btest db_translate_read

my $o = __PACKAGE__->new();
$test->ok($o, "Created object");
my $def = {
	'able' => 'SQL_INTEGER',
	'baker' => 'SQL_INTEGER',
	'charlie' => 'SQL_INTEGER',
	'delta' => 'SQL_INTEGER'
};

$test->is($o->definition($def), $def, "Set definition");
$test->is($o->definition, $def, "Got definition");
$test->is($o->name('table'), 'table', 'Set table name');

$test->is(scalar($o->db_translate_read()), undef, 'Cannot db_translate_read w/o col');
$test->is($o->errcode, 'BDT-34', 'proper error code for db_translate_read (col)');

$test->is($o->db_translate_read('able'), 'table.able', 'db_translate_read for able on select');
$test->is($o->db_translate_read('baker'), 'table.baker', 'db_translate_read for baker on select');
$test->is($o->db_translate_read('charlie'), 'table.charlie', 'db_translate_read for charlie on select');
$test->is($o->db_translate_read('delta'), 'table.delta', 'db_translate_read for delta on select');

my $translator = 	{
		'able' => 'ableselectorprime',
		'baker' => 'bakerallprime',
		'charlie' => 'charlieselectprime',
	};

$test->is($o->db_read_translation($translator), $translator, "Set translator again");

$test->is($o->db_translate_read('able'), 'ableselectorprime', 'db_translate_read for able on select');
$test->is($o->db_translate_read('baker'), 'bakerallprime', 'db_translate_read for baker on select');
$test->is($o->db_translate_read('charlie'), 'charlieselectprime', 'db_translate_read for charlie on select');
$test->is($o->db_translate_read('delta'), 'table.delta', 'db_translate_read for delta on select');

=end btest

=cut

sub db_translate_read {
	my $self	= shift;
	my @cols		= @_ or return $self->error("Cannot do db_translate_read w/o col", "BDT-34");

	my $db_read_translation = $self->db_read_translation;

	foreach my $col (@cols) {
		if (defined $db_read_translation->{$col}) {
			$col = $db_read_translation->{$col};
		} else {
			$col = $self->qualified_name($col);
		}
	}

	return wantarray ? @cols : $cols[0];
};

=pod

=item alias_column

Returns the aliased version of the column if one is defined in the column_aliases hash. Returns the column otherwise.

 $table->column_aliases(
 	{
 		'id' => 'user_id'
 	}
 );

 print $table->alias_column('id');		#prints user_id (uses alias)
 print $table->alias_column('name');	#prints name (no alias)

=cut

=pod

=begin btest alias_column

my $o = __PACKAGE__->new('name' => 'testtable1');
$test->ok($o, "Created object");
my $def = {
	'able' => 'SQL_INTEGER',
	'baker' => 'SQL_INTEGER',
	'charlie' => 'SQL_INTEGER',
	'delta' => 'SQL_INTEGER'
};

my $aliases = {
	'able' => 'aliased_able',
	'baker' => 'aliased_baker',
	'charlie' => 'aliased_charlie',
};

$test->is($o->definition($def), $def, "Set definition");
$test->is($o->definition, $def, "Got definition");

$test->is($o->column_aliases($aliases), $aliases, "Set column aliases");
$test->is($o->column_aliases(), $aliases, "Got column aliases");

$test->is(scalar($o->alias_column()), undef, "Could not alias_column w/o column");
$test->is($o->errcode, 'BDT-36', 'proper error code for alias_column (col)');

$test->is($o->alias_column('able'), 'aliased_able', 'properly aliased able');
$test->is($o->alias_column('baker'), 'aliased_baker', 'properly aliased baker');
$test->is($o->alias_column('charlie'), 'aliased_charlie', 'properly aliased charlie');
$test->is($o->alias_column('delta'), 'delta', 'properly aliased able (no alias)');

$test->is($o->alias_column('testtable1.able'), 'aliased_able', 'properly aliased able');
$test->is($o->alias_column('testtable1.baker'), 'aliased_baker', 'properly aliased baker');
$test->is($o->alias_column('testtable1.charlie'), 'aliased_charlie', 'properly aliased charlie');
$test->is($o->alias_column('testtable1.delta'), 'delta', 'properly aliased able (no alias)');

=end btest

=cut

sub alias_column {
	my $self	= shift;
	my @cols	= @_ or return $self->error('Cannot alias column w/o column', "BDT-36");

	my $aliases = $self->column_aliases;

	foreach my $col (@cols) {

		$col = $self->nonqualified_name($col);	
		$col = $aliases->{$col}
			if defined $aliases->{$col};
	}

	return wantarray ? @cols : $cols[0];
};

=pod

=item column_for_alias

Returns the non-aliased version of the column if one is defined in the column_aliases hash. Returns the column otherwise.

 $table->column_aliases(
 	{
 		'id' => 'user_id'
 	}
 );

 print $table->alias_column('user_id');	#prints id (undoes alias)
 print $table->alias_column('name');	#prints name (no alias)

=cut

=pod

=begin btest column_for_alias

my $o = __PACKAGE__->new('name' => 'testtable2');
$test->ok($o, "Created object");
my $def = {
	'able' => 'SQL_INTEGER',
	'baker' => 'SQL_INTEGER',
	'charlie' => 'SQL_INTEGER',
	'delta' => 'SQL_INTEGER'
};

my $aliases = {
	'able' => 'aliased_able',
	'baker' => 'aliased_baker',
	'charlie' => 'aliased_charlie',
};

$test->is($o->definition($def), $def, "Set definition");
$test->is($o->definition, $def, "Got definition");

$test->is($o->column_aliases($aliases), $aliases, "Set column aliases");
$test->is($o->column_aliases(), $aliases, "Got column aliases");

$test->is(scalar($o->column_for_alias()), undef, "Could not get column_for_alias w/o alias");
$test->is($o->errcode, 'BDT-35', 'proper error code for column_for_alias (alias)');

$test->is($o->column_for_alias('aliased_able'), 'able', 'properly unaliased able');
$test->is($o->column_for_alias('aliased_baker'), 'baker', 'properly unaliased baker');
$test->is($o->column_for_alias('aliased_charlie'), 'charlie', 'unproperly aliased charlie');
$test->is($o->column_for_alias('delta'), 'delta', 'properly aliased able (no alias)');

$test->is($o->column_for_alias('testtable2.aliased_able'), 'able', 'properly unaliased able');
$test->is($o->column_for_alias('testtable2.aliased_baker'), 'baker', 'properly unaliased baker');
$test->is($o->column_for_alias('testtable2.aliased_charlie'), 'charlie', 'unproperly aliased charlie');
$test->is($o->column_for_alias('testtable2.delta'), 'delta', 'properly aliased able (no alias)');

=end btest

=cut

sub column_for_alias {
	my $self	= shift;
	my $col		= shift or return $self->error("Cannot get column w/o alias", "BDT-35");

	$col = $self->nonqualified_name($col);

	my %rev;
	@rev{values %{$self->column_aliases}} = keys %{$self->column_aliases};
	if (defined $rev{$col}) {
		return $rev{$col};
	} else {
		return $col;
	};
};

=pod

=item insert_bindables

Returns the columns in this table that should be bound with values upon an insert.

 my @insertables = $table->insert_bindables();

=cut

=pod

=begin btest insert_bindables

my $o = __PACKAGE__->new();
$test->ok($o, "Created object");

my $def = {
	'able' => 'SQL_INTEGER',
	'baker' => 'SQL_INTEGER',
	'charlie' => 'SQL_INTEGER',
	'delta' => 'SQL_INTEGER'
};

$test->is($o->definition($def), $def, "Set definition");
$test->is($o->definition, $def, "Got definition");

my %bindable = map {$_, 1} $o->insert_bindables();

$test->is($bindable{'able'}, 1, 'able is insert bindable');
$test->is($bindable{'baker'}, 1, 'baker is insert bindable');
$test->is($bindable{'charlie'}, 1, 'charlie is insert bindable');
$test->is($bindable{'delta'}, 1, 'delta is insert bindable');

my $translator = {
	'able' => {
		'I' => {
			'val' => 'lc(?)',
			'binds' => 1
		},
		'A' => {
			'val' => 'NOW()',
			'binds' => 0,
		},
	},
	'baker' => {
		'I' => {
			'val' => 'NOW()',
			'binds' => 0
		},
		'A' => {
			'val' => '?',
			'binds' => 1,
		},
	},
	'charlie' => {
		'I' => {
			'val' => 'NOW()',
			'binds' => 0
		},
	},
	'delta' => {
		'A' => {
			'val' => 'NOW()',
			'binds' => 0
		},
	},
};

$test->is($o->db_write_translation($translator), $translator, 'set db_write_translation');

my %bindable2 = map {$_, 1} $o->insert_bindables();

$test->is($bindable2{'able'}, 1, 'able is insert bindable');
$test->is($bindable2{'baker'}, undef, 'baker is not insert bindable');
$test->is($bindable2{'charlie'}, undef, 'charlie is not insert bindable');
$test->is($bindable2{'delta'}, undef, 'delta is not insert bindable');

=end btest

=cut

sub insert_bindables {
	my $self = shift;
	if (my $bindables = $self->_cached_bindables->{'insert'}) {
		return @$bindables;
	} else {
		my @bindables = grep {$self->is_bindable('I', $_)} $self->insert_columns;
		$self->_cached_bindables->{'insert'} = \@bindables;
		return @bindables;
	};
};

=pod

=item replace_bindables

Returns the columns in this table that should be bound with values upon a replace.

 my @replaceables = $table->replace_bindables();

=cut

=pod

=begin btest replace_bindables

my $o = __PACKAGE__->new();
$test->ok($o, "Created object");

my $def = {
	'able' => 'SQL_INTEGER',
	'baker' => 'SQL_INTEGER',
	'charlie' => 'SQL_INTEGER',
	'delta' => 'SQL_INTEGER'
};

$test->is($o->definition($def), $def, "Set definition");
$test->is($o->definition, $def, "Got definition");

my %bindable = map {$_, 1} $o->replace_bindables();

$test->is($bindable{'able'}, 1, 'able is replace bindable');
$test->is($bindable{'baker'}, 1, 'baker is replace bindable');
$test->is($bindable{'charlie'}, 1, 'charlie is replace bindable');
$test->is($bindable{'delta'}, 1, 'delta is replace bindable');

my $translator = {
	'able' => {
		'R' => {
			'val' => 'lc(?)',
			'binds' => 1
		},
		'A' => {
			'val' => 'NOW()',
			'binds' => 0,
		},
	},
	'baker' => {
		'R' => {
			'val' => 'NOW()',
			'binds' => 0
		},
		'A' => {
			'val' => '?',
			'binds' => 1,
		},
	},
	'charlie' => {
		'R' => {
			'val' => 'NOW()',
			'binds' => 0
		},
	},
	'delta' => {
		'A' => {
			'val' => 'NOW()',
			'binds' => 0
		},
	},
};

$test->is($o->db_write_translation($translator), $translator, 'set db_write_translation');

my %bindable2 = map {$_, 1} $o->replace_bindables();

$test->is($bindable2{'able'}, 1, 'able is replace bindable');
$test->is($bindable2{'baker'}, undef, 'baker is not replace bindable');
$test->is($bindable2{'charlie'}, undef, 'charlie is not replace bindable');
$test->is($bindable2{'delta'}, undef, 'delta is not replace bindable');

=end btest

=cut

sub replace_bindables {
	my $self = shift;
	return grep {$self->is_bindable('R', $_)} $self->replace_columns;
};

=pod

=item update_bindables

Returns the columns in this table that should be bound with values upon an update.

 my @updatables = $table->update_bindables();

=cut

=pod

=begin btest update_bindables

my $o = __PACKAGE__->new();
$test->ok($o, "Created object");

my $def = {
	'able' => 'SQL_INTEGER',
	'baker' => 'SQL_INTEGER',
	'charlie' => 'SQL_INTEGER',
	'delta' => 'SQL_INTEGER'
};

$test->is($o->definition($def), $def, "Set definition");
$test->is($o->definition, $def, "Got definition");

my %bindable = map {$_, 1} $o->update_bindables();

$test->is($bindable{'able'}, 1, 'able is update bindable');
$test->is($bindable{'baker'}, 1, 'baker is update bindable');
$test->is($bindable{'charlie'}, 1, 'charlie is update bindable');
$test->is($bindable{'delta'}, 1, 'delta is update bindable');

my $translator = {
	'able' => {
		'U' => {
			'val' => 'lc(?)',
			'binds' => 1
		},
		'A' => {
			'val' => 'NOW()',
			'binds' => 0,
		},
	},
	'baker' => {
		'U' => {
			'val' => 'NOW()',
			'binds' => 0
		},
		'A' => {
			'val' => '?',
			'binds' => 1,
		},
	},
	'charlie' => {
		'U' => {
			'val' => 'NOW()',
			'binds' => 0
		},
	},
	'delta' => {
		'A' => {
			'val' => 'NOW()',
			'binds' => 0
		},
	},
};

$test->is($o->db_write_translation($translator), $translator, 'set db_write_translation');

my %bindable2 = map {$_, 1} $o->update_bindables();

$test->is($bindable2{'able'}, 1, 'able is update bindable');
$test->is($bindable2{'baker'}, undef, 'baker is not update bindable');
$test->is($bindable2{'charlie'}, undef, 'charlie is not update bindable');
$test->is($bindable2{'delta'}, undef, 'delta is not update bindable');

=end btest

=cut

__PACKAGE__->add_attr('_cached_bindables');

sub update_bindables {
	my $self = shift;
	if (my $bindables = $self->_cached_bindables->{'update'}) {
		return @$bindables;
	} else {
        my @excess = $self->primary_cols;
		my @bindables = grep {$self->is_bindable('U', $_)} ($self->update_columns, @excess);
		$self->_cached_bindables->{'update'} = \@bindables;
		return @bindables;
	};
}

=pod

=item delete_bindables

Returns the columns in this table that should be bound with values upon an delete.

 my @deletables = $table->delete_bindables();

=cut

=pod

=begin btest delete_bindables

my $o = __PACKAGE__->new();
$test->ok($o, "Created object");

my $def = {
	'able' => 'SQL_INTEGER',
	'baker' => 'SQL_INTEGER',
	'charlie' => 'SQL_INTEGER',
	'delta' => 'SQL_INTEGER'
};

$test->is($o->definition($def), $def, "Set definition");
$test->is($o->definition, $def, "Got definition");

my %bindable = map {$_, 1} grep {defined} $o->delete_bindables();

$test->is($bindable{'able'}, undef, 'able is not delete bindable');
$test->is($bindable{'baker'}, undef, 'baker is not delete bindable');
$test->is($bindable{'charlie'}, undef, 'charlie is not delete bindable');
$test->is($bindable{'delta'}, undef, 'delta is not delete bindable');

$o->primary_column('able');

my %bindable2 = map {$_, 1} $o->delete_bindables();

$test->is($bindable2{'able'}, 1, 'able is delete bindable');
$test->is($bindable2{'baker'}, undef, 'baker is not delete bindable');
$test->is($bindable2{'charlie'}, undef, 'charlie is not delete bindable');
$test->is($bindable2{'delta'}, undef, 'delta is not delete bindable');

$o->primary_column(['charlie', 'delta']);

my %bindable3 = map {$_, 1} $o->delete_bindables();

$test->is($bindable3{'able'}, undef, 'able is not delete bindable');
$test->is($bindable3{'baker'}, undef, 'baker is not delete bindable');
$test->is($bindable3{'charlie'}, 1, 'charlie is delete bindable');
$test->is($bindable3{'delta'}, 1, 'delta is delete bindable');


=end btest

=cut

sub delete_bindables {
	my $self = shift;
	return $self->primary_cols;
};

=pod

=begin btest select_bindables

my $o = __PACKAGE__->new();
$test->ok($o, "Created object");

my $def = {
	'able' => 'SQL_INTEGER',
	'baker' => 'SQL_INTEGER',
	'charlie' => 'SQL_INTEGER',
	'delta' => 'SQL_INTEGER'
};

$test->is($o->definition($def), $def, "Set definition");
$test->is($o->definition, $def, "Got definition");

my %bindable = map {$_, 1} grep {defined} $o->select_bindables;

$test->is($bindable{'able'}, undef, 'able is not select bindable');
$test->is($bindable{'baker'}, undef, 'baker is not select bindable');
$test->is($bindable{'charlie'}, undef, 'charlie is not select bindable');
$test->is($bindable{'delta'}, undef, 'delta is not select bindable');

$o->primary_column('able');

my %bindable2 = map {$_, 1} $o->select_bindables();

$test->is($bindable2{'able'}, 1, 'able is select bindable');
$test->is($bindable2{'baker'}, undef, 'baker is not select bindable');
$test->is($bindable2{'charlie'}, undef, 'charlie is not select bindable');
$test->is($bindable2{'delta'}, undef, 'delta is not select bindable');

$o->primary_column(['charlie', 'delta']);

my %bindable3 = map {$_, 1} $o->select_bindables();

$test->is($bindable3{'able'}, undef, 'able is not select bindable');
$test->is($bindable3{'baker'}, undef, 'baker is not select bindable');
$test->is($bindable3{'charlie'}, 1, 'charlie is select bindable');
$test->is($bindable3{'delta'}, 1, 'delta is select bindable');

=end btest

=cut

sub select_bindables {
	my $self = shift;

	return $self->primary_cols;
}

=pod

=item insert_query

Returns an insert query for this table.

 my $insert_query = $table->insert_query();

The query is a full insert with columns defined in the query. You may also pass
in an array of columns to use in the insert. Otherwise, all columns defined in the
table will be used.

 my $insert_qery = $table->insert_query('foo');

Returns the insert query but only to be able to insert into column 'foo'. If you try
to use a column that is not in the table, you'll get an error.

=cut

=pod

=begin btest insert_query

my $o = __PACKAGE__->new();
$test->ok($o, "Created object");

my $def = {
	'able' => 'SQL_INTEGER',
	'baker' => 'SQL_INTEGER',
	'charlie' => 'SQL_INTEGER',
	'delta' => 'SQL_INTEGER'
};

$test->is($o->definition($def), $def, "Set definition");
$test->is($o->definition, $def, "Got definition");

$test->is($o->name('mytable'), 'mytable', "set tablename");

{
	my @insert = $o->insert_columns;

	$test->is($o->insert_query, "insert into mytable (" . join(', ', @insert)
		. ") values (?, ?, ?, ?)", "got default insert query");
}

{
	my @insert = $o->insert_columns(['able']);

	$test->is($o->insert_query, "insert into mytable (" . join(', ', @insert)
		. ") values (?)", "got able insert query");
	$o->insert_columns(undef);
}

{
	my @insert = $o->insert_columns(['able', 'baker']);

	$test->is($o->insert_query, "insert into mytable (" . join(', ', @insert)
		. ") values (?, ?)", "got able, baker insert query");
	$o->insert_columns(undef);
}

{

	$test->is(scalar($o->insert_query('foo')), undef, "Could not get insert query w/invalid column");
	$test->is($o->errcode, 'BDT-07', 'proper error code');
}

{
	$test->is($o->insert_query('able', 'baker'), "insert into mytable (able, baker) values (?, ?)", "got able, baker insert query");
}

{
	my $translator = 	{
			'able' => {
				'I' => {
					'val' => 'lc(?)',
					'binds' => 0
				},
			},
			'baker' => {
				'A' => {
					'val' => 'uc(?)',
					'binds' => 0,
				},
			},
		};

	$test->is($o->db_write_translation($translator), $translator, "Set translator");

	my @insert = $o->insert_columns;

	my $q = "insert into mytable (" . join(', ', @insert)
		. ") values (" . join(', ', map{$_ eq 'able' ? 'lc(?)' : $_ eq 'baker' ? 'uc(?)' : '?'} @insert) . ")";

	$test->is($o->insert_query, $q, "got re-written default insert query");

}

=end btest

=cut

sub insert_query {
	my $self = shift;

	my @cols = @_;

	if (@cols){
		foreach my $col (@cols){
			return $self->error("Cannot insert column not in table : $col", "BDT-07")
				unless $self->is_column($col);
		};
	} else {
		@cols = $self->insert_columns;
	}

	my $querykey = join(',', 'insert', @cols);

	my $query = $self->_cached_queries->{$querykey} || "insert into " . $self->name . " ("
		. join(', ', @cols)
		. ") values ("
		. join(", ", $self->db_translate_write('I', @cols)) #map { $self->db_translate_write($_, 'I') } @cols)
		. ")";

	$self->_cached_queries->{$querykey} = $query;

	return $query;
};

=pod

=item replace_query

Returns an replace query for this table.

 my $replace_query = $table->replace_query();

The query is a full replace with columns defined in the query. You may also pass
in an array of columns to use in the insert. Otherwise, all columns defined in the
table will be used.

 my $replace_qery = $table->replace_query('foo');

Returns the replace query but only to be able to replace into column 'foo'. If you try
to use a column that is not in the table, you'll get an error.

=cut

=pod

=begin btest replace_query

my $o = __PACKAGE__->new();
$test->ok($o, "Created object");

my $def = {
	'able' => 'SQL_INTEGER',
	'baker' => 'SQL_INTEGER',
	'charlie' => 'SQL_INTEGER',
	'delta' => 'SQL_INTEGER'
};

$test->is($o->definition($def), $def, "Set definition");
$test->is($o->definition, $def, "Got definition");

$test->is($o->name('mytable'), 'mytable', "set tablename");

{
	my @replace = $o->replace_columns;

	$test->is($o->replace_query, "replace into mytable (" . join(', ', @replace)
		. ") values (?, ?, ?, ?)", "got default replace query");
}

{
	my @replace = $o->replace_columns(['able']);

	$test->is($o->replace_query, "replace into mytable (" . join(', ', @replace)
		. ") values (?)", "got able replace query");
	$o->replace_columns(undef);
}

{
	my @replace = $o->replace_columns(['able', 'baker']);

	$test->is($o->replace_query, "replace into mytable (" . join(', ', @replace)
		. ") values (?, ?)", "got able, baker replace query");
	$o->replace_columns(undef);
}

{

	$test->is(scalar($o->replace_query('foo')), undef, "Could not get replace query w/invalid column");
	$test->is($o->errcode, 'BDT-08', 'proper error code');
}

{
	$test->is($o->replace_query('able', 'baker'), "replace into mytable (able, baker) values (?, ?)", "got able, baker replace query");
}

{
	my $translator = 	{
			'able' => {
				'R' => {
					'val' => 'lc(?)',
					'binds' => 0
				},
			},
			'baker' => {
				'A' => {
					'val' => 'uc(?)',
					'binds' => 0,
				},
			},
		};

	$test->is($o->db_write_translation($translator), $translator, "Set translator");

	my @replace = $o->replace_columns;

	my $q = "replace into mytable (" . join(', ', @replace)
		. ") values (" . join(', ', map{$_ eq 'able' ? 'lc(?)' : $_ eq 'baker' ? 'uc(?)' : '?'} @replace) . ")";

	$test->is($o->replace_query, $q, "got re-written default replace query");

}

=end btest

=cut

sub replace_query {
	my $self = shift;

	my @cols = @_;

	if (@cols){
		foreach my $col (@cols){
			return $self->error("Cannot replace column not in table : $col", "BDT-08")
				unless $self->is_column($col);
		};
	};

	@cols = $self->replace_columns unless @cols;

	my $querykey = join(',', 'replace', @cols);

	my $query = $self->_cached_queries->{$querykey} || "replace into " . $self->name . " ("
		. join(', ', @cols)
		. ") values ("
		. join(", ", $self->db_translate_write('R', @cols)) #map { $self->db_translate_write($_, 'R') } @cols)
		. ")";

	$self->_cached_queries->{$querykey} = $query;

	return $query;
};

=pod

=item update_query

Returns an update_query query for this table.

 my $update_query = $table->update_query();

The query is a full update with columns defined in the query. You may also pass
in an array of columns to use in the insert. Otherwise, all columns defined in the
table will be used.

 my $update_query = $table->update_query('foo');

Returns the update query but only to be able to update column 'foo'. If you try
to use a column that is not in the table, you'll get an error.

Be warned that no where clause is attached

=cut

=pod

=begin btest update_query

my $o = __PACKAGE__->new();
$test->ok($o, "Created object");

my $def = {
	'able' => 'SQL_INTEGER',
	'baker' => 'SQL_INTEGER',
	'charlie' => 'SQL_INTEGER',
	'delta' => 'SQL_INTEGER'
};

$test->is($o->definition($def), $def, "Set definition");
$test->is($o->definition, $def, "Got definition");

$test->is($o->name('mytable'), 'mytable', "set tablename");

{
	my @update = $o->update_columns;

	$test->is($o->update_query, "update mytable set " . join(', ', map {"$_ = ?"} @update), "got default update query");
}

{
	my @update = $o->update_columns(['able']);

	$test->is($o->update_query, "update mytable set " . join(', ', map {"$_ = ?"} @update), "got able update query");

	$o->update_columns(undef);
}

{
	my @update = $o->update_columns(['able', 'baker']);

	$test->is($o->update_query, "update mytable set " . join(', ', map {"$_ = ?"} @update), "got able, baker update query");
	$o->update_columns(undef);
}

{

	$test->is(scalar($o->update_query('foo')), undef, "Could not get update query w/invalid column");
	$test->is($o->errcode, 'BDT-06', 'proper error code');
}

{
	$test->is($o->update_query('able', 'baker'), "update mytable set able = ?, baker = ?", "got able, baker update query");
}

{
	my $translator = 	{
			'able' => {
				'U' => {
					'val' => 'lc(?)',
					'binds' => 0
				},
			},
			'baker' => {
				'A' => {
					'val' => 'uc(?)',
					'binds' => 0,
				},
			},
		};

	$test->is($o->db_write_translation($translator), $translator, "Set translator");

	my @update = $o->update_columns;

	my $q = "update mytable set " . join(', ', map{$_ . ' = ' . ($_ eq 'able' ? 'lc(?)' : $_ eq 'baker' ? 'uc(?)' : '?')} @update);

	$test->is($o->update_query, $q, "got re-written default update query");

}

=end btest

=cut

sub update_query {
	my $self = shift;

	my @cols = @_;

	if (@cols){
		foreach my $col (@cols){
			return $self->error("Cannot update column not in table : $col", "BDT-06")
				unless $self->is_column($col);
		};
	} else {
		@cols = $self->update_columns;
	}

	#my $where = " where " . join(' and ', map {"$_ = ?"} $self->primary_cols);

	my $querykey = join(',', 'update', @cols);

	my $query = $self->_cached_queries->{$querykey} || "update " . $self->name . " set "
		. join(', ', map {$_ . " = " . $self->db_translate_write('U', $_)} @cols)
		;#	. $where;

	$self->_cached_queries->{$querykey} = $query;

	return $query;
};

=pod

=item delete_query

returns a delete query for this table.

 my $delete_query = $table->delete_query

Be warned that no where clause is attached

=cut

=pod

=begin btest delete_query

my $o = __PACKAGE__->new();
$test->ok($o, "Created object");

$test->is($o->name('mytable'), 'mytable', "set tablename");

$test->is($o->delete_query, 'delete from mytable', 'proper delete query');

=end btest

=cut

sub delete_query {
	my $self = shift;

	return "delete from " . $self->name;
};

=pod

=item select_query

Returns an select_query query for this table.

 my $select_query = $table->select_query();

The query is a full update with columns defined in the query. You may also pass
in an array of columns to use in the select. Otherwise, all columns defined in the
table will be used.

 my $select_query = $table->select_query('foo');

Returns the select query but only to be able to select column 'foo'. If you try
to use a column that is not in the table, you'll get an error.

Be warned that no where clause is attached

=cut

=pod

=begin btest select_query

my $o = __PACKAGE__->new();
$test->ok($o, "Created object");

my $def = {
	'able' => 'SQL_INTEGER',
	'baker' => 'SQL_INTEGER',
	'charlie' => 'SQL_INTEGER',
	'delta' => 'SQL_INTEGER'
};

$test->is($o->definition($def), $def, "Set definition");
$test->is($o->definition, $def, "Got definition");

$test->is($o->name('mytable'), 'mytable', "set tablename");

$test->is(scalar($o->select_query('fake')), undef, 'Could not select unknown column');
$test->is($o->errcode, 'BDT-05', 'proper error code');

{
	my @select = $o->select_columns;

	$test->is($o->select_query, 'select ' . join(', ', map {"mytable.$_ as $_"} @select) . ' from mytable', "got default select query");	

}

{
	my @select = $o->select_columns(['able']);

	$test->is($o->select_query, 'select ' . join(', ', map {"mytable.$_ as $_"} @select) . ' from mytable', "got able select query");	

	$o->select_columns(undef);
}

{
	my @select = $o->select_columns(['able', 'baker']);

	$test->is($o->select_query, 'select ' . join(', ', map {"mytable.$_ as $_"} @select) . ' from mytable', "got able baker select query");	

	$o->select_columns(undef);
}

{
	$test->is($o->select_query('able', 'baker'), 'select mytable.able as able, mytable.baker as baker from mytable', 'got able baker passed select query');
}

{
	$test->is($o->select_query('NOW()', '"const1"', "'const2'", 2004), 'select NOW(), "const1", \'const2\', 2004 from mytable', 'got function, constant select query');
}

{
	my $translator = 	{
		'able' => 'lc(able)',
		'baker' => 'uc(baker)',
	};

	my @select = $o->select_columns();

	$test->is($o->db_read_translation($translator), $translator, "Set translator");

	$test->is($o->select_query, 'select ' . join(', ', map{($_ eq 'able' ? 'lc(able)' : $_ eq 'baker' ? 'uc(baker)' : "mytable.$_") . " as $_"} @select) . ' from mytable', "got translated select query");	

	$o->db_read_translation({});
}

{
	my $extra = {
		'current_time' => 'NOW()'
	};

	$test->is($o->extra_select($extra), $extra, "Set extra select");

	my @select = ((map {"mytable.$_ as $_"} $o->select_columns), (map {$extra->{$_} . ' as ' . $_} keys %$extra));

	$test->is($o->select_query, 'select ' . join(', ', @select) . ' from mytable', "got extra selecting select query");
};

=end btest

=cut

sub select_query {
	my $self = shift;

	my @cols = @_;

	if (@cols){
		foreach my $col (@cols){
			return $self->error("Cannot select column not in table : $col", "BDT-05")
				unless $self->is_selectable($col);
				#regex matches numbers, "foo", 'foo', or function(), also used below
				#in constructing the query
		};
	} else {
		@cols = ($self->select_columns, keys %{$self->extra_select});
	};

	my $querykey = join(',', 'select', @cols);

	my $query = $self->_cached_queries->{$querykey} || "select "
		. join(', ', map {
						$self->extra_select->{$_}
							? $self->extra_select->{$_} . ' as ' . $_
							: $self->is_column($_)
								? $self->db_translate_read($_) . ' as ' . $self->alias_column($_)
								: $_
			}
			@cols)
		. " from " . $self->name;

	$self->_cached_queries->{$querykey} = $query;

	return $query;

};

=pod

=item multiselect_query

Magic time. The multiselect_query allows you to auto-build and execute select queries across multiple tables. Expects up
to two arguments, in a hash.

=over

=item tables

The table objects that will be joined in this select statement. You need at least one table, but if you're only selecting one
table, you should probably just use its select_query.

=item cols

The list of columns to select in the join. If this is not specified, then all columns in all tables will be used.
NOTE THAT COLUMN ALIASES WILL NOT BE USED UNLESS YOU PASS THE use_aliases flag.

 $table->multiselect_query('tables' => $tables, 'use_aliases' => 1);

This is by design, it is assumed that most of the time, you're using a multi select query when doing an arbitrary_sql call to get
back massive amounts of data and you need to know the original column name, and the table it was from.

=back

Most of the time, hiding behind Basset's object persistence capabilities are more than sufficient. You can load up objects,
manipulate them, write them back out. Everything's peachy. But some of the time, you just need data. Lots of data. And you need
it fast. Real fast. Basset doesn't deal well with that.

Let's say you have a table of users and a table (that serves as a log) of login information. Each time the user logs in, you
insert an entry into the login table. You want to get a list of all users and the number of times they've logged in.

You can do this with the standard methods.

 my $users = Some::User->load_all();

 foreach my $user (@$users) {
 	print $user->name, " logged in : ", $user->logininformation, "\n";	#assuming logininformation wrappers what we want
 }

But there's a lot of overhead involved in that and it's not necessarily the fastest way to do it. Sure, in this case, 
it makes sense. But it might not always. So, instead, you can do a multiselect_query. Let's define the tables for 
clarity, and we'll even assume they're in different packages.

 my $user_table = Basset::DB::Table->new(
 	'name' => 'user',
 	'primary_column' => 'id',
 	'definition' => {
 		'id'	=> 'SQL_INTEGER',
 		'name'	=> 'SQL_VARCHAR'
 	}
 );

 my $login_table = Basset::DB::Table->new(
 	'name' => 'login',
 	'primary_column' => 'id',
 	'definition' => {
 		'id' => 'SQL_INTEGER'
 		'user_id'	=> 'SQL_INTEGER',
 		'login_time'=> 'SQL_TIMESTAMP'
 	},
 	'references' => {
 		'user_id' => 'user.id'
 	}
 );

 my $q = Basset::DB::Table->multiselect_query(
 	'tables' => [$user_table, $login_table],
 );

 print "$q\n";

This prints out:

 select
 	user.name,
 	user.id,
 	login.login_time,
 	login.user_id,
 	login.id,
 from
 	user inner join login
 on user.id = login.user_id

So now we have one query that will get us back all of our data. But we're still yanking back too much. We
actually only care about the user and the total login info. We can fix that by specifying the columns we want. Please note that you
need to qualify the column names.

 my $q = Basset::DB::Table->multiselect_query(
 	'tables' => [$user_table, $login_table],
 	'cols'	=> [qw[user.id user.name count(*)]]
 ) or die Basset::DB::Table->errstring;

 print "$q\n";

This prints out:

 select
 	user.id,
 	user.name,
 	count(*)
 from
 	user inner join login
 on user.id = login.user_id

Closer, but still not quite there. For one thing, this will ignore any users that have never logged in, since they don't have
an entry in the login table. Easy to fix, specify the join type:

 my $q = Basset::DB::Table->multiselect_query(
 	'tables' => [
 		$user_table,
 		['left', $login_table]
 	],
	'cols'	=> [qw[user.id name], 'coalesce(count(*), 0) as count'],
 ) or die Basset::DB::Table->errstring;

 print "$q\n";

This prints out:

 select
 	user.id as id,
 	user.name as name,
	coalesce(count(*), 0) as count
 from
 	user left join login
 on user.id = login.user_id

That's all of the data we want, but we're still missing something - the group by clause. So we attach one. We'll even tack
on an order by clause for good measure so we don't need to sort later.

 my $q = Basset::DB::Table->attach_to_query(
 	Basset::DB::Table->multiselect_query(
		'tables' => [
			$user_table,
			['left', $login_table]
		],
		'cols'	=> [qw[user.id name], 'coalesce(count(*), 0) as count'],
	 ) ,
 	{
 		'group by' => 'user.id, name',
 		'order by' => 'count',
 	}
 );

 print "$q\n";

This prints out:

 select
 	user.id as id,
 	user.name as name,
 	coalesce(count(*), 0) as count
 from
 	user left join login
 on user.id = login.user_id
 group by
 	user.id, name
 order by
 	count

And voila! We're done. Hand that query off to whatever method it is you use to run sql queries (such as Basset::Object::Persistent's
arbitrary_sql method), get back your data, and you're all set.

=cut

=pod

=begin btest multiselect_query

my $o = __PACKAGE__->new();
$test->ok($o, "Created object");

my $o2 = __PACKAGE__->new();
$test->ok($o2, "Created object");

my $o3 = __PACKAGE__->new();
$test->ok($o3, "Created object");

my $o4 = __PACKAGE__->new();
$test->ok($o4, "Created object");

my $def1 = {
	'able' => 'SQL_INTEGER',
	'baker' => 'SQL_INTEGER',
	'charlie' => 'SQL_INTEGER',
	'delta' => 'SQL_INTEGER'
};

my $ref1 = {
	'able' => 'table2.aid',
	'baker' => 'table4.b',
	'charlie' => 'table4.c',
};

$test->is($o->definition($def1), $def1, "Set definition");
$test->is($o->references($ref1), $ref1, "Set references");
$test->is($o->name("table1"), "table1", "set table name");
$test->is($o->primary_column("able"), "able", "set table primary id");

my $def2 = {
	'aid' => 'SQL_INTEGER',
	'bid' => 'SQL_INTEGER',
	'cid' => 'SQL_INTEGER',
	'did' => 'SQL_INTEGER'
};

my $ref2 = {
	'aid' => 'table3.ace'
};

my $aliases2 = {
	'aid' => 'ALIASED_aid',
};

$test->is($o2->definition($def2), $def2, "Set definition");
$test->is($o2->references($ref2), $ref2, "Set references");
$test->is($o2->column_aliases($aliases2), $aliases2, "Set aliases");
$test->is($o2->name("table2"), "table2", "set table name");
$test->is($o2->primary_column("aid"), "aid", "set table primary id");

my $def3 = {
	'ace' => 'SQL_INTEGER',
	'bogey' => 'SQL_INTEGER',
};

$test->is($o3->definition($def3), $def3, "Set definition");
$test->is($o3->name("table3"), "table3", "set table name");
$test->is($o3->primary_column("ace"), "ace", "set table primary id");

my $def4 = {
	'a' => 'SQL_INTEGER',
	'b' => 'SQL_INTEGER',
	'c' => 'SQL_INTEGER',
	'd' => 'SQL_INTEGER'
};

$test->is($o4->definition($def4), $def4, "Set definition");
$test->is($o4->name("table4"), "table4", "set table name");
$test->ok($o4->primary_column([qw(b c)]), "set table primary id");

my @o1select = $o->select_columns();
my @o2select = $o2->select_columns();
my @o3select = $o3->select_columns();
my @o4select = $o4->select_columns();

$test->is(scalar(__PACKAGE__->multiselect_query()), undef, "Could not multiselect w/o tables");
$test->is(__PACKAGE__->errcode, 'BDT-47', 'proper error code');

$test->is(__PACKAGE__->multiselect_query('tables' => $o),
"select " . (join(", ",map {"table1.$_ as $_"} @o1select)) . " from table1",
'multi selected single table');

$test->is(__PACKAGE__->multiselect_query('tables' => [$o]),
"select " . (join(", ",map {"table1.$_ as $_"} @o1select)) . " from table1",
'multi selected single table in arrayref');


$test->is(__PACKAGE__->multiselect_query('cols' => 'able', 'tables' => $o),
"select " . (join(", ",map {"table1.$_ as $_"} ('able'))) . " from table1",
'multi selected single table, different cols');

$test->is(__PACKAGE__->multiselect_query('cols' => ['able'], 'tables' => $o),
"select " . (join(", ",map {"table1.$_ as $_"} ('able'))) . " from table1",
'multi selected single table, different cols in arrayref');

$test->is(__PACKAGE__->multiselect_query('cols' => ['able'], 'tables' => [$o]),
"select " . (join(", ",map {"table1.$_ as $_"} ('able'))) . " from table1",
'multi selected single table in arrayref, different cols');

$test->is(__PACKAGE__->multiselect_query('tables' => [$o, $o2]),
"select\n" . (join(",\n",map {"\ttable1.$_"} @o1select)) 
. ",\n" . (join(",\n",map {"\ttable2.$_"} @o2select)) . "\nfrom\n" .
"table1
	inner join
		table2
			on table1.able = table2.aid",
'multi selected multi table in arrayref');

$test->is(__PACKAGE__->multiselect_query('cols' => ['able', 'baker'], 'tables' => [$o, $o2]),
"select\n" . (join(",\n",map {"\t$_"} ('able'))) 
. ",\n" . (join(",\n",map {"\t$_"} ('baker'))) . "\nfrom\n" .
"table1
	inner join
		table2
			on table1.able = table2.aid",
'multi selected multi table in arrayref with differing columns');

$test->is(__PACKAGE__->multiselect_query('use_aliases' => 1, 'tables' => [$o, $o2]),
"select\n" . (join(",\n",map {"\ttable1.$_ as " . $o2->alias_column($_)} @o1select)) 
. ",\n" . (join(",\n",map {"\ttable2.$_ as " . $o2->alias_column($_)} @o2select)) . "\nfrom\n" .
"table1
	inner join
		table2
			on table1.able = table2.aid",
'multi selected multi table in arrayref with aliases');

=end btest

=cut

sub multiselect_query {
	my $class = shift;

	my %init = (
		'cols'			=> [],
		'use_aliases'	=> 0,
		@_
	);

	return $class->error("Cannot multi-select w/o tables", "BDT-47") unless defined $init{'tables'};

	$init{'tables'} = [$init{'tables'}] unless ref $init{'tables'} eq 'ARRAY';
	$init{'cols'} = [$init{'cols'}] unless ref $init{'cols'} eq 'ARRAY';

	if (@{$init{'tables'}} == 1) {
		return $init{'tables'}->[0]->select_query(@{$init{'cols'}});
	};

	my $joined_tables = $class->join_tables(@{$init{'tables'}}) or return;

	my %omit = ();

	if ($init{'omit_columns_from_tables'}) {
		%omit = map {$_->name, 1} @{$init{'omit_columns_from_tables'}};
	}

	unless (@{$init{'cols'}}) {
		#we duplicate the for loop to keep from doing the condition constantly in the loop. Lazy, I know.
		if ($init{'use_aliases'}) {
			foreach my $table (@{$init{'tables'}}) {
				next if $omit{$table->name};
				push @{$init{'cols'}}, map {$table->db_translate_read($_) . ' as ' . $table->alias_column($_)} $table->select_columns;
			}
		} else {
			foreach my $table (@{$init{'tables'}}) {
				next if $omit{$table->name};
				push @{$init{'cols'}}, map {$table->db_translate_read($_)} $table->select_columns;
			}
		}
	};

	return "select\n\t" . join(",\n\t", @{$init{'cols'}}) . "\nfrom\n" . $joined_tables;
}

=pod

=item count_query

Returns a count query ("select count(*) from $table").

 my $count_query = $table->count_query();

Be warned that no where clause is attached.

=cut

=pod

=begin btest count_query

my $o = __PACKAGE__->new();
$test->ok($o, "Got object");

$test->is($o->name('count_query_table'), 'count_query_table', 'Set table name');
$test->is($o->count_query, 'select count(1) as count from count_query_table', 'Got count query');

=end btest

=cut

sub count_query {
	my $self = shift;

	return "select count(1) as count from " . $self->name;
};

=pod

=item optimize_query

Returns an optimize table query.

 my $optimize_query = $table->optimize_query();

=cut

=pod

=begin btest optimize_query

my $o = __PACKAGE__->new();
$test->ok($o, "Got object");
$test->is($o->name("test table"), "test table", "set table name");
$test->is($o->optimize_query, 'optimize table test table', "got optimize query");

=end btest

=cut

sub optimize_query {
	my $self = shift;

	return "optimize table " . $self->name;
};

=pod

=item describe_query

Returns an describe table query.

 my $describe_query = $table->describe_query();

=cut

=pod

=begin btest describe_query

my $o = __PACKAGE__->new();
$test->ok($o, "Got object");
$test->is($o->name("test table"), "test table", "set table name");
$test->is($o->describe_query, 'desc test table', "got desc query");

=end btest

=cut


sub describe_query {
	my $self = shift;

	return "desc " . $self->name;
};

=pod

=item reference_query

Given a column, returns a count query referencing the other table to determine whether the key is valid.

 $table->references(
 	{
 		'user_id' => 'user.id',
 		'user_name' => 'user.name'
 	}
 );

 print $table->reference_query('user_id');	#prints select count(1) from user where id = ?

 print $table->reference_query('login');	#prints nothing

=cut

=pod

=begin btest reference_query

my $def = {
	'user_id' => 'SQL_INTEGER',
	'user_name' => 'SQL_VARCHAR',
};

my $foreign = {
	'user_id' => 'user.id',
	'user_name' => 'user.name'
};

my $o = __PACKAGE__->new(
	'definition' => $def,
	'references' => $foreign,
);
$test->ok($o, "Got object");
$test->is($o->definition, $def, "proper definition");
$test->is($o->references, $foreign, "proper foreign references");

my $f2 = {%$foreign};

$test->is($o->references($f2), $f2, "properly reset foreign reference");

$test->is($o->reference_query('user_id'), "select count(1) as count from user\n  where id = ?", "successful reference query");
$test->is($o->reference_query('user_name'), "select count(1) as count from user\n  where name = ?", "successful reference query");

$test->is(scalar($o->reference_query('foo')), undef, "got nothing for non-referenced column");
$test->is($o->errcode, "BDT-14", "Proper error code");

=end btest

=cut

sub reference_query {
	my $self = shift;
	my $column = shift;

	if (my $def = $self->referenced_column($column)) {
		my ($table, $col) = split(/\./, $def);
		my $tempTable = $self->pkg->new('name' => $table);
		return $tempTable->attach_to_query(
			$tempTable->count_query,
			{
				'where' => "$col = ?"
			}
		);
	} else {
		return $self->error("Cannot build query...$column is not a referenced column", "BDT-14");
	}
};

=pod

=item is_column

When passed a column name, returns a 1 if it is a column in this table, a 0 if it is not.

 print $table->is_column('foo');

=cut

=pod

=begin btest is_column

my $o = __PACKAGE__->new();
$test->ok($o, "Created object");
my $def = {
	'able' => 'SQL_INTEGER',
	'baker' => 'SQL_INTEGER',
	'charlie' => 'SQL_INTEGER',
	'delta' => 'SQL_INTEGER'
};

$test->is($o->definition($def), $def, "Set definition");
$test->is($o->definition, $def, "Got definition");

$test->ok($o->is_column('able'), 'able is column');
$test->ok($o->is_column('baker'), 'baker is column');
$test->ok($o->is_column('charlie'), 'charlie is column');
$test->ok($o->is_column('delta'), 'delta is column');
$test->is($o->is_column('edgar'), 0, 'edgar is not column');
$test->is($o->is_column('foxtrot'), 0, 'foxtrot is not column');

$test->is(scalar($o->is_column), undef, "Cannot call w/o column");
$test->is($o->errcode, "BDT-04", "proper error code");

=end btest

=cut

sub is_column {
	my $self	= shift;
	my $col		= shift or return $self->error("Cannot column-ness without column", "BDT-04");

	foreach my $column ($self->cols){
		return 1 if $column eq $col;
	}
	return 0;
};

=pod

=item is_primary

When passed a column name, returns a 1 if it is a primary column in this table, a 0 if it is not

 print $table->is_primary('foo');

=cut

=pod

=begin btest is_primary

my $o = __PACKAGE__->new();
$test->ok($o, "Created object");
my $def = {
	'able' => 'SQL_INTEGER',
	'baker' => 'SQL_INTEGER',
	'charlie' => 'SQL_INTEGER',
	'delta' => 'SQL_INTEGER'
};

$test->is($o->definition($def), $def, "Set definition");
$test->is($o->primary_column('able'), 'able', 'Set primary column');;

$test->is(scalar($o->is_primary), undef, 'Cannot determine primary-ness w/o a column');
$test->is($o->errcode, 'BDT-01', 'proper error code');

$test->ok($o->is_primary('able'), 'able is primary');
$test->is($o->is_primary('baker'), 0, 'baker is not primary');
$test->is($o->is_primary('charlie'), 0, 'charlie is not primary');
$test->is($o->is_primary('delta'), 0, 'delta is not primary');
$test->is($o->is_primary('edgar'), 0, 'edgar is not primary');
$test->is($o->is_primary('foxtrot'), 0, 'foxtrot is not primary');

my $primaries = [qw(baker delta)];

$test->is($o->primary_column($primaries), $primaries, 'set primary column');

$test->is($o->is_primary('able'), 0, 'able is not primary');
$test->is($o->is_primary('baker'), 1, 'baker is primary');
$test->is($o->is_primary('charlie'), 0, 'charlie is not primary');
$test->is($o->is_primary('delta'), 1, 'delta is primary');
$test->is($o->is_primary('edgar'), 0, 'edgar is not primary');
$test->is($o->is_primary('foxtrot'), 0, 'foxtrot is not primary');

$test->is(scalar($o->is_primary), undef, "Cannot call w/o column");
$test->is($o->errcode, "BDT-01", "proper error code");

=end btest

=cut

sub is_primary {
	my $self	= shift;
	my $col		= shift or return $self->error("Cannot determine primary-ness without column", "BDT-01");

	my %primaries = map {$_, 1} grep {defined} $self->primary_cols();

	return 1 if $primaries{$col};
	return 0;
};

=pod

=item non_primary_cols

Returns a list of all of the non primary columns in the table.

 my @nons = $table->non_primary_cols();

=cut

=pod

=begin btest non_primary_cols

my $o = __PACKAGE__->new();
$test->ok($o, "Created object");
my $def = {
	'able' => 'SQL_INTEGER',
	'baker' => 'SQL_INTEGER',
	'charlie' => 'SQL_INTEGER',
	'delta' => 'SQL_INTEGER'
};

$test->is($o->definition($def), $def, "Set definition");

{
	my %primary = map {$_, 1} $o->non_primary_cols;

	$test->is($primary{'able'}, 1, 'able is not primary column');
	$test->is($primary{'baker'}, 1, 'baker is not primary column');
	$test->is($primary{'charlie'}, 1, 'charlie is not primary column');
	$test->is($primary{'delta'}, 1, 'delta is not primary column');
}

{
	$o->primary_column('able');

	my %primary = map {$_, 1} $o->non_primary_cols;

	$test->is($primary{'able'}, undef, 'able is primary column');
	$test->is($primary{'baker'}, 1, 'baker is not primary column');
	$test->is($primary{'charlie'}, 1, 'charlie is not primary column');
	$test->is($primary{'delta'}, 1, 'delta is not primary column');
}

{
	$o->primary_column(['charlie', 'delta']);

	my %primary = map {$_, 1} $o->non_primary_cols;

	$test->is($primary{'able'}, 1, 'able is not primary column');
	$test->is($primary{'baker'}, 1, 'baker is not primary column');
	$test->is($primary{'charlie'}, undef, 'charlie is primary column');
	$test->is($primary{'delta'}, undef, 'delta is primary column');
}

=end btest

=cut

sub non_primary_cols {
	my $self	= shift;

	return grep {! $self->is_primary($_)} $self->cols;
};

=pod

=item primary_cols

Returns a list of all the primary columns in the table.

 my @primaries = $table->primary_cols();

=cut

=pod

=begin btest primary_cols

my $o = __PACKAGE__->new();
$test->ok($o, "Created object");
my $def = {
	'able' => 'SQL_INTEGER',
	'baker' => 'SQL_INTEGER',
	'charlie' => 'SQL_INTEGER',
	'delta' => 'SQL_INTEGER'
};

$test->is($o->definition($def), $def, "Set definition");

{
	my %primary = map {$_, 1} grep {defined} $o->primary_cols;

	$test->is($primary{'able'}, undef, 'able is not primary column');
	$test->is($primary{'baker'}, undef, 'baker is not primary column');
	$test->is($primary{'charlie'}, undef, 'charlie is not primary column');
	$test->is($primary{'delta'}, undef, 'delta is not primary column');
}

{
	$o->primary_column('able');

	my %primary = map {$_, 1} $o->primary_cols;

	$test->is($primary{'able'}, 1, 'able is primary column');
	$test->is($primary{'baker'}, undef, 'baker is not primary column');
	$test->is($primary{'charlie'}, undef, 'charlie is not primary column');
	$test->is($primary{'delta'}, undef, 'delta is not primary column');
}

{
	$o->primary_column(['charlie', 'delta']);

	my %primary = map {$_, 1} $o->primary_cols;

	$test->is($primary{'able'}, undef, 'able is not primary column');
	$test->is($primary{'baker'}, undef, 'baker is not primary column');
	$test->is($primary{'charlie'}, 1, 'charlie is primary column');
	$test->is($primary{'delta'}, 1, 'delta is primary column');
}

=end btest

=cut

sub primary_cols {
	my $self	= shift;

	my $primary = $self->primary_column;

	if (! ref $primary) {
		return ($primary);
	} elsif (ref $primary) {
		return @{$primary};
	} else {
		return ();
	};
};

=pod

=item foreign_cols

Given a table and an optional list of columns, returns all of the columns in the present table that reference
the columns in the second table. If no columns are passed, then the second table's primary columns are assumed.

 $table->references(
 	{
 		'user_id' => 'user.id',
 		'user_name' => 'user.name'
 	}
 );

 $table->foreign_cols($user_table);	#prints user_id
 $table->foreign_cols($user_table, 'id', 'name'); #prints user_id, user_name
 $table->foreign_cols($user_table, 'last_name', 'login'); #prints nothing - we have no references to those columns

=cut

=pod

=begin btest foreign_cols

my $o = __PACKAGE__->new();
$test->ok($o, "Created object");
my $def = {
	'able' => 'SQL_INTEGER',
	'baker' => 'SQL_INTEGER',
	'charlie' => 'SQL_INTEGER',
	'delta' => 'SQL_INTEGER'
};

$test->is($o->definition($def), $def, "Set definition");
$test->is($o->name('tableone'), 'tableone', 'set object 1 name');
$test->ok($o->primary_column(['able', 'baker']), 'set object 1 primary columns');

my $o2 = __PACKAGE__->new();
$test->ok($o2, "Created object 2");
my $def2 = {
	'edgar' => 'SQL_VARCHAR',
	'foxtrot' => 'SQL_DATE',
	'goats' => 'SQL_TIMESTAMP',
	'henry' => 'SQL_FLOAT'
};

$test->is($o2->definition($def2), $def2, "set object 2 definition");
$test->is($o2->name('tabletoo'), 'tabletoo', "set object 2 name");
$test->ok($o2->primary_column(['edgar', 'foxtrot']), 'set object 2 primary columns');

my $ref1 = {
	'able' => 'tabletoo.edgar',
	'baker' => 'tabletoo.foxtrot',
	'charlie' => 'tablethree.col'
};

$test->is($o->references($ref1), $ref1, "Set reference 1");

my $o3 = __PACKAGE__->new();
$test->ok($o3, "Created object 3");
my $def3 = {
	'col' => 'SQL_VARCHAR',
};

$test->is($o3->definition($def3), $def3, "set object 3 definition");
$test->is($o3->name('tablethree'), 'tablethree', "set object 3 name");

my $o4 = __PACKAGE__->new();
$test->ok($o4, "Created object 4");
$test->is($o4->name('tablefour'), 'tablefour', "set object 4 name");


$test->is(scalar($o->foreign_cols), undef, "Cannot get foreign cols w/o table");
$test->is($o->errcode, 'BDT-45', "Proper error code");

{
	my $i = 1;
	my %f1 = map {$_, $i++} $o->foreign_cols($o2);
	$test->is($f1{'able'}, 1, 'able is foreign column');
	$test->is($f1{'baker'}, 2, 'baker is foreign column');
	$test->is(scalar (keys %f1), 2, 'only 2 foreign columns');
}

{
	my %f2 = map {$_, 1} $o->foreign_cols($o3);
	$test->is(scalar (keys %f2), 0, 'object 1 references no primary columns in object 3');
}

{
	my %f2 = map {$_, 1} $o->foreign_cols($o3, 'col');
	$test->is($f2{'charlie'}, 1, 'charlie is foreign column');
	$test->is(scalar (keys %f2), 1, 'only 1 foreign column');
}

=end btest

=cut

sub foreign_cols {
	my $self = shift;

	my $foreign_table = shift or return $self->error("Cannot get foreign cols w/o table", "BDT-45");

	my @foreign_table_cols = map {$foreign_table->qualified_name($_)} grep {defined} (@_ ? @_ : $foreign_table->primary_cols);

	my $idx = 0;
	my %foreign_table_cols = map {$_, ++$idx} @foreign_table_cols;

	return
		sort {$foreign_table_cols{$self->references->{$a}} <=> $foreign_table_cols{$self->references->{$b}}}
		grep {$foreign_table_cols{$self->references->{$_}}}
	keys %{$self->references};
}

=pod

=item referenced_column

Given a column, returns the column it references in a foreign table or sets an error if references nothing.

 $table->references(
 	{
 		'user_id' => 'user.id',
 		'user_name' => 'user.name'
 	}
 );

 print $table->referenced_column('user_id');	#prints user.id
 print $table->referenced_column('password');	#prints nothing

=cut

=pod

=begin btest referenced_column

my $def = {
	'user_id' => 'SQL_INTEGER',
	'user_name' => 'SQL_VARCHAR',
	'user_extra' => 'SQL_VARCHAR',
};

my $foreign = {
	'user_id' => 'user.id',
	'user_name' => 'user.name'
};

my $o = __PACKAGE__->new(
	'definition' => $def,
	'references' => $foreign,
);
$test->ok($o, "Got object");
$test->is($o->definition, $def, "proper definition");
$test->is($o->references, $foreign, "proper foreign references");

my $f2 = {%$foreign};

$test->is($o->references($f2), $f2, "properly reset foreign reference");

$test->is(scalar($o->referenced_column), undef, "Cannot get referenced column w/o column");
$test->is($o->errcode, "BDT-15", "proper error code");

$test->is($o->referenced_column('user_id'), 'user.id', 'user_id properly referenced');
$test->is($o->referenced_column('user_name'), 'user.name', 'user_id properly referenced');
$test->is(scalar($o->referenced_column('user_extra')), undef, 'user_id properly referenced');
$test->is($o->errcode, "BDT-16", "proper error code");

=end btest

=cut

sub referenced_column {
	my $self = shift;
	my $column = shift or return $self->error("Cannot determine reference w/o column", "BDT-15");

	return $self->references->{$column} 
		|| $self->error("Column does not reference any other table", "BDT-16");

}

=pod

=item discover_columns

Takes a table name as an argument. Returns a hashref of the columns in that table, suitable to be
used in a definition call.

 my $definition = Basset::DB::Table->discover_columns('user_table');

This should be typically be invoked via the discover flag to the constructor.

 my $table = Basset::DB::Table->new(
 	'discover' => 1
 );

=cut

=pod

=begin btest discover_columns

=end btest

=cut

sub discover_columns {
	my $self = shift;
	my $table = shift or return $self->error("Cannot discover columns w/o table", "BDT-51");

	my $columns = join(', ', @_ ? @_ : ('*'));

	my $stmt = $self->arbitrary_sql(
		'query' => "select $columns from $table where 1 = 0",
		'iterator' => 1,
	) or do {
		if ($columns) {
			return { map {$_, undef} @_};
		} else {
			return;
		}
	};

	my $definition = {};
	for (my $idx = 0; $idx < $stmt->{'NUM_OF_FIELDS'}; $idx++) {
		$definition->{$stmt->{'NAME_lc'}->[$idx]} = $stmt->{'TYPE'}->[$idx];
	}

	$stmt->finish or return $self->error($stmt->errstr, 'BDT-37');

	return $definition;

}

=pod

=item attach_to_query

Given a query string and a hashref of clauses, attaches the clauses to the query.

 my $update_query = $table->attach_to_query(
 	$table->update_query,
 	{
 		'where' => 'id = ?'
 	}
 );

 Valid clauses are "where", "group by", "having", "order by" and "limit", reflecting the
 SQL clauses of the same kind.

=cut

=pod

=begin btest attach_to_query

my $o = __PACKAGE__->new();
$test->ok($o, "Created object");
my $def = {
	'able' => 'SQL_INTEGER',
};

$test->is($o->definition($def), $def, "Set definition");
$test->is($o->definition, $def, "Got definition");

$test->is($o->name('mytable'), 'mytable', 'set table name');

$test->is(scalar($o->attach_to_query), undef, "Cannot attach to query w/o query");
$test->is($o->errcode, "BDT-02", "proper error code");

$test->is($o->select_query, 'select mytable.able as able from mytable', 'proper select query');
my $query = $o->select_query;
$test->is(scalar($o->attach_to_query($query)), $query, "No clauses returns original query");
$test->is(scalar($o->attach_to_query($query, {})), $query, "Empty clauses returns original query");
$test->is(scalar($o->attach_to_query($query, {'having' => 'having clause'})), undef, "Cannot have having w/o group");
$test->is($o->errcode, "BDT-09", "proper error code");

$test->is($o->attach_to_query($query, {'where' => 'able = ?'}), $query . "\n  where able = ?", "attached where clause");
$test->is($o->attach_to_query($query, {'group by' => 'able'}), $query . "\n  group by able", "attached group by clause");
$test->is($o->attach_to_query($query, {'order by' => 'baker'}), $query . "\n  order by baker", "attached order by clause");
$test->is($o->attach_to_query($query, {'limit' => '5'}), $query . "\n  limit 5", "attached limit clause");

$test->is(
	$o->attach_to_query(
		$query, 
		{
			'where' => 'able = ?',
			'group by' => 'baker',
		}
	), $query . "\n  where able = ?\n  group by baker", "attached where and group by clause");

$test->is(
	$o->attach_to_query(
		$query, 
		{
			'where' => 'able = ?',
			'group by' => 'baker',
			'having' => 'count(*) > 1'
		}
	), $query . "\n  where able = ?\n  group by baker\n  having count(*) > 1", "attached where, group by, and having clause");

=end btest

=cut

sub attach_to_query {
	my $class = shift;

	my $query	= shift or return $class->error("Cannot attach to query w/o query", "BDT-02");
	my $clauses	= shift || {};

	unless (keys %$clauses) {
		$class->notify("warnings", "No clauses to attach to query");
		return $query;
	};

	return $class->error("Cannot have having without group", "BDT-09")
		if defined $clauses->{'having'} && ! defined $clauses->{'group by'};

	foreach my $clause ('where', 'group by', 'having', 'order by', 'limit'){
		if (defined $clauses->{$clause}){
			my $value = $clauses->{$clause};

			$query .= "\n  " . $clause . " " . $value;
		};
	};

	return $query;
};

=pod

=item join_tables

Magic time.

join_tables is used internally by the multiselect_query, but you can use it yourself if you want.

Takes an array of table objects or arrayrefs. arrayrefs must be of the following form:

=over

=item join type

The type of join to be performed. Should be a string. "inner", "outer", "left outer", that sort of thing.
Defaults to inner. This parameter is optional.

=item table object

The table object you're using.

=item columns

SQL clauses to override the auto-join.  This parameter is optional.

=back

So, for example, if you have a usertable and a movietable, and movie.user references user.id, you could do:

 Basset::DB::Table->join_tables(
 	$usertable,
 	$movietable,
 ) || die Basset::DB::Table->errstring;

which returns:

 user
	inner join
		movie
			on user.id = movie.user

Say that user.movie was a foreign key to movie.id. Then you'd get back:

 user
	inner join
		movie
			on user.id = movie.user
			and user.movie = movie.id

I can't say why you'd want to have two tables referencing each other, but it's important to know that it happens.

3 tables is the same thing. Say that movie.genre references genre.id

 Basset::DB::Table->join_tables(
 	$usertable,
 	$movietable,
 	$genretable,
 ) || die Basset::DB::Table->errstring;

 user
	inner join
		movie
			on movie.user = user.id
	inner join
		genre
			on movie.user = genre.id

Okay, say that you want to use a left join between the user table and the movie table.

 Basset::DB::Table->join_tables(
 	$usertable,
 	['left', $movietable],
 	$genretable,
 ) || die Basset::DB::Table->errstring;

 user
	left join
		movie
			on movie.user = user.id
	inner join
		genre
			on movie.user = genre.id

You can also join with earlier tables. Say that snack.user references user.id

 Basset::DB::Table->join_tables(
 	$usertable,
 	['left', $movietable],
 	$genretable,
 	$snacktable,
 ) || die Basset::DB::Table->errstring;

 user
	left join
		movie
			on movie.user = user.id
	inner join
		genre
			on movie.user = genre.id
	inner join
		snack
			on user.id = snack.user

Or, you can override the defaults specified in the table's references. For example, if the references don't
exist for the table.

 Basset::DB::Table->join_tables(
 	$usertable,
 	['left', $movietable],
 	$genretable,
 	[$snacktable, 'user.id = snack.user AND user.status = snack.status'],
 ) || die Basset::DB::Table->errstring;

 user
	left join
		movie
			on movie.user = user.id
	inner join
		genre
			on movie.user = genre.id
	inner join
		snack
			on user.id = snack.user
			and user.status = snack.status

=cut

=pod

=begin btest join_tables

my $o = __PACKAGE__->new();
$test->ok($o, "Created object");

my $o2 = __PACKAGE__->new();
$test->ok($o2, "Created object");

my $o3 = __PACKAGE__->new();
$test->ok($o3, "Created object");

my $o4 = __PACKAGE__->new();
$test->ok($o4, "Created object");

my $def1 = {
	'able' => 'SQL_INTEGER',
	'baker' => 'SQL_INTEGER',
	'charlie' => 'SQL_INTEGER',
	'delta' => 'SQL_INTEGER'
};

my $ref1 = {
	'able' => 'table2.aid',
	'baker' => 'table4.b',
	'charlie' => 'table4.c',
};

$test->is($o->definition($def1), $def1, "Set definition");
$test->is($o->references($ref1), $ref1, "Set references");
$test->is($o->name("table1"), "table1", "set table name");
$test->is($o->primary_column("able"), "able", "set table primary id");

my $def2 = {
	'aid' => 'SQL_INTEGER',
	'bid' => 'SQL_INTEGER',
	'cid' => 'SQL_INTEGER',
	'did' => 'SQL_INTEGER'
};

my $ref2 = {
	'aid' => 'table3.ace'
};

$test->is($o2->definition($def2), $def2, "Set definition");
$test->is($o2->references($ref2), $ref2, "Set references");
$test->is($o2->name("table2"), "table2", "set table name");
$test->is($o2->primary_column("aid"), "aid", "set table primary id");

my $def3 = {
	'ace' => 'SQL_INTEGER',
	'bogey' => 'SQL_INTEGER',
};

$test->is($o3->definition($def3), $def3, "Set definition");
$test->is($o3->name("table3"), "table3", "set table name");
$test->is($o3->primary_column("ace"), "ace", "set table primary id");

my $def4 = {
	'a' => 'SQL_INTEGER',
	'b' => 'SQL_INTEGER',
	'c' => 'SQL_INTEGER',
	'd' => 'SQL_INTEGER'
};

$test->is($o4->definition($def4), $def4, "Set definition");
$test->is($o4->name("table4"), "table4", "set table name");
$test->ok($o4->primary_column([qw(b c)]), "set table primary id");


$test->is(scalar(__PACKAGE__->join_tables), undef, "Could not join w/o tables");
$test->is(__PACKAGE__->errcode, "BDT-28", "proper error code BDT-28");

$test->is(__PACKAGE__->join_tables($o), "table1", "Join with one table is same table");

$test->is(__PACKAGE__->join_tables($o, $o2),
"table1
	inner join
		table2
			on table1.able = table2.aid",
"Default joined two tables"
);

$test->is(__PACKAGE__->join_tables($o2, $o),
"table2
	inner join
		table1
			on table1.able = table2.aid",
"Reverse  joined two tables"
);

$test->is(__PACKAGE__->join_tables($o, $o2, $o3),
"table1
	inner join
		table2
			on table1.able = table2.aid
	inner join
		table3
			on table2.aid = table3.ace",
"Default joined three tables"
);
$test->is(__PACKAGE__->join_tables($o, $o2, $o3, $o4),
"table1
	inner join
		table2
			on table1.able = table2.aid
	inner join
		table3
			on table2.aid = table3.ace
	inner join
		table4
			on table1.baker = table4.b
			and table1.charlie = table4.c",
"Default joined four tables"
);

$test->is(__PACKAGE__->join_tables($o, [$o2]),
"table1
	inner join
		table2
			on table1.able = table2.aid",
"Default joined two tables w/arrayref table only"
);

$test->is(__PACKAGE__->join_tables($o, ['inner',$o2]),
"table1
	inner join
		table2
			on table1.able = table2.aid",
"inner joined 2 tables");

$test->is(__PACKAGE__->join_tables($o, ['outer',$o2]),
"table1
	outer join
		table2
			on table1.able = table2.aid",
"outer joined 2 tables");

$test->is(__PACKAGE__->join_tables($o, ['natural',$o2]),
"table1
	natural join
		table2",
"natural joined 2 tables");

$test->is(__PACKAGE__->join_tables($o, ['left',$o2]),
"table1
	left join
		table2
			on table1.able = table2.aid",
"left joined 2 tables");

$test->is(__PACKAGE__->join_tables($o, ['right',$o2]),
"table1
	right join
		table2
			on table1.able = table2.aid",
"right joined 2 tables");

$test->is(__PACKAGE__->join_tables($o, ['left outer',$o2]),
"table1
	left outer join
		table2
			on table1.able = table2.aid",
"left outer joined 2 tables");

$test->is(__PACKAGE__->join_tables($o, ['right outer',$o2]),
"table1
	right outer join
		table2
			on table1.able = table2.aid",
"right outer joined 2 tables");

$test->is(__PACKAGE__->join_tables($o, [$o4, 'table1.baker = table4.b']),
"table1
	inner join
		table4
			on table1.baker = table4.b",
"joined 2 tables with alternate clause");

$test->is(__PACKAGE__->join_tables($o, ['outer', $o4, 'table1.baker = table4.b']),
"table1
	outer join
		table4
			on table1.baker = table4.b",
"outer joined 2 tables with 3 arg alternate clause");

$test->is(__PACKAGE__->join_tables($o, [$o4, ['table1.baker = table4.b', 'table1.baker = table4.c']]),
"table1
	inner join
		table4
			on table1.baker = table4.b
			and table1.baker = table4.c",
"joined 2 tables with 2 alternate clauses");

$test->is(__PACKAGE__->join_tables($o, ['outer', $o4, ['table1.baker = table4.b', 'table1.baker = table4.c']]),
"table1
	outer join
		table4
			on table1.baker = table4.b
			and table1.baker = table4.c",
"outer joined 2 tables with 2 alternate clauses");

$test->is(__PACKAGE__->join_tables(['inner', $o], ['outer', $o4, ['table1.baker = table4.b', 'table1.baker = table4.c']]),
"table1
	outer join
		table4
			on table1.baker = table4.b
			and table1.baker = table4.c",
"outer joined 2 tables with 2 alternate clauses w/ first table array");

$test->is(__PACKAGE__->join_tables(['inner', $o, 'foo = bar'], ['outer', $o4, ['table1.baker = table4.b', 'table1.baker = table4.c']]),
"table1
	outer join
		table4
			on table1.baker = table4.b
			and table1.baker = table4.c",
"outer joined 2 tables with 2 alternate clauses w/ first table array and columns");

$test->is(__PACKAGE__->join_tables([$o, 'foo = bar'], ['outer', $o4, ['table1.baker = table4.b', 'table1.baker = table4.c']]),
"table1
	outer join
		table4
			on table1.baker = table4.b
			and table1.baker = table4.c",
"outer joined 2 tables with 2 alternate clauses w/ first table array and columns, non-inner");

$test->is(scalar(__PACKAGE__->join_tables($o, $o3)), undef, "Cannot auto-join unreferenced tables");
$test->is(__PACKAGE__->errcode, 'BDT-27', 'proper error code');

=end btest

=cut


sub join_tables {
	my $self = shift;

	my @tables = @_;

	my @last_tables = ();

	{
		my $first_table = shift @tables or return $self->error("Cannot join tables w/o table", "BDT-28");

		if (ref $first_table eq 'ARRAY') {
			$first_table = ref $first_table->[0] ? $first_table->[0] : $first_table->[1];
		};

		unshift @last_tables, $first_table;

	}

	my $joined_tables = $last_tables[0]->name;

	while (@tables) {
		my $table = shift @tables;
		my ($join, $cols) = ('inner', []);

		if (ref $table eq 'ARRAY') {
			if (@$table == 3) {
				($join, $table, $cols) = @$table;
				$cols = [$cols] unless ref $cols;
			} elsif (@$table == 2) {
				if (! ref $table->[0]) {
					($join, $table) = @$table;
				} else {
					($table, $cols) = @$table;
					$cols = [$cols] unless ref $cols;
				}
			}
			else {
				($table) = @$table;
			};

		}

		if (@$cols == 0 && $join ne 'natural') {
			my $found = 0;
			my $idx = 0;
			while (! $found && $idx < @last_tables) {

				my $last_table = $last_tables[$idx++];

				my @foreign_cols = $table->foreign_cols($last_table);
				if (@foreign_cols) {
					$found++;
					foreach my $col (@foreign_cols) {
						push @$cols, $table->qualified_name($col) . ' = ' . $table->referenced_column($col);
					}
				}
				my @last_foreign_cols = $last_table->foreign_cols($table);

				if (@last_foreign_cols) {
					$found++;
					foreach my $col (@last_foreign_cols) {
						push @$cols, $last_table->qualified_name($col) . ' = ' . $last_table->referenced_column($col);
					}
				}
			}
			unless ($found) {
				return $self->error("Cannot auto-join table " . $table->name . " : not referenced by prior table", "BDT-27");
			}
		}

		$joined_tables .= "\n\t$join join\n\t\t" . $table->name;
		$joined_tables .= "\n\t\t\ton " . join("\n\t\t\tand ", @$cols) if @$cols;
		unshift @last_tables, $table;
	}

	return $joined_tables;
}

=pod

=item many_clause

Convenience method. Given a column and a list of values, returns a foo in (???) clause for use in queries.

 print $table->many_clause('id', qw(1 2 3 4));	#prints "id in (?, ?, ?, ?)"

You may optionally pass your values in an arrayref, if it's more convenient.

 print $table->many_clause('id', [qw(1 2 3 4)]);	#prints "id in (?, ?, ?, ?)"

Finally, if you pass your values in an arrayref, you may specify the 'not' parameter to build a 'not in' clause

 print $table->many_clause('id', 'not', qw(1 2 3 4));	#prints "id not in (?, ?, ?, ?)"

=cut

sub many_clause {
	my $self = shift;
	my $column = shift or return $self->error("Cannot build many clause w/o column", "BDT-21");
	my $negative = 0;

	my @columns = @_ or return $self->error("Cannot build many clause w/o values", "BDT-22");

	if (ref $columns[-1] eq 'ARRAY') {
		$negative = shift @columns if @columns == 2;
		@columns = @{$columns[-1]} or return $self->error("Cannot build many clause w/o values", "BDT-22");
	}

	$column = $self->column_for_alias($column);

	$negative = $negative ? ' not' : '';

	return $self->qualified_name($column) . "$negative in (" . join(', ', ("?") x @columns) . ')';
};

=pod

=begin btest many_clause

my $o = __PACKAGE__->new();
$test->ok($o, "Created object");
my $def = {
	'able' => 'SQL_INTEGER',
	'baker' => 'SQL_INTEGER',
	'charlie' => 'SQL_INTEGER',
	'delta' => 'SQL_INTEGER'
};

$test->is($o->definition($def), $def, "Set definition");
$test->is($o->definition, $def, "Got definition");
$test->is($o->name('test'), 'test', 'set name');

$test->is(scalar($o->many_clause), undef, "Cannot build many clause w/o column");
$test->is($o->errcode, 'BDT-21', 'proper error code');

$test->is(scalar($o->many_clause('able')), undef, "Cannot build many clause w/o values");
$test->is($o->errcode, 'BDT-22', 'proper error code');

$test->is(scalar($o->many_clause('able', [])), undef, "Cannot build many clause w/o values");
$test->is($o->errcode, 'BDT-22', 'proper error code');

$test->is($o->many_clause('able', 1), 'test.able in (?)', 'built single many clause');
$test->is($o->many_clause('able', 1, 2), 'test.able in (?, ?)', 'built double many clause');
$test->is($o->many_clause('able', 1, 2, 3), 'test.able in (?, ?, ?)', 'built triple many clause');

$test->is($o->many_clause('able', [1]), 'test.able in (?)', 'built single many clause from arrayref');
$test->is($o->many_clause('able', [1, 2]), 'test.able in (?, ?)', 'built double many clause from arrayref');
$test->is($o->many_clause('able', [1, 2, 3]), 'test.able in (?, ?, ?)', 'built triple many clause from arrayref');

$test->is($o->many_clause('able', 'not', [1]), 'test.able not in (?)', 'built single not many clause');
$test->is($o->many_clause('able', 'not', [1, 2]), 'test.able not in (?, ?)', 'built double not many clause');
$test->is($o->many_clause('able', 'not', [1, 2, 3]), 'test.able not in (?, ?, ?)', 'built triple not many clause');
$test->is($o->many_clause('able', 1, [1, 2, 3]), 'test.able not in (?, ?, ?)', 'built triple not many clause w/arbitrary true value');

=end btest

=cut




=pod

=item qualified_name

Given a column name, returns the column name with the table name prepended.

 print $user->qualified_name('id');	#prints user.id

=cut

=pod

=begin btest qualified_name

my $o = __PACKAGE__->new();
$test->ok($o, "Got object");

$test->is(scalar($o->qualified_name()), undef, 'could not get qualified name w/o column');

$test->is($o->errcode, 'BDT-23', 'Proper error code');
$test->is(scalar($o->qualified_name('foo')), undef, 'could not get qualified name w/o table name');
$test->is($o->errcode, 'BDT-24', 'Proper error code');
$test->is($o->name('test1'), 'test1', 'Set table name');
$test->is($o->qualified_name('foo'), 'test1.foo', 'column foo properly qualified');
$test->is($o->qualified_name('bar'), 'test1.bar', 'column bar properly qualified');
$test->is($o->name('test2'), 'test2', 'changed column name to test2');
$test->is($o->qualified_name('foo'), 'test2.foo', 'column foo properly qualified');
$test->is($o->qualified_name('bar'), 'test2.bar', 'column bar properly qualified');
$test->is($o->qualified_name('test2.foo'), 'test2.foo', 'previously column test2.foo properly qualified');
$test->is($o->qualified_name('test2.bar'), 'test2.bar', 'previously column test2.bar properly qualified');

=end btest

=cut

sub qualified_name {
	my $self	= shift;
	my @cols	= @_ or return $self->error("Cannot qualify name w/o column", "BDT-23");
	my $name = $self->name or return $self->error("Cannot qualify name w/o table name", "BDT-24");

	foreach my $column (@cols) {
		next if index($column, '.') != -1;
		$column = "$name.$column";
	}
	
	return wantarray ? @cols : $cols[0];
};

=pod

=item nonqualified_name

Given a column name, returns the column name without the table name prepended.

 print $user->qualified_name('id');	#prints id
 print $user->qualified_name('user.id');	#prints id

=cut

=pod

=begin btest nonqualified_name

$test->is(scalar(__PACKAGE__->nonqualified_name()), undef, "Could not get nonqualified name w/o column");
$test->is(__PACKAGE__->nonqualified_name('foo.bar'), 'bar', 'stripped table name');
$test->is(__PACKAGE__->nonqualified_name('bar'), 'bar', 'returned column w/o table name');

=end btest

=cut

sub nonqualified_name {
	my $class = shift;
	my $column = shift or return $class->error("Cannot unqualify name w/o column", "BDT-46");

	return substr($column, index($column, '.') + 1);
}

=pod

=item construct_where_clause

The where clause constructor is a class method that takes an arrayref of tables as its first argument, and then
an arbitrary set of clauses in a list.

 my ($clause, @bindvalues) = Basset::DB::Table->construct_where_clause($tables, @clauses);

This is used to hide SQL from your application layer. You can specify arbitrarily complex statements here to build
where clauses. The tables array is used to qualify the names of the columns passed. The array will be walked and the
first table encounted that has the given column will be used to qualify the name. Hence, if a column exists in multiple
tables, you should qualify it to ensure that you get it from the place you expect.

Easily pass in key value pairs.

 my ($stmt, @values) = Basset::DB::Table->construct_where_clause(
 	$tables,
 	'id' => 7
 );  #returns ('tablename.id = ?', 7)

To specify an 'in' clause, pass in an array.

 my ($stmt, @values) = Basset::DB::Table->construct_where_clause(
 	$tables,
 	'id' => [7, 8, 9]
 );  #returns ('tablename.id in (?, ?, ?)', 7, 8, 9)

Additional values are joined by AND statements.

 my ($stmt, @values) = Basset::DB::Table->construct_where_clause(
 	$tables,
 	'id' => [7, 8, 9],
 	'status' => 1,
 );  #returns ('tablename.id in (?, ?, ?) AND tablename.status = ?', 7, 8, 9, 1)

You may specify alternative values for columns in a hashref.

 my ($stmt, @values) = Basset::DB::Table->construct_where_clause(
 	$tables,
 	'id' => {
 		'>' => 7,
 		'<' => 14,
 	'status' => 1,
 );  #returns ('(tablename.id > ? OR tablename.id < ?) AND tablename.status = ?', 7, 14, 1)

Groups of sets of values are joined with OR clauses.

 my ($stmt, @values) = Basset::DB::Table->construct_where_clause(
 	$tables,
 	['id' => 7,'status' => 1,],
 	['id' => {'>' => 18'}, 'status' => 3],
 	['status' => 5'],
 );  #returns ('(tablename.id = ? OR tablename.status = ?) OR (tablename.id > ? AND status = ?) OR (status = ?)', 7, 1, 18, 3, 5)

groups may be nested

 my ($stmt, @values) = Basset::DB::Table->construct_where_clause(
 	$tables,
 	'id' => 7,
 	['id' => {'>' => 20}, ['name' => 'test', status => 5]]
 );  #returns ('(tablename.id = ?) OR (tablename.id > ? OR (tablename.name = ? AND tablename.status = ?))', 7, 20, test, 5)

Column order may not be preserved.

 my ($stmt, @values) = Basset::DB::Table->construct_where_clause(
 	$tables,
 	'id' => 7,
 	['id' => 8],
 	'name' => 'foo',
 );  #returns ('(tablename.id = ? AND tablename.name = ?) OR (tablename.id = ?)', 7, 'foo', 8)

To group different columns with different and clauses, repeat the clause.

 my ($stmt, @values) = Basset::DB::Table->construct_where_clause(
 	$tables,
 	'id' => {'>' => 8},
 	'id' => {'<' => 25},
 );  #returns ('tablename.id > ? AND tablename.id < ?', 8, 25)

Finally, sometimes you just need to have a literal value in there that you can't bind to a place
holder. In that case, you want to pass in a reference.

 my ($stmt, @values) = Basset::DB::Table->construct_where_clause(
 	$tables,
 	'id' => {'>' => \8},
 	'id' => {'<' => \25},
 );  #returns ('tablename.id > 8 AND tablename.id < 25')
 
 This is most useful, obviously, for NULLs.
 
  my ($stmt, @values) = Basset::DB::Table->construct_where_clause(
 	$tables,
 	'id' => {'is' => \'NULL', '=' => 4},
 );  #returns ('tablename.id is NULL or tablename.id = ?', 4)

=cut

sub construct_where_clause {
	my $class = shift;

	my $tables	= shift or return $class->error("Cannot construct_where_clause w/o tables", "BDT-48");
	my @clauses = @_ or return $class->error("Cannot construct_where_clause w/o clauses", "BDT-49");

	my @where = ();
	my @values = ();
	my @extra = ();

	while (@clauses) {
		my $clause = shift @clauses;

		if (ref $clause eq 'ARRAY') {
			my @subvalues = $class->construct_where_clause($tables, @$clause)
				or return;
			push @extra, \@subvalues;
			next;
		}

		my @relational = ();
		my @myvalues = ();

		my $value	= shift @clauses;
		if (ref $value eq 'HASH') {
			push @relational, sort keys %$value;
			push @myvalues, map {$value->{$_}} sort keys %$value;
		} else {
			push @myvalues, $value;
		};

		my $table = undef;
		my ($name, $col);
		if ($clause =~ /^(\w+)\.(\w+)$/) {
			($name, $col) = ($1, $2);
		};

		foreach my $t (@$tables) {

			if (defined $t->definition->{$t->column_for_alias($clause)}) {
				if (! defined $name || $name eq $t->name) {
					$table = $t;
					last;
				}
			}
		};

		return $class->error("Cannot construct_where_clause with clause $clause : not in any object table", "BDT-50")
			unless defined $table;

		my @mywhere = ();

		while (@myvalues) {
			my $value = shift @myvalues;
			my $relation = shift @relational || '=';

			if (ref $value eq 'ARRAY') {
				push @mywhere, $table->many_clause($clause, @$value);
				if (ref $value->[-1] eq 'ARRAY') {
					push @values, @{$value->[-1]};
				} else {
					push @values, @$value
				}
			} else {
				if (ref $value) {
					push @mywhere, $table->qualified_name($clause) . " $relation $$value";
				} else {
					push @mywhere, $table->qualified_name($clause) . " $relation ?";
					push @values, $value;
				}
			}
		}

		my $mywhere = join(' OR ', @mywhere);
		$mywhere = "($mywhere)" if @mywhere > 1;
		push @where, $mywhere;

	}

	my $where = join(' AND ', @where);
	if (@extra) {
		while (@extra) {
			my $extra = shift @extra;
			my $clause = shift @$extra;
			$where .= " OR " if $where;
			$where .= "($clause)";
			push @values, @$extra;
		}
	}
	return ($where, @values);
}

=pod

=begin btest construct_where_clause

$test->ok(! __PACKAGE__->construct_where_clause, "Cannot construct_where_clause w/o tables");
$test->is(__PACKAGE__->errcode, "BDT-48", 'proper error code');

my $t1 = __PACKAGE__->new(
	'name'				=> 't1',
	'primary_column'	=> 'id',
	'definition'	=> {
		'id' => 'SQL_INTEGER',
		'name' => 'SQL_VARCHAR',
	}
);

$test->ok($t1, 'built first table');

my $t2 = __PACKAGE__->new(
	'name'				=> 't2',
	'primary_column'	=> 'id',
	'definition'	=> {
		'id' => 'SQL_INTEGER',
		'size' => 'SQL_VARCHAR',
	}
);

$test->ok($t2, 'built second table');

my $one_table = [$t1];
my $two_tables = [$t1, $t2];

$test->ok(! __PACKAGE__->construct_where_clause($one_table), "Cannot construct_where_clause w/o clauses");
$test->is(__PACKAGE__->errcode, "BDT-49", 'proper error code');

{
	my @return = __PACKAGE__->construct_where_clause($one_table, 'id' => 1);
	$test->ok(scalar(@return), "got values from constructing where clause");
	$test->is($return[0], 't1.id = ?', 'proper clause');
	$test->is($return[1], '1', 'proper value');
}

{
	my @return = __PACKAGE__->construct_where_clause($one_table, 'id' => 1, 'name' => 'foo');
	$test->ok(scalar(@return), "got values from constructing where clause");
	$test->is($return[0], 't1.id = ? AND t1.name = ?', 'proper clause');
	$test->is($return[1], '1', 'proper value');
	$test->is($return[2], 'foo', 'proper value');
}

{
	$test->ok(!__PACKAGE__->construct_where_clause($one_table, 'id' => 1, 'name' => 'foo', 'bar' => 'baz'),
		'could not construct_where_clause w/non-existent column');
	$test->is(__PACKAGE__->errcode, 'BDT-50', 'proper error code');
}

{
	$test->ok(!__PACKAGE__->construct_where_clause($one_table, 'id' => 1, 'name' => 'foo', ['bar' => 'baz']),
		'could not construct_where_clause w/non-existent column');
	$test->is(__PACKAGE__->errcode, 'BDT-50', 'proper error code');
}

{
	my @return = __PACKAGE__->construct_where_clause($one_table, 'id' => [qw(a b c)]);
	$test->ok(scalar(@return), "got values from constructing where clause");
	$test->is($return[0], 't1.id in (?, ?, ?)', 'proper clause');
	$test->is($return[1], 'a', 'proper value');
	$test->is($return[2], 'b', 'proper value');
	$test->is($return[3], 'c', 'proper value');
}

{
	my @return = __PACKAGE__->construct_where_clause($one_table, 'id' => {'>' => 1});
	$test->ok(scalar(@return), "got values from constructing where clause");
	$test->is($return[0], 't1.id > ?', 'proper clause');
	$test->is($return[1], '1', 'proper value');
}

{
	my @return = __PACKAGE__->construct_where_clause($one_table, 'id' => {'>' => 1, '<' => 10});
	$test->ok(scalar(@return), "got values from constructing where clause");
	$test->is($return[0], '(t1.id < ? OR t1.id > ?)', 'proper clause');
	$test->is($return[1], '10', 'proper value');
	$test->is($return[2], '1', 'proper value');
}

{
	my @return = __PACKAGE__->construct_where_clause($one_table, 'id' => {'>' => 1, '<' => 10}, 'name' => 'you');
	$test->ok(scalar(@return), "got values from constructing where clause");
	$test->is($return[0], '(t1.id < ? OR t1.id > ?) AND t1.name = ?', 'proper clause');
	$test->is($return[1], '10', 'proper value');
	$test->is($return[2], '1', 'proper value');
	$test->is($return[3], 'you', 'proper value');
}

{
	my @return = __PACKAGE__->construct_where_clause($one_table, 'id' => {'>' => 1}, 'id' => {'<' => 10});
	$test->ok(scalar(@return), "got values from constructing where clause");
	$test->is($return[0], 't1.id > ? AND t1.id < ?', 'proper clause');
	$test->is($return[1], '1', 'proper value');
	$test->is($return[2], '10', 'proper value');
}

{
	my @return = __PACKAGE__->construct_where_clause($one_table, 'id' => 1, ['name' => 'me', 'id' => 3]);
	$test->ok(scalar(@return), "got values from constructing where clause");
	$test->is($return[0], 't1.id = ? OR (t1.name = ? AND t1.id = ?)', 'proper clause');
	$test->is($return[1], '1', 'proper value');
	$test->is($return[2], 'me', 'proper value');
	$test->is($return[3], '3', 'proper value');
}

{
	my @return = __PACKAGE__->construct_where_clause($one_table, 'id' => 1, ['name' => 'me', 'id' => 3], 'id' => 5);
	$test->ok(scalar(@return), "got values from constructing where clause");
	$test->is($return[0], 't1.id = ? AND t1.id = ? OR (t1.name = ? AND t1.id = ?)', 'proper clause');
	$test->is($return[1], '1', 'proper value');
	$test->is($return[2], '5', 'proper value');
	$test->is($return[3], 'me', 'proper value');
	$test->is($return[4], '3', 'proper value');
}

{
	my @return = __PACKAGE__->construct_where_clause($two_tables, 'id' => 1, ['name' => 'me', 'id' => 3], 'id' => 5);
	$test->ok(scalar(@return), "got values from constructing where clause");
	$test->is($return[0], 't1.id = ? AND t1.id = ? OR (t1.name = ? AND t1.id = ?)', 'proper clause');
	$test->is($return[1], '1', 'proper value');
	$test->is($return[2], '5', 'proper value');
	$test->is($return[3], 'me', 'proper value');
	$test->is($return[4], '3', 'proper value');
}

{
	my @return = __PACKAGE__->construct_where_clause($two_tables, 'id' => 1, ['name' => 'me', 'id' => 3], 't2.id' => 5);
	$test->ok(scalar(@return), "got values from constructing where clause");
	$test->is($return[0], 't1.id = ? AND t2.id = ? OR (t1.name = ? AND t1.id = ?)', 'proper clause');
	$test->is($return[1], '1', 'proper value');
	$test->is($return[2], '5', 'proper value');
	$test->is($return[3], 'me', 'proper value');
	$test->is($return[4], '3', 'proper value');
}

{
	my @return = __PACKAGE__->construct_where_clause($two_tables, 'id' => 1, 'size' => 7);
	$test->ok(scalar(@return), "got values from constructing where clause");
	$test->is($return[0], 't1.id = ? AND t2.size = ?', 'proper clause');
	$test->is($return[1], '1', 'proper value');
	$test->is($return[2], '7', 'proper value');
}

{
	my @return = __PACKAGE__->construct_where_clause($two_tables, 'id' => \1, 'size' => \7);
	$test->is(scalar(@return), 1, "got values from constructing where clause");
	$test->is($return[0], 't1.id = 1 AND t2.size = 7', 'proper clause');
}

{
	my @return = __PACKAGE__->construct_where_clause($two_tables, 'id' => {'is' => \'NULL'}, 'size' => 7);
	$test->ok(scalar(@return), "got values from constructing where clause");
	$test->is($return[0], 't1.id is NULL AND t2.size = ?', 'proper clause');
	$test->is($return[1], '7', 'proper value');
}

=end btest

=cut



=pod

=item arbitrary_sql

Wrappers Basset::Object::Persistent's arbitrary_sql method.

=cut

=pod

=begin btest arbitrary_sql

my $poclass = __PACKAGE__->pkg->pkg_for_type('persistentobject');

$test->ok($poclass, "Got persistent object class");
$test->ok($poclass->can('arbitrary_sql'), 'persistent class can arbitrary_sql');

=end btest

=cut

sub arbitrary_sql {
	my $class = shift;

	my $persistent_class = $class->pkg_for_type('persistentobject');
	return $persistent_class->arbitrary_sql(@_) || $class->error($persistent_class->errvals);
}

1;

=pod

=back

=cut