1.6 to 2.0 Data Migration

Catalog Changes

Products and Skus

The bulk of these changes are centered around the changes in how Products relate to Skus (see Main Entities for how the relationships work now). If you had a custom Product subclass, you would normally extend ProductSkuImpl since most framework users have the assumption that a Product itself can be sold. Since this is the most common case, we have removed the ProductSkuImpl entity entirely, and pushed the one-to-one relationship from Product to Sku up to the ProductImpl entity, and moved fields that were in ProductImpl toSkuImpl.

Using ProductSkuImpl as the main entity

For 1.6.0 and prior, the most common use of the catalog domain was to subclass ProductSkuImpl. This is also the entity that the admin had out-of-the-box support for. In order to move your database tables and migrate your data to the new paradigm, there are a few steps to take to ensure your data ends up in the right place:

Remove duplicated columns between BLC_PRODUCT and BLC_SKU - these were currently being tracked side-by-side as far as the admin is concerned so data should be the same in both tables.
The following columns have been removed from BLC_PRODUCT in favor of their duplicates in BLC_SKU:

`ACTIVE_END_DATE` datetime DEFAULT NULL
`ACTIVE_START_DATE` datetime DEFAULT NULL
`DESCRIPTION` varchar(255) DEFAULT NULL
`LONG_DESCRIPTION` longtext
`NAME` varchar(255) NOT NULL

These are removed from the BLC_PRODUCT table with the following SQL:

ALTER TABLE `BLC_PRODUCT` DROP KEY `PRODUCT_NAME_INDEX`;
ALTER TABLE `BLC_PRODUCT` DROP COLUMN `NAME`;
ALTER TABLE `BLC_PRODUCT` DROP COLUMN `DESCRIPTION`;
ALTER TABLE `BLC_PRODUCT` DROP COLUMN `LONG_DESCRIPTION`;
ALTER TABLE `BLC_PRODUCT` DROP COLUMN `ACTIVE_START_DATE`;
ALTER TABLE `BLC_PRODUCT` DROP COLUMN `ACTIVE_END_DATE`;

Move columns that are currently being tracked in BLC_PRODUCT to where they are moved to in BLC_SKU
The following columns have been moved from BLC_PRODUCT into BLC_SKU:

`CONTAINER_SHAPE` varchar(255) DEFAULT NULL,
`DEPTH` decimal(19,2) DEFAULT NULL,
`DIMENSION_UNIT_OF_MEASURE` varchar(255) DEFAULT NULL,
`GIRTH` decimal(19,2) DEFAULT NULL,
`HEIGHT` decimal(19,2) DEFAULT NULL,
`CONTAINER_SIZE` varchar(255) DEFAULT NULL,
`WIDTH` decimal(19,2) DEFAULT NULL,
`IS_MACHINE_SORTABLE` bit(1) DEFAULT NULL,
`WEIGHT` decimal(19,2) DEFAULT NULL,
`WEIGHT_UNIT_OF_MEASURE` varchar(255) DEFAULT NULL,

To add these to your BLC_SKU table, run the following script:

ALTER TABLE `BLC_SKU` ADD COLUMN `CONTAINER_SHAPE` varchar(255) DEFAULT NULL;
ALTER TABLE `BLC_SKU` ADD COLUMN `DEPTH` decimal(19,2) DEFAULT NULL;
ALTER TABLE `BLC_SKU` ADD COLUMN `DIMENSION_UNIT_OF_MEASURE` varchar(255) DEFAULT NULL;
ALTER TABLE `BLC_SKU` ADD COLUMN `GIRTH` decimal(19,2) DEFAULT NULL;
ALTER TABLE `BLC_SKU` ADD COLUMN `HEIGHT` decimal(19,2) DEFAULT NULL;
ALTER TABLE `BLC_SKU` ADD COLUMN `CONTAINER_SIZE` varchar(255) DEFAULT NULL;
ALTER TABLE `BLC_SKU` ADD COLUMN `WIDTH` decimal(19,2) DEFAULT NULL;
ALTER TABLE `BLC_SKU` ADD COLUMN `IS_MACHINE_SORTABLE` bit(1) DEFAULT NULL;
ALTER TABLE `BLC_SKU` ADD COLUMN `WEIGHT` decimal(19,2) DEFAULT NULL;
ALTER TABLE `BLC_SKU` ADD COLUMN `WEIGHT_UNIT_OF_MEASURE` varchar(255) DEFAULT NULL;

