4.0 to 5.0 Custom Field Migration

The scripts for Custom Field data migration. Note that there are database specific and version specific differences.

Custom Field Module - data migration of BLC_INDEX_FIELD (All modules)

-- Add an Index Field record for each Field that has a "search type"
INSERT INTO BLC_INDEX_FIELD
  (
   INDEX_FIELD_ID,
   SEARCHABLE,
   FIELD_ID
  )
  SELECT FIELD_ID,
         SEARCHABLE,
         FIELD_ID
  FROM BLC_FIELD;

Data migration of BLC_INDEX_FIELD_TYPE and BLC_FIELD (MySQL, All modules)

-- Migrate Field Search Types to Index Field Type (mysql only)
INSERT INTO BLC_INDEX_FIELD_TYPE
(
 INDEX_FIELD_TYPE_ID,
 FIELD_TYPE,
 INDEX_FIELD_ID
)
SELECT @rownum := @rownum + 1 AS id,
       SEARCHABLE_FIELD_TYPE,
       FIELD_ID
FROM BLC_FIELD_SEARCH_TYPES, 
  (SELECT @rownum := 0) r;

INSERT INTO BLC_INDEX_FIELD_TYPE
(
 INDEX_FIELD_TYPE_ID,
 FIELD_TYPE,
 INDEX_FIELD_ID
)
SELECT @rownum := @rownum + 1 AS id,
       FACET_FIELD_TYPE,
       FIELD_ID
FROM BLC_FIELD, 
  (SELECT @rownum := (select max(INDEX_FIELD_TYPE_ID) from BLC_INDEX_FIELD_TYPE)) r 
WHERE FACET_FIELD_TYPE is not null and not exists (select * from BLC_INDEX_FIELD_TYPE where FIELD_TYPE = FACET_FIELD_TYPE and INDEX_FIELD_ID = FIELD_ID);

Data migration of BLC_INDEX_FIELD_TYPE (SQL Server Only, All modules)

-- Migrate Field Search Types to Index Field Type (mysql only)
SELECT null as ID,
       SEARCHABLE_FIELD_TYPE as FIELD_TYPE,
       FIELD_ID as INDEX_FIELD_ID
into BLC_TMP_INDEX_FIELD_TYPE
FROM BLC_FIELD_SEARCH_TYPES;
GO

INSERT INTO BLC_TMP_INDEX_FIELD_TYPE
SELECT NULL,
       FACET_FIELD_TYPE,
       FIELD_ID
FROM BLC_FIELD
WHERE FACET_FIELD_TYPE is not null and not exists (select * from BLC_TMP_INDEX_FIELD_TYPE where FIELD_TYPE = FACET_FIELD_TYPE and INDEX_FIELD_ID = FIELD_ID);
GO 

DECLARE @id INT 
SET @id = 0 
UPDATE BLC_TMP_INDEX_FIELD_TYPE 
SET @id = ID = @id + 1 
GO

INSERT INTO BLC_INDEX_FIELD_TYPE
(
 INDEX_FIELD_TYPE_ID,
 FIELD_TYPE,
 INDEX_FIELD_ID
)
SELECT * from BLC_TMP_INDEX_FIELD_TYPE
GO

DROP TABLE BLC_TMP_INDEX_FIELD_TYPE
GO

Data migration of BLC_INDEX_FIELD_TYPE and BLC_FIELD (Oracle, All modules)

-- Migrate Field Search Types to Index Field Type (oracle only)
CREATE SEQUENCE idx_field_type;

INSERT INTO BLC_INDEX_FIELD_TYPE
(
 INDEX_FIELD_TYPE_ID,
 FIELD_TYPE,
 INDEX_FIELD_ID
)
SELECT idx_field_type.nextval,
       SEARCHABLE_FIELD_TYPE,
       FIELD_ID
FROM BLC_FIELD_SEARCH_TYPES;

INSERT INTO BLC_INDEX_FIELD_TYPE
(
 INDEX_FIELD_TYPE_ID,
 FIELD_TYPE,
 INDEX_FIELD_ID
)
SELECT idx_field_type.nextval,
       FACET_FIELD_TYPE,
       FIELD_ID
