Thread: Schema tool
A while back, I thought I remembered seeing a Mac OS X client for PostgreSQL which had a feature where it would display agraphic schema of whatever database you connect to but I can't seem to find it again (web searching.) I did come acrossone post which said that this was a planned feature for pgAdmin. I need to do a formal schema of a database whichI have in development and it would be nice to have an automated tool, whether it connects to the database or uses SQLtable definition code to generate graphics. Is there such a thing? if not, what do you all use? OpenOffice Draw? OmniGraffle? -Aram
Hey Aram,
I recommend dbWrench by Nizana. It has a nice synchronization capabilities,
forward / reverse engineering and supports many built-in PostgreSQL types
and user-defined types as well.
NB: it is commercial application.
Another alternative is a MicroOLAP Database Designer.
The both tools are not require running PostgreSQL server.
If you wish to work with database directly you may look at tools like
PostgreSQL Maestro.
Good luck!
--
// Dmitriy.
I recommend dbWrench by Nizana. It has a nice synchronization capabilities,
forward / reverse engineering and supports many built-in PostgreSQL types
and user-defined types as well.
NB: it is commercial application.
Another alternative is a MicroOLAP Database Designer.
The both tools are not require running PostgreSQL server.
If you wish to work with database directly you may look at tools like
PostgreSQL Maestro.
Good luck!
2010/11/11 Aram Fingal <fingal@multifactorial.com>
A while back, I thought I remembered seeing a Mac OS X client for PostgreSQL which had a feature where it would display a graphic schema of whatever database you connect to but I can't seem to find it again (web searching.) I did come across one post which said that this was a planned feature for pgAdmin. I need to do a formal schema of a database which I have in development and it would be nice to have an automated tool, whether it connects to the database or uses SQL table definition code to generate graphics. Is there such a thing? if not, what do you all use? OpenOffice Draw? OmniGraffle?
-Aram
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
// Dmitriy.
On 11/11/2010 09:50 AM, Aram Fingal wrote: > A while back, I thought I remembered seeing a Mac OS X client for PostgreSQL which had a feature where it would displaya graphic schema of whatever database you connect to but I can't seem to find it again (web searching.) I did comeacross one post which said that this was a planned feature for pgAdmin. I need to do a formal schema of a database whichI have in development and it would be nice to have an automated tool, whether it connects to the database or uses SQLtable definition code to generate graphics. Is there such a thing? if not, what do you all use? OpenOffice Draw? OmniGraffle? > > -Aram DbVisualizer has a free and a commercial release and can do a decent job of diagramming a schema. Nothing compared to Embarcadaro, put not as pricey either.
Le 11/11/2010 18:58, Rob Sargent a écrit : > > > On 11/11/2010 09:50 AM, Aram Fingal wrote: >> A while back, I thought I remembered seeing a Mac OS X client for PostgreSQL which had a feature where it would displaya graphic schema of whatever database you connect to but I can't seem to find it again (web searching.) I did comeacross one post which said that this was a planned feature for pgAdmin. I need to do a formal schema of a database whichI have in development and it would be nice to have an automated tool, whether it connects to the database or uses SQLtable definition code to generate graphics. Is there such a thing? if not, what do you all use? OpenOffice Draw? OmniGraffle? >> >> -Aram > > DbVisualizer has a free and a commercial release and can do a decent job > of diagramming a schema. Nothing compared to Embarcadaro, put not as > pricey either. > I actually use DBVisualizer when I need to get a graphical view of an existing database. BTW, this is a planned feature of pgAdmin. We have an interesting patch from a GSoC student (Luis Ochoa), but it still needs (a lot of) work. I still hope to include it for the next release. -- Guillaume http://www.postgresql.fr http://dalibo.com
Hello, may schemaspy help you ? http://schemaspy.sourceforge.net/sample/relationships.html HTH, Marc Mamin -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Aram Fingal Sent: Donnerstag, 11. November 2010 17:51 To: Postgres-General General Subject: [GENERAL] Schema tool A while back, I thought I remembered seeing a Mac OS X client for PostgreSQL which had a feature where it would display a graphic schema of whatever database you connect to but I can't seem to find it again (web searching.) I did come across one post which said that this was a planned feature for pgAdmin. I need to do a formal schema of a database which I have in development and it would be nice to have an automated tool, whether it connects to the database or uses SQL table definition code to generate graphics. Is there such a thing? if not, what do you all use? OpenOffice Draw? OmniGraffle? -Aram -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On 11/11/10 12:45, Marc Mamin wrote: > Hello, > > may schemaspy help you ? > http://schemaspy.sourceforge.net/sample/relationships.html > > HTH, > > Marc Mamin Thanks for this link! I've been looking for something that can run on the command line for quite a while, and even better it outputs to html and shows me some, um, interesting things in my database that probably need addressing. Warm regards from snowy Wyoming! Jeff Ross
Marc, > may schemaspy help you ? > http://schemaspy.sourceforge.net/sample/relationships.html Thank you *VERY* much for suggesting this tool! -- Gary Chambers /* Nothing fancy and nothing Microsoft! */
Thanks, each of you for all the suggestions on schema generating tools. I haven't had a chance to evaluate them all yetbut DBVisualizer looks pretty good. In the meanwhile I found SQL Power Architect, which is also free/open source, andcan do this kind of diagraming but is not as good as DBVisualizer for my purposes. It has some other interesting featureswhich would be of interest to someone working in an environment with several different kinds of databases. SchemaSpylooks to be a good option since the description mentions that they had to implement product-specific queries tosupport views but I haven't had a chance to try it out yet. Support for views is important to me and DBVisualizer andthe other tools I have looked at so far, just display them as disconnected objects. I was thinking of reporting back to this forum with advantages/disadvantages of each tool, as I see it, but realized thatI was rapidly getting too far off topic for a list focused specifically on PostgreSQL. -Aram
Aram Fingal wrote on 11.11.2010 22:45: > I was thinking of reporting back to this forum with > advantages/disadvantages of each tool, as I see it, but realized that > I was rapidly getting too far off topic for a list focused > specifically on PostgreSQL. I don't think this woul be off-topic here if you post your experience using those tools together with PostgreSQL Actually I think it would be worthwhile documenting your experience in the PostgreSQL Wiki as well: http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools Regards Thomas
Thomas Kellerer <spam_eater@gmx.net> writes: > Aram Fingal wrote on 11.11.2010 22:45: >> I was thinking of reporting back to this forum with >> advantages/disadvantages of each tool, as I see it, but realized that >> I was rapidly getting too far off topic for a list focused >> specifically on PostgreSQL. > I don't think this woul be off-topic here if you post your experience using those tools together with PostgreSQL Agreed, that seems well within the list's area of interest. If you told us about some tool that couldn't be used with PG, maybe we'd get impatient. regards, tom lane
> -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > owner@postgresql.org] On Behalf Of Marc Mamin > Sent: Thursday, November 11, 2010 11:46 AM > To: Aram Fingal; Postgres-General General > Subject: Re: [GENERAL] Schema tool > > Hello, > > may schemaspy help you ? > http://schemaspy.sourceforge.net/sample/relationships.html This thing is also nice: http://sourceforge.net/projects/mogwai/
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
On Nov 11, 2010, at 4:56 PM, Thomas Kellerer wrote:
Actually I think it would be worthwhile documenting your experience in the PostgreSQL Wiki as well:
http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools
Thanks, I will post something there after I have done some more investigation. I notice that some of the ones mentioned here are on there but with only a line or two of description. I'll also see about putting something about PGnJ. That's a free, generic query tool which I have been using with PostgreSQL.
-Aram