SQrbL: A gem to help with SQL-based database conversions
Sam Livingston-Gray
After Igal's presentation, I imagine that some of you have already
switched all of your development to use Tokyo Tyrant or some other
document-oriented database. Those people should probably stop
reading, because this gem is all about relational databases. (Also,
you might consider laying off the caffeine.) (=
In an ideal world, you'll have absolute control over your database
(schema and data) from the get-go. Sometimes, though, you get stuck
having to make sense of some ABSOLUTELY CRITICAL legacy data that
started life as an Excel spreadsheet. I've done a few of those in
recent months, and after the third or fourth one, I spent a couple of
hours writing some Ruby code to make my life a little easier.
Now, instead of managing a bunch of numbered *.sql files (and doing
'git rename' like mad when I inserted a new step 3 of 18 -- boy, was
that stupid), I stick chunks of SQL inside a pretty Ruby internal DSL,
check one Ruby file into Git, and use that to generate all of the
numbered *.sql files for me (one per logical step, plus one giant file
containing all the steps). Then, I can just paste their contents into
my SQL client, run them, and go.
I call it SQrbL, as in, "you got Ruby in my SQL." (An old friend used
to tell this joke about two junkies bumping into each other and
inventing crack: "You got baking soda in my cocaine." "You got
cocaine in my baking soda." "I know -- let's smoke it!")
Turned out, this approach made life *considerably* easier. In one
case, I was able to use 75 lines of Ruby to generate about 1100 lines
of extremely repetitive and annoying SQL. And, I went from "this is
the worst snafu in the history of the company" to "this is brilliant!"
...not that it saved my job. Upon suddenly finding myself unemployed,
I spent another 20 hours and turned it into a gem (using the Mr Bones
gem). To use it, just do '[sudo] gem install sqrbl'.
Links:
- RDoc: http://sqrbl.rubyforge.org/
- Source: http://github.com/geeksam/sqrbl/
- You can look at the prototype version of SQrbL on github (see
generate_sql.rb): http://github.com/rasantiago/scethics-civicrm/
Feedback would be extremely welcome. (Pull requests doubly so!)
And, of course, I'd be happy to talk about various aspects of this at
an upcoming meeting:
- designing a Ruby internal DSL -- I was particularly happy about a
method_missing hack
- deciphering how to use the 'bones' and 'rubyforge' gems
- showing off a horrible Excel-like schema whose ass I totally kicked
with SQrbL
- ???
Cheers,
-Sam