I’d like to give you all some tips on how the OutSystems Platform names user tables in the database.
First of all, all User tables (tables created by eSpaces) have a prefix of ‘OSUSR_’. Default behaviour is then to follow this prefix with a 3-digit hash of the parent eSpace name and finally the entity name is used.
There is a system DB table used by OutSystems Platform to manage the relationship between entities and the physical tables that correspond to them. This table is called OSSYS_Entity. If you want to find the physical table that corresponds to your entity, you can do a query such as
Select * from ossys_entity where name like '%<entity name>%'
Entity names are limited to 50 characters in Service Studio, so the maximum length of a table name managed by the OutSystems Platform would be 60 characters (‘OSUSR_<eSpace name hash>_<entity name>’).
Due to Oracle limitations, when using this DB type OutSystems Platform needs to truncate the name of an entity whose total table name would exceed 28 characters (meaning all entities with names longer than 18 characters will be truncated in the DB).
Example: Let’s imagine that entity ‘UnnecessarilyLongEntityName’ has a hash of 'abc'. In SQL Server, the physical table name would be:
However in Oracle the physical table name would be truncated as follows:
In some situations the platform will find that after building the table name according to the rules above, the resulting name will already exist in the database. This can happen for several reasons, such as the truncation of names due to Oracle limits or because the Entity or eSpace had previously been deleted and was now re-created (since OutSystemsPlatform does not delete the physical DB tables when an eSpace or entity is deleted to avoid accidental loss of data this is a possibility). In these cases either a number is added to the end of the table name (or incremented if it was already there) or the last digit is replaced with this number.
entity ‘Clients’ is created and receives a table name of ‘OSUSR_abc_Clients’. The eSpace containing it is then deleted from the server but afterwards re-uploaded: upon publishing this newly uploaded eSpace, entity ‘Clients’ will receive a physical table name of ‘OSUSR_abc_Clients1’, since the old table was not deleted to preserve data.
When you rename an eSpace, the physical table names for entities created before the renaming action do not change, but all tables created afterwards will have a 3-digit hash corresponding to the new eSpace name. This means that a physical table name can change from environment to environment if, for example, the eSpace was renamed during development before being deployed for the first time to a production environment.
Let’s imagine that an eSpace has a 3-digit hash of ‘abc’. Creating entity ‘Customers’ in this eSpace would result in a physical table name of ‘OSUSR_abc_Customers’. If the eSpace is then renamed and its hash is now ‘xyz’, the physical table name of Entity ‘Customers’ does not change, but when we create a new Entity named ‘Receipts’ it will have a physical table name of ‘OSUSR_xyz_Receipts’.
When we now deploy this eSpace to Production environment, the physical table names for these two entities will be ‘OSUSR_xyz_Customers’ and ‘OSUSR_xyz_Receipts’.
When creating entities that have similar names you might get Name Clashes as explained before. But when you deploy the eSpace containing these entities to another environment, the names may be different from the original ones, depending on the order they are created.
Your environments use Oracle DBs. You create an entity called VERYLONGENTITYNAME_MODULE and deploy your application in DEV environment.
You then extend your eSpace and create another entity called VERYLONGENTITYNAME_CACHE in your DEV environment.
Your DEV environment’s DB now has tables OSUSR_xyz_VERYLONGENTITYNAME (for VERYLONGENTITYNAME_MODULE) and OSUSR_xyz_VERYLONGENTITYNAM1 (for VERYLONGENTITYNAME_CACHE, created later).
If you now push your eSpace to Pre-PROD and it was never deployed there before, the names may be switched since the order for table creation can change. If this happens, you will get *_MODULE ending with NAM1 in DB and *_CACHE ending with NAME.