XML Schema for PostgreSQL database

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

Thanks for your help,

Edson

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Em 14/12/2012 12:21, Merlin Moncure escreveu:

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.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, Dec 14, 2012 at 9:47 PM, Edson Richter <edsonrichter@hotmail.com>wrote:

Em 14/12/2012 12:21, Merlin Moncure escreveu:

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.

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.

Hi,

At the moment, the project is in prototyping phase. We are planning to dump
HTML from the database. Once it is ready for testing, we will let you know
how you can help(hopefully with testing!)

Regards,

Atri

merlin

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/**mailpref/pgsql-general&lt;http://www.postgresql.org/mailpref/pgsql-general&gt;

--
Regards,

Atri
*l'apprenant*

On Fri, Dec 14, 2012 at 10:17 AM, Edson Richter
<edsonrichter@hotmail.com> wrote:

Em 14/12/2012 12:21, Merlin Moncure escreveu:

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.

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

hrm, I just found schemaspy. It looks pretty nice.

merlin

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, 2012-12-14 at 14:17 -0200, Edson Richter wrote:

Em 14/12/2012 12:21, Merlin Moncure escreveu:

On Thu, Dec 13, 2012 at 5:52 PM, Edson Richter <edsonrichter@hotmail.com> wrote:

Em 13/12/2012 20:10, Merlin Moncure escreveu:
[...]

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

We had something pretty good like this called the Database Designer.
There are still some works to make it available on a next release.

You can also try pgModeler. Seems really cool. Too bad it doesn't (yet?)
do reverse engineering.

--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Em 16/12/2012 20:27, Guillaume Lelarge escreveu:

On Fri, 2012-12-14 at 14:17 -0200, Edson Richter wrote:

Em 14/12/2012 12:21, Merlin Moncure escreveu:

On Thu, Dec 13, 2012 at 5:52 PM, Edson Richter <edsonrichter@hotmail.com> wrote:

Em 13/12/2012 20:10, Merlin Moncure escreveu:
[...]

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

We had something pretty good like this called the Database Designer.
There are still some works to make it available on a next release.

You can also try pgModeler. Seems really cool. Too bad it doesn't (yet?)
do reverse engineering.

I'm looking forward for pgModeler. Reverse and Forward engineering (with
true alter scripts) are an requirement for me.
Visual design is desirable, but not mandatory.
Where can I look Database Designer? Which platform (or language it's
written)?
Maybe I can help with development, why start a new effort?

I've strong Java programming expertise (> 15yrs), and also have worked
with dozen different languages before.

Regards,

Edson

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sun, 2012-12-16 at 22:25 -0200, Edson Richter wrote:

Em 16/12/2012 20:27, Guillaume Lelarge escreveu:

On Fri, 2012-12-14 at 14:17 -0200, Edson Richter wrote:

Em 14/12/2012 12:21, Merlin Moncure escreveu:

On Thu, Dec 13, 2012 at 5:52 PM, Edson Richter <edsonrichter@hotmail.com> wrote:

Em 13/12/2012 20:10, Merlin Moncure escreveu:
[...]

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

We had something pretty good like this called the Database Designer.
There are still some works to make it available on a next release.

You can also try pgModeler. Seems really cool. Too bad it doesn't (yet?)
do reverse engineering.

I'm looking forward for pgModeler. Reverse and Forward engineering (with
true alter scripts) are an requirement for me.
Visual design is desirable, but not mandatory.
Where can I look Database Designer?

It's part of pgAdmin 1.16, but you need to compile pgAdmin with a
specific command line switch.

Which platform (or language it's written)?

Platform: Linux, Windows, Mac OS X, and even Solaris IIRC.
Language: C++

Maybe I can help with development, why start a new effort?

Sure, I'll be happy to have some help on this.

I've strong Java programming expertise (> 15yrs), and also have worked
with dozen different languages before.

pgAdmin is written in C++ and uses the wxWidgets toolkit. So no Java
here, sorry.

--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Edson Richter wrote on 14.12.2012 00:52:

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.

Do you mean TOAD the "SQL Tool" or TOAD Data Modeler (TDM)? TDM is far from perfect but quite nice actually.

You might also want to look at DbSchema: http://www.dbschema.com/

Thomas

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Em 17/12/2012 20:14, Thomas Kellerer escreveu:

Edson Richter wrote on 14.12.2012 00:52:

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.

Do you mean TOAD the "SQL Tool" or TOAD Data Modeler (TDM)? TDM is far
from perfect but quite nice actually.

TOAD Data Modeler.

You might also want to look at DbSchema: http://www.dbschema.com/

Thanks, I have tried that also. It's nice, but it is not a true forward
engineer (drop and create can't be called that, and the DbSchema does
not generate true change scripts - then it requires extra effort
reimporting data after changes - and depending on the change, it can't
even be applied by drop/create).

Regards,

Edson

Thomas

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general