Download the PHP package genesis/behat-sql-extension without Composer

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:

• Custom exceptions.

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:

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

Installation

require with composer

Instantiation

Instantiating the sql extension in your FeatureContext class.

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

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:

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

This will make the qwerty keyword usable as follows:

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:

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

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

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:

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:

2. Environment variable

An environment variable can be set for the database connection details in the following way:

Fields required are

The fields needs to be preset but may be left empty.

Calls provided by this extension

Inserting data in a table

This will run an insert query using the @where/@with data provided

To insert more rows at once the above statement can be re-written as follows:

The above will insert two rows.

Deleting data in a table

This will run a delete query against the database using the @where/@with criteria given

Updating data in a table

This call will run an update query on the database records matching the @where clause

Using the not operator.

You can use the not operator to say a column should not be equal to value as follows:

This will generate active is not null. For a value other than null it would generatecolumn != value.

The same can be written as:

Note the top row is just explanatory, it will not be used as part of the query.

Performing a LIKE search.

You can perform a LIKE clause with the following format:

Greater than or less than comparison.

In order to apply greater than or less than comparisons:

OR

Note: These operators are only applicable on numbers and date formats (yyyy-mm-dd).

Re-using values from another record

After creating or updating data you can assign the record's values to a keyword with the following clause

The Given I have ... command will do two things for you:

• Attempt to create a new record if it doesn't exist.
• Save all columns of that new record for re-usability in its keywords store. These are accessible like so: {table.column} Example:
  • Consider a table user with the following columns:
    • id
    • name
    • email
    • role_id
  • This Given I have a "user" where "email: [email protected]" will give you the following keywords:
    • {user.id}
    • {user.name}
    • {user.email}
    • {user.role_id}

Referencing foreign table values

To substitute a value from another table use the following syntax:

Putting the above into context.

The above syntax i.e [...] will be resolved as follows:

Verifying data in the database - Depreciated

Only verify the behaviour of your appication by testing your application and not the database with this extension. The following is not recommended except in extraordinary circumstances.

Verify the database records as follows:

Note: the 'in the database' part of the step definition is optional and is only for clarity of the step definition.

Debug mode

Debug mode can be used to print sql queries and results to the screen for quick debugging.

The above "I have" command will output something like this to the screen:

The SQLContext API

The extension provides an easy API for the same functionality as the DSL language. To give the code more context use the following:

Anything the DSL does will be done using the above methods (i.e setting keywords, outputting to debug log etc...)

Contributing to this extension

Found a bug? Excellent, I want to know all about it. Please log an issue here a link for the love of the project, or just open a PR I'd love to approve.