Is it possible to add virtual objects to real database projects?

[aisbergde]

I add virtual objects to the databases in my SQL Server projects: PK, UK, FK (via virtual indices), virtual references between entities and between columns for a data lineage. This applies especially to views, which do not have such a thing "in real". The graphical representation of real objects together with virtual objects is then always a challenge. Would it be possible to use SchemaCrawler for this?

The "discussions" here are very empty. Is this the right place to ask such questions, or are there better places?

[sualeh]

@aisbergde Germo, this is a good place to have discussions. Are you using SchemaCrawler programatically? If yes, then you can can add attributes to any object using the setAttribute method. You can programatically retrieve this information, and use it to generate diagrams. Please take a look at how to use a Python script to generate diagrams in How to Generate dbdiagram.io Diagrams for Your Database.

[aisbergde]

@sualeh Thanks for the link to the article and the idea to look at dbdiagram.io and DBML!

So far I have only found SchemaCrawler (yesterday), but not used it with my databases yet. I will test that right now.

I'll have to look at setAttribute and then see which is easier: using setAttribute and then creating a DBML description in one piece with SchemaCrawler, or creating the additions for virtual objects directly in DBML and appending the description of the real objects.

I still want to combine this with the (preferably interactive) documentation of DataLineage. So "switching around" between ER representation with PK-FK, a display of object level references and the column level references. For columns Datalineage I could try Pseudo-FK instead of real FK.

[aisbergde]

Reading your other articles on dev.to I understand, that the best way will be to use setAttribute and to use SchemaCrawler to generate whatever output is required. And maybe the interactive usage can be used to create diagrams only for a focused part, to build some kind of graphical navigation. Or maybe there exists some output formats which already allow interactive navigation.

[aisbergde]

It looks like this setAttribute is something which can be used when programming in Java. I am a database developer but not a Java developer and I never programmed in Java. Are there other ways to bring additional attributes into the generated documentation? Anything which can be prepared in the database? I would be able to prepare views, tables, some kind of JSON, something in DBML. Whatever I could do in TSQL.

The tool is primarily aimed at users who work with databases. Couldn't it provide an interface that can be used by database users? Everyone should do what they are good at: Java developers should develop in Java, they do it faster and better than SQL developers. SQL developers are better in database operations. :-)

[sualeh]

@aisbergde - Please use SchemaCrawler v16.14.1, and see Extensions with Catalog Attributes for information on how to add column references.

[aisbergde]

I have already found this feature, and now it will be possible to define virtual FK. Thank you for remembering.

2 questions about this:

• would it also be possible to define virtual PK?
• would it also be possible to "disable" real FK?
  Why? This would allow to define several types of relation in different files:
  • FK (virtual FK will supplement real FK). This should be possible right now
  • data lineage between columns: column references instead of FK relations (real FK should be disabled to show only virtual relations)
  • data lineage between tables and views: real and virtual object references could be documented

Should I create Feature Requests for this?

• option to not show real FK but only virtual FK
• Attribute definition for virtual PK

[sualeh]

Yes, I will need two feature requests for this.

• To hide the foreign keys, I will see if I can add a feature that allows you to change config/schemacrawler.config/properties file, and change the commented line to schemacrawler.schema.retrieval.strategy.foreignkeys=none
• Attribute definitions for virtual PK will need to be added to the attributes file