A lot of time is lost by manually entering data to reproduce specific situations. Ideally, tooling is used to move current data to acceptance or test environments.

In many cases, the target environment already contains data, and we want to keep this data or even update it. This is not a trivial matter. How do we ensure that the test set is retained while missing data is supplemented?

How do we approach this?
​Suppose we have an application which registers customers. In the figure below we can see that customers Alan and Becky are present in the production environment while Alan and Tom are present in the test environment.

We move data from Production to Test, and we want to amend Alan’s data, add Becky’s data, and leave Tom’s data unchanged.

At first, this seems easy to achieve by simply moving Alan’s and Becky’s data. However, a complicating factor is the use of “Autonumber Identifiers”, as is generally the case in OutSystems environments. As a result, the same records often have different IDs in different environments. In our example, Alan's ID in Production is 10, while in Test it is 15. So it is impossible to match customer records based on these IDs. If we fail to take this into account, then this will result in the creation of a new customer record for Alan, and he will be registered twice. That is something we want to avoid.

We can expand on this example because alongside the customer information, we also want to move order information. The Order entity holds the order information, and it has a foreign key attribute for the customer as well as a SequenceNumber attribute that is unique per customer.

If we want to transfer Becky and her orders to Acceptance, then we need to:

  • Create customer Becky (with a newly generated ID of, for example, 30);
  • Create Becky’s orders;
  • Link these orders to Becky by populating the foreign key with the value 30

To do this successfully, we have to take the following into account.

ID mapping
​In the production environment, Becky’s ID is 20. When we moved to the test environment, her ID became 30. We need this information to be able to enter Becky's orders. After all, the value of the foreign key for her orders in Production is 20, but when entered in Test the value has to be 30.

This is why we need to have a record of which links the original ID and the new ID.

The order in which to add new records
Before we can create Orders, there first has to be a customer. So, the relationships between the entities determine the order in which to add new records. In complex data models, it is difficult to determine the correct order and topological sorting is required.

In some cases, we are dealing with circular relationships, in which an entity ultimately refers back to itself via 0 or more intermediate steps. The solution to this problem lies in ensuring that at least one of the relationships in this circle is optional. Then the circle can be broken. We can insert the relevant records and assign a NULL value to the optional foreign key. After adding all the records, we update the foreign key to the correct value.

Matching records
Matching a record means finding a corresponding record in the target environment. Because a customer cannot be matched using the technical ID, this has to be done based on a combination of several other attributes within that entity: the functional key. In our example, this could be the CustomerNumber for the Customer. For the Order entity, it will be the SequenceNumber in combination with the foreign key to Customer.

When transferring the data, the functional key can be used to check whether the record is already there. In that way, we establish that Alan is already present in the test environment, while Becky is not.

For entities with ‘non-autonumber’ IDs, matching can be done based on an ID attribute, if required.

Static entities also have to be matched because they also can have different IDs in different environments. However, it is not necessary to define a functional key for static entities. Matching can be done based on information in the system table ossys_entity_record as this table holds the ID for each record in that particular environment.

Conclusion
Although at first sight, it would appear to be relatively easy to move data, it most certainly is not. We have to take numerous hurdles to transfer data successfully.

CoolProfs has developed the Cool Data Mover to support this. This tool provides a smart solution for moving data between OutSystems environments. CoolProfs will be present as a Gold and Platinum Sponsor at OutSystems NextStep in Denver and Amsterdam. Do drop by because then we can tell you more about what our Cool Data Mover can do for you.

This blog is the first in a series on data migration in OutSystems.

Michel Coudron
​Principal Consultant CoolProfs

Wil je meer weten over de Cool Data Mover, klik hier.