#woocommerce

cssllc-purge-woocommerce-data.php

<?php
/**
 * Description: MU-Plugin of CLI command to purge WooCommerce data.
 * Plugin URI: https://gist.github.com/crstauf/0917df51455efa1a61058c050565fe72
 * Author: Caleb Stauffer
 * Author URI: https://develop.calebstauffer.com
 */

if ( !defined( 'WP_CLI' ) || !WP_CLI )
	return;

/**
 * Purge WooCommerce data.
 */
class CSSLLC_Purge_WooCommerce_Data {
	
	const POST_TYPES = array(
		'shop_order',
		'shop_order_refund',
		'shop_subscription',
		'shop_coupon',
		'scheduled-action',
		'payment_retry',
		'sub_transaction',
		'subscription',
		'paypal_ipn',
	);
	
	const STEPS = 7;
	const DEFAULT_LIMIT = 10000000;
	
	protected $sql;
	protected $args;
	protected $assoc_args;
	
	/**
	 * Construct.
	 *
	 * @param wpdb $sql
	 */
	function __construct( $sql ) {
		$this->sql = $sql;
	}
	
	/**
	 * Purge WooCommerce order and customer data.
	 *
	 * ## OPTIONS
	 *
	 * [--limit=<value>]
	 * : Query limit.
	 * ---
	 * default: 10000000
	 * ---
	 *
	 * [--yes]
	 * : Answer yes to the confirmation message.
	 *
	 * @param array $args
	 * @param array $assoc_args
	 * @uses wp_get_environment_type()
	 * @uses $this->execute()
	 */
	function __invoke( array $args, array $assoc_args = array() ) {
		if ( 'production' === wp_get_environment_type() ) {
			WP_CLI::warning( 'Not permitted to run on production environment.' );
			return;
		}
		
		WP_CLI::confirm( 'Are you sure you want to purge WooCommerce data?', $assoc_args );
		
		$this->args = $args;
		$this->assoc_args = wp_parse_args( $assoc_args, array(
			'limit' => self::DEFAULT_LIMIT,
		) );
		
		$this->execute();
		
		do_action( 'cssllc_after_purge_woocommerce_data', $this->args, $this->assoc_args );
	}
	
	/**
	 * Execute!
	 *
	 * @uses $this->truncate_tables()
	 * @uses $this->delete_meta_data()
	 * @uses $this->delete_order_notes_meta()
	 * @uses $this->delete_order_notes()
	 * @uses $this->delete_objects()
	 * @uses $this->delete_customers()
	 * @uses $this->delete_customers_meta()
	 * @return void
	 */
	protected function execute() : void {
		$this->truncate_tables();
		$this->delete_meta_data();
		$this->delete_order_notes_meta();
		$this->delete_order_notes();
		$this->delete_objects();
		$this->delete_customers();
		$this->delete_customers_meta();
		
		WP_CLI::success( 'Purged WooCommerce data' );
	}

	/*
	 ######  ######## ######## ########   ######
	##    ##    ##    ##       ##     ## ##    ##
	##          ##    ##       ##     ## ##
	 ######     ##    ######   ########   ######
	      ##    ##    ##       ##              ##
	##    ##    ##    ##       ##        ##    ##
	 ######     ##    ######## ##         ######
	*/

	/**
	 * 1. Truncate tables.
	 *
	 * @return void
	 */
	protected function truncate_tables() : void {
		WP_CLI::log( 'Truncating tables' );
		
		$unprefixed_table_names = array(
			'actionscheduler_actions',
			'actionscheduler_claims',
			'actionscheduler_groups',
			'actionscheduler_logs',
			'amazon_orders',
			'automatewoo_abandoned_carts',
			'automatewoo_customers',
			'automatewoo_customer_meta',
			'automatewoo_guests',
			'automatewoo_guest_meta',
			'automatewoo_logs',
			'automatewoo_log_meta',
			'automatewoo_queue',
			'automatewoo_queue_meta',
			'wc_order_stats',
			'wc_webhooks',
			'woocommerce_order_itemmeta',
			'woocommerce_order_items',
			'woocommerce_payment_tokenmeta',
			'woocommerce_payment_tokens',
			'woocommerce_sessions',
		);
		
		foreach ( $unprefixed_table_names as $unprefixed_table_name ) {
			$table_name = $this->sql->prefix . $unprefixed_table_name;
			
			$results = $this->sql->query( "TRUNCATE TABLE `$table_name`" );
			
			if ( false === $results ) {
				WP_CLI::warning( sprintf( 'Unable to truncate `%s` table', $table_name ) );
				WP_CLI::debug( $this->sql->last_error );

				continue;
			}
			
			WP_CLI::debug( sprintf( 'Truncated table `%s`', $table_name ) );
		}
		
		WP_CLI::debug( 'Truncated tables' );
	}
	
