function dbDelta( $queries = '', $execute = true ) {
    global $wpdb;
 
    if ( in_array( $queries, array( '', 'all', 'blog', 'global', 'ms_global' ), true ) ) {
        $queries = wp_get_db_schema( $queries );
    }
 
    // Separate individual queries into an array
    if ( ! is_array( $queries ) ) {
        $queries = explode( ';', $queries );
        $queries = array_filter( $queries );
    }
 
    /**
     * Filters the dbDelta SQL queries.
     *
     * @since 3.3.0
     *
     * @param string[] $queries An array of dbDelta SQL queries.
     */
    $queries = apply_filters( 'dbdelta_queries', $queries );
 
    $cqueries   = array(); // Creation Queries
    $iqueries   = array(); // Insertion Queries
    $for_update = array();
 
    // Create a tablename index for an array ($cqueries) of queries
    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 {
            // Unrecognized query type
        }
    }
 
    /**
     * Filters the dbDelta SQL queries for creating tables and/or databases.
     *
     * Queries filterable via this hook contain "CREATE TABLE" or "CREATE DATABASE".
     *
     * @since 3.3.0
     *
     * @param string[] $cqueries An array of dbDelta create SQL queries.
     */
    $cqueries = apply_filters( 'dbdelta_create_queries', $cqueries );
 
    /**
     * Filters the dbDelta SQL queries for inserting or updating.
     *
     * Queries filterable via this hook contain "INSERT INTO" or "UPDATE".
     *
     * @since 3.3.0
     *
     * @param string[] $iqueries An array of dbDelta insert or update SQL queries.
     */
    $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 ) {
        // Upgrade global tables only for the main site. Don't upgrade at all if conditions are not optimal.
        if ( in_array( $table, $global_tables ) && ! wp_should_upgrade_global_tables() ) {
            unset( $cqueries[ $table ], $for_update[ $table ] );
            continue;
        }
 
        // Fetch the table column structure from the database
        $suppress    = $wpdb->suppress_errors();
        $tablefields = $wpdb->get_results( "DESCRIBE {$table};" );
        $wpdb->suppress_errors( $suppress );
 
        if ( ! $tablefields ) {
            continue;
        }
 
        // Clear the field and index arrays.
        $cfields = $indices = $indices_without_subparts = array();
 
        // Get all of the field names in the query from between the parentheses.
        preg_match( '|\((.*)\)|ms', $qry, $match2 );
        $qryline = trim( $match2[1] );
 
        // Separate field lines into an array.
        $flds = explode( "\n", $qryline );
 
        // For every field line specified in the query.
        foreach ( $flds as $fld ) {
            $fld = trim( $fld, " \t\n\r\0\x0B," ); // Default trim characters, plus ','.
 
            // Extract the field name.
            preg_match( '|^([^ ]*)|', $fld, $fvals );
            $fieldname            = trim( $fvals[1], '`' );
            $fieldname_lowercased = strtolower( $fieldname );
 
            // Verify the found field name.
            $validfield = true;
            switch ( $fieldname_lowercased ) {
                case '':
                case 'primary':
                case 'index':
                case 'fulltext':
                case 'unique':
                case 'key':
                case 'spatial':
                    $validfield = false;
 
                    /*
                     * Normalize the index definition.
                     *
                     * This is done so the definition can be compared against the result of a
                     * `SHOW INDEX FROM $table_name` query which returns the current table
                     * index information.
                     */
 
                    // Extract type, name and columns from the definition.
                    // phpcs:disable Squiz.Strings.ConcatenationSpacing.PaddingFound -- don't remove regex indentation
                    preg_match(
                        '/^'
                        .   '(?P<index_type>'             // 1) Type of the index.
                        .       'PRIMARY\s+KEY|(?:UNIQUE|FULLTEXT|SPATIAL)\s+(?:KEY|INDEX)|KEY|INDEX'
                        .   ')'
                        .   '\s+'                         // Followed by at least one white space character.
                        .   '(?:'                         // Name of the index. Optional if type is PRIMARY KEY.
                        .       '`?'                      // Name can be escaped with a backtick.
                        .           '(?P<index_name>'     // 2) Name of the index.
                        .               '(?:[0-9a-zA-Z$_-]|[\xC2-\xDF][\x80-\xBF])+'
                        .           ')'
                        .       '`?'                      // Name can be escaped with a backtick.
                        .       '\s+'                     // Followed by at least one white space character.
                        .   ')*'
                        .   '\('                          // Opening bracket for the columns.
                        .       '(?P<index_columns>'
                        .           '.+?'                 // 3) Column names, index prefixes, and orders.
                        .       ')'
                        .   '\)'                          // Closing bracket for the columns.
                        . '$/im',
                        $fld,
                        $index_matches
                    );
                    // phpcs:enable
 
                    // Uppercase the index type and normalize space characters.
                    $index_type = strtoupper( preg_replace( '/\s+/', ' ', trim( $index_matches['index_type'] ) ) );
 
                    // 'INDEX' is a synonym for 'KEY', standardize on 'KEY'.
                    $index_type = str_replace( 'INDEX', 'KEY', $index_type );
 
                    // Escape the index name with backticks. An index for a primary key has no name.
                    $index_name = ( 'PRIMARY KEY' === $index_type ) ? '' : '`' . strtolower( $index_matches['index_name'] ) . '`';
 
                    // Parse the columns. Multiple columns are separated by a comma.
                    $index_columns = $index_columns_without_subparts = array_map( 'trim', explode( ',', $index_matches['index_columns'] ) );
 
                    // Normalize columns.
                    foreach ( $index_columns as $id => &$index_column ) {
                        // Extract column name and number of indexed characters (sub_part).
                        preg_match(
                            '/'
                            . '`?'                      // Name can be escaped with a backtick.
                            . '(?P<column_name>'    // 1) Name of the column.
                            . '(?:[0-9a-zA-Z$_-]|[\xC2-\xDF][\x80-\xBF])+'
                            . ')'
                            . '`?'                      // Name can be escaped with a backtick.
                            . '(?:'                     // Optional sub part.
                            . '\s*'                 // Optional white space character between name and opening bracket.
                            . '\('                  // Opening bracket for the sub part.
                            . '\s*'             // Optional white space character after opening bracket.
                            . '(?P<sub_part>'
                            . '\d+'         // 2) Number of indexed characters.
                            . ')'
                            . '\s*'             // Optional white space character before closing bracket.
                            . '\)'                 // Closing bracket for the sub part.
                            . ')?'
                            . '/',
                            $index_column,
                            $index_column_matches
                        );
 
                        // Escape the column name with backticks.
                        $index_column = '`' . $index_column_matches['column_name'] . '`';
 
                        // We don't need to add the subpart to $index_columns_without_subparts
                        $index_columns_without_subparts[ $id ] = $index_column;
 
                        // Append the optional sup part with the number of indexed characters.
                        if ( isset( $index_column_matches['sub_part'] ) ) {
                            $index_column .= '(' . $index_column_matches['sub_part'] . ')';
                        }
                    }
 
                    // Build the normalized index definition and add it to the list of indices.
                    $indices[]                  = "{$index_type} {$index_name} (" . implode( ',', $index_columns ) . ')';
                    $indices_without_subparts[] = "{$index_type} {$index_name} (" . implode( ',', $index_columns_without_subparts ) . ')';
 
                    // Destroy no longer needed variables.
                    unset( $index_column, $index_column_matches, $index_matches, $index_type, $index_name, $index_columns, $index_columns_without_subparts );
 
                    break;
            }
 
            // If it's a valid field, add it to the field array.
            if ( $validfield ) {
                $cfields[ $fieldname_lowercased ] = $fld;
            }
        }
 
        // For every field in the table.
        foreach ( $tablefields as $tablefield ) {
            $tablefield_field_lowercased = strtolower( $tablefield->Field );
            $tablefield_type_lowercased  = strtolower( $tablefield->Type );
 
            // If the table field exists in the field array ...
            if ( array_key_exists( $tablefield_field_lowercased, $cfields ) ) {
 
                // Get the field type from the query.
                preg_match( '|`?' . $tablefield->Field . '`? ([^ ]*( unsigned)?)|i', $cfields[ $tablefield_field_lowercased ], $matches );
                $fieldtype            = $matches[1];
                $fieldtype_lowercased = strtolower( $fieldtype );
 
                // Is actual field type different from the field type in query?
                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 ) {
                        // Add a query to change the column type.
                        $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}";
                    }
                }
 
                // Get the default value from the array.
                if ( preg_match( "| DEFAULT '(.*?)'|i", $cfields[ $tablefield_field_lowercased ], $matches ) ) {
                    $default_value = $matches[1];
                    if ( $tablefield->Default != $default_value ) {
                        // Add a query to change the column's 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}";
                    }
                }
 
                // Remove the field from the array (so it's not added).
                unset( $cfields[ $tablefield_field_lowercased ] );
            } else {
                // This field exists in the table, but not in the creation queries?
            }
        }
 
        // For every remaining field specified for the table.
        foreach ( $cfields as $fieldname => $fielddef ) {
            // Push a query line into $cqueries that adds the field to that table.
            $cqueries[]                              = "ALTER TABLE {$table} ADD COLUMN $fielddef";
            $for_update[ $table . '.' . $fieldname ] = 'Added column ' . $table . '.' . $fieldname;
        }
 
        // Index stuff goes here. Fetch the table index structure from the database.
        $tableindices = $wpdb->get_results( "SHOW INDEX FROM {$table};" );
 
        if ( $tableindices ) {
            // Clear the index array.
            $index_ary = array();
 
            // For every index in the table.
            foreach ( $tableindices as $tableindex ) {
 
                // Add the index to the index data array.
                $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;
            }
 
            // For each actual index in the index array.
            foreach ( $index_ary as $index_name => $index_data ) {
 
                // Build a create string to compare to the query.
                $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 = '';
 
                // For each column in the index.
                foreach ( $index_data['columns'] as $column_data ) {
                    if ( $index_columns != '' ) {
                        $index_columns .= ',';
                    }
 
                    // Add the field to the column list string.
                    $index_columns .= '`' . $column_data['fieldname'] . '`';
                }
 
                // Add the column list to the index create string.
                $index_string .= " ($index_columns)";
 
                // Check if the index definition exists, ignoring subparts.
                if ( ! ( ( $aindex = array_search( $index_string, $indices_without_subparts ) ) === false ) ) {
                    // If the index already exists (even with different subparts), we don't need to create it.
                    unset( $indices_without_subparts[ $aindex ] );
                    unset( $indices[ $aindex ] );
                }
            }
        }
 
        // For every remaining index specified for the table.
        foreach ( (array) $indices as $index ) {
            // Push a query line into $cqueries that adds the index to that table.
            $cqueries[]   = "ALTER TABLE {$table} ADD $index";
            $for_update[] = 'Added index ' . $table . ' ' . $index;
        }
 
        // Remove the original table creation query from processing.
        unset( $cqueries[ $table ], $for_update[ $table ] );
    }
 
    $allqueries = array_merge( $cqueries, $iqueries );
    if ( $execute ) {
        foreach ( $allqueries as $query ) {
            $wpdb->query( $query );
        }
    }
 
    return $for_update;
}

