[Dbix-class] Oracle 8 doesn't understand JOIN :(

[Matt S Trout]

On Tue, Feb 14, 2006 at 10:14:59AM +0000, Michael Gray wrote:
> Oh woe.  My next problem is that the rather elderly Oracle DB that I'm 
> working against does not seem to understand the modern SQL JOIN keyword.

Oh dear $deity, it's like MySQL 3.23 all over again.
 
> Thus, my schema:
> 
> __PACKAGE__->table('students');
> ...
> __PACKAGE__->belongs_to('personnel_key' => 'COMET::Schema::Person');
> 
> gives rise to
> 
>  SELECT me.student_id, me.personnel_key, ...
>  FROM students me  JOIN people personnel_key
>  ON ( personnel_key.personnel_key = me.personnel_key ) 
>  WHERE (student_id = ?)
> 
> which would in fact need to be expressed instead as the more traditional
> 
>  SELECT me.student_id, me.personnel_key, ...
>  FROM students me, people personnel_key
>  WHERE ( personnel_key.personnel_key = me.personnel_key )
>    AND (student_id = ?)
>
> It looks as if Storage::DBI::_recurse_from() is where the join action 
> is, but even if I passed a new pseudo_join_type of, say, 
> _SIMULATE_JOIN_, it's not clear to me how I'd add the join condition to 
> the statement's WHERE clause.

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:6585774577187

Yep, cos that's a great way to do LEFT JOINs as well.

That _recurse_from method isn't a Storage::DBI method, it's part of the
inline SQL::Abstract::Limit subclass that smooths away much of the evil of
making S::A and S::A::L work usefully. You *do* have all the information you
need, you'd just need to do something mildly insane to get the info passed
across so it ends up in the WHERE clause instead.

Best way would probably be to subclass that and then set $storage->sql_maker
to the modified generator (the embedded S::A subclass I did for CDBI::Sweet
may be a guide here since that does put the joins in the where clause :).

I'd love to see this available to people, but I can't really justify
hacking it into the core for the few old databases that don't understand
ANSI JOINs.

-- 
     Matt S Trout       Offering custom development, consultancy and support
  Technical Director    contracts for Catalyst, DBIx::Class and BAST. Contact
Shadowcat Systems Ltd.  mst (at) shadowcatsystems.co.uk for more information

 + Help us build a better perl ORM: http://dbix-class.shadowcatsystems.co.uk/ +