NAME

SQL::Concat - SQL concatenator, only cares about bind-vars, to write SQL generator

SYNOPSIS

#
# Functional interface
#
use SQL::Concat qw/SQL/;

$q = SQL("SELECT uid FROM authors"); # Just single fixed SQL

$q = SQL("SELECT uid FROM authors"   # Fixed SQL fragment

        , ["WHERE name = ?", 'foo']  # Pair of placeholder(s) and value(s)

        , "ORDER BY uid"             # Fixed SQL fragment (again)

        , ($reverse ? "desc" : ())   # Conditional Fixed SQL fragment
      );

$q = SQL($q                          # SQL(SQL(SQL(...), SQL(..))) is ok
         , "LIMIT 10"
         , ["OFFSET ?", 30]
      );

# Extract concatenated SQL and bind vars.
#
($sql, @binds) = $q->as_sql_bind;
# ==>
# SQL: SELECT uid FROM authors WHERE name = ? ORDER BY uid LIMIT 10 OFFSET ?
# BIND: ('foo', 30)

#
# SQL() doesn't care about composed SQL syntax. It just concat given args.
#
$q = SQL("SELECT uid", "FROM authors");
$q = SQL("SELECT uid FROM", "authors");
$q = SQL(SELECT => uid => FROM => 'authors');

#
# OO Interface
#
my $comp = SQL::Concat->new(sep => ' ')
  ->concat(SELECT => foo => FROM => 'bar');

DESCRIPTION

SQL::Concat is NOT a SQL generator, but a minimalistic SQL fragments concatenator with safe bind-variable handling. SQL::Concat doesn't care anything about SQL syntax but placeholder and bind-variables. Other important topics to generate correct SQL such as SQL syntaxes, SQL keywords, quotes, or even parens are all remained your-side.

This module only focuses on correctly concatenating SQL fragments with keeping their corresponding bind variables.

Motivation

To run complex queries on RDBs, you must compose complex SQLs. There are many feature-rich SQL generators on CPAN to help these tasks (e.g. SQL::Abstract, SQL::Maker, SQL::QueryMaker, ...). Unfortunately, they themselves come with their own syntax and semantics and have significant learning cost. And anyway, when you want to generate complex SQL at some level, you can't avoid learning target SQL anymore. Eventually, you may realize you doubled complexity and learning cost.

So, this module is written not for SQL refusers but for dynamic SQL programmers who really want to write precisely controlled SQL, who already know SQL enough and just want to handle placeholders and bind-variables safely.

Concatenate STRING, BIND_ARRAY and SQL::Concat

SQL::Concat can concatenate following four kind of values into single SQL::Concat object.

SQL("SELECT uid FROM authors"   # STRING

  , ["WHERE name = ?", 'foo']   # BIND_ARRAY

  , SQL("ORDER BY uid")         # SQL::Concat object

  , undef                       # undef is ok and silently disappears.
);

In other words, SQL::Concat is join($SEP, @ITEMS) with special handling for pairs of placeholders and bind variables.

Default $SEP is a space character ' ' but you can give it as sep => $sep option for new() or constructor argument like SQL::Concat->concat_by($SEP).

STRING

Non-reference values are used just as resulting SQL as-is. This means each given strings are treated as RAW SQL fragment. If you want to use foreign values, you must use next "BIND_ARRAY".

use SQL::Concat qw/SQL/;

SQL("SELECT 1")->as_sql_bind;
# SQL: "SELECT 1"
# BIND: ()

SQL("SELECT foo, bar" => FROM => 'baz', "\nORDER BY bar")->as_sql_bind;
# SQL: "SELECT foo, bar FROM baz
#       ORDER BY bar"
# BIND: ()

Note: SQL() is just a shorthand of SQL::Concat->new(sep => ' ')->concat( @ITEMS... ).

BIND_ARRAY [$RAW_SQL, @BIND]

If item is ARRAY reference, it is treated as BIND_ARRAY. The first element of BIND_ARRAY is treated as RAW SQL. The rest of the elements are pushed into ->bind array. This SQL fragment must contain same number of SQL-placeholders(?) with corresponding @BIND variables.

SQL(["city = ?", 'tokyo'])->as_sql_bind
# SQL: "city = ?"
# BIND: ('tokyo')

SQL(["age BETWEEN ? AND ?", 20, 65])->as_sql_bind
# SQL: "age BETWEEN ? AND ?"
# BIND: (20, 65)

SQL::Concat

Finally, concat() can accept SQL::Concat instances. In this case, ->sql and ->bind are extracted and treated just like "BIND_ARRAY"

SQL("SELECT * FROM members WHERE" =>
    SQL(["city = ?", "tokyo"]),
    AND =>
    SQL(["age BETWEEN ? AND ?", 20, 65])
)->as_sql_bind;
# SQL: "SELECT * FROM members WHERE city = ? AND age BETWEEN ? AND ?"
# BIND: ('tokyo', 20, 65)

TUTORIAL

