Upgrading Drupal Commerce data - 1047078

The post now contains what I would call a full upgrade path from alpha to beta. I might be missing a detail in this post, but for the most part, this is what you need to do.

Note: This post is written and have since been updated twice. The reason for this, is that when I updated the commerce site I maintain, I did it in these steps. This means you need to complete all the steps to complete the upgrade from alpha to beta.

Not so long ago, 31st of January, a lot of changed were made in Drupal Commerce that affect the data structure. There is an issue on drupal.org that describes this. The idea is to prefix all the fields that commerce make with commerce_ to make sure it doesn't conflict with what the site could possible have already. The idea behind this is very good, however since Drupal Commerce is still in alpha, there is no upgrade support. Since I'm one of the few that have a live site running on Drupal Commerce, I didn't have the luxury of nuking the database with the latest changes. Instead I went through all the changes, and figured out what needed to be done to get my database up to speed, while maintaining my data.

Upgrading the database

The first step, is to upgrade the database. Note, I made this based on what my database looked like, since I just needed to make a one shot upgrade script. I run my site on PostgreSQL and the fields are created through the Field API, so if you need to upgrade, this might not be a 100% match. If that's the case, you can use this to get the idea of what needs to be done, and maybe tweak this script to your own install.

ALTER TABLE field_data_purchase_price RENAME TO field_data_commerce_price;
ALTER TABLE field_data_commerce_price RENAME purchase_price_amount TO commerce_price_amount;
ALTER TABLE field_data_commerce_price RENAME purchase_price_currency_code TO commerce_price_currency_code;
ALTER TABLE field_data_commerce_price RENAME purchase_price_data TO commerce_price_data;
ALTER TABLE field_revision_purchase_price RENAME TO field_revision_commerce_price;
ALTER TABLE field_revision_commerce_price RENAME purchase_price_amount TO commerce_price_amount;
ALTER TABLE field_revision_commerce_price RENAME purchase_price_currency_code TO commerce_price_currency_code;
ALTER TABLE field_revision_commerce_price RENAME purchase_price_data TO commerce_price_data;
UPDATE field_config SET field_name = 'commerce_price' WHERE field_name = 'purchase_price';
UPDATE field_config_instance SET field_name = 'commerce_price' WHERE field_name = 'purchase_price';

ALTER TABLE field_data_unit_price RENAME TO field_data_commerce_unit_price;
ALTER TABLE field_data_commerce_unit_price RENAME unit_price_amount TO commerce_unit_price_amount;
ALTER TABLE field_data_commerce_unit_price RENAME unit_price_currency_code TO commerce_unit_price_currency_code;
ALTER TABLE field_data_commerce_unit_price RENAME unit_price_data TO commerce_unit_price_data;
ALTER TABLE field_revision_unit_price RENAME TO field_revision_commerce_unit_price;
ALTER TABLE field_revision_commerce_unit_price RENAME unit_price_amount TO commerce_unit_price_amount;
ALTER TABLE field_revision_commerce_unit_price RENAME unit_price_currency_code TO commerce_unit_price_currency_code;
ALTER TABLE field_revision_commerce_unit_price RENAME unit_price_data TO commerce_unit_price_data;
UPDATE field_config SET field_name = 'commerce_unit_price' WHERE field_name = 'unit_price';
UPDATE field_config_instance SET field_name = 'commerce_unit_price' WHERE field_name = 'unit_price';

ALTER TABLE field_data_total RENAME TO field_data_commerce_total;
ALTER TABLE field_data_commerce_total RENAME total_amount TO commerce_total_amount;
ALTER TABLE field_data_commerce_total RENAME total_currency_code TO commerce_total_currency_code;
ALTER TABLE field_data_commerce_total RENAME total_data TO commerce_total_data;
ALTER TABLE field_revision_total RENAME TO field_revision_commerce_total;
ALTER TABLE field_revision_commerce_total RENAME total_amount TO commerce_total_amount;
ALTER TABLE field_revision_commerce_total RENAME total_currency_code TO commerce_total_currency_code;
ALTER TABLE field_revision_commerce_total RENAME total_data TO commerce_total_data;
UPDATE field_config SET field_name = 'commerce_total' WHERE field_name = 'total';
UPDATE field_config_instance SET field_name = 'commerce_total' WHERE field_name = 'total';

