Diagram: unwanted connections between primary keys with same names

[akkinitsch]

First of all i'ver git to thank you for your great work. SchemaCrawler helps me a lot to get a better overview about existing and new databases i've got to work with. Thank you very much for this.

Here comes my question/problem: I've got a database i want to analyse that has got a lot of tables and a lot of that tables have got primary keys with the same name. These primary keys are valid for their corresponding table only and there is no logical connection to the primary keys of the other tables.

When analysing the database and drawing a diagram, SchemaCrawler connects these primary keys of all tables - that generates a lot of clutter in my diagram.

To illustrate my question i created a small test-db with the following commands:

CREATE DATABASE SchemaCrawlerTestDB;

CREATE TABLE testtable1 ( id integer PRIMARY KEY, field1 integer, field2 varchar(10) );

CREATE TABLE testtable2 ( id integer PRIMARY KEY, field1 integer, field2 varchar(10), fieldref integer REFERENCES testtable1(id) );

CREATE TABLE testtable3 ( foobar integer PRIMARY KEY, field1 integer, field2 varchar(10), id integer );

CREATE TABLE testtable4 ( foobar integer PRIMARY KEY, field1 integer, field2 varchar(10), id integer );

CREATE TABLE testtable5 ( foobar integer PRIMARY KEY, field1 integer, field2 varchar(10), id integer );

And this is the diagram SchemaCrawler generates for me: 172.16.11.55_SchemaCrawlerTestDB.pdf

What i expected was a connection between testtable1 and testtable2 (between primary key and foreign key). What i did not expect were the connections between the primary keys (with same name) of tables testtable3, testtable4, and testtable5.

Is there a command to suppress these connections between primary keys with same name when drawing a diagram?

I am using SchemaCrawler version 14.0.8 with PostgreSQL version 9.2.14 and PostgreSQL Native Driver PostgreSQL 9.4.1208

Thanks for your help

[schemacrawler]

Please use -infolevel=standard. Also, please provide the entire command-line. Thanks.

[akkinitsch]

That is something i forgot to mention: i already tested that parameter and in my little testcase it does exactly what i need: no more connections between primary keys with same name.

Whe i try to generate the diagram for the db i've got to analyse with -infolevel=standard, the result is a pdf-document in same size but without any content (blank white sheet).

My entirecommand-line: call java -classpath ./schemacrawler/_schemacrawler/lib/*;lib/* schemacrawler.Main -host=172.16.11.55 -server=postgresql -database=testDB -user=testUser -password=testPassword -infolevel=maximum -command=graph -outputformat=pdf -outputfile=./diagrams/172.16.11.55_testoutput_infolevel_maximum.pdf

My next test was to create png-files instead of pdf. With -infolevel=maximum i got the same output as in the generated pdf: connections between primary keys with same name. With -infolevel=standard i got an image with all tables but only one connection between a foreign key and a table referencing to that foreign key (there are more than one foreign-key-connections in that database.

[schemacrawler]

There are a few things going on here. To hide the inferred relationships - "weak associations" - that are shown as dotted lines in the diagram, always use =infolevel=standard.

Also,

1. GraphViz has a bug where it fails to correctly generate PDF files for large databases. PNG output usually works in this case. The other option, if you want to zoom into diagrams, is to generate in DOT format, and use a GraphViz visualizer that you find on the internet. A better way to go is to use SchemaCrawler grep functionality to create multiple, targeted diagrams.
2. I am not sure why SchemaCrawler is not showing all the foreign-keys in your database. In order to research that, I will need the full log. You can generate this by using an extra command-line parameter, -loglevel=ALL, and send me the zipped output by email to sualeh@hotmail.com

[akkinitsch]

Thanks for the explanations - another workaround regarding diagrams of large databases that will work for me is to generate svg and to convert it (i.e. via Inkscapes CLI) to pdf. I prefer pdf because i like to attach them to other pdf-reports. All the steps are running on a Jenkins CI so they run automaticaly for me after set up and then i do not have to care anymore.

[schemacrawler]

Good. Glad you worked around the GraphViz bug. Do you still have the issue with some foreign keys not showing on the diagram?