How can I keep data in sync during deployment?

Question

I have the occasion to produce Drupal web sites using development, staging, and production environments. Keeping the code in sync between the sites is a simple task using subversion. What is not so simple is propagating changes to the database data (not just the schema) between installations.

The reason for this will be familiar to any Drupal developer. Drupal stores certain configuration settings in the database, specifically related to CCK fields, Views, and other modules that allow things to be set dynamically using the admin interface. Simply syncing the schema isn't enough - essential information is also in the data.

What I'm looking for is a way to sync these database changes so that if one developer makes CCK field changes on the staging server, they can be propagated down to local development environments for more work, and eventually up to the production environment.

Is there a tool that will do this? What is your process for handling single or multiple developers on a project like this?

Answer 1

For basic data sync: I use mysqldump to dump all data to a .sql file on a nightly basis. The script then checks it in to the version control system. This is cronned up in a simple bash script but you could do something similar on almost any platform...

I've just read a little further and I'm not sure if my method will help. I've never had to merge a SQL dump so I can't comment on how effectively it is managed.

While you should be able to push changes out (schema, new data) to the staging/production server, you might have more hassle pulling changes back into dev. As I say - merging may or may not be possible. I just don't know.

Answer 2

Use database versioning system. For this you need a VersionInfo table in the database and all of your sql ddl and dml queries in xml format along with version info. Now you only a simple .net tool which will check the VersionInfo table and run all the queries from xml which are added after that version and update the version to current in versionInfo.

Answer 3

See previous answers to this question

Answer 4

Around here we've pretty much relegated CCK to use for prototypes and v.simple node-types. It's not worth the headache of trying to separate the 'configuration' from the 'content' in the database. There's all sorts of ways in which you can try to keep things in sync but, in short, unless it's in a file or gives you an option to export to one, you're going to be hurting. ( As an added bonus, exporting your views to a file is going to be a little faster than pulling it out of the DB every time its used. )

You mention Dev, Staging & Live servers - if you have developers making undocumented changes in Staging, you're screwed. If you have Staging regularly synced with Live & mandate the (common sense) policy that the only changes made to Staging are things that have been worked out in Dev & are being tested before moved to Live, you might have more success.

Answer 5

I attempted to answer how I do this in another question. I'll post it here also

I think a good strategy here is to use the install profile API. With install profile API you can do most things that using the Drupal admin tools do. Most core forms simply set variables in the variables table. To be able to sensibly version your non content database contents i.e. configuration it is wise to use update functions.

On my site we have on module "ec" that does very little apart from have it's ec.install file contain update functions e.g. ec_update_6001()

Your main install function can take care of actually running the updates on any new installs you make to bring your modules up to date.

function ec_install() {
  $ret = array();
  $num = 0;
  while (1) {
   $version = 6000 + $num;
   $funcname = 'ec_update_' . $version;
   if (function_exists($funcname)) {
     $ret[] = $funcname();
     $num++;
   } else {
     break;
   }
  }
return $ret;
}

A sample update function or two from our actual file now follow

// Create editor role and set permissions for comment module
function ec_update_6000() {
  install_include(array('user'));
  $editor_rid = install_add_role('editor');
  install_add_permissions(DRUPAL_ANONYMOUS_RID, array('access comments'));
  install_add_permissions(DRUPAL_AUTHENTICATED_RID, array('access comments', 'post comments', 'post comments without approval'));
  install_add_permissions($editor_rid, array('administer comments', 'administer nodes'));
  return array();
}
// Enable the pirc theme.
function ec_update_6001() {
  install_include(array('system'));
  // TODO: line below is not working due to a bug in Install Profile API. See http://drupal.org/node/316789.
  install_enable_theme('pirc');
  return array();
}

// Add the content types for article and mtblog
function ec_update_6002() {
  install_include(array('node'));
  $props = array(
    'description' => 'Historical Movable Type blog entries',
  );
  install_create_content_type('mtblog', 'MT Blog entry', $props);
  $props = array(
    'description' => 'Article',
  );
install_create_content_type('article', 'Article', $props);
return array();
}

Effectively this mostly solves the versioning problem with databases and Drupal code. We use it extensively. It allows us to promote new code which changes database configuration without having to reimport the database or make live changes. This also means we can properly test releases without fear of hidden database changes.

Finally cck and views support this approach. See this code snippet

// Enable CCK modules, add CCK types for Articles in prep for first stage of migration,
// enable body for article, enable migration modules.
function ec_update_6023() {
  $ret = array();
  drupal_install_modules(array('content', 'content_copy', 'text', 'number', 'optionwidgets'));
  install_include(array('content', 'content_copy'));
  install_content_copy_import_from_file(drupal_get_path('module', 'ec') . '/' . 'article.type', 'article');
  $sql = "UPDATE {node_type} SET body_label='Body', has_body=1
  WHERE type = 'article'";
  $ret[] = update_sql($sql);
  return $ret;
}