docs/persistence/sql/advanced.md
:::{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](#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.
```sql
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`.
1. Create your own Query class inside your library. If necessary create your
own Connection class too.
2. Make use of composer and add dependency to DSQL.
3. Add a nice README file explaining all the quirks or extensions. Provide
install instructions.
4. Fork DSQL library.
5. Modify {php:meth}`Connection::connect` to recognize your database identifier
and refer to your namespace.
6. Modify docs/extensions.md to list name of your database and link to your
repository / composer requirement.
7. Copy phpunit-mysql.xml into phpunit-myvendor.xml and make sure that
dsql/tests/db/* works with your database.
8. 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":
1. Define new property inside your {php:class}`Query` class $templateLoadData.
2. Add public method allowing to specify necessary parameters.
3. Re-use existing methods/template tags if you can.
4. 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.
1. {php:meth}`Expression::render` query, then send the 1st element into PDO::prepare();
2. use new $statement to bindValue with the contents of 2nd element;
3. set result fetch mode and parameters;
4. 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
```{eval-rst}
: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.
```{eval-rst}
: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.