The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.
 / 
DBIx-ThinSQL-0.0.49
River stage one • 1 direct dependent • 1 total dependent
/ DBIx::ThinSQL

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 <nomad@null.net>

COPYRIGHT AND LICENSE

Copyright (C) 2013-2020 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.