ERD Generation using schemacrawler

[Git4geeks]

Hi,
I am trying to generate ER Diagram for a large Oracle Database for a schema comprising around 1k tables and using schemacrawler v16.16.15 along with Graphviz v2.40
We are not looking for an ERD for entire schema as it will be obviously not that readable, but for a given table.
Input is a single table name and the output should show diagrammatic representation for a particular table i.e. only significant columns, such as primary and foreign key columns for this table and directly related tables (having constraint columns).

I am using -grep-tables along with -info-level=standard --command=brief command-line options to achieve the same.

Command used:
schemacrawler.sh --command=brief --output-format=png --output-file=graph1.png --info-level=standard --server=oracle --host= --port= --database= --schemas=TESTUSER --user=TESTUSER --password= --no-info --loglevel=ALL --table-types=TABLE --no-remarks=true --only-matching --grep-tables=.*.TABLE1

But this command only displays the table given under grep-tables in a tabular diagram while only the name of the corresponding FK column from the parent tables (containing columns that form the foreign key) is shown. Sample output given below:

Requirement is to show significant columns from all the tables shown in the diagram (keeping referential integrity depth w.r.t. the input table as 1).

I also tried using the "parents" command line option. But the moment I use --parents=1, I start seeing the FK constraints of the parent table for other tables as well in my diagram which defeats my purpose.

Command used:
schemacrawler.sh --command=brief --output-format=png --output-file=graph2.png --info-level=standard --server=oracle --host= --port= --database= --schemas=TESTUSER --user=TESTUSER --password= --no-info --loglevel=ALL --table-types=TABLE --no-remarks=true --only-matching --grep-tables=.*.TABLE1 --parents=1

Output:

Expected output:

Hence we are looking for a way to hide non relevant constraints for the other tables (apart from TABLE1 in example above) but important columns from all tables (both TABLE1 and TABLE2) should be visible.
Any help on the same is appreciated.

[sualeh]

@Git4geeks - yes, this would have been addressed by the "only-matching" functionality that was removed in SchemaCrawler 16.15.1. It was making the code unnecessarily complicated. I will see if I can add this back in some other more elegant way.

[Git4geeks]

Thanks for the response @sualeh
I tried downloading Schemacrawler 16.14.6 and issued below command:
schemacrawler.sh --command=brief --output-format=png --output-file=graph2.png --info-level=standard --server=oracle --host= --port= --database= --schemas=TESTUSER --user=TESTUSER --password= --no-info --loglevel=ALL --table-types=TABLE --no-remarks=true --grep-tables=.*.TABLE1 --parents=1 --only-matching
But still getting same output as above (foreign keys that reference other non-matching tables are still being displayed).

[sualeh]

Let me see what I can come up with.

[sualeh]

Please use SchemaCrawler 16.16.16. See #741

[Git4geeks]

Hi,
Thanks a lot @sualeh
This works like a charm !!

I see in source although ForeignKeyRetriever::retrieveForeignKeys gets all foreign keys how beautifully it filters out other foreign keys in SchemaDotFormatter::printForeignKeys
Thanks again. Will mark this thread as Answered.

Two small requests (not required immediately though):
1.) --log-level=ALL doesn't seem to work with some latest releases (although other log levels like CONFIG, FINEST, etc are working fine). This was working fine some releases back.
2.) I think below Change History page needs a change for latest release:

[sualeh]

Thanks. I will take care of these also.

[sualeh]

@Git4geeks - I have fixed the release notes. I tested --log-level=ALL and it is working for me. Please could you share your command-line with me?

[Git4geeks]

Thanks for your response.
Apologies my mistake.
I was using loglevel without hyphen. With log-level it's working fine. Thanks.

[sualeh]

I did not notice that earlier, but now I see it in your command-line.

[chaitanyav28]

@Git4geeks am also trying to draw ER diagram for my project, am new to java ,if possible can you share some of the code to make use of it and i can extend

[chaitanyav28]

thanks in advance