[Dbix-class] Oracle 8 w/DBIx::Class

[Igor Longagnani]

Hi Justin,
I still didnt have much time to test it properly but
after installing (that is copying)  the module "as was" under
Activestate Perl into C:\Perl\site\lib\DBIx\Class\Storage\DBI\Oracle8.pm
I had a problem with $self->_sql_maker_args at line 82/83  in method
sql_maker at every query (even with no JOIN)

|Caught exception in effenerg::Controller::elenco_evc->manage_options "Can't locate object method "_sql_maker_args" via package "DBIx::Class::Storage::DBI::Oracle8" at C:/Perl/site/lib/DBIx/Class/Storage/DBI/Oracle8.pm line 82."

|

# ------------------------------------------------------------------
sub sql_maker {
  # my ($self) = @_;
  my ($self) = @_;

  $DB::single = 1;

  unless ($self->_sql_maker) {
    $self->_sql_maker(
      new DBIC::SQL::Abstract::Oracle8( $self->_sql_maker_args )
    );
  }

  return $self->_sql_maker;
}
# ------------------------------------------------------------------


I found some workround  through SQL, views and so on so i still am not
using Ansi 99  JOINS,
but not through your code yet. I have some deadlines so i cant
concetrate properly on figuring out some proper solutions.

I will surely drill down this issue ... but unfortuantely a little later.

Thanks again coz your code gave me some possibility to learn more :)

Igor






