[Catalyst] Catalyst and Oracle

[Simon Miner]

Thanks for the code samples.

I tried setting this up as you suggested.  (I needed to install
Class::DBI::Sweet first.)  But now I'm getting this error.

Caught exception "UUID features not available at
/usr/local/lib/perl5/site_perl/5.8.3/Class/DBI/Sweet.pm line 590, <DATA>
line 1."

Any thoughts?  Here is my updated CDBI.pm and Page.pm code.  (I put the
sequence-setup code in the table-specific class as I would like to use a
different sequence for each table.)

package MiniMojo::M::CDBI;

use strict;
use base 'Class::DBI::Sweet';

__PACKAGE__->connection(
    "dbi:Oracle:$database",
    $schema,
    $pw,
    { LongReadLen => 65536, AutoCommit => 1 }
);

__PACKAGE__->sequence( 'page_seq' );
__PACKAGE__->set_sql( 'Nextval', 'select %s.nextval from dual' );

=head1 NAME

MiniMojo::M::CDBI - CDBI Model Component

=head1 SYNOPSIS

    Very simple to use

=head1 DESCRIPTION

Very nice component.

=head1 AUTHOR

Clever guy

=head1 LICENSE

This library is free software . You can redistribute it and/or modify it
under
the same terms as perl itself.

=cut

1;

-----

package MiniMojo::M::CDBI::Page;

use strict;
use base 'MiniMojo::M::CDBI';

__PACKAGE__->table( 'page' );
__PACKAGE__->sequence( 'page_seq' );
__PACKAGE__->set_sql( 'Nextval', 'select %s.nextval from dual' );
__PACKAGE__->sequence( 'page_seq' );
__PACKAGE__->columns( Primary => 'id' );
__PACKAGE__->columns( Essential => qw/title body/ );

=head1 NAME

MiniMojo::M::CDBI::Page - CDBI Model Component Table Class

=head1 SYNOPSIS

    Very simple to use

=head1 DESCRIPTION

Very nice component.

=head1 AUTHOR

Clever guy

=head1 LICENSE

This library is free software . You can redistribute it and/or modify it
under
the same terms as perl itself.

=cut

1;

Thanks again.

- Simon

-----Original Message-----
From: catalyst-bounces at lists.rawmode.org
[mailto:catalyst-bounces at lists.rawmode.org] On Behalf Of Andy Grundman
Sent: Monday, August 15, 2005 2:26 PM
To: The elegant MVC web framework
Subject: Re: [Catalyst] Catalyst and Oracle

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/Au
to/Oracle.pm

But what you really want to do is simply forget about Class::DBI::Loader

and define everything manually.  I've got 2 production Catalyst+Oracle 
systems running now and they run just fine with manually defined table 
classes.

Here's what our model classes look like for one of these apps:

package Events::M::CDBI;

use strict;
use base 'Class::DBI::Sweet';
use DateTime::Format::Strptime;
use Class::DBI::FromForm;

# For debugging, use this:
# $DBI::neat_maxlen = 2000;
# DBI->trace( 1, '/tmp/dbi.trace' );

# Get your $database, $schema, and $password values however you want

__PACKAGE__->connection(
     "dbi:Oracle:$database",
     $schema,
     $password,
     { LongReadLen => 65536, AutoCommit => 1 }
);

# cache all queries
# use this with care, on a fast server it will be a performance hit to 
have resultset_cache enabled globally

__PACKAGE__->cache( Events->cache );
__PACKAGE__->default_search_attributes( { use_resultset_cache => 1, 
profile_cache => 0 } );

# sequence support
__PACKAGE__->sequence( 'teched_events_seq_1' );
__PACKAGE__->set_sql( 'Nextval', 'SELECT %s.NEXTVAL from DUAL' );

# create all the date relationships
sub setup_date_fields {
     my $self = shift;
     foreach my $field ( $self->columns ) {
         if ( $field =~ /date$/i ) {
             $self->has_a(
                 $field => 'DateTime',
                 inflate => sub { $self->handle_date(shift) },
                 deflate => sub { shift->ymd },
             );
         }
     }
}

# handle dates, both from Oracle 16-JUN-05, and from the web YYYY-MM-DD
sub handle_date {
     my ( $self, $date ) = @_;

     if ($date =~ /\d{4}-\d{2}-\d{2}/) {
         return DateTime::Format::Strptime->new(
             pattern => '%Y-%m-%d',
             time_zone => 'America/New_York',
         )->parse_datetime( $date );
     } else {
         return DateTime::Format::Strptime->new(
             pattern => '%d-%b-%y',
             time_zone => 'America/New_York',
         )->parse_datetime( $date );
     }
}

1;

----

And here's one of the table classes.  Every table class follows the same

basic pattern.  Note the alter session code.  This changes Oracle to use

YYYY-MM-DD instead of the stupid DD-MON-YY format.  It's called in 
triggers because for whatever reason mod_perl connections don't seem to 
remember their date format.

package Events::M::Event;

use strict;
use base 'Events::M::CDBI';

__PACKAGE__->table('event');
__PACKAGE__->columns(Primary => qw/event_id/);
__PACKAGE__->columns(Essential => qw/title start_date end_date region_id
                                      location details_url comments 
enabled/);
__PACKAGE__->columns(Others => qw/create_date created_by modified_date 
modified_by/);

__PACKAGE__->setup_date_fields;

__PACKAGE__->has_a( region_id => 'Events::M::Region' );
__PACKAGE__->has_many( contacts => [ 'Events::M::EventContact' => 
'contact_id' ] );
__PACKAGE__->has_many( markets => [ 'Events::M::EventMarket' => 
'market_id' ] );
__PACKAGE__->has_many( product_groups => [ 
'Events::M::EventProductGroup' => 'product_group_id' ] );
__PACKAGE__->has_many( types => [ 'Events::M::EventType' => 'type_id' ]
);

__PACKAGE__->add_trigger( before_create => \&alter_session );
__PACKAGE__->add_trigger( before_update => \&alter_session );
__PACKAGE__->add_trigger( select => \&alter_session );

# Change the Oracle date format
# Every mod_perl process needs this to run at least once
sub alter_session {
     my $self = shift;

     # check the current nls_date_format
     my ($format) = $self->db_Main->selectrow_array( "SELECT value FROM 
sys.v_\$nls_parameters WHERE parameter = 'NLS_DATE_FORMAT'" );
     if ( $format ne "YYYY-MM-DD" ) {
         warn "NLS_DATE_FORMAT using incorrect value of $format, 
resetting to YYYY-MM-DD\n";
         $self->db_Main->do( 'alter session set nls_date_format = 
"YYYY-MM-DD"' );
     }
}

1;

_______________________________________________
Catalyst mailing list
Catalyst at lists.rawmode.org
http://lists.rawmode.org/mailman/listinfo/catalyst