Re: Schema tool - Mailing list pgsql-general

Thanks, each of you for all the suggestions on schema generating tools.  The idea is to have something which will
connectto the database and automatically make a schema from what you've got.  Here's what I have had a chance to
evaluateso far... 

DBVisualizer - It does a good job with the default placement of tables and links and some nice features for what to
include/excludefrom the diagram.   The ability to select specific tables is very handy since I have a few tables which
Iplan to get rid of as soon as I have the data moved to a different database.  Also, part of the point of what I'm
doingis to support certain other people who will want to write queries.  They each have specific interests and don't
needall the tables.  I will probably make more than one simplified version of the schema, each focused on a specific
setof data.   

Drawbacks:  It missed one foreign key relationship.  I don't know why but that link is missing from the diagram.  SQL
PowerArchitect (see below) did see and diagram this relationship.  Links get a little jumbled when I move things around
alittle and then switch back and forth between different kinds of views (hierarchic, organic, orthogonal, etc.)  That's
nottoo difficult to fix because all you have to do is drag each table slightly and the links get redrawn in a more
optimalway.  It doesn't have the turkey foot (or whatever you call it) to indicate a many-to-one relationship.  I
supposethat's not a big deal since the relationships are pretty obvious in my database, with each foreign key link
goingto the primary key of another table.  One very minor thing is that there are some places where I want to indicate
thata foreign key is specified with "on update cascade." I will have to export to graphics and add that note to the
diagram.  

The one big thing is that it displays views as disconnected objects.  Views are very important for this project and it
wouldbe good to indicate where their contents are coming from.   I'm not sure how best to diagram that since many of
theview columns are taking several columns from various tables and performing a mathematical computation.  To
understandwhat is in each column of the view, you need to see both the inputs and the formula.   

SQL Power Architect - also free and open source - This was not actually suggested on this list but some of the
suggestionsled me to the right key words to do another search and find it.  As I understand, the main point of this
toolis to transfer data from one database instillation to another.  For example, if you want to migrate data from MS
SQLServer to PostgreSQL, this is a tool you should look at.  Deriving a schema is just one element of this.  You
connectto the source database and it reverse engineers it for you and shows you the results.  You make changes and
selectionsof what you want to move, etc.  Then you hit "Forward Engineer" and it moves the data to your target,
accordingto your edited schema.  The advantage is that it shows lots of information and looks to be good for figuring
outenvironments with lots of different kinds of databases all over the place. It has more features to change appearance
ofthe diagrams than DbVisualizer does, including colors of the text and the table, itself.  It can export to HTML.   

Drawbacks:  It does not put the tables in a convenient layout by default.  You can't easily exclude objects but you can
deletethem from the diagram.  Links point to any place on the tables and not the specific columns which they refer
to/from. You can drag the links around so that they do point to the exact column but this can be frustrating drudge
worksince each drag moves both ends of the link and you can easily mess up corrections you made earlier.  For some
reasonit didn't detect the correct datatypes for many rows and just put "CLOB" where it should be INTEGER, DATE, etc.
SQLPower Architect also displays views as disconnected objects. 

SchemaSpy looks to be a good option since they mention that they had to do some custom work to support views but I'm
havingtrouble getting it to work at this point. 

dbWrench by Nizana looks interesting but it's commercial and I want to see if a free option will work first.  I'm not
totallyagainst spending money but I have seen situations where free stuff ends up being as good or better than
commercial.  

MicroOLAP Database Designer and PostgreSQL Maestro are Windows only, unless I missed something.  I do have both WINE
anda Windows VM under VirtualBox but I would prefer something Mac native.   

I haven't had a chance to check out Mogwai yet.

-Aram