Slashdot Mirror

← Back to Stories (view on slashdot.org)

How Do You Sync Database Schemas?

Posted by Cliff on from the don't-dis-my-DML-buddy dept.

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?

3 of 31 comments (clear)

  1. Syncing schemas by mangino · · Score: 4, Informative

    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.

    --
    Mike Mangino
    mmangino@acm.org
  2. Data Model Generation by bwt · · Score: 3, Informative

    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.

  3. Jakarta Turbine Torque by roblambert · · Score: 2, Informative

    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).