ALTER TABLE field_data_line_items RENAME TO field_data_commerce_line_items;
ALTER TABLE field_data_commerce_line_items RENAME line_items_line_item_id TO commerce_line_items_line_item_id;
ALTER TABLE field_revision_line_items RENAME TO field_revision_commerce_line_items;
ALTER TABLE field_revision_commerce_line_items RENAME line_items_line_item_id TO commerce_line_items_line_item_id;
UPDATE field_config SET field_name = 'commerce_line_items' WHERE field_name = 'line_items';
UPDATE field_config_instance SET field_name = 'commerce_line_items' WHERE field_name = 'line_items';

ALTER TABLE field_data_product RENAME TO field_data_commerce_product;
ALTER TABLE field_data_commerce_product RENAME product_product_id TO commerce_product_product_id;
ALTER TABLE field_revision_product RENAME TO field_revision_commerce_product;
ALTER TABLE field_revision_commerce_product RENAME product_product_id TO commerce_product_product_id;
UPDATE field_config SET field_name = 'commerce_product' WHERE field_name = 'product';
UPDATE field_config_instance SET field_name = 'commerce_product' WHERE field_name = 'product';

ALTER TABLE commerce_line_item ADD order_id INTEGER NOT NULL DEFAULT 0;
ALTER TABLE commerce_line_item ALTER order_id DROP DEFAULT;

UPDATE commerce_line_item AS l SET order_id = entity_id
FROM field_data_commerce_line_items AS f
WHERE f.commerce_line_items_line_item_id = l.line_item_id;

This is a lot of SQL, especially the last query, which updates the newly created order_id on the commerce_line_item table might not work on MySQL.

Adding the new field

Drupal Commerce has also added a new field, the commerce_order_total for orders. This is used to store the order total, and also needs to be added. This is best done through Drupal/PHP with the following PHP script.

<?php

commerce_price_create_instance('commerce_order_total', 'commerce_order', 'commerce_order', t('Order total'), -8);

$result = db_query("SELECT order_id FROM {commerce_order}");
  foreach ($result as $row) {
    $order = commerce_order_load($row->order_id);
    if (empty($order->commerce_order_total)) {
      $order->commerce_order_total['und'][] = array(
        'amount' => 0,
        'currency' => commerce_default_currency(),
      );
      commerce_order_save($order);
    }
  }

?>

The first line adds the field for the commerce_order entity of type commerce_order which is what commerce creates as default. If you have created your own commerce_order bundle a new you would need to run that query for them as well. Since Drupal Commerce expects orders to have a value in the commerce_order_total field, you will get fatal errors unless you give put in a value. In the save method Drupal Commerce will calculate the value and currency for the order total, so all you need to do, is put in any value and save the order, and let Drupal Commerce do the rest.

So upgrading your Drupal Commerce database isn't such a big hassle after all, though it took a bit of time to figure out all the database changes that were made. I hope there is a few of your out there that can save some time reading this, instead of going through the process yourself.

The winds of change are blowing across Drupal commerce

Since I originally wrote this blog post a lot has changed in the commerce database structure, so I'm writing an update of queries and code to run. One thing I actually missed in the original post, was the billing profile changes, they are included here

ALTER TABLE commerce_order ADD hostname VARCHAR(128) NOT NULL DEFAULT '';
ALTER TABLE commerce_order_revision ADD revision_hostname VARCHAR(128) NOT NULL DEFAULT '';

ALTER TABLE field_data_customer_billing RENAME TO field_data_commerce_customer_billing;
ALTER TABLE field_data_commerce_customer_billing RENAME customer_billing_profile_id TO commerce_customer_billing_profile_id;
ALTER TABLE field_revision_customer_billing RENAME TO field_revision_commerce_customer_billing;
ALTER TABLE field_revision_commerce_customer_billing RENAME customer_billing_profile_id TO commerce_customer_billing_profile_id;
UPDATE field_config SET field_name = 'commerce_customer_billing' WHERE field_name = 'customer_billing';
UPDATE field_config_instance SET field_name = 'commerce_customer_billing' WHERE field_name = 'customer_billing';


ALTER TABLE field_data_commerce_price ALTER commerce_price_amount TYPE INTEGER;
UPDATE field_data_commerce_price SET commerce_price_amount = commerce_price_amount * 100;
ALTER TABLE field_revision_commerce_price ALTER commerce_price_amount TYPE INTEGER;
UPDATE field_revision_commerce_price SET commerce_price_amount = commerce_price_amount * 100;

