http://www.wiki.crossplatform.ru/index.php?title=Data_Models:_SQL_Table_vs._Flat_File&feed=atom&action=historyData Models: SQL Table vs. Flat File - История изменений2024-03-28T23:21:55ZИстория изменений этой страницы в викиMediaWiki 1.15.1http://www.wiki.crossplatform.ru/index.php?title=Data_Models:_SQL_Table_vs._Flat_File&diff=6262&oldid=prevLit-uriy в 03:39, 20 февраля 20092009-02-20T03:39:14Z<p></p>
<table style="background-color: white; color:black;">
<col class='diff-marker' />
<col class='diff-content' />
<col class='diff-marker' />
<col class='diff-content' />
<tr valign='top'>
<td colspan='2' style="background-color: white; color:black;">← Предыдущая</td>
<td colspan='2' style="background-color: white; color:black;">Версия 03:39, 20 февраля 2009</td>
</tr>
<tr><td colspan="2" class="diff-lineno">Строка 1:</td>
<td colspan="2" class="diff-lineno">Строка 1:</td></tr>
<tr><td class='diff-marker'>-</td><td style="background: #ffa; color:black; font-size: smaller;"><div>{{<del class="diffchange diffchange-inline">menu_Qt_Издания</del>}}</div></td><td class='diff-marker'>+</td><td style="background: #cfc; color:black; font-size: smaller;"><div>{{<ins class="diffchange diffchange-inline">Панель навигации по Qt Quarterly|Выпуск 15</ins>}}</div></td></tr>
<tr><td class='diff-marker'> </td><td style="background: #eee; color:black; font-size: smaller;"><div>by Mark Summerfield</div></td><td class='diff-marker'> </td><td style="background: #eee; color:black; font-size: smaller;"><div>by Mark Summerfield</div></td></tr>
<tr><td class='diff-marker'> </td><td style="background: #eee; color:black; font-size: smaller;"><div><blockquote>'''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.'''</div></td><td class='diff-marker'> </td><td style="background: #eee; color:black; font-size: smaller;"><div><blockquote>'''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.'''</div></td></tr>
<!-- diff generator: internal 2024-03-28 23:21:57 -->
</table>Lit-uriyhttp://www.wiki.crossplatform.ru/index.php?title=Data_Models:_SQL_Table_vs._Flat_File&diff=5686&oldid=prevViGOur: Новая: {{menu_Qt_Издания}} by Mark Summerfield <blockquote>'''Qt 4's item view classes make it easy topresent data from different data sources, including SQL databases, inthe same view....2009-02-10T09:00:40Z<p>Новая: {{menu_Qt_Издания}} by Mark Summerfield <blockquote>'''Qt 4's item view classes make it easy topresent data from different data sources, including SQL databases, inthe same view....</p>
<p><b>Новая страница</b></p><div>{{menu_Qt_Издания}}<br />
by Mark Summerfield<br />
<blockquote>'''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.'''<br />
__TOC__<br />
</blockquote><br />
<br />
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.<br />
<br />
[[Image:qq15-mailview.png|center]]<br />
<br />
Creating a table view is simple, as this snippet from the window'sconstructor shows:<br />
<source lang="cpp-qt"><br />
view = new QTableView;<br />
view->setAlternatingRowColors(true);</source> <br />
Since we will be accessing a SQL database, it is convenient to set thedatabase driver type once only in the window's constructor:<br />
<source lang="cpp-qt"><br />
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");</source> <br />
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:<br />
<source lang="cpp-qt"><br />
if (fileName.endsWith(".db"))<br />
setSqlModel(fileName);<br />
else if (fileName.endsWith(".dat"))<br />
setFlatFileModel(fileName);</source> <br />
<br />
<br />
<div id="usingsqlmodels"></div><br />
== Using SQL Models ==<br />
Here's the implementation of <tt>setSqlModel()</tt>:<br />
<source lang="cpp-qt"><br />
void MailView::setSqlModel(const QString &amp;fileName)<br />
{<br />
QSqlDatabase db = QSqlDatabase::database();<br />
if (db.isValid())<br />
db.setDatabaseName(fileName);<br />
if (!db.isValid() || !db.open()) {<br />
// Give error message, e.g., using QMessageBox<br />
return;<br />
}<br />
<br />
QSqlTableModel *model = new QSqlTableModel; // A<br />
model->setTable("messages"); // B<br />
model->select(); // C<br />
<br />
model->setHeaderData(0, Qt::Horizontal, tr("ID"));<br />
model->setHeaderData(1, Qt::Horizontal,<br />
tr("Subject"));<br />
...<br />
model->setHeaderData(5, Qt::Horizontal, tr("Body"));<br />
view->setModel(model);<br />
}</source> <br />
The [[Qt:Документация_4.3.2/qsqltablemodel | QSqlTableModel]] class is a data model that fetches its datafrom a given SQL table. The <tt>select()</tt> call effectively does<tt>SELECT * FROM messages</tt>. We've chosen to set our own column headernames; if we omitted the <tt>setHeaderData()</tt> calls the databasetable's field names would be used instead. When we call<tt>setModel()</tt>, the view automatically clears any existing data andpopulates itself from the model.<br />
<br />
Using this first approach we get a certain amount of functionality forfree, for example, we can sort by a particular column by calling<tt>sortByColumn()</tt> on the view. Another approach would be to call<tt>setSort()</tt> on the model.<br />
<br />
An alternative that provides finer control is to replace the linescommented <tt>A</tt>, <tt>B</tt>, and <tt>C</tt> asfollows:<br />
<source lang="cpp-qt"><br />
QSqlQueryModel *model = new QSqlQueryModel;<br />
model->setQuery("SELECT id, subject, sender, recipient, "<br />
"date, body FROM messages");</source> <br />
If we use this approach, the view cannot sort for us, but it is easyenough, and potentially much more flexible, to call <tt>model()</tt> on theview to retrieve the model, and then to call <tt>setQuery()</tt> with an<tt>ORDER BY</tt> clause. Similarly, we could restrict the records that wereavailable to the view by using a <tt>WHERE</tt> clause: This can also beachieved in a [[Qt:Документация_4.3.2/qsqltablemodel | QSqlTableModel]] by calling <tt>setFilter()</tt>.<br />
<br />
<div id="usingacustomflatfilemodel"></div><br />
== Using a Custom Flat File Model ==<br />
Setting the view to use a custom model is easy because we have put allthe functionality into the model subclass itself:<br />
<source lang="cpp-qt"><br />
void MailView::setFlatFileModel(const QString &amp;fileName)<br />
{<br />
view->setModel(new FlatFileModel(fileName));<br />
}</source> <br />
We've chosen to subclass our <tt>FlatFileModel</tt> from[[Qt:Документация_4.3.2/qabstracttablemodel | 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.<br />
<br />
The <tt>.dat</tt> 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.<br />
<br />
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.<br />
<br />
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.<br />
<br />
For read-only models, we only need to reimplement <tt>data()</tt>,<tt>rowCount()</tt>, and <tt>columnCount()</tt>, but implementing <tt>headerData()</tt>is recommended. All these functions are <tt>const</tt> so they can only call<tt>const</tt> functions themselves. Let's begin by looking at the headerfile:<br />
<source lang="cpp-qt"><br />
class FlatFileModel : public QAbstractTableModel<br />
{<br />
public:<br />
FlatFileModel(const QString &amp;fileName,<br />
QObject *parent = 0);<br />
<br />
QVariant data(const QModelIndex &amp;index,<br />
int role) const;<br />
QVariant headerData(int section,<br />
Qt::Orientation orientation,<br />
int role) const;<br />
int rowCount(const QModelIndex &amp;parent) const;<br />
int columnCount(const QModelIndex &amp;) const<br />
{ return 6; }<br />
<br />
private:<br />
bool updateRequired() const;<br />
void updateOffsets() const;<br />
<br />
mutable QFile file;<br />
mutable QDateTime modified;<br />
mutable QVector<int> offsets;<br />
};</source> <br />
Because we know that our data files always have six fields, we haveimplemented <tt>columnCount()</tt> inline. The private function<tt>updateRequired()</tt> is used to see if the file offsets need to beupdated, and the <tt>updateOffsets()</tt> function performs the updating.<br />
<br />
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 <tt>mutable</tt> because they are updated inside<tt>const</tt> functions.<br />
<br />
We are now ready to look at the implementations in detail.<br />
<source lang="cpp-qt"><br />
FlatFileModel::FlatFileModel(const QString &amp;fileName,<br />
QObject *parent)<br />
: QAbstractTableModel(parent)<br />
{<br />
file.setFileName(fileName);<br />
updateOffsets();<br />
}</source> <br />
When a new model is constructed we only need to set the file name andupdate the offsets.<br />
<source lang="cpp-qt"><br />
void FlatFileModel::updateOffsets() const<br />
{<br />
const int ChunkSize = 50;<br />
offsets.clear();<br />
QFileInfo finfo(file);<br />
qint64 size = finfo.size();<br />
if (!size || !file.open(QIODevice::ReadOnly))<br />
return;<br />
modified = finfo.lastModified();<br />
offsets.append(0);<br />
qint64 offset = 0;<br />
while (size) {<br />
QByteArray bytes = file.read(ChunkSize);<br />
if (bytes.isEmpty())<br />
break;<br />
size -= bytes.size();<br />
qint64 i = bytes.indexOf("\nID: ");<br />
if (i != -1)<br />
offsets.append(offset + i);<br />
offset += bytes.size();<br />
}<br />
file.close();<br />
offsets.append(finfo.size());<br />
}</source> <br />
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 <tt>ChunkSize</tt> 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 (<tt>0</tt>). We then read the file in chunks, searching for "ID" fields and appending their offsets to the <tt>offsets</tt> vector. Atthe end we append an additional offset, the end of the file, since thisis convenient in the <tt>data()</tt> function.<br />
<source lang="cpp-qt"><br />
QVariant FlatFileModel::data(const QModelIndex &amp;index,<br />
int role) const<br />
{<br />
if (updateRequired()) updateOffsets();<br />
<br />
if (!index.isValid() || index.row() < 0<br />
|| index.row() >= offsets.size() - 1<br />
|| role != Qt::DisplayRole)<br />
return QVariant();<br />
<br />
if (!file.open( QIODevice::ReadOnly))<br />
return QVariant();<br />
qint64 offset = offsets.at(index.row());<br />
qint64 length = offsets.at(index.row() + 1) - offset;<br />
file.seek(offset);<br />
QByteArray bytes = file.read(length);<br />
file.close();<br />
if (bytes.size() != length)<br />
return QVariant();<br />
<br />
QString record = QLatin1String(bytes.data());<br />
QString key;<br />
switch (index.column()) {<br />
case 0: key = "ID: "; break;<br />
case 1: key = "\nSUBJECT: "; break;<br />
...<br />
case 5: key = "\n\n\t"; break;<br />
default: return QVariant();<br />
}<br />
int i = record.indexOf(key);<br />
if (i != -1) {<br />
i += key.size();<br />
if (index.column() != 5) {<br />
int j = record.indexOf("\n", i);<br />
if (j != -1)<br />
return record.mid(i, j - i + 1);<br />
} else<br />
return record.mid(i).replace("\n\t", "\n");<br />
}<br />
return QVariant();<br />
}</source> <br />
The <tt>data()</tt> 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 [[Qt:Документация_4.3.2/qvariant | QVariant]].<br />
<br />
The number of records in our dataset is <tt>offsets.size() - 1</tt>; this isbecause we appended an extra offset at the end. We return a[[Qt:Документация_4.3.2/qvariant | 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 <tt>seek()</tt> to the record and retrievethe data we need, then we convert it to a [[Qt:Документация_4.3.2/qstring | QString]] ready fordecomposing.<br />
<br />
Each field has a piece of unique text that precedes it. We choose thetext to search for (the <tt>key</tt>), depending on which <tt>index.column()</tt>the view has asked for. Most of the fields are just one line long so wecan extract their data by using <tt>mid()</tt> 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.<br />
<source lang="cpp-qt"><br />
QVariant FlatFileModel::headerData(int section,<br />
Qt::Orientation orientation, int role) const<br />
{<br />
if (role != Qt::DisplayRole)<br />
return QVariant();<br />
<br />
if (orientation == Qt::Horizontal) {<br />
switch (section) {<br />
case 0: return tr("ID");<br />
case 1: return tr("Subject");<br />
...<br />
case 5: return tr("Body");<br />
default: return QVariant();<br />
}<br />
} else<br />
return QString("%1").arg(section + 1);<br />
}</source> <br />
If the view requests header data, that is labels for columns or rows,our <tt>headerData()</tt> 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 <tt>updateRequired()</tt>because our header data is fixed for our file format.<br />
<source lang="cpp-qt"><br />
int FlatFileModel::rowCount(const QModelIndex &amp;) const<br />
{<br />
if (updateRequired()) updateOffsets();<br />
return offsets.size() - 1;<br />
}</source> <br />
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.<br />
<source lang="cpp-qt"><br />
bool FlatFileModel::updateRequired() const<br />
{<br />
return modified != QFileInfo(file).lastModified();<br />
}</source> <br />
We assume that the offsets need updating if the file's last modifiedtime is different from when we last scanned it.<br />
<br />
<div id="conclusion"></div><br />
== Conclusion ==<br />
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.<br />
<br />
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.<br />
<br />
If we need foreign keys, we can use Qt's [[Qt:Документация_4.3.2/qsqlrelationaltablemodel | QSqlRelationalTableModel]],which provides foreign key support, instead of its superclass[[Qt:Документация_4.3.2/qsqltablemodel | QSqlTableModel]].<br />
<br />
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.<br />
<br />
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[[Qt:Документация_4.3.2/qvariant | 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.</div>ViGOur