Read my latest article: 8 things I look for in a Ruby on Rails app (posted Thu, 06 Jul 2017 16:59:00 GMT)

Rails Migrations and PostgreSQL Constraints

Posted by Fri, 11 Nov 2005 15:42:00 GMT

A question was posed on the Rails mailing list concerning how one would go about adding CONSTRAINTs to the database tables with ActiveRecord::Migration.

One argument was raised stating that it is easier to handle these in plain SQL schema files. I disagree. :-)

Migrations to the Rescue

Databases evolve and I have recently found the Migration structure to be perfect for handling iterations and schema changes. Using the #execute method has helped move more of my code into the Ruby/Rails framework… and that just makes things easier to manage in the long-run. This is the approach that we are using at PLANET ARGON with some of our current client projects.

# db/migrate/6_add_foreign_key.rb
class AddForeignKey < ActiveRecord::Migration
  def self.up
    execute "ALTER TABLE bees ADD CONSTRAINT beehive_id_fkey FOREIGN KEY
(beehive_id) REFERENCES beehives (id);"

  def self.down
    execute "ALTER TABLE bees DROP CONSTRAINT beehive_id_fkey;"

This gives us an easy way to use the standard, #create_table syntax for building our tables with Ruby… and then we can slap these constraints on later.

This would add the constraints…

rake migrate VERSION=6 tests…
...roll back…
rake migrate VERSION=5

I have found that this approach is really useful with testing in Rails. When I think that I have everything working great (without CONSTRAINTS in PostgreSQL), I run another migration to add a bunch of foreign key and data constraints to the tables and… run my tests again.

Let’s give Active Record a Hug

This has helped me gain some trust in Active Record while still giving me that comforting feeling that PostgreSQL is acting as the body guard for my data.

Even if you don’t end up using Migrations to handle these types of database schema changes, I would highly suggest that you model your implementation after this. I’ve worked with many database schemas and this just makes it easy to add your new change and run one command to commit it to the database.

...and now I go play with beehives…

Einstein on Rails

Posted by Fri, 04 Nov 2005 23:59:00 GMT

Einstein would like Active Record… I think…

Have a great weekend… !

Migrating from MySQL to PostgreSQL in 60 seconds (with Rails)

Posted by Sat, 29 Oct 2005 18:23:00 GMT

I do quite a few MySQL to PostgreSQL migrations for clients of PLANET ARGON and over the years I have gone through different methods of handling this procedure. Typically, it involved exporting data, re-importing it through some shell script that does its best to take into consideration the slight differences between the two databases.

While working on a recent project that was to be migrated from MySQL to PostgreSQL, but after some Refactoring had been done to the code base. I had been meaning to spend a few hours coming up with some simplified process of taking data from MySQL and replicating it to a new PostgreSQL database, while maintaining referential integrity.

My original idea was to build a script that referenced two seperate databases and then copied data out and inserted it into the new db after performing a few data changes. This seemed like too much work and I knew that I should be able to harness the power of Ruby and Rails in my process. My next thought? RailsFS.

In a perfect world, I would mount one instance of the application with a mysql database and another with a postgresql database… and just copy the yaml files to the postgresql and bam, it’d work, right? Well, it doesn’t quite work that way. So, I ruled out that idea.

Then I had another idea. I would build a task in my Rakefile that… imported each of those YAML files into PostgreSQL!

We can now type in the following commands:

This first creates my PostgreSQL database tables using Migrations.
rake migrate VERSION=1


rake mysql2pgsql
mysql2pgsql runs…and approx 60 seconds later, I had an exact copy of the MySQL database in PostgreSQL. But wait! PostgreSQL has SEQUENCES… so I then run:
rake migrate VERSION=2

...and my SEQUENCES are ALTER’d.

Yes, I will post some code in the near future. But all I am doing is basically running through all the YAML files that are available from RailsFS and adding them into the new database. It automatically figures out the model name…and is fairly generic at the moment. However, I’d like to spend a bit more time stress-testing it before I post it. :-)

