Extending SQL::Abstract

Standard

Most of the perl I write currently has ties into DBIx::Class and hence uses SQL::Abstract.

I also have far too much of a preference for boolean items, which I normally encode in the database as a column of type boolean (or the SQLite vague equivalent).

Its been fairly easy to encode a test for boolean value being true with SQL::Abstract – although the syntax

column => \''

which maps to

WHERE column

is a little esoteric. However its downright close to impossible to produce the opposite test without using literal SQL (actually the first version uses literal SQL – except the SQL statement is NULL and comes after the column reference).

So today I have spent a little time extending SQL::Abstract to support the -bool and -not_bool unary operators which allow both positive and negative boolean tests to be encoded in a way that does not resort to literal SQL.

As part of this I refactored a part of SQL::Abstract so that further extensions of this type can be added more easily – adding a unary_ops extension to the constractor in the same way as the previously existing special_ops.

So now:-

-bool      => this_column,
-not_bool  => that_column

will map to

WHERE this_column AND NOT that_column

The code for this is currently sitting in a svn branch – please have a look and comment on this. My own criticism of it is that the syntax is clunky, but we are restricted to the perl datastructure mapping approach of the existing code (which is the real raison d’etre of SQL::Abstract).

5 thoughts on “Extending SQL::Abstract”

  1. dami

    Hi Nigel,

    The SQLA mantra is {col1 => , …}. So putting {-bool => col1} is reversing the logic. You could achieve what you want with

    my $sqla = SQL::Abstract->new(special_ops => [
    {regex => qr/bool/,
    handler => sub {
    my ($self, $field, $op, $arg) = @_;
    return ($arg ? $field : “NOT $field”);
    },
    },
    ]);

    Then $sqla->where({foo => {-bool => 1 }, bar => {-bool => 0}})

    will yield

    WHERE ( ( NOT bar AND foo ) )

    as desired

    • Nigel

      There were a couple of reasons for doing things this way: first is that the -bool => column syntax feels slightly more natural (although this is a matter of debate – neither form is particularly cuddly). The other, and more important reason, is that this is set up to allow a subquery to be used instead of just a single column name.

      The big advantage of your syntax is the ease of handling multiple column tests (my syntax needs an explicit -and to prevent the hash keys overwriting each other).

  2. The current latest version of SQL::Abstract on CPAN is 1.56 and is from May 30th, which was after this article was written. Is this -bool operator now in trunk *and* on CPAN or still only in your branch?

Leave a comment