Last updated: 2016-06-06 13:00
With the TYPO3 8.1 release, the Doctrine Database Abstraction Layer (DBAL) will be introduced into the TYPO3 core. Sooner or later the old TYPO3 database wrapper (together with the old dbal and adodb extensions) will be removed. So if you’re an extension developer creating extensions for TYPO3 8.1 or above, it could come in handy to migrate all of your database calls to the new syntax. If your extension just relies on Extbase Repository stuff, then you are fine. The migration of Extbase will be done by the core itself so your magic functions will also work in the future. But in case you still have this dusty exec_SELECTgetRows somewhere in your extension, I’ll try to give you some help to come to the light side.
I gained all my knowledge during migrating system extensions to the new syntax shortly after the initial merge of Doctrine DBAL into the core. So it’s kinda “cutting edge” and not necessarily “best practice”.
Most of this is pure Doctrine DBAL syntax, so if in doubt don’t hesitate to check the Doctrine DBAL documentation for more detailed information [1].
Prerequisites
The following examples assumes you create a query builder object like this for every query you want to build:
|
1 2 3 4 5 6 7 8 |
// The use statements of course belong into the file header. use TYPO3\CMS\Core\Database\ConnectionPool; use TYPO3\CMS\Core\Database\Query\QueryBuilder; use TYPO3\CMS\Core\Utility\GeneralUtility; /** @var QueryBuilder $queryBuilder */ $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class) ->getQueryBuilderForTable('sys_log'); |
The query builder substitutes the custom getDatabaseConnection() wrapper method you most likely integrated in the past into your class to have proper code completion. Or, if your extension is that outdated, it will be somehow similar to $GLOBALS[‚TYPO3_DB‘].
It might seem to be a little bit odd to specify the table name here, because you will also write it down again in the query. But with Doctrine DBAL every table can be located in a different database and even on different database systems (e.g. MySQL, Oracle). This way the query builder instantly knows, what kind of table you want to address and can apply the database specific encapsulating and quoting later on.
Examples how to migrate
I try to cover most cases that were used in developers daily work.
exec_SELECTgetRows
That’s the most basic function to select a couple of rows out of the database and provide them as a handy array.
|
1 2 3 4 5 6 7 |
$rows = $this ->getDatabaseConnection() ->exec_SELECTgetRows( 'uid', 'pages, 'pid=' . $pid ); |
Now you can start to build your query.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
$rows = $queryBuilder // Select one ore more fields … ->select('uid') // … from a table … ->from('pages') // … that fits into this condition. ->where( // This looks a bit strange, but ensures that the condition is coded in the most suitable way for the database. $queryBuilder->expr()->eq('pid', (int)$pid) ) // Run the query … ->execute() // … and fetch all results as an associative array. ->fetchAll(); |
exec_SELECTcountRows
TYPO3 DB wrapper style:
|
1 2 3 4 5 6 7 |
$count = $this ->getDatabaseConnection() ->exec_SELECTcountRows( 'error', 'sys_log', 'tstamp >= ' . $timestamp . ' AND error IN(-1,1,2)'); |
Doctrine DBAL style:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
$count = $queryBuilder // Invoke the native count command of the database ->count('error') // Table ->from('sys_log') // Every where() will overwrite the last one. So … ->where( $queryBuilder->expr()->gte('tstamp', $timestamp), $queryBuilder->expr()->in('error', [-1, 1, 2]) ) // Run the command ->execute() // After executing you would get a result set with one result. And in this result the first column will contain the count. ->fetchColumn(0); |
exec_SELECTgetSingleRow
If you just need a single result row, you replace
|
1 2 3 4 5 6 |
$record = $this ->getDatabaseConnection() ->exec_SELECTgetSingleRow( '*', 'sys_history', '1=1; |
with the following
|
1 2 3 4 5 6 7 |
$record = $queryBuilder ->select('*') ->from('sys_history') // Yes, no “where” command here. Doctrine DBAL can build queries without where. Finally. ->execute() // This will fetch exactly on specified row out of the result. If no row is specified, it will fetch the first row. ->fetch(); |
exec_SELECTgetRows across different tables
Now things start to become a little bit more complex. I have to join two tables.
|
1 2 3 4 5 6 |
$rows = $databaseConnection->exec_SELECTgetRows( 'sys_history.*, sys_log.userid, sys_log.log_data', 'sys_history, sys_log', 'sys_history.sys_log_uid = sys_log.uid' . ' AND sys_history.tablename = ' . $databaseConnection->fullQuoteStr($table, 'sys_history') . ' AND sys_history.recuid = ' . (int)$uid, '', 'sys_log.uid DESC', $this->maxSteps); |
Because old habits die hard (and I don’t want to change the resulting SQL statement to avoid behavior changes) I refused to rebuild it using the JOIN syntax.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
$rows = $queryBuilder // Every argument is entered separately so Doctrine DBAL can take care of the encapsulation. ->select('sys_history.*', 'sys_log.userid', 'sys_log.log_data') // The first table … ->from('sys_history') // … and the second table. Every table gets an own from() command ->from('sys_log') ->where( $queryBuilder->expr()->eq( // This joins the two tables. 'sys_history.sys_log_uid', $queryBuilder->quoteIdentifier('sys_log.uid') ) ) ->andWhere($queryBuilder->expr()->eq('sys_history.tablename', // That is more sophisticated than just using the also possible quote() command, because quote() would cast everything to a string while createNamedParameter() will stay an object and will be more easy handleable for Doctrine DBAL during execution. $queryBuilder->createNamedParameter($table)) ) ->andWhere($queryBuilder->expr()->eq('sys_history.recuid', (int)$uid)) // Kinda self explaining ->orderBy('sys_log.uid', 'DESC') // This replaces the LIMIT statement ->setMaxResults($this->maxSteps) ->execute() ->fetchAll(); |
Some of you might realize at this point, that the call for callQueryBuilderForTable() at the top of this article gets just one table name while I am fiddling around with multiple tables. That seems a little bit odd, but will work, because that would only be interesting, if both tables are located on different databases. Join database operations will not work across different databases.
Nesting conditions
Sometimes you want to nest conditions in the where statement like
|
1 2 3 4 5 6 7 8 |
$rows = $databaseConnection ->exec_SELECTgetRows( 'uid, userid, action, tstamp, log_data', 'sys_log', 'type = 1 ' . ' AND (action=1 OR action=3)' . ' AND tablename = ' . $databaseConnection->fullQuoteStr($table, 'sys_log') . ' AND recuid = ' . (int)$uid, '', 'uid DESC', $this->maxSteps); |
You can do this by just using the OR operator, because it is simple enough to work with all database systems
|
1 2 3 4 5 |
->andWhere( $queryBuilder->expr()->eq('action', 1) . ' OR ' . $queryBuilder->expr()->eq('action', 3) ) |
But the nicer way would look like
|
1 2 3 4 5 6 |
->andWhere( $queryBuilder->expr()->orX( $queryBuilder->expr()->eq('action', 1), $queryBuilder->expr()->eq('action', 3) ) ) |
You can add as much values to orX() as you like. And it might not surprise you, that there is also an andX() function available.
Insert, update and delete
This is now pretty straight forward. TYPO3 syntax
|
1 2 3 4 5 6 7 8 |
$this->getDatabaseConnection() ->exec_UPDATEquery( 'sys_history', 'uid=' . $uid, array( 'snapshot' => !$row['snapshot'] ) ); |
is replaced by
|
1 2 3 4 5 |
$queryBuilder ->update('sys_history') ->where($queryBuilder->expr()->eq('uid',$uid)) ->set('snapshot', !$row['snapshot']) ->execute(); |
TYPO3 syntax:
|
1 2 3 4 5 6 7 |
$this->getDatabaseConnection() ->exec_INSERTquery( 'sys_history', array( 'snapshot' => !$row['snapshot'] ) ); |
is replaced by
|
1 2 3 4 5 6 7 8 |
$queryBuilder ->insert('sys_history') ->values( array( 'snapshot', !$row['snapshot'] ) ) ->execute(); |
Show SQL query
For debugging purposes you almost always need the possibility to show the exact SQL query. That’s quite easy with Doctrine DBAL. Just remove the fetch command and replace execute() with getSQL().
|
1 2 3 4 |
echo $queryBuilder ->select('*') ->from('sys_history') ->getSQL(); |
Conclusion
Even if the new syntax look strange on first glance, it is easy to use, because it works the same way in every case. And the benefit of beging able to support more database systems while removing DBAL and adodb makes me happy.
So. If you are, after reading this till the end, eager to gain some experience while supporting the TYPO3 core work, don’t hesitate to pick yourself a piece of the cake. We have a LOT database statements to be replaced in the core and happy about every helping hand. Check out the Epic on Forge [2] to see, what happens until now, open a ticket for very your own playground and start contributing.
Nice! That blog post helped me a lot!
Great article! Thanks a lot!