The other day at work I was doing some data mining for our business intelligence folks. They needed to get some stats from the last year of billing transactions, and the task came to me. I had to hack up a perl script to query the archived data to find a certain type of transaction. Easy enough, the only minor issue was that since the data set was both huge and old, the table were broken up by month.

My perl script looked something like this (cleaned up for brevity):

@months = qw( 200901 200902 200903 ); # up to 201004
$sql = "select * from BillingMONTH where resultCode=40";
foreach my $month( @months ) {
$sql_full = $sql;
$sql_full =~ s/MONTH/$month/; # change the table name for each record
$sth = $dbh->prepare($sql_full) or die;
$sth->execute();
[ ... ] # retrieve the data, save/process it
}

I started wondering if these sorts of quick and dirty, but non-trivial scripts were easily doable in Ruby using Active Record.

I set up a similar situation on my system, a mysql database with 3 tables and a couple of rows of garbage data in them.  I knew that I could get a database connection set up very fast by making a throwaway rails app, setting up the models in it, and then using script/console as my interface, but I’ve honestly never use activerecord in a scripting environment.

After some messing around I came up with this:

#!/usr/bin/ruby
require 'rubygems'
require 'active_record'
 
ActiveRecord::Base.establish_connection(
   :adapter => "mysql",
   :username => "root",
   :password => "password",
   :database => "test"
)
 
class Test < ActiveRecord::Base 
end 
 
%w( Archive201001 Archive201002 Archive201003 ).each do |tablename|
   res = Test.find( :all, :from => tablename, :conditions => "name like 'test%'" );
   puts "Total results from #{tablename}: #{res.size.to_s}"
   # [...] other awesome processing on the res object
end

Running it gives me:

alan@phoenix:~/code/ruby$ ./testdb.rb
Total results from Archive201001: 3
Total results from Archive201002: 6
Total results from Archive201003: 3

Whohoo!  The one thing I couldn’t figure out is how to get rid of having to create a sub-class of the ActiveRecord::Base class.  I technically can do it with:

ActiveRecord::Base.connection.execute("select * from #{tablename}")

But I don’t know how to get this into the “object oriented” version to use the :conditions and rder and such.  However, the main point of can I replace perl and DBI for quick and dirty database scripting has most definitively been answered.

Next step: seeing how to do the same thing in Data Mapper (to keep @dkubb happy)

When I first started learning Rails, my database setup and migrations looked something like this:

$ script/generate model Person name:string dob:date
$ rake db:migrate
[... oh crap, I guess I need login credentials too...]
$ script/generate migration add_username_and_password_to_person username:string password:string
$ rake db:migrate
[... better do it right, and make it properly salted like I should...]
$ script/generate migration add_salt_to_person salt:string
$ rake db:migrate
[... oops, there are a couple of other fields I need too...]
$ script/generate migration add_misc_to_person notes:text is_admin:boolean is_manager:boolean
$ rake db:migrate
[... no, need to do better role based management...]
$ script/generate migration remove_is_admin_and_is_manager_from_person
$ script/generate migration add_role_to_person role_id:integer
$ rake db:migrate

Ugh!

And that’s just to get the start of the project!  I’m sure I’m not the only one this has happened to as well.  The downsides are that at the end of the day you end up with a messy set of files in your db/migrate, it feels messy, and lets face it, it’ll make things harder to figure out when you go back and look at this in six months.

Someone suggested to me a while back to just use the initial migration file, and then going back and editing it, using db:rollback or db:reset to remove changes from your database (the latter would assume that right now there aren’t any other migrations you have to worry about).  You may need the migrations cheat sheet to get some of the subtle points, but the “thinking” process above would look something like:

$ script/generate model Person name:string dob:date
$ rake db:migrate
[...]
$ rake db:rollback # undo the last migration
$ vi db/migration/20100407161025_create_people.rb
$ rake db:migrate # re-run the migration
[...]
$ rake db:rollback
$ !vi # repeat the last vi command
$ rake db:migrate
[...]
$ rake db:rollback
etc....

