WordPress Db Codeception module - 06

I’ve written an acceptance test like

$I = new AcceptanceTester($scenario);
$I->wantTo('activate the Route Pages plugin with no permalink structure set and not be allowed to');

// set to no permalink structure
$I->haveOptionInDatabase('permalink_structure', '');



which is relying on my WP Browser module for Codeception to make my life easier.
I got the following error

There was 1 error:

1) Failed to activate the route pages plugin with no permalink structure set and not be allowed to in ShouldNotActivateIfPermalinkStructureIsNotSetCept (/Users/Luca/Dropbox/Developer/WebDeveloper/websites/wp-routing/wp-content/plugins/route-pages/tests/acceptance/ShouldNotActivateIfPermalinkStructureIsNotSetCept.php)
Couldn't have option in database "permalink_structure","":
PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'permalink_structure' for key 'option_name'

Scenario Steps:
1. I have option in database "permalink_structure",""

which means, in brief terms, that the permalink_structure option is there already and attempting its insertion in the database again is not good manners.
This is a tester error, a little waste of time, that will force me to either create a different SQL dump to be used by the WPDb module, an extension of Codecpetion original Db module or use some ad hoc access to the database to update the entry.
But when I wrote the statement above I did mean

In this test I do not care if the permalink_structure is there already or not: either create or update it and set it to this value.

Under the hood

Methods like haveOptionInDatabase are just wrappers around Db module haveInDatabase method and source reading yields a call stack that ends in a query compiled by the \Codeception\Lib\Driver\MySql::insert method into

INSERT INTO wp_options (option_name, option_value) VALUES ('permamlink_structure', '');

that will make the database refuse the query if the row is there already.

Extending the database driver

When the Db module is used its create method will be called and that, in turn, will return a new instance of the appropriate database driver using the dsn configuration parameter to discriminate.
In my specific case this means that an instance of \Codeception\Lib\Driver\MySql driver is returned and I have extended MySql driver to implement an insertOrUpdate method that will output, in the permalink_structure case above, a prepared statement like

INSERT INTO `wp_options` (`option_name`, `option_value`, `autoload`) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE option_name="permalink_structure", option_value="", autoload="yes"

which will take care of updating the option in the database if there already.

Using the extension in the module

After this modification I’ve been able to override Codeception built-in Db module haveInDatabase method to allow some configuration-based flexibility like

public function haveInDatabase($table, array $data)
    $this->debugSection('Configuration', sprintf('Update setting set to %s', $this->config['update']));

    if (isset($this->config['update']) and $this->config['update']) {
        return $this->haveOrUpdateInDatabase($table, $data);
    return parent::haveInDatabase($table, $data);

public function haveOrUpdateInDatabase($table, array $data)
    $query = $this->driver->insertOrUpdate($table, $data);
    $this->debugSection('Query', $query);

    $sth = $this->driver->getDbh()->prepare($query);
    if (!$sth) {
        $this->fail("Query '$query' can't be executed.");
    $i = 1;
    foreach ($data as $val) {
        $sth->bindValue($i, $val);
    $res = $sth->execute();
    if (!$res) {
        $this->fail(sprintf("Record with %s couldn't be inserted into %s", json_encode($data), $table));

the udpate optional configuration setting is set to true by default. Running the same exact test case from above will not generate any error this time.

Packages updated

I’ve updated the Packagist-hosted composer package to include this latest addition to WordPress specific Codeception module alongside with some much needed commenting and README update.