Change a database column type in SAP Hybris Commerce

Overview


Hybris doesn’t allow changing column types or dropping columns on existing tables. To prevent data loss, they chose not to support these types of changes in a system update.

In this article, we’ll describe the steps involved in manually updating a column’s data type without data loss.


Scenario

Suppose we have a running Hybris application for ACME Corp with an extension named acmecore.

We’ve defined an enum type in acmecore-items.xml to represent shipping carriers:

<enumtype code="ShippingCarrierType" autocreate="true" generate="true">
  <value code="UPS"/>
  <value code="FEDEX"/>
  <value code="DHL"/>
</enumtype>

In addition, we’ve created an item type and corresponding table to store necessary data for shipping carriers:

<itemtype code="ShippingCarrier" autocreate="true" generate="true">
  <description>Shipping carriers</description>
  <deployment table="ShippingCarrier" typecode="12000" />
  <attributes>
    <attribute qualifier="carrier" type="ShippingCarrierType">
      <modifiers read="true" write="true" optional="false" unique="true" />
      <persistence type="property" />
    </attribute>
  </attributes>
</itemtype>

Below is what the table structure looks like in our database:

table1.PNG

The p_carrier column will store the PK of the enum type we defined above.

Due to changing business requirements, we now require the flexibility to add new types on the fly without a code change (USPS, Canada Post, etc.). So we decide to change the attribute type for carrier to java.lang.String.

The next section outlines the steps required to accomplish this.


Steps

Backup the data

Make sure the data is backed up before proceeding with the next steps.

Remove the reference to the column from the attributedescriptors table

DELETE FROM attributedescriptors
WHERE lower(columnName) = 'p_carrier' AND EnclosingTypePK 
IN (SELECT PK FROM composedtypes WHERE itemtypecode 
IN (SELECT typecode FROM ydeployments WHERE lower(tablename) = 'shippingcarrier'))
AND PersistenceTypePK IS NOT NULL;

Note: If attribute was localized (different languages), we would also need to remove from the attributedescriptorslp table.

Drop the column

ALTER TABLE shippingcarrier DROP COLUMN p_carrier;

Update the attribute type to java.lang.String

acmecore-items.xml

<attribute qualifier="carrier" type="java.lang.String">

Run a Hybris update

This can be done via HAC or by running ant updatesystem from the command line.

We should now see that the column has been re-created with the correct type.

Repopulate the table via Impex

INSERT ShippingCarrier;carrier;
;"UPS";
;"FEDEX";
;"DHL";