	/**
	 * 2. Delete order and coupon meta data.
	 *
	 * @return void
	 */
	protected function delete_meta_data() : void {
		WP_CLI::log( 'Deleting meta data' );
		
		$placeholders = array_fill( 0, count( self::POST_TYPES ), '%s' );
		$placeholders_string = implode( ', ', $placeholders );
		
		$sub_query = $this->sql->prepare(
			"SELECT `ID` FROM `{$this->sql->posts}` WHERE `post_type` IN ( $placeholders_string )",
			self::POST_TYPES
		);

		$query = $this->sql->prepare( 
			"DELETE FROM `{$this->sql->postmeta}` WHERE `post_id` IN ( $sub_query ) LIMIT %d", 
			$this->assoc_args['limit'] 
		);
		
		$results = $this->sql->query( $query );
		
		if ( false === $results ) {
			WP_CLI::warning( 'Unable to purge meta data' );
			WP_CLI::debug( $this->sql->last_error );
		} else
			WP_CLI::debug( 'Purged meta data' );
	}
	
	/**
	 * 3. Delete order notes meta.
	 *
	 * @return void
	 */
	protected function delete_order_notes_meta() : void {
		WP_CLI::log( 'Deleting order notes meta' );
		
		$placeholders = array_fill( 0, count( self::POST_TYPES ), '%s' );
		$placeholders_string = implode( ', ', $placeholders );
		
		$sub_sub_query = $this->sql->prepare(
			"SELECT `ID` FROM `{$this->sql->posts}` WHERE `post_type` IN ( \n\t\t\t$placeholders_string \n\t\t)",
			self::POST_TYPES
		);
		
		$sub_query = "SELECT `comment_ID` FROM `{$this->sql->comments}` WHERE `comment_post_ID` IN ( \n\t\t$sub_sub_query \n\t)";
		
		$query = $this->sql->prepare( 
			"DELETE FROM `{$this->sql->commentmeta}` WHERE `comment_id` IN ( \n\t$sub_query \n) LIMIT %d",
			$this->assoc_args['limit']
		);
		
		$results = $this->sql->query( $query );
		
		if ( false === $results ) {
			WP_CLI::warning( 'Unable to delete order notes meta' );
			WP_CLI::debug( $this->sql->last_error );
		} else
			WP_CLI::debug( 'Deleted order notes meta' );
	}
	
	/**
	 * 4. Delete order notes
	 *
	 * @return void
	 */
	protected function delete_order_notes() : void {
		WP_CLI::log( 'Deleting order notes' );
		
		$placeholders = array_fill( 0, count( self::POST_TYPES ), '%s' );
		$placeholders_string = implode( ', ', $placeholders );
		
		$sub_query = $this->sql->prepare(
			"SELECT `ID` FROM `{$this->sql->posts}` WHERE `post_type` IN ( \n\t\t$placeholders_string \n\t)",
			self::POST_TYPES
		);
		
		$query = $this->sql->prepare( 
			"DELETE FROM `{$this->sql->comments}` WHERE `comment_post_ID` IN ( \n\t$sub_query \n) LIMIT %d",
			$this->assoc_args['limit']
		);
		
		$results = $this->sql->query( $query );
		
		if ( false === $results ) {
			WP_CLI::warning( 'Unable to delete order notes' );
			WP_CLI::debug( $this->sql->last_error );
		} else
			WP_CLI::debug( 'Deleted order notes' );
	}
	
