Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid
 
PerlMonks  

RFC - DBIx::Class::Prototyper

by jZed (Prior)
on Jun 28, 2007 at 01:15 UTC ( #623778=perlmeditation: print w/ replies, xml ) Need Help??

This module lets you create, load, and populate on-the-fly DBIC schemas from SQL strings or files. It is a thin and evil wrapper around the most excellent SQL::Translator (castaway++, the sqlfairies++). Although you can use it to create permanent module files, it defaults to creating the DBIC classes in memory so when used with temporary tables, you can prototype a complex schema without any permanent file or database footprint other than a single script file that contains none of the usual DBIC class packages and relationship definitions. After all, if you define Foreign Keys in your SQL, why should you have to redefine relationships in DBIC? In that sense, it functions like DBIx::Class::Loader - but instead of automatically loading the schema from an existing database, it lets you automatically load it from SQL and create both the database and a DBIC strategy to access it on the fly.

I'm in an early stage so right now there's no pod, just a (fairly clear) short working example and the module itself which is only a bit longer than the example. I'd appreciate thoughts and suggestions on the usual questions: is it worth CPANing? namespace ok? interface ok? and whatever else you care to comment on.

./dbic-prototyper.pl

#!/usr/bin/perl use warnings; use strict; use lib qw(./lib); use DBIx::Class::Prototyper; # create & populate in-memory tables # create & load in-memory DBIC classes & relationships # my $loader = DBIx::Class::Prototyper->new( \*DATA ); my $schema = $loader->connect_and_execute( 'dbi:SQLite:' ); # test a DBIC three-table join # on success, create module files for the DBIC classes & relationships # if( 'Beat It' eq $schema->resultset ( 'Artist' )->find( {name=>'Michael Jackson'} )->get_Cd->search_related ( 'get_Track' )->next->title ){ print "ok\n"; $loader->save_schema_as_module( 'My::Music' => 'MyMusic.pm' ); } __DATA__ CREATE TEMPORARY TABLE Artist ( id INTEGER PRIMARY KEY, name TEXT NOT NULL ); CREATE TEMPORARY TABLE Cd ( id INTEGER PRIMARY KEY, artist_id INTEGER NOT NULL REFERENCES Artist(id), title TEXT NOT NULL ); CREATE TEMPORARY TABLE Track ( id INTEGER PRIMARY KEY, cd_id INTEGER NOT NULL REFERENCES Cd(id), title TEXT NOT NULL ); INSERT INTO artist VALUES (1,'Michael Jackson'); INSERT INTO cd VALUES (1,1,'Thriller'); INSERT INTO track VALUES (1,1,'Beat It');

./lib/DBIx/Class/Prototyper.pm

package DBIx::Class::Prototyper; use warnings; use strict; use SQL::Translator; use vars qw($VERSION); $VERSION = '0.01'; sub new { my($class,$sql,$sqlt_args) = @_; my $self = {}; bless $self, $class; $self->{package} = $self->_load_package($sql,($sqlt_args||{})); return $self; } sub connect_and_execute { my($self,@connect_args)=@_; my $schema = $self->{package}->connect( @connect_args ); $schema->storage->dbh->do($_) for( split /;\n/, $self->{sql} ); return $schema; } sub save_schema_as_module { my($self,$class_name,$file_name)=@_; my $str = $self->{dbic_str}; $str =~ s/My::Schema/$class_name/g; local *OUT; open( OUT, '>', $file_name ) or die "Error writing '$file_name': $ +!\n"; print OUT $str; } sub _load_package { my($self,$source,$sqlt_args)=@_; die 'No data or file supplied' unless $source; $sqlt_args->{from} ||= 'MySQL'; $sqlt_args->{to} = 'DBIx::Class::File'; my $tr = SQL::Translator->new( %$sqlt_args ); $self->{dbic_str} = $tr->translate( $source ); $self->{sql} = ${$tr->data}; eval $self->{dbic_str}; die $@ if $@; return 'My::Schema'; } 1; =pod =head1 NAME DBIx::Class::Prototyper; =head1 AUTHOR, COPYRIGHT, AND LICENSE This module is copyright 2007, by Jeff Zucker (jZed), all rights reser +ved. It may be freely used and distributed under the same terms as Perl its +elf. =head1 The Rest of the POD Sorry, nothing here yet, move along and enjoy! =cut

Comment on RFC - DBIx::Class::Prototyper
Select or Download Code
Re: RFC - DBIx::Class::Prototyper
by stonecolddevin (Vicar) on Jul 06, 2007 at 07:48 UTC

    Bravo jZed, works great from what you gave here. I'll have to play around with it and put it to practical use to solidify my approval of it, but i have no doubt it will perform :-)

    meh.
Re: RFC - DBIx::Class::Prototyper
by castaway (Parson) on Jul 10, 2007 at 11:58 UTC
    As mentioned on irc earlier, this would make a good DBIx::Class::Schema::Loader backend.

    Also, watch out for that splitting on ;\n, it makes quite a mess when you have comments in the SQL.

    C.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlmeditation [id://623778]
Approved by GrandFather
Front-paged by planetscape
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others wandering the Monastery: (5)
As of 2014-09-24 02:23 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (244 votes), past polls