Writing data conversions in Novulo

[k.molokova]

TL;DR

Data conversion preserves or initializes application data when the Novulo data model changes.

Write data conversion when:

• a table or field is being dropped and data must be moved;
• the meaning or datatype of a field changes;
• a newly added field requires values other than its database default;
• a newly added cached function must be initialized. Cached functions add database columns and should be initialized through data conversion when introduced.

Keep the SQL readable and unique (meaning a variable name must be unique), document its purpose and assumptions (as comments), and test the affected data (for example, before starting the deployment order and before accepting the deployment order).

When is data conversion necessary?

The database does not understand the functional meaning of a model change. It only sees structural differences between the existing and generated database schemas.

For example, when you add a field, the generated update script adds a column. When you remove a field, the generated update script normally drops that column. If the removed field contains information that the application still needs, you must move or transform that information before the field is dropped. This is data conversion.

Changes Novulo may recognize

During deployment, the Novulo generator can recognize some structural changes automatically:

• If a field is renamed while its identity and relevant properties remain unchanged, the generator may recognize it as the same field and rename the corresponding column.
• If a field is removed and then added again, it receives a different internal identity. If its name and datatype still match, the generator may nevertheless interpret it as the same field.
• For tables, the generator compares the structure of the old and new tables. If field names and datatypes remain sufficiently consistent, it may recognize that a table has been renamed or moved between components.

Important: Do not rely on automatic recognition if multiple changes are made at once. If a table is moved and its fields are renamed, added, removed, or changed simultaneously, the generated update script may drop the old table and create a new one. Without data conversion, this can result in the loss of the data stored in the old table.

Always inspect the generated update script before approving it during deployment.

How to write data conversion for a cached field expression?

1. Copy the cached field as an expression from the Architect
2. Paste it into the Expression Debugger.
3. Profile the expression and retrieve the generated SQL.
   
   
4. Convert the generated SQL into an UPDATE statement.
5. Execute the script as data conversion.

This guarantees that the SQL matches the actual implementation of the cached field and prevents inconsistencies.

Write readable SQL

While writing data conversion in SQL, use the following conventions:

• Capitalize SQL keywords such as UPDATE, SET, FROM, LEFT JOIN, WHERE, and CASE.
• Put major SQL clauses on separate lines.
• Use short, meaningful table aliases.
• Prefix every column with its table alias.
• Put table and column names between square brackets.
• Specify the join type explicitly.
• Keep comments close to the assumption or decision they explain.

Square brackets prevent names such as [type], [order], or [user] from being interpreted as SQL keywords.

Example:

-- Move the journal-entry date to the related journal-entry lines
-- Assumption: every journal entry line has a valid journal entry

UPDATE jel
SET jel.[entry_datetmpfld] = je.[entry_date]
FROM [journalentrylines] AS jel
INNER JOIN [journalentries] AS je
    ON je.[id] = jel.[journal_entry];

Why does a field end with tmpfld?

During deployment, newly added fields are initially created as temporary database columns so you should write to the temporary column, not to the final field name. After the update process completes successfully, the platform finalizes the schema change and the temporary column becomes the actual field.

Rule of thumb:

• Existing field > use the regular column name.
• Newly introduced field > use <fieldname>tmpfld.
• Renamed field > use the new temporary field created by the update process.
• Cached field initialization after adding a new cached field > write to <cachedfieldname>tmpfld.

Usage of JOIN vs APPLY

Use LEFT JOIN and INNER JOIN for regular table joins. Use CROSS APPLY or OUTER APPLY when joining a derived result set, such as a TOP 1 query or another subquery that must be evaluated per row. CROSS APPLY behaves like INNER JOIN, while OUTER APPLY behaves like LEFT JOIN. In data conversions, prefer APPLY whenever a derived table is required.

Data Conversion Checklist

Before submitting data conversion, verify the following:

• All dropped fields and tables have been assessed for data migration requirements.
• Generated DROP TABLE and DROP COLUMN statements have been reviewed for potential data loss.
• Cached field dependencies are updated in the correct order.
• Newly added cached fields are initialized.
• Newly added fields use the appropriate tmpfld column.
• The affected records and expected outcome have been identified.
• An equivalent SELECT query has been tested first to validate which records and how many records will be affected.
• Handling of unmatched relations and NULL values has been verified.
• Each target record receives no more than one source value.
• SQL assumptions are documented with comments.
• SQL keywords are capitalized and formatting is consistent.
• Table aliases are used consistently and all column references are qualified.
• The update has been tested in a representative test environment.
• Record counts and critical values have been compared before and after the conversion.
• Cached field values match the corresponding function results after the conversion.
• Application behavior has been validated after the component update.