Virtual Links

[PRO feature]

In the realm of database design, foreign keys play a crucial role in enforcing relationships between tables. However, developers sometimes choose not to use explicit foreign key constraints due to various reasons, so it becomes harder to work with such data. The “Virtual Links” feature aims to recognize potential foreign keys based on field names using regular expression patterns. The virtual links are not saved in the database schema, they exist only within the Valentina Studio and can be used in the following tools like the ordinary links:

• SQL Editor – for autocompletion of JOIN conditions
• Data Editor – for exploring related data
• Query Editor – to construct queries without the need to define links manually
• Diagram Editor – for viewing relations between tables

Virtual Links work with all relational databases supported by Valentina Studio PRO. It does not work with MongoDB, which is not a relational database.

You can see a database structure diagram without defined foreign keys in the following picture:

In this case, potential foreign key field names follow a strict pattern <parent_table>_ptr → <parent_table>.id, so it can be used to recognize virtual links between tables automatically.

Open the context menu of the database and click Setup Virtual Links…

The following dialog will be shown:

It contains a global set of rules used to recognize virtual links divided into the following columns:

• Checkbox – If checked, the rule is used for the selected database
• Child Table – For all rules, it contains \0 pattern, representing a table where a potential foreign key field is located.
• Child Field – A regular expression used to identify the parent table from the child field name.
• Parent Table – A regular expression used to generate the parent table name based on the child field name.
• Parent Field – A regular expression used to generate the parent field name based on the child field name.
• Link Name – A pattern to generate a virtual link name.

Buttons above the table allow adding and removing rules.

The first rule matches the schema of the database, so check it an click button Test.

Entries satisfying the rules will be added to the same table:

Click OK to accept changes, now the links will be shown in all supported tools, such as Schema Editor:

An example of database without defined foreign keys but with a strict naming schema is Mantis bug tracker database.

A diagram shows established virtual relations:

Sometimes, database schema object naming doesn't follow strict rules, and in such cases, virtual links can be created manually.

For example, in the schema shown in the following picture, it is difficult to establish rules for the automatic creation of foreign links.

In the context menu of the database or table click Create→Virtual Link…:

Setup the properties of the link and click Create:

A new link is shown in the Schema Editor:

Virtual Links can be synchronized across multiple devices using the built-in mechanism. To do this, open the Preferences and select the Sync page (or simply click the Sync button on the main toolbar), choose the synchronization method, and check the Virtual Links checkbox.

An example of a database without defined foreign keys and lacking a strict naming schema is the Joomla CMS database. Additionally, different plugins may utilize various naming rules.

Data Editor:

SQL Editor:

Query Editor: