dbDelta() updates in 4.6

dbDelta() is a function which helps to modify the database based on specified SQL statements. In WordPress 4.6 this function has been updated to resolve some long standing bugs.

Normalized index definitions

Previously, dbDelta() compared the raw SQL data for index definitions with the output of SHOW INDEX FROM. This required a specific format so indices are not unnecessarily re-created. But this format wasn’t always ensured, until now. See [37583]:

  • dbDelta() now parses the raw index definition to extract the type, name and columns. With this data a normalized definition is build (#20263, #34873).
  • Core standardize now on uppercase types (#34871) and on ‘KEY’. ‘INDEX’ is only a synonym for ‘KEY’.
  • ASC and DESC definitions are currently unused by MySQL, dbDelta() ignores them now too (#34959).
  • Spaces in index definitions don’t create duplicate indices anymore (#34869).

Backtick all the things

Index names (#20263), index column names (#20263), names in index change queries (#20263) and other queries (#31679) are now escaped with backticks to improve compatibility with names which are reserved keywords.

Support for SPATIAL keys

dbDelta() already supported spatial fields (by virtue of not checking field types), so it’s nice to round that out with spatial key support, too. See #36948.

 

If you’re using dbDelta() in your plugin please test it with WordPress 4.6 and report any issues in the comments or on Trac.

There are still a few issues with dbDelta(), can you help us to fix them? 🔨

#4-6, #database, #dev-notes