The initial problem was 'How do I check multiple columns for uniqueness?'. That is, how do I ensure that for every row in my table, the n-tuple [col1, col2, ..., colN ] is unique (elipsis included to shorten the example), and have this enforced by the database? It turn out the answer is easy; just add an index like so (as part of a Rails DB migration), replacing the elipsis '...' as appropriate:
add_index :my_table, [ :col1, :col2, :col3, ..., :colN ], :unique => true
Easy! However, I found that my constraint didn't seem to be working:
describe MyTable do
it "should police uniqueness in DB" do
mt1 = nil
lambda {
mt1 = MyTable.create(:col1 => 1, :col2 => 2, ..., :colN => N)
mt2 = MyTable.create(:col1 => 1, :col2 => 2, ..., :colN => N)
}.should raise_error(ActiveRecord::StatementInvalid)
mt1.destroy
end
end
The Rspec case didn't pass; there was no exception raised, and the duplicate entry was being created. Looking in the logs, I found something suspicious:
Index name on table is too long; the limit is 64 characters. Skipping.
It turns out that the autogenerated DB index name (something like "by_#{columns.join('_and_')}") breached this 64-character limit, and was ignored, without the database migration failing!
Turn out the solution is to override the name explicitly to fit the 64-character limit:
add_index :my_table,
[ :col1, :col2, :col3, ..., :colN ],
:name => 'by_my_index_columns',
:unique => true
With this the index is added properly, and the uniqueness of the column-tuples worked as intended.
As an aside, I also had some fun adding a uniqueness index for a text column; it wouldn't let me! I had to add a migration first to convert the column type from :text to ':string, :limit => 64':
class AddListLabelUniqueness < ActiveRecord::Migration
def self.up
remove_index :list_labels, :label
change_column :list_labels, :label, :string, :limit => 64
add_index :list_labels, :label, :unique => true
add_index :list_label_instances,
[ :user_id, :list_id, :list_label_id ],
:name => 'by_list_label_indices',
:unique => true
end
def self.down
remove_index :list_label_instances, [ :user_id, :list_id, :list_label_id ]
remove_index :list_labels, :label
change_column :list_labels, :label, :text
#add_index :list_labels, :label
end
end
With that, I had the database ensuring that app races would not allow duplicate database rows to be created (with subsequent fun and bugs). Rails Model validations are good for a first line of defense, but if multiple requests are serviced in parallel, you need DB rules to protect data integrity.
- This was the first business book I read, shortly after ideas started sparking. It was an easy read, but got me thinking along the right lines at a very high level (people that would need to be involved, and how to test the business model for obvious flaws).
- This (and the rest of the Pragmatic Programmers books) are the standard reference for the Ruby on Rails web development framework. I devoured about a dozen of the books in this series and used the skills to try mocking up some of my ideas with quick and dirty prototypes. This really helped to refine the ideas and see what was important to focus on first.
- This one I read much later (over a year). I was still working for my old company, but the ideas had kept on ticking over, and I was thinking more seriously about whether I could make a business out of them. This book helped me do a much more critical analysis of the strengths, weaknesses and threats I would face, and how to actually write a plan for success.
- As mentioned in a previous post, this helped my wife and I do the initial design for the website. It covers basic principles to help you achieve a strong, effective look with good usability.