Now you can import the data from those fields in BLC_PRODUCT:

UPDATE `BLC_SKU` AS sku SET sku.CONTAINER_SHAPE=(SELECT product.CONTAINER_SHAPE FROM BLC_PRODUCT AS product, BLC_PRODUCT_SKU WHERE BLC_PRODUCT_SKU.SKU_ID = sku.SKU_ID AND product.PRODUCT_ID = BLC_PRODUCT_SKU.PRODUCT_ID);
UPDATE `BLC_SKU` AS sku SET sku.CONTAINER_SIZE=(SELECT product.CONTAINER_SIZE FROM BLC_PRODUCT AS product, BLC_PRODUCT_SKU WHERE BLC_PRODUCT_SKU.SKU_ID = sku.SKU_ID AND product.PRODUCT_ID = BLC_PRODUCT_SKU.PRODUCT_ID);
UPDATE `BLC_SKU` AS sku SET sku.DEPTH=(SELECT product.DEPTH FROM BLC_PRODUCT AS product, BLC_PRODUCT_SKU WHERE BLC_PRODUCT_SKU.SKU_ID = sku.SKU_ID AND product.PRODUCT_ID = BLC_PRODUCT_SKU.PRODUCT_ID);
UPDATE `BLC_SKU` AS sku SET sku.DIMENSION_UNIT_OF_MEASURE=(SELECT product.DIMENSION_UNIT_OF_MEASURE FROM BLC_PRODUCT AS product, BLC_PRODUCT_SKU WHERE BLC_PRODUCT_SKU.SKU_ID = sku.SKU_ID AND product.PRODUCT_ID = BLC_PRODUCT_SKU.PRODUCT_ID);
UPDATE `BLC_SKU` AS sku SET sku.GIRTH=(SELECT product.GIRTH FROM BLC_PRODUCT AS product, BLC_PRODUCT_SKU WHERE BLC_PRODUCT_SKU.SKU_ID = sku.SKU_ID AND product.PRODUCT_ID = BLC_PRODUCT_SKU.PRODUCT_ID);
UPDATE `BLC_SKU` AS sku SET sku.HEIGHT=(SELECT product.HEIGHT FROM BLC_PRODUCT AS product, BLC_PRODUCT_SKU WHERE BLC_PRODUCT_SKU.SKU_ID = sku.SKU_ID AND product.PRODUCT_ID = BLC_PRODUCT_SKU.PRODUCT_ID);
UPDATE `BLC_SKU` AS sku SET sku.WIDTH=(SELECT product.WIDTH FROM BLC_PRODUCT AS product, BLC_PRODUCT_SKU WHERE BLC_PRODUCT_SKU.SKU_ID = sku.SKU_ID AND product.PRODUCT_ID = BLC_PRODUCT_SKU.PRODUCT_ID);
UPDATE `BLC_SKU` AS sku SET sku.IS_MACHINE_SORTABLE=(SELECT product.IS_MACHINE_SORTABLE FROM BLC_PRODUCT AS product, BLC_PRODUCT_SKU WHERE BLC_PRODUCT_SKU.SKU_ID = sku.SKU_ID AND product.PRODUCT_ID = BLC_PRODUCT_SKU.PRODUCT_ID);
UPDATE `BLC_SKU` AS sku SET sku.WEIGHT=(SELECT product.WEIGHT FROM BLC_PRODUCT AS product, BLC_PRODUCT_SKU WHERE BLC_PRODUCT_SKU.SKU_ID = sku.SKU_ID AND product.PRODUCT_ID = BLC_PRODUCT_SKU.PRODUCT_ID);
UPDATE `BLC_SKU` AS sku SET sku.WEIGHT_UNIT_OF_MEASURE=(SELECT product.WEIGHT_UNIT_OF_MEASURE FROM BLC_PRODUCT AS product, BLC_PRODUCT_SKU WHERE BLC_PRODUCT_SKU.SKU_ID = sku.SKU_ID AND product.PRODUCT_ID = BLC_PRODUCT_SKU.PRODUCT_ID);

Delete the unnecessary columns from BLC_PRODUCT - now that the data has been moved, no need to keep these around

