OutSystems Server-Side Data Model: ALMOST EVERYTHING TO KNOW

Justin James
ITNEXT
Published in
9 min readOct 8, 2018

--

On a day-to-day-basis, the OutSystems data model “just works” and you do not need to know too much about it to get your job done. But live with the tool for a while, and you start bumping into things you might not understand. Questions like “what happens when I rename an Entity or an Attribute?” or perhaps “will the physical table names be the same across all environments?” and even “what exactly happens during a deployment?” are answered!

(Quick note, in this article, “*” is a wildcard placeholder and “XYZ” is a placeholder for exactly three letters)

Yeeeeeeaaaaaahhhh buddy! Time to go UNDER THE HOOD of the data model! (For the observant, yes I celebrate every July 7th, and every day with a red key)

Naming Conventions

You will see a number of table name patterns in the database. The major categories are:

  • OSSYS_*: system tables
  • OSLTM_*: Lifetime’s data tables
  • OSLOG_*: log tables (the logs you see in Service Center)
  • OSUSR_XYZ_*: application data tables, mapped to Entities in eSpaces
  • OSUSR_XYZ_*_t: views on top of the application data tables for multi-tenant Entities, they are filtered by tenant ID
  • OSUSR_XYZ__ML*: translations for Static Entities

System Tables

Be very careful with the OSSYS_* tables! Messing with them can cause your environment to become unstable or even go down completely. In almost all cases, do not make any schema changes, or create/update/delete data here unless you are positively certain you know what you are doing (in other words, someone from OutSystems Support told you to make the change) and know how to back the change out if needed, like making a backup copy of the database before changing a value.

For typical troubleshooting, you will often look at these tables:

  • OSSYS_User: stores the basic user information under the User entity; you can access this data through the User Entity if you need to edit User data.
  • OSSYS_Role: list of Roles in the system
  • OSSYS_User_Role: a mapping of roles to users. You can read this to learn if a User has a specific Role without calling Check*Role, particularly useful when working across applications or tenants. You can also write code to work with the data here (using the User_Role entity) to create User Role management (ignore what I said about never ever touching these tables…). I have also had to force values in this table before via direct SQL to fix an admin user. These are all things that you should work with a Customer Success Manager or an extremely experienced OutSystems developer if you feel you need to do them.
  • OSSYS_Espace: list of eSpaces, which you can map to Users, Roles, and more; to see which eSpace data “belongs” to
  • OSSYS_Tenant: list of tenants for multi-tenant eSpaces. Also note that every eSpace appears as a “tenant” even if it is not a multi-tenant eSpace. Users who were created in eSpaces, if those eSpaces are User Providers, but not assigned a specific tenant, will have a Tenant Id set to the eSpace ID of their User Provider eSpace.
  • OSSYS_Entity: mapping between the physical tables and the logical Entities within eSpaces (but not Entities from Extensions)
  • OSSYS_Entity_Attr: mapping between Entities and the Attributes in Entities

In a number of system tables, you will see a column named SS Key. SS Key is an identifier used by Studio as well as the Platform to identify something no matter which environment or installation it is deployed to. In general, things will retain their SS Key value if they are cut in Service Studio, but get a new one if they are copied. For example, every eSpace has an SS Key, and it will be the same no matter where that eSpace is published… until you use “Clone” which gives the clone a fresh SS Key. Do not be fooled by the fact that SS Key looks like a GUID, it is not always a unique value. For example, the SS Key used on Entities can have duplicates (if an eSpace was cloned), in order to support operations like merging.

Log Tables

There are a set of log tables that correspond to each tab under “Monitoring” in Service Center. These log tables each will end with a number from 0–9 indicating a week. There will also be a view without a number on it, pointing to the table for the current week, as well as one for the previous week. The platform rotates logs weekly, where it will update the current week’s view to point to a clean table, the previous week’s view will now be tied to the previous week’s table, then purge the oldest table. The number of retained weeks is configured with the Configuration Tool on the front end servers, and can rang from 1 to 10 weeks. There is no pattern to which table will hold the most recent logs. In Platform 11, there are some significant differences in how the logs are stored and what data is in them. This is not a complete list, but the most-important log tables and views:

  • OSLOG_Cyclic_Job_*, OSLOG_Timer view: details on timer execution
  • OSLOG_Email_* tables, OSLOG_Email view: email sending logs
  • OSLOG_Error_*, OSLOG_Error_Detail_* tables, OSLOG_Error view: the error information, including stack traces
  • OSLOG_Extension_* tables, OSLOG_Extension view: data for the Extensions in the system
  • OSLOG_General_* tables, OSLOG_General view: data for the “General” log, including application “Audit” information
  • OSLOG_Screen_* tables, OSLOG_Screen view: logs the time it takes for screens to be displayed or Ajax Refresh’ed

Application Data Tables

Application data tables are the most important, complicated, and common of the tables in your database. Understanding how they work is critical if you are thinking about business intelligenge, report writing, ETL, and other tasks that need direct database access.

The naming convention of OSUSR_XYZ_* breaks down as:

  • OSUSR: indicates an application data table (“user” data)
  • XYZ: a three character code that groups tables by eSpace
  • *: a name that roughly correlates to the Entity name

The XYZ code is unique per eSpace. Each eSpace has a preferred XYZ code to use. If I have an eSpace named “CustomerPortal” and and in my Development environment its tables get the XYZ code of “6D5” and then I deploy CustomerPortal to my QA environment, the XYZ code in QA should also be “6D5”. However, this is not a guarantee. One exception is if there is another eSpace using the same XYZ code, in which case a new one will be used. This will often happen if you deploy an eSpace to an environment, delete that eSpace, then deploy it again to the environment. Another exception is that renaming an eSpace will change its preferred XYZ code for new Entities.

Depending on your version and database provider, the NAME portion of the table name is a truncated version of the Entity name. If there is a reason for two tables to be create with the same truncated NAME, they will have a number appended to them, starting with 1. There are two circumstances where you will see this: if an Entity is deleted and then a new Entity with a similar name is created, or if two Entities have the same characters in their names in the portion that will be turned into NAME. For example, AReallyLongEntityNameThatWillBeTruncated and AReallyLongEntityNameThatWillAlsoBeTruncated might become OSUSR_6D5_AREALLYLONGENTITYNAME and OSUSR_6D5_AREALLYLONGENTITYNAM1. The first scenario is more complex, and can cause differences in physical table names between environments. The second scenario only applies to versions and configurations that truncate Entity names to make table names.

Consider the following timeline to understand how the table naming works:

Can this developer just make up their mind what to do with this Entity?!?!

The trick to all of this is the SS Key assigned to each Entity when the Entity is created in Service Studio. This SS Key is used to identify it within the system, and it is used in OSSYS_Entity. When you “Undo” an Entity deletion in Service Studio, or rollback to a previous version of the eSpace in Service Center, the system see that the SS Key is the same as what is stored in OSSYS_Entity, and continues to use the same physical table, but sets that record in OSSYS_Entity to be “active” again (it follows a soft delete pattern). But if you create a new Entity with the same name, it will get a new SS Key value, there will be no existing mapping, and the system will generate the name based on the formula. This is the same reason that the physical table names in DEV and QA and PROD can be different even though the Entities have the same names. Whenever you delete an Entity, the physical table remains behind so that a rollback or an undo can restore the table with no data loss.

Attribute Behavior

The Attributes/Columns information is stored in the OSSYS_Entity_Attr table. Just like Entities, each Attribute is assigned a SS Key when it is created. Unlike Entities, there is no mapping, the physical column name is the same as the logical Attribute name. This means that deleting an Attribute, then rolling the eSpace back or undoing the change and re-publishing will use the same physical column as before and the data will be restored. If you delete the Attribute and then create an identical one with a new name, it will reuse the existing physical column with the same name. If your new Attribute has a data type where the existing column’s data cannot be cast to the new type, the eSpace publish will fail.

WARNING! WARNING! WARNING!

There are two scenarios in which modifying an Attribute will go very poorly:

  • Changing the data type of an Attribute: depending on what data type it was and what you are changing it to, this will fail to publish (going from a Text value to an Integer, for example)
  • Renaming an Attribute: this will always cause your data to no longer be visible to your applications until you rename it back to the previous name

The following directions explain how to perform the above two procedures without losing data or being unable to publish. In both sets of instructions, you will need to work in multiple deployment iterations, or sprints, to fully complete the development work, but this work can be done alongside any other development work you need to do in those sprints.

Safely Change a Data Type

Iteration 1

  1. Create a new Attribute of the desired data type, with a temporary name. If we are changing the “Total” attribute from “Text” to “Integer” you create an Attribute named TEMPTotal of type Integer.
  2. Update your application to reference TEMPTotal everywhere Total is used. The “Find Usages” function (F12) and “Find Usages in All Espaces” (CTRL + F12) functions of Service Studio, combined with “Replace All Occurrences” on the results (look for the button in the lower-right hand corner of the search results), will make this task go much quicker.
  3. Write code (such as a Timer with a schedule of “When Published”, or a screen in an admin-only section) to go through every record in the system, copy the content of Total to TEMPTotal (with any needed validation, error handling, and conversions), set the values in Total to NullIndentifier (for reference attributes), empty string, or 0 (as appropriate for the destination data type) to clear the original values, and update the record.
  4. Deploy to TEST/QA, run the data migration, test, then push upstream until you get to PROD.

Iteration 2

  1. Delete the original Total Attribute
  2. Create a new “Total” attribute with the same data type as TEMPTotal (the desired final data type)
  3. Update the application again to reference Total instead of TEMPTotal
  4. Write a new data migration system to copy the data from TEMPTotal to Total
  5. Deploy, migrate data, test through to PROD

Iteration 3

  1. Delete the TEMPTotal Attribute
  2. Deploy through to Production

Safely Rename an Attribute

Iteration 1

  1. Create a new Attribute with the desired name and the same data type as the original Attribute. For example, if you are renaming “Total” (type Integer) to “WidgetCount”, create WidgetCount with type Integer.
  2. Update your application to reference WidgetCount everywhere Total is used. The “Find Usages” (F12) and “Find Usages in All Espaces” (CTRL + F12) functions of Service Studio, combined with “Replace All Occurrences” (look for the button in the lower-right hand corner of the search results) will be very useful here.
  3. Write code (such as a Timer with a schedule of “When Published” or a screen in an admin-only section) to go through every record in the system, copy the content of Total to WidgetCount, and update the record.
  4. Deploy, migrate the data with the timer or action, and test through to PROD.

Iteration 2

  1. Delete the Total attribute
  2. Deploy through to PROD.

Accessing Data

Because there is no guarantee that the physical table or column names in DEV, QA, PROD, etc. environments will match each other, and there is the possibility that the names will change if Entities are deleted and then re-created, you should never write SQL code that directly access those tables by their physical names. I have written an article that fully explains your options, but in general, using View4Entity (as described in that article) is the best approach.

There may be more details of the data model out there which are not explained here. If you have a specific question, please let me know and I will add to the article. I will also hopefully have a mobile version of this article in the near future as well.

J.Ja

--

--