Automattic\WooCommerce\Internal\DataStores\Orders

OrdersTableDataStore::get_database_schema()publicWC 1.0

Get the SQL needed to create all the tables needed for the custom orders table feature.

Method of the class: OrdersTableDataStore{}

No Hooks.

Return

String.

Usage

$OrdersTableDataStore = new OrdersTableDataStore();
$OrdersTableDataStore->get_database_schema();

OrdersTableDataStore::get_database_schema() code WC 8.6.1

public function get_database_schema() {
	global $wpdb;

	$collate = $wpdb->has_cap( 'collation' ) ? $wpdb->get_charset_collate() : '';

	$orders_table_name           = $this->get_orders_table_name();
	$addresses_table_name        = $this->get_addresses_table_name();
	$operational_data_table_name = $this->get_operational_data_table_name();
	$meta_table                  = $this->get_meta_table_name();

	$max_index_length                   = $this->database_util->get_max_index_length();
	$composite_meta_value_index_length  = max( $max_index_length - 8 - 100 - 1, 20 ); // 8 for order_id, 100 for meta_key, 10 minimum for meta_value.
	$composite_customer_id_email_length = max( $max_index_length - 20, 20 ); // 8 for customer_id, 20 minimum for email.

	$sql = "
CREATE TABLE $orders_table_name (
id bigint(20) unsigned,
status varchar(20) null,
currency varchar(10) null,
type varchar(20) null,
tax_amount decimal(26,8) null,
total_amount decimal(26,8) null,
customer_id bigint(20) unsigned null,
billing_email varchar(320) null,
date_created_gmt datetime null,
date_updated_gmt datetime null,
parent_order_id bigint(20) unsigned null,
payment_method varchar(100) null,
payment_method_title text null,
transaction_id varchar(100) null,
ip_address varchar(100) null,
user_agent text null,
customer_note text null,
PRIMARY KEY (id),
KEY status (status),
KEY date_created (date_created_gmt),
KEY customer_id_billing_email (customer_id, billing_email({$composite_customer_id_email_length})),
KEY billing_email (billing_email($max_index_length)),
KEY type_status_date (type, status, date_created_gmt),
KEY parent_order_id (parent_order_id),
KEY date_updated (date_updated_gmt)
) $collate;
CREATE TABLE $addresses_table_name (
id bigint(20) unsigned auto_increment primary key,
order_id bigint(20) unsigned NOT NULL,
address_type varchar(20) null,
first_name text null,
last_name text null,
company text null,
address_1 text null,
address_2 text null,
city text null,
state text null,
postcode text null,
country text null,
email varchar(320) null,
phone varchar(100) null,
KEY order_id (order_id),
UNIQUE KEY address_type_order_id (address_type, order_id),
KEY email (email($max_index_length)),
KEY phone (phone)
) $collate;
CREATE TABLE $operational_data_table_name (
id bigint(20) unsigned auto_increment primary key,
order_id bigint(20) unsigned NULL,
created_via varchar(100) NULL,
woocommerce_version varchar(20) NULL,
prices_include_tax tinyint(1) NULL,
coupon_usages_are_counted tinyint(1) NULL,
download_permission_granted tinyint(1) NULL,
cart_hash varchar(100) NULL,
new_order_email_sent tinyint(1) NULL,
order_key varchar(100) NULL,
order_stock_reduced tinyint(1) NULL,
date_paid_gmt datetime NULL,
date_completed_gmt datetime NULL,
shipping_tax_amount decimal(26,8) NULL,
shipping_total_amount decimal(26,8) NULL,
discount_tax_amount decimal(26,8) NULL,
discount_total_amount decimal(26,8) NULL,
recorded_sales tinyint(1) NULL,
UNIQUE KEY order_id (order_id),
KEY order_key (order_key)
) $collate;
CREATE TABLE $meta_table (
id bigint(20) unsigned auto_increment primary key,
order_id bigint(20) unsigned null,
meta_key varchar(255),
meta_value text null,
KEY meta_key_value (meta_key(100), meta_value($composite_meta_value_index_length)),
KEY order_id_meta_key_meta_value (order_id, meta_key(100), meta_value($composite_meta_value_index_length))
) $collate;
";

	return $sql;
}