I want to try to work on a RailsFS-less version as well so that I can run this on my PowerBook.

Until next time… have fun!

Using Named Placeholders in Ruby

Posted by Fri, 21 Oct 2005 04:53:00 GMT

2 comments Latest by Sokolov Yura Sun, 27 Aug 2006 07:34:26 GMT

Insert Hip Quote Here:

“In ancient times, hundreds of years before the dawn of history, an ancient race of people… the Druids. No one knows who they were or what they were doing… “ – Nigel Tufnel, Spinal Tap

Story Time…

Earlier, I was giving a customer of ours, Jared from a quick tutorial on some of the features script/console… which lead to helping him with a SQL query. When I provided him with some working code he was curious about what I had done in the SQL query string that I was passing to find_by_sql.


If you have ANY SQL queries that resembles the following, PLEASE READ THE REST OF THIS. :-)

values = params[:search]
RockLegend.find( :all, :conditions => "first_name = '#{values['first_name']}'" )

If you are doing that… then you are opening yourself up to some security problems. Let’s take a few minutes and discuss how you can make this more secure and still keep your code readable. (the best of both worlds!)

The ? Placeholder

Many of you are probably familiar with this approach…
RockLegend.find( :all, :conditions => ['first_name = ?', 'Nigel'] )

RockLegend.find( :all, :conditions => ['first_name = ? AND last_name = ?', 'Nigel', 'Tufnel'] )

You can pass it a hash as well.. and as long as you put everything in the same order as the ?s are placed… then all is well.

My only real problem with this approach is that it requires you to keep things in a specific sequential order… and who wants to keep track of that? So, I would like to recommend that you use named placeholders. Aside from that, it looks magical and I don’t like magical-looking code. I like easy to read code. :-)

Named Placeholders

If you already use these… you know how useful they can be in your SQL queries. If you haven’t seen them before… it’s because the Rails docs don’t really mention it and is something that comes from the underlying database library in Ruby. So what is so great about these?

Let’s first replace the above code with named parameters…
RockLegend.find( :all, :conditions => ['first_name = :first_name', { :first_name => 'Nigel' } ] )

We are passing a hash with a matching key to the conditions option. Pretty neat, right? In this case with just one placeholder we just increased the amount of code to do the same thing. So, it might always be the best solution… but it is easier to read.

Let’s try another with multiple keys in our hash… infact, we’ll build the hash prior to calling find.

values = { :first_name => 'Nigel', :last_name => 'Tufnel'}
RockLegend.find( :all, :conditions => ['first_name = :first_name AND last_name = :last_name', values ] )

It will happily match the hash keys to the named placeholders in the conditions string. Again, nothing terribly exciting…but it is easier to read.

Who cares about order? Not named placeholders!

Okay, let’s mix things up a bit…

values = { :last_name => 'Tufnel', :first_name => 'Nigel' }
RockLegend.find( :all, :conditions => ['first_name = :first_name AND last_name = :last_name', values ] )

The hash keys were not added in the same order… but it still works!

Okay, now for one last quick example (it’s late and I am tired…).

I have a search mechanism on a site that allows you to search for a string of text across multiple fields. So, I have one string… but several fields to compare against.

Here is a string that I will pass to the find method.
conditions = "role = :role AND (first_name ~* :str OR last_name ~* :str OR nick_name ~* :str)"

Note: this string is using PostgreSQL regular expressions... (~*).

Here is a hash that with that matches the keys, :str and :role

values = { :str => '^(Nigel|Tufnel)$', :role => 'Guitar' }

If you look above, you’ll see that the conditions string contains four named placeholders… but the hash only has two keys. With the ? placeholder, we would have to pass the same vaule three times… which isn’t any fun to read or maintain. ;-/

