Random Thoughts from Castaway

06 May

Shaving hairy SQLFairy yaks

written by Castaway

Now there's a fun title.

I stirred up some folks on the sqlfairy-developer list (well mostly
Ken), and as a result, I got co-maint rights on PAUSE. I also said I'd
look at the huge list of
Bugs and
have a go at at least maintaining it (checking if they are still
valid, etc. pp) - What have I gotten myself into?

But that's not all..

This particular piece of yak shaving came about because I started to
need to add fields to my stock Anything database, across the few
installs that I already have. (Yes, there is a fundamental
interconnectedness of all things (and guess what I've just been
reading?)). Anyway, the anooying thing with adding fields is that I
need to a) update the main sql, b) update the schema to know those
fields c) go apply an "ALTER TABLE" command to each of my databases
separately. Now clearly that gets more insane the more installs you

Luckily, folks in the #dbix-class channel have been pondering that the
DBIx::Class schema should be the source of all things.. So, well I
already wrote an SQL::Translator Parser for DBIx::Class a while back,
and sqlt has this handy little SQL diffing tool.. The next step was to
add functions to DBIx::Class' DBI storage module, to create an SQL
file for the current schema on demand, so I did.

Having done that, yesterday my brain finally stopped whirring around
the diffing problem, and decided how to implement that part as
well. sqlt-diff is the yak that needed shaving, its current incarnation
is a monolithic script which attempts to diff two schemas, and not too
badly from what I can see. I've extracted the actual diffing parts out
into a module, for easy re-use, and then, since that broke something,
I looked at the actual code.. Which had gotten its parameters all
reversed.. So I fixed that, and query-replace renamed all the $XX1,
$XX2 variables with $XX_src and $XX_tar, for much easier reading.

To cut a long story short, there is now a method in
DBIx::Class::Storage::DBI (at least in my copy ;), that when
passed a version (defaults to $VERSION in your schema), will create
SQL files from it. And when passed a version, and a previous version,
will create SQL files from the current version, AND output a file
which can alter the first into the second, via various ALTER TABLE

And for my next trick..

You'll have to wait until later.

Your captcha is:

Only the comment itself is required, but if you supply your email we'll gladly load your gravatar. We won't use your email for any other purpose or sell it to nigerian business men. Promise.
A live preview of your comment will appear in this box.