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

[Peter Rabbitson]

This is just to record my vote, which I shared with Nigel already.

Nigel Metheringham wrote:
> 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?

Probably not, the -and/-or machine is stable (albeit weird) the way it is, and -nest
may get a facelift before sqla2.

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

This will _only_ work for column values, as you can not have references
as hash keys (thus literal functions are off the table).

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

I wouldn't do that, either one or the other.

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

My vote is for what's in the branch.