view release on metacpan or  search on metacpan

lib/Basset/DB/Table.pm  view on Meta::CPAN

$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');

lib/Basset/DB/Table.pm  view on Meta::CPAN

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