NAME
SQL::Functional - Create SQL programmaticallySYNOPSIS
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 );DESCRIPTION
Builds SQL programmatically through a function-based interface.
EXPORTED FUNCTIONS
Generally, functions mapping to SQL keywords (like SELECT or FROM) are uppercase. Additional functions (like match and table) are lowercase.
Rawwrrr, why are you polluting my namespace?!!
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.
Top-level Functions
These return a list. The first element is the generated SQL. The rest are the bind params.
SELECT
INSERT
UPDATE
DELETE
Helper Functions
These are used to build the statement. Their return values eventually make their way to one of the top-level functions above.
star
A star, like the one you would use to say SELECT * FROM . . ..
field
Creates a SQL::Functional::FieldClause and returns it. You pass in the name of a field, like the names of columns..
col
Alias for field().
FROM
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;WHERE
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.
my $where_clause = WHERE AND(
match( 'bar', '=', 1 ),
OR(
match( 'baz', '=', 2 ),
match( 'qux', '=', 3 ),
),
);match
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.
op
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.
match_verbatim
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_idDon't know what's wrong with INNER JOIN, but some people prefer this syntax.
table
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:
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.
INTO
Alias for table. The wording here is better for INESRT statements.
ORDER_BY
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.
INNER_JOIN
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
Alias for INNER_JOIN.
LEFT_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.
LEFT_OUTER_JOIN
Alias for LEFT_JOIN.
RIGHT_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.
RIGHT_OUTER_JOIN
Alias for RIGHT_JOIN.
FULL_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.
FULL_OUTER_JOIN
Alias for FULL_JOIN.
SUBSELECT
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.
AND
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:
WHERE AND(
match( 'bar', '=', 1 ),
OR(
match( 'baz', '=', 2 ),
match( 'qux', '=', 3 ),
),
match( 'foo', '=', 4 ),
);OR
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 ),
);VALUES
Creates a SQL::Functional::ValuesCaluse and returns it. Takes an arrayref of values, which will become bind variables.
SET
Creates a SQL::Functional::SetClause and returns it. Takes a list of SQL::Functional::MatchClause objects, which you can make with op (or match).
wrap
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 INTO foo (bar) SELECT id from bar;In which case you don't need to use wrap():
INSERT INTO 'foo', [ 'bar' ],
SUBSELECT ['id'], FROM( 'baz' ), WHERE match( 'qux', '=', 1 );IS_NULL
Creates a SQL::Functional::NullClause and returns it. Takes a field to check as being null.
IS_NOT_NULL
Creates a SQL::Functional::NullClause and returns it. Takes a field to check as being not null.
LIMIT
Creates a SQL::Functional::LimitClause and returns it. Takes a number that limits the number of rows returned.
TRUNCATE
Creates a SQL::Functional::TruncateClause and returns it. Takes a table name.
DISTINCT
Creates a SQL::Functional::DistinictClause and returns it. Takes a list of fields, which will be listed as DISTINCT rows in the SQL.
func
Creates a SQL::Functional::FunctionClause and returns it. Takes a function name, followed by any parameters.
literal
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.
COUNT
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.
WRITING EXTENSIONS
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.
WHY ANOTHER WAY TO WRITE SQL?
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.
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 firstInstead, 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.
SEE ALSO
Also, all the classes at SQL::Functional::*Clause.
LICENSE
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.