Re: recommended schema diff tools?

On Thu, Apr 12, 2012 at 10:10 AM, Chris Angelico <rosuav@gmail.com> wrote:
> On Fri, Apr 13, 2012 at 12:57 AM, Welty, Richard <rwelty@ltionline.com> wrote:
>> can anyone recommend an open source tool for diffing schemas?
>>
>> (it should go without saying that i'm looking for ddl to update production
>> and QA DBs from development DBs, but i'll say it, just in case.)
>
> We toyed with this exact issue at work. In the end, we went the other
> direction, and created two files, both managed in source control: a
> .sql file with everything necessary to initialize the database from
> scratch, and a patch script. Every change gets done (by hand) to the
> primary .sql file, and the SQL statements needed to effect the
> transition (eg ALTER TABLE to add a column) get added to the patch
> script. A field in our singleton configuration table records the
> current patch level, so only the necessary changes will be made.
>
> It requires some developer discipline, but it ensures that there's
> always an audit trail giving the _why_ of every change, which is
> something that a diff utility can never do. The patch script is quite
> simple, and looks broadly like this:
>
> patchlevel = query("select patchlevel from config")
> switch (patchlevel)
> {
>  default: print("Unknown patch level!"); break;
>  case 1:
>    print("20120216: Adding Foobar columns to Quux")
>    query("ALTER TABLE Quux ADD foo smallint not null default 0, ADD
> bar varchar")
>  case 2:
>    ... etc ...
>  case 42:
>    ...
>    // Add new patch levels here
>    query("update config set patchlevel=43"); query("commit");
>  case 43: break;
> }
>
>
> Every change is thus assigned a number. The current patch level is a
> safe no-op; any unrecognized number is a major error. The script is
> thus safe to run on any database, and will always bring that database
> up to the script's current patch level.
>
> This has worked out far safer than attempting an after-the-event diff.
> YMMV of course though.

+1

this, or some variation of the approach, is the correct path for doing
database updates in a team environment.  tool driven systems bring
enormous challenges that require enormous engineering to work
properly.  i've heard (although not personally observed) that some
incredibly pricey and complex commercial tools have solved the problem
of automating database updates but the button clicky stuff i've seen
in the open source and shovelware realms come up short in terms of
integrating fully into a proper change management system (although
they can be great from reporting or double checking standpoint).

merlin