PostgreSQL specific data types in migrations

rubyonrails-talk
1

I am willing to give up the common denominator in database compatibility and dive into using some PostgreSQL specific types. In particular, I need bigint (int8) id columns and 'cidr' data type.

I know I can fake it with a string, but I really want to be able to use the cidr type's features. We're not talking about a small database here; it will likely be huge.

I managed to get a schema created using this:

class CreateProbes < ActiveRecord::Migration   def self.up     create_table :probes do |t|       t.column "target", :cidr       t.binary :flags       t.string :status, :limit => 1, :default => 'A'       t.timestamps     end   end

  def self.down     drop_table :probes   end end

which does SOME of it. However, db/schema.db contains:

ActiveRecord::Schema.define(:version => 20090121052017) do

  create_table "probes", :force => true do |t|     t.string "target", :limit => nil     t.binary "flags"     t.string "status", :limit => 1     t.datetime "created_at"     t.datetime "updated_at"   end

end

which is NOT what I wanted. This makes all my "rake test" tests fail.

I looked into part of activerecord, and man is it tricky. Trying to get this to work means major changes all over the place from what I can see. There aren't any database-specific hooks in place to allow adding custom types without a lot of monkey patching.

Has anyone already done all this work by chance?

Thanks, --Michael

2

Michael Graff wrote:

I am willing to give up the common denominator in database compatibility and dive into using some PostgreSQL specific types. In particular, I need bigint (int8) id columns and 'cidr' data type.

Can't help you on the cidr, but to change the id to bigint I've used

change_column :probes, :id, :bigint, :limit => 8

after the create_table block.

3

Thanks for the reply!

It sort of turns out that the development (and anything generated from a "rake migrate" step) work as intended. However, it seems that the test database is created by running db/schema.rb, which has badness.

I know the general work of acriverecord is to maintain as much database agnostic types as it can, but this is contrary to my reality. I choose to use postgresql because it has these features, and not being able to use them in rails makes my coworkers question the use of rails, not the use of postgresql.

I'll have to look into ways to contribute back I suppose, and see what happens.

Types like t.reference would have to change to bigint in my world as well, or I'll have to create the othertable_id columns as bigint specially.

--Michael