Debugging the SQLite driver

Some time ago I wrote about creating a custom collation for SQLite, integrating SQLite in a Qt application and creating a custom SQLite driver. Although it's a bit of inconvenience, there are also additional benefits of having a custom SQLite driver, for example better support for debugging.

Tracing executed queries is very useful for debugging complex applications because it gives an idea what's happening during its execution without need to debug it line by line. Most server based database systems include tracing functionalities, but in case of a server-less database such as SQLite the only option is to build in tracing into the client.

There are at least two ways of doing that. The simplest one is to modify the SQLiteDriver::prepare method and include code which prints out the query:

qDebug() << "SQLite:" << query;

The disadvantage of that solution is that if the query contains parameters, their values will not be printed. Furthermore, if the query is prepared once and executed multiple times with various parameters, it will still be printed only once.

Luckily SQLite gives us an API function which can be used to trace executed queries. First we need to define a callback function:

static void trace( void* /*arg*/, const char* query )
{
    qDebug() << "SQLite:" << QString::fromUtf8( query );
}

Now we can register the function using the following code:

sqlite3_trace( db, trace, NULL );

Voilà! All executed queries are printed and SQLite automatically replaces the parameter placeholders with appropriate values.

Another thing that is very useful for debugging is information about errors. Printing out all error messages makes debugging a lot easier, especially that it's quite difficult to handle QSqlQuery errors properly in all places.

SQLite does not have a callback for reporting errors, but QSqlResult, which is a base class of the SQLiteResult has a virtual method called setLastError. It is called with appropriate error details whenever some SQLite function return an error. Normally it only stores the error so it is available by using the lastError mehod. All we need to do is override it so that is prints out the error:

void SQLiteResult::setLastError(const QSqlError& e)
{
    if (e.isValid())
        qDebug() << "SQLite error:" << e.driverText() << e.databaseText();
    SqlCachedResult::setLastError(e);
}

The driverText is the description of the operation that was performed and databaseText is the error message returned by SQLite.

When the error occurs while preparing the statement, for example because the query has invalid syntax, the trace method is not called and no information about the query that failed is printed. The simplest solution is to manually call the trace method when sqlite3_prepare16 returns an error:

trace( NULL, query.toUtf8().constData() );

The QSqlDriver class also has a virtual setLastError method which can be overloaded is order to report errors related to opening the database and handling transactions.

Tracing should obviously be disabled in release builds; it's generally a good idea to disable it by default and only enable it conditionally, for example when a special preprocessor macro is defined.