recommended schema diff tools?

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.

Chris Angelico

(sorry for top posting but I'm using a less than sane email client)

I came across SQL Power Architect not long ago and it might be something you could use.
http://code.google.com/p/power-architect/

I haven't had much time to look at it though.

Regards,
roppert

Från: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] För Welty, Richard
Skickat: den 12 april 2012 16:58
Till: pgsql-general@postgresql.org
Ämne: [GENERAL] recommended schema diff tools?

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

thanks,
richard

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

On Fri, Apr 13, 2012 at 1:28 AM, Merlin Moncure <mmoncure@gmail.com> wrote:

(although
they can be great from reporting or double checking standpoint).

Good point. Double-checking your patch script may well be worth doing.
It ought, in theory, be possible to load up a copy of your existing
database, spin up a brand new one from the CREATE TABLE script, diff
them, and compare against the patch script. Of course, that does run
the risk of useless diff noise (for instance, I'll often add a new
field into the middle of a table where it most logically fits, but the
patch will simply ALTER TABLE to add it at the end), but it could
still be a useful check, especially if completely automated.

ChrisA

Chris Angelico wrote on 12.04.2012 17:10:

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.

Sounds like you implement something very similar to Liquibase.

Welty, Richard wrote on 12.04.2012 16:57:

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

You might want to have a look at SQL Workbench/J.
It has it's own "SQL command" for doing a diff on schema level.

http://www.sql-workbench.net/manual/compare-commands.html

The output is a (dbms independent) XML file, but XSLT templates to transform that into SQL are available:

http://www.sql-workbench.net/xslt.html

The XSTL might need some adjusting for your purposes though.

But you should put a controlled way of deploying schema changes into place.
Doing a diff of a developer DB isn't really the ideal approach.

We are quite happy using Liquibase for this purpose: http://liquibase.org/

Thomas

On Fri, Apr 13, 2012 at 2:02 AM, Thomas Kellerer <spam_eater@gmx.net> wrote:

Sounds like you implement something very similar to Liquibase.

Never heard of it till today; just googled it. Looks cool. At first
glance, yes, does appear similar; but what we're doing is WAY less
sophisticated.

ChrisA

On Thu, Apr 12, 2012 at 9:35 PM, Thomas Kellerer <spam_eater@gmx.net> wrote:

Welty, Richard wrote on 12.04.2012 16:57:

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

Take the dumps of two schemas in plain format like this:

pg_dump.exe -Fp -U edb -s -n schema1 edb > db.out

pg_dump.exe -Fp -U edb -s -n schema2 edb > db1.out

Now you can use a tool like *Kompare [
http://www.kde.org/applications/development/kompare/]* to get a diffbetween two
schema files and see where there is a change in the schema.
--

Thanks & Regards,

Raghu Ram

EnterpriseDB: http://www.enterprisedb.com

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

thanks,
richard

Richard,

A java command line tool that we have used for years is AGPDIFF -
http://apgdiff.startnet.biz/

Usage is something like this ...
c:> java -jar apgdiff.jar --ignore-start-with pg_old.sql pg_new.sql >
diff.sql

It is PostgreSQL specific. Takes two SQL files and produces the diff.sql,
that we use as the basis of our production upgrade artifacts.

A very useful and basic tool..

HTH
-Damian

Hi,

about database schema migrations:

I am very happy with south http://south.aeracode.org/

It is written for django (python web framework), but could be used for database
migrations outside django, too.

Thomas Güttler

Am 12.04.2012 17:10, schrieb Chris Angelico:

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

--
Thomas Guettler, http://www.thomas-guettler.de/
E-Mail: guettli (*) thomas-guettler + de

Le 2012-04-12 10:57, Welty, Richard a �crit :

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

thanks,
richard

Hello,

Not free, but :
- powerful
- ease of use
- nice gui
- many more...

EMS DB Comparer for PostgreSQL
http://www.sqlmanager.net/en/products/postgresql/dbcomparer

You can download and evaluate, sometimes it is worth the price.
Hope that helps
Bruno

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

can anyone recommend an open source tool for diffing schemas?

You might want to check out the "same_schema" action of check_postgres.pl:

http://bucardo.org/check_postgres/check_postgres.pl.html#same_schema
http://bucardo.org/wiki/Check_postgres

It allows you to compare the schemas of any number of databases to
each other, and also compare a schema to an earlier version of itself,
which can be used for things such as mailing a list of all schema
changes in the last 24 hours, for example.

- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201204171202
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAk+NlAUACgkQvJuQZxSWSsjhrwCg7VgFxG8xh6+pfup4QMSxdtKz
tJcAoIkmtNRKOtYue5jRy9+FpTy6707u
=wwqY
-----END PGP SIGNATURE-----