Update Schema API to make it more usable

Just by reading those two examples, I think most will agree that #1 has better DX. #2 requires more brainpower and referring to docs. In either case, this could be a very big performance win. InnoDB and Sqlite are very very slow with ALTER TABLE operations and batching them like this is a essential.

I think both options represent more engineering than is needed to solve Moshe's actual problem: that you cannot perform multiple table alterations in a single query. Larry's suggestion #1 for DDL query builders really boils down to an OO interface to modify a schema structure and invoke the "do what is necessary to make the database table look like this schema structure" function. We always knew we'd want such a function, we just left if out of the initial D6 patch.

So, we could just write that function (per db driver), which the DDL query builder would need anyway, instead of completly redoing Schema API. Or, at least, we could just write the function first and let Larry spend his life on another big API rewrite if he really wants to. :-)

Comment #2 from moshe talks about a performance win, but how would the generated SQL be different between this:

db_alter('tablename')
  ->addIndex('foo')
  ->addField('bar')
  ->execute();

and that:

db_alter('tablename')->addIndex('foo')->execute();
db_alter('tablename')->addField('bar')->execute();

And in what way would it make any difference in performance?

subscribe. No time to look at this now.

I read that indexes are not rebuilt until a transaction is finished. Would it make sense to do all of update.php inside a transaction?

re #9: That would allow update.php to rollback all updates if any fail...

Interesting Crell. Adding another tag.

I would love to help and have some ideas around making schema API more robust cross-database (eg. column quoting).

Subscribing for now, but I'll be back.

subscribing

I like #2. Actually, the first example makes no sense to me when I start thinking about how I'd go about setting a fields attributes.

Is this how you'd image actually doing something useful in the first example? (not a rhetorical question)

db_alter('tablename')
  ->addField('bar')
  // i'm guessing that $this does not refer to the field "bar", but to the table named "tablename"?
  ->getField('bar')
  // so I must get the field before I set type ? 
  ->setType('int')
  // when I want to get back to the table, will I do a jquery end() like thingy?
  ->end() 
  // now we are dealing with a table again?
  ->addIndex('foo')
  ->execute();

The first example seems fraught with peril (assuming its possible). Or would we be doing what we always do:

db_alter('tablename')
   // false refers to being unsigned of course
  ->addField('bar', 'int', 'small', false)
// or even better
db_alter('tablename')
// how drupal is that?
  ->addField('bar', 'int', array('size' => 'small', 'unsigned' => false, 'default' => 0))

+1 for #2

double -1 for #1

I edited my comment to make me sound like less of a jackass. :-D

So if in both examples we rely on using the same old drupal arrays, e.g.

db_table_alter('mytable')
  ->addColumn('columnname',array(
    'type' => 'varchar',
    'length' => 88,
    'not null' => true,
    'default' => 'Douglas',
  )),
  ->addColumn('columnname2',array(
    'type' => 'varchar',
    'length' => 88,
    'not null' => true,
    'default' => 'Wombat',
  )),
  ->alterColumn('whatever', array(
   'type' => 'int'
  ))
  ->execute();

If we describe a columns attributes as classic schema api arrays in both examples. then I take back my minus -1 on #1

Something that attracted me to the second was that I wasn't passing these arrays. Usually, my schema updates are pretty damn specific, and I liked the idea of calling a function "e.g. setType, setSize" (which my IDE brings up suggestions for) instead of remembering what array keys.

The second strikes me as being better, but i confess its probably because it reminds me of a lot non-drupal classes I've already worked with. I haven't had a good experience with PHP objects that chain yet...

I also confess I've altered more tables through the existing schema api then straight sql.

Edit
Actually, i'm in #1 camp now. My dream of variable hints in my IDE seems dead, so #1 is at least better than the existing way -- and #2 seems cumbersome in comparison.

No input, so this will have to wait until D8.

Neither of these affect hook_schema()

Your remark just prevented another sidetracking :)
#1154334: "Fluid" syntax for hook_schema() (method chaining).

subscribe

Discussed this with webchick in IRC. We agree this is more of a feature request.

For 9.x, we might want to define tables in YAML. hook_schema() is a real PITA to write by hand.

Entity/Field API and 'backends responsible for own storage' make hook_schema() much less necessary, to the point where we could look at removing it entirely in 9.x. Downgrading.

+1 for define tables in YAML! As a contrib module developer, my first instinct was to go to "mymodule/config/install/" to find the db schema yml, but it wasn't there. A temporary work-around solution for D8 is to define an API, such as drupal_schema_yml_to_array(), and then do:

function mymodule_schema() {
  // this function convert a YAML db definition to a hook_schema() array().
  // you could still use the conventional way of define db array in hook_schema().
  return drupal_schema_yml_to_array();
}

I would say use YAML to define data in ContentEntityBase::baseFieldDefinitions() as well. PHP is not Java and I don't see the point of using getters/settings to access data structure. In the "contact_entity_example" module, baseFieldDefinitions() took about 120 lines of code. It feels insane to me to write 120 lines of code just to define such a simple data structure. Even the old way of using hook_entity_info() looks cleaner.

A side note: We as a community should really try to win over developers through better DX. Recently I'm looking at Python-Django per request from a client. I was amazed how simple it was to define tables in django.

Actually, unless I'm mistaken, I think this is the kind of thing we could do in 8.1.x, as long as we retain backwards compatibility for the old way of doing things.

No longer needs to be postponed (nor currently in need of committer feedback).

Also more of a task than a feature request since it's about DX and API improvement.

Are there still any plans to define database table schemas in yaml instead of a hook?
It seems this would work fine for all schemas that are not dynamic, that is, that don't depend on site configuration.

Of course contrib modules can just call Yaml::parseFile() within their hook_schema(), but then every module would have to invent its own convention how to name and where to store this yml file.

@donquixote there's nothing active, but that would be a good issue to open.