[PATCH] Cannot rename columns if column name is reserved word. (MySQL)

rename columns if column name is reserved word in mysql

rename columns if column name is reserved word in mysql

To rename a column, that is a reserved word in MySql, is quoting the table, column old name and column new name. Another good practice is specify the table name before the column name.

# ALTER TABLE `configurations` CHANGE `configurations`.`group` `configurations`.`business_unit` varchar(255)

Edge release in /trunk/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb uses a method to quote the table name:

# /tags/rel_1-2-6/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb
def quote_table_name(name) #:nodoc:
  quote_column_name(name).gsub('.', '`.`')
end

So rails 1.2.6 can insert that method and change the method's implementation rename_column(table_name, column_name, new_column_name) to:

# /tags/rel_1-2-6/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb
def rename_column(table_name, column_name, new_column_name) #:nodoc:
  current_type = select_one("SHOW COLUMNS FROM #{table_name} LIKE '#{column_name}'")["Type"]
  execute "ALTER TABLE #{quote_table_name(table_name)} CHANGE #{quote_table_name(table_name)}.#{quote_column_name(column_name)} #{quote_table_name(table_name)}.#{quote_column_name(new_column_name)} #{current_type}"
end

Test it:

# /tags/rel_1-2-6/activerecord/test/migration_test.rb
def test_rename_column_with_reserved_word
  Person.delete_all      
  begin
    Person.connection.add_column "people", "group", :string
    Person.connection.rename_column "people", "group", "business_unit"
    Person.reset_column_information
    assert Person.column_names.include?("business_unit")
  ensure
    Person.connection.remove_column("people", "business_unit")
  end
end

This test fails on PostgreSql, so Rails 1.2.6 needs to quote the table and column name on ADD COLUMN too:

# /tags/rel_1-2-6/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb
def quote_table_name(name)
  %("#{name}")
end

and change the PostgreSQLAdapter add_column(table_name, column_name, type, options = {}) piece of implementation:

# Add the column.
execute("ALTER TABLE #{quote_table_name(table_name)} ADD COLUMN #{quote_column_name(column_name)} #{type_to_sql(type, options[:limit])}")

Real world test to Mysql and Postgresql:

class CreateConfigurations < ActiveRecord::Migration
  def self.up
    create_table :configurations do |t|
      t.column :name, :string
    end
    add_column :configurations, :group, :string
    rename_column :configurations, :group, :business_unit
  end

  def self.down    
    rename_column :configurations, :business_unit, :group
    remove_column :configurations, :group
    drop_table :configurations
  end
end

The last code example works without errors on rails trunk.