Chris@1295: class AddUniqueIndexOnCustomFieldsTrackers < ActiveRecord::Migration
Chris@1295:   def up
Chris@1295:     table_name = "#{CustomField.table_name_prefix}custom_fields_trackers#{CustomField.table_name_suffix}"
Chris@1295:     duplicates = CustomField.connection.select_rows("SELECT custom_field_id, tracker_id FROM #{table_name} GROUP BY custom_field_id, tracker_id HAVING COUNT(*) > 1")
Chris@1295:     duplicates.each do |custom_field_id, tracker_id|
Chris@1295:       # Removes duplicate rows
Chris@1295:       CustomField.connection.execute("DELETE FROM #{table_name} WHERE custom_field_id=#{custom_field_id} AND tracker_id=#{tracker_id}")
Chris@1295:       # And insert one
Chris@1295:       CustomField.connection.execute("INSERT INTO #{table_name} (custom_field_id, tracker_id) VALUES (#{custom_field_id}, #{tracker_id})")
Chris@1295:     end
Chris@1295: 
Chris@1295:     if index_exists? :custom_fields_trackers, [:custom_field_id, :tracker_id]
Chris@1295:       remove_index :custom_fields_trackers, [:custom_field_id, :tracker_id]
Chris@1295:     end
Chris@1295:     add_index :custom_fields_trackers, [:custom_field_id, :tracker_id], :unique => true
Chris@1295:   end
Chris@1295: 
Chris@1295:   def down
Chris@1295:     if index_exists? :custom_fields_trackers, [:custom_field_id, :tracker_id]
Chris@1295:       remove_index :custom_fields_trackers, [:custom_field_id, :tracker_id]
Chris@1295:     end
Chris@1295:     add_index :custom_fields_trackers, [:custom_field_id, :tracker_id]
Chris@1295:   end
Chris@1295: end