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 Fetches more rows from a database.
143 This only affects databases that don't report back the size of a query
144 (see QSqlDriver::hasFeature()).
145
146 To force fetching of the entire result set, you can use the following:
147
148 \snippet code/src_sql_models_qsqlquerymodel.cpp 0
149
150 \a parent should always be an invalid QModelIndex.
151
152 \sa canFetchMore()
153*/
154void QSqlQueryModel::fetchMore(const QModelIndex &parent)
155{
156 Q_D(QSqlQueryModel);
157 if (parent.isValid())
158 return;
159 d->prefetch(limit: qMax(a: d->bottom.row(), b: 0) + QSQL_PREFETCH);
160}
161
162/*!
163 Returns \c true if it is possible to read more rows from the database.
164 This only affects databases that don't report back the size of a query
165 (see QSqlDriver::hasFeature()).
166
167 \a parent should always be an invalid QModelIndex.
168
169 \sa fetchMore()
170 */
171bool QSqlQueryModel::canFetchMore(const QModelIndex &parent) const
172{
173 Q_D(const QSqlQueryModel);
174 return (!parent.isValid() && !d->atEnd);
175}
176
177/*!
178 \since 5.10
179 \reimp
180
181 Returns the model's role names.
182
183 Qt defines only one role for the QSqlQueryModel:
184
185 \table
186 \header
187 \li Qt Role
188 \li QML Role Name
189 \row
190 \li Qt::DisplayRole
191 \li display
192 \endtable
193*/
194QHash<int, QByteArray> QSqlQueryModel::roleNames() const
195{
196 return QHash<int, QByteArray> {
197 { Qt::DisplayRole, QByteArrayLiteral("display") }
198 };
199}
200
201/*! \internal
202 */
203void QSqlQueryModel::beginInsertRows(const QModelIndex &parent, int first, int last)
204{
205 Q_D(QSqlQueryModel);
206 if (!d->nestedResetLevel)
207 QAbstractTableModel::beginInsertRows(parent, first, last);
208}
209
210/*! \internal
211 */
212void QSqlQueryModel::endInsertRows()
213{
214 Q_D(QSqlQueryModel);
215 if (!d->nestedResetLevel)
216 QAbstractTableModel::endInsertRows();
217}
218
219/*! \internal
220 */
221void QSqlQueryModel::beginRemoveRows(const QModelIndex &parent, int first, int last)
222{
223 Q_D(QSqlQueryModel);
224 if (!d->nestedResetLevel)
225 QAbstractTableModel::beginRemoveRows(parent, first, last);
226}
227
228/*! \internal
229 */
230void QSqlQueryModel::endRemoveRows()
231{
232 Q_D(QSqlQueryModel);
233 if (!d->nestedResetLevel)
234 QAbstractTableModel::endRemoveRows();
235}
236
237/*! \internal
238 */
239void QSqlQueryModel::beginInsertColumns(const QModelIndex &parent, int first, int last)
240{
241 Q_D(QSqlQueryModel);
242 if (!d->nestedResetLevel)
243 QAbstractTableModel::beginInsertColumns(parent, first, last);
244}
245
246/*! \internal
247 */
248void QSqlQueryModel::endInsertColumns()
249{
250 Q_D(QSqlQueryModel);
251 if (!d->nestedResetLevel)
252 QAbstractTableModel::endInsertColumns();
253}
254
255/*! \internal
256 */
257void QSqlQueryModel::beginRemoveColumns(const QModelIndex &parent, int first, int last)
258{
259 Q_D(QSqlQueryModel);
260 if (!d->nestedResetLevel)
261 QAbstractTableModel::beginRemoveColumns(parent, first, last);
262}
263
264/*! \internal
265 */
266void QSqlQueryModel::endRemoveColumns()
267{
268 Q_D(QSqlQueryModel);
269 if (!d->nestedResetLevel)
270 QAbstractTableModel::endRemoveColumns();
271}
272
273/*! \internal
274 */
275void QSqlQueryModel::beginResetModel()
276{
277 Q_D(QSqlQueryModel);
278 if (!d->nestedResetLevel)
279 QAbstractTableModel::beginResetModel();
280 ++d->nestedResetLevel;
281}
282
283/*! \internal
284 */
285void QSqlQueryModel::endResetModel()
286{
287 Q_D(QSqlQueryModel);
288 --d->nestedResetLevel;
289 if (!d->nestedResetLevel)
290 QAbstractTableModel::endResetModel();
291}
292
293/*! \fn int QSqlQueryModel::rowCount(const QModelIndex &parent) const
294
295 If the database supports returning the size of a query
296 (see QSqlDriver::hasFeature()), the number of rows of the current
297 query is returned. Otherwise, returns the number of rows
298 currently cached on the client.
299
300 \a parent should always be an invalid QModelIndex.
301
302 \sa canFetchMore(), QSqlDriver::hasFeature()
303 */
304int QSqlQueryModel::rowCount(const QModelIndex &index) const
305{
306 Q_D(const QSqlQueryModel);
307 return index.isValid() ? 0 : d->bottom.row() + 1;
308}
309
310/*! \reimp
311 */
312int QSqlQueryModel::columnCount(const QModelIndex &index) const
313{
314 Q_D(const QSqlQueryModel);
315 return index.isValid() ? 0 : d->rec.count();
316}
317
318/*!
319 Returns the value for the specified \a item and \a role.
320
321 If \a item is out of bounds or if an error occurred, an invalid
322 QVariant is returned.
323
324 \sa lastError()
325*/
326QVariant QSqlQueryModel::data(const QModelIndex &item, int role) const
327{
328 Q_D(const QSqlQueryModel);
329 if (!item.isValid())
330 return QVariant();
331
332 QVariant v;
333 if (role & ~(Qt::DisplayRole | Qt::EditRole))
334 return v;
335
336 if (!d->rec.isGenerated(i: item.column()))
337 return v;
338 QModelIndex dItem = indexInQuery(item);
339 if (dItem.row() > d->bottom.row())
340 const_cast<QSqlQueryModelPrivate *>(d)->prefetch(limit: dItem.row());
341
342 if (!d->query.seek(i: dItem.row())) {
343 d->error = d->query.lastError();
344 return v;
345 }
346
347 return d->query.value(i: dItem.column());
348}
349
350/*!
351 Returns the header data for the given \a role in the \a section
352 of the header with the specified \a orientation.
353*/
354QVariant QSqlQueryModel::headerData(int section, Qt::Orientation orientation, int role) const
355{
356 Q_D(const QSqlQueryModel);
357 if (orientation == Qt::Horizontal) {
358 QVariant val = d->headers.value(i: section).value(key: role);
359 if (role == Qt::DisplayRole && !val.isValid())
360 val = d->headers.value(i: section).value(key: Qt::EditRole);
361 if (val.isValid())
362 return val;
363 if (role == Qt::DisplayRole && d->rec.count() > section && d->columnInQuery(modelColumn: section) != -1)
364 return d->rec.fieldName(i: section);
365 }
366 return QAbstractItemModel::headerData(section, orientation, role);
367}
368
369/*!
370 This virtual function is called whenever the query changes. The
371 default implementation does nothing.
372
373 query() returns the new query.
374
375 \sa query(), setQuery()
376 */
377void QSqlQueryModel::queryChange()
378{
379 // do nothing
380}
381
382#if QT_DEPRECATED_SINCE(6, 2)
383/*!
384 \deprecated [6.2] Use the \c{setQuery(QSqlQuery &&query)} overload instead.
385 \overload
386*/
387void QSqlQueryModel::setQuery(const QSqlQuery &query)
388{
389 QT_IGNORE_DEPRECATIONS(QSqlQuery copy = query;)
390 setQuery(std::move(copy));
391}
392#endif // QT_DEPRECATED_SINCE(6, 2)
393
394/*!
395 Resets the model and sets the data provider to be the given \a
396 query. Note that the query must be active and must not be
397 isForwardOnly().
398
399 lastError() can be used to retrieve verbose information if there
400 was an error setting the query.
401
402 \note Calling setQuery() will remove any inserted columns.
403
404 \since 6.2
405
406 \sa query(), QSqlQuery::isActive(), QSqlQuery::setForwardOnly(), lastError()
407*/
408void QSqlQueryModel::setQuery(QSqlQuery &&query)
409{
410 Q_D(QSqlQueryModel);
411 beginResetModel();
412
413 QSqlRecord newRec = query.record();
414 bool columnsChanged = (newRec != d->rec);
415
416 if (d->colOffsets.size() != newRec.count() || columnsChanged)
417 d->initColOffsets(size: newRec.count());
418
419 d->bottom = QModelIndex();
420 d->error = QSqlError();
421 d->query = std::move(query);
422 d->rec = newRec;
423 d->atEnd = true;
424
425 if (d->query.isForwardOnly()) {
426 d->error = QSqlError("Forward-only queries cannot be used in a data model"_L1,
427 QString(), QSqlError::ConnectionError);
428 endResetModel();
429 return;
430 }
431
432 if (!d->query.isActive()) {
433 d->error = d->query.lastError();
434 endResetModel();
435 return;
436 }
437
438 if (d->query.driver()->hasFeature(f: QSqlDriver::QuerySize) && d->query.size() > 0) {
439 d->bottom = createIndex(arow: d->query.size() - 1, acolumn: d->rec.count() - 1);
440 } else {
441 d->bottom = createIndex(arow: -1, acolumn: d->rec.count() - 1);
442 d->atEnd = false;
443 }
444
445
446 // fetchMore does the rowsInserted stuff for incremental models
447 fetchMore();
448
449 endResetModel();
450 queryChange();
451}
452
453/*! \overload
454
455 Executes the query \a query for the given database connection \a
456 db. If no database (or an invalid database) is specified, the
457 default connection is used.
458
459 lastError() can be used to retrieve verbose information if there
460 was an error setting the query.
461
462 Example:
463 \snippet code/src_sql_models_qsqlquerymodel.cpp 1
464
465 \sa query(), queryChange(), lastError()
466*/
467void QSqlQueryModel::setQuery(const QString &query, const QSqlDatabase &db)
468{
469 setQuery(QSqlQuery(query, db));
470}
471
472/*!
473 Clears the model and releases any acquired resource.
474*/
475void QSqlQueryModel::clear()
476{
477 Q_D(QSqlQueryModel);
478 beginResetModel();
479 d->error = QSqlError();
480 d->atEnd = true;
481 d->query.clear();
482 d->rec.clear();
483 d->colOffsets.clear();
484 d->bottom = QModelIndex();
485 d->headers.clear();
486 endResetModel();
487}
488
489/*!
490 Sets the caption for a horizontal header for the specified \a role to
491 \a value. This is useful if the model is used to
492 display data in a view (e.g., QTableView).
493
494 Returns \c true if \a orientation is Qt::Horizontal and
495 the \a section refers to a valid section; otherwise returns
496 false.
497
498 Note that this function cannot be used to modify values in the
499 database since the model is read-only.
500
501 \sa data()
502 */
503bool QSqlQueryModel::setHeaderData(int section, Qt::Orientation orientation,
504 const QVariant &value, int role)
505{
506 Q_D(QSqlQueryModel);
507 if (orientation != Qt::Horizontal || section < 0 || columnCount() <= section)
508 return false;
509
510 if (d->headers.size() <= section)
511 d->headers.resize(size: qMax(a: section + 1, b: 16));
512 d->headers[section][role] = value;
513 emit headerDataChanged(orientation, first: section, last: section);
514 return true;
515}
516
517/*!
518 Returns a reference to the const QSqlQuery object associated with this model.
519
520 \sa setQuery()
521*/
522const QSqlQuery &QSqlQueryModel::query(QT6_IMPL_NEW_OVERLOAD) const
523{
524 Q_D(const QSqlQueryModel);
525 return d->query;
526}
527
528/*!
529 Returns information about the last error that occurred on the
530 database.
531
532 \sa query()
533*/
534QSqlError QSqlQueryModel::lastError() const
535{
536 Q_D(const QSqlQueryModel);
537 return d->error;
538}
539
540/*!
541 Protected function which allows derived classes to set the value of
542 the last error that occurred on the database to \a error.
543
544 \sa lastError()
545*/
546void QSqlQueryModel::setLastError(const QSqlError &error)
547{
548 Q_D(QSqlQueryModel);
549 d->error = error;
550}
551
552/*!
553 Returns the record containing information about the fields of the
554 current query. If \a row is the index of a valid row, the record
555 will be populated with values from that row.
556
557 If the model is not initialized, an empty record will be
558 returned.
559
560 \sa QSqlRecord::isEmpty()
561*/
562QSqlRecord QSqlQueryModel::record(int row) const
563{
564 Q_D(const QSqlQueryModel);
565 if (row < 0)
566 return d->rec;
567
568 QSqlRecord rec = d->rec;
569 for (int i = 0; i < rec.count(); ++i)
570 rec.setValue(i, val: data(item: createIndex(arow: row, acolumn: i), role: Qt::EditRole));
571 return rec;
572}
573
574/*! \overload
575
576 Returns an empty record containing information about the fields
577 of the current query.
578
579 If the model is not initialized, an empty record will be
580 returned.
581
582 \sa QSqlRecord::isEmpty()
583 */
584QSqlRecord QSqlQueryModel::record() const
585{
586 Q_D(const QSqlQueryModel);
587 return d->rec;
588}
589
590/*!
591 Inserts \a count columns into the model at position \a column. The
592 \a parent parameter must always be an invalid QModelIndex, since
593 the model does not support parent-child relationships.
594
595 Returns \c true if \a column is within bounds; otherwise returns \c false.
596
597 By default, inserted columns are empty. To fill them with data,
598 reimplement data() and handle any inserted column separately:
599
600 \snippet sqldatabase/sqldatabase.cpp 23
601
602 \sa removeColumns()
603*/
604bool QSqlQueryModel::insertColumns(int column, int count, const QModelIndex &parent)
605{
606 Q_D(QSqlQueryModel);
607 if (count <= 0 || parent.isValid() || column < 0 || column > d->rec.count())
608 return false;
609
610 beginInsertColumns(parent, first: column, last: column + count - 1);
611 for (int c = 0; c < count; ++c) {
612 QSqlField field;
613 field.setReadOnly(true);
614 field.setGenerated(false);
615 d->rec.insert(pos: column, field);
616 if (d->colOffsets.size() < d->rec.count()) {
617 int nVal = d->colOffsets.isEmpty() ? 0 : d->colOffsets[d->colOffsets.size() - 1];
618 d->colOffsets.append(t: nVal);
619 Q_ASSERT(d->colOffsets.size() >= d->rec.count());
620 }
621 for (qsizetype i = column + 1; i < d->colOffsets.size(); ++i)
622 ++d->colOffsets[i];
623 }
624 endInsertColumns();
625 return true;
626}
627
628/*!
629 Removes \a count columns from the model starting from position \a
630 column. The \a parent parameter must always be an invalid
631 QModelIndex, since the model does not support parent-child
632 relationships.
633
634 Removing columns effectively hides them. It does not affect the
635 underlying QSqlQuery.
636
637 Returns \c true if the columns were removed; otherwise returns \c false.
638 */
639bool QSqlQueryModel::removeColumns(int column, int count, const QModelIndex &parent)
640{
641 Q_D(QSqlQueryModel);
642 if (count <= 0 || parent.isValid() || column < 0 || column >= d->rec.count())
643 return false;
644
645 beginRemoveColumns(parent, first: column, last: column + count - 1);
646
647 for (int i = 0; i < count; ++i)
648 d->rec.remove(pos: column);
649 for (qsizetype i = column; i < d->colOffsets.size(); ++i)
650 d->colOffsets[i] -= count;
651
652 endRemoveColumns();
653 return true;
654}
655
656/*!
657 Returns the index of the value in the database result set for the
658 given \a item in the model.
659
660 The return value is identical to \a item if no columns or rows
661 have been inserted, removed, or moved around.
662
663 Returns an invalid model index if \a item is out of bounds or if
664 \a item does not point to a value in the result set.
665
666 \sa QSqlTableModel::indexInQuery(), insertColumns(), removeColumns()
667*/
668QModelIndex QSqlQueryModel::indexInQuery(const QModelIndex &item) const
669{
670 Q_D(const QSqlQueryModel);
671 int modelColumn = d->columnInQuery(modelColumn: item.column());
672 if (modelColumn < 0)
673 return QModelIndex();
674 return createIndex(arow: item.row(), acolumn: modelColumn, adata: item.internalPointer());
675}
676
677QT_END_NAMESPACE
678
679#include "moc_qsqlquerymodel.cpp"
680

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