[Catalyst] Catalyst and Oracle

[Scottsweep]

David,
   Attached is the version of
Class::DBI::Loader::Oracle (v 0.02?) updated to handle
relationships. Sorry for the lack of diff/patch, but
my bandwidth just went way downhill. The only diff is
the addition of the _relationships method.
   The updated Class::DBI::Oracle is forthcoming (tied
up in some proprietary stuff right now, just need to
pull the weeds). Are any of the authors listed still
maintaining? I emailed Dan and Jay some months ago,
but don't recall that I got anything back...


Thanks,
Scott

--- Scottsweep <scottsweep at yahoo.com> wrote:

> David,
>    I do indeed have sequence discovery that does a
> little more checking (parses trigger code, etc)
> along
> with some other minor fixes (all_ v. user_ prefixes
> on
> the lookups) for setup_table. I'll put it together
> and
> post it. I'll try to get patches and tests together
> for both this and the relationships patch mentioned
> below.
> 
> Thank,
> Scott
> 
> --- David Naughton <naughton at umn.edu> wrote:
> 
> > On Mon, Aug 15, 2005 at 02:25:51PM -0400, Andy
> > Grundman wrote:
> > > Simon Miner wrote:
> > > >Can anyone tell me why the Page class is not
> > finding its sequence or 
> > > >using this code?  How can I code logic and
> > configuration specific to 
> > > >this table?
> > > 
> > > Class::DBI::Loader::Oracle doesn't contain any
> > sequence discovery code. 
> > >  A coworker of mine wrote some but it hasn't
> made
> > it into the 
> > > Loader::Oracle distribution yet.  You can see
> the
> > code as part of 
> > > DBIx::Class though:
> > >
> >
>
http://search.cpan.org/src/AGRUNDMA/DBIx-Class-0.02/lib/DBIx/Class/PK/Auto/Oracle.pm
> > 
> > When I first saw this message, I assumed you were
> > referring to the relationships
> > code that someone you know sent me...
> > 
> > <URL: http://rt.cpan.org/NoAuth/Bug.html?id=12730>
> > 
> > ...but you mention "sequence discovery code". Did
> > you really mean
> > "relationships", or did your coworker write some
> > sequence discovery
> > code too? If so, I don't think anyone has sent it
> to
> > me. Anyway,
> > Class::DBI::Loader::Oracle does have sequence
> > discovery code, since it
> > inherits from Class::DBI::Oracle, which provides
> > that functionality.
> > 
> > In any case, I apologize that I haven't done
> > anything with the
> > relationships code yet. I'm not using
> CDBI:L:Oracle
> > right now and am
> > extremely busy with other projects. Therefore,
> > evaluating and writing
> > tests for that code isn't a high priority for me
> > right now. If someone
> > submits some good tests as part of a proper patch,
> > I'll happily apply it. 
> > If lots of people really want this functionality,
> > let me know and I may
> > be convinced to re-prioritize it and do it sooner
> > myself. :)
> > 
> > David
> > 
> > P.S. Sorry for the cross-posting, but I thought
> > people on other lists
> > may be interested in this.
> > 
> 
> 
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam
> protection around 
> http://mail.yahoo.com 
> 
-------------- next part --------------
package Class::DBI::Loader::Oracle;

use warnings;
use strict;
use DBI;
use Carp;
require Class::DBI::Oracle;
use base 'Class::DBI::Loader::Generic';
use vars '$VERSION';
$VERSION = '0.02';

sub _db_class { return 'Class::DBI::Oracle' }

sub _tables {
    my $self = shift;

    my $user = uc $self->{_datasource}->[1];
    # handle user strings of the form user at sid or user/password at sid
    # we want only the user (schema) name
    $user =~ s/^(\w+)[@\/]?.*$/$1/;

    my $dbh = DBI->connect(@{$self->{_datasource}}) or croak($DBI::errstr);
    my @tables;
    for my $table ( $dbh->tables(undef, $user, '%', 'TABLE') ) { #catalog, schema, table, type
        my $quoter = $dbh->get_info(29);
        $table =~ s/$quoter//g;

        # remove "user." (schema) prefixes
        $table =~ s/\w+\.//;

        next if $table eq 'PLAN_TABLE';
        $table = lc $table;
        push @tables, $1
          if $table =~ /\A(\w+)\z/;
    }
    $dbh->disconnect;
    return @tables;
}

# Find and setup relationships
sub _relationships {
    my $self = shift;
    foreach my $table ( $self->tables ) {
        my $dbh = $self->find_class($table)->db_Main;
        my $user = uc $self->{_datasource}->[1];
        # handle user strings of the form user at sid or user/password at sid
        # we want only the user (schema) name
        $user =~ s/^(\w+)[@\/]?.*$/$1/;
                           
        if (my $sth = $dbh->foreign_key_info( undef, $user, undef
                                            , undef, $user, uc($table)) ) {                                                                                                                                                       
            for my $res ( @{ $sth->fetchall_arrayref( {} ) } ) {                              
                my $column = lc($res->{FK_COLUMN_NAME} || $res->{fk_column_name}); # these are usually lower case, depending on the DBI/Oracle driver
                my $other  = lc($res->{UK_TABLE_NAME} || $res->{uk_table_name});                                                  
                eval { $self->_has_a_many( $table, $column, $other ) };
                warn qq/has_a_many failed "$@"/ if $@ && $self->debug;
            }
        }
    }
}

=head1 NAME

Class::DBI::Loader::Oracle - Class::DBI::Loader Oracle Implementation.

=head1 SYNOPSIS

  use Class::DBI::Loader;

  # $loader is a Class::DBI::Loader::Oracle
  my $loader = Class::DBI::Loader->new(
    dsn       => $dsn,      # "dbi:Oracle:", "dbi:Oracle:DB", ...
    user      => $user,     # "user", "user at DB", "user/pass", ...
    password  => $password, # "pass", "", ...
    namespace => "Data",
  );
  my $class = $loader->find_class('film'); # $class => Data::Film
  my $obj = $class->retrieve(1);

=head1 DESCRIPTION

See L<Class::DBI::Loader>, L<Class::DBI::Loader::Generic>.

=head1 SEE ALSO

L<Class::DBI::Loader>, L<Class::DBI::Loader::Generic>

=head1 TODO

Add support for relationships. SSC - added to this non-standard distribution

=head1 BUGS

Please report any bugs or feature requests to
C<bug-class-dbi-loader-oracle at rt.cpan.org>, or through the web interface at
L<http://rt.cpan.org/NoAuth/ReportBug.html?Queue=Class-DBI-Loader-Oracle>.
I will be notified, and then you'll automatically be notified of progress on
your bug as I make changes.

=head1 ACKNOWLEDGEMENTS

Special thanks to Frank Carnovale and Ian VanDerPoel for sharing their code, upon which this module is based. Thanks also to Jay Strauss, Johan Lindstrom and Dan Sully for their helpful comments.

=head1 AUTHOR

David Naughton, C<< <naughton at umn.edu> >>

=head1 COPYRIGHT & LICENSE

Copyright 2005 David Naughton, All Rights Reserved.

This program is free software; you can redistribute it and/or modify it
under the same terms as Perl itself.

=cut

1; # End of Class::DBI::Loader::Oracle