dbDelta doesn't support SPATIAL KEY syntax

[stuporglue]

dbDelta can create spatial indexes on the initial CREATE TABLE call, but subsequent calls to dbDelta don't recognize the existing spatial index, resulting in a MySQL error indicating that it's trying add a new column.

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SPATIAL KEY (meta_value)' at line 1
ALTER TABLE wp_index_test ADD COLUMN SPATIAL KEY (meta_value)

MySQL has supported spatial columns and indexes since version 5.4. The syntax has remained the same since 5.4.

• ​Create Table in v5.4
• ​Create Table in v5.7

Adding spatial index support to dbDelta would allow developers to create and use tables with spatial columns while continuing to use dbDelta.

SPATIAL index syntax is the same as FULLTEXT support which dbDelta already supports. As such, adding support is a simple one line change.

Note: dbDelta works correctly for creating spatial columns. It's just indexes that it chokes on.

[stuporglue]

Test file

[stuporglue]

My comment about it being a one line fix was wrong. Adding the one line gets rid of the error, but it adds an additional index every time dbDelta is run.

[pento]

Thanks for the feature request, @stuporglue!

It's cool to see that you're writing stuff for WordPress involving spatial data, that's always been something I've enjoyed. :-)

This is a very small change to dbDelta(), so I think we can go ahead with it.

[pento]

In 37574:

Database: Add support for SPATIAL keys to dbDelta().

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.

Fixes #36948.

[stuporglue]

The previous test file did not give the spatial index a name which caused it to get re-added each time dbDelta ran.

[stuporglue]

This patch allows spatial indexes to be added with dbDelta

[stuporglue]

Wow that was fast @pento!

Thanks!

[pento]

No problem!

It's also worth noting, in your test script, dbDelta() expects column types to be lowercase, so GEOMETRYCOLLECTION should be geometrycollection, just in case you run into that problem with your code. :-)

[nickciske]

So this made it into 4.6, right? I didn't see stuporglue in the props list...? Which is a bummer as its his first ticket/contribution...

[pento]

Thanks for noting this, @nickciske!

@stuporglue wasn't automatically added to the props list, as I managed to fix this ticket before he uploaded the patch. You're right that he should've been though, I've updated the props list manually in ​[meta3826]. (This API is pretty heavily cached, so it may take up to 12 hours before you see @stuporglue appearing in the Credits list.)