NAME
   DBIx::ThinSQL - A lightweight SQL helper for DBI

VERSION
   0.0.49 (2020-02-04) development release.

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: arrayref or hashref references.

       my $ref = $db->xhashref(
           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.
       })

DESCRIPTION
   Sorry, this documentation is invalid or out of date.

   DBIx::ThinSQL is an extension to the Perl Database Interface (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 DBIx::Connector. Security
   conscious coders will be pleased to know that all user-supplied
   values are bound properly using 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
   hashref. 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 Log::Any for logging.

CONSTRUCTOR
   Works like a normal DBI. Can be used with things like
   DBIx::Connector to get nice transaction support.

DBH METHODS
   share_dir -> Path::Tiny
       Returns the path to the distribution share directory. If
       $DBIx::ThinSQL::SHARE_DIR is set then that value will be
       returned instead of the default method which uses
       File::ShareDir.

   throw_error
       If DBIX::ThinSQL or a statement raises an exception then the
       "throw_error()" method will be called. By default it just croaks
       but classes that inherit from DBIx::ThinSQL can override it. The
       original use case was to turn database error text into blessed
       objects.

   xprepare
       Does a prepare but knows about bind values and quoted values.

   xprepare_cached
       Does a prepare_cached but knows about bind values and quoted
       values.

   xval
       Creates a statement handle using xprepare(), executes it, and
       returns the result of the val() method.

   xlist
       Creates a statement handle using xprepare(), executes it, and
       returns the result of the list() method.

   xarrayref
       Does a prepare but knows about bind values and quoted values.

   xarrayrefs
       Does a prepare but knows about bind values and quoted values.

   xhashref
       Does a prepare but knows about bind values and quoted values.

   xhashrefs
       Does a prepare but knows about bind values and quoted values.

   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 "txn" can be nested. Savepoints will be used by nested
       "txn" calls for databases that support them.

   dump( $sql, [ @bind_values ] )
   xdump( @tokens )
       Debugging shortcut methods. Take either an SQL string (for
       "dump") or a set of tokens (for "xdump"), run the query, and
       then call the "dump_results" (which pretty-prints to STDOUT) on
       the resulting statement handle.

   log_debug( $sql, [ @bind_values ] )
       Like "dump" but sends the results to Log::Any "debug()".

   log_warn( $sql, [ @bind_values ] )
       Like "dump" but displays the results using Perl's "warn"
       function.

STH METHODS
   val -> SCALAR
       Return the first value of the first row as a scalar.

   list -> LIST
       Return the first row from the query as a list.

   arrayref -> ARRAYREF
       Return the first row from the query as an array reference.

   arrayrefs -> ARRAYREF
   arrayrefs -> 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.

   hashref -> HASHREF
       Delete rows from the database and return the number of rows
       affected.

   hashrefs -> ARRAYREF[HASHREF]
   hashrefs -> LIST
       Delete rows from the database and return the number of rows
       affected.

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.

   bv( $value, [ $bind_type ] ) -> 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.

   qv( $value )
   AND
   OR
   "sq ( @subquery )" -> DBIx::ThinSQL::_expr
       A function for including a sub query inside another:

           $db->xarrayref(
               select => 'subquery.col',
               from   => sq(
                   select => 'col',
                   from   => 'table',
                   where  => 'condition IS NOT NULL',
               )->as('subquery'),
           );

   sql_and( @args ) -> DBIx::ThinSQL::Expr
       Maps to "$arg1 AND $arg2 AND ...".

   sql_case( @stmts ) -> 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

   sql_coalesce(@args) -> DBIx::ThinSQL::Expr
       Maps to "COALESCE($arg1, $arg2, ...)".

   sql_cast($arg1, as => $arg2) -> DBIx::ThinSQL::Expr
       Maps to "CAST( $arg1 AS $arg2 )".

   sql_concat(@args) -> DBIx::ThinSQL::Expr
       Maps to "$arg1 || $arg2 || ...".

   sql_count(@args) -> DBIx::ThinSQL::Expr
       Maps to "COUNT($arg1, $arg2, ...)".

   sql_exists(@args) -> DBIx::ThinSQL::Expr
       Maps to "EXISTS(@args)".

   sql_func('myfunc', @args) -> DBIx::ThinSQL::Expr
       Maps to "MYFUNC($arg1, $arg2, ...)".

   sql_hex(@args) -> DBIx::ThinSQL::Expr
       Maps to "HEX($arg1, $arg2, ...)".

   sql_length(@args) -> DBIx::ThinSQL::Expr
       Maps to "LENGTH(@args)".

   sql_lower(@args) -> DBIx::ThinSQL::Expr
       Maps to "LOWER(@args)".

   sql_ltrim(@args) -> DBIx::ThinSQL::Expr
       Maps to "LTRIM(@args)".

   sql_max(@args) -> DBIx::ThinSQL::Expr
       Maps to "MAX(@args)".

   sql_min(@args) -> DBIx::ThinSQL::Expr
       Maps to "MIN(@args)".

   sql_rtrim(@args) -> DBIx::ThinSQL::Expr
       Maps to "RTRIM(@args)".

   sql_sum(@args) -> DBIx::ThinSQL::Expr
       Maps to "MIN(@args)".

   sql_or(@args) -> DBIx::ThinSQL::Expr
       Maps to "$arg1 OR $arg2 OR ...".

   sql_replace(@args) -> DBIx::ThinSQL::Expr
       Maps to "REPLACE($arg1,$arg2 [,$arg3])".

   sql_substr(@args) -> DBIx::ThinSQL::Expr
       Maps to "SUBSTR($arg1, $arg2, ...)".

   sql_table($name, @columns) -> DBIx::ThinSQL::Expr
       Maps to "name(col1,col2,...)".

   sql_upper(@args) -> DBIx::ThinSQL::Expr
       Maps to "UPPER(@args)".

   sql_values(@args) -> DBIx::ThinSQL::Expr
       Maps to "VALUES($arg1, $arg2, ...)".

SEE ALSO
   Log::Any

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

AUTHOR
   Mark Lawrence <[email protected]>

COPYRIGHT AND LICENSE
   Copyright (C) 2013-2020 Mark Lawrence <[email protected]>

   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.

You are viewing proxied material from ftp.icm.edu.pl. The copyright of proxied material belongs to its original authors. Any comments or complaints in relation to proxied material should be directed to the original authors of the content concerned. Please see the disclaimer for more details.