svn - Drupal DATABASE deployment strategies?

Question

From this item: What's best Drupal deployment strategy? .... I quote:

Databases are trickier; cleaning up the dev/staging DB and pushing it to live is easiest for the initial rollout but there are a few wrinkles when doing incremental DB updates if users on the live site are also generating content.

I want some ideas on how to do this? Currently I get a complete copy of the existing db on my local machine, commit that subversion, and then deploy the whole database. Currently the file is 15megs, and each time I have to upload the whole file (i think subversion sees it as a whole new file, because it has so many changes each time).

So, my questions are really:

1. How can I get my Db size down when committing (other than committing less frequently)?
2. Is there any other way to keep my db and server DB in synch? especially considering that users will be posting new data all the time?

Answer 1

Is there any other way to keep my db and server DB in synch? especially considering that users will be posting new data all the time?

We have a large distributed team and editorial staff everywhere so deploying the database is not feasible.

To get around this we make extensive use of update functions. We have a module which has no real code, which we use for updating settings. Every time a developer makes a configuration change they write an update function in this module which when run will make the corresponding change on the other development DBs, staging and live.

There are issues, particularly with cross dependencies (if people write update functions in more than one module), and it can take time to code something that is a relatively minor change in the admin. Install profile api helps in this.

For example

function mysite_update_6000() {
  install_include(array('user'));
  $editor_rid = install_add_role('editor');
  install_add_permissions(DRUPAL_ANONYMOUS_RID, array('do something'));
  install_add_permissions($editor_rid, array('do something', 'administer nodes'));
  return array();
} 

Will add a role and assign some permissions to it. Doing this keeps all of the changes in the code so that you don't have to try to migrate and synchronise databases.

There is also a migration module which may help with this, it logs changes to tables and saves them to an update function. This is not to be confused with the drupal.org migrate module which is for content migration.

We have had some success, but also some issues with the features module, which can help with migrating features.