database integration through Ruby on Rails

Rails has the built in ability to connect to multiple databases.
Short story is, you define databases through the database.yml file, beyond development, test and production. You can define sets of development, test and production databases that connect to any of the supported DB clients provided with Rails.

There are ruby adaptors for all major databases. There’s even ODBC support provided to finish opening up the ability for the DBs to connect.

Couple this with the ability for an AR object to define its own database connection. Add the ability to define an inherited class for an AR model.. Add the ability to set the table name, primary key names to each model.

Finish it up with the rails console.

a smart DBA can really milk this

Updating a Rails 2.1.0 app to Rails 2.3.3

let’s see how this goes

my local env is a needlesly complicated windows 64 bit vista home edition. it all works so far with Apache and MySQL in the mix.

First thing to do. Go to environment.rb and

RAILS_GEM_VERSION = '2.3.3' unless defined? RAILS_GEM_VERSION

There; now lets see how things play out.


=> Booting Mongrel
=> Rails 2.3.3 application starting on http://127.0.0.1:3000
=> Call with -d to detach
=> Ctrl-C to shutdown server

!!! The bundled mysql.rb driver has been removed from Rails 2.2. Please install the mysql gem and try again: gem install mysql.

So I go on.. oh yeah no more ruby mysql gem. need to get it to work with c bidnings.. in windows vista ! the problem is mysql c bindings. in windows… I remember the problem showed up on Rails 2.2 had no time to deal with it then.

,, given that the mysql gem for windows only works for MySQL 5.0.27 … copying an older version .. lets see. Eureka. see this post for a fix if you have windows and trouble with MYSQL 5.1 http://forums.aptana.com/viewtopic.php?f=20&t=7563

Continuing, launch the app

uninitialized constant ApplicationController


What? Oh yeah, need to

rake rails:update

application controller is renamed. Yes, I felt that too sometime.

Ok, moving on

NoMethodError in MyControllerName#index where line #2 raised:
method `has_key?' for "mycontrollername/search":String


Error in a view.. Oh yeah, all the partials with render_partial to render :partial =>. Continuing..
HEY application came up images, javascripts all nice and clean. Things are looking up!

Now for deprecations.. only ONE? cool. update parameters for number_with_precision method.. and Done.

Ok, run tests now.

rake test

Hmm.. los of changes here. Ah, inherit from the new ActiveSupport::TestCase, ok, changing the test_helper…
, aok now. Continue.

Now to deploy.. Gotta have rails 2.3 on my public host.. Good thing that my app is version controlled via git and capistrano recipe makes backups and allows me to rollback to any prev version. Even better Phusion will allow us to mix rails versions without issues.

Ok got that done.. updated passenger to the latest version since only 2.2.2 onwards is compatible with rails 2.3.x

and now.. update Capistrano to the latest…

and
cap deploy...

deploy output is looking clean..

And now the terror comes true; major page break oh no..
look into it. darn, reset apache2 now what? Oh..
a renamed application_controller file didnt make it into my repository?????
Well.. ok, lets fix that.

We’re Baack..

We are now running on Rails 2.3.3 from Rails 2.1.0

I was down for a full total of 6 minutes , starting from not knowing what to expect to solving the problem.

Ok, now I gotta go catch my breath

[ a few hours later ]

Production crash on login.. Log in stopped working ! rack 1.0 error on request.rb:150
aw damn!!! look around sniff here and there.. the traces all indicate that there’s a bug with phusion 2.2.2 but I installed 2.2.4 what ?

ah, passenger had been originally configured to load using a different config file than indicated on the host documentation wiki page.. tsk tsk.
Problem ? Yeah, phusion 2.0.4 was loading instead of phusion 2.2.4. Well…

Ok, now made sure the mod is loaded only once.

BACK IN BUSINESS

[next day]

Everything is running smoothly

One quick tip for db performance on Rails

Make sure you add indexes to your migrations. Most of the time db performance problems are SQL related. Most of the time the SQL is fairly innocuous. A lot of the time though, in loading the objects the SQL will work best if the candidate keys on the many side of the equation are indexed columns. Create models through generators and either add the indexes to the added migrations or add new migrations to make sure that not only the id primary key indexes are created, but the supporting indexes (like user_id, tagging_id, rateable_asset_id) are also indexed.

Finally, note that cardinality and composition is important in indexes:
Given an index on column A, index on B, index on C
And a query which searches for conditions on A,B,C , then index A is being used most probably alone. If Index A is a low cardinality index (like a flag) then the query is going to scan the whole index which is just as bad as a full table read. The execution plan will say Index read but performance will be awful . B or C might be even faster choices than A, but by having query look at A and an index on A your query is going to go with the slow performing read.

So make sure your indexes are useful, if B gives better spread than A, and your query is asking for conditons on A,B,C then make your index a composite B,A,C order and the SQL will fly.

All of this of course should be transparent to the app side in rails. All of this can be handled via migrations, and will work equally well on all DB choices Rails provides.