NAME DBIx::ThinSQL - A lightweight SQL helper for DBI VERSION 0.0.44 (2016-05-20) 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. 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 COPYRIGHT AND LICENSE Copyright (C) 2013 Mark Lawrence 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.