The reality is that what we want is this: http://www.liquibase.org/
Liquibase is an open source (Apache 2.0 Licensed),
database-independent library for tracking, managing and applying
database changes. It is built on a simple premise: All database
changes are stored in a human readable yet trackable form and checked
into source control.
However our development process doesn't support it. We typically don't modify the database through discrete scripts we write ourselves, we use plugins that we activate. We don't write DML scripts to modify look-up data that we then check into source code control, we use a UI on the admin page and therefore have no source code for later use in replicating that change during migration.
However, we can emulate some of it -- using some of the tools listed on this page:
https://stackoverflow.com/q/225772/149060
For instance, liquidbase has a diff feature that also, optionally includes changes to data. We could, potentially, output the schema and data diff to a script, excluding (as possible) certain tables likely to include test data ( i.e. post, etc. ) and then apply the script to the production database.
MySQLDiff (discussed on the StackOverflow question) does schema diffs, and it's author recommends mysql_coldiff for table-wise data diffs - both are implemented in perl, if java tools (liquidbase) are too resource heavy for your servers -- although bring both databases local and running the tool on your PC solves that problem ...
If we really want to do it right, we should log any sql that relates to settings, options, or other configuration changes, and any schema changes -- and convert the logged code into a migration script to play against our production server. Play the migration script against the server, copy the wordpress site files (excluding uploads, if applicable) and we're gold.
So, it seems to me, that the best way out, is a developer's migration-builder-plugin that traps the sql we need, stores it and then generates a migration script from the logged code, rather than to build a way to merge databases between staging and production. Seems a simpler problem to solve too.
If we look at the code of @bueltge 's instrumenting hook calls plugin for inspiration: https://gist.github.com/1000143 (thanks to Ron Rennick via G+ for pointing me in the direction of SAVEQUERIES and the shutdown hook, that lead me to find it)
-- alter it to get the SAVEQUERIES output instead
-- only run while in admin
-- filter out all selects
-- save results out to table in the shutdown hook
-- we could selectively toggle output trapping based on what we were doing at the moment.
For example:
Capture Name: Activate & Configure Plugin XYZ
Capture State Toggle - on
... install and configure plugin XYZ
Capture State Toggle - off
Export Migration Script for: Activate & Configure Plugin XYZ
Press Export Button -- to produce a popup text field with the filtered trapped SQL - ideally pre-formatted as a shell script with command-line call to mysql. Copy & paste it out to your migration code folder and add to your source code repository.
Careful attention to toggling the capture on and off as you're working and you'll be able to generate the perfect migration script to take your production database to an equivalent configuration to your staging database.
What's better, you'll have a script (or series of same) that you can TEST. Imaging having replicable, testable, migration scripts!!
I'm in love already.
Anyone else?
