6

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

  1. Write the response of dbDelta into the log
  2. 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:

  1. Is it possible to create tables during unit testing via PHPUnit?
  2. 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.

  • How could you handle class Test_Db during installation? and I test in PHP 7 and see your function test_db is the same name with your class, it will trigger error during activation. – Jevuska Mar 10 '16 at 8:47
  • @Jevuska Case is different, wouldn't function and class names then be seen as different? – BillK Mar 10 '16 at 9:24
  • @Jevuska: ^This. As BillK said, it's not a problem. But I updated the code above (changed the method name) so there would not be any confusion. – Paolo Mar 11 '16 at 7:06
10

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.

|improve this answer|||||
  • Thanks for the response. I modified my code as you described above and used TEMPORARY. However, now my sql is not returning anything. The sql query is returning an empty array (inside the $results variable). So it looks like the table wasn't created in the first place? – Paolo Mar 11 '16 at 7:09
  • Hmmm, checked the logs, I got this: code <div id="error"><p class="wpdberror"><strong>WordPress database error:</strong> [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near &#039;TEMPORARY TABLES LIKE &#039;%&#039;&#03 9; at line 1]<br /><code>SHOW TEMPORARY TABLES LIKE &#039;%&#039;</code></p></div> – Paolo Mar 11 '16 at 7:48
  • 1
    I guess the 'SHOW TEMPORARY' is not valid and generates an error. Anyway, to validate that the temporary table does exist, I thought outside the box and I inserted a row right after creating the table. I did a SELECT in the test_db_stuff method and it returned the row I inserted. In other words, the temporary table exists albeit we have no way to SHOW it (I can't believe this? I think I'm wrong...)? Thanks for all the help. I'll accept this answer. – Paolo Mar 11 '16 at 7:59
  • 1
    @Paolo — I was just assuming the MySQL would support showing temporary tables, but I guess it doesn't. I'm glad you found a work-around. I'll add that to my answer. – J.D. Mar 11 '16 at 13:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.