CM and databases

(Jerker Montelius)

1Introduction

To be CM in a ”source code” is relatively simple. Take care to check in your code and mark it in some clear and logic way with branches and tags. You do an installation by checking out the latest production ready code, remove the old code and substitute it with the new code. If something goes wrong rollback to your last functional code until you figure out whats wrong.

1.1The database problem

Of cause the world is not beautiful like this. One major obstacle is databases and data. Here you not only have to juggle old and new code but also the data. Its not possible to as it obviously is very valuable and/or created through hard work. May times the data is also so big that dumping it on a disk is far from a easy operation.

1.1.1Old Naive Solution

What has then been the the traditional approach to this problem? You gusset it, migrate scripts. However migrate scripts have problems of their own.

  • Hard to write. Its generally harder to write migrate script compared to standard SDL code.
  • Hard to apply. A migrate script have to be applied to a very specific DB version and if you have more than one they have to go in I a very special order.
  • Time consuming to apply. In any DB system under development the number of migrate script will raise rapidly. Then it can be quite a log process to create a new database instance from scratch.
  • Hard to set up new instances. If its fairly time consuming to set up new databases you will probably shun that practice.

2New solution

Well do we have a new and better way of doing this? Yes we have!

2.1Prerequisite

To be able to show some examples I will make some assumptions of preinstalled software. Everything here is open source and easy to come by.

  • Version control system: SVN
  • Ability to create new databases: postgresql
  • sql dump utility: pg_dump
  • sql diff utility: apgdiff

2.2Deploy

2.2.1Initial version

  1. Hack away in your development environment. Use GUI or CLI according to your taste.
  2. Dump the database and commit.
$ pg_dump -s $DEV_DB >/sql/db.sql
$ svn add sql/db.sql
$ svn ci -m”Initial DB version”
  1. Install in test environment.
$ psql $TEST_DB  <sql/db.sql
  1. Test.
  2. Install in production.
$ psql $PROD_DB <sql/db.sql

2.2.2Following versions

  1. Hack away in your development environment
  2. Dump your version.
$ pg_dump -s $DEV_DB >/sql/db1.sql
  1. Agpdiff with prod version. Save the patch
$ java -jar apgdiff-2.4.jar --ignore-start-with db.sql db1.sql >uppgrade1.sql
$ mv  sql/db1.sql sql/db.sql
$ svn add sql/upgrade1.sql
$ svn ci -m”New DB version”
  1. Patch test environment.
$ psql $TEST_DB  <sql/upgrade1.sql
  1. Test
  2. Patch production up to the next version.
$ psql $TEST_DB  <sql/upgrade1.sql

2.3Advantages

  • Complete picture in the code. You can actually look in your code and understand how the database looks.
  • Developers can use GUI tools if they like. Some developers like it. There might be some advantages.
  • You can create new instances relatively fast and without a series of migrates. If you work with migration you know that the sheer number of migrate script grows fairly fast. One script executes much faster

3Alternatives

The are of course alternatives to this approach.

  • Ruby on Rails Migrations
  • Python Django South

Both these approaches are tied to a framework and requires the developer to use a programming language instead of SQL.