Justin Wheeler ha scritto:
> Igor (and anyone else),
>
> Has anyone used this code, and did it work out for them?
>
> It works alright for me, but I want to be sure I haven't excluded or
> missed something in it before anyone goes about putting it in any dev
> branches, or I go writing test code for it.
>
> Thanks,
>
> Justin
>
> On Tue, 2 Jan 2007, Igor Longagnani wrote:
>
>> Many thanks Justin,
>> I needed it and I wrote some unpacked code to solve my "local" problem.
>> I will use thisclass...which is surely better code than mine
>>
>> Thanks again.
>> Igor
>>
>> Justin W ha scritto:
>>> Hi,
>>>
>>> I read that someone on this list was looking for Oracle 8 support in
>>> DBIx::Class.  I have needed the same thing, and ended up writing it.
>>> It is attached.
>>>
>>> It does not have a test suite, so you'll have to just use it, and hope
>>> it works out for you.  Anyone is welcome to write and contribute test
>>> code for it.
>>>
>>> #include
>>> <standard-disclaimer-about-not-knowing-dbic-internals-very-well-and-the-possibility-of-this-code-being-completely-wrong.h>
>>>
>>>
>>>
>>> To use it, in your base module, after:
>>>
>>> use base qw( DBIx::Class::Schema );
>>>
>>> Put:
>>>
>>> __PACKAGE__->storage_type('DBIx::Class::Storage::DBI::Oracle8');
>>>
>>> It should just work after that.
>>>
>>> Justin
>>> ------------------------------------------------------------------------
>>>
>>>
>>> package DBIx::Class::Storage::DBI::Oracle8;
>>>
>>> use base qw( DBIx::Class::Storage::DBI::Oracle );
>>>
>>> use strict;
>>> use warnings;
>>>
>>> BEGIN {
>>>   package DBIC::SQL::Abstract::Oracle8;
>>>
>>>   use base qw( DBIC::SQL::Abstract );
>>>
>>>   sub select {
>>>     my ($self, $table, $fields, $where, $order, @rest) = @_;
>>>
>>>     $self->_oracle_joins($where, @{ $table });
>>>
>>>     return $self->SUPER::select($table, $fields, $where, $order,
>>> @rest);
>>>   }
>>>
>>>   sub _recurse_from {
>>>     my ($self, $from, @join) = @_;
>>>
>>>     my @sqlf = $self->_make_as($from);
>>>
>>>     foreach my $j (@join) {
>>>       my ($to, $on) = @{ $j };
>>>
>>>       if (ref $to eq 'ARRAY') {
>>>         push (@sqlf, $self->_recurse_from(@{ $to }));
>>>       }
>>>       else {
>>>         push (@sqlf, $self->_make_as($to));
>>>       }
>>>     }
>>>
>>>     return join q{, }, @sqlf;
>>>   }
>>>
>>>   sub _oracle_joins {
>>>     my ($self, $where, $from, @join) = @_;
>>>
>>>     foreach my $j (@join) {
>>>       my ($to, $on) = @{ $j };
>>>
>>>       if (ref $to eq 'ARRAY') {
>>>         $self->_oracle_joins($where, @{ $to });
>>>       }
>>>
>>>       my $to_jt      = ref $to eq 'ARRAY' ? $to->[0] : $to;
>>>       my $left_join  = q{};
>>>       my $right_join = q{};
>>>
>>>       if (ref $to_jt eq 'HASH' and exists $to_jt->{-join_type}) {
>>>         #TODO: Support full outer joins -- this would happen much
>>> earlier in
>>>         #the sequence since oracle 8's full outer join syntax is best
>>>         #described as INSANE.
>>>         die "Can't handle full outer joins in Oracle 8 yet!\n"
>>>           if $to_jt->{-join_type} =~ /full/i;
>>>
>>>         $left_join  = q{(+)} if $to_jt->{-join_type} =~ /right/i
>>>                              && $to_jt->{-join_type} !~ /inner/i;
>>>
>>>         $right_join = q{(+)} if $to_jt->{-join_type} =~ /left/i
>>>                              && $to_jt->{-join_type} !~ /inner/i;
>>>       }
>>>
>>>       foreach my $lhs (keys %{ $on }) {
>>>         $where->{$lhs . $left_join} = \" = $on->{ $lhs }$right_join";
>>>       }
>>>     }
>>>   }
>>> }
>>>
>>> sub sql_maker {
>>>   my ($self) = @_;
>>>
>>>   unless ($self->_sql_maker) {
>>>     $self->_sql_maker(
>>>       new DBIC::SQL::Abstract::Oracle8( $self->_sql_maker_args )
>>>     );
>>>   }
>>>
>>>   return $self->_sql_maker;
>>> }
>>>
>>> 1;
>>>
>>> __END__
>>>
>>> =pod
>>>
>>> =head1 DBIx::Class::Storage::DBI::Oracle8
>>>
>>> =head2 Purpose
>>>
>>> This module's purpose is to provide DBIx::Class with some (rudimentary)
>>> Oracle 8 support, as Oracle does not support:
>>>
>>>     SELECT x FROM y JOIN z ON y.id = z.id
>>>
>>> Oracle requires the query by written as:
>>>
>>>     SELECT x FROM y, z WHERE y.id = z.id
>>>
>>> This module attempts to support that.  It should properly support
>>> left joins,
>>> and right joins.  Full outer joins are not possible due to the fact
>>> that
>>> Oracle 8 requires the entire query be written to union the results
>>> of a left
>>> and right join, and by the time this module is called to create the
>>> where
>>> query and table definition part of the sql query, it's already too
>>> late.
>>>
>>> =head2 Synopsis
>>>
>>> When initialising your code in the base DBIx module, simply tell
>>> DBIx to use
>>> this as a storage class, and you're set:
>>>
>>>     use base qw( DBIx::Class::Schema );
>>>
>>>     __PACKAGE__->storage_type('DBIx::Class::Storage::DBI::Oracle8');
>>>     __PACKAGE__->load_classes();
>>>
>>>     ... continue as normal.
>>>
>>> =head2 Methods
>>>
>>> This module replaces a subroutine contained in DBIC::SQL::Abstract:
>>>
>>> =over
>>>
>>> =item sql_maker
>>>
>>> =back
>>>
>>> It also creates a new module in its BEGIN { } block called
>>> DBIC::SQL::Abstract::Oracle8 which has the following methods:
>>>
>>> =over
>>>
>>> =item select ($\@$;$$@)
>>>
>>> Replaces DBIC::SQL::Abstract's select() method, which calls
>>> _oracle_joins()
>>> to modify the column and table list before calling SUPER::select().
>>>
>>> =item _recurse_from ($$\@)
>>>
>>> Recursive subroutine that builds the table list.
>>>
>>> =item _oracle_joins ($$$@)
>>>
>>> Creates the left/right relationship in the where query.
>>>
>>> =back
>>>
>>> =head2 Bugs
>>>
>>> Does not support full outer joins.
>>> Probably lots more.
>>>
>>> =head2 See Also
>>>
>>> =over
>>>
>>> =item L<DBIC::SQL::Abstract>
>>>
>>> =item L<DBIx::Class>
>>>
>>> =back
>>>
>>> =head2 Author
>>>
>>> Justin Wheeler <justin.wheeler at caledoncard.com>
>>>
>>> =head2 Licence
>>>
>>> This module is licensed under the same terms as Perl itself.
>>>
>>> =cut
>>>
>>>
>>> ------------------------------------------------------------------------
>>>
>>>
>>> _______________________________________________
>>> List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
>>> Wiki: http://dbix-class.shadowcatsystems.co.uk/
>>> IRC: irc.perl.org#dbix-class
>>> SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
>>> Searchable Archive:
>>> http://www.mail-archive.com/dbix-class@lists.rawmode.org/
>>
>>
>> -- 
>> Igor Longagnani c/o Synervis
>> -------------------------------------------------------------------
>> e-mail: i.longagnani at synervis.it             phone : +39 059 558442
>>
>> sede operativa: via Pirandello, 49/51 - 41043 Formigine (Mo) Italia
>>
>>
>
>


-- 
Igor Longagnani c/o Synervis
-------------------------------------------------------------------
e-mail: i.longagnani at synervis.it             phone : +39 059 558442

sede operativa: via Pirandello, 49/51 - 41043 Formigine (Mo) Italia