Data conversion

Introduction to Data conversion

A very important step in preparing an upgrade is setting up version management for the data conversion. If version management is not applied correctly, it can ruin the database. In a best-case scenario, the upgrade script will fail, and a backup has to be restored. Yet, in the worst case, the upgrade is carried out without an error message but with data lost.

The data conversion screen has four tasks:

• Refresh data conversion - deletes the generated records and rebuilds the data conversion from that point.
• Amend previous version - amends the previous version of the project version. After this, it creates the data conversion for the new upgrade.
• Delete data conversion table - deletes one record with the underlying columns.
• Delete data conversion - deletes all data conversions between two versions.

Check the selected previous version

The first step is to check the selected previous version. This check ensures that the new version is created from the correct version.

When you're working with various branches and/or several versions, it can be confusing to see which version is the correct one to create a new version. In the database, in the sf_product_info table, you can check which version is correct.

Using the Update previous version task, you can convert the previous project version and data conversion mapping. You can define a ​mapping for various earlier project versions, but by default, only the mapping for the current previous version shows.

Matching tables or columns

menu Data > Data conversion > tab Data conversion or Data conversion (all columns)

Data conversion automatically attempts to match the old tables and new tables based on naming. If the match is successful, the From table ID and the To table ID are filled, and the Status is set to 'Modified'.

When a table is renamed, the data from the old table must be added manually to the new table by adding the old table name to the From table ID in the new record and then updating the data conversion again. Then, the matching is updated, and the old record is deleted.

Splitting tables or columns

Sometimes a table has the same name as before but a different function after the conversion. Therefore, the data is no longer correct, and the new table has to be emptied after the upgrade. This is done by deleting the value from the From table ID and updating the data conversion again. Then, there will be a second table with the Status 'Deleted'.

Rebuilding tables

Even though nothing has changed, it can still be desirable to rebuild a complete table. For example, when the structure of a table can no longer be guaranteed.

Default values for new columns

menu Data > Data conversion > tab Data conversion > tab Column
or
menu Data > Data conversion > tab Data conversion (all columns)

After the tables and columns have been matched, you can set a default value for a column.

The difference between the default value during the data conversion and the default value in the data model is that the default value of the data conversion is used only during the upgrade.

1. Select Default value type 'Constant value' or 'Expression'. An expression can fill any new or changed column during the upgrade.

note

Advanced users: before the 2021.3 release, you could set a default value by preceding it with an =. The = is replaced by the Default value type 'Expression'.

Squashing

menu Data > Data conversion > tab Data conversion

When many small changes are made in different project versions, it may be desirable to combine these project versions into one version before upgrading to a new version. The Squash data conversions task is available to squash these changes in the data conversions.

Squash data conversions

Consider the following example: In version 1.10, column x is renamed to y. In version 1.40, the same column is renamed to z. By squashing the data conversion between 1.00 and 1.40, the Software Factory will automatically rename column x to z.

Manual data conversion

The data conversion settings are intended for simple scenarios, where records are moved from one table to another or columns are filled using default values. You can set more advanced default values by using an expression.

For more complex scenarios, i.e., merging tables, changing primary keys, normalizing or denormalizing, you can insert manual scripts into the upgrade process. This can be done using a control procedure with Code group 'Upgrade'. You can place custom scripts in various stages of the upgrade process. The stage depends on the program object to which a template is assigned.

Program object	Stage
ug_before_upgrade_from_[x]_to_[y]	Before any upgrade code from version [x] to [y] has been executed.
ug_during_upgrade_from_[x]_to_[y]	After the data migration from version [x] to [y] has been done but before the old tables are dropped.
ug_after_upgrade_from_[x]_to_[y]	After the old tables from version [x] have been dropped when upgrading to version [y].

Versions [x] and [y] in the program object name correspond to the configured version control base version and the current version. Because of this, the template will only affect this specific upgrade.

If a manual code needs to be run before, during, or after every single upgrade, you can use one of the following program objects instead:

Program object	Stage
ug_before_upgrade_always	Before any upgrade code has been executed.
ug_during_upgrade_always	After the data migration has been done but before the old tables are dropped.
ug_after_upgrade_always	After the old tables have been dropped.

When using a manual template during the upgrade, you can refer to the old tables using __[tab_id]__.