How decently track the database structure modification by SVN?

Question

The main problem is the versioning of the database structure.

The standard mysqldump and pg_dump utilities do not produce very well suited files for versioning.

The dump commands produces the dump files with autoincrement values, TOC entries and so on. Since these objects is subject to continuous changes it always produces the huge difference files.

PostgreSQL Diff

 --
--- TOC entry 2630 (class 0 OID 0)
+-- TOC entry 2549 (class 0 OID 0)
 -- Dependencies: 6
 -- Name: SCHEMA adm; Type: COMMENT; Schema: -; Owner: admin
@@ -61,5 +61,5 @@

MySQL Diff

--- Dump completed on 2010-07-20 14:33:44
+-- Dump completed on 2010-08-11  8:59:39
Index: /db.sql
===================================================================
--- /db.sql (revision 1274)
+++ /db.sql (revision 1317)
@@ -36,5 +36,5 @@
   `message` text,
   PRIMARY KEY  (`id`)
-) ENGINE=MyISAM AUTO_INCREMENT=21122 DEFAULT CHARSET=utf8;
+) ENGINE=MyISAM AUTO_INCREMENT=23730 DEFAULT CHARSET=utf8;

Any suggestions/links/utilities on better way of version control are appreciated!

Thank you.

Answer 1

Take a look at LiquiBase (http://www.liquibase.org/)

It's a tool designed for allowing developers to commit database changes to SVN, and then apply them safely and automatically to the database.

Changes can either be reverse engineered by comparing two databases, or hand coded by the developer and committed.

It also ensures that database changes are applied in the correct order, and only applied once to a given database.

Answer 2

We simply version the scripts used to create the database from scratch. The developers edit the scripts in the text files, and not in the database. Developers do not have access to the production SQL servers, and the DBA team uses tools specifically designed to compare database schemas (in our case, Red-Gate SQLCompare) in order to do production builds. They'll create a new, empty database from the scripts, and use the compare tool to detect changes. Some changes can be automatically applied, and some must be hand-altered.

It's not a perfect system, but it's worked fairly well for us so far.