view release on metacpan or  search on metacpan

README.md  view on Meta::CPAN

    db_delete {
        my $t : table1;
        !defined $t->age  or
        $t->age < 18;
    };

    # inserts:
    my $id = 42;
    db_insert 'users', {
        id   => $id,
        name => "moi",
    };

# DESCRIPTION

The `DBIx::Perlish` module provides the ability to work with databases
supported by the `DBI` module using Perl's own syntax for four most
common operations: SELECT, UPDATE, DELETE, and INSERT.

By using `DBIx::Perlish`, you can write most of your database
queries using a domain-specific language with Perl syntax.
Since a Perl programmer knows Perl by definition,
and might not know SQL to the same degree, this approach
generally leads to a more comprehensible and maintainable
code.

The module is not intended to replace 100% of SQL used in your program.
There is a hope, however, that it can be used to replace
a substantial portion of it.

The `DBIx::Perlish` module quite intentionally neither implements
nor cares about database administration tasks like schema design
and management.  The plain `DBI` interface is quite sufficient for
that.  Similarly, and for the same reason, it does not take care of
establishing database connections or handling transactions.  All this
is outside the scope of this module.

## Ideology

There are three sensible and semi-sensible ways of arranging code that
works with SQL databases in Perl:

- SQL sprinkling approach

    One puts queries wherever one needs to do something with the database,
    so bits and pieces of SQL are intermixed with the program logic.
    This approach can easily become an incomprehensible mess that is difficult
    to read and maintain.

- Clean and tidy approach

    Everything database-related is put into a separate module, or into a
    collection of modules.  Wherever database access is required,
    a corresponding sub or method from such a module is called from the
    main program.  Whenever something is needed that the DB module does
    not already provide, a new sub or method is added into it.

- Object-relational mapping

    One carefully designs the database schema and an associated collection
    of classes, then formulates the design in terms of any of the existing
    object-relational mapper modules like `Class::DBI`, `DBIx::Class`
    or `Tangram`, then uses objects which perform all necessary queries
    under the hood.  This approach is even cleaner than "clean and tidy"
    above, but it has other issues.  Some schemas do not map well into
    the OO space.  Typically, the resulting performance is an issue
    as well.  The performance issues can in some cases be alleviated
    by adding hand-crafted SQL in strategic places, so in this regard
    the object-relational mapping approach can resemble the "clean and tidy"
    approach.

The `DBIx::Perlish` module is meant to eliminate the majority
of the "SQL sprinkling" style of database interaction.
It is also fully compatible with the "clean and tidy" method.

## Procedural interface

### db\_fetch {}

The `db_fetch {}` function queries and returns data from
the database.

The function parses the supplied query sub,
converts it into the corresponding SQL SELECT statement,
and executes it.

What it returns depends on two things: the context and the
return statement in the query sub, if any.

If there is a return statement which specifies exactly one
column, and `db_fetch {}` is called in the scalar context,
a single scalar representing the requested column is returned
for the first row of selected data.  Example:

    my $somename = db_fetch { return user->name };

Borrowing DBI's terminology, this is analogous to

    my $somename =
        $dbh->selectrow_array("select name from user");

If there is a return statement which specifies exactly one
column, and `db_fetch {}` is called in the list context,
an array containing the specified column for all selected
rows is returned.  Example:

    my @allnames = db_fetch { return user->name };

This is analogous to

    my @allnames =
        @{$dbh->selectcol_arrayref("select name from user")};

When there is no return statement, or if 
the return statement specifies multiple columns,
then an individual row is represented by a hash
reference with column names as the keys.

In the scalar context, a single hashref is returned, which
corresponds to the first row of selected data.  Example: