[Dbix-class] RE: SQL Generation with SQL::DB

[Mark Lawrence]

On Fri Sep 07, 2007 at 11:50:46AM +0200, Dami Laurent (PJ) wrote:

> somewhere else ... but so far, this is where people debate about ORM
> design, so let's stay here for the moment and thank the DBIC list for
> hosting this thread.

I should also say thanks to all for putting up with the noise. I didn't
intend to generate such a long thread.

> However, I can also think of several drawbacks :
> 
> 1) Perl operators are not enough to cover all possible SQL operators.
> For example :
>    - ... WHERE ... AND MATCH(column1, column2) AGAINST 'some_word'
>    - ... WHERE NATIVE (' column PH LIKE ''some_word'' ')

You are right, there are not enough operators, but I wouldn't want to go
mapping all kinds of operators to these terms anyway. The reason '&',
'|' and '!' make sense is because their meaning is close to what we
really want to say, and they are commonly used.

For some SQL words (such as LIKE and AS) it seems to be clean that they
are methods on the Expr objects instead of "=~":

    $cd->title->like('%string%');

However, similar to the example you give above, I have already needed the
COALESCE function. For this the following appeared to be suitable, and
will extend to any number of functions.

    use SQL::DB::Schema qw(coalesce native);
    ...
    my $query = $db->query(
        select => [coalesce($cd->c1, $cd->c2)->as('c3')],
        from   => $cd,
        where  => native( ... ),
    );

> database; the user will fill some of these search fields, but not all.
> Some fields may be multiple-valued (if the form element is a SELECT
> MULTIPLE, or a collection of checkboxes). Then you can write code like
> this:
> 
>   my %search_criteria;
>   foreach my $field ($form->param) { # iterate over fields in HTML form
>     my @vals = $form->param($field) or next; # skip this field if empty
>     $search_criteria{$field} = @vals > 1 ? {-in => \@vals} : $vals[0];
>   }
>   $sql_abstract->select($tables, \%criteria, ...);

    my $table = Table::Abstract->new();
    my $expr  = SQL::DB::Expr->new();

    foreach my $field ($form->param) {
        my @vals = $form->param($field) or next;
        $expr    = $expr & ( @vals > 1 ? $table->$field->in(@vals)
                                       : $table->$field == $vals[0] );
    }

    $sql_db->query(
        select => [],
        from   => $table,
        where  => $expr,
    );

However I've just thought of an optimisation. Since ->in() is a method,
it could in fact return "IN" for multiple values, and "=" for when
called with just one...

        $expr = $expr & $table->$field->in(@vals);

Actually SQL::Abstract could also do the same when it parses {-in => ...}
Should I post a patch?

> 3) risk of confusion when mixing with core operators, like in the
> following (contrived) example :
> 
>    ... where => $table->column < ($x < $y) ? $y : $y 
> 

Yes, that critism is valid. By the way, 'gt', 'lt', 'ge', 'le' etc are
also overloaded which may help to keep things clearer:

    ... where => $table->column gt ( $x < $y ? $y : $y )

> So ... I like it intellectually, but I'm not sure I would buy it for my
> projects. I have to look into it in more details, though.

Luckily I'm not selling it then :-) I think the concepts are different
to the current wisdom, so I certainly don't expect large interest. But I
do think it has some features that are worth looking into.

> Best regards, Laurent Dami

Thanks for your feedback.

Cheers,
Mark.
-- 
Mark Lawrence