{ height: 1%; } - Ruby on Rails and User Interface Design

On Rails Migrations Continued

Posted by Richard White Sat, 04 Feb 2006 02:06:00 GMT

Encountered another issue with migrations today when trying to do the following

add_column :appt_service_logins, :id, :primary_key

This threw an error when running rake migrate against my PostgreSQL DB that essentially boiled down to migrations trying to run the following SQL

ALTER TABLE appt_service_logins ADD id

Obviously missing the datatype for the column. I tracked this down to type_to_sql returning nil. This was because def native_database_types define a Hash where primary key does not map to another Hash with a :name key (see previous post). Therefore the following in def type_to_sql would return nil since it specifically looks for the value using the :name key.

def type_to_sql(type, limit = nil) #:nodoc:
  native = native_database_types[type]
  limit ||= native[:limit]
  column_type_sql = native[:name]
  column_type_sql << "(#{limit})" if limit
  column_type_sql  
end

To get around this I modified def type_to_sql to use the native value if column_type_sql was nil.

def type_to_sql(type, limit = nil) #:nodoc:
  native = native_database_types[type]
  limit ||= native[:limit] 
  column_type_sql = native[:name]
  column_type_sql << "(#{limit})" if limit      
  if column_type_sql
    column_type_sql
  else
    native
  end
end

I have no idea what the implications of such a change would be which is why I submitted this as a bug.

In the meantime since I was worried about the implications of changing def type_to_sql, I added a native database type of bigserial and am adding the column as a non-null bigserial and with a unique index.

Changes to postgre_extensions.rb:

def native_database_types
{
  :primary_key => "bigserial primary key",
  :string      => { :name => "character varying", :limit => 255 },
  :text        => { :name => "text" },
  :integer     => { :name => "integer" },
  :float       => { :name => "float" },
  :datetime    => { :name => "timestamp" },
  :timestamp   => { :name => "timestamp" },
  :time        => { :name => "time" },
  :date        => { :name => "date" },
  :binary      => { :name => "bytea" },
  :boolean     => { :name => "boolean" },
  :bigint      => { :name => "int8" },
  <strong>:bigserial   => { :name => "bigserial" }</strong>
}
end

In self.up in the migration file:

add_column :appt_service_logins, :id, :bigserial, :null => false
add_index :appt_service_logins, :id, :unique

To wrap up I attempted to put all of my changes into a Rails plugin so I wouldn’t have to put require ‘postgre_extensions’ at the top of each migration file as this fellow did but to no avail. I settled on just adding require ‘postgre_extensions’ to the bottom of my environment.rb. If you know of a better way to do this, let me know.

On Rails Migrations and PostgreSQL data types

Posted by Richard White Mon, 30 Jan 2006 02:06:00 GMT

ActiveRecord::Migrations for Ruby on Rails is nice and easy way to keep track of sql changes in a SQL server agnostic way. I won’t get much into the basics of migrations since others have already done a good job of that, but I will say that it has made a mid-project switch from MySQL to PostgreSQL as easy as it really should be.

The problem is of course that this database neutrality comes at a price and of course that price is in the form of a lack of precision when defining your schema. Since migrations supports [all sorts] of databases it also ends up only supporting the lowest common denominator of their abilites.

Case in point, on a current project I was trying to setup migrations from an existing schema. Unfortunately that schema included bigint(int8) id columns, and migrations only offers you :integer and :float in the way of numeric types. Fortunately getting around these problems is trivial in ruby, if you are willing to give up some of the database portability of migrations.

All that is required is to override the method in the PostgreSQL adapter that defines the mappings of data types in migrations to database data types and add a mapping for :bigint.

class ActiveRecord::ConnectionAdapters::PostgreSQLAdapter
 def native_database_types
 {
   <strong>:primary_key => "bigserial primary key",</strong>
   :string      => { :name => "character varying", :limit => 255 },
   :text        => { :name => "text" },
   :integer     => { :name => "integer" },
   :float       => { :name => "float" },
   :datetime    => { :name => "timestamp" },
   :timestamp   => { :name => "timestamp" },
   :time        => { :name => "time" },
   :date        => { :name => "date" },
   :binary      => { :name => "bytea" },
   :boolean     => { :name => "boolean" },
   <strong>:bigint      => { :name => "int8" }</strong>
 }
 end
end

Those of you that have looked at this method before will notice that I have also changed the :primary_key value from “serial primary key” to “bigserial primary key”. If you are familiar with PostgreSQL, or have just googled PostgreSQL datatypes like I did you will find that bigserial is the equivalent of a bigint with auto increment (or identity depending on where you come from).

Now all there is to do is to include the preceding code in each migration file. I simply created postgre_extensions.rb in the /lib directory of my rails app and added “require ‘postgre_extensions’” to the top of my migration files.