schema.rb lacks ENGINE and CHARSET

rubyonrails-core
1

It seems to me the generated schema.rb file in Rails does not retain table type (and probably other characteristics).

MySQL 5, Rails 1.2.5

Working with some legacy tables here, and experimenting with how migrations are going to interact, and how best to initialize a rails project using a legacy db as starting point for migrations.

Rather than hand coding a boatload of rails-style create_table tasks, I figured I could cheat and initialize an empty database using legacy CREATE statements, and use db:migrate to create them for me in schema.rb.

I created my db, used my legacy CREATE statements to initialize all tables, added a single migration to create the standard Rails sessions table, then ran the rake task. That of course generated the schema.rb file.

I then deleleted all tables and the create_session migration, and I used the create_table and add_index statements from schema.rb to create a new 001 migration called create_all_tables

In reviewing my tables, I notice they're all InnoDB now instead of MyISAM.

After reading through some docs about :options, I also suspect that if I didn't already have utf8 defined, that whatever CHARSET I had defined would have been nuked too.

This seems to be significant shortcoming. I would think that schema.rb should be generated with appropriate options for table type, character set, collations and probably other stuff too.

I know I can override it by editing the create_table statement myself, but I shouldn't have to.

Am I missing something? Is this a known "defect" in how schema.rb is generated? I know Rails can detect these attributes of existing tables (I've done it in other similar work of my own). Is this a judgement call for database neutrality? If so, still seems to me there should be some capability to generate schema.rb based on what actually exists, because as it is, it's not preserving what the schema really is.

-- gw

2

This seems to be significant shortcoming. I would think that schema.rb should be generated with appropriate options for table type, character set, collations and probably other stuff too.

All that is database specific. schema.rb doesn't deal with database- specific things. If you need those, you'd want to use the SQL-based schema dump instead. See

  # Use SQL instead of Active Record's schema dumper when creating the test database.   # This is necessary if your schema can't be completely dumped by the schema dumper,   # like if you have constraints or database-specific column types   # config.active_record.schema_format = :sql

in the config/environment.rb and rake db:structure:dump.

3

DHH wrote:

This seems to be significant shortcoming. I would think that schema.rb should be generated with appropriate options for table type, character set, collations and probably other stuff too.

All that is database specific. schema.rb doesn't deal with database- specific things. If you need those, you'd want to use the SQL-based schema dump instead. See

  # Use SQL instead of Active Record's schema dumper when creating the test database.   # This is necessary if your schema can't be completely dumped by the schema dumper,   # like if you have constraints or database-specific column types   # config.active_record.schema_format = :sql

in the config/environment.rb and rake db:structure:dump.

Ah. Sorry I didn't see that. Thx for thinking of the alternative.

-- gw

4

Greg, Considering the shortcomings in the :ruby schema builder, I'm surprised that anyone with legacy issues would consider it all: deviations in primary key structure or name, foreign key constraints, table types -you name it and the :ruby schema builder can't handle it. I'm OK with that because I don't expect Rails to be able to grok everything a RDBMS supports and render it in a vendor-neutral format.

What I'm not OK with is that the VERY capable migrations support in RoR is not an option for building the test database -although there are hacks out there to do it (http://dev.rubyonrails.org/ticket/8389, http://snippets.dzone.com/posts/show/2031). This would allow one to build the test db with the same strong capabilities available when constructing the development and production databases.

My suggestion: give up using the ruby schema dumper with legacy tables. Instead, use only migrations. For tests, consider the links above for building test tables via migrations.

-Chris