NAME

SQL::Concrete - render SQL from fragments and placeholders from data structures

VERSION

version 1.003

SYNOPSIS

 use SQL::Concrete ':all';

 my ( $sql, @bind ) = sql_render 'INSERT INTO table', VALUES \%item;

 my ( $sql, @bind ) = sql_render 'UPDATE table', SET( %item ), 'WHERE y <>', \2;

 # same thing:
 my ( $sql, @bind ) = sql_render 'DELETE FROM table WHERE x =', \$x, 'AND y IN', \@y;
 my ( $sql, @bind ) = sql_render 'DELETE FROM table WHERE', { x => $x, y => \@y };

DESCRIPTION

This module converts SQL fragments interleaved with variable references and some keywords into one regular SQL string along with a list of bind values, suitable for passing to DBI.

It is an antithesis of sorts to SQL::Abstract: you are expected to write most of any query as regular SQL. The job of this module is to manage your placeholders for you rather than hide the SQL, and it can infer them from data structures you usually already have. Without it, passing the data from such data structures to DBI manually would mean laboriously destructuring them into a plain list of bind values, then carefully ensuring the correspondence of placeholders with the order of bind values every time you modify the query.

This module does do some SQL generation, but it makes no attempt to invent conventions to express all possible SQL constructs. The aim is only to make common obvious cases easier to read and write. For anything beyond that you are expected to fall back to verbatim SQL.

This makes database code easier to read as well as easier to write, while easily providing ready access to all SQL features, even without SQL::Concrete having to have specific support for almost any of them.

SQL, unparametrized:

 name LIKE "%son" AND (age >= 10 AND age <= 20)

DBI with placeholders:

 'name LIKE ? AND (age >= ? AND age <= ?)', '%son', 10, 20

SQL::Abstract, trying to express it all:

 { name => { like => '%son' }, age => [ -and => { '>=', 10 }, { '<=', 20 } ] }

SQL::Concrete, lacking syntactic shortcuts for this task:

 'name LIKE', \'%son', 'AND (age >=', \10, 'AND', 'age <=', \20, ')'

INTERFACE

The recommended way to use SQL::Concrete is via DBIx::Simple, which provides an excellent alternative to plain DBI access:

 use DBIx::Simple::Concrete;
 # ...
 my $rows = $db->cquery( '
     SELECT title
     FROM threads
     WHERE date >', \$date, '
     AND', { subject => \@subjects }, '
 ' )->arrays;

The cquery method (provided by DBIx::Simple::Concrete) integrates "sql_render" directly into DBIx::Simple.

sql_render

This function converts its arguments into SQL constructs, joins them together with whitespace as necessary, and returns a single query with placeholders, plus a corresponding list of bind values.

It converts arguments according to their type as follows:

plain scalar

A verbatim SQL fragment.

 ()  'SELECT *', 'FROM', 'mytable'
 ->  'SELECT * FROM mytable'

scalar reference

A single placeholder with a corresponding bind value:

 ()  'x=', \10
 ->  'x=?', 10

array reference

A comma-separated list of placeholders and a corresponding list of bind values.

 ()  [1, 2, 3, 4]
 ->  '?, ?, ?, ?', 1, 2, 3, 4

hash reference

A conditional expression in which each key specifies the left-hand side of a term, its value specifies the right-hand side, and the type of the value specifies the SQL operator, as follows:

plain, defined scalar

A simple = comparison plus a single bind value:

 ()  { foo => 1 }
 ->  'foo = ?', 1

array reference

An IN test with a list of bind values:

 ()  { foo => [1, 2, 3] }
 ->  'foo IN (?, ?, ?)', 1, 2, 3

undefined value

An IS NULL test:

 ()  { foo => undef }
 ->  'foo IS NULL'

Multiple terms are combined using AND and surrounded with parentheses:

 ()  { foo => 1, quux => [2, 3] }
 ->  '(foo = ? AND quux IN (?, ?))', 1, 2, 3

SET

This function takes pairs of column names and values and converts them to a SET clause for an UPDATE statement:

 ()  'UPDATE article', SET( body => 'hi', user => 3 ), 'WHERE', { id => 7 }
 ->  'UPDATE article SET body=?, user=? WHERE id=?', 'hi', 3, 7

VALUES

This function takes a reference to either a hash or an array and converts it to a VALUES clause for an INSERT statement:

 ()  'INSERT INTO article', VALUES({ body => 'hi', user => 3 })
 ->  'INSERT INTO article (body, user) VALUES(?, ?)', 'hi', 3

SELECT

This function takes a list of references to either all hashes or all arrays and converts it to a UNION of SELECT clauses that can be used as an inline table reference:

 ()  SELECT [1, 2], [3, 4]
 ->  '(SELECT ?, ? UNION ALL SELECT ?, ?)', 1, 2, 3, 4

 ()  SELECT { a => 1, b => 2 }, { b => 4, a => 3 }
 ->  '(SELECT ? AS a, ? AS b UNION ALL SELECT ?, ?)', 1, 2, 3, 4

It optionally accepts a name for the table reference as its first argument:

 ()  SELECT nonsense => [1, 2, 3, 4]
 ->  '(SELECT ?, ?, ?, ?) AS nonsense', 1, 2, 3, 4

You can pass an undefined value to ask it to autogenerate a name this will be unique to this query:

 ()  SELECT undef, [1, 2, 3, 4]
 ->  '(SELECT ?, ?, ?, ?) AS tbl0', 1, 2, 3, 4

sql

This function lets you inject verbatim SQL fragments into your SQL instead of placeholders. It takes the same arguments as "sql_render" but returns one single scalar value that you can use in place of any normal scalar that would otherwise become a bind value:

 ()  'UPDATE article', SET( body => 'hi', user => 3, updated => sql('NOW()') )
 ->  'UPDATE article SET body=?, updated=NOW(), user=?', 'hi', 3

EXPORTS

The following export tags are available:

:core

Exports sql_render.

:util

Exports sql.

:clauses

Exports sql_set, sql_values, and sql_select, which are aliases for SET, VALUES, and SELECT, respectively.

:CLAUSES

Exports VALUES, SET, and SELECT.

:all

Exports everything from the :core, :util, and :CLAUSES tags.

Naturally you can also export any of these functions individually.

AUTHOR

Aristotle Pagaltzis <pagaltzis@gmx.de>

COPYRIGHT AND LICENSE

This software is copyright (c) 2015 by Aristotle Pagaltzis.

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.

cpanm SQL::Concrete

perl -MCPAN -e shell
install SQL::Concrete