Advanced Topics¶
DSQL has huge capabilities in terms of extending. This chapter explains just some of the ways how you can extend this already incredibly powerful library.
Advanced Connections¶
Connection
is incredibly lightweight and powerful in DSQL.
The class tries to get out of your way as much as possible.
Using DSQL without Connection¶
You can use Query
and Expression
without connection
at all. Simply create expression:
$expr = new Mysql\Expression('show tables like []', ['foo%']);
or query:
$query = (new Mysql\Query())->table('user')->where('id', 1);
When it’s time to execute you can specify your Connection manually:
$rows = $expr->getRows($connection);
foreach ($rows as $row) {
echo json_encode($row) . "\n";
}
With queries you might need to select mode first:
$stmt = $query->mode('delete')->executeStatement($connection);
The Expresssion::execute
is a convenient way to prepare query,
bind all parameters and get DoctrineDBALResult, but if you wish to do it manually,
see Manual Query Execution.
Using in Existing Framework¶
If you use DSQL inside another framework, it’s possible that there is already a PDO object which you can use. In Laravel you can optimize some of your queries by switching to DSQL:
$c = new Connection(['connection' => $pdo]);
$userIds = $c->dsql()->table('expired_users')->field('user_id');
$c->dsql()->table('user')->where('id', 'in', $userIds)->set('active', 0)->mode('update')->executeStatement();
// Native Laravel Database Query Builder
// $userIds = DB::table('expired_users')->lists('user_id');
// DB::table('user')->whereIn('id', $userIds)->update(['active', 0]);
The native query builder in the example above populates $userIds with array from expired_users table, then creates second query, which is an update. With DSQL we have accomplished same thing with a single query and without fetching results too.
UPDATE
user
SET
active = 0
WHERE
id in (SELECT user_id from expired_users)
If you are creating Connection
through constructor, you may have
to explicitly specify property Connection::queryClass
:
$c = new Connection(['connection' => $pdo, 'queryClass' => Atk4\Data\Persistence\Sql\Sqlite\Query::class]);
This is also useful, if you have created your own Query class in a different namespace and wish to use it.
Extending Query Class¶
You can add support for new database vendors by creating your own
Query
class.
Let’s say you want to add support for new SQL vendor:
class Query_MyVendor extends Atk4\Data\Persistence\Sql\Query
{
protected string $identifierEscapeChar = '"';
protected string $expressionClass = Expression_MyVendor::class;
// truncate is done differently by this vendor
protected string $templateTruncate = 'delete [from] [table]';
// also join is not supported
public function join(
$foreignTable,
$masterField = null,
$joinKind = null,
$foreignAlias = null
) {
throw new Atk4\Data\Persistence\Sql\Exception('Join is not supported by the database');
}
}
Now that our custom query class is complete, we would like to use it by default on the connection:
$c = \Atk4\Data\Persistence\Sql\Connection::connect($dsn, $user, $pass, ['queryClass' => 'Query_MyVendor']);
Adding new vendor support through extension¶
If you think that more people can benefit from your custom query class, you can create a separate add-on with it’s own namespace. Let’s say you have created myname/dsql-myvendor.
- Create your own Query class inside your library. If necessary create your own Connection class too.
- Make use of composer and add dependency to DSQL.
- Add a nice README file explaining all the quirks or extensions. Provide install instructions.
- Fork DSQL library.
- Modify
Connection::connect
to recognize your database identifier and refer to your namespace. - Modify docs/extensions.rst to list name of your database and link to your repository / composer requirement.
- Copy phpunit-mysql.xml into phpunit-myvendor.xml and make sure that dsql/tests/db/* works with your database.
- Finally:
- Submit pull request for only the Connection class and docs/extensions.rst.
If you would like that your vendor support be bundled with DSQL, you should contact copyright@agiletoolkit.org after your external class has been around and received some traction.
Adding New Query Modes¶
By Default DSQL comes with the following Query Modes:
- select
- delete
- insert
- replace
- update
- truncate
You can add new mode if you wish. Let’s look at how to add a MySQL specific query “LOAD DATA INFILE”:
- Define new property inside your
Query
class $templateLoadData. - Add public method allowing to specify necessary parameters.
- Re-use existing methods/template tags if you can.
- Create _render method if your tag rendering is complex.
So to implement our task, you might need a class like this:
use \Atk4\Data\Persistence\Sql\Exception;
class QueryMysqlCustom extends \Atk4\Data\Persistence\Sql\Mysql\Query
{
protected string $templateLoadData = 'load data local infile [file] into table [table]';
public function file($file)
{
if (!is_readable($file)) {
throw Exception(['File is not readable', 'file' => $file]);
}
$this['file'] = $file;
}
public function loadData(): array
{
return $this->mode('loadData')->getRows();
}
}
Then to use your new statement, you can do:
$c->dsql()->file('abc.csv')->loadData();
Manual Query Execution¶
If you are not satisfied with Expression::execute
you can execute
query yourself.
Expression::render
query, then send the 1st element into PDO::prepare();- use new $statement to bindValue with the contents of 2nd element;
- set result fetch mode and parameters;
- execute() your statement
Exception Class¶
DSQL slightly extends and improves Exception
class
-
class
Exception
¶
The main goal of the new exception is to be able to accept additional information in addition to the message. We realize that often $e->getMessage() will be localized, but if you stick some variables in there, this will no longer be possible. You also risk injection or expose some sensitive data to the user.
-
Exception::
__construct
($message, $code)¶ Create new exception
Parameters: - $message (string|array) – Describes the problem
- $code (int) – Error code
Usage:
throw new Atk4\Data\Persistence\Sql\Exception('Hello');
throw (new Atk4\Data\Persistence\Sql\Exception('File is not readable'))
->addMoreInfo('file', $file);
When displayed to the user the exception will hide parameter for $file, but you still can get it if you really need it:
-
Exception::
getParams
()¶ Return additional parameters, that might be helpful to find error.
Returns: array
Any DSQL-related code must always throw Atk4DataPersistenceSqlException. Query-related errors will generate PDO exceptions. If you use a custom connection and doing some vendor-specific operations, you may also throw other vendor-specific exceptions.