FROM BLC_FIELD
WHERE FACET_FIELD_TYPE is not null and not exists (select * from BLC_INDEX_FIELD_TYPE where FIELD_TYPE = FACET_FIELD_TYPE and INDEX_FIELD_ID = FIELD_ID);

DROP SEQUENCE idx_field_type;

Data migration for BLC_SEARCH_FACET (MySQL, All modules)

UPDATE BLC_SEARCH_FACET facet 
join BLC_FIELD field on facet.FIELD_ID = field.FIELD_ID 
join BLC_INDEX_FIELD_TYPE type on field.FIELD_ID = type.INDEX_FIELD_ID and field.FACET_FIELD_TYPE = type.FIELD_TYPE 
SET facet.INDEX_FIELD_TYPE_ID = type.INDEX_FIELD_TYPE_ID;

Data migration for BLC_SEARCH_FACET (SQL Server, All modules)

UPDATE facet
SET facet.INDEX_FIELD_TYPE_ID = type.INDEX_FIELD_TYPE_ID
from BLC_SEARCH_FACET as facet
join BLC_FIELD as field on facet.FIELD_ID = field.FIELD_ID 
join BLC_INDEX_FIELD_TYPE as type on field.FIELD_ID = type.INDEX_FIELD_ID and field.FACET_FIELD_TYPE = type.FIELD_TYPE;

Data migration for BLC_SEARCH_FACET (Oracle, All modules)

merge into BLC_SEARCH_FACET facet
using (select blcField.FIELD_ID as FIELD_ID, idxFieldType.INDEX_FIELD_TYPE_ID
from BLC_INDEX_FIELD_TYPE idxFieldType
join BLC_FIELD blcField on blcField.FIELD_ID = idxFieldType.INDEX_FIELD_ID
join BLC_SEARCH_FACET facet on facet.FIELD_ID = blcField.FIELD_ID
where blcField.FACET_FIELD_TYPE = idxFieldType.FIELD_TYPE) sq
on (facet.FIELD_ID = sq.FIELD_ID)
when matched then update set facet.INDEX_FIELD_TYPE_ID = sq.INDEX_FIELD_TYPE_ID

Data migration of BLC_CUSTOM_FIELD to BLC_FIELD (Multi-Tenant)

-- Add a Field record for each Custom Field
-- Field_Id defaults to the opposite of the sum of the Custom_Field_Id and 100
-- Field_Id defaults to (-1 * (100 + Custom_Field_id))
INSERT INTO BLC_FIELD
  (
   FIELD_ID,
   ABBREVIATION,
   ENTITY_TYPE,
   FRIENDLY_NAME,
   PROPERTY_NAME,
   SEARCHABLE,
   SITE_DISC
  )
  SELECT (-1 * (100 + CUSTOM_FIELD_ID)),
         ATTR_NAME,
         CUSTOM_FIELD_TARGET,
         FRIENDLY_NAME,
         ATTR_NAME,
         SEARCHABLE,
         SITE_DISC
  FROM BLC_CUSTOM_FIELD;

Data migration of BLC_CUSTOM_FIELD to BLC_FIELD (Enterprise-only)

-- Add a Field record for each Custom Field
-- Field_Id defaults to the opposite of the sum of the Custom_Field_Id and 100
-- Field_Id defaults to (-1 * (100 + Custom_Field_id))
INSERT INTO BLC_FIELD
  (
   FIELD_ID,
   ABBREVIATION,
   ENTITY_TYPE,
   FRIENDLY_NAME,
   PROPERTY_NAME,
   SEARCHABLE
  )
  SELECT (-1 * (100 + CUSTOM_FIELD_ID)),
         ATTR_NAME,
         CUSTOM_FIELD_TARGET,
         FRIENDLY_NAME,
         ATTR_NAME,
         SEARCHABLE
  FROM BLC_CUSTOM_FIELD;

Set FIELD_ID in BLC_CUSTOM_FIELD (all)

-- Add Field Ids to Custom Field
UPDATE BLC_CUSTOM_FIELD SET FIELD_ID = -1 * (100 + CUSTOM_FIELD_ID);