Home · All Namespaces · All Classes · Grouped Classes · Modules · Functions

SQL Integration

• SQL Database Requirements
• Concurrent Access
• Internationalization
• SQL Language Support
• Performance and Size
• Integration
• QSql
• Specifying the SQL Database Driver
• Ensuring Table Schemas

SQL Database Requirements

Concurrent Access

The database handles simultaneous query attempts from multiple applications by blocking new queries while existing queries are running. However, the Qt Extended applications and libraries are designed to only create short-lived/temporary SQL queries.

Internationalization

Qt Extended supports a number of languages and so strings should be stored in a Unicode-compatible encoding such as UTF8 or UTF16. A localized string comparison operation must be provided by the database as localeAwareCompare needs to be equivalent to the QString::localeAwareCompare() function. The SQLite version of this function is as follows:

    int sqliteLocaleAwareCompare(void *, int ll, const void *l, int rl, const void *r)
    {
        QString left = QString::fromUtf16((const ushort *)l, ll);
        QString right = QString::fromUtf16((const ushort *)r, rl);
        return QString::localeAwareCompare(left, right);
    }

SQL Language Support

Much of Qtopia / Qt Extended 4 was developed with the SQLite database in mind. So while it is intended that embedded SQL statements are not dependent on any specific implementation it is possible there will be incompatibilities with SQL databases that do not support the SQLite SQL language subset. Any incompatiblities will be addressed in the affected libraries and applications as they are identified. An exception to this is the creation of tables and constraints which is explained in the section Ensuring Table Schemas.

Performance and Size

Qt Extended is intended for use on embedded devices and it is important that the database has small implementation and dataset sizes in addition to good performance. Currently SQLite Version 3 is being used with Qt Extended and provides a benchmark for size and performance requirement as follows:

• table sizes are expected to be at most 10000 items
• performance must be acceptable on a 200Mhz device
• focus on record retrieval with LIMIT and OFFSET.

Integration

QSql

Qt Extended uses the QSql classes provided by Qt and so a Qt SQL database driver is required for access to the database. If the access to the database is not provided by one of the existing Qt SQL database drivers a new SQL database driver will need to be implemented. Additional documentation on SQL database drivers for Qt can be found at Qt SQL Module - Drivers.

Specifying the SQL Database Driver

Code related to specifying a Qt Extended driver as well as many other driver-specific code can be found in:

         $QPEDIR/src/libraries/qtopia/qtopiasql.cpp

however relevant sections may be moved to custom.cpp in future.

Ensuring Table Schemas

Incompatibilities between SQL implementations exist when creating tables and specifying constraints on fields. Qt Extended overcomes this limitation by providing a mechanism to abstract the creation of tables in the database as follows:

1. Libraries and applications specify a set of resource files containing the SQL statements required to create required tables and constraints. For example the resource file to create the categories table, related constraints and initial data can be found at

   $QPEDIR/src/libraries/qtopia/resources/categories.sqlite.sql

2. The file is then specified in the .qrc file for the qtopia library using the alias:

            <file alias="QtopiaSql/QSQLITE/categories">resources/categories.sqlite.sql</file>

   The alias has the form QtopiaSql/<driver>/<table> where:

   • the driver is the identifier that is used for the Qt SQL driver
   • the table is as used by the QtopiaSql::ensureSchema command:

              QtopiaSql::ensureSchema("categories");

3. The function first checks for an existing table of the name specified. If it does not exist it will load the schema as via the resource files described earlier. The resource file is responsible for creating the table of its own name and setting any constraints or initial data required by that table.
4. Code that uses the SQL database is required to call ensureSchema for all tables it depends on. For example, the Contacts part of Qt Extended PIM library has the following ensureSchema statements:

            QStringList tables;
            tables << "categories";
            tables << "contacts";
            tables << "emailaddresses";
            tables << "contactcategories";
            tables << "contactcustom";
            QtopiaSql::ensureSchema(tables);

Some schemas may depend on others and it is the responsibility of the application or library to ensure schemas are loaded in the correct order. Constraints specified in Qt Extended are almost entirely foreign key constraints, although currently there are also system data constraints for the categories table.