Data Model Introspection

Data Model Introspection

  
Introduction

At the end of 2015 next step, someone from the audience asked about how can we introspect the data model and find out to which entity and primary key a foreign key belongs to.

Although I did not have an answer at the time I was convinced this would be possible. We have plenty of examples where the platform does this but mainly they are inside the development tool. 

At first glance the system entities did not reveal any obvious connection between entities, primary and foreign keys.

Data Model

The data model is fairly strait forward. A module has many entities which have many attributes.

The Espace_Entity  identifies which entities are referenced on your module. 

Primary Keys

Besides the traditional integer auto number primary key ID you have probably noticed some SS_Key attributes. These are GUID like and also represent a unique way of identifying a record.

Notice how the Entity has a PrimaryKey_SS_Key attribute, this will allow you to identify from the Entity which of its attributes is the Primary Key.(Entity.PrymaryKey_SS_Key = Entity_Attr.SS_Key)

Foreign Key Data Type

On the Entity_Attr notice the Type. This attribute represents the type of the entity attribute (rtText, rtBoolean, rtInteger, etc).
However if you do a select distinct on these types you'll notice some types that look like  this:
bt478870b9-2d60-4f73-9eb3-7cd8b994a737*d028b5ef-4477-4e37-9fa2-cddd7e0e7146
 

·       The bt indicate this is a foreign key
·       The 478870b9-2d60-4f73-9eb3-7cd8b994a737 is the module (espace) SS_Key who own this entity
·       The * is a separator
·       The d028b5ef-4477-4e37-9fa2-cddd7e0e7146 represents the entity

This allows us to identify that this attribute if of type entity identifier, which module, which entity.

Combining this with how to identify primary keys in entities and we have reached our goal. 

Attributes Example

In the Attributes Example you will see a list of Modules (eSpaces), then a list of the selected module Entities and from there the list of Attributes of the selected entity.

Notice how the system is able to identify foreign keys or attributes of type entity



Foreign Keys Example

In the Foreign Keys Example you will see a list of Entities of this Module and the dynamic display of a record.

Notice how the system is able to show you the actual value (Label or 1st Text Attribute) of the foreign key rather than just the ID.
(If the table does not have any Label or Text attribute it will display the ID instead) 


 

Check out the Forge HowTo Data Model Introspection

Have fun!

Cheers