Trolltech | Documentation | Qt Quarterly

Data Models: SQL Table vs. Flat File
by Mark Summerfield
Qt 4's item view classes make it easy to present data from different data sources, including SQL databases, in the same view. These classes use the model/view approach, and in this article we present a view that can be populated with data from either a SQL table or from a custom flat file model.

Imagine that we are managing email archives and are migrating from flat files to a database. During the transition, we need to be able to view emails from either type of archive.

Mailview

Creating a table view is simple, as this snippet from the window's constructor shows:

    view = new QTableView;
    view->setAlternatingRowColors(true);

Since we will be accessing a SQL database, it is convenient to set the database driver type once only in the window's constructor:

    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");

When the user clicks the Open button, they will be presented with a file open dialog. If they choose a file we use its suffix to determine which model to use with the table view:

    if (fileName.endsWith(".db"))
        setSqlModel(fileName);
    else if (fileName.endsWith(".dat"))
        setFlatFileModel(fileName);

Using SQL Models

Here's the implementation of setSqlModel():

    void MailView::setSqlModel(const QString &fileName)
    {
        QSqlDatabase db = QSqlDatabase::database();
        if (db.isValid())
            db.setDatabaseName(fileName);
        if (!db.isValid() || !db.open()) {
            // Give error message, e.g., using QMessageBox
            return;
        }
    
        QSqlTableModel *model = new QSqlTableModel; // A
        model->setTable("messages");                // B
        model->select();                            // C
    
        model->setHeaderData(0, Qt::Horizontal, tr("ID"));
        model->setHeaderData(1, Qt::Horizontal,
                             tr("Subject"));
        ...
        model->setHeaderData(5, Qt::Horizontal, tr("Body"));
        view->setModel(model);
    }

The QSqlTableModel class is a data model that fetches its data from a given SQL table. The select() call effectively does SELECT * FROM messages. We've chosen to set our own column header names; if we omitted the setHeaderData() calls the database table's field names would be used instead. When we call setModel(), the view automatically clears any existing data and populates itself from the model.

Using this first approach we get a certain amount of functionality for free, for example, we can sort by a particular column by calling sortByColumn() on the view. Another approach would be to call setSort() on the model.

An alternative that provides finer control is to replace the lines commented A, B, and C as follows:

    QSqlQueryModel *model = new QSqlQueryModel;
    model->setQuery("SELECT id, subject, sender, recipient, "
                    "date, body FROM messages");

If we use this approach, the view cannot sort for us, but it is easy enough, and potentially much more flexible, to call model() on the view to retrieve the model, and then to call setQuery() with an ORDER BY clause. Similarly, we could restrict the records that were available to the view by using a WHERE clause: This can also be achieved in a QSqlTableModel by calling setFilter().

Using a Custom Flat File Model

Setting the view to use a custom model is easy because we have put all the functionality into the model subclass itself:

    void MailView::setFlatFileModel(const QString &fileName)
    {
        view->setModel(new FlatFileModel(fileName));
    }

We've chosen to subclass our FlatFileModel from QAbstractTableModel since that most closely matches our data structure. Qt 4's item view abstraction means that from the view's viewpoint, the actual data source is irrelevant.

The .dat files used to store our emails are plain text Latin-1 encoded files. Each email is a variable-length "record", which stores the ID, subject, from, to, and date fields each on their own line, e.g., "SUBJECT: This is about", followed by a blank line and then zero or more tab indented lines of the body of the message.

Qt's views are smart enough to only request the data they actually need to display, so even very large datasets need not consume vast amounts of memory. Since a message file is potentially huge we don't want to just read the whole file into an in-memory data structure since that would throw away the item view classes' careful optimization of memory use. Instead we will scan the file looking for each record's "ID:" line, and record the offset into the file (as if it were a binary file). Then, whenever a record is requested we will open the file and read in the record from its offset up to the offset of the following record (or to the end of the file for the last record). Once we have a record's data in memory we can then decompose it into its parts and supply the data requested by the view.

Occasionally, the file might be changed while we are using it, for example, a new message might be appended. We could lock the file, but this may be inconvenient if we are reading it for a long time. So instead we simply record the last modified time and if this changes, re-scan the file to update the offsets.

For read-only models, we only need to reimplement data(), rowCount(), and columnCount(), but implementing headerData() is recommended. All these functions are const so they can only call const functions themselves. Let's begin by looking at the header file:

    class FlatFileModel : public QAbstractTableModel
    {
    public:
        FlatFileModel(const QString &fileName,
                      QObject *parent = 0);
    
        QVariant data(const QModelIndex &index,
                      int role) const;
        QVariant headerData(int section,
                            Qt::Orientation orientation,
                            int role) const;
        int rowCount(const QModelIndex &parent) const;
        int columnCount(const QModelIndex &) const
                { return 6; }
    
    private:
        bool updateRequired() const;
        void updateOffsets() const;
    
        mutable QFile file;
        mutable QDateTime modified;
        mutable QVector<int> offsets;
    };

Because we know that our data files always have six fields, we have implemented columnCount() inline. The private function updateRequired() is used to see if the file offsets need to be updated, and the updateOffsets() function performs the updating.

We keep a single file object that we use for reading, along with the last modified date/time, and a vector of offsets. We have had to declare all of these mutable because they are updated inside const functions.

We are now ready to look at the implementations in detail.

    FlatFileModel::FlatFileModel(const QString &fileName,
                                 QObject *parent)
        : QAbstractTableModel(parent)
    {
        file.setFileName(fileName);
        updateOffsets();
    }

