—

              
HideShow 359 lines of Pod
=head1 NAME
DBIx::ThinSQL - A lightweight SQL helper for DBI
=head1 VERSION
0.0.9_1 (2013-06-11) development release.
=head1 SYNOPSIS
    use strict;
    use warnings;
    use DBIx::ThinSQL qw/ bv qv /;
    my $db = DBIx::ThinSQL->connect(
        'dbi:Driver:...'
        'username',
        'password',
    );
    # Some basic CrUD statements to show the simple stuff first. Note
    # the inline binding of data that you normally have to call
    # $dbh->bind_param() on.
    my $success = $db->xdo(
        insert_into => 'actors',
        values      => {
            id    => 1,
            name  => 'John Smith',
            photo => bv( $image, DBI::SQL_BLOB ),
        },
    );
    # A "where" with a HASHref "AND"s the elements together
    my $count = $db->xdo(
        update => 'actors',
        set    => { name => 'Jack Smith' },
        where  => { id => 1, name => \'IS NOT NULL' },
    );
    # A "where" with an ARRAYref concatenates items together. Note the
    # string that is quoted according to the database type.
    my $count = $db->xdo(
        delete_from => 'actors',
        where       => [
            'actor_id = 1', ' OR ',
            'last_name != ', qv("Jones", DBI::SQL_VARCHAR ),
        ],
    );
    # Methods for reading from the database depend on the type of
    # structure you want back: array or hash references.
    my $ref = $db->xhash(
        select => [ 'id', 'name', qv("Some string") ],
        from   => 'actors',
        where  => [
            'id = ', qv( 1, DBI::SQL_INTEGER ),
            ' AND photo IS NOT NULL',
        ],
        limit  => 1,
    );
    $db->xdo(
        insert_into => [ 'table', 'col1', 'col2', 'col3' ],
        select => [ 't1.col3', 't3.col4', bv( 'value', DBI::SQL_VARCHAR ) ],
        from   => 'table AS t1',
        inner_join => 'other_table AS t2',
        on         => 't1.something = t2.else',
        left_join  => 'third_table AS t3',
        on    => [ 't3.dont = t1.care AND t1.fob = ', qv( 1, DBI::SQL_INT ) ],
        where => [],
        order_by => [ 't3.dont', 't1.col4' ],
        limit    => 2,
    );
    $db->txn( sub {
        # Anything you like, done inside a BEGIN/COMMIT pair, with
        # nested calls to txn() done inside a SAVEPOINT/RELEASE pair.
    })
=head1 DESCRIPTION
Sorry, this documentation is invalid or out of date.
B<DBIx::ThinSQL> is an extension to the Perl Database Interface
(L<DBI>).  It is designed for complicated queries and efficient access
to results.  With an API that lets you easily write almost-raw SQL,
DBIx::ThinSQL gives you unfettered access to the power and flexibility
of your underlying database. It aims to be a tool for programmers who
want their databases to work just as hard as their Perl scripts.
DBIx::ThinSQL gives you access to aggregate expressions, joins, nested
selects, unions and database-side operator invocations. Transactional
support is provided via L<DBIx::Connector>.  Security conscious coders
will be pleased to know that all user-supplied values are bound
properly using L<DBI> "bind_param()".  Binding binary data is handled
transparently across different database types.
DBIx::ThinSQL offers a couple of very simple Create, Retrieve, Update
and Delete (CRUD) action methods.  These are designed to get you up and
running quickly when your query data is already inside a hash. The
methods are abstractions of the real API, but should still read as much
as possible like SQL.
Although rows can be retrieved from the database as simple objects,
DBIx::ThinSQL does not attempt to be an Object-Relational-Mapper (ORM).
There are no auto-inflating columns or automatic joins and the code
size and speed reflect the lack of complexity.
DBIx::ThinSQL uses the light-weight L<Log::Any> for logging.
=head1 CONSTRUCTOR
Works like a normal DBI. Can be used with things like
L<DBIx::Connector> to get nice transaction support.
=head1 DBH METHODS
=over
=item xprepare
Does a prepare but knows about bind values and quoted values.
=item xarray
Does a prepare but knows about bind values and quoted values.
=item xarrays
Does a prepare but knows about bind values and quoted values.
=item xhash
Does a prepare but knows about bind values and quoted values.
=item xhashes
Does a prepare but knows about bind values and quoted values.
=item txn( &coderef )
Runs the &coderef subroutine inside an SQL transaction.  If &coderef
raises an exception then the transaction is rolled back and the error
gets re-thrown.
Calls to C<txn> can be nested. Savepoints will be used by nested C<txn>
calls for databases that support them.
=item dump( $sql, [ @bind_values ] )
=item xdump( @tokens )
Debugging shortcut methods.  Take either an SQL string (for C<dump>) or
a set of tokens (for C<xdump>), run the query, and then call the
C<dump_results> (which pretty-prints to STDOUT) on the resulting
statement handle.
=item log_debug( $sql, [ @bind_values ] )
Like C<dump> but sends the results to L<Log::Any> C<debug()>.
=back
=head1 STH METHODS
=over
=item array -> ARRAYREF
Insert a row into the database and return the number of rows affected.
=item arrays -> ARRAYREF
=item arrays -> LIST
Update rows in the database and return the number of rows affected.
This method is retricted to the wholesale replacement of column values
(no database-side calculations etc).  Multiple WHERE key/values are
only 'AND'd together. An 'undef' value maps to SQL's NULL value.
=item hash -> HASHREF
Delete rows from the database and return the number of rows affected.
=item hashes -> ARRAYREF[HASHREF]
=item hashes -> LIST
Delete rows from the database and return the number of rows affected.
=back
=head1 CLASS FUNCTIONS
The following functions can be exported individually or all at once
using the ':all' tag.  They all return an object which can be combined
with or used inside other functions.
=over 4
=item bv( $value, [ $bind_type ] ) -> L<DBIx::ThinSQL::BindValue>
This function returns an object which tells DBIx::ThinSQL to bind
$value using a placeholder. The optional $bind_type is a database type
(integer, varchar, timestamp, bytea, etc) which will be converted to
the appropriate bind constant during a prepare() or prepare_cached()
call.
=item qv( $value )
=item AND
=item OR
=item C<sq ( @subquery )> -> L<DBIx::ThinSQL::_expr>
A function for including a sub query inside another:
    $db->xarray(
        select => 'subquery.col',
        from   => sq(
            select => 'col',
            from   => 'table',
            where  => 'condition IS NOT NULL',
        )->as('subquery'),
    );