Expand full source code Collapse full source code View on Trac

Top ↑

Changelog Changelog

Changelog
Version	Description
1.5.0	Introduced.

Top ↑

Uses Uses

Uses
Uses	Description
wp-admin/includes/upgrade.php: wp_should_upgrade_global_tables()	Determine if global tables should be upgraded.
wp-admin/includes/schema.php: wp_get_db_schema()	Retrieve the SQL for creating database tables.
wp-admin/includes/upgrade.php: dbdelta_create_queries	Filters the dbDelta SQL queries for creating tables and/or databases.
wp-admin/includes/upgrade.php: dbdelta_insert_queries	Filters the dbDelta SQL queries for inserting or updating.
wp-admin/includes/upgrade.php: dbdelta_queries	Filters the dbDelta SQL queries.
wp-includes/plugin.php: apply_filters()	Call the functions added to a filter hook.
wp-includes/wp-db.php: wpdb::get_results()	Retrieve an entire SQL result set from the database (i.e., many rows)
wp-includes/wp-db.php: wpdb::query()	Perform a MySQL database query, using current database connection.
wp-includes/wp-db.php: wpdb::tables()	Returns an array of WordPress tables.
wp-includes/wp-db.php: wpdb::suppress_errors()	Whether to suppress database errors.

Show 5 more uses Hide more uses

