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.

XML plug.

[Big Sean O]

Describe the table structure using an XML format of your own devising. Write a script that will create the SQL commands based on the XML format. For instance, a section of the XML could look like this:

<table name="Address">
<field name="First" type="char" length="20" />
<field name="Last" type="char" length="30" />
<field name="Username" type="char" length="10" />
<yadda />
<yadda />
<yadda />
</table>

An XML format of a table is about as human-readable as the raw SQL and a diff between two versions should make a lot of sense (Oh, you've changed the "zip" field in the Address table to "postalcode"!)

Writing a python script that would read this and output SQL is pretty easy. You could even do this it in XSL. Once you write the script, it should work great. You can tweak the script to output SQL for different DBMSs.

Make sure both developers are using the same dtd (to make sure your XML is valid). And since XML is verbose (is it _ever_), commits have a better chance of not clashing unless you're both hacking the same part of the schema.

you suggested it yourself

[frankske]

we put a mysqldump (with a couple of options) in the cvs! We considered other options, but it's by far the easiest!

Jakarta Turbine Torque

[roblambert]

If you don't mind using Java, check out the Jakarta project Turbine-Torque at http://jakarta.apache.org/turbine/torque/.
Abstra ct schemas are stored in XML and Torque parses the XML and generates the database schema for whatever database vendor you pick. (Torque also generates Java code to easily work with data in the database).

Have you read their website

[Chacham]

Either you haven't RTMed, or you didn't explain very well. The documentation has it here. Just make both masters and both slaves, and hope you don't trample on each other.

Now that you have the key to complete disaster, I will warn you since you obviously don't know this. This is a stupid idea. Hmm... that not harsh enough. If you were working for me, I'd reconsider your employment if you came up with this crazy idea.

You should never, ever, ever, ever, have more than one person working on the same schema while coding! Either the database will be driven by your code, which is a quick way to denormalize everthing, and wreck havok, or more than one person will drive the design, and will denormalize and wreck havoc.

Only one person (or group) should make decisions on a schema, and it should be done, *before* any coding is done. The database structure will lend structure to the program itself.

I was a DBA for Oracle at a small company. You wouldn't believe what those idiots (Andrew, you listening? :-)) wanted to do, and did when I went on vacation! Instead of coding properly, they added tables to make it easier. And then they wonder why the DB slows down on their queries. Or they want to know a concrete method (query or idea) of encapsulating all data of a certain type, and wonder why I can't do it, since they left so many holes in the system.

The database should be designed to handle the system. And that it much more important than coding. Both because of speed and structural reasons. The only time the database should be changed, is when there was a mistake in the original design, or the project it is for has changed dramatically.

So, their is a way to do it, but its on par with logging in as root all the time, so things are easier. Don't do it.

Re:Have you read their website

[BetaJim]

I have to agree with the OP. Ruin the normalization of a large database and your screwed. I don't want to pick on the developers, but I wouldn't accept modifications to the database from them with out good reason.

Certainly, there are times that de-normalization of the schema is necessary for good perfomance or other reasons. Just don't change the schema only to ease application development.

Re:Schemas?

[aminorex]

Schemata is Greek. Schemas is English as She is Spoke.

Plurals of loan-words are inconsistent in English.

Oracle?

[larien]

Dunno if Oracle's really an option for you, but I do know that you can have a fallback database set up which is 0-15 minutes (exact delay depends on configuration) behind the 'live' one. This uses the archive logs to apply updates to the backup DB. It's normally used for high availability (i.e. when the live dies, you switch to the fallback which is almost up to date) but might help here.

The main problem is that if you change the dev copy of data, you'll have to rebuild it from scratch unless you can rollback.

Have to agree, great way to blow up your DB

[Sean Clifford]

I have to agree with Chacham here; you *never* *ever* have developers changing a database schema willy nilly. It's a great way to destroy your project. The database comes *first*; if you plan your project properly from the beginning you shouldn't need to make many structural changes to your database.

This is 101 level stuff: plan first, code later. You don't make things up as you go along.