How to Add MySQL Full Text Indexes in Rails

Posted on January 10, 2008

Rails by default uses the InnoDB engine, but MySQL full-text indexing is only supported by the MyISAM table type.

So, first we’ll have to convert our target table over to MyISAM. Next we add the full-text index on several columns. 
class FullTextSearch < ActiveRecord::Migration
  def self.up
    execute 'ALTER TABLE torrents ENGINE = MyISAM'
    execute 'CREATE FULLTEXT INDEX ft_idx_torrents ON torrents(name,filename,description)'
  end

  def self.down
    execute 'ALTER TABLE torrents DROP INDEX ft_idx_torrents'
    execute 'ALTER TABLE torrents ENGINE = InnoDB'
  end
end

This is the actual migration used in The Hydra Project to add full-text searching support of torrents.

Tagged with: migrations  |

How to Convert MySQL Tables to MyISAM or InnoDB

Posted on January 10, 2008

If you have a InnoDB table that you’d like to convert to MyISAM:

class ConvertToMyIsam < ActiveRecord::Migration
  def self.up
    execute 'ALTER TABLE torrents ENGINE = MyISAM'
  end

  def self.down
    execute 'ALTER TABLE torrents ENGINE = InnoDB'
  end
end

You can flip the migrations for the reverse, of course.

Tagged with: migrations  |

Rails Migrations: Create HABTM Tables Without the 'id' Column

Posted on January 07, 2008

If you have a ‘has and belongs to many’ lookup table, chances are you don’t want it to have an ‘id’ field.

Unless otherwise specified, the Rails ‘create_table’ method in migrations automatically adds this field for you.

To force Rails not to do this, specify ”:id => false” in the declaration, as such:

    create_table :friends, :id => false, :force => true do |t|
      t.integer  :user_id
      t.integer  :friend_id
      t.datetime :created_at
    end

There are nicer has_many :through ways to do this, but drop this into your user.rb model for a friends list (using the above friends table schema):

class User < ActiveRecord::Base
  ...
  has_and_belongs_to_many :friends,
                          :class_name => 'User',
                          :join_table => 'friends',
                          :association_foreign_key => 'friend_id',
                          :foreign_key => 'user_id'
  ...
end

Tagged with: migrations  |