From: Robert Klemme <shortcutter@...>
Date: 2010-07-28T17:15:22+09:00
Subject: Re: tool to compare DB schema against DDL file

On 28.07.2010 03:57, Fabian Marin wrote:
> First of all any feedback from you guys will be of tremendous help.
>
> I'm looking for a good topic for my thesis (to major in Computer
> Engineering) and at the same time I'm encountering a problem in my
> current job.  Simply stated:
>
> I have a DDL file that describes the entire Data Model of an
> application.
>
> We use 6 different environments (including production) with a DB that
> must (eventually) match the schema proscribed by the DDL.
>
> We delegate the execution of the DDL to a DBA; I work in a highly
> bureaucratic environment so I can't just go ahead and run DDL myself.
> Additionally the app itself is currently running Java, though I want to
> use Ruby to generate the tool I need right now.
>
> Such a tool would parse the DDL as an external DSL and then compare it
> with the current DB schema, to then generate a series of DDL statements
> that would synchronize the two, a sort of DDL diff, if you will.
>
> I've done my share of research, looking for a similar tool to avoid
> reinventing the wheel.  However, I could not reach the author of
> db_discovery (http://rubyforge.org/projects/db-discovery/), and
> otherwise I found no other author.  It seems like db_discovery did not
> find a niche even though it seems to be a very good tool.
>
> Does anyone know if such a tool already exists and is actively
> supported??? As a newbie Rubyist I'm trying to exploit the power of this
> community by querying you guys to make sure such a tool would actually
> be novel.

It wouldn't.  This is a solved problem already, for example Sybase has 
an excellent product which has sophisticated schema comparison 
functionality with a good graphical UI:
http://www.sybase.com/products/modelingdevelopment/powerdesigner

> As for its implementation I have a couple of ideas (e.g. parse DDL as an
> external DSL using a multiple dispatch library, use ActiveRecord to
> reflect on a database's schema).  However, I want to make sure that such
> a tool would actually benefit the community.

I would create these tools:

1. schema extractor into a standard format which you define (could be an 
XML representation or just marshalled object model).

2. diff for the standard format schema (two way, maybe also three way)

3. maybe also a converter from a given DDL to the standard format so you 
do not have to go through the DB although this might be fragile since 
the actual DB's settings may influence how the schema eventually looks.

Kind regards

	robert


-- 
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/