Virtual Relationships Redux

In the early part of 2016, we introduced a feature called DataMesh, also known as Virtual Relationships, or Virtual Foreign Keys. The idea behind this feature is to allow users to set up “virtual foreign keys” to relate data across tables in the same SQL database where foreign keys did not exist or were not desired, or across different SQL databases altogether. We even allowed relating a SQL database table to a MongoDB collection, opening up a whole new way for a client to retrieve all the data it needed, molded in a way that was easily consumable.

In hindsight, our first implementation was limited in the resources and directions that you could provision. Some of those limitations were:

• Only one virtual relationship could be defined per field on a particular table.
• No virtual relationship could be defined if a real foreign key constraint existed on that field.
• Relationships could only be defined from the perspective of the child to the parent, i.e. a “belongs to” relationship.
• Relationships could only be defined on SQL database services.

Release 2.4.1 removes these limitations and also expands this feature to other database services in the near future.

Provisioning Changes

A quick look at the Schema Manager UI changes should help clarify the new provisioning changes. The biggest change is how the virtual relationships are created. Previously this was done on the Field view with a “virtual foreign key” checkbox and selection dropdown menus. Now, relationships are added via the “Relationships” area of the Table view.

Clicking the “Add Virtual Relationship” button will present the user with the new interface for entering in the following information about the relationship:

• Name (name) - String. Auto-generated by the server based on the relationship type and designated services, tables, and fields. This is the string to use in the related URL parameter for record retrieval. If you wish to customize this name in the API usage, see the “Alias” attribute.
• Alias (alias) - String. Defaults to null. Set this to a string unique to this table, i.e. cannot be the same as any field or other relationships that are attributed to this table. If set, use this string in the related URL parameter for record retrieval instead of the name.
• Label (label) - String. Defaults to capitalized version of the name if not set. User-defined label for displaying in UI.
• Description (description) - String. Defaults to null. User-defined description for displaying in UI.
• Type (type) - Enumeration. Required. The type declares the direction of the relationship from the viewpoint of the currently selected table and behaves just as the relationships of similar types would from real SQL foreign key constraints. Possible values include:
  • “Belongs To” (belongs_to) - This table’s row can belong to one (or none) foreign table’s row, i.e. a Contact belonging to an Account. This relationship will return 0 or 1 related row.
  • “Has Many” (has_many) - The reverse view of the “Belongs To”, this table’s row can “have” many foreign table rows referring to it, i.e. an Account has many Contacts. This relationship will return 0 or more.
  • “Many to Many” (many_many)
• Field (field) - String. Required. The field name of the table in view that takes part in the relationship.
• Is Virtual (is_virtual) - Boolean. Defaults to false. If checked (true), this relationship is virtual, otherwise this is based on a intra-database foreign key constraint. Must be true to enable the rest of the fields below, otherwise these show as read-only.
• Reference Service (ref_service_id) - Integer. Required. Defaults to same service hosting the table in view. Identifier of the database service hosting the foreign end of the relationship.
• Reference Table (ref_table) - String. Required. Name of the table hosting the foreign end of the relationship.
• Reference Field (ref_field) - String. Required. Name of the field of the Reference Table participating in the relationship.
• Junction Service (junction_service_id) - Integer. Required and used only if type is “Many to Many”. Identifier of the service that hosts the junction table linking both sides of the relationship.
• Junction Table (junction_table) - String. Required and used only if type is “Many to Many”. The junction table linking both sides of the relationship.
• Junction Field (junction_field) - String. Required and used only if type is “Many to Many”. The junction table field that points to the table in view.
• Junction Reference Field (junction_ref_field) - String. Required and used only if type is “Many to Many”. The junction table field that points to the foreign/referenced table.
• Always Fetch (always_fetch) - Boolean. Defaults to false. When checked (true), this relationship is always returned regardless of URL parameter related value.

A look at the Schema API, shows a similar layout.

{

    "alias": null,

    "name": "pgsql.contact_by_assigned_to",

    "label": "Pgsql Contact By Assigned To",

    "description": null,

    "type": "belongs_to",

    "field": "assigned_to",

    "is_virtual": true,

    "ref_service_id": 31,

    "ref_table": "contact",

    "ref_field": "display_name",

    "junction_service_id": null,

    "junction_table": null,

    "junction_field": null,

    "junction_ref_field": null,

    "always_fetch": false

}

The relationship resources can be edited as part of the complete table in the schema API api/v2/<service_name>/_schema/<table_name> or from the _related sub-resource of that API (new in 2.4) api/v2/<service_name>/_schema/<table_name>/_related/[<relationship_name>]

Retrieving the Related Data

Once provisioned, the relationship is retrievable just like any of the relationships previously supported. Use the relationship name, or alias if provisioned, as part of the related URL parameter upon issuing the request, which works in GET requests as well as the other verbs where fields and related can be returned.

GET api/v2/<service_name>/_table/<table_name>?[related=<relationship_name>]

For example…

GET api/v2/mysql/_table/todo/21?related=pgsql.contact_by_assigned_to

{

    "id": 21,

    "name": "Purchase party platter",

    "complete": false,

    "assigned_to": "Jane Smith",

    "pgsql.contact_by_assigned_to": {

	    "id": 13,

	    "first_name": "Jane",

	    "last_name": "Smith",

	    "display_name": "Jane Smith",

	    "birth_date": null,

	    "last_contact": null,

	    "active": true,

	    "imageUrl": null,

	    "notes": null,

	    "created_date": "2016-11-28 20:07:50.233078",

	    "last_modified_date": "2016-11-28 20:07:50.233078",

	    "created_by_id": 1,

	    "last_modified_by_id": 1

	}

}

All other previously supported aspects of database relationships are available across virtual relationships as well. For more on our database services, check out the docs.