How Do You Sync Database Schemas?

Rob Sweet asks: "I recently got started coding for a PHP front end to RRDTool. Right now, there are only two developers but we get the impression that once a protocol is in place, we'll have several more. The question has been posed: We can use CVS to keep our code synchronized but how do we go about keeping our database schemas synchronized? The obvious answers involve using mysqldump to keep updated table creation scripts in CVS but I'm wondering if there isn't a better way..." At the very least, a file containing a list of schema changes would be necessary, but what about programs that can take two schemas, look at the differences, and return the commands necessary to make the one mirror the other?

Comments

Syncing schemas

[mangino]

In the past, I have created scripts that populate data. You can do several levels of refresh, refresh just the data, or delete all objects and re-create them as well. This is a bit of a pain to set up, but it works well in simple cases.

Using ant, I just had a task to take a snapshot of the data in the database and save it to cvs. I then had a data refresh as a general part of the setup.

Every once in a while, we would rebuild all objects by dropping all tables and recreating them. This was nice in development, but a pain in production (reloading a 4million row table takes a while, not to mention keeping the data in CVS)

For production usage, we created alter table scripts that got added with the correct TAG. When we installed a build, all alter scripts were run before any code was pushed.

I've hit this problem before

[photon317]

I've been working in private for a while on my own ERD-like software (similar in flavor to Erwin and the likes), and I've dealt with this problem to some degree. It's much easier to have a higher-level tool deal with the issue. In my case, abstract schemas are stored in XML, and I have a tool that parses the XML and generates all the sql for "create table blah blah blah" for whatever db vendor you pick. Then there's another tool that can diff between two revisions of the XML schema definition and issue "alter table blah blah" statements to update a database's table layout.

Mine won't be ready for public consumption for some time yet, since I only work on it now again in spare time, and it has big huge unrealistic design goals - but it's not a hard job to build a simple version of the above on your own that's tailored to just your needs.

Data Model Generation

[bwt]

Some database shops use CASE tools for data model generation and reverse engineering. Ultimately, these sorts of tools represent a data model with an object model, allow direct editing of the internal representation, can import by examining the data dictionary of a datbase, and can generate SQL DDL as needed to apply the difference or create from scratch.

In proprietary realm, Oracle Designer is pretty good at this sort of thing. You can get a developer licence for free from technet.oracle.com, but it's big $ for production use.

There are some open source tools for this, but they all seem to be are fairly young. I happened to notice one on Freshmeat today called Alzabo.

Alzabo

[m_ilya]

If you use MySQL or PostgreSQL you can use Alzabo to synchronize database schemas.