[wp-trac] [WordPress Trac] #50789: Improve WPDB logic around information_schema

[WordPress Trac]

#50789: Improve WPDB logic around information_schema
--------------------------+------------------------------
 Reporter:  andy          |       Owner:  (none)
     Type:  defect (bug)  |      Status:  new
 Priority:  normal        |   Milestone:  Awaiting Review
Component:  Database      |     Version:  trunk
 Severity:  normal        |  Resolution:
 Keywords:                |     Focuses:  multisite
--------------------------+------------------------------

Comment (by johnjamesjacoby):

 Interesting. (Also, hey @andy!)

 It's not common, but querying the `information_schema` database is a valid
 use of `WPDB::query()`. I expect for `get_table_from_query()` to return
 the first table being queried, not the `TABLE_NAME` - it seems too clever.

 The `get_table_from_query()` method documentation says:

 {{{
         /**
          * Finds the first table name referenced in a query.
          *
 }}}

 ...emphasis **first**. 😕

 ----

 I probably wouldn't query `information_schema` directly in this situation
 for this reason. WooCommerce ''should'' be storing a version as an option
 for each of its custom tables, and the values of those options should
 dictate what SQL is generated. (This is also the approach I've taken in
 the [https://github.com/berlindb/core BerlinDB] project.)

 If table versions are unknown, I would reverse engineer them by calling:

 {{{
         $prefix = $wpdb->get_blog_prefix();
         $table  = "{$prefix}wc_download_log";
         $query  = "SHOW CREATE TABLE {$table}";
         $create = $wpdb->query( $query );
 }}}

 ...and comparing those results to known database table schema changes.

 ----

 I do think if this idea made its way into `get_table_from_query()` that
 probably not very many people would notice. But I still don't think it's a
 good idea. That said, I'm attaching a patch imminently so that others can
 test it out and draw their own conclusions. ❤️

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/50789#comment:1>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform