Home · All Classes · Grouped Classes · Annotated · Functions

Database Specification

This document describes the use and operation of relational database systems in Qtopia and should be used in conjunction with the Database Policy document.

General Architectural Description

Qtopia relies on relational database systems for a number of purposes and functions as summarized in the following table:

FunctionDescriptionCreatedVisibility
SystemPersistent system data and meta information, eg track VPN & PKI certificates, system tones, system categories.ROM imageNot user visible
ContentQtopia Document system; CategoriesROM image / Device run-timeUser visible
PIMQtopia Personal Information Management data, including Contacts (Address book), and CalendarROM image / Device run-timeUser visible
ApplicationAdditional Qtopia applications, Custom applications, 3rd party or installed applicationsDevice run-timeDepends on app

General Concepts

For the purposes of Qtopia, a relational database is defined as a system of managed data stores, which are queried and updated using Data Manipulation Language statements.

The most popular data manipulation standard is SQL, which is standardized in ISO/IEC 9075:1999(E) Information technology - Database languages - SQL.

An SQL relational database system is organized as:

where each is made up of the next, ie Databases are made up of Tables, which are made up of Columns. Additionally within Databases, a Table may have one or more indexes. Non-SQL-99 standard extensions such as stored procedures, relational constraints and triggers are also part of the database.

A Table may be TEMPORARY. Such tables are lost when the database connection is dropped, and may be used for staging large or complex queries.

More on basic or general theory regarding databases can be found in online sources or textbooks.

For the details of Qtopia system organization see Schema below.

Qtopia Databases

A relational database system may contain a number of discrete databases used to partition the system by purpose or function or to allow databases with different characteristics to coexist. For example databases may have:

Databases may have back-end storage on temporary file-systems or removable media. Such transient databases are a different concept to a temporary table. Transient databases are lost when the storage system is removed or reset (for example, tmpfs on reboot).

The following databases are used in Qtopia:

PREFIXThe System function of Qtopia is backed by a read-only database. The storage back-end is associated with the Qtopia prefix, which is the root of the Qtopia ROM image on the file-system.
CONTENTThe Content function is provided by a separate database It includes the Categories table accessed by PIM. This database is read-write, and files used by the storage engine are located in a user home directory.
PIMThe PIM function is provided by a separate database which reduces locking contention and namespace issues with the Content database. The Categories table, where required is accessed from the Content database. This database is read-write, and files used by the storage engine are located in a user home directory.
MEDIADatabases with the same identical schema as the HOME database are used for each media card on the Qtopia device. These Databases are transient and there is one for each media device.
APPSIn future releases of Qtopia other applications will create their own databases. This will allow access control to be used to protect data from corruption by incorrect use. Applications might include optional Qtopia applications, and/or second- & third-party applications.

The same schema of PIM + Content Tables is used for PREFIX, HOME and per MEDIA databases.

Other applications are free to use any schema as defined by the Database Policy.

Content and PIM Functions

The Content function is maintained as a separate collection of schema entries and has a separate API based around the QContent and QContentSet classes.

PIM has also its own set of schema entries and is accessed via the PIM library and its API via QPimRecord and related classes.

Applications using the PIM API connect to the database directly and execute SQL to update and query the tables in the PIM schema.

However, the Content function has a client-server architecture, as shown in the diagram below, such that only the Qtopia server process can perform updates and queries on it.

The Content function has this structure for the following reasons:

Multiple Databases

Typically it is not possible to query or update across databases, and it is best practice to design systems so requirements do not arise for cross-database queries. Currently in Qtopia cross-database queries are required due to the database per-media architecture. Application code is used to work around the situation, and the client-server architecture mitigates the risks.

Where separate databases are used and queries are required across both, there are two options:

Cross-database Work-aroundRisks
Write application code to perform the operations and merge the results as required.Race conditions, non-transactional performance problems.
Employ vendor specific extensions, such as MERGE TABLES or ATTACH DATABASE.Portability issues - extensions can be factored into a pluggable architecture to avoid this.

Supported Relational Database Systems

When examining vendor database products great care must be taken with their use of the word embedded. It is used with two different and conflicting meanings:

