Wednesday, January 16, 2008

The Default Debacle

As I continue my study of RubyOnRails using Build Your Own Ruby On Rails Web Applications by Patrick Lenz, I'm including expansions on certain topics that piqued my interest. First, here are a few useful links if you're reading the book too.

Today I diverged from the examples in the book again and encountered an issue with the word 'default'. It turns out that default is a SQL keyword depending on the context of the expression it is used in. I wanted to store a default value for a record in a table. This is illustrated in the table below:


The table tracks the 'default' or usual state of each user and their current 'actual' state. This seemed simple enough. The add_column call in my migration file had no problem with the column name 'default'. However, when I tried to add items to the table, the column name 'default' caused an SQL error. This is because the word 'default' can have a special meaning in an insert query.

There may be a way to escape 'default' so that it's accepted as a column name, but I don't know what it is. (If you do, please comment! Thanks!) So, the simple path seemed to be to just rename the column. But... when I used rename_column, I also received SQL errors. The solution that finally worked was to first use remove_column to get rid of the column all together. Fortunately since insert didn't like 'default' there was no data in the column to worry about deleting. I then added the column default_value using the add_column call.

Just to be on the safe side, I did not implement the opposite of add_column in the self.down method of the migration file.

No comments: