enkidu

It's a snow day and time to play. I have a cow orker who prefers to write all the business logic in SQL because he hasn't moved on from what he learnt in the 80's. Nice guy, but I suspect he has a hearing deficiency because he never listens to what I have to say (or he's got a nasty case of NIH). Yesterday, he happened to be getting on my last nerve, but that no longer matters for I will have sweet, sweet Revenge.

Today I started playing with dbic.

It always takes me sometime to find the required mental space to try something new. Having too much on my plate means that I rarely take the time to explore the latest shiny, but I'm sitting here quietly staring at the screen contemplating how dbic is going to change my life and you, my brothers and sisters, are only 1 CPAN install and 2 command lines away from where I am because - Jesus H. Tap Dancing Christ - I've seen the light and I'm going to tell you about it.

I work with a huge, sprawling Oracle database that hasn't been designed - it grew. It consists of 158 tables with 11 primary keys and no foreign keys. How do I drag this morass into the 21st century?

First thing, install DBIx::Class::Schema::Loader from CPAN (you know how to do that). This gives you a life-giving script called dbicdump.
Create a temp directory and drop into it. Run this:

dbicdump Local::Schema dbi:Oracle:database_name username password

This interrogates your database schema and writes out a module for each table. For me it threw up a few exceptions about 3 ill-behaved tables and finished 5 minutes later. So, go ahead and do this now and make a brew. It's ok, I'll wait.

(No really - do it now and read the rest while you're waiting!)

You'll see all the modules with ls Local/Schema/Result/. Pick out your favourite table and column. You may notice that the module name is not quite the same as the table name. It took my underscore separated tables and turned them into camel case (my_table becomes MyTable). The only thing I'm going to do is get a list of all the values of the name column in that table. Run this on the command line:

perl -MLocal::Schema -E '$s = Local::Schema->connect("dbi:Oracle:database_name", "username", "password");

say $_->name() for $s->resultset("MyTable")->all()'

...

That's all you need to know to realize that the days of writing

$dbh = DBI->connect("dbi:Oracle:database_name", "username", "password"); 

$sth = $dbh->prepare("SELECT * FROM my_table" ); 

$sth->execute();

while ($hash_ref = $sth->fetchrow_hashref()) {

    print $hash_ref->{name};

}

Now, sit back and wonder at how little effort you've just expended to open up any frickin' database like an over-ripe peach and think about what you're going to do with this monster-on-a-lead, juice dribbling down your chin.

Me? I'm going to play around with dbic until I've got the killer query that requires contortions in SQL and then just drop it casually on my cow orker, in front of an audience. That'll fix his little red wagon!