schema.rb lacks ENGINE and CHARSET

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

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.

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

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