1/****************************************************************************
2**
3** Copyright (C) 2016 The Qt Company Ltd.
4** Contact: https://www.qt.io/licensing/
5**
6** This file is part of the QtSql module of the Qt Toolkit.
7**
8** $QT_BEGIN_LICENSE:LGPL$
9** Commercial License Usage
10** Licensees holding valid commercial Qt licenses may use this file in
11** accordance with the commercial license agreement provided with the
12** Software or, alternatively, in accordance with the terms contained in
13** a written agreement between you and The Qt Company. For licensing terms
14** and conditions see https://www.qt.io/terms-conditions. For further
15** information use the contact form at https://www.qt.io/contact-us.
16**
17** GNU Lesser General Public License Usage
18** Alternatively, this file may be used under the terms of the GNU Lesser
19** General Public License version 3 as published by the Free Software
20** Foundation and appearing in the file LICENSE.LGPL3 included in the
21** packaging of this file. Please review the following information to
22** ensure the GNU Lesser General Public License version 3 requirements
23** will be met: https://www.gnu.org/licenses/lgpl-3.0.html.
24**
25** GNU General Public License Usage
26** Alternatively, this file may be used under the terms of the GNU
27** General Public License version 2.0 or (at your option) the GNU General
28** Public license version 3 or any later version approved by the KDE Free
29** Qt Foundation. The licenses are as published by the Free Software
30** Foundation and appearing in the file LICENSE.GPL2 and LICENSE.GPL3
31** included in the packaging of this file. Please review the following
32** information to ensure the GNU General Public License requirements will
33** be met: https://www.gnu.org/licenses/gpl-2.0.html and
34** https://www.gnu.org/licenses/gpl-3.0.html.
35**
36** $QT_END_LICENSE$
37**
38****************************************************************************/
39
40#include "qsqlquerymodel.h"
41#include "qsqlquerymodel_p.h"
42
43#include <qdebug.h>
44#include <qsqldriver.h>
45#include <qsqlfield.h>
46
47QT_BEGIN_NAMESPACE
48
49#define QSQL_PREFETCH 255
50
51void QSqlQueryModelPrivate::prefetch(int limit)
52{
53 Q_Q(QSqlQueryModel);
54
55 if (atEnd || limit <= bottom.row() || bottom.column() == -1)
56 return;
57
58 QModelIndex newBottom;
59 const int oldBottomRow = qMax(bottom.row(), 0);
60
61 // try to seek directly
62 if (query.seek(limit)) {
63 newBottom = q->createIndex(limit, bottom.column());
64 } else {
65 // have to seek back to our old position for MS Access
66 int i = oldBottomRow;
67 if (query.seek(i)) {
68 while (query.next())
69 ++i;
70 newBottom = q->createIndex(i, bottom.column());
71 } else {
72 // empty or invalid query
73 newBottom = q->createIndex(-1, bottom.column());
74 }
75 atEnd = true; // this is the end.
76 }
77 if (newBottom.row() >= 0 && newBottom.row() > bottom.row()) {
78 q->beginInsertRows(QModelIndex(), bottom.row() + 1, newBottom.row());
79 bottom = newBottom;
80 q->endInsertRows();
81 } else {
82 bottom = newBottom;
83 }
84}
85
86QSqlQueryModelPrivate::~QSqlQueryModelPrivate()
87{
88}
89
90void QSqlQueryModelPrivate::initColOffsets(int size)
91{
92 colOffsets.resize(size);
93 memset(colOffsets.data(), 0, colOffsets.size() * sizeof(int));
94}
95
96int QSqlQueryModelPrivate::columnInQuery(int modelColumn) const
97{
98 if (modelColumn < 0 || modelColumn >= rec.count() || !rec.isGenerated(modelColumn) || modelColumn >= colOffsets.size())
99 return -1;
100 return modelColumn - colOffsets[modelColumn];
101}
102
103/*!
104 \class QSqlQueryModel
105 \brief The QSqlQueryModel class provides a read-only data model for SQL
106 result sets.
107
108 \ingroup database
109 \inmodule QtSql
110
111 QSqlQueryModel is a high-level interface for executing SQL
112 statements and traversing the result set. It is built on top of
113 the lower-level QSqlQuery and can be used to provide data to
114 view classes such as QTableView. For example:
115
116 \snippet sqldatabase/sqldatabase.cpp 16
117
118 We set the model's query, then we set up the labels displayed in
119 the view header.
120
121 QSqlQueryModel can also be used to access a database
122 programmatically, without binding it to a view:
123
124 \snippet sqldatabase/sqldatabase.cpp 21
125
126 The code snippet above extracts the \c salary field from record 4 in
127 the result set of the query \c{SELECT * from employee}. Assuming
128 that \c salary is column 2, we can rewrite the last line as follows:
129
130 \snippet sqldatabase/sqldatabase.cpp 22
131
132 The model is read-only by default. To make it read-write, you
133 must subclass it and reimplement setData() and flags(). Another
134 option is to use QSqlTableModel, which provides a read-write
135 model based on a single database table.
136
137 The \l{querymodel} example illustrates how to use
138 QSqlQueryModel to display the result of a query. It also shows
139 how to subclass QSqlQueryModel to customize the contents of the
140 data before showing it to the user, and how to create a
141 read-write model based on QSqlQueryModel.
142
143 If the database doesn't return the number of selected rows in
144 a query, the model will fetch rows incrementally.
145 See fetchMore() for more information.
146
147 \sa QSqlTableModel, QSqlRelationalTableModel, QSqlQuery,
148 {Model/View Programming}, {Query Model Example}
149*/
150
151/*!
152 Creates an empty QSqlQueryModel with the given \a parent.
153 */
154QSqlQueryModel::QSqlQueryModel(QObject *parent)
155 : QAbstractTableModel(*new QSqlQueryModelPrivate, parent)
156{
157}
158
159/*! \internal
160 */
161QSqlQueryModel::QSqlQueryModel(QSqlQueryModelPrivate &dd, QObject *parent)
162 : QAbstractTableModel(dd, parent)
163{
164}
165
166/*!
167 Destroys the object and frees any allocated resources.
168
169 \sa clear()
170*/
171QSqlQueryModel::~QSqlQueryModel()
172{
173}
174
175/*!
176 \since 4.1
177
178 Fetches more rows from a database.
179 This only affects databases that don't report back the size of a query
180 (see QSqlDriver::hasFeature()).
181
182 To force fetching of the entire result set, you can use the following:
183
184 \snippet code/src_sql_models_qsqlquerymodel.cpp 0
185
186 \a parent should always be an invalid QModelIndex.
187
188 \sa canFetchMore()
189*/
190void QSqlQueryModel::fetchMore(const QModelIndex &parent)
191{
192 Q_D(QSqlQueryModel);
193 if (parent.isValid())
194 return;
195 d->prefetch(qMax(d->bottom.row(), 0) + QSQL_PREFETCH);
196}
197
198/*!
199 \since 4.1
200
201 Returns \c true if it is possible to read more rows from the database.
202 This only affects databases that don't report back the size of a query
203 (see QSqlDriver::hasFeature()).
204
205 \a parent should always be an invalid QModelIndex.
206
207 \sa fetchMore()
208 */
209bool QSqlQueryModel::canFetchMore(const QModelIndex &parent) const
210{
211 Q_D(const QSqlQueryModel);
212 return (!parent.isValid() && !d->atEnd);
213}
214
215/*!
216 \since 5.10
217 \reimp
218
219 Returns the model's role names.
220
221 Qt defines only one role for the QSqlQueryModel:
222
223 \table
224 \header
225 \li Qt Role
226 \li QML Role Name
227 \row
228 \li Qt::DisplayRole
229 \li display
230 \endtable
231*/
232QHash<int, QByteArray> QSqlQueryModel::roleNames() const
233{
234 return QHash<int, QByteArray> {
235 { Qt::DisplayRole, QByteArrayLiteral("display") }
236 };
237}
238
239/*! \internal
240 */
241void QSqlQueryModel::beginInsertRows(const QModelIndex &parent, int first, int last)
242{
243 Q_D(QSqlQueryModel);
244 if (!d->nestedResetLevel)
245 QAbstractTableModel::beginInsertRows(parent, first, last);
246}
247
248/*! \internal
249 */
250void QSqlQueryModel::endInsertRows()
251{
252 Q_D(QSqlQueryModel);
253 if (!d->nestedResetLevel)
254 QAbstractTableModel::endInsertRows();
255}
256
257/*! \internal
258 */
259void QSqlQueryModel::beginRemoveRows(const QModelIndex &parent, int first, int last)
260{
261 Q_D(QSqlQueryModel);
262 if (!d->nestedResetLevel)
263 QAbstractTableModel::beginRemoveRows(parent, first, last);
264}
265
266/*! \internal
267 */
268void QSqlQueryModel::endRemoveRows()
269{
270 Q_D(QSqlQueryModel);
271 if (!d->nestedResetLevel)
272 QAbstractTableModel::endRemoveRows();
273}
274
275/*! \internal
276 */
277void QSqlQueryModel::beginInsertColumns(const QModelIndex &parent, int first, int last)
278{
279 Q_D(QSqlQueryModel);
280 if (!d->nestedResetLevel)
281 QAbstractTableModel::beginInsertColumns(parent, first, last);
282}
283
284/*! \internal
285 */
286void QSqlQueryModel::endInsertColumns()
287{
288 Q_D(QSqlQueryModel);
289 if (!d->nestedResetLevel)
290 QAbstractTableModel::endInsertColumns();
291}
292
293/*! \internal
294 */
295void QSqlQueryModel::beginRemoveColumns(const QModelIndex &parent, int first, int last)
296{
297 Q_D(QSqlQueryModel);
298 if (!d->nestedResetLevel)
299 QAbstractTableModel::beginRemoveColumns(parent, first, last);
300}
301
302/*! \internal
303 */
304void QSqlQueryModel::endRemoveColumns()
305{
306 Q_D(QSqlQueryModel);
307 if (!d->nestedResetLevel)
308 QAbstractTableModel::endRemoveColumns();
309}
310
311/*! \internal
312 */
313void QSqlQueryModel::beginResetModel()
314{
315 Q_D(QSqlQueryModel);
316 if (!d->nestedResetLevel)
317 QAbstractTableModel::beginResetModel();
318 ++d->nestedResetLevel;
319}
320
321/*! \internal
322 */
323void QSqlQueryModel::endResetModel()
324{
325 Q_D(QSqlQueryModel);
326 --d->nestedResetLevel;
327 if (!d->nestedResetLevel)
328 QAbstractTableModel::endResetModel();
329}
330
331/*! \fn int QSqlQueryModel::rowCount(const QModelIndex &parent) const
332 \since 4.1
333
334 If the database supports returning the size of a query
335 (see QSqlDriver::hasFeature()), the number of rows of the current
336 query is returned. Otherwise, returns the number of rows
337 currently cached on the client.
338
339 \a parent should always be an invalid QModelIndex.
340
341 \sa canFetchMore(), QSqlDriver::hasFeature()
342 */
343int QSqlQueryModel::rowCount(const QModelIndex &index) const
344{
345 Q_D(const QSqlQueryModel);
346 return index.isValid() ? 0 : d->bottom.row() + 1;
347}
348
349/*! \reimp
350 */
351int QSqlQueryModel::columnCount(const QModelIndex &index) const
352{
353 Q_D(const QSqlQueryModel);
354 return index.isValid() ? 0 : d->rec.count();
355}
356
357/*!
358 Returns the value for the specified \a item and \a role.
359
360 If \a item is out of bounds or if an error occurred, an invalid
361 QVariant is returned.
362
363 \sa lastError()
364*/
365QVariant QSqlQueryModel::data(const QModelIndex &item, int role) const
366{
367 Q_D(const QSqlQueryModel);
368 if (!item.isValid())
369 return QVariant();
370
371 QVariant v;
372 if (role & ~(Qt::DisplayRole | Qt::EditRole))
373 return v;
374
375 if (!d->rec.isGenerated(item.column()))
376 return v;
377 QModelIndex dItem = indexInQuery(item);
378 if (dItem.row() > d->bottom.row())
379 const_cast<QSqlQueryModelPrivate *>(d)->prefetch(dItem.row());
380
381 if (!d->query.seek(dItem.row())) {
382 d->error = d->query.lastError();
383 return v;
384 }
385
386 return d->query.value(dItem.column());
387}
388
389/*!
390 Returns the header data for the given \a role in the \a section
391 of the header with the specified \a orientation.
392*/
393QVariant QSqlQueryModel::headerData(int section, Qt::Orientation orientation, int role) const
394{
395 Q_D(const QSqlQueryModel);
396 if (orientation == Qt::Horizontal) {
397 QVariant val = d->headers.value(section).value(role);
398 if (role == Qt::DisplayRole && !val.isValid())
399 val = d->headers.value(section).value(Qt::EditRole);
400 if (val.isValid())
401 return val;
402 if (role == Qt::DisplayRole && d->rec.count() > section && d->columnInQuery(section) != -1)
403 return d->rec.fieldName(section);
404 }
405 return QAbstractItemModel::headerData(section, orientation, role);
406}
407
408/*!
409 This virtual function is called whenever the query changes. The
410 default implementation does nothing.
411
412 query() returns the new query.
413
414 \sa query(), setQuery()
415 */
416void QSqlQueryModel::queryChange()
417{
418 // do nothing
419}
420
421/*!
422 Resets the model and sets the data provider to be the given \a
423 query. Note that the query must be active and must not be
424 isForwardOnly().
425
426 lastError() can be used to retrieve verbose information if there
427 was an error setting the query.
428
429 \note Calling setQuery() will remove any inserted columns.
430
431 \sa query(), QSqlQuery::isActive(), QSqlQuery::setForwardOnly(), lastError()
432*/
433void QSqlQueryModel::setQuery(const QSqlQuery &query)
434{
435 Q_D(QSqlQueryModel);
436 beginResetModel();
437
438 QSqlRecord newRec = query.record();
439 bool columnsChanged = (newRec != d->rec);
440
441 if (d->colOffsets.size() != newRec.count() || columnsChanged)
442 d->initColOffsets(newRec.count());
443
444 d->bottom = QModelIndex();
445 d->error = QSqlError();
446 d->query = query;
447 d->rec = newRec;
448 d->atEnd = true;
449
450 if (query.isForwardOnly()) {
451 d->error = QSqlError(QLatin1String("Forward-only queries "
452 "cannot be used in a data model"),
453 QString(), QSqlError::ConnectionError);
454 endResetModel();
455 return;
456 }
457
458 if (!query.isActive()) {
459 d->error = query.lastError();
460 endResetModel();
461 return;
462 }
463
464 if (query.driver()->hasFeature(QSqlDriver::QuerySize) && d->query.size() > 0) {
465 d->bottom = createIndex(d->query.size() - 1, d->rec.count() - 1);
466 } else {
467 d->bottom = createIndex(-1, d->rec.count() - 1);
468 d->atEnd = false;
469 }
470
471
472 // fetchMore does the rowsInserted stuff for incremental models
473 fetchMore();
474
475 endResetModel();
476 queryChange();
477}
478
479/*! \overload
480
481 Executes the query \a query for the given database connection \a
482 db. If no database (or an invalid database) is specified, the
483 default connection is used.
484
485 lastError() can be used to retrieve verbose information if there
486 was an error setting the query.
487
488 Example:
489 \snippet code/src_sql_models_qsqlquerymodel.cpp 1
490
491 \sa query(), queryChange(), lastError()
492*/
493void QSqlQueryModel::setQuery(const QString &query, const QSqlDatabase &db)
494{
495 setQuery(QSqlQuery(query, db));
496}
497
498/*!
499 Clears the model and releases any acquired resource.
500*/
501void QSqlQueryModel::clear()
502{
503 Q_D(QSqlQueryModel);
504 beginResetModel();
505 d->error = QSqlError();
506 d->atEnd = true;
507 d->query.clear();
508 d->rec.clear();
509 d->colOffsets.clear();
510 d->bottom = QModelIndex();
511 d->headers.clear();
512 endResetModel();
513}
514
515/*!
516 Sets the caption for a horizontal header for the specified \a role to
517 \a value. This is useful if the model is used to
518 display data in a view (e.g., QTableView).
519
520 Returns \c true if \a orientation is Qt::Horizontal and
521 the \a section refers to a valid section; otherwise returns
522 false.
523
524 Note that this function cannot be used to modify values in the
525 database since the model is read-only.
526
527 \sa data()
528 */
529bool QSqlQueryModel::setHeaderData(int section, Qt::Orientation orientation,
530 const QVariant &value, int role)
531{
532 Q_D(QSqlQueryModel);
533 if (orientation != Qt::Horizontal || section < 0 || columnCount() <= section)
534 return false;
535
536 if (d->headers.size() <= section)
537 d->headers.resize(qMax(section + 1, 16));
538 d->headers[section][role] = value;
539 emit headerDataChanged(orientation, section, section);
540 return true;
541}
542
543/*!
544 Returns the QSqlQuery associated with this model.
545
546 \sa setQuery()
547*/
548QSqlQuery QSqlQueryModel::query() const
549{
550 Q_D(const QSqlQueryModel);
551 return d->query;
552}
553
554/*!
555 Returns information about the last error that occurred on the
556 database.
557
558 \sa query()
559*/
560QSqlError QSqlQueryModel::lastError() const
561{
562 Q_D(const QSqlQueryModel);
563 return d->error;
564}
565
566/*!
567 Protected function which allows derived classes to set the value of
568 the last error that occurred on the database to \a error.
569
570 \sa lastError()
571*/
572void QSqlQueryModel::setLastError(const QSqlError &error)
573{
574 Q_D(QSqlQueryModel);
575 d->error = error;
576}
577
578/*!
579 Returns the record containing information about the fields of the
580 current query. If \a row is the index of a valid row, the record
581 will be populated with values from that row.
582
583 If the model is not initialized, an empty record will be
584 returned.
585
586 \sa QSqlRecord::isEmpty()
587*/
588QSqlRecord QSqlQueryModel::record(int row) const
589{
590 Q_D(const QSqlQueryModel);
591 if (row < 0)
592 return d->rec;
593
594 QSqlRecord rec = d->rec;
595 for (int i = 0; i < rec.count(); ++i)
596 rec.setValue(i, data(createIndex(row, i), Qt::EditRole));
597 return rec;
598}
599
600/*! \overload
601
602 Returns an empty record containing information about the fields
603 of the current query.
604
605 If the model is not initialized, an empty record will be
606 returned.
607
608 \sa QSqlRecord::isEmpty()
609 */
610QSqlRecord QSqlQueryModel::record() const
611{
612 Q_D(const QSqlQueryModel);
613 return d->rec;
614}
615
616/*!
617 Inserts \a count columns into the model at position \a column. The
618 \a parent parameter must always be an invalid QModelIndex, since
619 the model does not support parent-child relationships.
620
621 Returns \c true if \a column is within bounds; otherwise returns \c false.
622
623 By default, inserted columns are empty. To fill them with data,
624 reimplement data() and handle any inserted column separately:
625
626 \snippet sqldatabase/sqldatabase.cpp 23
627
628 \sa removeColumns()
629*/
630bool QSqlQueryModel::insertColumns(int column, int count, const QModelIndex &parent)
631{
632 Q_D(QSqlQueryModel);
633 if (count <= 0 || parent.isValid() || column < 0 || column > d->rec.count())
634 return false;
635
636 beginInsertColumns(parent, column, column + count - 1);
637 for (int c = 0; c < count; ++c) {
638 QSqlField field;
639 field.setReadOnly(true);
640 field.setGenerated(false);
641 d->rec.insert(column, field);
642 if (d->colOffsets.size() < d->rec.count()) {
643 int nVal = d->colOffsets.isEmpty() ? 0 : d->colOffsets[d->colOffsets.size() - 1];
644 d->colOffsets.append(nVal);
645 Q_ASSERT(d->colOffsets.size() >= d->rec.count());
646 }
647 for (int i = column + 1; i < d->colOffsets.count(); ++i)
648 ++d->colOffsets[i];
649 }
650 endInsertColumns();
651 return true;
652}
653
654/*!
655 Removes \a count columns from the model starting from position \a
656 column. The \a parent parameter must always be an invalid
657 QModelIndex, since the model does not support parent-child
658 relationships.
659
660 Removing columns effectively hides them. It does not affect the
661 underlying QSqlQuery.
662
663 Returns \c true if the columns were removed; otherwise returns \c false.
664 */
665bool QSqlQueryModel::removeColumns(int column, int count, const QModelIndex &parent)
666{
667 Q_D(QSqlQueryModel);
668 if (count <= 0 || parent.isValid() || column < 0 || column >= d->rec.count())
669 return false;
670
671 beginRemoveColumns(parent, column, column + count - 1);
672
673 int i;
674 for (i = 0; i < count; ++i)
675 d->rec.remove(column);
676 for (i = column; i < d->colOffsets.count(); ++i)
677 d->colOffsets[i] -= count;
678
679 endRemoveColumns();
680 return true;
681}
682
683/*!
684 Returns the index of the value in the database result set for the
685 given \a item in the model.
686
687 The return value is identical to \a item if no columns or rows
688 have been inserted, removed, or moved around.
689
690 Returns an invalid model index if \a item is out of bounds or if
691 \a item does not point to a value in the result set.
692
693 \sa QSqlTableModel::indexInQuery(), insertColumns(), removeColumns()
694*/
695QModelIndex QSqlQueryModel::indexInQuery(const QModelIndex &item) const
696{
697 Q_D(const QSqlQueryModel);
698 int modelColumn = d->columnInQuery(item.column());
699 if (modelColumn < 0)
700 return QModelIndex();
701 return createIndex(item.row(), modelColumn, item.internalPointer());
702}
703
704QT_END_NAMESPACE
705