[development] Separating configuration vs data tables

[Barry Jaspan]

One thing I'm not aware of a good way to do with Drupal is sandbox 
testing or controlled change management on a running system.  There 
are some posts talking about how have a test site and test database 
for development, but you always have the problem of re-integrating 
your changes back into the running system.  You either have to (a) 
re-make the changes on the production system one step at a time or 
(b) transfer the development database to the production database, 
losing any new content/users/data.

I do (a), but it is annoying and error prone.  If someone has a 
better system, I'd love to hear it.

My suggestion is to identify which database tables contain 
administrative or configuration data vs. which tables contain content 
and then store them in separate databases (brief research indicates 
that MySQL supports cross-database queries but Postgres does not, so 
this functionality would be turned off with Postgres).  Ideally, it 
would then be possible to clone both databases, add modules or change 
settings in the sandbox until happy, and then copy back only the 
admin/config database and any filesystem mods.  In one step, the 
production system would be upgraded.

We would have to decide which tables fall into each category and 
possibly refactor some tables if they span both categories.  Perhaps 
it is not possible to separate them all cleanly in this way, but it 
seems worth trying.

As for implementation, we already have the $db_prefix array that can 
be used to convert {tablename} syntax into database-qualified 
values.  That could be set up manually.  Even better would be to have 
this separation work automatically for core and contributed modules 
by specifying which category each table belongs in a data driven way:

function system_schema() {
   $tables['variable']['#category'] = DB_CAT_CONFIG;
   ...;
}

function node_schema() {
   $tables['node']['#category'] = DB_CAT_DATA;
   ...;
}

Comments?

Barry