ContextMeaning of Embedded
MobileSuitable for systems with limited resources such as mobile phones and PDAs
IntegratedA database used locally and tightly integrated into a single application.

Note: Integrated databases do not generally support access by multiple processes. Qtopia is designed to cater for this by using a client server architecture in the case of Content, and by limiting access to a single process in other cases.

Database Systems and their support status are:

SystemVendorStatusEmbedded
SqliteSqlite.org & D. Richard HippSupported since Qtopia 4.0Mobile and Integrated
Mimer SQLMimer Information Technology AB, SwedenSupported since Qtopia 4.3Mobile

Sqlite Specific Notes

The Sqlite relational database system is one of the supported systems for Qtopia. In that system, there is a one-to-one correspondence between actual files on the file-system and Databases. To create a new database in Sqlite, a new database file is created on the file-system which encapsulates all data, relations and indexes.

Generally databases are created by a DML statement, for example the SQL statement

CREATE DATABASE Home_Database;

would create a new database in most SQL based relational database systems.

Application programs cannot or should not make assumptions about the location of the physical storage backing the database. That will depend on the configuration of the system and what vendor it was from.

Some database systems can use raw unformatted partitions (raw block devices), others create a number of files for indexes, journals and meta-data (eg. PostgreSQL), while yet others use a single flat-file structure (eg. SQLite).

Great care needs to be taken in all implementation code not to intermix the notion of Database and Database file, since this would create code that is unlikely to work with other systems.

Qtopia Database Schema

The Qtopia Database Schema is split into two separate collections of schema files, one for Content and one for PIM.

Other applications should maintain their own schema files following this model. The table names listed below should be considered as "Qtopia reserved words" when creating or modifying schema.

The main tables and their purpose are listed here:

TableDescription
Content Functions
contentContains meta data about Content items available to the device. Items include plain files, such as an MP3 music file, and contained or downloaded content for which there is no clear concept of a file, such as DRM content, or streamed content.
mapCategoryToContentContains relational mappings between categories and content. Using a mapping table allows multiple tags to be applied to a content item, and the same category to be applied to many contents.
locationLookuppaths for file based content, this is primarily a storage optimization to save the cost of storing the full path where it is repeated across a number of content items. The content table contains a foreignKey entry pointing to this table.
contentPropsArbitrary meta-data may be associated with content items via the strings stored in this table.
PIM and Content
categoriesLists names and translations of categories, which are tags used to label items in the Content system. The System function uses non-user visible categories to locate system resources such as certificates and system ring-tones. The end-user may create their own categories for sorting Content and PIM items. Two pre-loaded and specially treated categories are Business and Personal.
PIM Functions
contactsAddress book entries. This table has over 40 columns, including ones for "firstname" and "nickname".
emailaddressesEmail addresses for contacts. Linked to the contacts table by a foreign key.
contactphonenumbersPhone numbers for contacts. Linked to the contacts table by a foreign key.
appointmentsEvents forming part of the calendaring system.
tasksTasks and TODO entries, also part of the calendaring system.
*customEach of appointments, tasks and contacts has a freeform field that can be added to create extra data entries.
*categoriesEach of appointments, tasks and contacts has a category table specific to it.

This is not a full list of all tables. For comprehensive up-to-date details of the current schema refer to the files in the qtopia source tree under:

Meta-data and Versioning

The Content + PIM database contains embedded versioning information which shows the current version number of that database.

When the schema of that database is changed, the SQL statement files referred to in the resources listing in the previous section are updated so that the current schema version can be obtained by an SQL query.

Previously the mechanism for doing this was an entry PRAGMA user_version 100; in the file:

However this is Sqlite specific, and has been superseded in 4.3 by the schema_versions table, which contains:

The query SELECT MAX(schema_version) FROM schema_versions; will then return the current version number.

The PIM database also maintains a changelog table and a sqlsources table, which contain meta data about updates to the PIM data and identities of data sources. These are intended for use in managing data synchronisation and import/export.

Custom Databases and Applications

When creating applications which require access or modifications to the Qtopia database, follow the rules laid out in the Database Policy.

At this time there are no examples of Custom database requirements.

See also QContent, QContentSet, QPimRecord, and Document System.


Copyright © 2008 Nokia Trademarks
Qtopia 4.3.3