:::{php:namespace} Atk4\Data\Persistence\Sql :::

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

{php:class}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 {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.

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 {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)=

Extending Query Class

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)=

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 {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.

Adding New Query Modes

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();

Manual Query Execution

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

Exception Class

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.

Data Laravel Package