OutSystems — Changing a Static Entity’s AutoNumber to False
If you are an OutSystems practitioner chance is that somewhere in time you may have heard someone say to never use auto-number integers as identifiers of Static Entities.
There are a few reasons to say this:
- You want the consistency of identifiers cross servers. This is important if you are using restored databases — a common scenario is taking the data from a Production environment to a Pre-Production one;
- You need to set the identifier yourself with a predefined value known beforehand;
- You may need to refactor your application and move a static entity to another module — keeping the same identifiers.
From my experience, things go bad in the first case.
The rationale behind this is that Static Entities by default have the primary key attribute with “IsAutoNumber” property set to “True”.
This guarantees the key is sequential, unique BUT ALSO that you can get different keys across different servers.
Having gone through some bad experiences with static entities and different identifiers I decided to write a step-by-step guide to help you overcome these type of scenarios.
With more and more clients moving their infrastructures to the cloud the process described below doesn’t need direct access to the database. You may need to do one update in Production and that’s it.
Context
You have one or more static entities with auto number enabled and you want to disable this property.
You try to set “Is AutoNumber” property to “False” and publish… The result is an upgrade error.
DISCLAIMER: The following steps may not be sufficient, but usually are enough or, at least, a good start based on my experience.
I — Pre-requisites
You need to have DBCleaner component installed in your infrastructure.
In a Production environment you may need access to the database to do the update described in step IV.
II — Backup stuff
Don’t forget the number one rule of IT! Make sure you have a backup of your data before proceeding!
III — Delete existing records
Use a SQL node to delete the existing records associated with your target Static Entity. Query the “Entity_Record” system table to find out the right identifiers and delete them.
IV — Inactivate Static Entity
Query the “Entity” system table and find the appropriate identifier of your target.
Set the “Is_Active” attribute to “False” in the correct row. This will be necessary for step VI!
V — Disable constraints
When doing this operation you’ll probably already have tables with foreign keys with your target static entity type.
Set the “Delete Rule” to “Ignore” to prevent errors in step VI!
VI — Drop table
Access DBCleaner and choose the “Database Entities” tab. Because you updated “Is_Active” in step IV your target will appear in the list.
Drop the entity! (Do you have a backup?? 😄)
If you didn’t disable the constraints in step V you’ll get an error like the one below.
VII — Setting AutoNumber to “False”
Change the “AutoNumber” property to “False” of the static entity and set the appropriate identifiers in your records. Publish the module.
VIII — Clean the house
- Validate your data and do the necessary data fixing;
- Refresh all the consumers of your static entity. With this approach, the internal Service Studio identifiers are maintained! I’ve seen developers take a different path and do CTRL+X/CTRL+V in the Static Entity and create a new internal identifier. I don’t like it 😉;
- Revert the delete rules back to their original settings (step V).
Like I said in the beginning, this is not a bullet proof solution but it should cover most cases.
Dedicated to NMS 💪 💣
