DBIx::ThinSQL - A lightweight SQL helper for DBI
0.0.18 (2014-04-10) development release.
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. })
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 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 Log::Any for logging.
Works like a normal DBI. Can be used with things like DBIx::Connector to get nice transaction support.
Does a prepare but knows about bind values and quoted values.
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.
txn
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.
dump
xdump
dump_results
Like dump but sends the results to Log::Any debug().
debug()
Insert a row into the database and return the number of rows affected.
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.
Delete rows from the database and return the number of rows affected.
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.
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.
sq ( @subquery )
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'), );
Maps to "$arg1 AND $arg2 AND ...".
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
Maps to "COALESCE($arg1, $arg2, ...)".
Maps to "CAST( $arg1 AS $arg2 )".
Maps to "$arg1 || $arg2 || ...".
Maps to "COUNT($arg1, $arg2, ...)".
Maps to "EXISTS(@args)".
Maps to "MYFUNC($arg1, $arg2, ...)".
Maps to "HEX($arg1, $arg2, ...)".
Maps to "LENGTH(@args)".
Maps to "LOWER(@args)".
Maps to "LTRIM(@args)".
Maps to "MAX(@args)".
Maps to "MIN(@args)".
Maps to "RTRIM(@args)".
Maps to "$arg1 OR $arg2 OR ...".
Maps to "REPLACE($arg1,$arg2 [,$arg3])".
Maps to "SUBSTR($arg1, $arg2, ...)".
Maps to "name(col1,col2,...)".
Maps to "UPPER(@args)".
Maps to "VALUES($arg1, $arg2, ...)".
Log::Any
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
Mark Lawrence <nomad@null.net>
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.
To install DBIx::ThinSQL, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DBIx::ThinSQL
CPAN shell
perl -MCPAN -e shell install DBIx::ThinSQL
For more information on module installation, please visit the detailed CPAN module installation guide.