[development] Solving the dev->staging->live problem

[Kathleen Murtagh]

I created the dbscripts module to assist me in many of these issues.
The issues are:

 1. Certain settings should be set differently when in a production
and development environment (like caching)
 2. Content data can be created, deleted and modified in both
production and development
 3. The database schema can change and be manipulated on the fly (ie: CCK)
 4. The development process (and thusly, the recording changes
process) must be fast and minimize hindering the development team

Issue 1 can be solved for any configuration setting that is stored in
the variables table, by setting it in the bottom of the settings.php
file (read the bottom of that file for details).  To expand on this,
it would be great to give more flexibility to allow people to set
configuration settings within a file, instead of the database (could
this also improve performance?).

Issues 2 and 3 are the primary issues.  In issue 2, some people want
the capability to merge more than just content, but certain
configuration settings, too!  Such as blocks, site name, etc.  This is
important to those who allow clients to manipulate what they can
handle on the site, and hire them for the big features.  Issue 3 is
what makes merging this stuff hard.  The dbscripts modules depends
upon the usage of update.php and content_copy in order to merge
content.

Another database issue is that one row can hold different types of
data - content, configuration and user data.  For example, the users
table stores configuration (UID, username), content (email address)
and user data (last visited).  Blocks hold both the content of the
block, and the location of it.  This makes it difficult to easily
merge the changes made to these rows if they were both modified in
development and production.

Lastly, issue 4 is vitally important.  You want to minimize repeating
any steps.  You don't want to have to test a configuration setting,
then repeat all your steps while recording it.  Development needs to
be fast and quick, minimizing the time being used for version control.

My dream is to have automatically created database migrations for
Drupal.  The database is being manipulated constantly, is there
anything preventing us from recording those manipulations?  If we
could somehow record each change made to the database automatically in
their original SQL queries and then export those changes to a set of
incremental files.  We could then run a script that would run through
each of the migration scripts to update the database in your working
space.

Optimally, if there was automatic database migrations, the SQL queries
would have to be specific.  The query should target a specific column
in a row, so then an editor in production could change the content of
a block, while the developer could change the visibility settings.
Effectively, the goal is that both production and development could
manipulate the data on the same row while minimizing conflicts.

This would allow a team to both move database changes "down" from
production to development, and "up" from development to production
with relative ease, even on large databases (which dbscripts can't
handle).

Is this technically possible with Drupal?  Is the barrier just finding
someone who will sit down and do it?  If only I knew more about the
interaction with the database layer, I would do it.  However, my
original goal when creating dbscripts was to do this, but I simply
don't know enough about the database layer.

---
Kathleen Murtagh