Migrating from WooCommerce to Classic Commerce

[ozfiddler]

@james

What may not always be so easy is changing from Woo to Classic Commerce, since we started from version 3.5.3 of Woo and from what I understand there have been some DB schema changes in later versions of Woo.

I think we need to initiate a place for discussion about this, as it will be a question that comes up more often. I still have two sites running the latest version of WordPress and WooCommerce, but I hope to eventually move them over to CP/CC. Especially given the latest debacle with the WP5.5 update breaking WooCommerce (as well as Classic Editor and Yoast ), so I am keen to move these two sites away as soon as possible.

Unfortunately I have kept WooCommerce fully updated, so I’ve got the DB changes installed now (and also all the other extra unnecessary bloat that came with the new version). I’ll do some testing on a local server and see what happens if I switch to CC.

Does anyone have any more information about what DB changes were involved?

[ozfiddler]

I have just gone through and compared a backup version of an old database with a current copy and these are the tables that have been added.

table wp_actionscheduler_actions
table wp_actionscheduler_claims
table wp_actionscheduler_groups
table wp_actionscheduler_logs
table wp_wc_admin_note_actions
table wp_wc_admin_notes
table wp_wc_category_lookup
table wp_wc_customer_lookup
table wp_wc_order_coupon_lookup
table wp_wc_order_product_lookup
table wp_wc_order_stats
table wp_wc_order_tax_lookup
table wp_wc_reserved_stock

The first ones would be for the addition of action scheduler (I think this is a replacement for WP-cron). I assume the rest of them are for the new reporting facility.

It looks like no tables have been removed (or changed as far as I can see).

[james]

Are you saying that no columns or column definitions were changed in existing tables either?

I think the next step to get a definitive answer here is going to be to identify which Woo versions actually made database changes and what those changes were. The majority of this information should be public on their GitHub.

Then from there, figure out whether these changes were actually backwards compatible (i.e. do newer versions still fill in expected data using the old schema). That will tell us whether or not we need to build some kind of “reverse migration” thing for recent Woo to CC.

@ozfiddler

I’ll do some testing on a local server and see what happens if I switch to CC.

This is another good next step.

[ozfiddler]

@james

Are you saying that no columns or column definitions were changed in existing tables either?

I’ll have to check that more carefully.

[ozfiddler]

@james

no columns or column definitions were changed in existing tables either?

I’ve just done a side-by-side comparison of each table. This is the only difference I can see.

Old version:

--
-- Table structure for table `wp_wc_product_meta_lookup`
--