ALTER TABLE field_data_commerce_total ALTER commerce_total_amount TYPE INTEGER;
UPDATE field_data_commerce_total SET commerce_total_amount = commerce_total_amount * 100;
ALTER TABLE field_revision_commerce_total ALTER commerce_total_amount TYPE INTEGER;
UPDATE field_revision_commerce_total SET commerce_total_amount = commerce_total_amount * 100;

ALTER TABLE field_data_commerce_order_total ALTER commerce_order_total_amount TYPE INTEGER;
UPDATE field_data_commerce_order_total SET commerce_order_total_amount = commerce_order_total_amount * 100;
ALTER TABLE field_revision_commerce_order_total ALTER commerce_order_total_amount TYPE INTEGER;
UPDATE field_revision_commerce_order_total SET commerce_order_total_amount = commerce_order_total_amount * 100;

ALTER TABLE field_data_commerce_unit_price ALTER commerce_unit_price_amount TYPE INTEGER;
UPDATE field_data_commerce_unit_price SET commerce_unit_price_amount = commerce_unit_price_amount * 100;
ALTER TABLE field_revision_commerce_unit_price ALTER commerce_unit_price_amount TYPE INTEGER;
UPDATE field_revision_commerce_unit_price SET commerce_unit_price_amount = commerce_unit_price_amount * 100;

ALTER TABLE field_data_field_product_discount_price ALTER field_product_discount_price_amount TYPE INTEGER;
UPDATE field_data_field_product_discount_price SET field_product_discount_price_amount = field_product_discount_price_amount * 100;
ALTER TABLE field_revision_field_product_discount_price ALTER field_product_discount_price_amount TYPE INTEGER;
UPDATE field_revision_field_product_discount_price SET field_product_discount_price_amount = field_product_discount_price_amount * 100;

ALTER TABLE commerce_payment_transaction ALTER amount TYPE INTEGER;
UPDATE commerce_payment_transaction SET amount = amount * 100;
ALTER TABLE commerce_payment_transaction_revision ALTER amount TYPE INTEGER;
UPDATE commerce_payment_transaction_revision SET amount = amount * 100;

ALTER TABLE commerce_line_item ADD COLUMN data bytea;
COMMENT ON COLUMN commerce_line_item.data IS 'A serialized array of additional data.';

In addition to the SQL queries, some PHP code is needed to be run as well.

<?php
// Look for or add a display path textfield to the product line item type.
$field_name = 'commerce_display_path';
$field = field_info_field($field_name);
$instance = field_info_instance('commerce_line_item', $field_name, 'product');

if (empty($field)) {
 $field = array(
   'field_name' => $field_name,
   'type' => 'text',
   'cardinality' => 1,
   'entity_types' => array('commerce_line_item'),
   'translatable' => FALSE,
   'locked' => TRUE,
 );
 $field = field_create_field($field);
}

if (empty($instance)) {
 $instance = array(
   'field_name' => $field_name,
   'entity_type' => 'commerce_line_item',
   'bundle' => 'product',
   'label' => t('Display path'),
   'required' => TRUE,
   'settings' => array(),

   'widget' => array(
     'type' => 'text_textfield',
   ),

   'display' => array(
     'display' => array(
       'label' => 'hidden',
     ),
   ),
 );
 field_create_instance($instance);
}

$result = db_query("SELECT line_item_id FROM {commerce_line_item} WHERE type = 'product';");
foreach ($result as $row) {
  $line_item = commerce_line_item_load($row->line_item_id);
  $line_item->data['display_uri'] = NULL;
  $line_item_wrapper = entity_metadata_wrapper('commerce_line_item', $line_item);
  $line_item_wrapper->commerce_display_path = '';
  $line_item_wrapper->save();
}
?>

Beta is here

The final upgrade for beta is only a few data fields, and a name change of address field. The SQL for the update:

ALTER TABLE commerce_customer_profile ADD COLUMN data bytea;
COMMENT ON COLUMN commerce_line_item.data IS 'A serialized array of additional data.';
ALTER TABLE commerce_customer_profile_revision ADD COLUMN data bytea;
COMMENT ON COLUMN commerce_line_item.data IS 'A serialized array of additional data.';

