Pensieri di un lunatico minore

Rails schema migrations

Rails has developed a bit more maturity now, and has the concept of “schema migrations” in the ActiveRecord component. I decided that rather than even try and “jump-start” my system with a normal initial schema, that I’d try and use the migration facilities. Some observations follow.

First, ActiveRecord::Migration does not implicitly wrap it’s DDL in a transaction as far as I can determine. This means that if anything goes wrong, you could leave your schema in an unusable state. For PostgreSQL at least, DDL can exist in a transaction, so I decided to wrap it up in: BEGIN and END so that if something goes wrong, things get rolled back. Not doing this by default is a bad idea¹.

Next, data types supported by ActiveRecord are woefully simplistic, at least for automatic schema creation. Your choices are:

• :string becomes a VARCHAR
• :text becomes a TEXT
• :integer becomes a INTEGER (32-bit signed)
• :float becomes a FLOAT (likely single-precision)
• :datetime becomes a TIMESTAMP
• :timestamp becomes a TIMESTAMP
• :time becomes a TIME
• :date becomes a DATE
• :binary becomes a BYTEA or BLOG

• :boolean becomes a BOOLEAN

  For those of us who depend on our databases not to make huge mistakes, this leaves out the NUMERIC fixed-point representation—for which the recommendation is to multiply by the decimal places and store it as a flat INTEGER, which is little more than a kludge. For me, especially, I use INET for storing IP addresses. The other “options,” such as they are, are to store it either as a string (useless for querying), or as a 32-bit packed number (using inet_ntoa or others), which requires using a NUMERIC column in standard SQL databases.

  In initial tests, INET, at least, comes back as a string, and so long as you hand the database a properly formatted string, it will “do the right thing,” but this makes me uncomfortable. The first thing is to wrap it in a custom class, but after that I’m contemplating patching up the ActiveRecord behavior to allow for proper behavior. Regardless, it basically requires that I do my migrations by simply using execute and raw SQL, which defeats a lot of the purpose of the migration system. While platform-neutral SQL might be interesting in some situations, such as widely distributed plug-ins, it is universally deplorable for in-house applications, or those distributed with their database.

  Overall, however, the migration system now works reasonably well, but a lot of the magic disappears if you care to work above the least common denominator. This is frustrating, but I understand how it might fit in with some people’s views of the Right Thing.

  ¹ I suspect this is another latent sloppiness inherited from the MySQL brain-damage. MySQL, at least as late as 4.1, doesn’t really integrate transactions and DDL, and never will outside the InnoDB engine. This means you can’t actually have atomic schema migrations. Bad juju if you ask me.

  This entry was posted at 12:06 am on 20 February 2006 and is filed under Ruby. You can follow any responses to this entry through the post-specific RSS 2.0 feed.

  20 February 2006 Michael Koziarski

  Hey there,

  Which Database adapter are you using? DDL and transactions aren’t integrated in mysql, oracle or DB2. Though they do seem to work in postgres. I’ll add support for transactional migrations to trunk.

  Numeric is another good point, I haven’t done financial apps with cents since switching to rails full time, but it’s definitely missing.

  Does the rest of AR behave for you with numeric columns?

  20 February 2006 petrilli

  Almost everything I do is on PostgreSQL. I do believe, however, that Oracle also allows DDL to be performed inside a transaction, but I could be wrong.

  NUMERIC seems to get treated as a Float, which is mega evil in the grand scheme of things. Right now, I’m actually not using NUMERIC in this app, since I’m using the INET type. This just gets treated as a String basically. Seems to work so far, but I haven’t tried using the strange searching pieces.

  I’m using the PostgreSQL C adapter, not the Ruby-native one. If it’s only supported by PostgreSQL, then it doesn’t make sense necessarily to integrate it into the trunk.

  20 February 2006 Michael Koziarski

  Most of the other databases seem to either commit the moment you issue DDL or just bark at you for trying but carry on anyway. (psql

  20 February 2006 petrilli

  That’s unfortunate, since being able to maintain DDL inside a transaction—thereby making sure you commit all your changes or none—is a huge benefit to making sure that the database stays in a stable state. The ability to migrate up and down is seriously degraded in its trustworthiness by the fact that you could get the database hung in strange situations.

  Ah well, I’m sure transactions are just a passing fad after all.

  Both comments and pings are currently closed.

  Name Email URL Thoughts