Using the Extensible SQLite driver

The SQLITEX driver is very similar to the standard SQLITE driver which is part of the Qt framework.

The main difference is that upon opening a database, it registers custom collations for case-insenitive comparisions and a function implementing the REGEXP operator. These are the most important two feature missing from standard SQLite implementation.

Why create another driver and not just use the existing one? The reason is that it is currently not possible to call the sqlite_ functions from the SQLite API, because they are not exported when the SQLite library bundled with Qt is used. Unfortunately, this is the case for many platforms, including Windows. See https://bugreports.qt-project.org/browse/QTBUG-18084 and http://www.mimec.org/node/296 for more information.

To workaround this limitation, the SQLITEX driver comes with it's own bundled SQLite library. However, it can also be configured to use the system sqlite library when it's possible (see below).

To use SQLITEX in your application, simply include sqlitex.pri in your project file.

Available configuration options:

system-sqlite

Use the system library instead of the bundled one.

You can enable these options by adding OPTIONS += ... to your project file before including sqlitex.pri.

Additional macros:

SQLITEDRIVER_EXPORT

Define this macro when building SQLITEX as a shared plug-in library.

SQLITEDRIVER_DEBUG

Enable detailed debugging messages containing executed SQL statements and errors reported by SQLite.

You can add these macros by putting DEFINES += ... in your project file.

You can use the SQLITEX driver just like the built-in SQLITE driver, for example:

QSqlDatabase database = QSqlDatabase::addDatabase( "SQLITEX" );
database.setDatabaseName( "/path/to/file.db" );
database.open();

The following Unicode aware, case insensitive collations are available:

LOCALE

Compare strings using the QString::localeAwareCompare() function. This is useful when sorting data using the ORDER BY clause or comparing strings using the > and < operators.

NOCASE

Compare strings using the QString::compare() function with the Qt::CaseInsensitive parameter. This overrides the built-in NOCASE collation and can be useful when comparing strings using the = operator or when using UNIQUE constaints.

Refer to the SQLite documentation for more information about using custom collations. Here are some example querieries:

• SELECT id, name FROM demo ORDER BY name COLLATE LOCALE
• SELECT id, name FROM demo WHERE name COLLATE NOCASE = 'ADAM'

The SQLITEX driver also registers the regexp function which makes it possible to use the REGEXP operator. The regular expression is matched by calling the exactMatch() method on a QRegExp created with the Qt::CaseInsensitive parameter.

Note that for performance reasons the SQLITEX driver sets the internal storage format of strings to SQLITE_UTF16. This increases the size of the database but makes it possible to compare strings without unnecessary conversions.