Hide WHERE clause if $name is empty

Suppose you have a sql statement select * from artists where name = ? order by age and you want to make where name = ? part conditional. It can be achieved via SQL().

use SQL::Concat qw/SQL/;

$q = SQL("select * from artists"
        , ($name ? ["where name = ?", $name] : ())
        , "order by age"
     );
($sql, @bind) = $q->as_sql_bind;

Add more conditions with parens

Then, you want to add age = ? to where clause. So you may want to put "WHERE" only if $name or $age is present. You can achieve it via PFX($STR, @OTHER). PFX() prefixes @OTHER with $STR. If @OTHER is empty, whole PFX() is also empty.

use SQL::Concat qw/PFX/;

$q = SQL("select * from artists"
        , PFX("WHERE"
             , ($name ? ["name = ?", $name] : ())
             , ($age  ? ["age = ?", $age] : ())
          )
        , "order by age"
     );
# (Wrong)
# select * from artists WHERE name = ? age = ? order by age

Unfortunately, this doesn't work if both $name and $age is given. You must decide conjunction or disjunction. Suppose this time you want to put OR between them (oh, really?;-). You can achieve it via CAT(). CAT() behaves like Perl's join($SEP, @ITEM) but keeps bind-variables safely.

use SQL::Concat qw/CAT/;

$q = SQL("select * from artists"
        , PFX("WHERE" =>
             CAT("OR"
                , ($name ? ["name = ?", $name] : ())
                , ($age  ? ["age = ?", $age] : ())
             )
          )
        , "order by age"
     );
# select * from artists WHERE name = ? OR age = ? order by age

Then, you may feel above is bit complicated and factorize it out.

$c = CAT("OR"
        , ($name ? ["name = ?", $name] : ())
        , ($age  ? ["age = ?", $age] : ())
     );
$q = SQL("select * from artists"
        , PFX(WHERE => $c)
        , "order by age"
     );

Then, you want to add another condtion AND address = ?. You will nest CAT().

$c = CAT("AND"
        , CAT("OR"
             , ($name ? ["name = ?", $name] : ())
             , ($age  ? ["age = ?", $age] : ())
          )
        , ($address ? ["address = ?", $address] : ())
     );
#..
# (Wrong)
# select * from artists WHERE name = ? OR age = ? AND address = ? order by age

Unfortunately, this doesn't work as expected because of the lack of paren. To put paren around "OR" clause, you can use ->paren() method.

$c = CAT("AND"
        , CAT("OR"
             , ($name ? ["name = ?", $name] : ())
             , ($age  ? ["age = ?", $age] : ())
          )->paren                                   # <<----- THIS
        , ($address ? ["address = ?", $address] : ())
     );
# select * from artists WHERE (name = ? OR age = ?) AND address = ? order by age

FUNCTIONS

`SQL( @ITEMS... )`

Equiv. of

SQL::Concat->concat( @ITEMS... )
SQL::Concat->concat_by(' ', @ITEMS... )
SQL::Concat->new(sep => ' ')->concat( @ITEMS... )

`CAT($SEP, @ITEMS... )`

Equiv. of SQL::Concat->concat_by($SEP, @ITEMS... ), except $SEP is wrapped by whitespace when necessary.

CAT(UNION =>
    , SQL("select * from foo")
    , SQL("select * from bar")
)

If @ITEMS are empty, this returns empty result:

CAT(AND =>
    , ($name ? ["name = ?", $name] : ())
    , ($age  ? ["age = ?", $age]   : ())
)

`PFX($ITEM, @OTHER_ITEMS...)`

Prefix $ITEM only when @OTHER_ITEMS are not empty.

PFX(WHERE =>
    ($name ? ["name = ?", $name] : ())
)

Usually used with CAT() like following:

PFX(WHERE =>
    CAT('AND'
        , ($name ? ["name = ?", $name] : ())
        , ($age  ? ["age = ?", $age]   : ())
    )
)

`OPT(RAW_SQL, VALUE, @OTHER...)`

If VALUE is defined, (SQL([$RAW_SQL, $VALUE]), @OTHER_ITEMS) are returned. Otherwise empty list is returned.

This is designed to help generating "LIMIT ? OFFSET ?".

OPT("limit ?", $limit, OPT("offset ?", $offset));

is shorthand version of:

SQL(defined $limit
   ? (["limit ?", $limit]
     , SQL(defined $offset
          ? ["offset ?", $offset]
          : ()
       )
     )
   : ()
)

`PAR( @ITEMS... )`

Equiv. of SQL( ITEMS...)->paren

`CSV( @ITEMS... )`

Equiv. of CAT(', ', @ITEMS... )

Note: you can use "," anywhere in concat() items. For example, you can write SQL(SELECT => "x, y, z") instead of SQL(SELECT => CSV(qw/x y z/)).

METHODS

`SQL::Concat->new(%args)`

Constructor, inherited from MOP4Import::Base::Configure.

Options

Following options has their getter. To set these options after new, use "configure" in MOP4Import::Base::Configure method.

