NAME

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

SYNOPSIS

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

my $composed = SQL(SELECT => "*" =>
                   FROM   => entries =>
                   WHERE  => ("uid =" =>
                              PAR(SQL(SELECT => uid => FROM => authors =>
                                      WHERE => ["name = ?", 'foo'])))
                 );

my ($sql, @bind) = $composed->as_sql_bind;
# ==>
# SQL: SELECT * FROM entries WHERE uid = (SELECT uid FROM authors WHERE name = ?)
# BIND: foo

# 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 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.

In other words, generating correct SQL is all up-to-you users of SQL::Concat. If you don't (want to) learn about SQL, use other SQL generators (e.g. SQL::Maker, SQL::Abstract, ...) instead.

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

What concat() does is...

join($SEP, @ITEMS)! except it knows about bind variables and placeholders. 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... ). See SQL() for more equivalent examples.

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)

Helper methods/functions for complex SQL construction

To build complex SQL, we often need to put parens around some SQL fragments. For example:

SQL(SELECT =>
    , SQL(SELECT => "count(*)" => FROM => "foo")
    , ","
    , SQL(SELECT => "count(*)" => FROM => "bar")
)
# (WRONG) SQL: SELECT SELECT count(*) FROM foo , SELECT count(*) FROM bar

Fortunately, SQL::Concat has ->paren() method, so you can write

SQL(SELECT =>
    , SQL(SELECT => "count(*)" => FROM => "foo")->paren
    , ","
    , SQL(SELECT => "count(*)" => FROM => "bar")->paren
)
# SQL: SELECT (SELECT count(*) FROM foo) , (SELECT count(*) FROM bar)

Or you can use another function PAR().

use SQL::Concat qw/SQL PAR/;

SQL(SELECT =>
    , PAR(SELECT => "count(*)" => FROM => "foo")
    , ","
    , PAR(SELECT => "count(*)" => FROM => "bar")
)

You may feel "," is ugly. In this case, you can use CSV().

use SQL::Concat qw/SQL PAR CSV/;

SQL(SELECT =>
    , CSV(PAR(SELECT => "count(*)" => FROM => "foo")
         , PAR(SELECT => "count(*)" => FROM => "bar"))
)
# SQL: SELECT (SELECT count(*) FROM foo), (SELECT count(*) FROM bar)

You may want to use other separator to compose "UNION" query. For this, use CAT(). This will be useful to compose AND/OR too.

use SQL::Concat qw/SQL CAT/;

CAT(UNION =>
    , SQL(SELECT => "*" => FROM => "foo")
    , SQL(SELECT => "*" => FROM => "bar"))
)
# SQL: SELECT * FROM foo UNION SELECT * FROM bar

To construct SQL conditionally, you can use Ternary operator in list context as usual.

SQL(SELECT => "*" => FROM => members =>
    ($name ? SQL(WHERE => ["name = ?", $name]) : ())
)
# SQL: SELECT * FROM members WHERE name = ?

# (or when $name is empty)
# SQL: SELECT * FROM members

You may feel above cumbersome. If so, you can try another helper OPT() and PFX().

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

SQL(SELECT => "*" => FROM => members =>
    PFX(WHERE => OPT("name = ?", $name))
)

Complex example

  use SQL::Concat qw/SQL PAR OPT CSV/;

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

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

FUNCTIONS

`SQL( @ITEMS... )`

Equiv. of

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

`CSV( @ITEMS... )`

Equiv. of SQL::Concat->concat_by(', ', @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/)).

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

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

XXX: Should I use "\n" as wrapping char instead of " "?

`PAR( @ITEMS... )`

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

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

Prefix $ITEM only when @OTHER_ITEMS are not empty. Usually used like PFX(WHERE => ...conditional...).

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

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 ?".

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, @ITEMS)`

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

`paren()`

Equiv. of $obj->format('(%s)').

`format_by($FMT)`

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

`as_sql_bind()`

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

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

What concat() does is...

Helper methods/functions for complex SQL construction

Complex example

FUNCTIONS

SQL( @ITEMS... )

CSV( @ITEMS... )

CAT($SEP, @ITEMS... )

PAR( @ITEMS... )

PFX($ITEM, @OTHER_ITEMS...)

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

METHODS

SQL::Concat->new(%args)