PHPackages                             srathbone/behat-sql-extension - PHPackages - PHPackages  [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register)

1. [Directory](/)
2. /
3. [Testing & Quality](/categories/testing)
4. /
5. srathbone/behat-sql-extension

ActiveBehat-extension[Testing & Quality](/categories/testing)

srathbone/behat-sql-extension
=============================

SQL/PDO extension that facilitates fixture data creation on the fly for Behat

8.5.1(5y ago)011MITPHPPHP ~5.5|~7.0

Since Oct 4Pushed 4y agoCompare

[ Source](https://github.com/srathbone/behat-sql-extension)[ Packagist](https://packagist.org/packages/srathbone/behat-sql-extension)[ RSS](/packages/srathbone-behat-sql-extension/feed)WikiDiscussions master Synced 1w ago

READMEChangelogDependencies (2)Versions (150)Used By (0)

Behat SQL Extension [ ![Codeship Status for forceedge01/behat-sql-extension](https://camo.githubusercontent.com/35e8b01b7ff7952b37cc9806ddd286715704e6f303f7fa55f1b84349fba88e96/68747470733a2f2f6170702e636f6465736869702e636f6d2f70726f6a656374732f32373832643737302d396335362d303133352d643939632d3365303236336236323430342f7374617475733f6272616e63683d62656861742f332e78)](https://app.codeship.com/projects/252932)
=========================================================================================================================================================================================================================================================================================================================================================================================================================

[](#behat-sql-extension--)

Generic library: Provides easy data manipulation with any PDO enabled database for Behat. Core features:

- Out of the box step definitions for simple db interactions.
- Auto-fills required fields in a table, freeing you from the schackles of required data.
- Maintain SQL history for all queries executed for clean up later on.
- Provides an api to replace keywords in strings such as URLs, allowing easy navigation to dynamic URLs.
- Provides easy access to the entire last record manipulated from the keystore.
- An API for advanced integration.
- Advanced query internal resolutions for quick setup.

You can find usage examples in the features/test.feature file.

New Features in version 8:
--------------------------

[](#new-features-in-version-8)

- Custom exceptions.

New Feature in Minor:
---------------------

[](#new-feature-in-minor)

- 1: OBDC Support added.
- 2: Count API call added.
- 3: Read all columns of a table. Dev improvements.
- 4: Ability to register external database providers.

Patch fix:
----------

[](#patch-fix)

- 1: LastInsertId fetch adjusted to work correctly with the postgresql driver.

Installation
------------

[](#installation)

require with composer

```
composer require "genesis/behat-sql-extension"
```

Instantiation
-------------

[](#instantiation)

Instantiating the sql extension in your FeatureContext class.

```
use Genesis\SQLExtension\Context;

$databaseParams = [
    'engine' => 'mssql', // The database engine to use, mysql, mssql, pgsql.
    'schema' => 'dbo', // The database schema. Optional.
    'dbname' => 'MyDB', // The database name.
    'prefix' => 'dev_', // You can provide a database prefix which could be different based on the environment.
    'host' => '192.168.0.1', // The database host.
    'port' => '9876', // The database port.
    'username' => 'db_username', // The username for the database.
    'password' => 'db_password' // The password for the database.
];

$this->sqlContext = new Context\API(
    new Context\DBManager(
      new Context\DatabaseProviders\Factory(),
      $databaseParams
    ),
    new Context\SQLBuilder(),
    new Context\LocalKeyStore(),
    new Context\SQLHistory()
);
```

Please note that the Context\\SQLHistory parameter is optional and you may leave it.

Setup
-----

[](#setup)

After composer has installed the extension you would need to setup the connection details. This can be done in 2 ways:

\###1. Behat.yml

In addition to the usual mink-extension parameters, you can pass in a `connection_details` parameter as follows:

```
default:
    extensions:
        ...
        Genesis\SQLExtension\Extension:
          # Database connection details
          connection_details:
            engine: pgsql
            host: 127.0.0.1
            port: 3306
            schema: ...
            dbname: ...
            username: ...
            password: ...
            dbprefix: ...
          # Keywords to be used with the SQL extension steps
          keywords:
            ...
          notQuotableKeywords:
            ...
          # 1 for max debug, 2 dumps only SQL queries executed.
          debug: false
```

In the above example, the `keywords` section provides injection of keywords. For example you can have:

```
default:
    extensions:
        ...:
          ...
          keywords:
            qwerty: thisisthehashofthepassword
```

This will make the `qwerty` keyword usable as follows:

```
Given I have a "user" where "email:its.inevitable@hotmail.com,password_hash:{qwerty}"
```

Note the use of `{qwerty}` keyword. `{qwerty}` will be replaced with `thisisthehashofthepassword`.

The 'notQuotableKeywords' provide a way to specify mysql functions you do not wish to put in quotes when the SQLContext generates the SQL query. These are expected to be regular expressions but without the delimiters. The defaults that are already set are:

```
$keywords = [
  'true',
  'false',
  'null',
  'NOW\(\)',
  'COUNT\(.*\)',
  'MAX\(.*\)',
  'DATE\(.*\)',
  '\d+'
];
```

To add a non-quotable word through the use of the API only, use the line below:

```
$_SESSION['behat']['GenesisSqlExtension']['notQuotableKeywords'][] = 'YOUR-REGEX-GOES-HERE';

```

Note: The `schema` is a very important parameter for the SQLContext, if you are working with multiple databases don't set a fixed schema. To reference a table from another database simply prefix that databases' name as per the sql convention and it will be used as your schema on the fly for that table. If you are just using one database in your application set the schema the same as the database.

Enabling strict exceptions
--------------------------

[](#enabling-strict-exceptions)

To enable throwing exceptions for any issues that come up during the execution of queries, you can do so by setting it on via the dbConnection like so:

```
$this->get('dbManager')->getConnection()->setAttribute(
  PDO::ATTR_ERRMODE,
  PDO::ERRMODE_EXCEPTION
);
```

Registering your own database provider class
--------------------------------------------

[](#registering-your-own-database-provider-class)

In case the provided provider isn't compatible with your engine version or is missing, you can register your own provider before any calls are made like so:

```