ALTER TABLE commerce_payment_transaction ADD COLUMN data bytea;
COMMENT ON COLUMN commerce_line_item.data IS 'A serialized array of additional data.';
ALTER TABLE commerce_payment_transaction_revision ADD COLUMN data bytea;
COMMENT ON COLUMN commerce_line_item.data IS 'A serialized array of additional data.';

ALTER TABLE commerce_product ADD COLUMN data bytea;
COMMENT ON COLUMN commerce_line_item.data IS 'A serialized array of additional data.';


ALTER TABLE field_data_billing_address RENAME TO field_data_commerce_customer_address;
ALTER TABLE field_data_commerce_customer_address RENAME billing_address_country TO commerce_customer_address_country;
ALTER TABLE field_data_commerce_customer_address RENAME billing_address_administrative_area TO commerce_customer_address_administrative_area;
ALTER TABLE field_data_commerce_customer_address RENAME billing_address_sub_administrative_area TO commerce_customer_address_sub_administrative_area;
ALTER TABLE field_data_commerce_customer_address RENAME billing_address_locality TO commerce_customer_address_locality;
ALTER TABLE field_data_commerce_customer_address RENAME billing_address_dependent_locality TO commerce_customer_address_dependent_locality;
ALTER TABLE field_data_commerce_customer_address RENAME billing_address_postal_code TO commerce_customer_address_postal_code;
ALTER TABLE field_data_commerce_customer_address RENAME billing_address_thoroughfare TO commerce_customer_address_thoroughfare;
ALTER TABLE field_data_commerce_customer_address RENAME billing_address_premise TO commerce_customer_address_premise;
ALTER TABLE field_data_commerce_customer_address RENAME billing_address_sub_premise TO commerce_customer_address_sub_premise;
ALTER TABLE field_data_commerce_customer_address RENAME billing_address_data TO commerce_customer_address_data;

ALTER TABLE field_revision_billing_address RENAME TO field_revision_commerce_customer_address;
ALTER TABLE field_revision_commerce_customer_address RENAME billing_address_country TO commerce_customer_address_country;
ALTER TABLE field_revision_commerce_customer_address RENAME billing_address_administrative_area TO commerce_customer_address_administrative_area;
ALTER TABLE field_revision_commerce_customer_address RENAME billing_address_sub_administrative_area TO commerce_customer_address_sub_administrative_area;
ALTER TABLE field_revision_commerce_customer_address RENAME billing_address_locality TO commerce_customer_address_locality;
ALTER TABLE field_revision_commerce_customer_address RENAME billing_address_dependent_locality TO commerce_customer_address_dependent_locality;
ALTER TABLE field_revision_commerce_customer_address RENAME billing_address_postal_code TO commerce_customer_address_postal_code;
ALTER TABLE field_revision_commerce_customer_address RENAME billing_address_thoroughfare TO commerce_customer_address_thoroughfare;
ALTER TABLE field_revision_commerce_customer_address RENAME billing_address_premise TO commerce_customer_address_premise;
ALTER TABLE field_revision_commerce_customer_address RENAME billing_address_sub_premise TO commerce_customer_address_sub_premise;
ALTER TABLE field_revision_commerce_customer_address RENAME billing_address_data TO commerce_customer_address_data;

UPDATE field_config SET field_name = 'commerce_customer_address' WHERE field_name = 'billing_address';
UPDATE field_config_instance SET field_name = 'commerce_customer_address' WHERE field_name = 'billing_address';

When I did this upgrade I also updated rules and entity. When I did this on the live site I ran into a problem I didn't when I tested this out. Entity menu looks like this:

function entity_menu() {
  $items = array();
  foreach (entity_ui_controller() as $controller) {
    $items += $controller->hook_menu();
  }
  return $items;
}

The problem here is that entity_ui_controller returns a cached list of controllers. For Entity beta6, Rules included here, but for version beta7 Rules shouldn't be included and its UI controller doesn't have the hook_menu method. So when clearing the cache this gave a fatal error before the entity_ui_controller cache was cleared. My fix was to let entity_ui_controller return an empty array, clear cache, remove the code and clear cache once more. This seems to work fine. It's important to clear cache after changing anything in the fields config tables, since field settings are cached.

Following this guide should allow you to safely enter beta. From here on out, commerce will include upgrades for any data changes needed.

Have fun.

Comments

For a newer drupal user I really like these posts that have pictures of code - though I don't entirely understand what it all means it makes it easier to learn :)
Dave

Add new comment