> "mysqldiff is a Perl script which compares the data structures (i.e.
> table definitions) of two MySQL databases, and returns the differences
> as a sequence of MySQL commands suitable for piping into mysql which
> will transform the structure of the first database to be identical to
> that of the second (c.f. diff and patch). Database structures can be
> compared whether they are files containing table definitions or
> existing databases, local or remote. "
>
> It would make our lives 1000x easier if such a tool existed. As it
> stands now, the process of updating the DB involves manually writing a
> series of alter statements, which is a huge pain. I can't be the only
> one with this problem!
This tool would be somewhat like saying: "We have a copy of Windows95b1
and Windows95b2, and we want something that takes all the EXEs and DLLs
and converts them from one to the other. Right now we use a hex editor and
hand-change each hex value from the old to the new."
Someone might ask you: "Why? Just go to CVS, check out the source for
Windows95b2, compile it, and there you have it. Why are you diff'ing two
binary sets of data?"
By the same token, database schemas should be created by some source code
(ie: ERDs) and then 'compiled' into the form you use. Revisions of the
ERDs should be stored in revision control (ie: CVS).
Your problem may have a solution (a postgresdiff might exist) but if you
find it, I urge you not to use it. Instead, keep ERDs for the old version
and new version, and compile them to the database.
Disallow the use of DDL in your database unless it was generated by the
tool that stores your ERD. Give developers that need to change the table
definitions access to your ERD tool. Dia is such a tool that gives you
these options.
If you cannot do this, your databases will drive you mad.
--
Tim Ellis
Senior Database Architect
Gamet, Inc.