function dbDelta( $queries = '', $execute = true ) {
global $wpdb;
if ( in_array( $queries, array( '', 'all', 'blog', 'global', 'ms_global' ), true ) )
$queries = wp_get_db_schema( $queries );
if ( !is_array($queries) ) {
$queries = explode( ';', $queries );
$queries = array_filter( $queries );
}
$queries = apply_filters( 'dbdelta_queries', $queries );
$cqueries = array();
$iqueries = array();
$for_update = array();
foreach ($queries as $qry) {
if ( preg_match( "|CREATE TABLE ([^ ]*)|", $qry, $matches ) ) {
$cqueries[ trim( $matches[1], '`' ) ] = $qry;
$for_update[$matches[1]] = 'Created table '.$matches[1];
} elseif ( preg_match( "|CREATE DATABASE ([^ ]*)|", $qry, $matches ) ) {
array_unshift( $cqueries, $qry );
} elseif ( preg_match( "|INSERT INTO ([^ ]*)|", $qry, $matches ) ) {
$iqueries[] = $qry;
} elseif ( preg_match( "|UPDATE ([^ ]*)|", $qry, $matches ) ) {
$iqueries[] = $qry;
} else {
}
}
$cqueries = apply_filters( 'dbdelta_create_queries', $cqueries );
$iqueries = apply_filters( 'dbdelta_insert_queries', $iqueries );
$text_fields = array( 'tinytext', 'text', 'mediumtext', 'longtext' );
$blob_fields = array( 'tinyblob', 'blob', 'mediumblob', 'longblob' );
$global_tables = $wpdb->tables( 'global' );
foreach ( $cqueries as $table => $qry ) {
if ( in_array( $table, $global_tables ) && ! wp_should_upgrade_global_tables() ) {
unset( $cqueries[ $table ], $for_update[ $table ] );
continue;
}
$suppress = $wpdb->suppress_errors();
$tablefields = $wpdb->get_results("DESCRIBE {$table};");
$wpdb->suppress_errors( $suppress );
if ( ! $tablefields )
continue;
$cfields = $indices = $indices_without_subparts = array();
preg_match("|\((.*)\)|ms", $qry, $match2);
$qryline = trim($match2[1]);
$flds = explode("\n", $qryline);
foreach ( $flds as $fld ) {
$fld = trim( $fld, " \t\n\r\0\x0B," );
preg_match( '|^([^ ]*)|', $fld, $fvals );
$fieldname = trim( $fvals[1], '`' );
$fieldname_lowercased = strtolower( $fieldname );
$validfield = true;
switch ( $fieldname_lowercased ) {
case '':
case 'primary':
case 'index':
case 'fulltext':
case 'unique':
case 'key':
case 'spatial':
$validfield = false;
preg_match(
'/^'
. '(?P<index_type>'
. 'PRIMARY\s+KEY|(?:UNIQUE|FULLTEXT|SPATIAL)\s+(?:KEY|INDEX)|KEY|INDEX'
. ')'
. '\s+'
. '(?:'
. '`?'
. '(?P<index_name>'
. '(?:[0-9a-zA-Z$_-]|[\xC2-\xDF][\x80-\xBF])+'
. ')'
. '`?'
. '\s+'
. ')*'
. '\('
. '(?P<index_columns>'
. '.+?'
. ')'
. '\)'
. '$/im',
$fld,
$index_matches
);
$index_type = strtoupper( preg_replace( '/\s+/', ' ', trim( $index_matches['index_type'] ) ) );
$index_type = str_replace( 'INDEX', 'KEY', $index_type );
$index_name = ( 'PRIMARY KEY' === $index_type ) ? '' : '`' . strtolower( $index_matches['index_name'] ) . '`';
$index_columns = $index_columns_without_subparts = array_map( 'trim', explode( ',', $index_matches['index_columns'] ) );
foreach ( $index_columns as $id => &$index_column ) {
preg_match(
'/'
. '`?'
. '(?P<column_name>'
. '(?:[0-9a-zA-Z$_-]|[\xC2-\xDF][\x80-\xBF])+'
. ')'
. '`?'
. '(?:'
. '\s*'
. '\('
. '\s*'
. '(?P<sub_part>'
. '\d+'
. ')'
. '\s*'
. '\)'
. ')?'
. '/',
$index_column,
$index_column_matches
);
$index_column = '`' . $index_column_matches['column_name'] . '`';
$index_columns_without_subparts[ $id ] = $index_column;
if ( isset( $index_column_matches['sub_part'] ) ) {
$index_column .= '(' . $index_column_matches['sub_part'] . ')';
}
}
$indices[] = "{$index_type} {$index_name} (" . implode( ',', $index_columns ) . ")";
$indices_without_subparts[] = "{$index_type} {$index_name} (" . implode( ',', $index_columns_without_subparts ) . ")";
unset( $index_column, $index_column_matches, $index_matches, $index_type, $index_name, $index_columns, $index_columns_without_subparts );
break;
}
if ( $validfield ) {
$cfields[ $fieldname_lowercased ] = $fld;
}
}
foreach ( $tablefields as $tablefield ) {
$tablefield_field_lowercased = strtolower( $tablefield->Field );
$tablefield_type_lowercased = strtolower( $tablefield->Type );
if ( array_key_exists( $tablefield_field_lowercased, $cfields ) ) {
preg_match( '|`?' . $tablefield->Field . '`? ([^ ]*( unsigned)?)|i', $cfields[ $tablefield_field_lowercased ], $matches );
$fieldtype = $matches[1];
$fieldtype_lowercased = strtolower( $fieldtype );
if ($tablefield->Type != $fieldtype) {
$do_change = true;
if ( in_array( $fieldtype_lowercased, $text_fields ) && in_array( $tablefield_type_lowercased, $text_fields ) ) {
if ( array_search( $fieldtype_lowercased, $text_fields ) < array_search( $tablefield_type_lowercased, $text_fields ) ) {
$do_change = false;
}
}
if ( in_array( $fieldtype_lowercased, $blob_fields ) && in_array( $tablefield_type_lowercased, $blob_fields ) ) {
if ( array_search( $fieldtype_lowercased, $blob_fields ) < array_search( $tablefield_type_lowercased, $blob_fields ) ) {
$do_change = false;
}
}
if ( $do_change ) {
$cqueries[] = "ALTER TABLE {$table} CHANGE COLUMN `{$tablefield->Field}` " . $cfields[ $tablefield_field_lowercased ];
$for_update[$table.'.'.$tablefield->Field] = "Changed type of {$table}.{$tablefield->Field} from {$tablefield->Type} to {$fieldtype}";
}
}
if ( preg_match( "| DEFAULT '(.*?)'|i", $cfields[ $tablefield_field_lowercased ], $matches ) ) {
$default_value = $matches[1];
if ($tablefield->Default != $default_value) {
$cqueries[] = "ALTER TABLE {$table} ALTER COLUMN `{$tablefield->Field}` SET DEFAULT '{$default_value}'";
$for_update[$table.'.'.$tablefield->Field] = "Changed default value of {$table}.{$tablefield->Field} from {$tablefield->Default} to {$default_value}";
}
}
unset( $cfields[ $tablefield_field_lowercased ] );
} else {
}
}
foreach ($cfields as $fieldname => $fielddef) {
$cqueries[] = "ALTER TABLE {$table} ADD COLUMN $fielddef";
$for_update[$table.'.'.$fieldname] = 'Added column '.$table.'.'.$fieldname;
}
$tableindices = $wpdb->get_results("SHOW INDEX FROM {$table};");
if ($tableindices) {
$index_ary = array();
foreach ($tableindices as $tableindex) {
$keyname = strtolower( $tableindex->Key_name );
$index_ary[$keyname]['columns'][] = array('fieldname' => $tableindex->Column_name, 'subpart' => $tableindex->Sub_part);
$index_ary[$keyname]['unique'] = ($tableindex->Non_unique == 0)?true:false;
$index_ary[$keyname]['index_type'] = $tableindex->Index_type;
}
foreach ($index_ary as $index_name => $index_data) {
$index_string = '';
if ($index_name == 'primary') {
$index_string .= 'PRIMARY ';
} elseif ( $index_data['unique'] ) {
$index_string .= 'UNIQUE ';
}
if ( 'FULLTEXT' === strtoupper( $index_data['index_type'] ) ) {
$index_string .= 'FULLTEXT ';
}
if ( 'SPATIAL' === strtoupper( $index_data['index_type'] ) ) {
$index_string .= 'SPATIAL ';
}
$index_string .= 'KEY ';
if ( 'primary' !== $index_name ) {
$index_string .= '`' . $index_name . '`';
}
$index_columns = '';
foreach ($index_data['columns'] as $column_data) {
if ( $index_columns != '' ) {
$index_columns .= ',';
}
$index_columns .= '`' . $column_data['fieldname'] . '`';
}
$index_string .= " ($index_columns)";
if ( ! ( ( $aindex = array_search( $index_string, $indices_without_subparts ) ) === false ) ) {
unset( $indices_without_subparts[ $aindex ] );
unset( $indices[ $aindex ] );
}
}
}
foreach ( (array) $indices as $index ) {
$cqueries[] = "ALTER TABLE {$table} ADD $index";
$for_update[] = 'Added index ' . $table . ' ' . $index;
}
unset( $cqueries[ $table ], $for_update[ $table ] );
}
$allqueries = array_merge($cqueries, $iqueries);
if ($execute) {
foreach ($allqueries as $query) {
$wpdb->query($query);
}
}
return $for_update;
}
You must be very careful in your SQL command structure when creating tables with indexes.
Here is a simple example of the proper create table syntax for a table with a primary key on a field named “id” and a secondary key on a field named “first”.
PRIMARY KEY must be followed by TWO SPACES then the open parenthesis then the field name and a closing parenthesis.
KEY must be followed by a SINGLE SPACE then the key name then a space then open parenthesis with the field name then a closed parenthesis.
privatefunctionindex_test_001() {global$wpdb;$table_name=$wpdb->prefix .'dbdelta_test_001';$wpdb_collate=$wpdb->collate;$sql="CREATE TABLE {$table_name} (id mediumint(8) unsigned NOT NULL auto_increment ,first varchar(255) NULL,PRIMARY KEY (id),KEY first (first))COLLATE {$wpdb_collate}";require_once(ABSPATH .'wp-admin/includes/upgrade.php');dbDelta($sql);}Expand full source code
Be careful not to put a COMMENT on field or key; the preg_match code doesn’t handle it. The following code is wrong (thanks to Store Locator Plus’ code).
privatefunctionindex_test_001() {global$wpdb;$table_name=$wpdb->prefix .'dbdelta_test_001';$wpdb_collate=$wpdb->collate;$sql="CREATE TABLE {$table_name} (id mediumint(8) unsigned NOT NULL auto_increment ,first varchar(255) NULL,PRIMARY KEY (id),KEY first (first) COMMENT'First name')COLLATE {$wpdb_collate}";require_once(ABSPATH .'wp-admin/includes/upgrade.php');dbDelta($sql);}Expand full source code
Note: If you change the name of a field, an empty column with the new name will be created, but the old column is not removed!
If you need to change the structure of a table, is better to use
$wpdbrather than this method because is not guaranteed it works for updating any table structure.(I post here a corrected version of my previous note. Several typographical mistakes have slipped into the original version.)
As a side-note, the dbDelta function cannot be used to drop a table from the wp_ database . A function such as the one below can be used instead (don’t forget to replace
my_themewith your own theme name):functionmy_theme_drop_table ($table_name='the_name_without_any_prefix'){global$wpdb;$table_name_prepared=$wpdb->prefix .$table_name;$the_removal_query="DROP TABLE IF EXISTS {$table_name_prepared}";$wpdb->query($the_removal_query);}See also https://developer.wordpress.org/plugins/the-basics/uninstall-methods/.