Migration of internal parameter tables as of version 3.26.5

As of version 3.26.5, the following internal XDM database tables used for storing parameters for various objects are no longer in use.

  • object_parameter tables

    • application_parameter

    • attribute_generator_parameter

    • backup_parameter

    • connection_parameter

    • data_request_form_parameter

    • domain_attribute_parameter

    • domain_entity_parameter

    • entity_generator_parameter

    • entity_modification_rule_parameter

    • entity_scenario_generator_parameter

    • environment_parameter

    • execution_parameter

    • external_tool_parameter

    • file_parameter

    • file_operator_parameter

    • modification_rule_parameter

    • modification_set_parameter

    • reference_parameter

    • requested_execution_parameter

    • task_parameter

    • task_stage_hook_usage_parameter

    • task_template_parameter

    • version_parameter

    • workflow_parameter

    • workflow_template_parameter

  • relationship tables

    • parameter_values

    • single_value_parameter

    • multi_value_parameter

These tables remain present in the database but will be removed at a later date.

Custom parameters, scripts, or task execution reports that directly access these tables should be reviewed and any SQL statements should be adjusted accordingly. Any customizations should be reviewed and updated to align with the new structure.

What is changing?

Up to and including version 3.26.5, parameter information was stored in separate tables. Starting with version 3.26.5, this information is stored directly in a new jsonb column named parameters within the respective object table (e.g., backup).

Database tables up to version 3.26.5

The following table diagram shows the tables and their relationships as they existed prior to version 3.26.5.

Diagram

  1. Tables and relationships prior to XDM version 3.26.5

Database tables as of version 3.26.5

The following table diagram shows the tables and their relationships as of version 3.26.5, using the backup table as an example. Since the <object>_parameter table is no longer used, the <object> tables now have a new jsonb column in which the parameter information is stored.

Diagram

  1. Tables and relationships as of XDM version 3.26.5

Example for adapting an SQL statement

The following example demonstrates how a custom SQL statement for querying order IDs and creation dates from backups needs to be adjusted for the new table structure introduced in version 3.26.5. Previously, the order ID was stored in the single_value_parameter table. In this example, 'orderId' is a custom parameter that increases sequentially for each icebox generation created.

Prior to version 3.26.5:

SELECT DISTINCT 'OrderId: ' || svp."value" || ' (' || b.created_on || ')' AS "Display Name", svp."value" AS "Value"
FROM "public".backup b
JOIN "public".backup_parameter bp ON b.database_id = bp.backup_database_id
JOIN "public".single_value_parameter svp ON svp.database_id = bp.parameters_database_id and svp."name" = 'orderId'
order by svp."value" desc

As of version 3.26.5, the SQL statement must be adapted as follows. The jsonb column parameters of the backup table is accessed directly.

SELECT DISTINCT 'OrderId: ' || (b.parameters ->> 'orderId') || ' (' || b.created_on || ')' AS "Display Name",
(b.parameters ->> 'orderId') AS "Value"
FROM "public".backup b
ORDER BY (b.parameters ->> 'orderId') DESC;