Drupal 6 development and version control best practices?

I always work with Subversion. I hadn't worked with Drupal, though. Versioning Drupal is a bit tricky because the configuration, which must be versioned to have a homogeneous development environment, is stored in MySQL. Now, I am a total newbie when it comes to Drupal, so take my advice with a grain of salt.

On my root directory, I have three folders,

documentacion
drupal
mysql

I installed everything related to Drupal in the drupal directory; all documentation in the documentacion directory (the name is in Spanish, although without accent); and, most important, all MySQL dumps (necessary for versioning) in the mysql directory. You can of course use the names you want, etc.

On the mysql directory I have the following contents:

completa.sql
dump.sh
esquema.sql
load.sh

This is the code in dump.sh (I changed the database username, password, etc):

#!/bin/bash
mysqldump -u mysqlUser -pmysqlPassword dataBaseName access actions actions_aid authmap batch blocks blocks_roles boxes comments content_node_field content_node_field_instance files filter_formats filters flood history languages locales_source locales_target menu_custom menu_links menu_router node node_access node_comment_statistics node_counter node_revisions node_type permission role system term_data term_hierarchy term_node term_relation term_synonym url_alias users users_roles variable vocabulary vocabulary_node_types > completa.sql

mysqldump --no-data --add-drop-table -u mysqlUser -pmysqlPassword dataBaseName > esquema.sql

After dataBaseName and just before > completa.sql are listed all "relevant" (for versioning purposes) tables on the MySQL database for my drupal installation. I read on several forum threads that I could safely discard (but you may wanna double check this) all cache* tables, along with the sessions and watchdog tables. On my MySQL I have the following cache* tables:

cache
cache_block
cache_content
cache_filter
cache_form
cache_menu
cache_page
cache_update

You may not have an identical list of cache* tables as I added some stuff.

After dumping the "relevant" tables' contents to completa.sql, I dump the entire schema (which must include all the tables) to esquema.sql. Note the --no-data --add-drop-table options on the second instruction.

Something very important to remember is that several drupal modules add tables to the database. Unless these tables are of the cache* kind, you must add them to the "relevant" tables list.

Now the contents for load.sh:

#!/bin/bash
mysql -u mysqlUser -pmysqlPassword -e "drop database dataBaseName"
mysql -u mysqlUser -pmysqlPassword -e "create database dataBaseName"
mysql -u mysqlUser -pmysqlPassword dataBaseName < esquema.sql
mysql -u mysqlUser -pmysqlPassword dataBaseName < completa.sql

There may be more efficient ways to do this, but this definitely worked for us.

This is how it works. I assume you already have a repository and a working copy of your drupal, but you haven't versioned your database yet. The mysql user (in this case, mysqlUser) must have the proper privileges.

1. Add the scripts to a directory you find convenient on your working copy.
2. Edit the first script (the one I named dump.sh), and check that you have all "relevant" tables in the list. Change the username, password, etc.
3. Run the script.
4. Edit the second script to have the proper names, etc.
5. Add both scripts and both dumps with svn add, and commit:

svn add completa.sql dump.sh esquema.sql load.sh
svn ci completa.sql dump.sh esquema.sql load.sh -m "Whatever message here"

6. Once your comrades make an update, they should have the scripts and the dumps on their working copies.
7. To have an identical database, they only have to run load.sh.

When other teammates add configuration changes, or modules, or whatever changes the database, they'll have to run dump.sh. They should remember to add the "relevant" tables to the script before running the script. I'm sure there's a way to do this more elegantly (anyone?). You can always run the script and check for any changes to esquema.sql with svn st and svn diff commands, though.

Now, what load.sh does may also be pretty barbarous. It drops the entire database, it then creates it again, loads the schema, and then loads the data.

The users table is also committed, so all usernames and passwords are also homogeneous. This may not be very safe, so you may want to omit the users table from the "relevant" tables list, but I don't know if this may have any negative repercussions on the system.

I hope this helps someone, and I also hope for some healthy criticism for this method to improve things.