	/**
	 * 5. Delete the order and comment objects
	 *
	 * @return void
	 */
	protected function delete_objects() : void {
		WP_CLI::log( 'Deleting objects' );
		
		$values = self::POST_TYPES;
		$placeholders = array_fill( 0, count( $values ), '%s' );
		$placeholders_string = implode( ', ', $placeholders );
		
		$values[] = $this->assoc_args['limit'];
		
		$query = $this->sql->prepare(
			"DELETE FROM `{$this->sql->posts}` WHERE `post_type` IN ( $placeholders_string ) LIMIT %d",
			$values
		);
		
		$results = $this->sql->query( $query );
		
		if ( false === $results ) {
			WP_CLI::warning( 'Unable to delete objects' );
			WP_CLI::debug( $this->sql->last_error );
		} else
			WP_CLI::debug( 'Deleted objects' );
	}
	
	/**
	 * 6. Delete user accounts for customers
	 *
	 * @return void
	 */
	protected function delete_customers() : void {
		WP_CLI::log( 'Deleting customers' );
		
		$sub_query = $this->sql->prepare(
			"SELECT `user_id` FROM `{$this->sql->usermeta}` WHERE `meta_key` = %s AND `meta_value` IN ( %s, %s )",
			'wp_capabilities',
			serialize( array( 'customer' => true ) ),
			serialize( array( 'subscribed_customer' => true ) )
		);
		
		$query = $this->sql->prepare(
			"DELETE FROM `{$this->sql->users}` WHERE `ID` IN ( $sub_query ) LIMIT %d",
			$this->assoc_args['limit']
		);
		
		$results = $this->sql->query( $query );
		
		if ( false === $results ) {
			WP_CLI::warning( 'Unable to delete customers' );
			WP_CLI::debug( $this->sql->last_error );
		} else
			WP_CLI::debug( 'Deleted customers' );
	}
	
	/**
	 * 7. Delete user meta for customers
	 *
	 * @return void
	 */
	protected function delete_customers_meta() : void {
		WP_CLI::log( 'Deleting customers meta' );
		
		$table_name = '_wc_cleanup_customer_ids';
		
		$drop_temp_query = "DROP TEMPORARY TABLE IF EXISTS `$table_name`";
		
		$create_query = $this->sql->prepare(
			"CREATE TEMPORARY TABLE `$table_name`\n" . 
			"SELECT `user_id` FROM `{$this->sql->usermeta}` WHERE `meta_key` = %s AND `meta_value` IN ( %s, %s ) LIMIT %d",
			'wp_capabilities',
			serialize( array( 'customer' => true ) ),
			serialize( array( 'subscribed_customer' => true ) ),
			( $this->assoc_args['limit'] / 10 )
		);
		
		$delete_query = "DELETE a.* FROM `{$this->sql->usermeta}` a WHERE `user_id` IN ( SELECT `user_id` FROM `$table_name` )";
		
		foreach ( array(
			$drop_temp_query,
			$create_query,
			$delete_query,
		) as $query ) {
		
			$results = $this->sql->query( $query );
			
			if ( false === $results ) {
				WP_CLI::warning( 'Unable to delete customers meta' );
				WP_CLI::debug( $this->sql->last_error );
			} else
				WP_CLI::debug( 'Deleted customers meta' );
				
		}
	}
	
}

global $wpdb;
WP_CLI::add_command( 'purge-woocommerce-data', new CSSLLC_Purge_WooCommerce_Data( $wpdb ) );

purge-woocommerce-data.sql

/**
 * USE AT YOUR OWN RISK!
 *
 * In no way do I guarantee the accuracy or comprehensiveness of these queries to do exactly what you want on your own server.
 * I have written them down for my own personal record, and you are free to use at your own discretion.
 * Recommended corrections and improvements are welcome in the comments.
 */

/**
 * 1. Truncate tables.
 */
TRUNCATE TABLE `wp_actionscheduler_actions`;
TRUNCATE TABLE `wp_actionscheduler_claims`;
TRUNCATE TABLE `wp_actionscheduler_groups`;
TRUNCATE TABLE `wp_actionscheduler_logs`;
TRUNCATE TABLE `wp_wc_order_stats`;
TRUNCATE TABLE `wp_wc_webhooks`;
TRUNCATE TABLE `wp_woocommerce_order_itemmeta`;
TRUNCATE TABLE `wp_woocommerce_order_items`;
TRUNCATE TABLE `wp_woocommerce_payment_tokenmeta`;
TRUNCATE TABLE `wp_woocommerce_payment_tokens`;
TRUNCATE TABLE `wp_woocommerce_sessions`;