=item sql_and( @args ) -> L<DBIx::ThinSQL::Expr>
Maps to "$arg1 AND $arg2 AND ...".
=item sql_case( @stmts ) -> L<DBIx::ThinSQL::Expr>
Wraps @stmts inside a CASE/END pair while converting arguments to
expressions where needed.
    sql_case(
        when => $actors->name->is_null,
        then => 'No Name',
        else => $actors->name,
    )->as('name')
    # CASE WHEN actors0.name IS NULL
    # THEN ? ELSE actors0.name END AS name
=item sql_coalesce(@args) -> L<DBIx::ThinSQL::Expr>
Maps to "COALESCE($arg1, $arg2, ...)".
=item sql_cast($arg1, as => $arg2) -> L<DBIx::ThinSQL::Expr>
Maps to "CAST( $arg1 AS $arg2 )".
=item sql_concat(@args) -> L<DBIx::ThinSQL::Expr>
Maps to "$arg1 || $arg2 || ...".
=item sql_count(@args) -> L<DBIx::ThinSQL::Expr>
Maps to "COUNT($arg1, $arg2, ...)".
=item sql_exists(@args) -> L<DBIx::ThinSQL::Expr>
Maps to "EXISTS(@args)".
=item sql_func('myfunc', @args) -> L<DBIx::ThinSQL::Expr>
Maps to "MYFUNC($arg1, $arg2, ...)".
=item sql_hex(@args) -> L<DBIx::ThinSQL::Expr>
Maps to "HEX($arg1, $arg2, ...)".
=item sql_length(@args) -> L<DBIx::ThinSQL::Expr>
Maps to "LENGTH(@args)".
=item sql_lower(@args) -> L<DBIx::ThinSQL::Expr>
Maps to "LOWER(@args)".
=item sql_ltrim(@args) -> L<DBIx::ThinSQL::Expr>
Maps to "LTRIM(@args)".
=item sql_max(@args) -> L<DBIx::ThinSQL::Expr>
Maps to "MAX(@args)".
=item sql_min(@args) -> L<DBIx::ThinSQL::Expr>
Maps to "MIN(@args)".
=item sql_rtrim(@args) -> L<DBIx::ThinSQL::Expr>
Maps to "RTRIM(@args)".
=item sql_sum(@args) -> L<DBIx::ThinSQL::Expr>
Maps to "MIN(@args)".
=item sql_or(@args) -> L<DBIx::ThinSQL::Expr>
Maps to "$arg1 OR $arg2 OR ...".
=item sql_replace(@args) -> L<DBIx::ThinSQL::Expr>
Maps to "REPLACE($arg1,$arg2 [,$arg3])".
=item sql_substr(@args) -> L<DBIx::ThinSQL::Expr>
Maps to "SUBSTR($arg1, $arg2, ...)".
=item sql_table($name, @columns) -> L<DBIx::ThinSQL::Expr>
Maps to "name(col1,col2,...)".
=item sql_upper(@args) -> L<DBIx::ThinSQL::Expr>
Maps to "UPPER(@args)".
=item sql_values(@args) -> L<DBIx::ThinSQL::Expr>
Maps to "VALUES($arg1, $arg2, ...)".
=back
=head1 SEE ALSO
L<Log::Any>
=head1 DEVELOPMENT & SUPPORT
DBIx::ThinSQL is managed via Github:
    https://github.com/mlawren/p5-DBIx-ThinSQL/tree/devel
DBIx::ThinSQL follows a semantic versioning scheme:
    http://semver.org
=head1 AUTHOR
Mark Lawrence E<lt>nomad@null.netE<gt>
=head1 COPYRIGHT AND LICENSE
Copyright (C) 2013 Mark Lawrence <nomad@null.net>
This program is free software; you can redistribute it and/or modify it
under the terms of the GNU General Public License as published by the
Free Software Foundation; either version 3 of the License, or (at your
option) any later version.