[development] Re: Data-driven database tables and updates

[Barry Jaspan]

I just joined this list on May 30 so I did not see that thread.  It 
was quite a thread, though.  :-)  I'll follow up with Adrian 
directly, but I want to make a couple points addressing the major 
themes of that prior thread:

My goal is not abstraction for the sake of portability.  I am 
sympathetic to many that felt that SQL is already an abstraction and 
it will not necessarily be easier to use a data-structure based API 
than CREATE TABLE directly.  I agree that changing databases will 
never be easy and is not really a goal.

I also am not advocating for removing SQL from the system 
generally.  I haven't thought about using SQL versus Active Record or 
anything else enough to have an opinion, but "If it works, don't fix 
it" is a good starting point.

I am addressing only table structure.  The goals I can think of at 
the moment are:

- Maintainability.  We choose to support MySQL and PostgreSQL.  The 
current _install and _upgrade system, with switches and 
nearly-but-not-quite duplicated code to CREATE/ALTER TABLE, is ugly 
and unmaintainable.  If we can't choose just one (I'm not saying we 
should), we should try to minimize the duplicated code needed to support both.

- Reflection.  There are a variety of good things that can happen if 
Drupal knows its own database schema.  For example, the update.php 
script could actually validate the database layout rather than 
trusting that the schema version number in the system table is 
correct.  We could have a self-documenting database scheme by adding 
fields to the data structure:

    $tables['node']['nid'] = array(
       '#description' => 'The primary identifier for nodes.',
       ...);

    $tables['event']['nid'] = array(
       '#description' => 'The node this event data belongs to.',
       // this could be used to build a graphical schema view
       '#joinswith' => '{node}.nid',
       ...);

We can assign various properties to tables (e.g. configuration vs 
data) and treat them differently as a result.  And so forth.

My concern with the approach I saw discussed in May is that it 
addresses Maintainability but not Reflection.  If we have:

function foo_install() {
    $table = array(...whatever...);
    db_create_table($table);
}

we haven't taught Drupal about its schema (sure, db_create_table() 
could cons up a global based on its arguments at each invocation, but 
it isn't natural).  If we have:

function foo_install() {
    $table = array(...whatever...);
    return $table;
}

then we can.

I'll follow up off-list with Adrian.

Thanks,

Barry