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

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