How to get the SQL of the changes made to the database from an update or upgrade?

Question

When I update/upgrade a plugin, theme, or even the core of WordPress, it has an impact in the database, it changes many things. How do I keep track of it?

The problem is that I have a repository, all the changes made to the files are all correctly tracked, but I don't have any idea how to create an SQL file to store the database changes (alter, insert, update, drop, create, etc.).

I use a tool, which is Flyway, that picks the list of SQL files I have in the repository and applies the query of each file to the database, so my question is, how do I create or get an SQL query of all the changes made by the update/upgrade future of WordPress? This way all the team will be able just to make a pull from the repo and the changes I did will be propagated.

Thanks.

Answer 1

Log queries:

You could collect all the queries during core/plugin/theme upgrades to see what happens. Just follow these two steps:

1) You should add:

define( 'SAVEQUERIES', TRUE );

to your wp-config.php file to collect all queries during a page load into the $wpdb->queries array. Just remember to remove it afterwards.

2) Then you could log it into the sql.log file. Here's a simple example:

/**
 * Dump all database queries to the /wp-content/sql.log file.
 */
add_action( 'shutdown', function(){

    $file =  WP_CONTENT_DIR . '/sql.log'; // Edit this filepath to your needs.

    if( current_user_can( 'manage_options' ) 
         && file_exists( $file )
         && is_writeable( $file )
         && isset( $GLOBALS['wpdb']->queries )
     )
         file_put_contents( 
                $file, 
                date( 'c' ) . PHP_EOL .  print_r( $GLOBALS['wpdb']->queries, TRUE ), 
                FILE_APPEND 
         );

});

or use the query filter of the wpdb class to log only the INSERT, UPDATE and DELETE queries:

/**
 * Log the INSERT, UPDATE, DELETE database queries to the /wp-content/sql.log file.
 */
add_filter( 'query', function( $query ){
    if( FALSE !== stripos( $query, 'UPDATE ' )
        || FALSE !== stripos( $query, 'INSERT ' )
        || FALSE !== stripos( $query, 'DELETE ' )
     ) {
            $file =  WP_CONTENT_DIR . '/sql.log'; // Edit this filepath to your needs.  
            if( file_exists( $file ) && is_writeable( $file ) ) 
                file_put_contents( 
                    $file, 
                    date( 'c' ) . ' - ' . $query . PHP_EOL, 
                    FILE_APPEND | LOCK_EX 
                );            
    }
    return $query;
}, PHP_INT_MAX );

Core upgrade files:

For core upgrades these files might be of interest to you

• /wp-includes/version.php
• /wp-admin/includes/upgrade.php
• /wp-admin/includes/schema.php

In the file /wp-admin/includes/upgrade.php you can find the upgrade function: wp_upgrade() that calls the upgrade_all() function. It contains database upgrades for each version in terms of functions like upgrade_xxx()

For example:

    ...truncated...

    if ( $wp_current_db_version < 22422 )
            upgrade_350();

    if ( $wp_current_db_version < 25824 )
            upgrade_370();

    if ( $wp_current_db_version < 26148 )
            upgrade_372();

    if ( $wp_current_db_version < 26691 )
            upgrade_380();

    maybe_disable_link_manager();

    maybe_disable_automattic_widgets();

    update_option( 'db_version', $wp_db_version );
    update_option( 'db_upgraded', true );

I hope this helps.