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

source code of qtbase/src/sql/models/qsqlquerymodel.cpp