[Dbix-class] Re: SELECT .. FOR ... (and other db-specific clauses).

[Dami Laurent (PJ)]

> I've been looking for a way to do SELECT FOR UPDATE via DBIx::Class
 
Hi all,
 
A couple of months ago I had to implement a similar requirement for
DBIx::DataModel, and came to some conclusions that are probably worth
sharing with the DBIC community : 
 
1) The SELECT ... FOR ... syntax may have other variants than 'SHARE' or
'UPDATE'. For example I'm working with a database that understands
statements like "SELECT ... FOR READ ONLY" (for the curious : that
database is "Livelink Collection Server", formerly "Basis+"). So what
you put after the "FOR" should not be hardcoded, it should be
programmable by the client code.
 
2) This example shows a general problem with SQL::Abstract::select :
positional arguments are too limitative, because you can only express
select($columns, $table, $where). The solution in DBIx::DataModel was to
add an intermediate layer that can understand named arguments like
 
  select(-columns => ..., -where => ...., -for => "read only", etc)
 
Then these are parsed and translated into arguments suitable for
SQL::Abstract. But it would be much better if SQL::Abstract itself could
evolve into a new API with named arguments. I heard in Matt Trout's
podcast that  in a near future SQL::Abstract is going to be maintained
by the DBIC community : when that happens, adding a richer API could be
a nice evolution project.
 
3) Even positional arguments may not be sufficient : sometimes you need
to tweak the generated SQL. With Livelink Collection Server, I sometimes
need a "SELECT ... WHERE NATIVE ('some db-specific syntax')". To do
this, DBIx::DataModel has a "-postSQL" callback that can manipulate the
result of SQL::Abstract. I haven't seen such a feature in DBIC; but I'm
sure some clients would need it  (sorry if it's already there and I
missed it). Overriding the "select" method in subclasses is not enough,
because those details may vary from statement to statement.
 
Regards, L. Dami
 
 
 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20070704/2da24a28/attachment.htm