/** 
 * 2. Delete order and coupon meta data.
 *
 * Note the LIMIT is for performance reasons; 
 * you may be able to increase, or you may need 
 * to decrease, with consideration to server specs.
 */
DELETE FROM `wp_postmeta` WHERE `post_id` IN (
	SELECT `ID` FROM `wp_posts` WHERE `post_type` IN ( 
		'shop_order',
		'shop_order_refund',
		'shop_subscription',
		'shop_coupon',
		'scheduled-action',
		'payment_retry',
		'sub_transaction',
		'subscription',
		'paypal_ipn'
	)
) LIMIT 100000;

/**
 * 3. Delete order notes meta.
 *
 * Note the LIMIT is for performance reasons; 
 * you may be able to increase, or you may need 
 * to decrease, with consideration to server specs.
 */
DELETE FROM `wp_commentmeta` WHERE `comment_id` IN (
	SELECT `comment_ID` FROM `wp_comments` WHERE `comment_post_ID` IN (
		SELECT `ID` FROM `wp_posts` WHERE `post_type` IN (
			'shop_order',
			'shop_order_refund',
			'shop_subscription',
			'scheduled-action',
			'payment_retry',
			'sub_transaction',
			'subscription',
			'paypal_ipn'
		)
	)
) LIMIT 100000;

/**
 * 4. Delete order notes.
 *
 * Note the LIMIT is for performance reasons; 
 * you may be able to increase, or you may need 
 * to decrease, with consideration to server specs.
 */
DELETE FROM `wp_comments` WHERE `comment_post_ID` IN (
	SELECT `ID` FROM `wp_posts` WHERE `post_type` IN (
		'shop_order',
		'shop_order_refund',
		'shop_subscription',
		'scheduled-action',
		'payment_retry',
		'subscription',
		'sub_transaction',
		'paypal_ipn'
	)
) LIMIT 100000;

/**
 * 5. Delete the order and comment objects.
 *
 * Note the LIMIT is for performance reasons; 
 * you may be able to increase, or you may need 
 * to decrease, with consideration to server specs.
 */
DELETE FROM `wp_posts` WHERE `post_type` IN ( 
	'shop_order', 
	'shop_order_refund', 
	'shop_subscription', 
	'shop_coupon',
	'scheduled-action',
	'payment_retry',
	'subscription',
	'sub_transaction',
	'paypal_ipn'
) LIMIT 100000;

/**
 * 6. Delete user accounts for customers.
 */
DELETE FROM `wp_users` WHERE `ID` IN (
	SELECT `user_id` FROM `wp_usermeta` WHERE 
		`meta_key` = 'wp_capabilities' 
		AND `meta_value` IN ( 'a:1:{s:8:"customer";b:1;}', 'a:1:{s:19:"subscribed_customer";b:1;}' )
) LIMIT 100000;

/**
 * 7.1. Delete temporary table of customer user IDs.
 */
DROP TEMPORARY TABLE IF EXISTS `_wc_cleanup__customer_ids`;

/**
 * 7.2. Create temporary table of next 25,000 user IDs with only `customer` or `subscribed_customer` role.
 */
CREATE TEMPORARY TABLE `_wc_cleanup__customer_ids`
SELECT `user_id` FROM `wp_usermeta` WHERE 
	`meta_key` = 'wp_capabilities' 
	AND `meta_value` IN ( 'a:1:{s:8:"customer";b:1;}', 'a:1:{s:19:"subscribed_customer";b:1;}' ) 
LIMIT 25000;

/**
 * 7.3. Delete user meta of customer user IDs in temporary table.
 */
DELETE a.* FROM `wp_usermeta` a WHERE `user_id` IN (
	SELECT `user_id` FROM `_wc_cleanup__customer_ids`
);

/** Repeat steps 7.1 - 7.3 until completed. */

Thank you for this, saved me a lot of trouble!

@andrewladlow Welcome! 🙇‍♂️