[Dbix-class] Schema stuff [WAS: RFC: DBIx::Class 0.05 Schemas + Catalyst?]

[Alan Humphrey]

Brandon asked:

How are you doing it now, without a Catalyst::Model::DBIC-like thing,
and still getting at various schema objects from all of your
controllers?


Here's what I'm doing.  I'll start general and get into code later.  If
anyone sees better ways to accomplish the job, please chime in.  This
discussion is based on DBIx-Class 0.04001.

Background

I'm a volunteer for Seattle Audubon.  One of our websites is
http://www.birdweb.org.  For legacy reasons (i.e. it was that way when I
found it) it's an ASP site fronting a SQL Server database.

To allow edits to the data in the database we have an administrative site
(http://birdwebdev.dnsalias.org/BirdWeb-Admin/).  This is a Catalyst based
application fronting a MySQL database.

We want to allow the bird experts to edit and review data before committing
it to public view, hence the two databases.  The MySQL database is the
staging database, the SQL Server database is the production db.  The
structure of the databases is the same, the data may differ.

Edit pages have three submit buttons: Save, Publish, Cancel.  "Save" puts
the data in the staging database.  "Publish" saves to staging, then copies
the data to production.  "Cancel" abandons changes.

Directory Structure

The Catalyst Model directory structure looks like this:

BirdWeb-Admin/lib/BirdWeb/Admin/Model/BirdWebSchema/
                                      BirdWebSchema.pm
                                      Production.pm
                                      Staging.pm
                                      NoBindStorage.pm

NoBindStorage.pm isn't generally important, but I'll explain it below.

There is also

BirdWeb-Admin/lib/BirdWeb/Admin/Model/BirdWebSchema/Birds.pm
                                                    BirdingSites.pm
                                                    Genera.pm
                                                    Orders.pm
                                                    Etc.

In other words, the various class/table definitions used by DBIx Schema.

The Schema Module

BirdWebSchema.pm looks like this (some edits for size):

package BirdWeb::Admin::Model::BirdWebSchema;

use strict;
use base qw/DBIx::Class::Schema/;

__PACKAGE__->load_classes( qw/Birds Images Contributors Genera Families
                              Orders SpeciesNames Maps BirdCommonNames 
                              Sounds BirdAttributes Abundancies Attributes 
                              Ecoregions BirdSounds BreedsIn Quads Counties 
                              BreedingCodes BreedingMaps BirdingSites
	                        BirdingSitesEcoregions HabitatCodes 
		                  SiteHabitatsAtRisk BirdingSitesSources
Sources 
                              Urls SiteAttributes BirdImages/ );

1;

Note: this is not a complete mapping of the database.  There are some tables
that aren't used in the application yet and they aren't reflected here.
Every time a new table is used I have to create a new TableName.pm module
and reference it here.  That's it.  (Although I'm hoping changes to Loader
will make it possible to load the 'easy' tables without having to write a
.pm.)

The Database Modules

The Staging and Production modules look like this.

Staging:

package BirdWeb::Admin::Model::Staging;

use strict;
use BirdWeb::Admin::Model::BirdWebSchema;

my @conn_info = (
    'dbi:mysql:BirdWeb',
    'dbuser',
    'password',
    { RaiseError => 1, PrintError => 0, ShowErrorStatement => 1, TraceLevel
=> 0 }
);

BirdWeb::Admin::Model::BirdWebSchema->compose_connection( __PACKAGE__,
@conn_info );
1;

Production:

package BirdWeb::Admin::Model::Production;

use strict;
use BirdWeb::Admin::Model::BirdWebSchema;
use BirdWeb::Admin::Model::NoBindStorage;

my @conn_info = (
        'DBI:Sybase:server=seattleaudubon;database=seattleaudubondb',
    'dbuser',
    'password',
    { RaiseError => 1, PrintError => 0, ShowErrorStatement => 1, TraceLevel
=> 0 }
);

BirdWeb::Admin::Model::BirdWebSchema->compose_connection( __PACKAGE__,
@conn_info );

my $store = BirdWeb::Admin::Model::NoBindStorage->new();
$store->connect_info( \@conn_info );

my $classes = __PACKAGE__->classes();
foreach my $class (keys ( %{$classes} ) ) {
        ${$classes}{$class}->storage( $store );
}
1;

Note that both Staging and Production use BirdWebSchema.  The only
difference is in the connection data.  Also note that none of the modules
explicitly references Catalyst.

NoBindStorage

So what's going on with NoBindStorage?  I'm accessing the SQL Server
database via the FreeTDS library.  That library doesn't yet support bind
variables, so NoBindStorage overrides the _execute method to swap out the
bind variables "by hand".  The little loop at the bottom of Production was
the only way I could get the storage mechanism for each class updated.
Ugly, but it works.

Catalyst

When I fire up Catalyst it finds the Schema, Staging, and Production class
definitions.  Then, in my Controller code I can do this kind of thing:

   my $items = $c->model('Staging')->class('Birds')->search({},
			{
			 rows => 10,
			 order_by => 'taxonomic_order'
			});

Want the production data?

$c->model('Production')->class('Birds')->find( $id );

Non-Catalyst

Writing command line utilities is easy:

#!/usr/pkg/bin/perl -w

use strict;
use lib '/home/alanh/birdweb/trunk/BirdWeb-Admin/lib';
use BirdWeb::Admin::Model::Staging;
use BirdWeb::Admin::Model::Production;

my $staging_bird = BirdWeb::Admin::Model::Staging->find ( 234 );
my $production_bird = BirdWeb::Admin::Model::Production->find ( 234 );

foreach my $column ( $staging_bird->columns() ) {
	if ( $staging->get_column($column) ne 
           $production->get_column($column) ) {
		print "$column differs\n";
     }
}

That, at any rate, is how I'm doing it.  Suggestions/feedback welcome.

- Alan