Data Models: SQL Table vs. Flat File
Материал из Wiki.crossplatform.ru
| 
|
| Qt Quarterly | Выпуск 15 | Документация | |
by Mark Summerfield
Qt 4's item view classes make it easy topresent data from different data sources, including SQL databases, inthe same view. These classes use the model/view approach, andin this article we present a view thatcan be populated with data from either a SQL table or from a custom flatfile model.
Содержание
Imagine that we are managing email archives and are migrating fromflat files to a database. During the transition, we need to be able toview emails from either type of archive.
Creating a table view is simple, as this snippet from the window'sconstructor shows:
view = new QTableView; view->setAlternatingRowColors(true);
Since we will be accessing a SQL database, it is convenient to set thedatabase 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 witha file open dialog. If they choose a file we use its suffix to determinewhich 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 datafrom a given SQL table. The select() call effectively doesSELECT * FROM messages. We've chosen to set our own column headernames; if we omitted the setHeaderData() calls the databasetable's field names would be used instead. When we callsetModel(), the view automatically clears any existing data andpopulates itself from the model.
Using this first approach we get a certain amount of functionality forfree, for example, we can sort by a particular column by callingsortByColumn() on the view. Another approach would be to callsetSort() on the model.
An alternative that provides finer control is to replace the linescommented A, B, and C asfollows:
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 easyenough, and potentially much more flexible, to call model() on theview to retrieve the model, and then to call setQuery() with anORDER BY clause. Similarly, we could restrict the records that wereavailable to the view by using a WHERE clause: This can also beachieved 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 allthe 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 datastructure. Qt 4's item view abstraction means that from the view'sviewpoint, the actual data source is irrelevant.
The .dat files used to store our emails are plain text Latin-1encoded files. Each email is a variable-length "record", which storesthe 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 ormore tab indented lines of the body of the message.
Qt's views are smart enough to only request the data they actually needto display, so even very large datasets need not consume vast amounts ofmemory. Since a message file is potentially huge we don't want to justread the whole file into an in-memory data structure since that wouldthrow 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 therecord from its offset up to the offset of the following record (or tothe end of the file for the last record). Once we have a record's datain memory we can then decompose it into its parts and supply the datarequested by the view.
Occasionally, the file might be changed while we are using it, forexample, a new message might be appended. We could lock the file, butthis may be inconvenient if we are reading it for a long time. Soinstead 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 callconst functions themselves. Let's begin by looking at the headerfile:
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 haveimplemented columnCount() inline. The private functionupdateRequired() is used to see if the file offsets need to beupdated, and the updateOffsets() function performs the updating.
We keep a single file object that we use for reading, along with thelast modified date/time, and a vector of offsets. We have had todeclare all of these mutable because they are updated insideconst 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 andupdate 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 smallerthan the smallest possible record size. Our first action is to clear theexisting offsets. We then check the file, doing nothing andreturning if it is empty or if we cannot open it. If all is well werecord the file's last modified time and append the first record'soffset (0). We then read the file in chunks, searching for "ID" fields and appending their offsets to the offsets vector. Atthe end we append an additional offset, the end of the file, since thisis 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 themodel. We begin by updating the offsets if necessary. If the model indexwe've been passed isn't valid, or has an out-of-range row, or the roleisn't for display, we do nothing and return an invalid QVariant.
The number of records in our dataset is offsets.size() - 1; this isbecause we appended an extra offset at the end. We return a QVariant() if we cannot open the file. Next we retrieve the offsetof the start of the requested record, and calculate its length using theoffset of the following record (or of the end of the file if we areretrieving the last record). We seek() to the record and retrievethe data we need, then we convert it to a QString ready fordecomposing.
Each field has a piece of unique text that precedes it. We choose thetext 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 wecan extract their data by using mid() from the character after thekey up to the newline. For the body of the message, which comes at theend, we extract from its beginning until the end of the record, and atthe same time we remove the leading tabs that are part of the fileformat, 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 wesimply return the column names we want to be used, and for rows we givea row number offset by 1 so that the first row number visible to theuser 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'verecorded; but we have to make sure the offsets are up-to-date beforereturning.
bool FlatFileModel::updateRequired() const { return modified != QFileInfo(file).lastModified(); }
We assume that the offsets need updating if the file's last modifiedtime is different from when we last scanned it.
[править] Conclusion
In this article we have used a read-only view and focused on theunderlying models. We haven't needed to edit data, show foreignkeys, or present custom views.
If we required editable data, we would need to implement a few morefunctions in our models, and could either use Qt's default editors orreimplement the delegate and provide our own custom editors for some orall of our item types. Compare Qt 4's versatility to Qt 3 where customediting 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 easilybe achieved---and will require no changes to the models we use to supplyour custom view with data.
Qt 4's item view classes provide a uniform means of handling data in Qtapplications, irrespective of the data source. The use of Qt's versatile QVariant data type makes passing data both flexible andconvenient. Standard models and views are provided, and as we haveseen in this article, creating custom models is straightforward.
