[Dbix-class] SQL::Abstract - boolean test support - should this merge?

[Nigel Metheringham]

Boolean tests using SQLA (and hence DBIC) have always been a bit of a
pain - you can do a test for a true value using the hackish
     { boolcolumn => \'' }

which produces
     WHERE boolcolumn

but there has been no reasonable alternative that would allow you to
produce
     WHERE NOT boolcolumn


A few months back, I finally got around to building better support for
boolean tests in SQL::Abstract - you can see the branch at
     http://dev.catalyst.perl.org/svnweb/bast/browse/SQL-Abstract/1.x/branches/bool_operator/

This implements the following syntax:-
     { -bool => boolcolumn }
         WHERE boolcolumn

     { -not_bool => boolcolumn }
         WHERE NOT boolcolumn

     { -and => [-bool => 'foo', -bool => 'bar'] }
         WHERE foo AND bar

     { -bool => \'function(x)' }
         WHERE function(x)

This is slightly back to front compared to some other syntax, but
allows other syntax extensions - for example NOT of a subselect etc.

A side effect of this work is a significant re-factor of the SQLA code
handling unary (or possibly a better name would be prefix) operators,
which allows for subsequent expansion of those operators (currently the
operators are -and -or -nest).

So the big decision now is should this work be merged into the SQLA
mainline. That at least partly depends on whether ash, as master of
SQLA-v2 loves or hates it (no point adding syntax that he will prune).

If the -bool/-not_bool part is not merged, should the refactor be taken
in anyhow?

There is an alternative solution to the *basic* boolean SQL test, which
is to add it as a normal binary operation - which could be done like
this:-

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

and leads to syntax like this:-
     { foo => {-bool, 1}, bar => {-bool, 0}}
         WHERE foo AND NOT bar

This will have some issues (subselects and parameterised functions
etc), although literal functions should work.

It is quite possible to add both forms of syntax (although this may
confuse the hell out of users unless we change the naming of one of
them)

So comments and votes (but this ain't a democracy) are welcome on the
best way to take this forward...

     Nigel.

[Just to be clear - none of this should break existing code - the test
suite all still passes]

--
[ Nigel Metheringham             Nigel.Metheringham at InTechnology.com ]
[ - Comments in this message are my own and not ITO opinion/policy - ]