The GUI layer of the SQL module in Qt 4 has been entirely redesigned to work with Interview (Qt's new model/view classes). It consists of three model classes (QSqlQueryModel, QSqlTableModel, and QSqlRelationalTableModel) that can be used with Qt's view classes, notably QTableView.

General Overview

The Qt 4 SQL classes are divided into three layers:

• The database drivers
• The core SQL classes
• The GUI classes

The database drivers and the core SQL classes are mostly the same as in Qt 3. The database item models are new with Qt 4; they inherit from QAbstractItemModel and make it easy to present data from a database in a view class such as QListView, QTableView, and QTreeView.

The philosophy behind the Qt 4 SQL module is that it should be possible to use database models for rendering and editing data just like any other item models. By changing the model at run-time, you can decide whether you want to store your data in an SQL database or in, say, an XML file. This generic approach has the additional benefit that you don't need to know anything about SQL to display and edit data.

The Qt 4 SQL module includes three item models:

• QSqlQueryModel is a read-only model based on an arbitrary SQL query.
• QSqlTableModel is a read-write model that works on a single table.
• QSqlRelationalTableModel is a QSqlTableModel subclass with foreign key support.

Combined with Qt's view classes and Qt's default delegate class (QItemDelegate), the models offer a very powerful mechanism for accessing databases. For finer control on the rendering of the fields, you can subclass one of the predefined models, or even QAbstractItemDelegate or QItemDelegate if you need finer control.

You can also perform some customizations without subclassing. For example, you can sort a table using QSqlTableModel::sort(), and you can initialize new rows by connecting to the QSqlTableModel::primeInsert() signal.

One nice feature supported by the read-write models is the possibility to perform changes to the item model without affecting the database until QSqlTableModel::submitAll() is called. Changes can be dropped using QSqlTableModel::revertAll().

The new classes perform advantageously compared to the SQL module's GUI layer in Qt 3. Speed and memory improvements in the tool classes (especially QVariant, QString, and QMap) and in the SQL drivers contribute to making Qt 4 database applications more snappy.

See the QtSql module overview for a more complete introduction to Qt's SQL classes.

Example Code

The simplest way to present data from a database is to simply combine a QSqlQueryModel with a QTableView:

QSqlQueryModel model;
model.setQuery("select * from person");

QTableView view;
view.setModel(&model);
view.show();

To present the contents of a single table, we can use QSqlTableModel instead:

QSqlTableModel model;
model.setTable("person");
model.select();

QTableView view;
view.setModel(&model);
view.show();

In practice, it's common that we need to customize the rendering of a field in the database. In that case, we can create our own model based on QSqlQueryModel. The next code snippet shows a custom model that prepends '#' to the value in field 0 and converts the value in field 2 to uppercase:

class CustomSqlModel : public QSqlQueryModel
{
    Q_OBJECT

public:
    CustomSqlModel(QObject *parent = 0);

    QVariant data(const QModelIndex &item, int role) const;
};

QVariant CustomSqlModel::data(const QModelIndex &index, int role) const
{
    QVariant value = QSqlQueryModel::data(index, role);
    if (value.isValid() && role == Qt::DisplayRole) {
        if (index.column() == 0)
            return value.toString().prepend("#");
        else if (index.column() == 2)
            return value.toString().toUpper();
    }
    if (role == Qt::TextColorRole && index.column() == 1)
        return QVariant::fromValue(QColor(Qt::blue));
    return value;
}

It is also possible to subclass QSqlQueryModel to add support for editing. This is done by reimplementing QAbstractItemModel::flags() to specify which database fields are editable and QAbstractItemModel::setData() to modify the database. Here's an example of a setData() reimplementation that changes the first or last name of a person:

bool EditableSqlModel::setData(const QModelIndex &index, const QVariant &value, int /* role */)
{
    if (index.column() < 1 || index.column() > 2)
        return false;

    QModelIndex primaryKeyIndex = QSqlQueryModel::index(index.row(), 0);
    int id = data(primaryKeyIndex).toInt();

    clear();

    bool ok;
    if (index.column() == 1) {
        ok = setFirstName(id, value.toString());
    } else {
        ok = setLastName(id, value.toString());
    }
    refresh();
    return ok;
}

It relies on helper functions called setFirstName() and setLastName(), which execute an update. Here's setFirstName():

bool EditableSqlModel::setFirstName(int personId, const QString &firstName)
{
    QSqlQuery query;
    query.prepare("update person set firstname = ? where id = ?");
    query.addBindValue(firstName);
    query.addBindValue(personId);
    return query.exec();
}

See Qt's examples/sql directory for more examples.

Comparison with Qt 3

The core SQL database classes haven't changed so much since Qt 3. Here's a list of the main changes:

• QSqlDatabase is now value-based instead of pointer-based.
• QSqlFieldInfo and QSqlRecordInfo has been merged into QSqlField and QSqlRecord.
• The SQL query generation has been moved into the drivers. This makes it possible to use non-standard SQL extensions. It also opens the door to non-SQL databases.

The GUI-related database classes have been entirely redesigned. The QSqlCursor abstraction has been replaced with QSqlQueryModel and QSqlTableModel; QSqlEditorFactory is replaced by QAbstractItemDelegate; QDataTable is replaced by QTableView. The old classes are part of the Qt3Support library to aid porting to Qt 4.