The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.
 / 
SQL-Functional-0.3
River stage zero No dependents
9 ++
/ SQL::Functional

NAME

  SQL::Functional - Create SQL programmatically

SYNOPSIS

    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_id

Don'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:

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

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.