At the end everything would be based off of that one migration file “20100407161025_create_people.rb”.  Note that you can find the current version of the scheme in the db/schema.rb file.

This isn’t perfect, as you may want to target a specific file or model.  IE: just keep on testing and rebuilding your Person model with new fields.  To do this you want to be able to find the target version to migrate up/down to.  You’d have to (I think anyway, please let me know if there’s a better way to do this) run the “rake db:migrate:down VERSION=xxx” (in this case xxx = 20100407161025, the number from the filename above) to delete anything in that file (in this case you’d want to have your full Person model defined) and then “rake db:migrate:up VERSION=xxx” to re-do that specific set of database additions (anything in the ‘up’ function).

Best part is that because you’re targeting a specific version and telling the system to up/down it, you aren’t resetting the version number the system knows the database is up to, so you can add other migrations after and not worry about issues running “rake db:migrate” after.  IE: The system will do The Right Thing.

Sadly it looks like you can’t just rename your migration file “001_people.rb” as the name of the file is closely matched to what’s inside.  With the 001_people filename it will complain about “uninitialized constant People” unless you change the class inside the file to match the “railsy” naming convention.  Check out the Ruby Guide on migrations for some more of the grisley details.

Things have been progressing nicely for me. As I said before, creating a new controller, action, model, or association has gone from scary black magic to “type it in without checking the syntax first”. Well, mostly anyway.

The new big challenge I’m having now is getting the data out from non-trivial (though not hugely complex) data model associations.  I have to find valid users’ games.  Clubs have Fields, Fields have Games, and games have an Agegroup (ie: peewee, junior, etc).  Users belong to a Club and have an Agegroup.  My challenge is to connect those two sets of associations so that basically I can say:

Give me all games with the same agegroup as a given user has, in the same club as the given user is in.

First I had to get all the associations setup, just simple “has_one”, “has_many”, and “belongs_to” in the models.  I got it to the point that I could do things like:

Club.first.fields.first.games

This lets me know that a) my models are set up right (and honestly I thought I had them set up until I came up with this example while typing this up and found it didn’t work, and had to add a missing “has_many” to my Field model).

The next step was IRB, in fact, living in IRB for a night.  Well, a bit more than a night actually.  This lets you do the code/test/results/try-again cycle way faster than editing a controller and reloading a webpage.  Man I wish we had this thing for my Perl programming with the Class::DBI ORM.

The next thing I found that’s been a big help was from this James Buck post on ActiveRecord logging.  It’s important to run this as the first command when you start up IRB, and it’ll display the SQL being executed, a huge help for seeing how things like :joins and :includes affect your queries.

Now don’t get me wrong, I can get the data out easily.  I can either hardcode the SQL and it’s ugly joins into the Model, or I can do it all in “pure” ActiveRecord with multiple calls and arrays (ie: get a list of fields, get a list of games from that, iterate through the list of games and grab the ones matching the age condition), but my real goal here (other than getting deeper into ActiveRecord of course) is to see if I can do this in “pure” ActiveRecord, in one line (must be the Perl programmer in me).

The secret it turns out, is reversing your thinking.  Instead of trying to figure out how the queries and relationships work “up” the chain, from the games, finding the fields they belong to, and if they belong to the club, I wondered why I couldn’t just get a list of all the games in a club.  This was the key.

I couldn’t relate a club to it’s games directly, because they belonged to an intermediate model, fields.  I had to relate a club to it’s games through another model.  So after adding this to the Club model:

has_many :games, :through => :fields

I could run

Club.first.games

and get a result, and from there it was an easy step to:

User.first.club.games.find_by_age_id(1)

Which is ugly, but when it’s put into “real” code it’ll look somewhat nicer, something along the lines of:


@user = User.find_by_id(params[:user])
@games = @user.club.games.find( :all, :conditions => { :age_id => @user.age_id } )


Oddly enough, “find.all( … )” doesn’t work, but that’s a battle for another day