How to map ServiceStudio entities to actual table names

How to map ServiceStudio entities to actual table names


For several reasons, the name you enter for an entity in Service Studio is not the same name that entity will have on the database. This is usually not a problem as the OutSystems Platform abstracts the physical table name and you never have to use it when developing,  you just use the entity name.

The OutSystems Platform manages the physical table names for you for several reasons:

  • You may have several entities with the same name in several modules, and they're different. They'll have the same name, but come from a different module.
  • Not all underlying Database Management Systems support the same size for a table name. For instance, tables in Oracle cannot be more than 30 characters long, and in SQL Server they can be 255 characters long. Service Studio allows you to create entities with up to 50 characters, if I'm not mistaken. This means that a direct mapping would not be possible in Oracle. The OutSystems Platform manages this for you and will trim the names and handle nameclashes for you.
  • You can delete entities in one version of the module and then rollback to a previous version. In this scenario you'll keep the data from the previous instance of the entity. While an entity is deleted in a module (let's call it EntityA) you can create, in the same module, another entity with the same name. These will be mapped to different tables so you're able to rollback to a previous version of the module without conflicts.

This management means that it's possible that the physical table name of an entity in Development is not the same as in Production (for example). This happens because the history of an environment is not the same and the physical table name is dependent on what other objects exist or existed in the model.

All this works well within the OutSystems Platform. However, sometimes there is a need to integrate a third party application with data from your OutSystems Platform.

These situations usally get to us with a specific solution in mind: either the customer wants to be able to control the table name (e.g. it be equal to the entity name) or wants to ensure that the name is the same across environments. None of these are currently possible in the OutSystems Platform.

The most common request for this is to integrate the data from the OutSystems Platform with a Reporting or ETL tool, but we also see people wanting to generate triggers over the OutSystems Platform or other kinds of scripts.

Given this, how can you integrate with these systems? Using the OutSystems Platform metadata to correctly identify the physical table name associated with an OutSystems Entity. For this you'll need to access two system entities:

  • Entity (ossys_entity) - This contains information about an entity. Its name within the module, the physical table name, a reference to the module that defines it, whether it's deleted or not.
  • Espace (ossys_espace) - This contains information about the modules. Their names, whether they're active.

With this information you can query the OutSystems Platform to know the physical table names of a given entity, for example, entity "Contacts" from the "PhoneBook" module can be obtained with the following query:

SELECT {Entity}.[Physical_Table_Name] FROM {Entity} INNER JOIN {Espace} ON ( {Entity}.[Espace_id] = {Espace}.[Id] ) WHERE {Entity}.[Name] = 'Contacts' AND {Espace}.[Name] = 'PhoneBook'

Or to know the physical table names of all active entities in the environment:

SELECT {Entity}.[Physical_Table_Name] FROM {Entity} INNER JOIN {Espace} ON ( {Entity}.[Espace_id] = {Espace}.[Id] ) WHERE {Entity}.[Is_Active] AND {Espace}.[Is_Active]

With this information you can obtain the information you need to input in your Reporting or ETL tools, or to generate maintenance plans based on specific tables. If you really, really, really want an object with a specific name in the database, you can use this information to create a SYNONYM to that table.

Best regards,
Ricardo Silva

Hi Ricardo,
Thank you for sharing. The OutSystems Platform has it's own unique characteristics (don't they all?) but I see a solution/workaround in every corner, when needed... We just need to know these.

I think it's a good approach to share this on the forum or website, it's probably some fewer emails/phone calls to support and a smoother/more enjoyable development ;)