Change management of database scripts

In this post I’m going to discuss the strategy used by our team to manage the changes taking place in the database. Most of the time we as developers mainly focus on implementing the feature and put less effort on manage the database changes. For instance individual might not straight away see the importance in the first place and also this may not be very important for projects with smaller teams but for projects with a larger scope, involving multiple teams and frequency of changes in the functionality is high then might feel the importance of coming up with a strategy to manage this.

First lets try to identify situations that favours to coming up with a strategy for projects that requires to maintain a RDBMS as the data store.

  • For a project having multiple teams to carrying out the development.
  • For a project with a larger scope, having fortnight or monthly deliverables.
  • For a projects with a longer duration.
  • For projects having teams distributed across different geographic locations.

Above list might not be a complete one, so the reader is always welcome for suggestions. I’ll be taking the above scenarios as the base for the rest of my discussion. The obvious fact is that the change is inevitable. To accommodate this change almost every development team use version controlling to effectively mange the development carry out by each individual in the team. Similarly it’s best to come up with a strategy early in the development life cycle on managing the changes taking place to the database of the application(s). The changes can take place to the database under following categories:

  1. A way to keep track of the new insertions of table(s).
  2. A way to keep track of insertion, updates and deletion of column(s) in a table.
  3. A way to keep track of insertion, updates and deletion of row(s) in a table.
  4. A way to manage application configurations/fixed data mappings.
  5. A way to manage setting up of the database for fresh installation or upgrades.

Step 1

The Step 1 can be handled by maintaining a separate SQL script (lets name it db_tables.sql) to keep track of all the new insertion of table(s) into the application. Most of the situations inserting of new table(s) arises when there is a requirement to implement a new feature. So this would give a clear picture of the database about when and at which point in time each table got introduced and by whom.

Step 2 & 3

The Step 2 & 3 can be handled by maintaining a separate SQL script (lets name it db_tables_tracker.sql).

Step 4

In the Step 4, maintain individual SQL scripts to represent the data mapping of each identified table(lets name them as sys_config.sql, app_modules.sql, etc…), also the changes required by each can be introduced directly in to mapping itself or can be accommodated via db_tables_tracker.sql. For tables having larger number of records as data mapping it would be easier to alter the existing mappings to reflect the new change rather accommodating it via db_tables_tracker.sql. This way it helps an individual to get an idea of the most recent state of the mapping just by looking at the SQL script, this approach fails when the application required to main foreign key constraints among related tables because this approach requires to drop and reintroduce the table every time the update takes place on the table column(s) or to the data.

Step 5

The Step 5 can be elaborated by broken it down in to two.

Fresh installation of the database

To accommodate a fresh installation scenario of the database, all the SQL scripts created in first 4 steps can be combines into one batch by carefully arranging the SQL scripts as follows.

  • SQL script from step 1 – db_tables.sql
  • SQL script from step 2 & 3 – db_tables_tracker.sql
  • SQL scripts from step 4 – sys_config.sql, app_modules.sql, etc…

