[development] Data-driven database tables and updates

[Barry Jaspan]

Nothing like sticking your neck out, eh? :-)

One of the major improvements in 4.7 is the data-driven Forms 
API.  Instead of having modules generate/munge form HTML directly, 
they generate and return a data structure describing the form they 
want.  This leads to all kinds of benefits I don't need to enumerate here.

Another major improvement in 4.7 is hook_install() and 
hook_update_N() which allow modules to programatically create and 
modify their own tables.  Good stuff, but the modules must maintain 
separate code for MySQL and Postgres and generate/munge their tables 
directly.  IMHO, _install() and _update_N() are to 4.7 what the 
pre-Forms API form-building techniques were to <=4.6.

So, why not create a data-driven table creation/modification 
API?  I'm not proposing a complex abstraction over all possible data 
storage mechanisms, just something that is enough to generate "CREATE 
TABLE" statements for MySQL and Postgres and, more importantly, 
allows Drupal to reflect on its own database scheme.

Now that I think about it, I bet CCK has something like internally 
already.  I haven't looked inside CCK yet.

Very roughly, I'm envisioning something like this:

function modulename_schema() {

   $tables['event']['#charset'] = 'utf8';

   $tables['event']['nid'] = array(
     '#type' => DB_INT,
     '#dispwidth' => 10,
     '#signed' => FALSE,
     '#null' => FALSE,
     '#default' => '0');

   $tables['event']['event_start'] = array(...);

   ...;

   return $tables;
}

This will provide numerous other advantages, most of which I probably 
haven't thought of.  One would be to more cleanly control scheme 
changes for updates.  One option would be to include a '#schema' 
version number field.  For simple new columns, the Database API could 
execute the ALTER TABLE itself.  For more complex changes, the module 
could specify a callback.

Obviously the idea would need to be fleshed out more.  If there is 
interest and is not already being done, I'll take it on (or at least 
get it started).

Comments?

Barry