So, with our new friends, named parameters, we can call find (or any find-like method) using this technique for placeholders.

RockLegend.find( :all, :conditions => [ conditions, values ] )

...and hopefully this is useful to you. :-)

Have fun!

New Active Record Options for Associations

Posted by Wed, 19 Oct 2005 22:00:00 GMT

7 comments Latest by dfhsdfh Mon, 21 Aug 2006 09:51:08 GMT

Two months ago today I posted about a bug in Active Record. A bug that reminded me to remain cautious about how much trust I put into a database abstraction layer. I am happy to now say that this particular bug has been fixed, and I got to help! In the process, I also got to add some new features. (see my original rant, Active Record, I love U but I still trust my database server a tiny bit more.

I discovered this bug when I was working on a chapter in my book on Active Record. I’m known to gladly take advantage of database constraints and triggers, and it was when I decided to test my code without these constraints, I discovered the bug. “Hey, Active Record isn’t doing what it’s supposed to!” I’ve since had a number of people ask me if what a more pragmatic way to work around this issue is, rather than go my route by adding a constraint/trigger.

You can now DO AWAY with ON DELETE CASCADE! (sort of)

The new release of Rails 1.0 Release Candidate includes some new options for the has_many and has_one declarations.

Previously, you could do the following:

class Customer < ActiveRecord::Base
  has_many :orders, :dependent => true

This was supposed to nullify the dependent records, but it didn’t!

My patch not only fixes this, but also gives more control with what :dependent does. Now, you can pass the :dependent option to the has_many and has_one declarations with either :nullify or :destroy. This has a similar affect as ON DELETE CASCADE in those fancy RDBMs like PostgreSQL.

Let’s take a closer look at these new options:


A spider has_one web, and the web belongs_to one spider. If you destroy the spider, you would most likely want to destroy the web as well.

class Spider < ActiveRecord::Base
  has_one :web, :dependent => :destroy

On the other hand, in the case of a snail that has_one shell (and the one shell belongs_to the snail), if you destroy the snail, you may want to keep the shell. Remember, your crazy Aunt Ruby collects snail shells.

class Snail < ActiveRecord::Base
  has_one :shell, :dependent => :nullify

Now, for every snail we destroy, the shells, though once dependent on the snail, are now available for Aunt Ruby.


The same rules apply to the has_many association. Most people assume that if you destory a beehive that you would destroy all the bees. I’d like to think that they wander around until they find a new beehive to join. So, we can :nullify their relationship when the beehive is destroyed, thus making them homeless, but available for future hives.

class Beehive < ActiveRecord::Base
  has_many :bees, :dependent => :nullify

Sadly, some people might want to destroy the bees along with the destruction of the beehive. So, those people can pass the :dependent option, :destroy.

class Beehive < ActiveRecord::Base
  has_many :bees, :dependent => :destroy

Don’t worry, your usage of :dependent => true will now work, even though it wasn’t working before.

I’m going to try to put more trust into Active Record now, and I hope that this new addition to the library finds itself useful for you. :-)

Take a peak at the cool new features in the latest version of Rails and see the Active Record CHANGELOG for more information.


Rails meets FuseFS

Posted by Wed, 21 Sep 2005 19:28:00 GMT

_why has posted an entry on his blog which shows how to use the Ruby FuseFS library to… mount Active Record.

railsmnt]$ cat contact_type/1
--- !ruby/object:ContactType
  name: Client
  id: "1" 

 railsmnt]$ for i in `ls`; do echo $i; done

Umm… I am… I dunno… (wow)

I’ve used FuseFS for mounting SSH… but found myself using SSHFS as a better alternative.

But… to mount Rails? This could get interesting.


A screenshot while browsing the directory with Konqueror…and opening the record with Kedit.

UPDATE #2 If you’re hosting at PLANET ARGON, you can try this out with your account. Both, FUSE and Ruby-FuseFS are installed on all the servers.

Older posts: 1 2 3