DROP TABLE IF EXISTS `wp_wc_product_meta_lookup`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `wp_wc_product_meta_lookup` (
  `product_id` bigint(20) NOT NULL,
  `sku` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT '',
  `virtual` tinyint(1) DEFAULT 0,
  `downloadable` tinyint(1) DEFAULT 0,
  `min_price` decimal(10,2) DEFAULT NULL,
  `max_price` decimal(10,2) DEFAULT NULL,
  `onsale` tinyint(1) DEFAULT 0,
  `stock_quantity` double DEFAULT NULL,
  `stock_status` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT 'instock',
  `rating_count` bigint(20) DEFAULT 0,
  `average_rating` decimal(3,2) DEFAULT 0.00,
  `total_sales` bigint(20) DEFAULT 0,
  PRIMARY KEY (`product_id`),
  KEY `virtual` (`virtual`),
  KEY `downloadable` (`downloadable`),
  KEY `stock_status` (`stock_status`),
  KEY `stock_quantity` (`stock_quantity`),
  KEY `onsale` (`onsale`),
  KEY `min_max_price` (`min_price`,`max_price`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

New version:

--
-- Table structure for table `wp_wc_product_meta_lookup`
--

DROP TABLE IF EXISTS `wp_wc_product_meta_lookup`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `wp_wc_product_meta_lookup` (
  `product_id` bigint(20) NOT NULL,
  `sku` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT '',
  `virtual` tinyint(1) DEFAULT 0,
  `downloadable` tinyint(1) DEFAULT 0,
  `min_price` decimal(19,4) DEFAULT NULL,
  `max_price` decimal(19,4) DEFAULT NULL,
  `onsale` tinyint(1) DEFAULT 0,
  `stock_quantity` double DEFAULT NULL,
  `stock_status` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT 'instock',
  `rating_count` bigint(20) DEFAULT 0,
  `average_rating` decimal(3,2) DEFAULT 0.00,
  `total_sales` bigint(20) DEFAULT 0,
  `tax_status` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT 'taxable',
  `tax_class` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT '',
  PRIMARY KEY (`product_id`),
  KEY `virtual` (`virtual`),
  KEY `downloadable` (`downloadable`),
  KEY `stock_status` (`stock_status`),
  KEY `stock_quantity` (`stock_quantity`),
  KEY `onsale` (`onsale`),
  KEY `min_max_price` (`min_price`,`max_price`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

So they have added tax_status and tax_class.

[1stepforward]

Might be worth trying the WP Rollback plugin.

[ozfiddler]

I just tested this. It will roll back the plugin files to a previous version but doesn’t change the DB. So no different to installing CC.

[1stepforward]

@ozfiddler

doesn’t change the DB

That’s always going to be the tricky bit. If changes to the database are because WC has introduced a new feature or new settings, then these are going to be lost when switching to CC. I can’t see a way around that.

But I might know a man that can.

[ozfiddler]

Interesting… I found this on the MainWP site.

There is a filter included in the WooCommerce core plugin which will allow the WooCommerce Admin to be disabled on a site.

add_filter( ‘woocommerce_admin_disabled’, ‘__return_true’ );

If you have the code snippet in place on those child sites when you update WooCommerce to version 4.0 then the WooCommerce Admin database tables will not be created.

wp_wc_admin_notes
wp_wc_admin_note_actions
wp_wc_category_lookup
wp_wc_customer_lookup
wp_wc_order_coupon_lookup
wp_wc_order_product_lookup
wp_wc_order_stats
wp_wc_order_tax_lookup

[ozfiddler]

WooCommerce 4.0 Changelog

• Enhancement - Included information about packages in the System Status Report. #25584
• Enhancement - New WooCommerce Admin. #25011
• Enhancement - Update dependency woocommerce/woocommerce-blocks to v2.5.12 #25587
• Enhancement - Updated Action Scheduler to 3.0.1. #25566
• Dev – Added support for placeholder attribute in quantity inputs. #25418
• Dev – Added tax_status and tax_class columns to the product meta data lookup table. #25428
• Dev – Introduced woocommerce_top_rated_widget_args filter. #25320
• Dev – Introduced woocommerce_admin_process_variation_object hook. #24929
• Dev – Added actions before and after grouped product list to allow adding custom rows. #25093
• Dev – Added filter to tweak whether a product has enough stock while attempting to pay for an order. #25230
• Dev – Added the automattic/jetpack-constants package and replace PHP constant definition checks with it. #25516
• Dev – Added a triggerHandler called checkout_place_order_success on a successful order during the checkout process. #25289
• Dev – Allow filtering of default meta value inside WC_Data::get_meta even if meta key not found. #24066
• Dev – Includes Emogrifier composer package instead of including into includes/libraries . #25525
• Dev – Introduce WC_Countries::get_vat_countries for returning a list of countries that uses VAT and refactor WC_Countries::get_european_union_countries with backward compatibility and deprecation to remove the VAT functionality from there. Brexit, remove GB from WC_Countries::get_european_union_countries . #24943
• Dev – Introduced woocommerce_download_product_filepath filter. #25485
• Dev – Introduced woocommerce_email_content_type filter. #25405
• Dev – Updated woocommerce_email_from_name and woocommerce_email_from_address filter arguments to include wp_email() default data. #25405
• Dev – Introduced woocommerce_shortcode_products_query_results filter. #25573
• Dev – Removed hash_equals() polyfill as it was no longer needed. #25474
• Dev – Removed unused .order-actions CSS. #25581

[1stepforward]

@ozfiddler

There is a filter included in the WooCommerce core plugin which will allow the WooCommerce Admin to be disabled on a site.

Yeah, that filter disables the new analytics and marketing features. It seems crazy you can’t disable this from WC settings. Plus, because the filter is called before the theme is loaded, you have to create a separate plugin with just that bit of code in. Adding it to functions.php won’t work. Apparently…

[ozfiddler]

@1stepforward

you have to create a separate plugin with just that bit of code in. Adding it to functions.php won’t work. Apparently…

Yes, this is the case. No good in functions.php. No good in a snippet plugin. Has to be in its own separate plugin.

[ozfiddler]

@1stepforward

It seems crazy you can’t disable this from WC settings.

I’m sure it was calculated. That code also hides the new top level “Marketing” menu item which looks like a place purely for pushing Woo commercial products.They don’t want to make it too easy for you to hide that little gold mine.

[anon71687268]

I’m not using a commerce plugin at this time, but, just wanted to say that I greatly appreciate all the time, effort, research, discussion (and so much more) that you’ve all put into the project. Really great work!

[ozfiddler]

Just to summarise what I have found so far… there are four main areas of change to the database in WooCommerce 4.x

1. New tables created for the action scheduler.
   actionscheduler_actions
actionscheduler_claims
actionscheduler_groups
actionscheduler_logs
   Note that plugins (eg WooCommerce Subscriptions) may start to make use of these tables for their procedures.

2. New tables created for the WooCommerce Admin reporting features
   wc_admin_note_actions
wc_admin_notes
wc_category_lookup
wc_customer_lookup
wc_order_coupon_lookup
wc_order_product_lookup
wp_wc_order_stats
wc_order_tax_lookup

3. A new table created for handling reserved stock (managing stock levels for items that may be currently in the checkout process) - wc_reserved_stock
   More details here: Reserved Stock in WooCommerce 4.3 Explained | Puri.io

4. A change to an existing table - wc_product_meta_lookup
   This has 2 new columns added - tax_status and tax_class
   See: Add product lookup table tax columns by mikejolley · Pull Request #25428 · woocommerce/woocommerce · GitHub
   It also has a change to two existing column definitions
   min_price decimal(10,2) DEFAULT NULL and max_price decimal(10,2) DEFAULT NULL
   becomes
   min_price decimal(19,4) DEFAULT NULL and max_price decimal(19,4) DEFAULT NULL
   See: Need to change `wc_product_meta_lookup` table structure datatype size for min_price and max_price · Issue #24428 · woocommerce/woocommerce · GitHub

[ozfiddler]

@Web242 - Are you going to be migrating from WooCommerce to CC, or starting from scratch. If you are migrating, what version of WC are you currently using?

[Web242]

Hi @ozfiddler I would defintely prefer to migrate, in order to retain customer orders and information. But it’s not the end of the world if I redo.

WC Version 4.4.1

My plan is to simplify everything, so we’ll get rid of the subscriptions etc. and just keep simple orders and existing products.

[ozfiddler]

@Web242

I would defintely prefer to migrate

OK, then you’ll be wanting to follow this discussion: Migrating from WooCommerce to Classic Commerce

and also the CC channel on Slack. I’m trying to get my head around it all at the moment.

@Web242

My plan is to simplify everything

Simplify is good!

[Web242]

@ozfiddler Yeah I hear you! WordCommerce is complicated enough. I suspect most of the new stuff, is for their marketing addons and Gutenberg blocks. I suspect there are not huge changes to the db tables/schema - but there have been db updates on WC upgrades.

I’ll go check out your post!

[Web242]

@ozfiddler What is the Slack channel?