Create Task Upgrade to create new unique_field table

[freddyDOTCMS]

Parent Issue

#29459

User Story

According to spike #29707, the best solution to address the unique fields bug is to create a new table to store and validate all unique values. To implement this, we need to create a new Task Upgrade to create and populate this new unique_fields table.

New Table Structure

• unique_key_val (bigint): A hash calculated by concatenating the following values: content_type, field_var, host_id, language, and value. The host_id is only included if the field variable uniquePerSite is set.
• supporting_values (jsonb): A JSON object containing the following fields: content_type, field_var, host_id, language, value, uniquePerSite, and content_identifier. The content_identifier will be an array, as multiple contentlets may share the same value before the upgrade.

The unique_key_val is the PK of the table.

Populate New Table

We need to populate this new table with the unique field values that already exist in the system. The following query was used during the spike to retrieve these unique values:

SELECT DISTINCT 
    structure.velocity_var_name AS type_var_name,
    field.velocity_var_name AS field_var_name,
    contentlet.language_id,
    identifier.host_inode,
    jsonb_extract_path_text(contentlet_as_json->'fields', field.velocity_var_name)::jsonb->>'value' AS field_value
FROM contentlet
    INNER JOIN structure ON structure.inode = contentlet.structure_inode
    INNER JOIN field ON structure.inode = field.structure_inode
    INNER JOIN identifier ON contentlet.identifier = identifier.id
WHERE jsonb_extract_path_text(contentlet_as_json->'fields', field.velocity_var_name) IS NOT NULL

Required Update:

The query above does not yet account for the content_identifier and the uniquePerSite field variable. We need to update this query to support these fields for the final fix.

Acceptance Criteria

• Create Task Upgrade to create/populate unique_fields table
• Don't include the new Task Upgrade in TaskLocatorUtil, we are going to do this later

Proposed Objective

Core Features

Proposed Priority

Priority 2 - Important

External Links... Slack Conversations, Support Tickets, Figma Designs, etc.

No response

Assumptions & Initiation Needs

No response

Quality Assurance Notes & Workarounds

No response

Sub-Tasks & Estimates

No response

[freddyDOTCMS]

Note to QA: The task hasn't been added to the TaskLocatorUtil yet, so you'll need to run it manually or add it for QA testing. Please don't push this change for now. It's important to check during IQA if the unique_tables are being populated correctly, to check the table popualte you are going to need to execute the query directly into the Database.

I think QA is not need here because the whole fix is still a Work in progress.

[dsilvam]

Passed IQA.

Executed the upgrade task via postman and checked the columns and the data of the new table.

[josemejias11]

Approved: Tested on trunk_17da5c2, Docker, macOS 14.5, FF v126.0.1