TYPO3 4.5 LTS: Prepared queries are the way to go!

By: Xavier Perseguers

Prepared queries are now part of the TYPO3 database API. The API is mostly based on PDO's method naming and currently supports SELECT queries. Read on to learn how to take advantage of this enhanced API for your own extensions.

Starting with TYPO3 4.5 LTS, you have the chance to create even more optimized database queries using the TYPO3 database API. As extension developer, you already know that using direct mysql_* calls is not the way to go and that you should use $GLOBALS['TYPO3_DB'] public methods instead. For instance:

• $GLOBALS['TYPO3_DB']->exec_SELECTquery()
• $GLOBALS['TYPO3_DB']->exec_SELECTgetRows()

to retrieve records from the database or

• $GLOBALS['TYPO3_DB']->exec_INSERTquery()
• $GLOBALS['TYPO3_DB']->exec_UPDATEquery()
• $GLOBALS['TYPO3_DB']->exec_DELETEquery()

to update them. The full list of public methods is found in t3lib/class.t3lib_db.php.

Why not use mysql_* functions

Before going on, you may wonder why it is so important to use this API and not the mysql_* functions instead?

The reason is that TYPO3 is an enterprise content management system and as such is not bound to use a MySQL database. In fact, when using TYPO3 for large organizations or companies such as government departments, using MySQL is simply not allowed and TYPO3 agencies have to use either Oracle, or MS SQL Server or PostgreSQL as underlying database system.

It's now clear that whenever you, as extension developer, write some code using mysql_* functions, your extension, whenever used in such a non-MySQL environment, will have no single chance to run!

In order to circumvent this pitfall, TYPO3 offers an unified database API brought to you through the global variable $GLOBALS['TYPO3_DB']. You write your query still using MySQL syntax and TYPO3 will rewrite it automatically, if needed, for the underlying database system you use. It's simple as that!

So what is this exciting new feature that was added to TYPO3 4.5 LTS?

Prepared queries!

What is a prepared query

Prepared queries are the ability to set up a statement or query once, and then execute it many times
with different parameters. They are designed to replace building ad hoc query strings and do it in a more
efficient manner. A typical prepared query would look something like:

SELECT * FROM pages WHERE pid = ?

The ? is called a placeholder. When you execute the above query, you would need to supply the value for it, which would replace the ? placeholder in the above query.

Another syntax lets you use named placeholders instead which becomes even more readable:

SELECT * FROM pages WHERE pid = :pid

Here ':pid' is such a named placeholder.

Why is this good for? First of all, this will allow the database to be much more efficient to retrieve records with lots of similar queries. Imagine you do that in your code:

for ($i = 1; $i < 10; $i++) {
    $res = $GLOBALS['TYPO3_DB']->exec_SELECTquery('*', 'pages', 'pid = ' . $i);
    while (($row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) !== FALSE) {
        // Do something with your $row
    }
    $GLOBALS['TYPO3_DB']->sql_free_result($res);
}

Your database will be queried 10 times with some similar query but still everytime different:

SELECT * FROM pages WHERE pid = 1
SELECT * FROM pages WHERE pid = 2
SELECT * FROM pages WHERE pid = 3
...
SELECT * FROM pages WHERE pid = 9

Your MySQL server will need to parse the query over and over again to prepare the execution plan and finally execute it and return you the corresponding rows. One may argue that DBMS nowadays are quite clever and they could notice such pattern but still, we are showing you some really simple query for the sake of the understanding. When using real world queries your database server will have less chance to infer the underlying pattern and MySQL, unlike more advanced other DBMS, is even more likely to fail at this job.

If however you would have used prepared queries, your MySQL server could have prepared the execution plan and would have simply taken your placeholder parameter in the compiled version of your query and the whole process would have been more efficient.

As said, since TYPO3 4.5 LTS it is now possible to use prepared queries. Using the same query as above, you would now write this instead:

$statement = $GLOBALS['TYPO3_DB']->prepare_SELECTquery('*', 'pages', 'pid = :pid');

for ($i = 1; $i < 10; $i++) {
    $statement->execute(array(':pid' => $i));
    while (($row = $statement->fetch()) !== FALSE) {
        // Do something with your $row
    }
    $statement->free();
}

As you see, it's straightforward to use! There are lots of different ways of using prepared queries and you should have a look at the class holding your prepared statement: t3lib/db/class.t3lib_db_preparedstatement.php for further examples of use.

Now the good news is that using prepared queries will dramatically boost your extension when using a non-MySQL database. In fact, when running TYPO3 on an Oracle database for instance, TYPO3 has to rewrite all queries to enclose each and every field and table name with double quotes, meaning your query would be rewritten to

SELECT * FROM "pages" WHERE "pid" = ...

But wait, there's more! Oracle does not support identifiers of more than 30 characters meaning you cannot access your book table in this example:

SELECT * FROM tx_myreallygreatextension_domain_model_book

But thanks to DBAL, the system extension that lets TYPO3 use a non-MySQL database, you could instruct TYPO3 to remap the table name to some shorter one, e.g.,

SELECT * FROM tx_myrgext_book

As extension developer you still have queries against table tx_myreallygreatextension_domain_model_book but the integrator who has to deal with Oracle may write a mapping configuration in typo3conf/localconf.php that will allow him to run your great extension on Oracle.

The point is that the remapping operation is quite costly and if you use prepared queries instead, TYPO3 will be able to perform this operation only once, resulting in a boost of your application.

If you use the TYPO3 caching framework with

$TYPO3_CONF_VARS['SYS']['useCachingFramework'] = 1;

the prepared query will be cached during the whole request. If you want to be even more efficient, you will need one or more memcached servers to cache your prepared queries for a longer period of time. You just have to add following configuration:

$TYPO3_CONF_VARS['SYS']['useCachingFramework'] = 1;
$TYPO3_CONF_VARS['SYS']['caching']['cacheConfigurations']['dbal'] = array(
      'backend' => 't3lib_cache_backend_MemcachedBackend',
      'options' => array(
              'servers' => array('localhost:11211', 'otherhost:11211'),
      )
);

That's it! We are all looking forward to seeing your extension use the enhanced TYPO3 database API.

Have fun!
Xavier