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

Dami Laurent (PJ) laurent.dami at justice.ge.ch
Fri Sep 7 10:50:46 GMT 2007

Hi all,

I have followed with interest the discussion on the new SQL::DB module
by Mark Lawrence, and would like to comment a few points. 

First, just to make clear from where I speak, I'm not a user of
DBIx::Class, but author of another ORM named DBIx::DataModel, that also
relies on SQL::Abstract. So maybe this discussion should take place
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.

Mark, I totally agree that SQL::Abstract is sometimes difficult to use
when formulating complex queries, especially when subqueries are
involved. Above all, the use of positional arguments instead of named
arguments is problematic because, as you mention, there is no room to
add other, optional arguments for further fine tuning of the generated
SQL. Matt Trout mentioned a couple of weeks ago in this list a project
to improve SQL::Abstract, but as far as I know this hasn't started yet. 

The idea of SQL::DB to use Perl operator overloading is very cute, and
could indeed probably yield more readable expressions in many cases.
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'' ')

2) If the collection of criteria is not known at compile-time, then Perl
operators may be awkward to use. In such cases SQL::Abstract is really
great for building a dynamic data structure and generate your SQL from
it. For example, let's assume a complex HTML form for searching a
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

  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, ...);

3) risk of confusion when mixing with core operators, like in the
following (contrived) example :

   ... where => $table->column < ($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.

Best regards, Laurent Dami

More information about the DBIx-Class mailing list