Introduction
This migration guide applies for 7.10.37.1, 7.10.32.8-LTS and 7.10.26.19-LTS.
Before version 7.10.37 it was possible to enter custom attribute names with leading and/or trailing spaces, either via ICM backoffice or via import. This could cause custom attributes to be duplicated. This behavior occurred on all entities that have custom attributes, e.g. products or categories.
Since version 7.10.37 it is no longer possible to create such custom attribute names. To clean up possibly existing custom attribute names, it it necessary to manually execute the SQL scripts provided with this migration guide.
Migration Steps
XML Custom Attributes
To migrate custom attributes which are stored as an XML structure, e.g. product custom attributes, the following scripts need to be executed, depending on the database system.
The scripts need to be executed for every custom attribute column, i.e. for the general column and each locale. For example, product could have the columns attributes, attributes_en_us, attributes_de_de, etc.
The following placeholders need to be replaced:
table_name→ the affected database table, e.g.PRODUCTcolumn_name→ the affected column of the table, e.g.attributes_en_us
Oracle SQL
In Oracle SQL, the XML custom attributes are stored as text and can be updated via regular expressions.
UPDATE table_name
SET column_name = REGEXP_REPLACE(column_name, '<attr name="[[:space:]]*([^[:space:]]{1,2}|[^[:space:]][^"]+[^[:space:]])[[:space:]]*">', '<attr name="\1">')
WHERE column_name IS NOT NULL;
Example:
UPDATE PRODUCT
SET ATTRIBUTES_EN_US = REGEXP_REPLACE(ATTRIBUTES_EN_US , '<attr name="[[:space:]]*([^[:space:]]{1,2}|[^[:space:]][^"]+[^[:space:]])[[:space:]]*">', '<attr name="\1">')
WHERE ATTRIBUTES_EN_US IS NOT NULL;
MS SQL
In MS SQL, the XML custom attributes are stored as an XML structure which cannot be updated via regular expressions. Instead the script needs to go through each XML node separately.
SELECT
p.uuid,
attr.value('(./@name)[1]', 'VARCHAR(MAX)') AS name,
TRIM(attr.value('(./@name)[1]', 'VARCHAR(MAX)')) AS trimmed_name
INTO #Temp
FROM table_name p
CROSS APPLY column_name .nodes('/attrs/attr') t(attr)
WHERE attr.value('(./@name)[1]', 'VARCHAR(MAX)') <> TRIM(attr.value('(./@name)[1]', 'VARCHAR(MAX)'));
DECLARE @uuid AS NVARCHAR(28)
DECLARE @name AS VARCHAR(MAX)
DECLARE @trimmed_name AS VARCHAR(MAX)
WHILE (SELECT COUNT(*) FROM #Temp) > 0
BEGIN
SELECT Top 1 @uuid = uuid, @name = name, @trimmed_name = trimmed_name FROM #Temp
UPDATE table_name
SET column_name.modify('replace value of (/attrs/attr[@name = sql:variable("@name")]/@name)[1] with sql:variable("@trimmed_name")')
WHERE uuid = @uuid
DELETE #Temp WHERE uuid = @uuid
END
DROP TABLE #Temp;
Example:
SELECT
p.uuid,
attr.value('(./@name)[1]', 'VARCHAR(MAX)') AS name,
TRIM(attr.value('(./@name)[1]', 'VARCHAR(MAX)')) AS trimmed_name
INTO #Temp
FROM PRODUCT p
CROSS APPLY attributes_en_us.nodes('/attrs/attr') t(attr)
WHERE attr.value('(./@name)[1]', 'VARCHAR(MAX)') <> TRIM(attr.value('(./@name)[1]', 'VARCHAR(MAX)'));
DECLARE @uuid AS NVARCHAR(28)
DECLARE @name AS VARCHAR(MAX)
DECLARE @trimmed_name AS VARCHAR(MAX)
WHILE (SELECT COUNT(*) FROM #Temp) > 0
BEGIN
SELECT Top 1 @uuid = uuid, @name = name, @trimmed_name = trimmed_name FROM #Temp
UPDATE PRODUCT
SET attributes_en_us.modify('replace value of (/attrs/attr[@name = sql:variable("@name")]/@name)[1] with sql:variable("@trimmed_name")')
WHERE uuid = @uuid
DELETE #Temp WHERE uuid = @uuid
END
DROP TABLE #Temp;
AV Custom Attributes
To migrate custom attributes that are stored in a separate AV database table, the following script needs to be executed, which works for both database systems.
The following placeholder needs to be replaced:
table_name→ the affected database table, e.g.CATALOGCATEGORY_AV
UPDATE table_name SET name = TRIM(name) WHERE name like ' %' OR name like '% ';
Example:
UPDATE CATALOGCATEGORY_AV SET name = TRIM(name) WHERE name like ' %' OR name like '% ';