Note : Above sequence can be automated using a shell script (

Upgrading of an existing database

For the purpose of preparing the database to accommodate new changes on to the existing table(s) we can maintain a new SQL script called db_upgrade_tracker.sql. This script will only hold the SQL statements required for updating the previous version of the database to the new state, taken from db_tables.sql and db_tables_tracker.sql scripts. Next step is to sequence the flow of the SQL scripts for the database upgrade as follows:

  • db_upgrade_tracker.sql
  • SQL scripts from step 4 – sys_config.sql, app_modules.sql, etc…

Note : Above sequence can be automated using a shell script (


Revision controlling with Subversion

Subversion also more famouly know as SVN is a version management tool used to manage source code, documents, designs, etc.. in software develpment. SVN is a centralized version control system where the main repository resides in a centralized environment and the parties involved in a project can download a copy of the repository to their local machine to introduce the changes. Once the changes are applied to the files/documents on the local copy, next step is to transfer them to the server. As mentioned SVN is a centralized version control system committing the changes to the server means incrementing the revision of the file/document on the main repository, when the other members of the team updates their local copy of the repository the change also get reflected into their local copy, where as with decentralized version control systems like Git, Mercural, etc.. the users can maintain their own local repository which they can commit the changes without pushing directly to the project in the main repository.

In the following sections I’m going to focus your attention on following aspects of setting up and maintaining a version control system taking SVN as a reference.

  • Setting up a new repository
  • Restoring and Backup a repository
  • Basic commands in SVN

Setting up a new repository

Step 1: Lets start by installing SVN(subversion) and required Apache module (libapache2-svn) into a Linux server/desktop (Debian based distributions but with rest of them are also follows the same). All the commands involve in this process needs to be executed as a super user, so always keep in mind to append sudo to the begining.

$ sudo apt-get install subversion libapache2-svn

Step 2: Creating the target directory for SVN repositories. Please note that name given to the SVN repository will be use as the base URL, which will be used to browse the repository via a web browser. The name can be mapped to any domain name or subdomain name (prefferbly via a virtual host) of your choice for easy access, which I’m not going to explore into it. Execute the following command on the terminal window.

$ sudo mkdir /path/to/svn-repository

Step 3: Introducing a new group to manage the activities of the SVN repositories. Let’s call that group as subversion, execute the following command on the terminal window.

$ sudo addgroup subversion

Step 4: Grant the ownership of the directory create to hold the SVN repositoriesy to the newly created subversion group and the www-data, the group which manages the permissions on resources that is requested via Apache Web server.

$ sudo chown -R www-data:subversion /path/to/svn-repository

Step 5: Now change the mode of the folder created to hold the repository using the following command.

$ sudo chmod -R g+rws /path/to/svn-repository

Step 6: Now we have finished the steps involve in setting up the environment, next let’s look at the steps to create a new project repository called svnproject.

$ sudo svnadmin create /path/to/svn-repository/svnproject

Step 7: Adding user(s) to the newly created subversion group. To introduce a new password file along with a new user, include -c and issue the following command, once hitting enter it prompts for the password and it’s confirmation.

$ sudo htpasswd -c /etc/subversion/passwd Hayesha

To introduce a new user to the existing subversion group to access and carry out activities execute the command as follows

$ sudo htpasswd /etc/subversion/passwd Piushan

Step 8: Grant access to the newly created SVN repository (eg: svnproject) via WebDAV protocol, configure the Apache Web server by adding the following snippet to dav_svn.conf file located under /etc/apache2/mods-available/. Place the following block of code snippt inside dav_svn.conf to reflect the parameters of the newly created SVN repository.

$ sudo cp /etc/apache2/mods-available/dav_svn.conf /etc/apache2/mods-available/dav_svn.conf.bk # Creating a backup of the existing configuration file
$ sudo vim /etc/apache2/mods-available/dav_svn.conf

<Location /svn-repository/svnproject>
    DAV svn
      SVNPath /path/to/svn-repository/svnproject
      AuthType Basic
      AuthName "Svnproject Repository"
      AuthUserFile /etc/subversion/passwd
      Require valid-user

Step 9: Grant the ownership of the newly created SVN project to subversion and www-data groups.

$ sudo chown -R www-data:subversion /path/to/svn-repository/svnproject/

Step 10: Now change the mode of newly created SVN project using the following command.

$ chmod -R g+rws /path/to/svn-repository/svnproject/

Step 11: Restart Apache server.

$ sudo /etc/init.d/apache2 restart


Restoring and Backup a SVN repository

  • Backup a SVN repository
    • The SVN‘s administrative tool named dump will be used to backup a project inside the SVN repository, this can be achieved using following cmmand.

      $ svnadmin dump /path/to/svn-repository/svnproject > /path/to/backup/svnproject_25_11_2012.dump

  • Restoring SVN project to an existing repository using a SVN backup (dump).
    • Create a new project called recoverproject inside the SVN repository as follows

      $ svnadmin create /path/to/svn-repository/recoverproject

    • Load the SVN dump of the project into new repository location as follows. This may take a while depending on the number of revisions had on the SVN dump used for the restoring.

      $ svnadmin load /path/to/svn-repository/recoverproject < /path/to/svn-project-backup.dump


Basic commands in SVN

Getting a checkout from a main repository of a SVN project. The syntax is svn checkout <url> or by using svn co <url>, co is the shorten form of checkout.

$ svn checkout

To update the local copy (development version) with the main repository of the SVN project. Navigate to the location of the local copy and execute the following command. svn update or by using svn up (up is the shorten form for update.

$ svn update

In the instances where the local copy (development version) of the code base is out of sync (in a lower revision number(s) compared to main repository of the SVN project’s revision numbers), need to execute the svn cleanup command to sync the local copy to bring it to the server’s current revision in order to avoid conflicts that experience while committing the code.

$ svn cleanup

To transfer the modifications in the local copy of the code base to the main repository of the SVN project need to use the svn commit command. It’s always considered a best parctice to introduce a comment using -m ‘comment’ along with the commit command.

$ svn commit -m "adding the modification done to bla_bla-bla.ext" <filename.ext>

To introduce new file(s) and folder(s) to the SVN project on server use svn add folowed with svn commit commands.

$ svn add <filename.ext> or <folder name>
$ svn commit -m "adding the new file(s) or folder(s) called filename.ext or foldername/" <filename.ext> or <foldername/>

To remove file(s) and folder(s) from the main repository of the SVN project use svn update, svn delete followed by svn commit commands.

$ svn update
$ svn delete <filename.ext> or <folder name>
$ svn commit -m "removing existing file(s) or folder(s) called filename.ext or foldername/" <filename.ext> or <foldername>