Looks like this should support all table options rather than ENGINE only. Also, how would the pg and sqlite adapters take advantage of this?

My goal was to be able to use MyISAM specific features. Since there are things like FULLTEXT indexes that are only supported in MyISAM, which are not usable because schema.rb would create the tables in InnoDB, and wouldn't let sql syntax like run through tests.

SELECT * 
FROM t1 
WHERE MATCH (t1.foo_desc, t2.bar_desc) AGAINST ('+foo* +bar*' IN BOOLEAN MODE)

I don't think other table specific variables are as important to store in the schema.

If there are other options we should add to pg and sqlite adapters, perhaps we should add them as needed?

Should be added as needed, yes, but when making API choices we'd be well-off to anticipate how/whether other dbs use table options.

I would say it should pull in a subset of values that options for the parameter of add_index, as they apply to tables in different databases.

https://github.com/where/rails/blob/master/activerecord/lib/active_record/connection_adapters/abstract/schema_statements.rb#L389

Happy to make changes to my code if you have suggestions.

I just made a new project that shows the problem that this is solving. https://github.com/kenmazaika/mysql-engine-example/commit/f8c5cf2d4507a76f73b4eb9ce2d387b3f28c5b65

Notice that using the API now, you can create a database table, of the type of "MyISAM", but this change will not be persisted in the schema. This causes the database that is created in the tests (by schema.rb) to use the default engine, which is InnoDB.

However there are specific features of MyISAM that InnoDB does not support (http://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html), and this causes all of those features not be able to be used in a rails application that is unit tested.

Since Rails is adding logic to default the engine to InnoDB, I think persisting the Engine makes sense in this case.

In MySQL the other fields we could track are:

  |  {ENGINE|TYPE} [=] engine_name
  | AUTO_INCREMENT [=] value
  | AVG_ROW_LENGTH [=] value
  | [DEFAULT] CHARACTER SET [=] charset_name
  | CHECKSUM [=] {0 | 1}
  | [DEFAULT] COLLATE [=] collation_name
  | COMMENT [=] 'string'
  | CONNECTION [=] 'connect_string'
  | DATA DIRECTORY [=] 'absolute path to directory'
  | DELAY_KEY_WRITE [=] {0 | 1}
  | INDEX DIRECTORY [=] 'absolute path to directory'
  | INSERT_METHOD [=] { NO | FIRST | LAST }
  | MAX_ROWS [=] value
  | MIN_ROWS [=] value
  | PACK_KEYS [=] {0 | 1 | DEFAULT}
  | PASSWORD [=] 'string'
  | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
  | UNION [=] (tbl_name[,tbl_name]...)

ENGINE seems to be the option that can add the most functionality to a table. We may want to include the CHARACTER_SET as well, as that seems like it could potentially have interesting data in it.

The table option field is explicit for MySQL in the docs.

I believe in Postgres the options can be used to configure these options:

[ INHERITS ( parent_table [, ... ] ) ]
[ WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace ]

In the future the options may want to include some of this data as well.

I can't find any documentation that leads me to believe SQLite can support options, so I think it does not.

Looks like the pull request was accepted on this, can this be closed?

I don't think this feature was merged. It doesn't look like schema.rb will be generated including the engine type in it if the type isn't InnoDB. The other pull request I opened was a different (but related) feature. Will you reopen this pull request?

👍

Any status on this ?

It doesn't merge cleanly.

merged in #12172 unless @kenmazaika wants to do it himself.

Closing in favor of #12172. @Intrepidd it would be awesome if you give @kenmazaika credits in your commit too.