[wp-trac] [WordPress Trac] #30655: dbdelta(): issues when using backticks for reserved SQL keywords

[WordPress Trac]

#30655: dbdelta(): issues when using backticks for reserved SQL keywords
--------------------------+-----------------------------
 Reporter:  harmr         |      Owner:
     Type:  defect (bug)  |     Status:  new
 Priority:  normal        |  Milestone:  Awaiting Review
Component:  Database      |    Version:  4.0.1
 Severity:  normal        |   Keywords:
  Focuses:                |
--------------------------+-----------------------------
 Within my plugin "Leaflet Maps Marker"
 (http://wordpress.org/extend/plugins/leaflet-maps-marker/) I am using the
 following dbdelta()-statement within /inc/install-and-updates.php to
 create the tables I need for my plugin:

 {{{
 $table_name_layers = $wpdb->prefix.'leafletmapsmarker_layers';
 $sql_layers_table = "CREATE TABLE " . $table_name_layers . " (
         id int(6) unsigned NOT NULL AUTO_INCREMENT,
         name varchar(255) NOT NULL,
         basemap varchar(25) NOT NULL,
         layerzoom int(2) NOT NULL,
         mapwidth int(4) NOT NULL,
         mapwidthunit varchar(2) NOT NULL,
         mapheight int(4) NOT NULL,
         panel tinyint(1) NOT NULL,
         layerviewlat decimal(9,6) NOT NULL,
         layerviewlon decimal(9,6) NOT NULL,
         createdby varchar(30) NOT NULL,
         createdon datetime NOT NULL,
         updatedby varchar(30) DEFAULT NULL,
         updatedon datetime DEFAULT NULL,
         controlbox int(1) NOT NULL,
         overlays_custom int(1) NOT NULL,
         overlays_custom2 int(1) NOT NULL,
         overlays_custom3 int(1) NOT NULL,
         overlays_custom4 int(1) NOT NULL,
         wms tinyint(1) NOT NULL,
         wms2 tinyint(1) NOT NULL,
         wms3 tinyint(1) NOT NULL,
         wms4 tinyint(1) NOT NULL,
         wms5 tinyint(1) NOT NULL,
         wms6 tinyint(1) NOT NULL,
         wms7 tinyint(1) NOT NULL,
         wms8 tinyint(1) NOT NULL,
         wms9 tinyint(1) NOT NULL,
         wms10 tinyint(1) NOT NULL,
         listmarkers tinyint(1) NOT NULL,
         multi_layer_map tinyint(1) NOT NULL,
         multi_layer_map_list varchar(4000) DEFAULT NULL,
         address varchar(255) NOT NULL,
         clustering tinyint(1) unsigned NOT NULL,
         gpx_url varchar(2083) NOT NULL,
         gpx_panel tinyint(1) NOT NULL,
         PRIMARY KEY  (id)
 ) ENGINE=MyISAM DEFAULT CHARSET=utf8;";
 dbDelta($sql_layers_table);
 }}}

 I now found out that some mySQL-servers (clustered edition) have
 clustering as defined keyword (although it is not mentioned in their doc
 at https://dev.mysql.com/doc/mysqld-version-reference/en/mysqld-version-
 reference-reservedwords-5-5.html or https://dev.mysql.com/doc/mysqld-
 version-reference/en/mysqld-version-reference-reservedwords-5-6.html -
 have told the mySQLs guys about it, but they did not add it yet).

 To fix this for my plugin users, I added backticks to corresponding SQL
 statements, including the dbdelta()-function:

 {{{
 $table_name_layers = $wpdb->prefix.'leafletmapsmarker_layers';
 $sql_layers_table = "CREATE TABLE " . $table_name_layers . " (
         id int(6) unsigned NOT NULL AUTO_INCREMENT,
         name varchar(255) NOT NULL,
         basemap varchar(25) NOT NULL,
         layerzoom int(2) NOT NULL,
         mapwidth int(4) NOT NULL,
         mapwidthunit varchar(2) NOT NULL,
         mapheight int(4) NOT NULL,
         panel tinyint(1) NOT NULL,
         layerviewlat decimal(9,6) NOT NULL,
         layerviewlon decimal(9,6) NOT NULL,
         createdby varchar(30) NOT NULL,
         createdon datetime NOT NULL,
         updatedby varchar(30) DEFAULT NULL,
         updatedon datetime DEFAULT NULL,
         controlbox int(1) NOT NULL,
         overlays_custom int(1) NOT NULL,
         overlays_custom2 int(1) NOT NULL,
         overlays_custom3 int(1) NOT NULL,
         overlays_custom4 int(1) NOT NULL,
         wms tinyint(1) NOT NULL,
         wms2 tinyint(1) NOT NULL,
         wms3 tinyint(1) NOT NULL,
         wms4 tinyint(1) NOT NULL,
         wms5 tinyint(1) NOT NULL,
         wms6 tinyint(1) NOT NULL,
         wms7 tinyint(1) NOT NULL,
         wms8 tinyint(1) NOT NULL,
         wms9 tinyint(1) NOT NULL,
         wms10 tinyint(1) NOT NULL,
         listmarkers tinyint(1) NOT NULL,
         multi_layer_map tinyint(1) NOT NULL,
         multi_layer_map_list varchar(4000) DEFAULT NULL,
         address varchar(255) NOT NULL,
         `clustering` tinyint(1) unsigned NOT NULL,
         gpx_url varchar(2083) NOT NULL,
         gpx_panel tinyint(1) NOT NULL,
         PRIMARY KEY  (id)
 ) ENGINE=MyISAM DEFAULT CHARSET=utf8;";
 dbDelta($sql_layers_table);
 }}}

 This workaround is ok and works for my users (tested on several different
 installations) - it produces a PHP error (Undefined offset: 1) though in
 the PHP log files.

 I now read in the Codex
 (http://codex.wordpress.org/Creating_Tables_with_Plugins) the following:

 '''You must not use any apostrophes or backticks around field names.'''

 Which is really bad - I know that backticks work in dbdelta(), but it is
 officially not supported. Changing the column name to an unreserved name
 is not really an option, as dbdelta() does not support renaming existing
 columns and just adds a new column at the end of the table with the new
 name (as well as other dependencies I would not want to change because of
 this if there is another workaround).

 Is the info in the codex about backticks still valid? If yes, is there a
 way to officially support backticks within dbdelta() in one of the next
 releases, as currently I do not know another way how to solve this within
 my plugin.
 Thanks for any help!

 Robert

--
Ticket URL: <https://core.trac.wordpress.org/ticket/30655>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform