Re: XML Schema for PostgreSQL database

Em 14/12/2012 12:21, Merlin Moncure escreveu:
> On Thu, Dec 13, 2012 at 5:52 PM, Edson Richter <edsonrichter(at)hotmail(dot)com> wrote:
>> Em 13/12/2012 20:10, Merlin Moncure escreveu:
>>
>>> On Thu, Dec 13, 2012 at 1:54 PM, Edson Richter <edsonrichter(at)hotmail(dot)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.

Yes, I've developed special tasks to update database automatically based
on schema version. But this becomes a hard work very quick (because
system grows too fast and we don't have dedicated DBA to deal with all
those changes).

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

Yes - also, tool must have multi diagram support, in order we can
organize different views of the structure.

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

Yes, I also hate that. DBWrench (tool I use today) has no auto layout of
any kind, and I feel like a fool arranging tables when I'm supposed to
do something that add value to our customers.

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

Yes - database diff between database and model design with true change
scripts. If I change the name of a column, I do expect "Alter table XXX
rename column YYYY to OOO" (or something like that, I can't remember the
syntax right now).

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

I don't really care about output - SVG would be excellent, because it
scales well and will print nicely.
I can tell that a data dictionary with diagramming output would be
enough. If I can edit the data dictionary and system keep track of all
changes, would be enough for me.
If PgAdmin would have the ability to maintain a data dictionary that is
not the database itself, and plus the feature to "synchronize with
database" (which would be any PostgreSQL database registered) would be
optimal for me.

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

That was my thought - if it's not too expensive, I can do something.
That's my question regarding XML, would be very easy for me to work with
it using Java (my preferred language, which I use on daily basis).
XML would work well with versioning systems, and is quite easy to "diff"
etc.

If there is anything I can help, let me know.

>
> merlin
>
>