SQL::Functional - Create SQL programmatically
my ($select, @select_params) = SELECT star, FROM( 'foo' ), WHERE match( 'bar', '=', 1 ); # Run through DBI my $sth = $dbh->prepare_cached( $select ) or die $dbh->errstr; $sth->execute( @select_params ) or die $sth->errstr; my $results = $sth->fetchall_arrayref; $sth->finish; my ($insert, @insert_params) = INSERT INTO 'foo', [ 'bar', ], VALUES [ 1, ]; my ($update, @update_params) = UPDATE 'foo', SET( op( 'bar', '=', 1 ), op( 'baz', '=', 2 ), ), WHERE match( 'qux', '=', 3 ); my ($delete, @delete_params) = DELETE FROM( 'foo' ), WHERE match( 'bar', '=', 1 );
Builds SQL programmatically through a function-based interface.
Generally, functions mapping to SQL keywords (like SELECT or FROM) are uppercase. Additional functions (like match and table) are lowercase.
SELECT
FROM
match
table
Functions gotta go somewhere. If you want to keep it out of your top level namespace, but also want to keep things short, try this:
package Q; use SQL::Functional; package main; my ($sql, @params) = Q::SELECT Q::star, Q::FROM 'foo';
Not exactly pretty, but it works.
These return a list. The first element is the generated SQL. The rest are the bind params.
These are used to build the statement. Their return values eventually make their way to one of the top-level functions above.
A star, like the one you would use to say SELECT * FROM . . ..
SELECT * FROM . . .
Creates a SQL::Functional::FieldClause and returns it. You pass in the name of a field, like the names of columns..
Alias for field().
field()
FROM(qw{ foo bar baz })
Creates a SQL::Functional::FromClause and returns it. You pass in a list of tables as a string name. Alternatively, they can also be specified by a SQL::Functional::TableClause object, which just so happens to be returned by the table function:
my $foo_tbl = table 'foo'; my $from_clause = FROM $foo_tbl;
Creates a SQL::Functional::WhereClause and returns it. You pass in a single sub clause. Since the and and or functions are a single clause that take other clauses, you can chain these together to create your full WHERE clause.
and
or
WHERE
my $where_clause = WHERE AND( match( 'bar', '=', 1 ), OR( match( 'baz', '=', 2 ), match( 'qux', '=', 3 ), ), );
Creates a SQL::Functional::MatchClause and returns it. This is how you would setup SQL phrases like baz = 1 in your WHERE. It takes a string, an SQL operator, and a value to match. "Raw" values will always be set as a bind parameter. Alternatively, if the value is an object that does the SQL::Functional::Clause role, it will be added as-is.
baz = 1
Alias for match. The wording of match tends to look better inside SELECT ... WHERE ... statements, while op tends to be better inside UPDATE ... SET ... statements.
SELECT ... WHERE ...
op
UPDATE ... SET ...
Creates a SQL::Functional::MatchClause and returns it. The difference between this and match is that the third argument is used directly, rather than a placeholder. This is useful, for example, when you want to do a join like:
WHERE foo.id = bar.foo_id
Don't know what's wrong with INNER JOIN, but some people prefer this syntax.
INNER JOIN
Creates a SQL::Functional::TableClause and returns it. Takes a string for the name of the table.
If you'd like to alias your table name, you can create it with this function, and then call the as method on the object. For instance:
as
my $foo_tbl = table 'foo'; my $bar_tbl = table 'bar'; $foo_tbl->as( 'f' ); $bar_tbl->as( 'b' ); my ($sql, @sql_params) = SELECT [ $foo_tbl->field( 'qux' ), $foo_tbl->field( 'quux' ), $bar_tbl->field( 'quuux' ), ], FROM( $foo_tbl, $bar_tbl ), ...
The calls to the field method will include the table alias.
field
Alias for table. The wording here is better for INESRT statements.
INESRT
ORDER_BY 'foo', 'bar';
Creates a SQL::Functional::OrderByClause and returns it. Takes a list of fields, with sorting being done in the order given. See DESC for sorting a field in decending order.
DESC
ORDER_BY 'foo', DESC 'bar';
Used with ORDER_BY to set a field to sort in decending order.
ORDER_BY
INNER_JOIN( $table, $field1, $field2 );
Creates a SQL::Functional::JoinClause with join_type = 'inner' and returns it. The first argument, ($table), is an SQL::Functional::TableClause object, which is the table being joined. The second argument is the field on the main table that will be checked. The third argument is the field on the joined table.
join_type = 'inner'
$table
SQL::Functional::TableClause
Alias for INNER_JOIN.
INNER_JOIN
LEFT_JOIN( $table, $field1, $field2 );
Creates a SQL::Functional::JoinClause with join_type = 'left' and returns it. The first argument, ($table), is an SQL::Functional::TableClause object, which is the table being joined. The second argument is the field on the main table that will be checked. The third argument is the field on the joined table.
join_type = 'left'
Alias for LEFT_JOIN.
LEFT_JOIN
RIGHT_JOIN( $table, $field1, $field2 );
Creates a SQL::Functional::JoinClause with join_type = 'right' and returns it. The first argument, ($table), is an SQL::Functional::TableClause object, which is the table being joined. The second argument is the field on the main table that will be checked. The third argument is the field on the joined table.
join_type = 'right'
Alias for RIGHT_JOIN.
RIGHT_JOIN
FULL_JOIN( $table, $field1, $field2 );
Creates a SQL::Functional::JoinClause with join_type = 'full' and returns it. The first argument, ($table), is an SQL::Functional::TableClause object, which is the table being joined. The second argument is the field on the main table that will be checked. The third argument is the field on the joined table.
join_type = 'full'
Alias for FULL_JOIN.
FULL_JOIN
Creates a SQL::Functional::SubSelectClause object and returns it. Takes the same arguments as SELECT, but returns the clause object rather than the SQL string and bind params.
Creates a SQL::Functional::AndClause and returns it. Takes a series of clauses (generally created by match/op), which will be joined with AND's. You can pass in as many clauses as you want, and even nest in OR clauses:
AND
OR
WHERE AND( match( 'bar', '=', 1 ), OR( match( 'baz', '=', 2 ), match( 'qux', '=', 3 ), ), match( 'foo', '=', 4 ), );
Creates a SQL::Functional::OrClause and returns it. Takes a series of clauses (generally created by match/op), which will be joined with OR's. You can pass in as many clauses as you want, and even nest in AND clauses:
WHERE OR( match( 'bar', '=', 1 ), AND( match( 'baz', '=', 2 ), match( 'qux', '=', 3 ), ), match( 'foo', '=', 4 ), );
Creates a SQL::Functional::ValuesCaluse and returns it. Takes an arrayref of values, which will become bind variables.
Creates a SQL::Functional::SetClause and returns it. Takes a list of SQL::Functional::MatchClause objects, which you can make with op (or match).
Creates a SQL::Functional::WrapClause and returns it. Takes a clause as an argument.
This is used when you need to wrap a portion of the SQL in parens. For instance, subqueries in a SELECT statement need this:
SELECT * FROM foo WHERE bar IN (SELECT id FROM bar);
Which you could build like this:
SELECT star, FROM( 'foo' ), WHERE match( 'bar', 'IN', wrap( SUBSELECT ['id'], FROM 'bar' ));
On the other hand, INSERT statements with subqueries don't take parens:
INSERT
INSERT INTO foo (bar) SELECT id from bar;
In which case you don't need to use wrap():
wrap()
INSERT INTO 'foo', [ 'bar' ], SUBSELECT ['id'], FROM( 'baz' ), WHERE match( 'qux', '=', 1 );
Creates a SQL::Functional::NullClause and returns it. Takes a field to check as being null.
Creates a SQL::Functional::NullClause and returns it. Takes a field to check as being not null.
Creates a SQL::Functional::LimitClause and returns it. Takes a number that limits the number of rows returned.
Creates a SQL::Functional::TruncateClause and returns it. Takes a table name.
Creates a SQL::Functional::DistinictClause and returns it. Takes a list of fields, which will be listed as DISTINCT rows in the SQL.
DISTINCT
Creates a SQL::Functional::FunctionClause and returns it. Takes a function name, followed by any parameters.
Creates a SQL::Functional::LiteralClause and returns it. Takes a string which will be put literally into the final SQL, rather than as a placeholder.
Creates a SQL::Functional::CountClause and returns it. Takes a set of fields, which could be passed as:
Scalar String -- for a single field
ArrayRef of Strings -- for several fields
An object that does SQL::Functional::FieldRole.
SQL::Functional can be easily extended for new SQL clauses using the SQL::Functional::Clause Moose role. See the documentation on that module to get started.
SQL::Functional
I should preface this section by saying that I'm not trying to insult the developers of SQL::Abstract or DBIx::Class. They've obviously worked hard to create successful and widely used libraries for a very common task. Perl is better for what they've accomplished. That said, I think they're stuck in an object-oriented way of thinking in a problem space that could be expressed more naturally with functions.
SQL::Abstract
DBIx::Class
Existing ways of making database calls fall into one of three approaches: direct string manipulation by hand, an object interface that outputs SQL (as in SQL::Abstract), or an object-relation mapper (DBIx::Class).
Direct string manipulation is fine when your database is almost trivial; just a few tables and straightforward relationships. The SQL::Abstract approach can handle slightly more complicated databases, but it tends to break down into esoteric, unintuitive syntax when things get really tough. Good object relational mappers can make some very complicated things easy, but there comes a point where you still need hand-optimized SQL.
What we end up with is that direct string manipulation is the way to go for both trivial and difficult cases. The middle ground is held by libraries that write the SQL for you.
If we look at SQL::Abstract's documentation (most of the examples below are directly copied from there), we see quite a few places where it's hamfisting the syntax in order to get increasingly complicated features to work. Here's an example of using direct SQL to set a date column:
my %where = ( date_entered => { '>' => \["to_date(?, 'MM/DD/YYYY')", "11/26/2008"] }, date_expires => { '<' => \"now()" } ); # Becomes: # WHERE date_entered > to_date(?, 'MM/DD/YYYY') AND date_expires < now() # With '11/26/2008' in the bind vars.
Why are we taking a reference to a scalar, or worse, a reference to an array reference? Quite simply because SQL::Abstract has to do everything in terms of arguments to methods on objects, and this is a way to twist Perl's syntax to get the result you want.
Switching between AND and OR operations gets complicated as well. You can do statements separated by AND like this:
my %where = ( user => 'nwiger', status => { '!=', 'completed', -not_like => 'pending%' } ); # Becomes: WHERE user = ? AND status != ? AND status NOT LIKE ?
Or separated by OR like this:
my %where = ( status => { '=', ['assigned', 'in-progress', 'pending'] } ); # Becomes: WHERE status = ? OR status = ? OR status = ?
So hashrefs give us AND and arrayrefs give us OR, which is already rather arbitrary. On top of that, we run into the problem of unique keys in hashes. That means the syntax can't be extended in the what would otherwise be the obvious way:
my %where = ( status => { '!=' => 2, '!=' => 1 } ); # Doesn't work, second '!=' clobbers the first
Instead, the syntax has to be further extended in less and less natural ways:
my %where = ( status => [ -and => {'!=', 2}, {'!=', 1} ]; );
At which point things start to look like an Abstract Syntax Tree. A point which reminds me of an old post from BrowserUK on Perlmonks:
LISP has virtually no syntax (aside from all the parens), so whan you write LISP code, you are essentially writing an AST (abstract syntax tree).
Which has stuck with me ever since I read it. We can represent Abstract Syntax Trees very naturally with functions. For instance, Lisp might handle the multiple AND statement like this:
(WHERE (AND (match 'status' '!=' 2) (match 'status' '!=' 1) ) )
Which looks at least vaguely like direct SQL, while avoiding some of the easy syntax errors and other cumbersome issues that you get with verbatim SQL strings.
With the right function definitions, we can get pretty close to the Lisp example in Perl:
WHERE AND( match( 'status', '!=', 2 ), match( 'status', '!=', 1 ), )
You could copy-and-paste that in an email to a DBA with only a short explanation of what's going on. It's not too far from the AST that an SQL parser might create internally.
As it happens, SQL::Functional uses a lot of objects to pass data around between functions. Objects aren't bad, they just aren't always the right tool. Objects and functions can be used in harmony, and it's wonderful that Perl allows you to do both without getting in your way.
DBI
Also, all the classes at SQL::Functional::*Clause.
SQL::Functional::*Clause
Copyright (c) 2016 Timm Murray All rights reserved.
Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:
* Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer. * Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.
THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
To install SQL::Functional, copy and paste the appropriate command in to your terminal.
cpanm
cpanm SQL::Functional
CPAN shell
perl -MCPAN -e shell install SQL::Functional
For more information on module installation, please visit the detailed CPAN module installation guide.