ALTER TABLE `BLC_PRODUCT` DROP COLUMN CONTAINER_SHAPE;
ALTER TABLE `BLC_PRODUCT` DROP COLUMN CONTAINER_SIZE;
ALTER TABLE `BLC_PRODUCT` DROP COLUMN DEPTH;
ALTER TABLE `BLC_PRODUCT` DROP COLUMN DIMENSION_UNIT_OF_MEASURE;
ALTER TABLE `BLC_PRODUCT` DROP COLUMN GIRTH;
ALTER TABLE `BLC_PRODUCT` DROP COLUMN HEIGHT;
ALTER TABLE `BLC_PRODUCT` DROP COLUMN WIDTH;
ALTER TABLE `BLC_PRODUCT` DROP COLUMN IS_MACHINE_SORTABLE;
ALTER TABLE `BLC_PRODUCT` DROP COLUMN WEIGHT;
ALTER TABLE `BLC_PRODUCT` DROP COLUMN WEIGHT_UNIT_OF_MEASURE;

Create new columns in BLC_PRODUCT

ALTER TABLE `BLC_PRODUCT` ADD COLUMN `ARCHIVED` char(1) DEFAULT NULL;
ALTER TABLE `BLC_PRODUCT` ADD COLUMN `DISPLAY_TEMPLATE` varchar(255) DEFAULT NULL;
ALTER TABLE `BLC_PRODUCT` ADD COLUMN `URL` varchar(255) DEFAULT NULL;
ALTER TABLE `BLC_PRODUCT` ADD COLUMN `URL_KEY` varchar(255) DEFAULT NULL;
ALTER TABLE `BLC_PRODUCT` ADD COLUMN `DEFAULT_SKU_ID` bigint(20) NOT NULL;

Move the BLC_PRODUCT_MEDIA_MAP to be handled at the Sku level - BLC_PRODUCT_MEDIA_MAP has been deprecated and is maintained at a Product's defaultSku. To move your data, run the following script:

INSERT INTO BLC_SKU_MEDIA_MAP (SELECT PRODUCT_SKU.SKU_ID, MEDIA_ID, MAP_KEY FROM BLC_PRODUCT_MEDIA_MAP AS PRODUCT_MEDIA_MAP, BLC_PRODUCT_SKU AS PRODUCT_SKU WHERE PRODUCT_MEDIA_MAP.BLC_PRODUCT_PRODUCT_ID = PRODUCT_SKU.PRODUCT_ID);

Populate the DEFAULT_SKU column in BLC_PRODUCT

UPDATE `BLC_PRODUCT` AS product SET `DEFAULT_SKU_ID`=(SELECT `SKU_ID` FROM `BLC_PRODUCT_SKU` WHERE BLC_PRODUCT_SKU.PRODUCT_ID = product.PRODUCT_ID);

Add relationship keys for DEFAULT_SKU_ID

ALTER TABLE `BLC_PRODUCT` ADD CONSTRAINT UNIQUE KEY `DEFAULT_SKU_ID` (`DEFAULT_SKU_ID`);
ALTER TABLE `BLC_PRODUCT` ADD KEY `FK5B95B7C96D386535` (`DEFAULT_SKU_ID`);
ALTER TABLE `BLC_PRODUCT` ADD CONSTRAINT `FK5B95B7C96D386535` FOREIGN KEY (`DEFAULT_SKU_ID`) REFERENCES `BLC_SKU` (`SKU_ID`);

Change foreign key reference in custom table subclass to reference BLC_PRODUCT
Assuming that you have a custom subclass of ProductSkuImpl (MyCompanyProductImpl) that maps to a custom table (MC_PRODUCT) that looks like this:

