WordPress at a glance

dbDelta() WP 1.5.0

Creates or modifies database tables based on the specified SQL query to create table.

Useful for creating new tables and updating existing tables to a new structure.

You need to pass CREATE TABLE SQL query to the function and if the table does not already exist, it will be created, if it already exists, the structure of the table will be updated.

When you update a table, the SQL query that you submit to the function is parsed into parts and based on this data function changes to the existing table.

Requirements

Because the function parses the request that is sent, it must meet some requirements:

  1. Each field must be on a new line.

  2. The query must not contain double line breaks, for example, you cannot separate fields from indexes with an empty line.

  3. When creating multi-indexes, separate its names with a comma without space. Good: KEY field (field, field2). Bad: KEY field (field, field2).

  4. You canNOT use apostrophes (' or `) or back quotes for table names or table field names: $wpdb->table, but not `$wpdb->table`.

  5. You must specify the length of the field if it can have a length. For example: int(11).
Notes about function work
  1. When you update the structure, the order of the fields is not taken into account. I.e, if the new field is on the second place in the query and it is added to the EXISTING table, it will be added to the end, but not on the place it was in SQL query.

  2. You will not be able to add the main field auto increment with PRIMARY KEY to an EXISTING table.

  3. If we delete index from the existing structure, it will not be deleted from the existing table.

  4. The same applies to the fields. For example, if we rename existing field dbDelta() create new field, but not delete existing one.

  5. If the index is changed, for example, another field is added to it, but the name has not changed, the function will cause an error (the index already exists). In General, to update the indexes, it is better to make separate queries...
When should I use dbDelta?

Suitable to create new tables, fields, indexes, to change the structure of fields (but not names).

Not suitable for changing index structure, for deleting fields or indexes. Any deletion in the field structure must be made by separate requests. Changing the structure of the existing indexes should also be done separately.

The function is not defined by default, so you need to connect the file wp-admin/includes/upgrade.php:

require_once ABSPATH . 'wp-admin/includes/upgrade.php';
  • Global. wpdb. $wpdb
Works based on: wp_get_db_schema()
Hooks from function:
Return

Array. Strings containing the results of the various update queries.

Usage

dbDelta( $queries, $execute );
$queries(string/array)

SQL query.
You can specify a string with multiple queries (separated by semicolons).
You can specify an array where each element must be a separate SQL query.

Internal use (usually not necessary). If you set this parameter to one of the values: '', 'all', 'blog', 'global', 'ms_global'. The $queries will get queries to create WordPress tables where:

  • blog - SQL queries for creating blog tables: $wpdb->posts, $wpdb->comments etc.
  • global - to create global tables, these include $wpdb->users (different for multi-sites) and $wpdb->usermeta.
  • ms_global - to create global tables for Multisite.
  • all - requests to create all possible WordPress tables: includes blog and global.

Default: ''

$execute(true/false)
Whether or not to execute queries specified in $queries. false - do not execute.
Default: true

Examples

#1 Create a custom table in the database

The created table should have a prefix that is written in wp-config.php we can get it like this: $wpdb->get_blog_prefix() or $wpdb->prefix. You can also get the default encoding specified in the same place: $wpdb->charset and $wpdb->collate.

function create_table() {
	global $wpdb;

	require_once(ABSPATH . 'wp-admin/includes/upgrade.php');

	$table_name = $wpdb->get_blog_prefix() . 'test_table';
	$charset_collate = "DEFAULT CHARACTER SET {$wpdb->charset} COLLATE {$wpdb->collate}";

	$sql = "CREATE TABLE {$table_name} (
	id  bigint(20) unsigned NOT NULL auto_increment,
	address varchar(255) NOT NULL default '',
	alert varchar(20) NOT NULL default '',
	meta longtext NOT NULL default '',
	PRIMARY KEY  (id),
	KEY alert (alert)
	)
	{$charset_collate};";

	dbDelta($sql);
}

create_table();

Here the function creates a table {prefix}test_table containing columns id, address, alert and meta. id is the primary key with auto-increment. The alert field is set as indexable.

#2 Add a column to the existing table

In this example, the function adds new column to the table named 'test_table', the function dbDelta() allows to do it more conveniently.

function update_table() {
	global $wpdb;

	require_once(ABSPATH . 'wp-admin/includes/upgrade.php');

	$table_name = $wpdb->get_blog_prefix() . 'test_table';
	$charset_collate = "DEFAULT CHARACTER SET {$wpdb->charset} COLLATE {$wpdb->collate}";

	$sql = "CREATE TABLE {$table_name} (
	id  bigint(20) unsigned NOT NULL auto_increment,
	address varchar(255) NOT NULL default '',
	alert varchar(20) NOT NULL default '',
	meta longtext NOT NULL default '',
	new_meta longtext NOT NULL default '',
	PRIMARY KEY  (id),
	KEY alert (alert)
	)
	{$charset_collate};";

	dbDelta($sql);
}

update_table();

#3 Another example of creating a table with comments

function create_payment_info_table(){
	global $wpdb;

	require_once ABSPATH . 'wp-admin/includes/upgrade.php';

	$sql = "CREATE TABLE $wpdb->payment_info (
		id             BIGINT(20) unsigned NOT NULL auto_increment,
		product_id     BIGINT(20)   NOT NULL default 0                     COMMENT 'ID поста (продукта)',
		customer_email VARCHAR(255) NOT NULL default ''                    COMMENT 'email покупателя',
		trans_no       VARCHAR(255) NOT NULL default ''                    COMMENT 'Номер транзакции',
		trans_date     DATETIME     NOT NULL default '0000-00-00 00:00:00' COMMENT 'Время транзакции',
		license_key    VARCHAR(255) NOT NULL default ''                    COMMENT 'Лицензионный ключ',
		download_url   VARCHAR(255) NOT NULL default ''                    COMMENT 'URL на скачивание продукта',
		PRIMARY KEY  (id),
		KEY trans_no (trans_no)
	)
	DEFAULT CHARACTER SET $wpdb->charset COLLATE $wpdb->collate;";

	dbDelta($sql);
}

create_payment_info_table();

Code of dbDelta: wp-admin/includes/upgrade.php VER 4.9.8

<?php
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 array $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 array $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 array $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.
					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
					);

					// 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;
}

Related Functions

From tag: sql

No comments
    Hello, !     Log In . Register