This year I’ve been learning about MySQL, PostgreSQL and Database security and optimization. One of the features I found useful was creating enumerated types. As they are a good solution for both efficiency and data consistency (only allowing one of the values in the enumeration) I’ve found myself using them in almost any case they fit.

But it wouldn’t be as easy (if it was that easy this post wouldn’t exist). I have been working with Ruby on Rails (an awesome framework to which I’ll devote a couple of posts someday) and its capacity to create the Database from scratch impressed me. But this capacity is also possible due to Active Record migrations. With just a simple command like this you can create the migration to rebuild your Database:

[code]rake db:schema:dump[/code]

But if you use enumerated types you’ll get an error message like this in your migration file:

[ruby]
# Could not dump table “activities” because of following StandardError
# Unknown type ‘activity_type’ for column ‘activity_type’
[/ruby]

Well, it doesn’t look good: Rails couldn’t dump an entire table just because one of the fields used an unknown type (an enumeration). One solution could be modifying each table that used an enumeration but it is a lot of work and we would also lose all the advantages of the enumerated values.

There is a better solution. Rails (3.2.12) allows you to retrieve the scheme in SQL format, rather than in Ruby code. To enable this you just have to edit config/application.rb and add the following line:

[ruby]
config.active_record.schema_format = :sql
[/ruby]

Next time you run db:schema:dump you’ll get a file named structure.sql. With this file Rails will rebuild the entire Database, including enumerated values, sequences and constraints.