Re: XML Schema for PostgreSQL database - Mailing list pgsql-general

On Thu, Dec 13, 2012 at 5:52 PM, Edson Richter <edsonrichter@hotmail.com> wrote:
> Em 13/12/2012 20:10, Merlin Moncure escreveu:
>
>> On Thu, Dec 13, 2012 at 1:54 PM, Edson Richter <edsonrichter@hotmail.com>
>> wrote:
>>>
>>> Has anyone created a XML Schema that would represent PostgreSQL database
>>> with all (or at least, major) structures?
>>
>> no -- furthermore, why would you want to?  what would be the consumer
>> of this 'schema'?
>>
>> merlin
>>
>>
>
> I was wondering to create a tool for diagramming and database forward
> engineering.
>
> There are already few tools around.
>
> If you know a good diagramming tool able to database diff and forward
> engineering (with "ALTER ...", not "DROP and CREATE"), I would like to know
> (by today I do use one commercial tool that is feature incomplete:
> DbWrench).
>
> Among others, I've considered also:
> - Sybase PowerDesigner: too expensive, does not support PostgreSQL 9.1/9.2,
> so is not appropriate.
> - ERWin: too expensive, and doesn't have proper support for PostgreSQL
> 9.1/9.2.
> - NaviCat: is feature extensive, but they don't have real change scripts
> (are drop/create).
> - ModelRight: it's "change script" is not change at all (is just another
> drop/create tool).
> - TORA and other open source tools are really incomplete.
> - TOAD is too confuse for simple day-by-day work.
>
> Most of these tools or doesn't support PostgreSQL features (are too
> generic), or doesn't do real forward engineer (are only able to drop/create
> objects, not alter them), or cannot deal with partial diagrams (I can't deal
> with only one diagram with hundred of tables at once).

Years ago I decided that the only way to do forward engineering was to
capture the changes I make to development databases in scripts and to
manually apply those scripts for release management.  This process
works and like you I've found the various commercial tools to have
various weaknesses.  So for forward engineering I say: quit using
tools and write scripts.

I'm also like you amazed how poor the various database diagramming
tools are -- they all suck.  Case Studio used to be pretty good back
in the day but I wouldn't recommend it today.  My personal take on
ERD/diagramming is that:

*) diagram generation should be automatic and useful

*) human input to adjust the layout should not be required (every time
I move the stupid boxes and straighten the stupid lines I feel like
I'm finger painting)

*) diagram tool should follow database changes and adjust the diagram
accordingly

*) diagram output should be standard html (only) without requiring
tool to log in and adjust diagram

I've come around to the point of view that this is an unfilled niche
in the industry.  Furthermore, as long as scope is kept reasonably
down, this is not a very difficult project.  So I've decided (along
with Atri) to give it a shot.  Iniitial plans is to do plain html
dumps directly out of the database and use GraphVis to document
dependency flow.

merlin