sep: Separator, used in concat().
sql: SQL, constructed when concat() is called. Once set, you are not allowed to call "concat" again.
bind: Bind variables, constructed when "BIND_ARRAY" is given to concat().

`SQL::Concat->concat( @ITEMS... )`

Central operation of SQL::Concat. It basically does:

$self->{bind} = [];
foreach my MY $item (@_) {
  next unless defined $item;
  if (not ref $item) {
    push @sql, $item;
  } else {
    $item = SQL::Concat->of_bind_array($item)
      if ref $item eq 'ARRAY';

    $item->validate_placeholders;

    push @sql, $item->{sql};
    push @{$self->{bind}}, @{$item->{bind}};
  }
}
$self->{sql} = join($self->{sep}, @sql);

`SQL::Concat->concat_by($SEP, @I..)`

Equiv. of SQL::Concat->new(sep => $SEP)->concat( @ITEMS... )

`->is_empty()`

Test whether $obj->sql doesn't contain /\S/ or not.

`->paren()`

Equiv. of $obj->is_empty ? () : $obj->format_by('(%s)').

`->paren_nl_indent()`

Indenting version of ->paren() method.

$q = SQL("select * from artists where aid in"
         => SQL(["select aid from records where release_year = ?", $year])
            ->paren_nl_indent
     );

Above generates following:

select * from artists where aid in (
  select aid from records where release_year = ?
)

`->format_by($FMT, ?$INDENT?)`

Apply sprintf($FMT, $self->sql). This will create a clone of $self.

If optional integer argument $INDENT is given, sql is indented before formatting.

`->as_sql_bind()`

my ($sql, @bind) = SQL(...)->as_sql_bind;

Extract $self->sql and @{$self->bind}. If caller is scalar context, wrap them with [].

`->sql_bind_pair()`

my ($sql, $bind) = SQL(...)->sql_bind_pair;

Extract [$self->sql, $self->bind]. If caller is scalar context, wrap them with [].

MISC

Complex example

  use SQL::Concat qw/SQL CAT OPT/;

  my ($tags, $limit, $offset, $reverse) = @_;

  my $pager = OPT("limit ?", $limit, OPT("offset ?", $offset));

  my ($sql, @bind)
    = SQL("SELECT datetime(ts, 'unixepoch', 'localtime') as dt, eid, path"
	  , "FROM entrytext"
	  , ($tags
	     ? SQL("WHERE eid IN"
                   , SQL("SELECT eid FROM"
                         => CAT("\nINTERSECT\n"
                                => map {
                                  SQL("SELECT DISTINCT eid, ts FROM entry_tag"
                                      , "WHERE tid IN"
                                      => SQL("SELECT tid FROM tag WHERE"
                                             , ["tag glob ?", lc($_)])
                                      ->paren_nl_indent
                                    )
                                } @$tags
                              )->paren_nl_indent
                         , "\nORDER BY"
                         , "ts desc, eid desc"
                         , $pager)->paren_nl_indent
                 )
             : ())
	  , "\nORDER BY"
	  , "fid desc, feno desc"
	  , ($tags ? () : $pager)
	)->as_sql_bind;

Generated SQL example:

SELECT datetime(ts, 'unixepoch', 'localtime') as dt, eid, path FROM entrytext WHERE eid IN (
  SELECT eid FROM (
    SELECT DISTINCT eid, ts FROM entry_tag WHERE tid IN (
      SELECT tid FROM tag WHERE tag glob ?
    )
    INTERSECT
    SELECT DISTINCT eid, ts FROM entry_tag WHERE tid IN (
      SELECT tid FROM tag WHERE tag glob ?
    )
  )
  ORDER BY ts desc, eid desc limit ? offset ?
)
ORDER BY fid desc, feno desc

LICENSE

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

AUTHOR

Kobayasi, Hiroaki <hkoba @ cpan.org>

To install SQL::Concat, copy and paste the appropriate command in to your terminal.

cpanm

cpanm SQL::Concat

CPAN shell

perl -MCPAN -e shell
install SQL::Concat

For more information on module installation, please visit the detailed CPAN module installation guide.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)

NAME

SYNOPSIS

DESCRIPTION

Motivation

Concatenate STRING, BIND_ARRAY and SQL::Concat

TUTORIAL

Hide WHERE clause if $name is empty

Add more conditions with parens

FUNCTIONS

SQL( @ITEMS... )

CAT($SEP, @ITEMS... )

PFX($ITEM, @OTHER_ITEMS...)

OPT(RAW_SQL, VALUE, @OTHER...)

PAR( @ITEMS... )

CSV( @ITEMS... )

METHODS

SQL::Concat->new(%args)

Options

SQL::Concat->concat( @ITEMS... )

SQL::Concat->concat_by($SEP, @I..)

->is_empty()

->paren()

->paren_nl_indent()

->format_by($FMT, ?$INDENT?)

->as_sql_bind()

->sql_bind_pair()