When a new model is constructed we only need to set the file name and update the offsets.

    void FlatFileModel::updateOffsets() const
    {
        const int ChunkSize = 50;
        offsets.clear();
        QFileInfo finfo(file);
        qint64 size = finfo.size();
        if (!size || !file.open(QIODevice::ReadOnly))
            return;
        modified = finfo.lastModified();
        offsets.append(0);
        qint64 offset = 0;
        while (size) {
            QByteArray bytes = file.read(ChunkSize);
            if (bytes.isEmpty())
                break;
            size -= bytes.size();
            qint64 i = bytes.indexOf("\nID: ");
            if (i != -1)
                offsets.append(offset + i);
            offset += bytes.size();
        }
        file.close();
        offsets.append(finfo.size());
    }

We are treating a text file as a binary file, and reading it as bytes. This works because we know that we are using an 8-bit encoding (Latin-1). We've set the ChunkSize to 50 bytes; this must be smaller than the smallest possible record size. Our first action is to clear the existing offsets. We then check the file, doing nothing and returning if it is empty or if we cannot open it. If all is well we record the file's last modified time and append the first record's offset (0). We then read the file in chunks, searching for "ID" fields and appending their offsets to the offsets vector. At the end we append an additional offset, the end of the file, since this is convenient in the data() function.

    QVariant FlatFileModel::data(const QModelIndex &index,
                                 int role) const
    {
        if (updateRequired()) updateOffsets();
    
        if (!index.isValid() || index.row() < 0
                || index.row() >= offsets.size() - 1
                || role != Qt::DisplayRole)
            return QVariant();
    
        if (!file.open(QIODevice::ReadOnly))
            return QVariant();
        qint64 offset = offsets.at(index.row());
        qint64 length = offsets.at(index.row() + 1) - offset;
        file.seek(offset);
        QByteArray bytes = file.read(length);
        file.close();
        if (bytes.size() != length)
            return QVariant();
    
        QString record = QLatin1String(bytes.data());
        QString key;
        switch (index.column()) {
            case 0: key = "ID: "; break;
            case 1: key = "\nSUBJECT: "; break;
            ...
            case 5: key = "\n\n\t"; break;
            default: return QVariant();
        }
        int i = record.indexOf(key);
        if (i != -1) {
            i += key.size();
            if (index.column() != 5) {
                int j = record.indexOf("\n", i);
                if (j != -1)
                    return record.mid(i, j - i + 1);
            } else
                return record.mid(i).replace("\n\t", "\n");
        }
        return QVariant();
    }

The data() function is called by the view to retrieve data from the model. We begin by updating the offsets if necessary. If the model index we've been passed isn't valid, or has an out-of-range row, or the role isn't for display, we do nothing and return an invalid QVariant.

The number of records in our dataset is offsets.size() - 1; this is because we appended an extra offset at the end. We return a QVariant() if we cannot open the file. Next we retrieve the offset of the start of the requested record, and calculate its length using the offset of the following record (or of the end of the file if we are retrieving the last record). We seek() to the record and retrieve the data we need, then we convert it to a QString ready for decomposing.

Each field has a piece of unique text that precedes it. We choose the text to search for (the key), depending on which index.column() the view has asked for. Most of the fields are just one line long so we can extract their data by using mid() from the character after the key up to the newline. For the body of the message, which comes at the end, we extract from its beginning until the end of the record, and at the same time we remove the leading tabs that are part of the file format, but not part of the data itself.

    QVariant FlatFileModel::headerData(int section,
                Qt::Orientation orientation, int role) const
    {
        if (role != Qt::DisplayRole)
            return QVariant();
    
        if (orientation == Qt::Horizontal) {
            switch (section) {
            case 0: return tr("ID");
            case 1: return tr("Subject");
            ...
            case 5: return tr("Body");
            default: return QVariant();
            }
        } else
            return QString("%1").arg(section + 1);
    }

If the view requests header data, that is labels for columns or rows, our headerData() function provides appropriate text. For columns we simply return the column names we want to be used, and for rows we give a row number offset by 1 so that the first row number visible to the user is 1 not 0. We do not need to call updateRequired() because our header data is fixed for our file format.

    int FlatFileModel::rowCount(const QModelIndex &) const
    {
        if (updateRequired()) updateOffsets();
        return offsets.size() - 1;
    }

The row count is simply 1 less than the number of offsets we've recorded; but we have to make sure the offsets are up-to-date before returning.

    bool FlatFileModel::updateRequired() const
    {
        return modified != QFileInfo(file).lastModified();
    }

We assume that the offsets need updating if the file's last modified time is different from when we last scanned it.

Conclusion

In this article we have used a read-only view and focused on the underlying models. We haven't needed to edit data, show foreign keys, or present custom views.

If we required editable data, we would need to implement a few more functions in our models, and could either use Qt's default editors or reimplement the delegate and provide our own custom editors for some or all of our item types. Compare Qt 4's versatility to Qt 3 where custom editing was only supported in the SQL classes.

If we need foreign keys, we can use Qt's QSqlRelationalTableModel, which provides foreign key support, instead of its superclass QSqlTableModel.

If our data needed to be presented using a custom view, this can easily be achieved---and will require no changes to the models we use to supply our custom view with data.

Qt 4's item view classes provide a uniform means of handling data in Qt applications, irrespective of the data source. The use of Qt's versatile QVariant data type makes passing data both flexible and convenient. Standard models and views are provided, and as we have seen in this article, creating custom models is straightforward.


This document is licensed under the Creative Commons Attribution-Share Alike 2.5 license.

Copyright © 2005 Trolltech Trademarks