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 Expression('show tables like []', ['foo%']);
or query:
$query = (new Query())->table('user')->where('id', 1);
When it’s time to execute you can specify your PDO manually:
$stmt = $expr->execute($pdo);
foreach($stmt as $row){
echo json_encode($row)."\n";
}
With queries you might need to select mode first:
$stmt = $query->selectMode('delete')->execute($pdo);
The Expresssion::execute
is a convenient way to prepare query,
bind all parameters and get PDOStatement, 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:
$pdo = DB::connection()->getPdo();
$c = new Connection(['connection'=>$pdo]);
$user_ids = $c->dsql()->table('expired_users')->field('user_id');
$c->dsql()->table('user')->where('id', 'in', $user_ids)->set('active', 0)->update();
// Native Laravel Database Query Builder
// $user_ids = DB::table('expired_users')->lists('user_id');
// DB::table('user')->whereIn('id', $user_ids)->update(['active', 0]);
The native query builder in the example above populates $user_id 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::query_class
:
$c = new Connection(['connection'=>$pdo, 'query_class'=>'atk4\dsql\Query_SQLite']);
This is also useful, if you have created your own Query class in a different namespace and wish to use it.
Using Dumper and Counter¶
DSQL comes with two nice features - “dumper” and “counter”. Dumper will output all the executed queries and how much time each query took and Counter will record how many queries were executed and how many rows you have fetched through DSQL.
In order to enable those extensions you can simply change your DSN from:
"mysql:host=localhost;port=3307;dbname=testdb"
to:
"dumper:mysql:host=localhost;port=3307;dbname=testdb"
"counter:mysql:host=localhost;port=3307;dbname=testdb"
"dumper:counter:mysql:host=localhost;port=3307;dbname=testdb"
When this DSN is passed into Connection::connect
, it will return
a proxy connection object that will collect the necessary statistics and
“echo” them out.
If you would like to do something else with these statistics, you can set a callback. For Dumper:
$c->callback = function($expression, $time) {
...
}
and for Counter:
$c->callback = function($queries, $selects, $rows, $expressions) {
...
}
If you have used “dumper:counter:”, then use this:
$c->callback = function($expression, $time) {
...
}
$c->connection()->callback = function($queries, $selects, $rows, $expressions) {
...
}
Proxy Connection¶
Connection class is designed to create instances of Expression
,
Query
as well as executing queries.
A standard Connection
class with the use of PDO will do nothing
inside its execute() because Expression::execute
would handle all
the work.
However if Connection::connection
is NOT PDO object, then
Expression
will not know how to execute query and will simply
call:
return $connection->execute($this);
Connection_Proxy
class would re-execute the query with a different
connection class. In other words Connection_Proxy
allows you
to “wrap” your actual connection class. As a benefit you get to extend
Proxy
class implementing some unified features that would work with
any other connection class. Often this will require you to know externals, but
let’s build a proxy class that will add “DELAYED” options for all INSERT
operations:
class Connection_DelayInserts extends \atk4\dsql\Connection_Proxy
{
function execute(\atk4\dsql\Expression $expr)
{
if ($expr instanceof \atk4\dsql\Query) {
if ($expr->mode == 'insert') {
$expr->insertOption('delayed');
}
}
return parent::execute($expr);
}
}
Next we need to use this proxy class instead of the normal one. Frankly, that’s quite simple to do:
$c = \atk4\dsql\Connection::connect($dsn, $user, $pass);
$c = new Connection_DelayInserts(['connection'=>$c]);
// use the new $c
Connection_Proxy
can be used for many different things.
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\dsql\Query
{
// truncate is done differently by this vendor
protected $template_truncate = 'delete [from] [table]';
// also join is not supported
public function join(
$foreign_table,
$master_field = null,
$join_kind = null,
$_foreign_alias = null
) {
throw new atk4\dsql\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\dsql\Connection::connect($dsn, $user, $pass, ['query_class'=>'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 $template_load_data. - 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\dsql\Exception;
class Query_MySQL extends \atk4\dsql\Query_MySQL
{
protected $template_load_data = '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()
{
return $this->mode('load_data')->execute();
}
}
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 it into PDO::prepare();- use new $statement to bindValue with the contents of
Expression::params
; - 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\dsql\Exception('Hello');
throw new atk4\dsql\Exception(['File is not readable', '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 atk4dsqlException. 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.