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.
- Custom exceptions.
- 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.
- 1: LastInsertId fetch adjusted to work correctly with the postgresql driver.
require with composer
composer require "genesis/behat-sql-extension"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.
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: falseIn the above example, the keywords section provides injection of keywords. For example you can have:
default:
extensions:
...:
...
keywords:
qwerty: thisisthehashofthepasswordThis will make the qwerty keyword usable as follows:
Given I have a "user" where "email:[email protected],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.
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
);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:
<?php
class FeatureContext
{
public function __construct()
{
$this->sqlContext = ...;
$this->sqlContext->get('dbManager')->getProviderFactory()->registerProvider(
string $engine,
string $providerClass
);
}
}###2. Environment variable
An environment variable can be set for the database connection details in the following way:
$ export BEHAT_ENV_PARAMS="DBENGINE:mysql;DBHOST:127.0.0.1;DBSCH..."Fields required are
DBENGINE
DBHOST
DBSCHEMA
DBNAME
DBUSER
DBPASSWORD
The fields needs to be preset but may be left empty.
This will run an insert query using the @where/@with data provided
# file: insert.feature
# replace @table with your table name, include schema if table is stored in a schema
# @with/@where are used synonymously in this call
Given I have a "@table" where "@where"To insert more rows at once the above statement can be re-written as follows:
# file: insert.feature
Given I have "@table" where:
| column1 | column2 |
| row1-column1-value | row1-column2-value |
| row2-column1-value | row2-column2-value |The above will insert two rows.
This will run a delete query against the database using the @where/@with criteria given
# file: delete.feature
# @with/@where are used synonymously in this call
Given I do not have a "@table" where "@where"This call will run an update query on the database records matching the @where clause
# file: update.feature
# @table for this to make sense your table should represent an entity
# @update the field you would like to update e.g email:[email protected]
# @where this functions exactly the same as the sql where clause
# Format for @update and @where is "email:its.inevitable.com,id:1,isActive:true"
Given I have an existing "@table" with "@update" where "@where"You can use the not operator to say a column should not be equal to value as follows:
Then I should have a "user" with "email:[email protected], active: !null" in the databaseThis will generate active is not null. For a value other than null it would generatecolumn != value.
The same can be written as:
Then I should have a "user" with:
| column | value |
| email | its.inevitable@hotmail.com |
| active | !null |Note the top row is just explanatory, it will not be used as part of the query.
You can perform a LIKE clause with the following format:
Then I should have a "user" with "user_agent:%Firefox%" in the databaseIn order to apply greater than or less than comparisons:
Then I should have a "user" with "dob:>2001-01-01" in the databaseOR
Then I should have a "user" with "age:<18" in the databaseNote: These operators are only applicable on numbers and date formats (yyyy-mm-dd).
After creating or updating data you can assign the record's values to a keyword with the following clause
# file: reuse.feature
# Create a new user.
Given I have a "user" where "email:[email protected]"
# The above command will create the record and also be aware of the data created. You can re-use this data in the following
# commands. To re-use, just use it like so "user.<column>". Remember any required fields that you may have not passed in data for
# explicitly will still be filled by the extension for you.
Given I have an "account" where "title:my account, user_id:{user.id}"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
- 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}
- Consider a table user with the following columns:
To substitute a value from another table use the following syntax:
Then I should have a "table" where "column1:value1, column2:[table1.columnToUse|whereColumn:Value]"Putting the above into context.
column1: value1 # Usual sql syntax.
column2: [table1.columnToUse|whereColumn:Value] # External reference to the table `table1`
The above syntax i.e [...] will be resolved as follows:
SELECT `table1.columnToUse` FROM `table1` WHERE `whereColumn` = 'Value';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:
Then I should have a "user" with "email:[email protected],status:1" in the databaseNote: the 'in the database' part of the step definition is optional and is only for clarity of the step definition.
Debug mode can be used to print sql queries and results to the screen for quick debugging.
# file: behat.yml
# Enable debug mode to check for errors
...
Genesis\SQLExtension\Extension:
debug: true
...The above "I have" command will output something like this to the screen:
Executing SQL: INSERT INTO user (email) VALUES ('[email protected]')
Last ID fetched: 57The extension provides an easy API for the same functionality as the DSL language. To give the code more context use the following:
$this
->select(string $table, array $where) # select a record, essentially perform a iHaveAnExistingWhere.
->insert(string $table, array $where) # Insert a new record if it does not exist, same as iHaveAWith
->update(string $table, array $update, array $where) # Update an existing record, same as iHaveAnExistingWithWhere
->delete(string $table, array $where) # Delete a record, same as iDontHaveAWhere
;Anything the DSL does will be done using the above methods (i.e setting keywords, outputting to debug log etc...)
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.
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent