OutSystems database implications

Entities and entity attributes translated to database objects

In OutSystems you work with entities and entity attributes to define the data model for your application. Entities will translate to database tables and entity attributes will translate to table columns.

Entities

The name of the entity is part of the table name that is created for the entity. The name of an entity table start with OSUSR_<3 character hash value>_<first 8 characters of entity>

If there are multiple entities where the first 8 characters are equal, than OutSystems will create tables with the first 7 characters of these entities names and add a number.

The 3 character hash value is a combination of 3 characters (letters and numbers) that defines to which eSpace/module the tables belong. This hash value is based on the name of the eSpace, so changing the eSpace/module name, will also change the 3 character hash value. Creating a new entity after renaming the eSpace/module will make OutSystems create a table with the new 3 character hash value. Tables for entities that were created before renaming the eSpace/module will keep using their original table name, thus with the old 3 character hash value.

All tables are stored within the OSADMIN schema.

To find the name of the table, you can use the following query where you need to specify the name of the eSpace/module and the name of the entity.

SELECT UPPER(en.physical_table_name)
FROM osadmin.ossys_entity en INNER JOIN osadmin.ossys_espace es
ON en.espace_id = es.id
WHERE es.name='<name of your eSpace – case sensitive>’
AND en.name='<name of your entity – case sensitive>’;

Note that the eSpace- and entity names are case sensitive.

Entity attribute

The name of the entity attribute will also be the name of the table column. OutSystems allows the name of the entity attribute to be only 28 characters long, so it won’t exceed the 30 byte/ASCII character limit in the Oracle database.

Entity Attribute named Address will become a database table column named ADDRESS in the Oracle database.

Data Type conversion table

OutSystems uses its own, database independent, data types. Of course the database does not understand these data types, so OutSystems translates its data types to data types of the used database. The below table shows the OutSystems data types versus Oracle’s data types.

Managing OutSystems entities and entity attributes

So now it’s clear how entities and their attributes translate to physical database objects, let’s see what happens when we add, change or remove entities or entity attributes.

OutSystems principle: NEVER removes data on entity and entity attributes

The OutSystems platform never removes a table column or table from the database. This is to prevent data loss and the ability to rollback to previous application versions.

OutSystems is clear on this priciple and they have valid reasons for this, but as a DBA I like to have a database without unnecessary objects. Cleaning up unused tables (that belonged to removed entities) allows reusing/reducing storage, reduce backup/restore times. Cleaning up unused table columns (that belonged to removed/renamed entity attributes) reduces/reuses storage and could improve performance.

Add entity

When you add an entity (the same goes for static entities) a database table will be created. Information on the new entity will be added to the OutSystems metadata table OSSYS_ENTITY. This information includes the name of the created table (column physical_table_name) and the is_active column set to 1.

Add entity attribute

When you add a attribute to an entity, a column will be added to the table using the same (upper case) name as the attribute in OutSystems. The OutSystems data type used for the attribute will determine the Oracle data type used for the column.

After the column is added to the table, the OutSystems platform will execute a UPDATE statement that sets the null value (the OutSystems platform does not understand Oracle’s NULL value) for the new column.

For a large table, containing a lot of data, this UPDATE can take a considerable amount of time

Here is a list of default (Null) values that OutSystems uses in the database:

The OutSystems platform will also add information about the new entity attribute to the OutSystems metadata table OSSYS_ENTITY_ATTR. The is_active column is set to 1.

Remove entity

When you remove a entity from OutSystems, it will update the is_active column in the OutSystems medata table OSSYS_ENTITY for the removed entity, but won’t drop the table from the database because of OutSystems principle of never removing data on entity and entity attribute changes.

The physical table is NOT removed from the database and thus keeps using space

Removing entity attribute

When you remove a entity attribute from and OutSystems entity, when publishing, OutSystems will set the is_active column in the metadata table OSSYS_ENTITY_ATTR to 0, for the removed entity attribute. It won’t however drop the column from the table.

The table column representing the entity attribute is not dropped from the table

Changing the name of a entity

As you have read in previous paragraphs of this blog post, the name of the entity is part of the table name that is created for that entity. Whenever you change the name of an existing entry, OutSystems will NOT change the name of the table, but will only update the name column in the OSSYS_ENTITY metadata table.

Changing the name of a entity attribute

Whenever you change the name of an entity attribute, a NEW column with the new name of the attribute will be added to the table and the row for the changed attribute in the OutSystems Metadata table OSSYS_ENTITY_ATTR, will be updated with the new name (and any other changes for the attribute, like data type, etc.).

The orignal table column is not dropped (OutSystems won’t remove data, remember), but there won’t be a reference in OutSystems metadata table OSSYS_ENTITY_ATTR to this column anymore. Although there is not reference in OSSYS_ENTITY_ATTR for such an orphan table column, when publshing, OutSystems will keep coming with “Database Integrity Suggestion” messages about these table columns.

DBCleaner_API

As of the OutSystems platform 9, an API, named DBCleaner_API, is available that will allow you to cleanup unused entities and entity attributes. Using this API will make sure both the OutSystems metadata and physical tables and table columns get removed.

On the Forge an application is available, named DBCleaner, that uses this API. Using this application you can remove unused entities and entity attributes both from OutSystems metadata tables and the tables and column. Unfortunately the application will not detect orphan table columns as the result of changing attribute names, probably because there no reference to these table columns after changing the name of an entity attribute.

The application is also able to show the used (and used) size of the tables (and its indexes/partitions) in all eSpaces. Unfortunately this only works for SQL server databases at the moment.