Top ↑

Used By Used By

Used By
Used By	Description
wp-admin/includes/schema.php: install_network()	Install Network.
wp-admin/includes/upgrade.php: make_db_current()	Updates the database tables to a new schema.
wp-admin/includes/upgrade.php: make_db_current_silent()	Updates the database tables to a new schema, but without displaying results.
wp-admin/includes/upgrade.php: install_global_terms()	Install global terms.

Top ↑

User Contributed Notes User Contributed Notes

Skip to note 1 content

You must log in to vote on the helpfulness of this noteVote results for this note: 2You must log in to vote on the helpfulness of this note

Contributed by Jacob N. Breetvelt — 2 years ago

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!

Log in to add feedback

Log in to add feedback

Contributed by Store Locator Plus — 3 years ago

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.

private function index_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 Collapse full source code

-1

Contributed by Earnest Boyd — 2 years ago

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).

private function index_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 Collapse full source code

-3

Contributed by octag — 2 years ago

(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_theme with your own theme name):

function my_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 );
}

Skip to note 5 content

You must log in to vote on the helpfulness of this noteVote results for this note: -3You must log in to vote on the helpfulness of this note

Contributed by neoacevedo — 1 year ago

If you need to change the structure of a table, is better to use $wpdb rather than this method because is not guaranteed it works for updating any table structure.

Log in to add feedback

Log in to add feedback

You must log in before being able to contribute a note or feedback.

WordPress.org

Code Reference

dbDelta( string[]|string $queries = '', bool $execute = true )

Contents

Description Description

Parameters Parameters

Return Return

Source Source

Changelog Changelog

User Contributed Notes User Contributed Notes

WordPress.org

dbDelta( string[]|string $queries = '', bool $execute = true )

Contents

Description #Description

Parameters #Parameters

Return #Return

Source #Source

Changelog #Changelog

Related #Related

Uses #Uses

Used By #Used By

User Contributed Notes #User Contributed Notes

Description Description

Parameters Parameters

Return Return

Source Source

Changelog Changelog

Related Related

Uses Uses

Used By Used By

User Contributed Notes User Contributed Notes