[Catalyst] Catalyst and Oracle

[Andy Grundman]

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

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;