Right now, Schema API has a few problems.
1) The API for table creation and modification is completely different than the rest of the DB layer, because it's still wacky functions with big arrays rather than fluent method chaining.
2) The API for table alteration doesn't allow multiple alter commands in one query, which in some cases is totally nuts for performance, especially when creating or manipulating indexes. It also leads to some crazy footwork to get around not having an index in places, such as: http://drupal.org/node/159329
So the solution is to make Schema API suck less. I see two possible approaches. (Neither of these affect hook_schema(). That's a layer above all of this.)
1) Introduce DDL query builders, along these lines:
db_alter('tablename')
->addIndex('foo')
->addField('bar')
->addField('blob')
->dropField('baz')
->execute();
db_table('tablename')
->addField('bar')
->addIndex('baz')
->addField('foo')
->execute();
Pros:
- API is similar to the other query builders we have.
- Probably easier to implement than option 2.
Cons:
- Still a query-based approach rather than a DB-state-based approach.
2) Fully objectify Schema API with classes/objects to represent tables and fields.
$schema = $connection->schema();
$table = $schema->getTable('tablename');
$field = new DBField('newfieldname');
$field->setType('blah');
$table->addField($field)l;
$table->dropIndex('bar');
$table->save();
$table = $schema->createTable('tablename');
$table->addField($field);
$array = $table->getArrayVersionLikeHookSchemaUses();
$sql = $table->getSqlCreateStatement();
$table->save();
Pros:
- Richer, more complete API.
- More like other similar systems (I think).
Cons:
- Different than other parts of the DB API at the moment, but less different than it is now.
- More effort required to implement.
Thoughts?