view release on metacpan or  search on metacpan

lib/DBIx/Squirrel.pod  view on Meta::CPAN

Use Perl's standard importing mechanisms (as shown above) to share
associations among different package namespaces.

=item * B<Resolving an association>

Fetching the reference to the associated database entity is accomplished
by calling the helper function without any arguments.

When no association exists in this scenario, a helper returns C<undef>.

=item * B<Addressing an association>

Addressing an association amounts to doing something meaningful with it,
and we accomplish this by calling the helper function with one or more
arguments.

Once associated with a database entity, a helper function will consume
any arguments that are passed to it and send a version of these to the
database entity method that imparts meaning to the interaction.

Meaning in this context is determined by the type of association:

=over

=item *

for a database connection, a statement is prepared using the C<prepare> method;

=item *

for statements and iterators, these are executed with the C<execute> and C<iterate>
methods respectively.

=back

B<Clearly there is a paradox here>, and itcentres around those statements
and iterators that take no parameters and expect no bind-values. In order
to smooth-out this wrinkle, you can opt to enclose arguments inside an
anonymous array or hash. When no bind-values are expected, you can coerce
the helper into performing the execution by passing an empty ARRAYREF or
HASHREF. Alternatively, you could just resolve the association and call
the relevant method manually.

=back

=head4 Examples

=over

=item *

Let us do a full worked example. We will connect to a database, create and
work with two result sets, one of which expects a single bind-value. Some
concepts will be expanded upon and improved later, but it might be helpful
to dip a toe in the water ahead of time:

    use DBIx::Squirrel database_entities => [ qw/db artists artist/ ];

    # Associate helper ("db") with our database connection:

    @connect_args = ( 'dbi:SQLite:dbname=chinook.db', '', '', { sqlite_unicode => 1 } );
    db( DBIx::Squirrel->connect(@connection_args) );

    # Resolve the database connection helper ("db"), using it to
    # associate helpers ("artist" and "artists") with different
    # result sets:

    artist( db->results('SELECT * FROM artists WHERE Name=? LIMIT 1') );
    artists( db->results('SELECT * FROM artists') );

    # Address the helper ("artist"), passing it a bind-value, to get
    # the ArtistId of the artist whose name is "Aerosmith".
    #
    # We could have called "next" to get the only matching record, but by
    # calling "single" (or "first") we can ensure that there are no warnings
    # about dangling active statements emitted when we disconnect from the
    # database.

    print artist('Aerosmith')->single->ArtistId, "\n";

    # Iterate over the "artists" result set, printing the Name-column for
    # each artist. We don't need to trigger execution manually because
    # the "next" method will do that for us, if it is necessary.

    while ( artists->next ) {
        print $_->Name, "\n";
    };

=back

=head2 CONNECTING TO DATABASES

Connecting to a database using C<DBIx::Squirrel> may be done exactly as it
would when using the C<DBI>'s C<connect_cached> and C<connect> methods.

=head3 Cloning database connections

The C<connect> method implemented by the C<DBIx::Squirrel> package offers
an alternative form:

    $new_dbh = DBIx::Squirrel->connect($original_dbh, \%attr);

This form clones another connection object and returns a brand new object
that is blessed using the same class that invoked the C<connect> method.
The method will allow you to clone database connections created by the
C<DBI> and any subclasses (C<DBIx::Squirrel> being one).

=head2 PREPARING STATEMENTS

Preparing a statement using C<DBIx::Squirrel> may be done exactly as
it would be done using the C<DBI>'s C<prepare_cached> and C<prepare>
methods.

=head3 Placeholders

A nice quality-of-life improvement offered by C<DBIx::Squirrel>'s own
implementation of the C<prepare_cached> and C<prepare> methods is their
built-in ability to cope with a number of different placeholder styles:

=over

lib/DBIx/Squirrel.pod  view on Meta::CPAN

Transformation can be summarised as the automatic, just-in-time processing,
re-shaping or filtering of results, as they are fetched from the database
and before they are handed-off to the caller.

A transformation is comprised of one or more processing stages. Each stage
receives its version of the result, changes it or does something else it,
and finally hands it off to the next stage, or to the caller if there are
no more stages.

Recall that there are two kinds of iterator, and two methods to construct
each:

    Basic Iterators              |  Fancy Iterators
    -----------------------------|------------------------------
    $itr = $dbh->iterate(        |  $itr = $dbh->results(
        $query,                  |      $query,
        [undef|\%attr,]          |      [undef|\%attr,]
        [@bindvalues,]           |      [@bindvalues,]
        [@transforms]            |      [@transforms]
    );                           |  );
                                 |
    $itr = $sth->iterate(        |  $itr = $sth->results(
        [@bindvalues,]           |      [@bindvalues,]
        [@transforms]            |      [@transforms]
    );                           |  );

The final element of each constructor's argument-list is the transformation
pipeline (C<[@transforms]>). Each stage of this pipeline is an individual
processing step, represented by a CODEREF (or a call that returns a CODEREF).

Each stage of a transformation receives the latest version of the result via
the argument-list (C<$_[0]> to be precise). For the sake of convenience (and
for convention), this result is also available as C<$_>. If you prefer to
rely on something like C<$_>, but would like something much less ephemeral,
just C<use DBIx::Squirrel::util 'result'> and use the C<result> function
inside your transformation stage.

Handing off to the next stage, or the caller, is with an explicit C<return>
statement, or the result of evaluating the unit's final expression. Returning
nothingE<mdash>either C<()>, or a bare C<return>E<mdash>from a processing
step will filter the result out entirely, and no further processing steps
will apply to it.

=head3 Examples

=over

=item 1.

See script C<examples/transformations/01.pl>:

    use DBIx::Squirrel database_entities => [qw/db get_artist_id_by_name/];

    db do {
        DBIx::Squirrel->connect(
            "dbi:SQLite:dbname=./t/data/chinook.db",
            "",
            "",
            {   PrintError     => !!0,
                RaiseError     => !!1,
                sqlite_unicode => !!1,
            },
        );
    };

    get_artist_id_by_name do {
        db->results(
            "SELECT ArtistId, Name FROM artists WHERE Name=? LIMIT 1" => sub {
                my($artist) = @_;
                print "----\n";
                print "Name: ", $artist->Name, "\n";
                return $artist;
            } => sub {$_->ArtistId}
        );
    };

    foreach my $name ("AC/DC", "Aerosmith", "Darling West", "Rush") {
        if (get_artist_id_by_name($name)->single) {
            print "ArtistId: $_\n";
        }
    }

    db->disconnect();

The script is comprised of four parts:

=over

=item *

B<Connect to the database>

Here, I am not just connecting to the database. I am associating the resulting
database connection handle with the C<db> helper function, meaning I can refer
to it as C<db> in future.

=item *

B<Create the C<get_artist_id_by_name> helper function>

Here, I am constructing a fancy iterator and also associating it with the
C<get_artist_id_by_name> helper function. This means I can just call the
C<get_artist_id_by_name> function to execute the iterator in future.

Also here, I describe the the kind of processing I want applied to every
single result produced by this iterator, expressed as a transformation
pipeline that is comprised of two separate stages:

=over

=item *

I want the names of matched artists printed nicely on the console;

=item *

I am only intersted in getting back the artist's id.

=back

=item *