dbDelta

Modifies the database based on specified SQL statements.

Description

(array) dbDelta( (string) $queries = '', (bool) $execute = true ); 

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

Returns (array)

Strings containing the results of the various update queries.

Parameters (2)

0. $queries — Optional. (string) => ''
The query to run. Can be multiple queries in an array, or a string of queries separated by semicolons. Default empty.
1. $execute — Optional. (bool) => true
Whether or not to execute the query right away. Default true.

Usage

  1. if ( !function_exists( 'dbDelta' ) ) { 
  2. require_once ABSPATH . '/wp-admin/includes/upgrade.php'; 
  3. $queries = ''; 
  4.  
  5. // Optional. Whether or not to execute the query right away. 
  6. // Default true. 
  7. $execute = true; 
  8.  
  9. // NOTICE! Understand what this does before running. 
  10. $result = dbDelta($queries, $execute); 
  11.  

Defined (1)

The function is defined in the following location(s).

/wp-admin/includes/upgrade.php  
  1. function dbDelta( $queries = '', $execute = true ) { 
  2. global $wpdb; 
  3.  
  4. if ( in_array( $queries, array( '', 'all', 'blog', 'global', 'ms_global' ), true ) ) 
  5. $queries = wp_get_db_schema( $queries ); 
  6.  
  7. // Separate individual queries into an array 
  8. if ( !is_array($queries) ) { 
  9. $queries = explode( ';', $queries ); 
  10. $queries = array_filter( $queries ); 
  11.  
  12. /** 
  13. * Filters the dbDelta SQL queries. 
  14. * @since 3.3.0 
  15. * @param array $queries An array of dbDelta SQL queries. 
  16. */ 
  17. $queries = apply_filters( 'dbdelta_queries', $queries ); 
  18.  
  19. $cqueries = array(); // Creation Queries 
  20. $iqueries = array(); // Insertion Queries 
  21. $for_update = array(); 
  22.  
  23. // Create a tablename index for an array ($cqueries) of queries 
  24. foreach ($queries as $qry) { 
  25. if ( preg_match( "|CREATE TABLE ([^ ]*)|", $qry, $matches ) ) { 
  26. $cqueries[ trim( $matches[1], '`' ) ] = $qry; 
  27. $for_update[$matches[1]] = 'Created table '.$matches[1]; 
  28. } elseif ( preg_match( "|CREATE DATABASE ([^ ]*)|", $qry, $matches ) ) { 
  29. array_unshift( $cqueries, $qry ); 
  30. } elseif ( preg_match( "|INSERT INTO ([^ ]*)|", $qry, $matches ) ) { 
  31. $iqueries[] = $qry; 
  32. } elseif ( preg_match( "|UPDATE ([^ ]*)|", $qry, $matches ) ) { 
  33. $iqueries[] = $qry; 
  34. } else { 
  35. // Unrecognized query type 
  36.  
  37. /** 
  38. * Filters the dbDelta SQL queries for creating tables and/or databases. 
  39. * Queries filterable via this hook contain "CREATE TABLE" or "CREATE DATABASE". 
  40. * @since 3.3.0 
  41. * @param array $cqueries An array of dbDelta create SQL queries. 
  42. */ 
  43. $cqueries = apply_filters( 'dbdelta_create_queries', $cqueries ); 
  44.  
  45. /** 
  46. * Filters the dbDelta SQL queries for inserting or updating. 
  47. * Queries filterable via this hook contain "INSERT INTO" or "UPDATE". 
  48. * @since 3.3.0 
  49. * @param array $iqueries An array of dbDelta insert or update SQL queries. 
  50. */ 
  51. $iqueries = apply_filters( 'dbdelta_insert_queries', $iqueries ); 
  52.  
  53. $text_fields = array( 'tinytext', 'text', 'mediumtext', 'longtext' ); 
  54. $blob_fields = array( 'tinyblob', 'blob', 'mediumblob', 'longblob' ); 
  55.  
  56. $global_tables = $wpdb->tables( 'global' ); 
  57. foreach ( $cqueries as $table => $qry ) { 
  58. // Upgrade global tables only for the main site. Don't upgrade at all if conditions are not optimal. 
  59. if ( in_array( $table, $global_tables ) && ! wp_should_upgrade_global_tables() ) { 
  60. unset( $cqueries[ $table ], $for_update[ $table ] ); 
  61. continue; 
  62.  
  63. // Fetch the table column structure from the database 
  64. $suppress = $wpdb->suppress_errors(); 
  65. $tablefields = $wpdb->get_results("DESCRIBE {$table};"); 
  66. $wpdb->suppress_errors( $suppress ); 
  67.  
  68. if ( ! $tablefields ) 
  69. continue; 
  70.  
  71. // Clear the field and index arrays. 
  72. $cfields = $indices = array(); 
  73.  
  74. // Get all of the field names in the query from between the parentheses. 
  75. preg_match("|\((.*)\)|ms", $qry, $match2); 
  76. $qryline = trim($match2[1]); 
  77.  
  78. // Separate field lines into an array. 
  79. $flds = explode("\n", $qryline); 
  80.  
  81. // For every field line specified in the query. 
  82. foreach ( $flds as $fld ) { 
  83. $fld = trim( $fld, " \t\n\r\0\x0B, " ); // Default trim characters, plus ', '. 
  84.  
  85. // Extract the field name. 
  86. preg_match( '|^([^ ]*)|', $fld, $fvals ); 
  87. $fieldname = trim( $fvals[1], '`' ); 
  88. $fieldname_lowercased = strtolower( $fieldname ); 
  89.  
  90. // Verify the found field name. 
  91. $validfield = true; 
  92. switch ( $fieldname_lowercased ) { 
  93. case '': 
  94. case 'primary': 
  95. case 'index': 
  96. case 'fulltext': 
  97. case 'unique': 
  98. case 'key': 
  99. case 'spatial': 
  100. $validfield = false; 
  101.  
  102. /** 
  103. * Normalize the index definition. 
  104. * This is done so the definition can be compared against the result of a 
  105. * `SHOW INDEX FROM $table_name` query which returns the current table 
  106. * index information. 
  107. */ 
  108.  
  109. // Extract type, name and columns from the definition. 
  110. preg_match( 
  111. '/^' 
  112. . '(?P<index_type>' // 1) Type of the index. 
  113. . 'PRIMARY\s+KEY|(?:UNIQUE|FULLTEXT|SPATIAL)\s+(?:KEY|INDEX)|KEY|INDEX' 
  114. . ')' 
  115. . '\s+' // Followed by at least one white space character. 
  116. . '(?:' // Name of the index. Optional if type is PRIMARY KEY. 
  117. . '`?' // Name can be escaped with a backtick. 
  118. . '(?P<index_name>' // 2) Name of the index. 
  119. . '(?:[0-9a-zA-Z$_-]|[\xC2-\xDF][\x80-\xBF])+' 
  120. . ')' 
  121. . '`?' // Name can be escaped with a backtick. 
  122. . '\s+' // Followed by at least one white space character. 
  123. . ')*' 
  124. . '\(' // Opening bracket for the columns. 
  125. . '(?P<index_columns>' 
  126. . '.+?' // 3) Column names, index prefixes, and orders. 
  127. . ')' 
  128. . '\)' // Closing bracket for the columns. 
  129. . '$/im',  
  130. $fld,  
  131. $index_matches 
  132. ); 
  133.  
  134. // Uppercase the index type and normalize space characters. 
  135. $index_type = strtoupper( preg_replace( '/\s+/', ' ', trim( $index_matches['index_type'] ) ) ); 
  136.  
  137. // 'INDEX' is a synonym for 'KEY', standardize on 'KEY'. 
  138. $index_type = str_replace( 'INDEX', 'KEY', $index_type ); 
  139.  
  140. // Escape the index name with backticks. An index for a primary key has no name. 
  141. $index_name = ( 'PRIMARY KEY' === $index_type ) ? '' : '`' . strtolower( $index_matches['index_name'] ) . '`'; 
  142.  
  143. // Parse the columns. Multiple columns are separated by a comma. 
  144. $index_columns = array_map( 'trim', explode( ', ', $index_matches['index_columns'] ) ); 
  145.  
  146. // Normalize columns. 
  147. foreach ( $index_columns as &$index_column ) { 
  148. // Extract column name and number of indexed characters (sub_part). 
  149. preg_match( 
  150. '/' 
  151. . '`?' // Name can be escaped with a backtick. 
  152. . '(?P<column_name>' // 1) Name of the column. 
  153. . '(?:[0-9a-zA-Z$_-]|[\xC2-\xDF][\x80-\xBF])+' 
  154. . ')' 
  155. . '`?' // Name can be escaped with a backtick. 
  156. . '(?:' // Optional sub part. 
  157. . '\s*' // Optional white space character between name and opening bracket. 
  158. . '\(' // Opening bracket for the sub part. 
  159. . '\s*' // Optional white space character after opening bracket. 
  160. . '(?P<sub_part>' 
  161. . '\d+' // 2) Number of indexed characters. 
  162. . ')' 
  163. . '\s*' // Optional white space character before closing bracket. 
  164. . '\)' // Closing bracket for the sub part. 
  165. . ')?' 
  166. . '/',  
  167. $index_column,  
  168. $index_column_matches 
  169. ); 
  170.  
  171. // Escape the column name with backticks. 
  172. $index_column = '`' . $index_column_matches['column_name'] . '`'; 
  173.  
  174. // Append the optional sup part with the number of indexed characters. 
  175. if ( isset( $index_column_matches['sub_part'] ) ) { 
  176. $index_column .= '(' . $index_column_matches['sub_part'] . ')'; 
  177.  
  178. // Build the normalized index definition and add it to the list of indices. 
  179. $indices[] = "{$index_type} {$index_name} (" . implode( ', ', $index_columns ) . ")"; 
  180.  
  181. // Destroy no longer needed variables. 
  182. unset( $index_column, $index_column_matches, $index_matches, $index_type, $index_name, $index_columns ); 
  183.  
  184. break; 
  185.  
  186. // If it's a valid field, add it to the field array. 
  187. if ( $validfield ) { 
  188. $cfields[ $fieldname_lowercased ] = $fld; 
  189.  
  190. // For every field in the table. 
  191. foreach ( $tablefields as $tablefield ) { 
  192. $tablefield_field_lowercased = strtolower( $tablefield->Field ); 
  193. $tablefield_type_lowercased = strtolower( $tablefield->Type ); 
  194.  
  195. // If the table field exists in the field array ... 
  196. if ( array_key_exists( $tablefield_field_lowercased, $cfields ) ) { 
  197.  
  198. // Get the field type from the query. 
  199. preg_match( '|`?' . $tablefield->Field . '`? ([^ ]*( unsigned)?)|i', $cfields[ $tablefield_field_lowercased ], $matches ); 
  200. $fieldtype = $matches[1]; 
  201. $fieldtype_lowercased = strtolower( $fieldtype ); 
  202.  
  203. // Is actual field type different from the field type in query? 
  204. if ($tablefield->Type != $fieldtype) { 
  205. $do_change = true; 
  206. if ( in_array( $fieldtype_lowercased, $text_fields ) && in_array( $tablefield_type_lowercased, $text_fields ) ) { 
  207. if ( array_search( $fieldtype_lowercased, $text_fields ) < array_search( $tablefield_type_lowercased, $text_fields ) ) { 
  208. $do_change = false; 
  209.  
  210. if ( in_array( $fieldtype_lowercased, $blob_fields ) && in_array( $tablefield_type_lowercased, $blob_fields ) ) { 
  211. if ( array_search( $fieldtype_lowercased, $blob_fields ) < array_search( $tablefield_type_lowercased, $blob_fields ) ) { 
  212. $do_change = false; 
  213.  
  214. if ( $do_change ) { 
  215. // Add a query to change the column type. 
  216. $cqueries[] = "ALTER TABLE {$table} CHANGE COLUMN `{$tablefield->Field}` " . $cfields[ $tablefield_field_lowercased ]; 
  217. $for_update[$table.'.'.$tablefield->Field] = "Changed type of {$table}.{$tablefield->Field} from {$tablefield->Type} to {$fieldtype}"; 
  218.  
  219. // Get the default value from the array. 
  220. if ( preg_match( "| DEFAULT '(.*?)'|i", $cfields[ $tablefield_field_lowercased ], $matches ) ) { 
  221. $default_value = $matches[1]; 
  222. if ($tablefield->Default != $default_value) { 
  223. // Add a query to change the column's default value 
  224. $cqueries[] = "ALTER TABLE {$table} ALTER COLUMN `{$tablefield->Field}` SET DEFAULT '{$default_value}'"; 
  225. $for_update[$table.'.'.$tablefield->Field] = "Changed default value of {$table}.{$tablefield->Field} from {$tablefield->Default} to {$default_value}"; 
  226.  
  227. // Remove the field from the array (so it's not added). 
  228. unset( $cfields[ $tablefield_field_lowercased ] ); 
  229. } else { 
  230. // This field exists in the table, but not in the creation queries? 
  231.  
  232. // For every remaining field specified for the table. 
  233. foreach ($cfields as $fieldname => $fielddef) { 
  234. // Push a query line into $cqueries that adds the field to that table. 
  235. $cqueries[] = "ALTER TABLE {$table} ADD COLUMN $fielddef"; 
  236. $for_update[$table.'.'.$fieldname] = 'Added column '.$table.'.'.$fieldname; 
  237.  
  238. // Index stuff goes here. Fetch the table index structure from the database. 
  239. $tableindices = $wpdb->get_results("SHOW INDEX FROM {$table};"); 
  240.  
  241. if ($tableindices) { 
  242. // Clear the index array. 
  243. $index_ary = array(); 
  244.  
  245. // For every index in the table. 
  246. foreach ($tableindices as $tableindex) { 
  247.  
  248. // Add the index to the index data array. 
  249. $keyname = strtolower( $tableindex->Key_name ); 
  250. $index_ary[$keyname]['columns'][] = array('fieldname' => $tableindex->Column_name, 'subpart' => $tableindex->Sub_part); 
  251. $index_ary[$keyname]['unique'] = ($tableindex->Non_unique == 0)?true:false; 
  252. $index_ary[$keyname]['index_type'] = $tableindex->Index_type; 
  253.  
  254. // For each actual index in the index array. 
  255. foreach ($index_ary as $index_name => $index_data) { 
  256.  
  257. // Build a create string to compare to the query. 
  258. $index_string = ''; 
  259. if ($index_name == 'primary') { 
  260. $index_string .= 'PRIMARY '; 
  261. } elseif ( $index_data['unique'] ) { 
  262. $index_string .= 'UNIQUE '; 
  263. if ( 'FULLTEXT' === strtoupper( $index_data['index_type'] ) ) { 
  264. $index_string .= 'FULLTEXT '; 
  265. if ( 'SPATIAL' === strtoupper( $index_data['index_type'] ) ) { 
  266. $index_string .= 'SPATIAL '; 
  267. $index_string .= 'KEY '; 
  268. if ( 'primary' !== $index_name ) { 
  269. $index_string .= '`' . $index_name . '`'; 
  270. $index_columns = ''; 
  271.  
  272. // For each column in the index. 
  273. foreach ($index_data['columns'] as $column_data) { 
  274. if ( $index_columns != '' ) { 
  275. $index_columns .= ', '; 
  276.  
  277. // Add the field to the column list string. 
  278. $index_columns .= '`' . $column_data['fieldname'] . '`'; 
  279. if ($column_data['subpart'] != '') { 
  280. $index_columns .= '('.$column_data['subpart'].')'; 
  281.  
  282. // The alternative index string doesn't care about subparts 
  283. $alt_index_columns = preg_replace( '/\([^)]*\)/', '', $index_columns ); 
  284.  
  285. // Add the column list to the index create string. 
  286. $index_strings = array( 
  287. "$index_string ($index_columns)",  
  288. "$index_string ($alt_index_columns)",  
  289. ); 
  290.  
  291. foreach ( $index_strings as $index_string ) { 
  292. if ( ! ( ( $aindex = array_search( $index_string, $indices ) ) === false ) ) { 
  293. unset( $indices[ $aindex ] ); 
  294. break; 
  295.  
  296. // For every remaining index specified for the table. 
  297. foreach ( (array) $indices as $index ) { 
  298. // Push a query line into $cqueries that adds the index to that table. 
  299. $cqueries[] = "ALTER TABLE {$table} ADD $index"; 
  300. $for_update[] = 'Added index ' . $table . ' ' . $index; 
  301.  
  302. // Remove the original table creation query from processing. 
  303. unset( $cqueries[ $table ], $for_update[ $table ] ); 
  304.  
  305. $allqueries = array_merge($cqueries, $iqueries); 
  306. if ($execute) { 
  307. foreach ($allqueries as $query) { 
  308. $wpdb->query($query); 
  309.  
  310. return $for_update;