Added displayTemplate field to Product
Support singular specific item choice in product import and export
Update product option generation logic to use JSON path expression for translations rather than index
Catalog entity attributes maps now allow for a label field
Added support for Yugabyte databases
Add a label to allow trigram index creation to be filtered in liquibase
Fixed: Updated category metadata preview path to not require string evaluation
Fixed: SecurityEnhancer to be overridden by name rather than type for compatability with flexpackage deployments where multiple of the same type will be present in the same app
Fixed: Deleting a product now removes any category product xrefs associated to it
Fixed: Updated variant grid metadata to allow translations
Fixed: Check if productId collection is empty when attempting to read products by id to avoid assertion errors
Fixed: Issue of mismatch currency when system default does not match product currency
Added an index to blc_notification_state table.
The translation entity fields for product options and their allowed values were originally using index (e.g. options[0].label), up until Broadleaf 1.1.0-GA where the logic was changed to using JSON path expression (e.g. options[?(@.id=='SOME_ID')]). Additionally, prior Broadleaf 1.7.2-GA, the translations that were generated from an option template were also using the index-based approach. Using the JSON path expression is preferred over using index, since the index would become mismatched if the order of the product options or allowed values changed.
The data migration scripts are split into three different sections:
|
Important
|
Note that the syntax of the migration scripts are in PostgreSQL, for other DBMS, please update the syntax accordingly |
If you had any product option or allowed value translations that were added prior Broadleaf 1.1.0-GA, or those which were generated via option template prior Broadleaf 1.7.2-GA, this data migration is recommended.
You can also identify any existing translations using the index-based approach using the following scripts:
SELECT * FROM "catalog".blc_translation
WHERE entity_type='com.broadleafcommerce.catalog.provider.jpa.domain.product.JpaProduct'
AND entity_field LIKE 'options[%].label'
AND entity_field NOT LIKE 'options[%].attributeChoice%'
AND entity_field NOT LIKE 'options[?(@.id==''%'
AND trk_level = 100000
AND (trk_archived IS NULL OR trk_archived = 'N')
AND (trk_sandbox_archived IS NULL OR trk_sandbox_archived = 'N');If there are no records found for this query, then migration #1 can be skipped.
SELECT * FROM "catalog".blc_translation
WHERE (trk_archived IS NULL OR trk_archived='N')
AND (trk_sandbox_archived IS NULL OR trk_sandbox_archived='N')
AND trk_level = 100000
AND entity_type='com.broadleafcommerce.catalog.provider.jpa.domain.product.JpaProduct'
AND entity_field LIKE '%.allowedValues[%'
AND entity_field NOT LIKE '%allowedValues[?(@.id==''%';If there are no records found for this query, then migration #2 can be skipped.
SELECT * FROM "catalog".blc_translation
WHERE (trk_archived IS NULL OR trk_archived='N')
AND (trk_sandbox_archived IS NULL OR trk_sandbox_archived='N')
AND trk_level = 100000
AND entity_type='com.broadleafcommerce.catalog.provider.jpa.domain.option.JpaOptionTemplate'
AND entity_field LIKE '%.allowedValues[%'
AND entity_field NOT LIKE '%allowedValues[?(@.id==''%';If there are no records found for this query, then migration #3 can be skipped.
There are two options for how to do the data migration:
Run the Migration Scripts via Liquibase
Run the Migration Scripts via DBA Interaction against the catalog schema
For example: options[0].label
-- ONLY updates the translations for product option labels that are added to PRODUCT, i.e. options[0].label
CREATE TEMP TABLE temp_product_opt_translation AS
SELECT id AS translation_id, entity_id AS product_id, entity_field AS old_entity_field, (SUBSTRING(entity_field, 'options\[(.+)\].label'))::int AS option_index, NULL AS option_id, NULL AS updated_entity_field
FROM "catalog".blc_translation
WHERE entity_type='com.broadleafcommerce.catalog.provider.jpa.domain.product.JpaProduct'
AND entity_field LIKE 'options[%].label'
AND entity_field NOT LIKE 'options[%].attributeChoice%'
AND entity_field NOT LIKE 'options[?(@.id==''%'
AND trk_level = 100000
AND (trk_archived IS NULL OR trk_archived = 'N')
AND (trk_sandbox_archived IS NULL OR trk_sandbox_archived = 'N');
UPDATE temp_product_opt_translation
SET option_id = (
SELECT "options"::json -> option_index -> 'id'
FROM "catalog".blc_product
WHERE (trk_archived IS NULL OR trk_archived='N')
AND (trk_sandbox_archived IS NULL OR trk_sandbox_archived='N')
AND trk_level = 100000
AND context_id=product_id);
UPDATE temp_product_opt_translation
SET updated_entity_field = REPLACE(old_entity_field, concat('options[', option_index, ']'), concat('options[?(@.id==''', translate(option_id, '"', ''), ''')]'));
UPDATE "catalog".blc_translation
SET entity_field = temp.updated_entity_field
FROM (SELECT translation_id, updated_entity_field FROM temp_product_opt_translation) AS temp
WHERE id = temp.translation_id;For example: options[1].attributeChoice.allowedValues[0].label
-- Update the translations for product options that are added to PRODUCT and their attribute choices. i.e. options[1].attributeChoice.allowedValues[0].label
CREATE TEMP TABLE temp_product_attr_choice_translation AS
SELECT id AS translation_id, entity_id AS product_id, entity_field AS old_entity_field, (SUBSTRING(entity_field, 'options\[(.+)\].attributeChoice'))::int AS option_index, NULL AS option_id, (SUBSTRING(entity_field, 'allowedValues\[(.+)\].label'))::int AS allowed_value_index, NULL AS allowed_value_id, NULL AS updated_entity_field
FROM "catalog".blc_translation
WHERE (trk_archived IS NULL OR trk_archived='N')
AND (trk_sandbox_archived IS NULL OR trk_sandbox_archived='N')
AND trk_level = 100000
AND entity_type='com.broadleafcommerce.catalog.provider.jpa.domain.product.JpaProduct'
AND entity_field LIKE '%.allowedValues[%'
AND entity_field NOT LIKE '%allowedValues[?(@.id==''%';
UPDATE temp_product_attr_choice_translation
SET option_id = (
SELECT "options"::json -> option_index -> 'id'
FROM "catalog".blc_product product
WHERE (product.trk_archived IS NULL OR product.trk_archived='N')
AND (product.trk_sandbox_archived IS NULL OR product.trk_sandbox_archived='N')
AND product.trk_level = 100000
AND product.context_id=product_id);
UPDATE temp_product_attr_choice_translation
SET allowed_value_id = (
SELECT "options"::json -> option_index -> 'attributeChoice' -> 'allowedValues' -> allowed_value_index -> 'id'
FROM "catalog".blc_product product
WHERE (product.trk_archived IS NULL OR product.trk_archived='N')
AND (product.trk_sandbox_archived IS NULL OR product.trk_sandbox_archived='N')
AND product.trk_level = 100000
AND product.context_id=product_id);
UPDATE temp_product_attr_choice_translation
SET updated_entity_field = REPLACE(old_entity_field, concat('options[', option_index, ']'), concat('options[?(@.id==''', translate(option_id, '"', ''), ''')]'));
UPDATE temp_product_attr_choice_translation
SET updated_entity_field = REPLACE(updated_entity_field, concat('allowedValues[', allowed_value_index, ']'), concat('allowedValues[?(@.id==''', translate(allowed_value_id, '"', ''), ''')]'));
UPDATE "catalog".blc_translation
SET entity_field = temp.updated_entity_field
FROM (SELECT translation_id, updated_entity_field FROM temp_product_attr_choice_translation) AS temp
WHERE id = temp.translation_id;-- Update the translations for Product Option Templates
CREATE TEMP TABLE temp_product_opt_template_translation AS
SELECT id AS translation_id, entity_id AS option_template_id, entity_field AS old_entity_field, (SUBSTRING(entity_field, 'allowedValues\[(.+)\].label'))::int AS allowed_value_index, NULL AS allowed_value_id, NULL AS updated_entity_field
FROM "catalog".blc_translation
WHERE (trk_archived IS NULL OR trk_archived='N')
AND (trk_sandbox_archived IS NULL OR trk_sandbox_archived='N')
AND trk_level = 100000
AND entity_type='com.broadleafcommerce.catalog.provider.jpa.domain.option.JpaOptionTemplate'
AND entity_field LIKE '%.allowedValues[%'
AND entity_field NOT LIKE '%allowedValues[?(@.id==''%';
UPDATE temp_product_opt_template_translation
SET allowed_value_id = (
SELECT "ac_allowed_values"::json -> allowed_value_index -> 'id'
FROM "catalog".blc_option_template
WHERE (trk_archived IS NULL OR trk_archived='N')
AND (trk_sandbox_archived IS NULL OR trk_sandbox_archived='N')
AND trk_level = 100000
AND context_id=option_template_id);
UPDATE temp_product_opt_template_translation
SET updated_entity_field = REPLACE(old_entity_field, concat('allowedValues[', allowed_value_index, ']'), concat('allowedValues[?(@.id==''', translate(allowed_value_id, '"', ''), ''')]'));
UPDATE "catalog".blc_translation
SET entity_field = temp.updated_entity_field
FROM (SELECT translation_id, updated_entity_field FROM temp_product_opt_template_translation) AS temp
WHERE id = temp.translation_id;