WC_Install::get_schema │ private static │ WC 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.

Returns

string.

Usage

$result = WC_Install::get_schema();

`WC_Install::get_schema() WC Install::get schema` code ^{WC 11.2.1}

woocommerce/includes/class-wc-install.php

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() : '';

	// Stock Notifications Table Schema.
	$stock_notifications_table_schema = wc_get_container()->get( StockNotificationsDataStore::class )->get_database_schema();

	$order_stats_table_schema = self::get_order_stats_table_schema( $collate );

	$mysql_version = wc_get_server_database_version()['number'];
	if ( version_compare( $mysql_version, '5.6', '>=' ) ) {
		$datetime_default = 'DEFAULT CURRENT_TIMESTAMP';
	} else {
		$datetime_default = "DEFAULT '1970-01-01 00:00:00'";
	}

	$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),
  KEY session_expiry (session_expiry),
  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),
  KEY idx_user_email (user_email(100))
) $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),
  KEY zone_order_id (zone_order, 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),
  KEY zone_id (zone_id),
  KEY method_id (method_id(20))
) $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)),
  KEY global_unique_id (global_unique_id(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`),
KEY product_id_expires (product_id, expires)
) $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;
$order_stats_table_schema
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 $datetime_default 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, order_id),
KEY order_id (order_id),
KEY product_id (product_id),
KEY customer_id (customer_id),
KEY date_created (date_created),
KEY customer_product_date (customer_id, product_id, 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,
order_item_id bigint(20) unsigned NOT NULL DEFAULT 0,
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,
taxable_amount double DEFAULT 0 NOT NULL,
PRIMARY KEY (order_id, tax_rate_id, order_item_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,
billing_phone varchar(100) DEFAULT '' NOT NULL,
shipping_phone 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;
$stock_notifications_table_schema;
	";

	return $tables;
}

WC_Install::get_schema │ private static │ WC 1.0

Returns

Usage

WC_Install::get_schema() WC Install::get schema code WC 11.2.1

`WC_Install::get_schema() WC Install::get schema` code ^{WC 11.2.1}