SQL::Concat - SQL concatenator, only cares about bind-vars, to write SQL generator
# # 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');
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.
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.
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.
join($SEP, @ITEMS)
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).
' '
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... ).
SQL()
SQL::Concat->new(sep => ' ')->concat( @ITEMS... )
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.
->bind
?
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)
Finally, concat() can accept SQL::Concat instances. In this case, ->sql and ->bind are extracted and treated just like "BIND_ARRAY"
->sql
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)
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().
select * from artists where name = ? order by age
where name = ?
use SQL::Concat qw/SQL/; $q = SQL("select * from artists" , ($name ? ["where name = ?", $name] : ()) , "order by age" ); ($sql, @bind) = $q->as_sql_bind;
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.
age = ?
@OTHER
$STR
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.
OR
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().
AND address = ?
$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
SQL( @ITEMS... )
Equiv. of
SQL::Concat->concat( @ITEMS... )
SQL::Concat->concat_by(' ', @ITEMS... )
CAT($SEP, @ITEMS... )
Equiv. of SQL::Concat->concat_by($SEP, @ITEMS... ), except $SEP is wrapped by whitespace when necessary.
SQL::Concat->concat_by($SEP, @ITEMS... )
$SEP
CAT(UNION => , SQL("select * from foo") , SQL("select * from bar") )
If @ITEMS are empty, this returns empty result:
@ITEMS
CAT(AND => , ($name ? ["name = ?", $name] : ()) , ($age ? ["age = ?", $age] : ()) )
PFX($ITEM, @OTHER_ITEMS...)
Prefix $ITEM only when @OTHER_ITEMS are not empty.
$ITEM
@OTHER_ITEMS
PFX(WHERE => ($name ? ["name = ?", $name] : ()) )
Usually used with CAT() like following:
CAT()
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.
(SQL([$RAW_SQL, $VALUE]), @OTHER_ITEMS)
This is designed to help generating "LIMIT ? OFFSET ?".
"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
SQL( ITEMS...)->paren
CSV( @ITEMS... )
Equiv. of CAT(', ', @ITEMS... )
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/)).
SQL(SELECT => "x, y, z")
SQL(SELECT => CSV(qw/x y z/))
SQL::Concat->new(%args)
Constructor, inherited from MOP4Import::Base::Configure.
Following options has their getter. To set these options after new, use "configure" in MOP4Import::Base::Configure method.
Separator, used in concat().
SQL, constructed when concat() is called. Once set, you are not allowed to call "concat" again.
Bind variables, constructed when "BIND_ARRAY" is given to concat().
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... )
SQL::Concat->new(sep => $SEP)->concat( @ITEMS... )
->is_empty()
Test whether $obj->sql doesn't contain /\S/ or not.
$obj->sql
/\S/
->paren()
Equiv. of $obj->is_empty ? () : $obj->format_by('(%s)').
$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.
sprintf($FMT, $self->sql)
If optional integer argument $INDENT is given, sql is indented before formatting.
$INDENT
sql
->as_sql_bind()
my ($sql, @bind) = SQL(...)->as_sql_bind;
Extract $self->sql and @{$self->bind}. If caller is scalar context, wrap them with [].
$self->sql
@{$self->bind}
[]
->sql_bind_pair()
my ($sql, $bind) = SQL(...)->sql_bind_pair;
Extract [$self->sql, $self->bind]. If caller is scalar context, wrap them with [].
[$self->sql, $self->bind]
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
SQL::Object, SQL::Maker, SQL::QueryMaker
Copyright (C) Kobayasi, Hiroaki.
This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.
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.