Wordpress Unit Testing - Cannot Create Tables

Question

Wordpress Unit Testing - Cannot Create Tables

Paolo

2022年4月21日 21:07

I'm using PHPUnit to Unit Test my WP plugin on top of the WP testing suite. Everything works fine except that when I try to create a table via the setUp method, the table doesn't get created.

Here's my code:

class Test_Db extends PAO_UnitTestCase {

function setUp() {

    parent::setUp();

    global $wpdb;

    $sql = "CREATE TABLE {$wpdb-prefix}mytest (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            column_1 varchar(255) NOT NULL,
            PRIMARY KEY  (id)
        ) ENGINE=MyISAM";

    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    $this-el(dbDelta($sql));

}

function tearDown() {
    parent::tearDown();
}

function test_db_stuff(){

    global $wpdb;

    $sql = "SHOW TABLES LIKE '%'";
    $results = $wpdb-get_results($sql);

    foreach($results as $index = $value) {
        foreach($value as $tableName) {
            $this-el($tableName);
        }
    }
}

}

The class PAO_UnitTestCase is simply an extension of the WP_UnitTestCase class which contains one method - the el method - which simply logs whatever into a file of my choosing.

As you can see, I used the el method to

Write the response of dbDelta into the log
Write the names of all existing tables into the log

According to the log, dbDelta was able to create the table but it's not being listed down as part of the existing tables.

So my questions are:

Is it possible to create tables during unit testing via PHPUnit?
If so, what am I doing wrong?

Hope someone can help me.

Thanks!

Update: As seen in the discussion and accepted answer below, the tables do get created but as TEMPORARY tables. You won't be able to see the tables via SHOW TABLES though but you check its existence by inserting a row and then trying to retrieve the row.

Topic unit-tests table wpdb database Wordpress

Category Web

Enrique · Accepted Answer · 2022年4月21日 21:07

Do not extend WP_UnitTestCase. Extend PHPUnit\Framework\TestCase instead. Changes made to tables structures won't be persisted (see here). But you will have to manually remove all the tables that were created during the test. You can look at this example. The repository is old but the code still works.

J.D. · Accepted Answer · 2016年9月10日 15:23

You've just discovered an important feature of the core test suite: it forces any tables created during the test to be temporary tables.

If you look in the WP_UnitTestCase::setUp() method you'll see that it calls a method called start_transaction(). That start_transaction() method starts a MySQL database transaction:

        function start_transaction() {
                global $wpdb;
                $wpdb->query( 'SET autocommit = 0;' );
                $wpdb->query( 'START TRANSACTION;' );
                add_filter( 'query', array( $this, '_create_temporary_tables' ) );
                add_filter( 'query', array( $this, '_drop_temporary_tables' ) );
        }

It does this so that any changes that your test makes in the database can just be rolled back afterward in the tearDown() method. This means that each test starts with a clean WordPress database, untainted by the prior tests.

However, you'll note that start_transaction() also hooks two methods to the 'query' filter: _create_temporary_tables and _drop_temporary_tables. If you look at the source of these methods, you'll see that they cause any CREATE or DROP table queries to be for temporary tables instead:

        function _create_temporary_tables( $query ) {
                if ( 'CREATE TABLE' === substr( trim( $query ), 0, 12 ) )
                        return substr_replace( trim( $query ), 'CREATE TEMPORARY TABLE', 0, 12 );
                return $query;
        }

        function _drop_temporary_tables( $query ) {
                if ( 'DROP TABLE' === substr( trim( $query ), 0, 10 ) )
                        return substr_replace( trim( $query ), 'DROP TEMPORARY TABLE', 0, 10 );
                return $query;
        }

The 'query' filter is applied to all database queries passed through $wpdb->query(), which dbDelta() uses. So that means when your tables are created, they are created as temporary tables.

So in order to list those tables, ~~I think you'd have to show temporary tables instead: $sql = "SHOW TEMPORARY TABLES LIKE '%'";~~

Update: MySQL doesn't allow you to list the temporary tables like you can with the regular tables. You will have to use another method of checking if the table exists, like attempting to insert a row.

But why does the unit test case require temporary tables to be created in the first place? Remember that we are using MySQL transactions so that the database stays clean. We don't want to ever commit the transactions though, we want to always roll them back at the end of the test. But there are some MySQL statements that will cause an implicit commit. Among these are, you guessed it, CREATE TABLE and DROP TABLE. However, per the MySQL docs:

CREATE TABLE and DROP TABLE statements do not commit a transaction if the TEMPORARY keyword is used.

So to avoid an implicit commit, the test case forces any tables created or dropped to be temporary tables.

This is not a well documented feature, but once you understand what is going on it should be pretty easy to work with.

Wordpress Unit Testing - Cannot Create Tables

About