Facing issues in depicting weak associations using schemacrawler

[Git4geeks]

Hi,
I am back with another question. I am trying to explore possibility of displaying weak associations between columns where foreign key constraint is not explicitly defined in tables in my Oracle database.
I am using SchemaCrawler v16.16.16 on Linux. I am referring weak-associations-readme.html available with schemacrawler download.
I have Java available on my PATH, I don't have jackson package available in my yum repo, I am only having Jackson jars available in the schemacrawler lib/ directory on my CLASSPATH.
In my config, I am having:
schemacrawler.format.hide_weakassociation_names=false
I am trying to load a weak association with TABLE1's COL3 refering TABLE2's COL4:

Sample weak-associations.yaml I am using is as below (Hope syntax is fine):

name: catalog
weak-associations:
- name: multi_line_remarks
  referenced-table:
    catalog:
    schema: TESTUSER
    name: TABLE2
  referencing-table:
    catalog:
    schema: TESTUSER
    name: TABLE1
  column-references:
    COL4: COL3
  remarks:
  - "Some remarks line 1"
  - "Some remarks line 2"

Catalog I am having as blank as for my oracle db, there is no catalog maintained.
Issue i am facing is as below:

But when I give below command:
schemacrawler.sh --command=schema --output-format=png --output-file=Test1.png --info-level=standard --server=oracle --host= --port= --database= --schemas=TESTUSER --user=TESTUSER --password= --log-level=CONFIG --table-types=TABLE --no-remarks=true --attributes-file=./weak-associations.yaml
There are no weak associations loaded

When I additionally try to use option --weak-associations=true, then the diagram becomes more cluttered with some additional metadata info coming in (looks like system constraints, not sure though)
Command:
schemacrawler.sh --command=schema --output-format=png --output-file=Test1.png --info-level=standard --server=oracle --host= --port= --database= --schemas=TESTUSER --user=TESTUSER --password= --log-level=CONFIG --table-types=TABLE --no-remarks=true --weak-associations=true --attributes-file=./weak-associations.yaml

But still I am not able to see weak association which I have defined with TABLE1's COL3 refering TABLE2's COL4

My final goal is to use weak-associations along with grep-tables option and schemacrawler.graph.show.foreignkey.filtered_tables=false in the config.

So like if I use below command:

schemacrawler.sh --command=schema --output-format=png --output-file=Test.png --info-level=standard --server=oracle --host=--port= --database= --schemas=TESTUSER --user=TESTUSER --password= --log-level=CONFIG --table-types=TABLE --no-remarks=true --only-matching --grep-tables=.*.TABLE1 --parents=1 --attributes-file=./weak-associations.yaml

Expected output is:

Similarly, if I use --grep-tables and --command=brief along with schemacrawler.graph.show.foreignkey.filtered_tables=false, it should display only the constraint columns of the grepped-tables plus any relevant columns for tables defined in weak associations.
Any help with the same is appreciated.

[sualeh]

@Git4geeks - let me take a look.

[Git4geeks]

Hi @sualeh
I changed my yaml file as below:
column-references:
COL3: COL4
so able to get below Diagram when i use --command=schema:

This looks correct

But moment I use option --command=brief to hide non constraint columns, this week association gets shifted to wrong columns.

[sualeh]

@Git4geeks - please send me the DDL for your sample schema, and your YAML. I will take a look.

[Git4geeks]

PFA..
TESTUSER_DDL.sql.txt
weak-associations.yaml.txt

[sualeh]

@Git4geeks - thanks for finding this issue, and for sharing your test schema. I will fix the bug where there is a hanging weak association in the diagram. However, you will not be able to show this kind of weak association with the brief command, since the brief command eliminates certain columns. If you define a weak association with columns that are shown in the diagram, the weak association will be shown with it.

[Git4geeks]

Hi @sualeh
I was trying below:
1.) Using command=brief and specifying a weak association for a column which is not currently a significant column (not a part of primary key or foreign key or unique index) for my table. But schemacrawler doesn't show association for such columns.

This I understand from your response above will not be achievable.

2.) Also I tried executing below command:
schemacrawler.sh --command=schema --output-format=png --output-file=Test.png --info-level=standard --server=oracle --host=--port= --database= --schemas=TESTUSER --user=TESTUSER --password= --log-level=CONFIG --table-types=TABLE --no-remarks=true --only-matching --grep-tables=.*.TABLE1 --parents=1 --attributes-file=./weak-associations.yaml

But now in my yaml, I have specified a weak association of Col3 of Table 1 with Col3 of Table3.

name: catalog
weak-associations:
- name: TABLE1_TABLE3_weak
  referenced-table:
    catalog:
    schema: TESTUSER
    name: TABLE3
  referencing-table:
    catalog:
    schema: TESTUSER
    name: TABLE1
  column-references:
    COL3: COL3

Here although I am using command=schema, but schemacrawler doesn't show the weak association since Table3 is not directly related to Table1 (No column of Table1 carries no referential integrity constraints for any Column of Table 3). Since I am using "--parents=1" option, hence the weak association is not displayed.

I understand that this might be happening because weak association loader phase happens after the grepping and filtering phase is already done.
But ours is a very large and complex schema, and I was hoping to show referential integrity depth of only 1 for any given table (only significant columns to be shown) plus there should be a mechanism to show weak associations with columns of some other tables in my schema not directly related to the given table (where foreign key constraint is not explicitly defined in the database).
Will it be possible to achieve this in any way? Thanks!

[sualeh]

1. Correct. And I have fixed the bug with a hanging weak association being shown in the diagram.
2. Let me take a look, and see what is going on.

[sualeh]

This is the output from SchemaCrawler 16.16.17. I will release this version as soon as you confirm that this the behavior you would like to see.

1. With "schemacrawler.graph.show.foreignkey.filtered_tables=true"
   

2. With "schemacrawler.graph.show.foreignkey.filtered_tables=false"
   

[Git4geeks]

Thanks for the response @sualeh
But I think above output I will get from SchemaCrawler version 16.16.16 also as no change is done to this part in new version. Pls correct me if I am wrong.
Somehow I was looking for a way to have

schemacrawler.graph.show.foreignkey.filtered_tables=false

and eliminate FK constraints of Table 2 to Tables 3,4 and 5. But since I have specifically mentioned weak association for Table1.Col3 and Table3.Col3, that association alone to be shown as dotted line. Will elaborate more on this tomorrow.

[sualeh]

I understand what you are looking for, but this is too much special logic which will complicate SchemaCrawler for one particular use case. It is a strange case, where you want to hide hanging foreign keys, but show hanging weak associations. I think you will have to pick between the two options above if you want out of the box functionality.

The other option I can think of is to script modifications the Graphviz DOT file after the fact. That is, generate the DOT file as is (using --output-format=scdot), and without loading the in attributes YAML. Then at the bottom of the DOT file append all the additional relationships you want to show before you invoke Graphviz to generate the diagram image. This way you can have the best of both worlds - you can use the "brief" command, as well as show your custom metadata on the diagram. You can even do funky things like color-coding your implied relationships.

[sualeh]

@Git4geeks - please use SchemaCrawler 16.16.17 anyway, since I have fixed the issue mentioned above.

[Git4geeks]

Thanks @sualeh for the suggestion.
Will use v16.16.17 and will generate required diagrams by modifying the Graphviz dot file. Many thanks.