Migrate from the TYPO3 database wrapper to the Doctrine DBAL syntax

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:

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.

 

Now you can start to build your query.

exec_SELECTcountRows

TYPO3 DB wrapper style:

Doctrine DBAL style:

exec_SELECTgetSingleRow

If you just need a single result row, you replace

with the following

exec_SELECTgetRows across different tables

Now things start to become a little bit more complex. I have to join two tables.

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.

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

You can do this by just using the OR operator, because it is simple enough to work with all database systems

But the nicer way would look like

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

is replaced by

TYPO3 syntax:

is replaced by

 

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

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.

Links

3 Gedanken zu „Migrate from the TYPO3 database wrapper to the Doctrine DBAL syntax

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.