How to adequately track the change in the structure of the SVN database?

Question

The main problem is version control of the database structure.

The standard mysqldump and pg_dump utilities do not create very good version control files.

Dump commands issue dump files with auto-increment values, TOC entries, etc. Because these objects are subject to constant change, it always creates 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 the best version control method are welcome!

Thanks.

Answer 1

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

This is a tool designed to allow developers to commit database changes to SVN and then apply them safely and automatically to the database.

Changes can either be reconstructed by comparing the two databases, or manually coded by the developer and committed.

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

Answer 2

We just use the scripts used to create the database from scratch. Developers edit scripts in text files, not in the database. Developers do not have access to production SQL servers, and the DBA team uses tools specifically designed to compare database schemas (in our case, Red-Gate SQLCompare) to perform production assemblies. They will create a new, empty database from the scripts and use the comparison tool to detect changes. Some changes can be automatically applied, and some must be changed manually.

This is not an ideal system, but so far it has worked quite well.

Answer 3

I would not use MySQL dumps because they are mainly used for backing up data, and you usually do not use version control to manage data backups. Instead, I would simply control the version of the install script or SQL file used to configure the original database structure.

For small projects, I usually have a file called install.sql that contains all my CREATE and schema.txt that describe the schema. For larger projects, you can use something like dbForge, which allows you to manage database schema versions in the professional version, although this is a bit expensive if everything you use for this.

For more information, see this article on horror coding (especially the first link in this post).

Answer 4

Depesz recently wrote a blog post “ HOW TO MANAGE YOUR DATABASE CHANGE? ”

I would say:

• If you just store each object schema in SVN, you still need to deploy the changes with order dependencies and data changes, so everything that really buys you will classify your change history for the objects involved.
• Write scripts to complete all of your changes, including scripts, to discard the changes.
• Use apgdiff to create a database schema (PostgreSQL).

Answer 5

You can use the free Other PostgreSQL Diff Tool for PostgreSQL Databases to compare your development design and production design. You simply update your development database in the way that is most convenient for you to do. If you want to upgrade the production database to the state of the development database, you will create a development database schema and a production database schema and let apgdiff compare them. It will give you an output containing the DDL instructions necessary to convert your production database into a development database state.

In fact, it's up to you to deploy apgdiff in your development cycle, all it does is create output with DDL statements to "move" your production database to the same state as the development database.

On the website you can find information on how it works, how to use it, what statements are supported, etc. There is also an article about updating the PostgreSQL schema in my blog at www.fordfrog.name (I was allowed to include only one link so I could not make this address link either).