view release on metacpan or  search on metacpan

docs/POD/README.md  view on Meta::CPAN

easily use scalar references—helper functions do possess the advantage
of being shared more easily among package namespaces than, say, lexical
variables.

Helper semantics deal with three common types of interaction:

- **Establishing an association**

    Before it can be used, a helper must first be associated with a database
    entity. This is accomplished by passing the function single argument: a
    reference to the associated object.

    Once established, associations are _sticky_ and cannot easily be undone.
    You should take care to create them once only, in a sensible place.

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

- **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 `undef`.

- **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:

    - for a database connection, a statement is prepared using the `prepare` method;
    - for statements and iterators, these are executed with the `execute` and `iterate`
    methods respectively.

    **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.

#### Examples

- 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',
        ]));

        # Address the helper ("artist"), passing it a bind-value, to get
        # the ArtistId of the artist whose name is "Aerosmith".
        #
        # We could call "next" to get the next matching record, and that would
        # be just fine. However, by calling "single" (or "first"), we ensure
        # that there are no warnings about dangling active statements when we
        # disconnect from the database. Furthermore, we would be warned if
        # the result set contained additional rows.

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

        artists(db->results('SELECT * FROM artists'));

        # 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";
        };

## CONNECTING TO DATABASES

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

### Cloning database connections

The `connect` method implemented by the `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 `connect` method.
The method will allow you to clone database connections created by the
`DBI` and any subclasses (`DBIx::Squirrel` being one).

## PREPARING STATEMENTS

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

Where, `DBI` expects a SQL statement string, `DBIx::Sqirrel` also accepts
a reference to an array of smaller strings; these will be concatenated
using a single SPACE separator. Other `DBIx::Squirrel` methods taking a
SQL statement provide the same accommodation.

docs/POD/README.md  view on Meta::CPAN

All `DBIx::Squirrel` iterators support an optional processing step called
_transformation_.

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,          |      $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 (`[@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 (`$_[0]` to be precise). For the sake of convenience (and
for convention), this result is also available as `$_`. If you prefer to
rely on something like `$_`, but would like something much less ephemeral,
just `use DBIx::Squirrel::util 'result'` and use the `result` function
inside your transformation stage.

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

### Examples

1. See script `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:

    - **Connect to the database**

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

    - **Create the `get_artist_id_by_name` helper function**

        Here, I am constructing a fancy iterator and also associating it with the
        `get_artist_id_by_name` helper function. This means I can just call the
        `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:

        - I want the names of matched artists printed nicely on the console;
        - I am only intersted in getting back the artist's id.

    - **Query the database and process the results**

        Here, I'm executing the query once for each one of four artists to get and
        print their artist ids.

    - **Disconnect from the database**

        Just as we would with the `DBI`.

    Find the script and run it: