WC_Install::get_schema()private staticWC 1.0

Get Table schema.

See https://github.com/woocommerce/woocommerce/wiki/Database-Description/

A note on indexes; Indexes have a maximum size of 767 bytes. Historically, we haven't need to be concerned about that. As of WordPress 4.2, however, we moved to utf8mb4, which uses 4 bytes per character. This means that an index which used to have room for floor(767/3) = 255 characters, now only has room for floor(767/4) = 191 characters.

Changing indexes may cause duplicate index notices in logs due to https://core.trac.wordpress.org/ticket/34870 but dropping indexes first causes too much load on some servers/larger DB.

When adding or removing a table, make sure to update the list of tables in WC_Install::get_tables().

Method of the class: WC_Install{}

No Hooks.

Return

String.

Usage

$result = WC_Install::get_schema();

WC_Install::get_schema() code WC 9.4.2

private static function get_schema() {
	global $wpdb;

	$collate = '';

	if ( $wpdb->has_cap( 'collation' ) ) {
		$collate = $wpdb->get_charset_collate();
	}

	$max_index_length = wc_get_container()->get( DatabaseUtil::class )->get_max_index_length();

	$product_attributes_lookup_table_creation_sql = wc_get_container()->get( DataRegenerator::class )->get_table_creation_sql();

	$feature_controller = wc_get_container()->get( FeaturesController::class );
	$hpos_enabled       =
		$feature_controller->feature_is_enabled( DataSynchronizer::ORDERS_DATA_SYNC_ENABLED_OPTION ) || $feature_controller->feature_is_enabled( CustomOrdersTableController::CUSTOM_ORDERS_TABLE_USAGE_ENABLED_OPTION ) ||
		self::should_enable_hpos_for_new_shop();
	$hpos_table_schema  = $hpos_enabled ? wc_get_container()->get( OrdersTableDataStore::class )->get_database_schema() : '';

	$tables = "
CREATE TABLE {$wpdb->prefix}woocommerce_sessions (
  session_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  session_key char(32) NOT NULL,
  session_value longtext NOT NULL,
  session_expiry bigint(20) unsigned NOT NULL,
  PRIMARY KEY  (session_id),
  UNIQUE KEY session_key (session_key)
) $collate;
CREATE TABLE {$wpdb->prefix}woocommerce_api_keys (
  key_id bigint(20) unsigned NOT NULL auto_increment,
  user_id bigint(20) unsigned NOT NULL,
  description varchar(200) NULL,
  permissions varchar(10) NOT NULL,
  consumer_key char(64) NOT NULL,
  consumer_secret char(43) NOT NULL,
  nonces longtext NULL,
  truncated_key char(7) NOT NULL,
  last_access datetime NULL default null,
  PRIMARY KEY  (key_id),
  KEY consumer_key (consumer_key),
  KEY consumer_secret (consumer_secret)
) $collate;
CREATE TABLE {$wpdb->prefix}woocommerce_attribute_taxonomies (
  attribute_id bigint(20) unsigned NOT NULL auto_increment,
  attribute_name varchar(200) NOT NULL,
  attribute_label varchar(200) NULL,
  attribute_type varchar(20) NOT NULL,
  attribute_orderby varchar(20) NOT NULL,
  attribute_public int(1) NOT NULL DEFAULT 1,
  PRIMARY KEY  (attribute_id),
  KEY attribute_name (attribute_name(20))
) $collate;
CREATE TABLE {$wpdb->prefix}woocommerce_downloadable_product_permissions (
  permission_id bigint(20) unsigned NOT NULL auto_increment,
  download_id varchar(36) NOT NULL,
  product_id bigint(20) unsigned NOT NULL,
  order_id bigint(20) unsigned NOT NULL DEFAULT 0,
  order_key varchar(200) NOT NULL,
  user_email varchar(200) NOT NULL,
  user_id bigint(20) unsigned NULL,
  downloads_remaining varchar(9) NULL,
  access_granted datetime NOT NULL default '0000-00-00 00:00:00',
  access_expires datetime NULL default null,
  download_count bigint(20) unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY  (permission_id),
  KEY download_order_key_product (product_id,order_id,order_key(16),download_id),
  KEY download_order_product (download_id,order_id,product_id),
  KEY order_id (order_id),
  KEY user_order_remaining_expires (user_id,order_id,downloads_remaining,access_expires)
) $collate;
CREATE TABLE {$wpdb->prefix}woocommerce_order_items (
  order_item_id bigint(20) unsigned NOT NULL auto_increment,
  order_item_name text NOT NULL,
  order_item_type varchar(200) NOT NULL DEFAULT '',
  order_id bigint(20) unsigned NOT NULL,
  PRIMARY KEY  (order_item_id),
  KEY order_id (order_id)
) $collate;
CREATE TABLE {$wpdb->prefix}woocommerce_order_itemmeta (
  meta_id bigint(20) unsigned NOT NULL auto_increment,
  order_item_id bigint(20) unsigned NOT NULL,
  meta_key varchar(255) default NULL,
  meta_value longtext NULL,
  PRIMARY KEY  (meta_id),
  KEY order_item_id (order_item_id),
  KEY meta_key (meta_key(32))
) $collate;
CREATE TABLE {$wpdb->prefix}woocommerce_tax_rates (
  tax_rate_id bigint(20) unsigned NOT NULL auto_increment,
  tax_rate_country varchar(2) NOT NULL DEFAULT '',
  tax_rate_state varchar(200) NOT NULL DEFAULT '',
  tax_rate varchar(8) NOT NULL DEFAULT '',
  tax_rate_name varchar(200) NOT NULL DEFAULT '',
  tax_rate_priority bigint(20) unsigned NOT NULL,
  tax_rate_compound int(1) NOT NULL DEFAULT 0,
  tax_rate_shipping int(1) NOT NULL DEFAULT 1,
  tax_rate_order bigint(20) unsigned NOT NULL,
  tax_rate_class varchar(200) NOT NULL DEFAULT '',
  PRIMARY KEY  (tax_rate_id),
  KEY tax_rate_country (tax_rate_country),
  KEY tax_rate_state (tax_rate_state(2)),
  KEY tax_rate_class (tax_rate_class(10)),
  KEY tax_rate_priority (tax_rate_priority)
) $collate;
CREATE TABLE {$wpdb->prefix}woocommerce_tax_rate_locations (
  location_id bigint(20) unsigned NOT NULL auto_increment,
  location_code varchar(200) NOT NULL,
  tax_rate_id bigint(20) unsigned NOT NULL,
  location_type varchar(40) NOT NULL,
  PRIMARY KEY  (location_id),
  KEY tax_rate_id (tax_rate_id),
  KEY location_type_code (location_type(10),location_code(20))
) $collate;
CREATE TABLE {$wpdb->prefix}woocommerce_shipping_zones (
  zone_id bigint(20) unsigned NOT NULL auto_increment,
  zone_name varchar(200) NOT NULL,
  zone_order bigint(20) unsigned NOT NULL,
  PRIMARY KEY  (zone_id)
) $collate;
CREATE TABLE {$wpdb->prefix}woocommerce_shipping_zone_locations (
  location_id bigint(20) unsigned NOT NULL auto_increment,
  zone_id bigint(20) unsigned NOT NULL,
  location_code varchar(200) NOT NULL,
  location_type varchar(40) NOT NULL,
  PRIMARY KEY  (location_id),
  KEY zone_id (zone_id),
  KEY location_type_code (location_type(10),location_code(20))
) $collate;
CREATE TABLE {$wpdb->prefix}woocommerce_shipping_zone_methods (
  zone_id bigint(20) unsigned NOT NULL,
  instance_id bigint(20) unsigned NOT NULL auto_increment,
  method_id varchar(200) NOT NULL,
  method_order bigint(20) unsigned NOT NULL,
  is_enabled tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY  (instance_id)
) $collate;
CREATE TABLE {$wpdb->prefix}woocommerce_payment_tokens (
  token_id bigint(20) unsigned NOT NULL auto_increment,
  gateway_id varchar(200) NOT NULL,
  token text NOT NULL,
  user_id bigint(20) unsigned NOT NULL DEFAULT '0',
  type varchar(200) NOT NULL,
  is_default tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY  (token_id),
  KEY user_id (user_id)
) $collate;
CREATE TABLE {$wpdb->prefix}woocommerce_payment_tokenmeta (
  meta_id bigint(20) unsigned NOT NULL auto_increment,
  payment_token_id bigint(20) unsigned NOT NULL,
  meta_key varchar(255) NULL,
  meta_value longtext NULL,
  PRIMARY KEY  (meta_id),
  KEY payment_token_id (payment_token_id),
  KEY meta_key (meta_key(32))
) $collate;
CREATE TABLE {$wpdb->prefix}woocommerce_log (
  log_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  timestamp datetime NOT NULL,
  level smallint(4) NOT NULL,
  source varchar(200) NOT NULL,
  message longtext NOT NULL,
  context longtext NULL,
  PRIMARY KEY (log_id),
  KEY level (level)
) $collate;
CREATE TABLE {$wpdb->prefix}wc_webhooks (
  webhook_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  status varchar(200) NOT NULL,
  name text NOT NULL,
  user_id bigint(20) unsigned NOT NULL,
  delivery_url text NOT NULL,
  secret text NOT NULL,
  topic varchar(200) NOT NULL,
  date_created datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  date_created_gmt datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  date_modified datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  date_modified_gmt datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  api_version smallint(4) NOT NULL,
  failure_count smallint(10) NOT NULL DEFAULT '0',
  pending_delivery tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY  (webhook_id),
  KEY user_id (user_id)
) $collate;
CREATE TABLE {$wpdb->prefix}wc_download_log (
  download_log_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  timestamp datetime NOT NULL,
  permission_id bigint(20) unsigned NOT NULL,
  user_id bigint(20) unsigned NULL,
  user_ip_address varchar(100) NULL DEFAULT '',
  PRIMARY KEY  (download_log_id),
  KEY permission_id (permission_id),
  KEY timestamp (timestamp)
) $collate;
CREATE TABLE {$wpdb->prefix}wc_product_meta_lookup (
  `product_id` bigint(20) NOT NULL,
  `sku` varchar(100) NULL default '',
  `global_unique_id` varchar(100) NULL default '',
  `virtual` tinyint(1) NULL default 0,
  `downloadable` tinyint(1) NULL default 0,
  `min_price` decimal(19,4) NULL default NULL,
  `max_price` decimal(19,4) NULL default NULL,
  `onsale` tinyint(1) NULL default 0,
  `stock_quantity` double NULL default NULL,
  `stock_status` varchar(100) NULL default 'instock',
  `rating_count` bigint(20) NULL default 0,
  `average_rating` decimal(3,2) NULL default 0.00,
  `total_sales` bigint(20) NULL default 0,
  `tax_status` varchar(100) NULL default 'taxable',
  `tax_class` varchar(100) NULL 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`),
  KEY sku (sku(50))
) $collate;
CREATE TABLE {$wpdb->prefix}wc_tax_rate_classes (
  tax_rate_class_id bigint(20) unsigned NOT NULL auto_increment,
  name varchar(200) NOT NULL DEFAULT '',
  slug varchar(200) NOT NULL DEFAULT '',
  PRIMARY KEY  (tax_rate_class_id),
  UNIQUE KEY slug (slug($max_index_length))
) $collate;
CREATE TABLE {$wpdb->prefix}wc_reserved_stock (
`order_id` bigint(20) NOT NULL,
`product_id` bigint(20) NOT NULL,
`stock_quantity` double NOT NULL DEFAULT 0,
`timestamp` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`expires` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY  (`order_id`, `product_id`)
) $collate;
CREATE TABLE {$wpdb->prefix}wc_rate_limits (
  rate_limit_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  rate_limit_key varchar(200) NOT NULL,
  rate_limit_expiry bigint(20) unsigned NOT NULL,
  rate_limit_remaining smallint(10) NOT NULL DEFAULT '0',
  PRIMARY KEY  (rate_limit_id),
  UNIQUE KEY rate_limit_key (rate_limit_key($max_index_length))
) $collate;
$product_attributes_lookup_table_creation_sql
CREATE TABLE {$wpdb->prefix}wc_product_download_directories (
url_id bigint(20) unsigned NOT NULL auto_increment,
url varchar(256) NOT NULL,
enabled tinyint(1) NOT NULL DEFAULT 0,
PRIMARY KEY (url_id),
KEY url (url($max_index_length))
) $collate;
CREATE TABLE {$wpdb->prefix}wc_order_stats (
order_id bigint(20) unsigned NOT NULL,
parent_id bigint(20) unsigned DEFAULT 0 NOT NULL,
date_created datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
date_created_gmt datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
date_paid datetime DEFAULT '0000-00-00 00:00:00',
date_completed datetime DEFAULT '0000-00-00 00:00:00',
num_items_sold int(11) DEFAULT 0 NOT NULL,
total_sales double DEFAULT 0 NOT NULL,
tax_total double DEFAULT 0 NOT NULL,
shipping_total double DEFAULT 0 NOT NULL,
net_total double DEFAULT 0 NOT NULL,
returning_customer tinyint(1) DEFAULT NULL,
status varchar(200) NOT NULL,
customer_id bigint(20) unsigned NOT NULL,
PRIMARY KEY (order_id),
KEY date_created (date_created),
KEY customer_id (customer_id),
KEY status (status({$max_index_length}))
) $collate;
CREATE TABLE {$wpdb->prefix}wc_order_product_lookup (
order_item_id bigint(20) unsigned NOT NULL,
order_id bigint(20) unsigned NOT NULL,
product_id bigint(20) unsigned NOT NULL,
variation_id bigint(20) unsigned NOT NULL,
customer_id bigint(20) unsigned NULL,
date_created datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
product_qty int(11) NOT NULL,
product_net_revenue double DEFAULT 0 NOT NULL,
product_gross_revenue double DEFAULT 0 NOT NULL,
coupon_amount double DEFAULT 0 NOT NULL,
tax_amount double DEFAULT 0 NOT NULL,
shipping_amount double DEFAULT 0 NOT NULL,
shipping_tax_amount double DEFAULT 0 NOT NULL,
PRIMARY KEY  (order_item_id),
KEY order_id (order_id),
KEY product_id (product_id),
KEY customer_id (customer_id),
KEY date_created (date_created)
) $collate;
CREATE TABLE {$wpdb->prefix}wc_order_tax_lookup (
order_id bigint(20) unsigned NOT NULL,
tax_rate_id bigint(20) unsigned NOT NULL,
date_created datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
shipping_tax double DEFAULT 0 NOT NULL,
order_tax double DEFAULT 0 NOT NULL,
total_tax double DEFAULT 0 NOT NULL,
PRIMARY KEY (order_id, tax_rate_id),
KEY tax_rate_id (tax_rate_id),
KEY date_created (date_created)
) $collate;
CREATE TABLE {$wpdb->prefix}wc_order_coupon_lookup (
order_id bigint(20) unsigned NOT NULL,
coupon_id bigint(20) NOT NULL,
date_created datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
discount_amount double DEFAULT 0 NOT NULL,
PRIMARY KEY (order_id, coupon_id),
KEY coupon_id (coupon_id),
KEY date_created (date_created)
) $collate;
CREATE TABLE {$wpdb->prefix}wc_admin_notes (
note_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
name varchar(255) NOT NULL,
type varchar(20) NOT NULL,
locale varchar(20) NOT NULL,
title longtext NOT NULL,
content longtext NOT NULL,
content_data longtext NULL default null,
status varchar(200) NOT NULL,
source varchar(200) NOT NULL,
date_created datetime NOT NULL default '0000-00-00 00:00:00',
date_reminder datetime NULL default null,
is_snoozable tinyint(1) DEFAULT 0 NOT NULL,
layout varchar(20) DEFAULT '' NOT NULL,
image varchar(200) NULL DEFAULT NULL,
is_deleted tinyint(1) DEFAULT 0 NOT NULL,
is_read tinyint(1) DEFAULT 0 NOT NULL,
icon varchar(200) NOT NULL default 'info',
PRIMARY KEY (note_id)
) $collate;
CREATE TABLE {$wpdb->prefix}wc_admin_note_actions (
action_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
note_id bigint(20) unsigned NOT NULL,
name varchar(255) NOT NULL,
label varchar(255) NOT NULL,
query longtext NOT NULL,
status varchar(255) NOT NULL,
actioned_text varchar(255) NOT NULL,
nonce_action varchar(255) NULL DEFAULT NULL,
nonce_name varchar(255) NULL DEFAULT NULL,
PRIMARY KEY (action_id),
KEY note_id (note_id)
) $collate;
CREATE TABLE {$wpdb->prefix}wc_customer_lookup (
customer_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
user_id bigint(20) unsigned DEFAULT NULL,
username varchar(60) DEFAULT '' NOT NULL,
first_name varchar(255) NOT NULL,
last_name varchar(255) NOT NULL,
email varchar(100) NULL default NULL,
date_last_active timestamp NULL default null,
date_registered timestamp NULL default null,
country char(2) DEFAULT '' NOT NULL,
postcode varchar(20) DEFAULT '' NOT NULL,
city varchar(100) DEFAULT '' NOT NULL,
state varchar(100) DEFAULT '' NOT NULL,
PRIMARY KEY (customer_id),
UNIQUE KEY user_id (user_id),
KEY email (email)
) $collate;
CREATE TABLE {$wpdb->prefix}wc_category_lookup (
category_tree_id bigint(20) unsigned NOT NULL,
category_id bigint(20) unsigned NOT NULL,
PRIMARY KEY (category_tree_id,category_id)
) $collate;
$hpos_table_schema;
	";

	return $tables;
}