Connection with virtual database whose content can be changed dynamically in the backend

[aisbergde]

New Feature Request

Describe the solution you'd like

The tool seems to be very well suited to generate different types of output. Currently, the tool can connect to various real databases and create diagrams for them. For this purpose, the tool probably uses system views for metadata of the real databases. One can filter interactively with the tool.

I would like to connect the tool with virtual databases: In a virtual database I can dynamically define what objects are included and what relationships between these objects. So instead of using the tool to filter, the tool would always document the whole virtual database. And depending on which objects I include in this virtual database and how I connect them with relations, I can always use the same commands in the tool, but the generated output will change each time.

So I can also simulate a navigation along the relationships. Sometimes everything is displayed: A-B-C-D-E, sometimes only A-B-C, or only B-C-D and so on.

I could define and display virtual PK for views.

Another advantage would be that I could display different relations: not only FK-PK, but I could use data lineage between objects (referencing-referenced) as relations and display them like FK-PK relations. By changing the virtual database, one then simply switches between the representation of different relationship. The tool will just interpret and report them like FK-PK:

• FK-PK (a combination of real and virtuell relations, for example between views)
• referencing entity - referenced entity
• referencing column - referenced column.

The metadata of this virtual database is ideally provided via views, so that one can dynamically change the content of these views.

Describe alternatives you've considered

• Program a frontend yourself (but I can't: I am not an application developer but a database developer)
• Search for ready-made frontends that can display relations stored in graph tables (node, edge) and show nodes like in ER diagrams.

[sualeh]

I am not sure what exactly you mean by a “virtual database”. SchemaCrawler will always connect to the “real database”, filter out certain database objects, then figure out weak associations, then read in table and column remarks or comments, and in the future possibly also weak associations between columns. Anything other than this would be outside the purview of SchemaCrawler.

The closest that SchemaCrawler comes to this is the SchemaCrawler interactive shell. You can connect to a “real database” and save a snapshot of the medata locally. Later on you can use SchemaCrawler to work with the saved snapshot, even if you no longer have a connection to the “real database” any more. Please see the Katacoda tutorial on the SchemaCrawler interactive shell.

[sualeh]

@aisbergde - SchemaCrawler v16.14.1 (soon to be released) has support for loading in column references from a YAML file.

[aisbergde]

@sualeh Thank you for this information! I will check again if this is released, how to use this and if I can read more than system schemas and dbo from my SQL Serer databases.

[sualeh]

SchemaCrawler 16.14.1 is released. Please take a look at the website for information on how to use this feature.