WordPress Version: 5.1
/**
* Retrieve the SQL for creating database tables.
*
* @since 3.3.0
*
* @global wpdb $wpdb WordPress database abstraction object.
*
* @param string $scope Optional. The tables for which to retrieve SQL. Can be all, global, ms_global, or blog tables. Defaults to all.
* @param int $blog_id Optional. The site ID for which to retrieve SQL. Default is the current site ID.
* @return string The SQL needed to create the requested tables.
*/
function wp_get_db_schema($scope = 'all', $blog_id = null)
{
global $wpdb;
$charset_collate = $wpdb->get_charset_collate();
if ($blog_id && $blog_id != $wpdb->blogid) {
$old_blog_id = $wpdb->set_blog_id($blog_id);
}
// Engage multisite if in the middle of turning it on from network.php.
$is_multisite = is_multisite() || defined('WP_INSTALLING_NETWORK') && WP_INSTALLING_NETWORK;
/*
* Indexes have a maximum size of 767 bytes. Historically, we haven't need to be concerned about that.
* As of 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.
*/
$max_index_length = 191;
// Blog specific tables.
$blog_tables = "CREATE TABLE {$wpdb->termmeta} (\n\tmeta_id bigint(20) unsigned NOT NULL auto_increment,\n\tterm_id bigint(20) unsigned NOT NULL default '0',\n\tmeta_key varchar(255) default NULL,\n\tmeta_value longtext,\n\tPRIMARY KEY (meta_id),\n\tKEY term_id (term_id),\n\tKEY meta_key (meta_key({$max_index_length}))\n) {$charset_collate};\nCREATE TABLE {$wpdb->terms} (\n term_id bigint(20) unsigned NOT NULL auto_increment,\n name varchar(200) NOT NULL default '',\n slug varchar(200) NOT NULL default '',\n term_group bigint(10) NOT NULL default 0,\n PRIMARY KEY (term_id),\n KEY slug (slug({$max_index_length})),\n KEY name (name({$max_index_length}))\n) {$charset_collate};\nCREATE TABLE {$wpdb->term_taxonomy} (\n term_taxonomy_id bigint(20) unsigned NOT NULL auto_increment,\n term_id bigint(20) unsigned NOT NULL default 0,\n taxonomy varchar(32) NOT NULL default '',\n description longtext NOT NULL,\n parent bigint(20) unsigned NOT NULL default 0,\n count bigint(20) NOT NULL default 0,\n PRIMARY KEY (term_taxonomy_id),\n UNIQUE KEY term_id_taxonomy (term_id,taxonomy),\n KEY taxonomy (taxonomy)\n) {$charset_collate};\nCREATE TABLE {$wpdb->term_relationships} (\n object_id bigint(20) unsigned NOT NULL default 0,\n term_taxonomy_id bigint(20) unsigned NOT NULL default 0,\n term_order int(11) NOT NULL default 0,\n PRIMARY KEY (object_id,term_taxonomy_id),\n KEY term_taxonomy_id (term_taxonomy_id)\n) {$charset_collate};\nCREATE TABLE {$wpdb->commentmeta} (\n\tmeta_id bigint(20) unsigned NOT NULL auto_increment,\n\tcomment_id bigint(20) unsigned NOT NULL default '0',\n\tmeta_key varchar(255) default NULL,\n\tmeta_value longtext,\n\tPRIMARY KEY (meta_id),\n\tKEY comment_id (comment_id),\n\tKEY meta_key (meta_key({$max_index_length}))\n) {$charset_collate};\nCREATE TABLE {$wpdb->comments} (\n\tcomment_ID bigint(20) unsigned NOT NULL auto_increment,\n\tcomment_post_ID bigint(20) unsigned NOT NULL default '0',\n\tcomment_author tinytext NOT NULL,\n\tcomment_author_email varchar(100) NOT NULL default '',\n\tcomment_author_url varchar(200) NOT NULL default '',\n\tcomment_author_IP varchar(100) NOT NULL default '',\n\tcomment_date datetime NOT NULL default '0000-00-00 00:00:00',\n\tcomment_date_gmt datetime NOT NULL default '0000-00-00 00:00:00',\n\tcomment_content text NOT NULL,\n\tcomment_karma int(11) NOT NULL default '0',\n\tcomment_approved varchar(20) NOT NULL default '1',\n\tcomment_agent varchar(255) NOT NULL default '',\n\tcomment_type varchar(20) NOT NULL default '',\n\tcomment_parent bigint(20) unsigned NOT NULL default '0',\n\tuser_id bigint(20) unsigned NOT NULL default '0',\n\tPRIMARY KEY (comment_ID),\n\tKEY comment_post_ID (comment_post_ID),\n\tKEY comment_approved_date_gmt (comment_approved,comment_date_gmt),\n\tKEY comment_date_gmt (comment_date_gmt),\n\tKEY comment_parent (comment_parent),\n\tKEY comment_author_email (comment_author_email(10))\n) {$charset_collate};\nCREATE TABLE {$wpdb->links} (\n\tlink_id bigint(20) unsigned NOT NULL auto_increment,\n\tlink_url varchar(255) NOT NULL default '',\n\tlink_name varchar(255) NOT NULL default '',\n\tlink_image varchar(255) NOT NULL default '',\n\tlink_target varchar(25) NOT NULL default '',\n\tlink_description varchar(255) NOT NULL default '',\n\tlink_visible varchar(20) NOT NULL default 'Y',\n\tlink_owner bigint(20) unsigned NOT NULL default '1',\n\tlink_rating int(11) NOT NULL default '0',\n\tlink_updated datetime NOT NULL default '0000-00-00 00:00:00',\n\tlink_rel varchar(255) NOT NULL default '',\n\tlink_notes mediumtext NOT NULL,\n\tlink_rss varchar(255) NOT NULL default '',\n\tPRIMARY KEY (link_id),\n\tKEY link_visible (link_visible)\n) {$charset_collate};\nCREATE TABLE {$wpdb->options} (\n\toption_id bigint(20) unsigned NOT NULL auto_increment,\n\toption_name varchar(191) NOT NULL default '',\n\toption_value longtext NOT NULL,\n\tautoload varchar(20) NOT NULL default 'yes',\n\tPRIMARY KEY (option_id),\n\tUNIQUE KEY option_name (option_name)\n) {$charset_collate};\nCREATE TABLE {$wpdb->postmeta} (\n\tmeta_id bigint(20) unsigned NOT NULL auto_increment,\n\tpost_id bigint(20) unsigned NOT NULL default '0',\n\tmeta_key varchar(255) default NULL,\n\tmeta_value longtext,\n\tPRIMARY KEY (meta_id),\n\tKEY post_id (post_id),\n\tKEY meta_key (meta_key({$max_index_length}))\n) {$charset_collate};\nCREATE TABLE {$wpdb->posts} (\n\tID bigint(20) unsigned NOT NULL auto_increment,\n\tpost_author bigint(20) unsigned NOT NULL default '0',\n\tpost_date datetime NOT NULL default '0000-00-00 00:00:00',\n\tpost_date_gmt datetime NOT NULL default '0000-00-00 00:00:00',\n\tpost_content longtext NOT NULL,\n\tpost_title text NOT NULL,\n\tpost_excerpt text NOT NULL,\n\tpost_status varchar(20) NOT NULL default 'publish',\n\tcomment_status varchar(20) NOT NULL default 'open',\n\tping_status varchar(20) NOT NULL default 'open',\n\tpost_password varchar(255) NOT NULL default '',\n\tpost_name varchar(200) NOT NULL default '',\n\tto_ping text NOT NULL,\n\tpinged text NOT NULL,\n\tpost_modified datetime NOT NULL default '0000-00-00 00:00:00',\n\tpost_modified_gmt datetime NOT NULL default '0000-00-00 00:00:00',\n\tpost_content_filtered longtext NOT NULL,\n\tpost_parent bigint(20) unsigned NOT NULL default '0',\n\tguid varchar(255) NOT NULL default '',\n\tmenu_order int(11) NOT NULL default '0',\n\tpost_type varchar(20) NOT NULL default 'post',\n\tpost_mime_type varchar(100) NOT NULL default '',\n\tcomment_count bigint(20) NOT NULL default '0',\n\tPRIMARY KEY (ID),\n\tKEY post_name (post_name({$max_index_length})),\n\tKEY type_status_date (post_type,post_status,post_date,ID),\n\tKEY post_parent (post_parent),\n\tKEY post_author (post_author)\n) {$charset_collate};\n";
// Single site users table. The multisite flavor of the users table is handled below.
$users_single_table = "CREATE TABLE {$wpdb->users} (\n\tID bigint(20) unsigned NOT NULL auto_increment,\n\tuser_login varchar(60) NOT NULL default '',\n\tuser_pass varchar(255) NOT NULL default '',\n\tuser_nicename varchar(50) NOT NULL default '',\n\tuser_email varchar(100) NOT NULL default '',\n\tuser_url varchar(100) NOT NULL default '',\n\tuser_registered datetime NOT NULL default '0000-00-00 00:00:00',\n\tuser_activation_key varchar(255) NOT NULL default '',\n\tuser_status int(11) NOT NULL default '0',\n\tdisplay_name varchar(250) NOT NULL default '',\n\tPRIMARY KEY (ID),\n\tKEY user_login_key (user_login),\n\tKEY user_nicename (user_nicename),\n\tKEY user_email (user_email)\n) {$charset_collate};\n";
// Multisite users table
$users_multi_table = "CREATE TABLE {$wpdb->users} (\n\tID bigint(20) unsigned NOT NULL auto_increment,\n\tuser_login varchar(60) NOT NULL default '',\n\tuser_pass varchar(255) NOT NULL default '',\n\tuser_nicename varchar(50) NOT NULL default '',\n\tuser_email varchar(100) NOT NULL default '',\n\tuser_url varchar(100) NOT NULL default '',\n\tuser_registered datetime NOT NULL default '0000-00-00 00:00:00',\n\tuser_activation_key varchar(255) NOT NULL default '',\n\tuser_status int(11) NOT NULL default '0',\n\tdisplay_name varchar(250) NOT NULL default '',\n\tspam tinyint(2) NOT NULL default '0',\n\tdeleted tinyint(2) NOT NULL default '0',\n\tPRIMARY KEY (ID),\n\tKEY user_login_key (user_login),\n\tKEY user_nicename (user_nicename),\n\tKEY user_email (user_email)\n) {$charset_collate};\n";
// Usermeta.
$usermeta_table = "CREATE TABLE {$wpdb->usermeta} (\n\tumeta_id bigint(20) unsigned NOT NULL auto_increment,\n\tuser_id bigint(20) unsigned NOT NULL default '0',\n\tmeta_key varchar(255) default NULL,\n\tmeta_value longtext,\n\tPRIMARY KEY (umeta_id),\n\tKEY user_id (user_id),\n\tKEY meta_key (meta_key({$max_index_length}))\n) {$charset_collate};\n";
// Global tables
if ($is_multisite) {
$global_tables = $users_multi_table . $usermeta_table;
} else {
$global_tables = $users_single_table . $usermeta_table;
}
// Multisite global tables.
$ms_global_tables = "CREATE TABLE {$wpdb->blogs} (\n\tblog_id bigint(20) NOT NULL auto_increment,\n\tsite_id bigint(20) NOT NULL default '0',\n\tdomain varchar(200) NOT NULL default '',\n\tpath varchar(100) NOT NULL default '',\n\tregistered datetime NOT NULL default '0000-00-00 00:00:00',\n\tlast_updated datetime NOT NULL default '0000-00-00 00:00:00',\n\tpublic tinyint(2) NOT NULL default '1',\n\tarchived tinyint(2) NOT NULL default '0',\n\tmature tinyint(2) NOT NULL default '0',\n\tspam tinyint(2) NOT NULL default '0',\n\tdeleted tinyint(2) NOT NULL default '0',\n\tlang_id int(11) NOT NULL default '0',\n\tPRIMARY KEY (blog_id),\n\tKEY domain (domain(50),path(5)),\n\tKEY lang_id (lang_id)\n) {$charset_collate};\nCREATE TABLE {$wpdb->blog_versions} (\n\tblog_id bigint(20) NOT NULL default '0',\n\tdb_version varchar(20) NOT NULL default '',\n\tlast_updated datetime NOT NULL default '0000-00-00 00:00:00',\n\tPRIMARY KEY (blog_id),\n\tKEY db_version (db_version)\n) {$charset_collate};\nCREATE TABLE {$wpdb->blogmeta} (\n\tmeta_id bigint(20) unsigned NOT NULL auto_increment,\n\tblog_id bigint(20) NOT NULL default '0',\n\tmeta_key varchar(255) default NULL,\n\tmeta_value longtext,\n\tPRIMARY KEY (meta_id),\n\tKEY meta_key (meta_key({$max_index_length})),\n\tKEY blog_id (blog_id)\n) {$charset_collate};\nCREATE TABLE {$wpdb->registration_log} (\n\tID bigint(20) NOT NULL auto_increment,\n\temail varchar(255) NOT NULL default '',\n\tIP varchar(30) NOT NULL default '',\n\tblog_id bigint(20) NOT NULL default '0',\n\tdate_registered datetime NOT NULL default '0000-00-00 00:00:00',\n\tPRIMARY KEY (ID),\n\tKEY IP (IP)\n) {$charset_collate};\nCREATE TABLE {$wpdb->site} (\n\tid bigint(20) NOT NULL auto_increment,\n\tdomain varchar(200) NOT NULL default '',\n\tpath varchar(100) NOT NULL default '',\n\tPRIMARY KEY (id),\n\tKEY domain (domain(140),path(51))\n) {$charset_collate};\nCREATE TABLE {$wpdb->sitemeta} (\n\tmeta_id bigint(20) NOT NULL auto_increment,\n\tsite_id bigint(20) NOT NULL default '0',\n\tmeta_key varchar(255) default NULL,\n\tmeta_value longtext,\n\tPRIMARY KEY (meta_id),\n\tKEY meta_key (meta_key({$max_index_length})),\n\tKEY site_id (site_id)\n) {$charset_collate};\nCREATE TABLE {$wpdb->signups} (\n\tsignup_id bigint(20) NOT NULL auto_increment,\n\tdomain varchar(200) NOT NULL default '',\n\tpath varchar(100) NOT NULL default '',\n\ttitle longtext NOT NULL,\n\tuser_login varchar(60) NOT NULL default '',\n\tuser_email varchar(100) NOT NULL default '',\n\tregistered datetime NOT NULL default '0000-00-00 00:00:00',\n\tactivated datetime NOT NULL default '0000-00-00 00:00:00',\n\tactive tinyint(1) NOT NULL default '0',\n\tactivation_key varchar(50) NOT NULL default '',\n\tmeta longtext,\n\tPRIMARY KEY (signup_id),\n\tKEY activation_key (activation_key),\n\tKEY user_email (user_email),\n\tKEY user_login_email (user_login,user_email),\n\tKEY domain_path (domain(140),path(51))\n) {$charset_collate};";
switch ($scope) {
case 'blog':
$queries = $blog_tables;
break;
case 'global':
$queries = $global_tables;
if ($is_multisite) {
$queries .= $ms_global_tables;
}
break;
case 'ms_global':
$queries = $ms_global_tables;
break;
case 'all':
default:
$queries = $global_tables . $blog_tables;
if ($is_multisite) {
$queries .= $ms_global_tables;
}
break;
}
if (isset($old_blog_id)) {
$wpdb->set_blog_id($old_blog_id);
}
return $queries;
}