[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        |   Keywords:
  Focuses:  multisite     |
--------------------------+-----------------------------
 WooCommerce uses a query on `information_schema.table_constraints` to
 determine whether it needs to add a foreign key constraint to one of its
 tables.

 {{{
 SELECT
   COUNT(*) AS fk_count
 FROM
   information_schema.TABLE_CONSTRAINTS
 WHERE
   CONSTRAINT_SCHEMA = ''
   AND CONSTRAINT_NAME = 'fk_wp_12345_wc_download_log_permission_id'
   AND CONSTRAINT_TYPE = 'FOREIGN KEY'
   AND TABLE_NAME = 'wp_12345_wc_download_log'
 }}}

 `$wpdb->get_table_from_query` returns `information_schema` which is not a
 table, it is a database containing the table `TABLE_CONSTRAINTS`. So it
 seems there is room to improve this method to extract the table name in
 cases where it is preceded by a database name. However, this is not the
 goal of this ticket. In the specific case of `information_schema` tables,
 we're more interested in the table referenced in the `WHERE` clause.

 My use case involves a `wpdb` drop-in, WordPress.com's hyperdb, which was
 the original source of this method when it was added in [30345]. We use
 hyperdb to map queries to database servers using table names as map keys.
 Given a table like `wp_12345_posts` we connect to the right database.

 The database `information_schema` exists in all database servers. When
 WooCommerce queries the table `table_constraints` it's looking for
 information about the table `wp_12345_wc_download_log`. To route the query
 to the appropriate database server, we are interested in this table name.

 I would propose adding this before the first `preg_match` in
 `get_table_from_query`:

 {{{
                 // SELECT FROM information_schema.* WHERE TABLE_NAME =
 'wp_12345_foo'
                 if ( preg_match('/^\s*'
                                 .
 'SELECT.*?\s+FROM\s+`?information_schema`?\.'
                                 .
 '.*\s+TABLE_NAME\s*=\s*["\']([\w-]+)["\']/is', $q, $maybe) )
                         return $maybe[1];
 }}}

 This returns `wp_12345_wc_download_log` which allows us to route the query
 to the correct database server.

 I am able to patch WordPress.com's drop-in to check this pattern before
 calling the parent method in core's `wpdb`. So there is no need to rush on
 our behalf.

 Does anyone know of a use case that relies on the existing implementation?

 It might be argued that a caller of this function would expect the return
 to be `TABLE_CONSTRAINTS` in this case. However, I was unable to find any
 tickets requesting a fix for the current behavior. I believe the best fix
 would be to return the table name from the `WHERE` clause.

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