Posted by Scott Laird
Sat, 10 Dec 2005 18:14:12 GMT
I just released a new version of my Rails Schema Generator on Rubyforge. The schema generator takes a collection of Rails database migration scripts and assembles a complete set of SQL schema files using only the information from the migrations. This release supports MySQL, PostgreSQL, and SQLite; it can generate schemas for all three DB types even if the databases aren’t installed on the system.
We’ve found that the schema generator drastically lowers the work needed to keep Typo working correctly with multiple database types.
This release fixes a number of bugs and should finally work correctly with all common migration operations, including field renaming. This has only been tested with Rails 0.14.4, and at least one API has changed recently, so this may not work with earlier 0.14.x releases.
To use the schema generator, run gem install schema_generator, and then (from your Rails project directory) ./script/generate schema. This will create several schema files in the db/ directory, prompting you before overwriting existing files.
Tags database, ruby, rubyonrails, schema, typo | 4 comments | no trackbacks
Posted by Scott Laird
Mon, 31 Oct 2005 16:32:42 GMT
This seems to be the season for talking about Rails migrations. A lot of people are finally discovering them and finding that they’re very useful for maintaining your database schema over time. I’m a big fan of Rails migrations; we’ve been using them with Typo since the middle of July, when they were all new and shiny. We’re currently up to 24 migrations in the Typo source tree. We’re even using migrations to create our initial database, via my Schema Generator. I haven’t done a formal survey, but I suspect that Typo is the biggest open-source user of migrations, and may actually be the biggest user overall.
The big problem is that we’ve been using migrations wrong the whole time, and we just realized it.
There are probably a dozen bugs in Typo’s bug tracker that boil down to “I fell behind the trunk and now rake migrate throws exceptions and I can’t upgrade anymore.” The problem is that migrations are designed to run against an earlier version of your database, but they use the current version of your code. The first time that this caused problems was with the migration from Typo 2.0 to 2.5–we’d added two new fields to articles. Migration number 7 added the permalink field and a before_save hook to make sure that all saved articles have permalinks. Then migration number 9 added GUIDs and a second before_save hook to fill the guid field. Both migrations did Articles.find(:all).each { |a| a.save } to update each Article and populate the new fields.
This worked great for developers who frequently upgraded. A few days after the GUID migration went in, though, we started getting weird bug reports–users who tried to do both upgrades at the same time found that migration number 7 was dying. What was happening was that migration number 7 added the new permalink field to articles, but when it went to run the save loop both before_save hooks ran, and Typo tried to add a GUID to each article. However, the guid field didn’t exist yet, so the migration threw a bunch of exceptions and died.
This caused a bunch of grumbling on the Typo IRC channel. We threw around a bunch of possible fixes. Our favorite was separating migrations into two parts–a schema change part and a data change part. First we’d run all of the schema changes, and then update all of the data. As a work-around, we added a hack that checked the current schema version and disabled specific before_save filters for older versions.
We managed to keep this little bandaid working until a couple weeks ago, when a huge set of new migrations went it; they renamed the articles table and merged several other tables into the new contents table using STI. And, again, we found that older migrations broke when users tried to upgrade from Typo 2.5.6 to the current dev tree. Unlink the permalink/guid case, this time there was no simple workaround. We couldn’t just add a couple if statements in a filter and make it all go away.
The fundamental problem is that we were using the wrong mental model for migrations. I saw migrations as a one-dimensional thing–a list of steps for migrating old data into the new format. In this view, the migration for going from schema version 6 to schema version 7 is constant–once it’s been written, the only reason to change it is if a bug turns up in the logic for that migration. Otherwise, the migration code should remain unchanged over time.
And that’s the problem–migrations aren’t one-dimensional. They are (and need to be) two dimensional–the schema version is one dimension and the code version is the other. Individual migrations exist to migrate from a specific old schema version to the current version, using the current code. Each migration should change over time to adapt to the changes in the code. So, the right fix for the permalink migration that caused so many problems wasn’t to add a bunch of logic to before_save. Instead, we should have deleted the entire save loop from the migration, and trusted the GUID migration to update both fields. If that wasn’t good enough, then we should have added a new migration at the end to do permalink cleanup after the GUIDs were added.
Once I came to grips with this, the migration changes needed to allow 2.5.x users to upgrade to the current trunk were pretty simple, and took about 5 minutes to write and test.
Or was I the only person in the Rails universe who thought about migrations this way?
Tags database, migrations, ruby, rubyonrails, typo | 5 comments
Posted by Scott Laird
Mon, 24 Oct 2005 17:25:30 GMT
I just uploaded version 0.2.0 of my Rails Schema Generator to Rubyforge. This is a minor update, but it was needed to make the schema generator work with Rails 1.0rc2 (AKA 0.14.1).
The schema generator is sort of the flip side of the new schema code in Rails 1.0. It takes a set of Rails migrations, aggregates them all together, and spits out a SQL file that describes the DB that you’d get if you ran all of the migrations. Or, viewed in a more useful light, it gives you a SQL file that you can use to create a new DB from scratch. The current version actually produces three different schema files, one for PostgreSQL, one for MySQL, and one for SQLite, each with DB-appropriate syntax and types.
This is an outgrowth of Typo; we’re up to 25 migrations now, and we actively support 3 different DBs. It was getting really painful to maintain 3 distinct schema files in addition to the collection of migrations, so I wrote this schema generator. Now we’re back to DRY-land–we create new migrations and let the schema generator do all of the hard work.
Tags database, generator, ruby, rubyonrails, schema, schemagenerator | no comments
Posted by Scott Laird
Sat, 03 Sep 2005 08:09:00 GMT
I just uploaded the first version of my schema generator to rubyforge. This is a Rails generator that knows how to take a collection of migration scripts and use them to build up a valid SQL schema file.
You should be able to install it via gem install schema_generator, and run it on any Rails project by running ./script/generate schema from the root of the Rails project. The current release (0.1.0) supports MySQL, PostgreSQL, and SQLite. It will auto-generate a schema for each DB in db/schema.DBTYPE.sql every time it runs, prompting you before overwriting existing files.
For this to work, your Rails migrations must describe your complete database schema. Many projects, like Typo, are older then Rails’s migration support, so their migrations don’t start with a clean slate; instead they describe how to migrate from a specific old version of the DB schema to the current version. In this case, either create a 0_initial_schema migration or to modify the existing migration #1 to create all of the original tables. I just committed an example to Typo’s subversion tree, feel free to use it as an example.
Read more...
Tags database, generators, rails, ruby, rubyonrails, schema
Posted by Scott Laird
Fri, 02 Sep 2005 22:56:00 GMT
My Rails Schema Generator is nearly complete. Here’s a sample run:
$ ./script/generate schema
Found 6 migration classes
Starting migration for AddSidebars
Starting migration for AddCacheTable
Starting migration for AddPages
Starting migration for AddPageTitle
Starting migration for AddTags
Starting migration for AddTextfilters
Adding TextFilters table
Migrations complete.
Tables found: 6
Indexes found: 1
Records found: 8
exists db
overwrite db/schema.postgresql.sql? [Ynaq] y
force db/schema.postgresql.sql
overwrite db/schema.mysql.sql? [Ynaq] y
force db/schema.mysql.sql
overwrite db/schema.sqlite.sql? [Ynaq] y
force db/schema.sqlite.sql
The migration classes that I’m using are copied straight from Typo without modification. I’ve left out all of the migrations that add features to “legacy” tables–tables like articles–since there isn’t a table definition that I can use. That’s my next project–adding a 0_initial_schema migration for Typo. Once that’s complete, I have a bit of code cleanup and then I’ll release my schema generator code to the world. Hopefully that’ll be later today.
Tags database, generators, rails, ruby, rubyonrails, schema, typo | no comments
Posted by Scott Laird
Thu, 01 Sep 2005 08:14:00 GMT
One of the things that has really bugged me with recent Typo development is the pain of maintaining 3 different database schema files (PostgreSQL, MySQL, SQLite) along with a set of Rails DB migration scripts. Every time we add a new table, we have to edit 4 different files, even though all of the information that we need is available in the migration file. Unfortunately, without the static schemas, new users would be adrift, so we’re stuck having to hand-modify each of the static schema files. This violates the DRY principle, causes errors, and irritates developers.
So I figured I’d fix it by writing some code that can take a set of Rails DB migrations, fold, spindle, and mutilate Rails itself, and then spit out a database-specific schema file showing all of the tables, indexes, and seed data provided by the migration files. This includes handling cases where a table is added in migration #4, two new fields are added in migration #6, and one field is deleted in migration #9. There are some corner cases that just can’t be handled, mostly relating to seed data that needs to be migrated to be correct with more recent schemas, but I think I can come close enough to make Typo happy, and probably a lot of other open-source Rails projects.
This turned out to be easier then I expected. I’ve put about 4 hours into it so far, and I can take this migration:
class Test4 < ActiveRecord::Migration
def self.up
create_table :sidebars do |t|
t.column :controller, :string
t.column :active_position, :integer
t.column :active_config, :text
t.column :staged_position, :integer
t.column :staged_config, :text
end
Sidebar.create(:active_position=>0, :controller=>'category')
Sidebar.create(:active_position=>1, :controller=>'static')
Sidebar.create(:active_position=>2, :controller=>'xml')
end
def self.down
drop_table :sidebars
end
end
And then do this:
$ irb
irb(main):001:0> require 'migrate'
=> true
irb(main):002:0> require 'db/migrate/4_test4' # the code above
=> true
irb(main):003:0> Test4.up
=> ...
irb(main):004:0> puts DBMigrator::Database.dump('postgresql')
CREATE TABLE sidebars (id serial primary key, controller character varying(255), active_position integer, active_config text, staged_position integer, staged_config text) ;
BEGIN;
INSERT INTO sidebars ("staged_position", "active_config", "active_position", "controller", "staged_config") VALUES(NULL, NULL, 0, 'category', NULL);
COMMIT;
BEGIN;
INSERT INTO sidebars ("staged_position", "active_config", "active_position", "controller", "staged_config") VALUES(NULL, NULL, 1, 'static', NULL);
COMMIT;
BEGIN;
INSERT INTO sidebars ("staged_position", "active_config", "active_position", "controller", "staged_config") VALUES(NULL, NULL, 2, 'xml', NULL);
COMMIT;
Postgres works now, at least with the 4 or 5 examples that I’ve swiped from Typo’s migrations. SQLite and MySQL are nearly working; I think I just need to fake out a couple classes each and they’ll be up and running. Once that’s done, I’ll bundle this all up into a Rails generator so people can do this:
$ ./script/generate schema postgresql
create db/schema.postgresql.sql
$ ./script/generate schema mysql
create db/schema.mysql.sql
$ ./script/generate schema sqlite
create db/schema.sqlite.sql
Tags database, migrations, rails, ruby, rubyonrails, schema, typo | 8 comments
Posted by Scott Laird
Fri, 13 Aug 2004 11:38:48 GMT
So, I’ve started playing with Rails, a Ruby MVC web framework. So far, I’ve just barely dipped my toes into it, but I’m really impressed with what I’ve seen so far.
My first stop was ActiveRecord, the database layer for Rails. It’s clearly young, but it’s almost a perfect match for Ruby’s dynamic nature. It uses reflection and database metadata to build table classes on the fly. Here’s a brief example:
require 'active_record'
ActiveRecord::Base.establish_connection(
:adapter => "postgresql",
:host => "localhost",
:username => "user",
:password => "password",
:database => "asterisk")
class Cdr < ActiveRecord::Base
def self.table_name() "cdr"; end
end
Cdr.find_all().each do |cdr|
puts "#{cdr.uniqueid}: #{cdr.channel} -> #{cdr.dstchannel}"
end
This dinky block of code is enough to connect to my Asterisk database and extract call details from the cdr table. I didn’t need to tell ActiveRecord anything about the structure of the table; it does the right thing on its own.
It’s actually quite a bit more capable then this example shows; it understands relationships and keys, and it has a full set of searching and updating methods. I suspect that I’ll be getting a lot of use out of it.
Posted in Ruby | Tags activerecord, database, ruby, rubyonrails | no comments
Posted by Scott Laird
Tue, 06 Apr 2004 17:40:15 GMT
databasejournal.com has a nice article on PostgreSQL query analysis from one of the guys behind RubyForge. It’s not rocket science, but it demonstrates that it’s really easy to do statistical query analysis with Postgres and a bit of Ruby code. The article concentrates on the statistical side of things (“which queries are we running most often”) rather then the query analysis side (*why is this query so slow?”). The implication is that the biggest performance wins are to be found by removing unneeded and excessive queries, rather then speeding up the ones that you’re already making. I’m not sure that I completely agree with that, but most of the database tuning articles that I’ve seen concentrate on the other side of things, so it’s nice to see some balance.
Posted in Computer System Administration, Ruby | Tags database, postgres, ruby | no comments