CREATE TABLE `MC_PRODUCT` (
  `MYCOMPANY_FIELD` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `PRODUCT_ID` bigint(20) NOT NULL,
  PRIMARY KEY (`PRODUCT_ID`),
  KEY `FK3CBEBADC689F939C` (`PRODUCT_ID`),
  CONSTRAINT `FK3CBEBADC689F939C` FOREIGN KEY (`PRODUCT_ID`) REFERENCES `BLC_PRODUCT_SKU` (`PRODUCT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

You can then drop and recreate the constraint that is on BLC_PRODUCT_SKU to instead just reference the BLC_PRODUCT table:

ALTER TABLE `MC_PRODUCT` DROP FOREIGN KEY `FK3CBEBADC689F939C`;
ALTER TABLE `MC_PRODUCT` ADD CONSTRAINT `FK3CBEBADC689F939C` FOREIGN KEY (`PRODUCT_ID`) REFERENCES `BLC_PRODUCT` (`PRODUCT_ID`);

Additional schema changes

You can run [[this script|sql/broadleaf-2.0-created.sql]] to add the additional tables and columns. This will not drop any tables, columns, indices, etc. It will simply create new tables and add additional columns and constraints that are required.

Tax Changes

Taxes have been refactored completely away from BLC_ORDER and moved to only being tracked via BLC_FULFILLMENT_GROUP. The top-level fields (COUNTY_TAX, STATE_TAX, etc) have been moved off of BLC_FULFILLMENT_GROUP and into a new table, BLC_TAX_DETAIL. BLC_TAX_DETAIL records are then related back to BLC_FULFILLMENT_GROUP records via BLC_FG_FG_TAX_REF. Since the handling of taxes is somewhat dependent on locale and your business and/or industry, we are not going to prescribe specifically how taxes should be migrated. However, we will identify table changes and provide some guidelines about how this might be accomplished.

`BLC_FULFILLMENT_GROUP`

Create a temporary table to hold the tax information:

CREATE TABLE `TMP_BLC_TAX_DETAIL` (
  `TAX_DETAIL_ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `AMOUNT` decimal(19,5),
  `TYPE` varchar(255),
  `ORDER_ID` bigint(20) DEFAULT NULL,
  `FULFILLMENT_GROUP_ID` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`TAX_DETAIL_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;

Export BLC_ORDER and BLC_FULFILLMENT_GROUP fields into the temporary table

INSERT INTO `TMP_BLC_TAX_DETAIL` (`ORDER_ID`, `AMOUNT`, `TYPE`) SELECT ORDER_ID, COUNTY_TAX, 'COUNTY' FROM BLC_ORDER;
INSERT INTO `TMP_BLC_TAX_DETAIL` (`ORDER_ID`, `AMOUNT`, `TYPE`) SELECT ORDER_ID, CITY_TAX, 'CITY' FROM BLC_ORDER;
INSERT INTO `TMP_BLC_TAX_DETAIL` (`ORDER_ID`, `AMOUNT`, `TYPE`) SELECT ORDER_ID, COUNTRY_TAX, 'COUNTRY' FROM BLC_ORDER;
INSERT INTO `TMP_BLC_TAX_DETAIL` (`ORDER_ID`, `AMOUNT`, `TYPE`) SELECT ORDER_ID, DISTRICT_TAX, 'DISTRICT' FROM BLC_ORDER;
INSERT INTO `TMP_BLC_TAX_DETAIL` (`ORDER_ID`, `AMOUNT`, `TYPE`) SELECT ORDER_ID, STATE_TAX, 'STATE' FROM BLC_ORDER;
INSERT INTO `TMP_BLC_TAX_DETAIL` (`FULFILLMENT_GROUP_ID`, `AMOUNT`, `TYPE`) SELECT FULFILLMENT_GROUP_ID, COUNTY_TAX, 'COUNTY' FROM BLC_FULFILLMENT_GROUP;
INSERT INTO `TMP_BLC_TAX_DETAIL` (`FULFILLMENT_GROUP_ID`, `AMOUNT`, `TYPE`) SELECT FULFILLMENT_GROUP_ID, CITY_TAX, 'CITY' FROM BLC_FULFILLMENT_GROUP;
INSERT INTO `TMP_BLC_TAX_DETAIL` (`FULFILLMENT_GROUP_ID`, `AMOUNT`, `TYPE`) SELECT FULFILLMENT_GROUP_ID, COUNTRY_TAX, 'COUNTRY' FROM BLC_FULFILLMENT_GROUP;
INSERT INTO `TMP_BLC_TAX_DETAIL` (`FULFILLMENT_GROUP_ID`, `AMOUNT`, `TYPE`) SELECT FULFILLMENT_GROUP_ID, DISTRICT_TAX, 'DISTRICT' FROM BLC_FULFILLMENT_GROUP;
INSERT INTO `TMP_BLC_TAX_DETAIL` (`FULFILLMENT_GROUP_ID`, `AMOUNT`, `TYPE`) SELECT FULFILLMENT_GROUP_ID, STATE_TAX, 'STATE' FROM BLC_FULFILLMENT_GROUP;

Export temporary fulfillment group tax data into BLC_TAX_DETAIL and BLC_FG_FG_TAX_REF
- There are a number of ways to do this using a stored procedure with cursors, or writing a Java program, etc. What you are trying to do is populate the BLC_TAX_DETAIL table with the the details from the temp table (especially where FULFILLMENT_GROUP_ID is not null - see the next bullet for moving order-specific tax details). Then you need to associate that tax detail with a particular fulfillment group. This is done in the BLC_FG_FG_TAX_REF. If you use a cursor in MySQL, it will look something like this: ```sql DELIMITER $$

DROP PROCEDURE IF EXISTS migrateTempTaxDetails$$
CREATE PROCEDURE migrateTempTaxDetails()
BEGIN
DECLARE no_more_rows BOOLEAN;
DECLARE fg_id,td_id BIGINT(20);
DECLARE amount DECIMAL(19,5);
DECLARE type VARCHAR(20);
DECLARE cur_1 CURSOR FOR SELECT TAX_DETAIL_ID, FULFILLMENT_GROUP_ID, AMOUNT, TYPE FROM TMP_BLC_TAX_DETAIL WHERE FULFILLMENT_GROUP_ID IS NOT NULL;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = TRUE;
OPEN cur_1;

the_loop: LOOP
FETCH cur_1 INTO td_id, fg_id, amount, type;

IF no_more_rows THEN
CLOSE cur_1;
LEAVE the_loop;
END IF;
START TRANSACTION;
INSERT INTO TAX_DETAIL (TAX_DETAIL_ID, AMOUNT, TYPE) values (td_id, amount, type);
INSERT INTO BLC_FG_FG_TAX_XREF (FULFILLMENT_GROUP_ID, TAX_DETAIL_ID) values (fg_id, td_id);
COMMIT;
END LOOP the_loop;

END$$

DELIMITER ;

4. **Move order-specific taxes to new model**
  - Since the tax columns on the order have gone away, you will have to determine how you want to save order-specific taxes on the fulfillment group. Since there may be more than one fulfillment group per order, you will have to determine how you want to split taxes among the fulfillment groups.  Use the records in `TMP_BLC_TAX_DETAIL` where `ORDER_ID` is not null.  You can then create `BLC_TAX_DETAIL` records with these as described above.  However, you will have to determine how to associate them with fulfillment groups in `BLC_FG_FG_TAX_REF`.

5. **Optionally, drop the temporary table - `DROP TABLE TMP_BLC_TAX_DETAIL;`**

6. **Optionally, drop stored procedure - `DROP PROCEDURE `migrateTempTaxDetails`;`**

7. **Optionally remove unneeded columns from `BLC_FULFILLMENT_GROUP` and `BLC_ORDER`**
    ```sql
    ## Optionally, drop old columns
    ALTER TABLE BLC_FULFILLMENT_GROUP DROP COLUMN `STATE_TAX`;
    ALTER TABLE BLC_FULFILLMENT_GROUP DROP COLUMN `CITY_TAX`;
    ALTER TABLE BLC_FULFILLMENT_GROUP DROP COLUMN `COUNTRY_TAX`;
    ALTER TABLE BLC_FULFILLMENT_GROUP DROP COLUMN `COUNTY_TAX`;
    ALTER TABLE BLC_FULFILLMENT_GROUP DROP COLUMN `DISTRICT_TAX`;
    ```

## Other existing table modifications
* `BLC_MEDIA`
    If you have existing data in this table, you will want to move your data to the new columns:
    ```sql
    ## New columns were added in the script above
    ## Transfer data from old columns to new
    UPDATE BLC_MEDIA SET `ALT_TEXT` = `LABEL`;
    UPDATE BLC_MEDIA SET `TITLE` = `NAME`;
    ## Optionally remove old columns
    ALTER TABLE BLC_MEDIA DROP COLUMN `LABEL`;
    ALTER TABLE BLC_MEDIA DROP COLUMN `NAME`;
    ALTER TABLE BLC_MEDIA DROP KEY `MEDIA_NAME_INDEX`;
    ```

## Raw Database Dumps
These dumps were generated by starting up test applications with BLC 1.6.0 and 2.0.0 on MySQL 5.5.14 and are included here for further reference
* [[Raw 1.6 Dump|sql/broadleaf-1.6-raw.sql]]
* [[Raw 2.0 Dump|sql/broadleaf-2.0-raw.sql]]