=head1 NAME

DBIx::DataModel::Doc::Cookbook - Helpful recipes


=head1 DOCUMENTATION CONTEXT

This chapter is part of the C<DBIx::DataModel> manual.

=over

=item *

L<SYNOPSIS AND DESCRIPTION|DBIx::DataModel>

=item *

L<DESIGN|DBIx::DataModel::Doc::Design>

=item *

L<QUICKSTART|DBIx::DataModel::Doc::Quickstart>

=item *

L<REFERENCE|DBIx::DataModel::Doc::Reference>

=item *

L<MISC|DBIx::DataModel::Doc::Misc>

=item *

L<INTERNALS|DBIx::DataModel::Doc::Internals>

=item *

L<GLOSSARY|DBIx::DataModel::Doc::Glossary>

=back


=head1 DESCRIPTION

This chapter provides some recipes for common ORM tasks.

=head1 SCHEMA DECLARATION

=head2 Automatically generate a schema

A schema skeleton can be produced automatically from
the following external sources : 
a C<DBI> connection, a L<SQL::Translator> parser, or a
C<DBIx::Class> schema. 
See L<DBIx::DataModel::Schema::Generator|DBIx::DataModel::Schema::Generator>.

=head2 Object inflation/deflation

Here is an example of inflating/deflating a scalar value from 
the database into a Perl object :

  # declare column type
  use Date::Simple;
  $schema->ColumnType(Date_simple => 
    fromDB => sub {Date::Simple->new($_[0]) if $_[0] },
    toDB   => sub {$_[0] = $_[0]->as_str    if $_[0] },
  );
  
  # apply column type to columns
  My::Table1->ColumnType(Date_simple => qw/d_start d_end/);
  My::Table2->ColumnType(Date_simple => qw/d_birth/);

B<Caveat>: the C<fromDB> / C<toDB> functions do not apply
automatically within C<-where> conditions. So the following
would not work :

  use Date::Simple qw/today/;
  my $rows = My::Table->select(-where => {d_end => {'<' => today()}});

because C<today()> returns a C<Date::Simple> object that will
not be understood by L<SQL::Abstract|SQL::Abstract> when
generating the SQL query. C<DBIx::DataModel> is not clever
enough to inspect the C<-where> conditions and decide
which column types to apply, so you have to do it yourself :

  my $today = today()->as_str;
  my $rows = My::Table->select(-where => {d_end => {'<' => $today}});


=head2 Schema versioning

Currently C<DBIx::DataModel> has no specific support
for schema versioning. Choose L<DBIx::Class> instead.


=head1 DATA RETRIEVAL

=head2 Aggregator functions

Use normal SQL syntax for aggregators, and give them
column aliases (with a vertical bar C<|>) in order to retrieve the results.

  my $row = $source->select(-columns => [qw/MAX(col1)|max_col1
                                            AVG(col2)|foo
                                            COUNT(DISTINCT(col3))|bar/],
                            -where    => ...,
                            -resultAs => 'firstrow');
  print "max is : $row->{max_col1}, average is $row->{foo}";

Or you can dispense with column aliases, and retrieve the results
directly into an arrayref, using C<< -resultAs => 'flat_arrayref' >> :

  my $array_ref = $source->select(-columns => [qw/MAX(col1)
                                                 AVG(col2)
                                                 COUNT(DISTINCT(col3))/],
                                  -where    => ...,
                                  -resultAs => 'flat_arrayref');
  my ($max_col1, $avg_col2, $count_col3) = @$array_ref;

B<Caveat>: currently, C<fromDB> handlers do not apply to aggregator functions
(this might be improved in a future version).


=head2 Database functions or stored procedures

Like above: normal SQL syntax and column aliases.

  my $rows = $source->select(-columns => [qw/FOOBAR(col1,col2)|foobar
                                            (col3+99)|big_col3/],
                             -where    => ...,
                             );
  print "$_->{foobar} and $_->{big_col3}" foreach @$rows;


=head2 Nested queries

  my $subquery = $source1->select(..., -resultAs => 'subquery');
  my $rows     = $source2->select(
      -columns => ...,
      -where   => {foo => 123, bar => {-not_in => $subquery}}
   );

=head2 Hashref inflation

There is no need for a hashref inflator: rows returned
by a C<select()> can be used directly as hashrefs.
For example here is a loop that prints a hash slice from  each row :

  my $rows       = My::Table->select(...);
  my @print_cols = qw/col3 col6 col7/;
  foreach my $row (@$rows) {
    print @{$row}{@print_cols};
  }

In fact, each row is a I<blessed> hashref. This can be a problem
with some external modules like L<JSON> that croaks when encoding
a blessed reference. In that case you can use the C<unbless> function

  foreach my $row (@$rows) {
    $schema->unbless($row);
    print to_json($row);
  }



=head2 Custom SQL

Create a L<DBIx::DataModel::View|DBIx::DataModel::View> to encapsulate your 
SQL.


=head1 DATA UPDATE

=head2 Transaction

  # anonymous sub containing the work to do
  my $to_do = sub {
    $table1->insert(...);
    $table2->delete(...);
  };
  # so far nothing has happened in the database
  
  # now do the transaction
  $schema->doTransaction($to_do);

=head2 Nested transaction

  $schema->doTransaction(sub {
    do_something;
    $schema->doTransaction(sub { 
      some_nested_code();
    });
    $schema->doTransaction(sub { 
      some_other_nested_code();
   });
  });


=head2 Generating random keys

Override the C<_singleInsert> method

  package MySchema::SomeTable;

  sub _singleInsert {
    my ($self) = @_;
    my $class = ref $self;

    my ($key_column) = $class->primKey;

    for (1..$MAX_ATTEMPTS) {
      my $random_key = int(rand($MAX_RANDOM));

        $self->{$key_column} = $random_key;
        eval {$self->_rawInsert; 1} 
          and return $random_key;   # SUCCESS

        # if duplication error, try again; otherwise die
        last unless $DBI::errstr =~ $DUPLICATE_ERROR;
     }
     croak "cannot generate a random key for $class: $@";
  }

=head2 Cascaded insert

First insert an arrayref of subrecords within the 
main record hashref; then call C<insert> on that main
record. See example in 
L<insert() reference|DBIx::DataModel::Doc::Reference/"insert">.
The precondition for this to work is that the 
relationship between the two classes should be a 
L<Composition|DBIx::DataModel::Doc::Reference/"Composition">.


=head2 Cascaded delete

  # first gather information tree from the database
  my $author = Author->fetch($author_id);
  my $distribs = $author->expand('distributions');
  $_->expand('modules') foreach @$distribs;
  
  # then delete the whole tree from the database
  $author->delete;

This only works if the relationship between classes is a 
L<Composition|DBIx::DataModel::Doc::Reference/"Composition">.
The C<expand> operations retrieve related records and add them
into a tree in memory. Then C<delete> removes from the database
all records found in the tree; therefore this is not a "true" cascaded 
delete, because the client code is responsible for fetching the
related records. 

True cascaded delete is best implemented directly in the 
database, rather than at the ORM layer.

=head2 Timestamp validation

[WRITE EXAMPLE ]

(make sure that the record was not touched between the time
it was presented to the user (display form) and the time
the user wants to update or delete that record).