view release on metacpan or  search on metacpan

lib/DBIO/Manual/FAQ.pod  view on Meta::CPAN

=over 4

=item .. insert a row with an auto incrementing primary key?

This happens automatically. After
L<creating|DBIO::ResultSet/create> a result object, the primary
key value created by your database can be fetched by calling C<id> (or
the access of your primary key column) on the object.

=item .. insert a row with a primary key that uses a sequence?

You need to create a trigger in your database that updates your
primary key field from the sequence. To help DBIO find the next
key value, you can tell it the name of the sequence in the
C<column_info> supplied with C<add_columns>.

 ->add_columns({ id => { sequence => 'mysequence', auto_nextval => 1 } });

=item .. insert many rows of data efficiently?

The C<populate> method in L<DBIO::ResultSet> provides
efficient bulk inserts.

L<DBIO::Fixtures> provides an alternative way to do this.

=item .. update a collection of rows at the same time?

Create a resultset using a C<search>, to filter the rows of data you
would like to update, then call C<update> on the resultset to change all
the rows at once.

=item .. use database functions when updating rows?

=item .. update a column using data from another column?

To stop the column name from being quoted, you'll need to tell DBIO
that the right hand side is an SQL identifier (it will be quoted
properly if you have quoting enabled):

 ->update({ somecolumn => { -ident => 'othercolumn' } })

This method will not retrieve the new value and put it in your Row
object. To fetch the new value, use the C<discard_changes> method on
the Row.

  # will return the scalar reference:
  $result->somecolumn()

  # issue a select using the PK to re-fetch the row data:
  $result->discard_changes();

  # Now returns the correct new value:
  $result->somecolumn()

To update and refresh at once, chain your calls:

  $result->update({ 'somecolumn' => { -ident => 'othercolumn' } })->discard_changes;

=item .. store JSON/YAML in a column and have it deflate/inflate automatically?

You can use L<DBIO::InflateColumn> to accomplish YAML/JSON storage transparently.

If you want to use JSON, then in your table schema class, do the following:

 use JSON;

 __PACKAGE__->add_columns(qw/ ... my_column ../)
 __PACKAGE__->inflate_column('my_column', {
     inflate => sub { jsonToObj(shift) },
     deflate => sub { objToJson(shift) },
 });

For YAML, in your table schema class, do the following:

 use YAML;

 __PACKAGE__->add_columns(qw/ ... my_column ../)
 __PACKAGE__->inflate_column('my_column', {
     inflate => sub { YAML::Load(shift) },
     deflate => sub { YAML::Dump(shift) },
 });

This technique is an easy way to store supplemental unstructured data in a table. Be
careful not to overuse this capability, however. If you find yourself depending more
and more on some data within the inflated column, then it may be time to factor that
data out.

=back

=head2 Custom methods in Result classes

You can add custom methods that do arbitrary things, even to unrelated tables.
For example, to provide a C<< $book->foo() >> method which searches the
cd table, you'd could add this to Book.pm:

  sub foo {
    my ($self, $col_data) = @_;
    return $self->result_source->schema->resultset('cd')->search($col_data);
  }

And invoke that on any Book Result object like so:

  my $rs = $book->foo({ title => 'Down to Earth' });

When two tables ARE related, L<DBIO::Relationship::Base> provides many
methods to find or create data in related tables for you. But if you want to
write your own methods, you can.

For example, to provide a C<< $book->foo() >> method to manually implement
what create_related() from L<DBIO::Relationship::Base> does, you could
add this to Book.pm:

  sub foo {
    my ($self, $rel_name, $col_data) = @_;
    return $self->related_resultset($rel_name)->create($col_data);
  }

Invoked like this:

  my $author = $book->foo('author', { name => 'Fred' });