[Openinteract-help] SPOPS template for SQL::Translator

[Ken Youens-C. <ky...@gm...>]

I've been playing around with SPOPS and liking it.  As part of the 
SQL::Translator group (http://sqlfairy.sourceforge.net/), I was 
interested in generating the configuration for SPOPS directly from my 
schema, which is fairly large (39 tables).  I came up with the template 
at the bottom of this message.  Saved to "spops.tt," I use it like so:

     $ mysqldump --no-data diversity19 > div19.sql
   	$ sqlt -f MySQL -t TTSchema --template spops.tt div19.sql > 
/opt/gdpdm/lib/GDPDM/Config.pm

And that produces something that looks like this:

package GDPDM::Config;

our $Config = {
     'cdv_allele_curated_allele' => {
         class           => 'GDPDM::CdvAlleleCuratedAllele',
         isa             => [
             qw/GDPDM::Datasource SPOPS::DBI::MySQL SPOPS::DBI/
         ],
         rules_from      => [ 'SPOPS::Tool::DBI::DiscoverField' ],
         code_class      => [],
         field_discover  => 'yes',
         base_table      => 'cdv_allele_curated_allele',
         id_field        => 'cdv_allele_curated_allele_id',
         increment_field => 1,
         no_insert       => ['cdv_allele_curated_allele_id'],
         no_update       => ['cdv_allele_curated_allele_id'],
         has_a           => {
             'GDPDM::CdvCuratedAllele' => 'cdv_curated_allele_id',
             'GDPDM::DivAllele' => 'div_allele_id',
             'GDPDM::CdvCuration' => 'cdv_curation_id',
         },
     },
     ...

Then in my code to use SPOPS, I have this:

	use lib '/opt/gdpdm/lib';
	use GDPDM::Config;
	Log::Log4perl::init( '/opt/gdpdm/spops/log4perl.conf' );
	SPOPS::Initialize->process( { config => $GDPDM::Config::Config } );

Is that essentially the Right Way to do things?

I'm cross-posting to the SQLT list just so people are aware of this.  
I'll probably add this as a "official" Producer to SQLT.

ky

[%
     MACRO case(n) n | lower | ucfirst;
     MACRO class(n)
     BLOCK;
         names = [];
         FOREACH part IN n.split('_');
             names.push( case( part ) );
         END;
         names.join('');
     END;

     SET tables = {};
     FOREACH table IN schema.get_tables;
         SET tname = table.name;
         SET fks   = [];
         FOREACH field IN table.get_fields;
             IF field.is_foreign_key;
                 SET fkref   = field.foreign_key_reference;
                 SET fktable = fkref.reference_table;
                 fks.push(
                     { fk => field.name, table => fktable }
                 );

                 UNLESS tables.${fktable}.links.defined;
                     tables.${fktable}.links = [];
                 END;

                 tables.${fktable}.links.push( tname );
             END;
             SET tables.${tname}.name = table.name;
             SET tables.${tname}.pk   = field.name IF 
field.is_primary_key;
         END;
         SET tables.${tname}.fks = fks;
     END;
     # USE dumper; dumper.dump(tables);
-%]
package GDPDM::Config;

our $Config = {
[%- FOREACH tname IN tables.keys.sort %]
     [%- table = tables.${tname}; NEXT UNLESS table.name; %]
     '[% table.name %]' => {
         class           => 'GDPDM::[% class( table.name ) %]',
         isa             => [
             qw/GDPDM::Datasource SPOPS::DBI::MySQL SPOPS::DBI/
         ],
         rules_from      => [ 'SPOPS::Tool::DBI::DiscoverField' ],
         code_class      => [],
         field_discover  => 'yes',
         base_table      => '[% table.name %]',
         id_field        => '[% table.pk %]',
         increment_field => 1,
         no_insert       => ['[% table.pk %]'],
         no_update       => ['[% table.pk %]'],
         [%- IF table.fks.size > 0 %]
         has_a           => {
         [%- FOREACH fk IN table.fks %]
             'GDPDM::[% class(fk.table) %]' => '[% fk.fk %]',
         [%- END %]
         },
         [%- END %]
         [%- IF table.links.size > 0 %]
         links_to        => {
         [%- FOREACH link IN table.links %]
             'GDPDM::[% class(link) %]' => '[% link %]',
         [%- END %]
         },
         [%- END %]
     },
[%- END %]
};

1;