:::{php:namespace} Atk4\Data\Persistence\Sql :::
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.
{php:class}Connection
is incredibly lightweight and powerful in DSQL.
The class tries to get out of your way as much as possible.
You can use {php:class}Query
and {php:class}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 {php:meth}Expression::execute
is a convenient way to prepare query,
bind all parameters and get Doctrine\DBAL\Result
, but if you wish to do it manually,
see Manual Query Execution.
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 {php:class}Connection
through constructor, you may have
to explicitly specify property {php:attr}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)=
You can add support for new database vendors by creating your own
{php:class}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']);
(new_vendor)=
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 {php:meth}
Connection::connect
to recognize your database identifier and refer to your namespace. - Modify docs/extensions.md 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.
- Submit pull request for only the Connection class and docs/extensions.md.
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.
By Default DSQL comes with the following {ref}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 {php:class}
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();
If you are not satisfied with {php:meth}Expression::execute
you can execute
query yourself.
- {php:meth}
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
DSQL slightly extends and improves {php:class}Exception
class
:::{php: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.
:::{php:method} __construct($message, $code) Create new exception
:param string|array $message: Describes the problem
:param int $code: 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:
:::{php:method} getParams() Return additional parameters, that might be helpful to find error.
:returns: array
:::
Any DSQL-related code must always throw Atk4\Data\Persistence\Sql\Exception. 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.