1// Copyright (C) 2021 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 "qsqltablemodel.h"
5
6#include "qsqldriver.h"
7#include "qsqlerror.h"
8#include "qsqlfield.h"
9#include "qsqlindex.h"
10#include "qsqlquery.h"
11#include "qsqlrecord.h"
12#include "qsqlresult.h"
13
14#include "qsqltablemodel_p.h"
15
16#include <qdebug.h>
17
18QT_BEGIN_NAMESPACE
19
20using namespace Qt::StringLiterals;
21
22using SqlTm = QSqlQueryModelSql;
23
24/*! \internal
25 Populates our record with values.
26*/
27QSqlRecord QSqlTableModelPrivate::record(const QList<QVariant> &values) const
28{
29 QSqlRecord r = rec;
30 for (int i = 0; i < r.count() && i < values.size(); ++i)
31 r.setValue(i, val: values.at(i));
32 return r;
33}
34
35int QSqlTableModelPrivate::nameToIndex(const QString &name) const
36{
37 return rec.indexOf(name: strippedFieldName(name));
38}
39
40QString QSqlTableModelPrivate::strippedFieldName(const QString &name) const
41{
42 QString fieldname = name;
43 if (db.driver()->isIdentifierEscaped(identifier: fieldname, type: QSqlDriver::FieldName))
44 fieldname = db.driver()->stripDelimiters(identifier: fieldname, type: QSqlDriver::FieldName);
45 return fieldname;
46}
47
48int QSqlTableModelPrivate::insertCount(int maxRow) const
49{
50 int cnt = 0;
51 CacheMap::ConstIterator i = cache.constBegin();
52 const CacheMap::ConstIterator e = cache.constEnd();
53 for ( ; i != e && (maxRow < 0 || i.key() <= maxRow); ++i)
54 if (i.value().insert())
55 ++cnt;
56
57 return cnt;
58}
59
60void QSqlTableModelPrivate::initRecordAndPrimaryIndex()
61{
62 rec = db.record(tablename: tableName);
63 primaryIndex = db.primaryIndex(tablename: tableName);
64 initColOffsets(size: rec.count());
65}
66
67void QSqlTableModelPrivate::clear()
68{
69 sortColumn = -1;
70 sortOrder = Qt::AscendingOrder;
71 tableName.clear();
72 editQuery.clear();
73 cache.clear();
74 primaryIndex.clear();
75 rec.clear();
76 filter.clear();
77}
78
79void QSqlTableModelPrivate::clearCache()
80{
81 cache.clear();
82}
83
84void QSqlTableModelPrivate::revertCachedRow(int row)
85{
86 Q_Q(QSqlTableModel);
87 ModifiedRow r = cache.value(key: row);
88
89 switch (r.op()) {
90 case QSqlTableModelPrivate::None:
91 Q_ASSERT_X(false, "QSqlTableModelPrivate::revertCachedRow()", "Invalid entry in cache map");
92 return;
93 case QSqlTableModelPrivate::Update:
94 case QSqlTableModelPrivate::Delete:
95 if (!r.submitted()) {
96 cache[row].revert();
97 emit q->dataChanged(topLeft: q->createIndex(arow: row, acolumn: 0),
98 bottomRight: q->createIndex(arow: row, acolumn: q->columnCount() - 1));
99 }
100 break;
101 case QSqlTableModelPrivate::Insert: {
102 QMap<int, QSqlTableModelPrivate::ModifiedRow>::Iterator it = cache.find(key: row);
103 if (it == cache.end())
104 return;
105 q->beginRemoveRows(parent: QModelIndex(), first: row, last: row);
106 it = cache.erase(it);
107 while (it != cache.end()) {
108 int oldKey = it.key();
109 const QSqlTableModelPrivate::ModifiedRow oldValue = it.value();
110 cache.erase(it);
111 it = cache.insert(key: oldKey - 1, value: oldValue);
112 ++it;
113 }
114 q->endRemoveRows();
115 break; }
116 }
117}
118
119bool QSqlTableModelPrivate::exec(const QString &stmt, bool prepStatement,
120 const QSqlRecord &rec, const QSqlRecord &whereValues)
121{
122 if (stmt.isEmpty())
123 return false;
124
125 // lazy initialization of editQuery
126 if (editQuery.driver() != db.driver())
127 editQuery = QSqlQuery(db);
128
129 // workaround for In-Process databases - remove all read locks
130 // from the table to make sure the editQuery succeeds
131 if (db.driver()->hasFeature(f: QSqlDriver::SimpleLocking))
132 const_cast<QSqlResult *>(query.result())->detachFromResultSet();
133
134 if (prepStatement) {
135 if (editQuery.lastQuery() != stmt) {
136 if (!editQuery.prepare(query: stmt)) {
137 error = editQuery.lastError();
138 return false;
139 }
140 }
141 for (int i = 0; i < rec.count(); ++i)
142 if (rec.isGenerated(i))
143 editQuery.addBindValue(val: rec.value(i));
144 for (int i = 0; i < whereValues.count(); ++i)
145 if (whereValues.isGenerated(i) && !whereValues.isNull(i))
146 editQuery.addBindValue(val: whereValues.value(i));
147
148 if (!editQuery.exec()) {
149 error = editQuery.lastError();
150 return false;
151 }
152 } else {
153 if (!editQuery.exec(query: stmt)) {
154 error = editQuery.lastError();
155 return false;
156 }
157 }
158 return true;
159}
160
161/*!
162 \class QSqlTableModel
163 \brief The QSqlTableModel class provides an editable data model
164 for a single database table.
165
166 \ingroup database
167 \inmodule QtSql
168
169 QSqlTableModel is a high-level interface for reading and writing
170 database records from a single table. It is built on top of the
171 lower-level QSqlQuery and can be used to provide data to view
172 classes such as QTableView. For example:
173
174 \snippet sqldatabase/sqldatabase_snippet.cpp 24
175
176 We set the SQL table's name and the edit strategy, then we set up
177 the labels displayed in the view header. The edit strategy
178 dictates when the changes done by the user in the view are
179 actually applied to the database. The possible values are \l
180 OnFieldChange, \l OnRowChange, and \l OnManualSubmit.
181
182 QSqlTableModel can also be used to access a database
183 programmatically, without binding it to a view:
184
185 \snippet sqldatabase/sqldatabase.cpp 25
186
187 The code snippet above extracts the \c salary field from record 4 in
188 the result set of the query \c{SELECT * from employee}.
189
190 It is possible to set filters using setFilter(), or modify the
191 sort order using setSort(). At the end, you must call select() to
192 populate the model with data.
193
194 The \l{tablemodel} example illustrates how to use
195 QSqlTableModel as the data source for a QTableView.
196
197 QSqlTableModel provides no direct support for foreign keys. Use
198 the QSqlRelationalTableModel and QSqlRelationalDelegate if you
199 want to resolve foreign keys.
200
201 \sa QSqlRelationalTableModel, QSqlQuery, {Model/View Programming},
202 {Table Model Example}, {Cached SQL Table}
203*/
204
205/*!
206 \fn QSqlTableModel::beforeDelete(int row)
207
208 This signal is emitted by deleteRowFromTable() before the \a row
209 is deleted from the currently active database table.
210*/
211
212/*!
213 \fn void QSqlTableModel::primeInsert(int row, QSqlRecord &record)
214
215 This signal is emitted by insertRows(), when an insertion is
216 initiated in the given \a row of the currently active database
217 table. The \a record parameter can be written to (since it is a
218 reference), for example to populate some fields with default
219 values and set the generated flags of the fields. Do not try to
220 edit the record via other means such as setData() or setRecord()
221 while handling this signal.
222*/
223
224/*!
225 \fn QSqlTableModel::beforeInsert(QSqlRecord &record)
226
227 This signal is emitted by insertRowIntoTable() before a new row is
228 inserted into the currently active database table. The values that
229 are about to be inserted are stored in \a record and can be
230 modified before they will be inserted.
231*/
232
233/*!
234 \fn QSqlTableModel::beforeUpdate(int row, QSqlRecord &record)
235
236 This signal is emitted by updateRowInTable() before the \a row is
237 updated in the currently active database table with the values
238 from \a record.
239
240 Note that only values that are marked as generated will be updated.
241 The generated flag can be set with \l QSqlRecord::setGenerated()
242 and checked with \l QSqlRecord::isGenerated().
243
244 \sa QSqlRecord::isGenerated()
245*/
246
247/*!
248 Creates an empty QSqlTableModel and sets the parent to \a parent
249 and the database connection to \a db. If \a db is not valid, the
250 default database connection will be used.
251
252 The default edit strategy is \l OnRowChange.
253*/
254QSqlTableModel::QSqlTableModel(QObject *parent, const QSqlDatabase &db)
255 : QSqlQueryModel(*new QSqlTableModelPrivate, parent)
256{
257 Q_D(QSqlTableModel);
258 d->db = db.isValid() ? db : QSqlDatabase::database();
259}
260
261/*! \internal
262*/
263QSqlTableModel::QSqlTableModel(QSqlTableModelPrivate &dd, QObject *parent, const QSqlDatabase &db)
264 : QSqlQueryModel(dd, parent)
265{
266 Q_D(QSqlTableModel);
267 d->db = db.isValid() ? db : QSqlDatabase::database();
268}
269
270/*!
271 Destroys the object and frees any allocated resources.
272*/
273QSqlTableModel::~QSqlTableModel()
274{
275}
276
277/*!
278 Sets the database table on which the model operates to \a
279 tableName. Does not select data from the table, but fetches its
280 field information.
281
282 To populate the model with the table's data, call select().
283
284 Error information can be retrieved with \l lastError().
285
286 \sa select(), setFilter(), lastError()
287*/
288void QSqlTableModel::setTable(const QString &tableName)
289{
290 Q_D(QSqlTableModel);
291 clear();
292 d->tableName = tableName;
293 d->initRecordAndPrimaryIndex();
294
295 if (d->rec.count() == 0)
296 d->error = QSqlError("Unable to find table "_L1 + d->tableName, QString(),
297 QSqlError::StatementError);
298
299 // Remember the auto index column if there is one now.
300 // The record that will be obtained from the query after select lacks this feature.
301 d->autoColumn.clear();
302 for (int c = 0; c < d->rec.count(); ++c) {
303 if (d->rec.field(i: c).isAutoValue()) {
304 d->autoColumn = d->rec.fieldName(i: c);
305 break;
306 }
307 }
308}
309
310/*!
311 Returns the name of the currently selected table.
312*/
313QString QSqlTableModel::tableName() const
314{
315 Q_D(const QSqlTableModel);
316 return d->tableName;
317}
318
319/*!
320 Populates the model with data from the table that was set via setTable(), using the
321 specified filter and sort condition, and returns \c true if successful; otherwise
322 returns \c false.
323
324 \note Calling select() will revert any unsubmitted changes and remove any inserted columns.
325
326 \sa setTable(), setFilter(), selectStatement()
327*/
328bool QSqlTableModel::select()
329{
330 Q_D(QSqlTableModel);
331 const QString query = selectStatement();
332 if (query.isEmpty())
333 return false;
334
335 beginResetModel();
336
337 d->clearCache();
338
339 this->QSqlQueryModel::setQuery(query, db: d->db);
340
341 if (!d->query.isActive() || lastError().isValid()) {
342 // something went wrong - revert to non-select state
343 d->initRecordAndPrimaryIndex();
344 endResetModel();
345 return false;
346 }
347 endResetModel();
348 return true;
349}
350
351/*!
352 \since 5.0
353
354 Refreshes \a row in the model with values from the database table row matching
355 on primary key values. Without a primary key, all column values must match. If
356 no matching row is found, the model will show an empty row.
357
358 Returns \c true if successful; otherwise returns \c false.
359
360 \sa select()
361*/
362bool QSqlTableModel::selectRow(int row)
363{
364 Q_D(QSqlTableModel);
365
366 if (row < 0 || row >= rowCount())
367 return false;
368
369 const int table_sort_col = d->sortColumn;
370 d->sortColumn = -1;
371 const QString table_filter = d->filter;
372 d->filter = d->db.driver()->sqlStatement(type: QSqlDriver::WhereStatement,
373 tableName: d->tableName,
374 rec: primaryValues(row),
375 preparedStatement: false);
376 static const QString wh = SqlTm::where() + SqlTm::sp();
377 if (d->filter.startsWith(s: wh, cs: Qt::CaseInsensitive))
378 d->filter.remove(i: 0, len: wh.size());
379
380 QString stmt;
381
382 if (!d->filter.isEmpty())
383 stmt = selectStatement();
384
385 d->sortColumn = table_sort_col;
386 d->filter = table_filter;
387
388 if (stmt.isEmpty())
389 return false;
390
391 bool exists;
392 QSqlRecord newValues;
393
394 {
395 QSqlQuery q(d->db);
396 q.setForwardOnly(true);
397 if (!q.exec(query: stmt))
398 return false;
399
400 exists = q.next();
401 newValues = q.record();
402 }
403
404 bool needsAddingToCache = !exists || d->cache.contains(key: row);
405
406 if (!needsAddingToCache) {
407 const QSqlRecord curValues = record(row);
408 needsAddingToCache = curValues.count() != newValues.count();
409 if (!needsAddingToCache) {
410 // Look for changed values. Primary key fields are customarily first
411 // and probably change less often than other fields, so start at the end.
412 for (int f = curValues.count() - 1; f >= 0; --f) {
413 if (curValues.value(i: f) != newValues.value(i: f)) {
414 needsAddingToCache = true;
415 break;
416 }
417 }
418 }
419 }
420
421 if (needsAddingToCache) {
422 d->cache[row].refresh(exists, newvals: newValues);
423 emit headerDataChanged(orientation: Qt::Vertical, first: row, last: row);
424 emit dataChanged(topLeft: createIndex(arow: row, acolumn: 0), bottomRight: createIndex(arow: row, acolumn: columnCount() - 1));
425 }
426
427 return true;
428}
429
430/*!
431 \reimp
432*/
433QVariant QSqlTableModel::data(const QModelIndex &index, int role) const
434{
435 Q_D(const QSqlTableModel);
436 if (!index.isValid() || (role != Qt::DisplayRole && role != Qt::EditRole))
437 return QVariant();
438
439 const auto it = d->cache.constFind(key: index.row());
440 if (it != d->cache.constEnd() && it->op() != QSqlTableModelPrivate::None)
441 return it->rec().value(i: index.column());
442
443 return QSqlQueryModel::data(item: index, role);
444}
445
446/*!
447 \reimp
448*/
449QVariant QSqlTableModel::headerData(int section, Qt::Orientation orientation, int role) const
450{
451 Q_D(const QSqlTableModel);
452 if (orientation == Qt::Vertical && role == Qt::DisplayRole) {
453 const QSqlTableModelPrivate::Op op = d->cache.value(key: section).op();
454 if (op == QSqlTableModelPrivate::Insert)
455 return "*"_L1;
456 else if (op == QSqlTableModelPrivate::Delete)
457 return "!"_L1;
458 }
459 return QSqlQueryModel::headerData(section, orientation, role);
460}
461
462/*!
463 \overload
464 \since 5.0
465
466 Returns \c true if the model contains modified values that have not been
467 committed to the database, otherwise false.
468*/
469bool QSqlTableModel::isDirty() const
470{
471 Q_D(const QSqlTableModel);
472 for (const auto &val : std::as_const(t: d->cache)) {
473 if (!val.submitted())
474 return true;
475 }
476 return false;
477}
478
479/*!
480 Returns \c true if the value at the index \a index is dirty, otherwise false.
481 Dirty values are values that were modified in the model
482 but not yet written into the database.
483
484 If \a index is invalid or points to a non-existing row, false is returned.
485*/
486bool QSqlTableModel::isDirty(const QModelIndex &index) const
487{
488 Q_D(const QSqlTableModel);
489 if (!index.isValid())
490 return false;
491
492 const auto it = d->cache.constFind(key: index.row());
493 if (it == d->cache.constEnd())
494 return false;
495 const QSqlTableModelPrivate::ModifiedRow &row = *it;
496 if (row.submitted())
497 return false;
498
499 return row.op() == QSqlTableModelPrivate::Insert
500 || row.op() == QSqlTableModelPrivate::Delete
501 || (row.op() == QSqlTableModelPrivate::Update
502 && row.rec().isGenerated(i: index.column()));
503}
504
505/*!
506 Sets the data for the item \a index for the role \a role to \a
507 value.
508
509 For edit strategy OnFieldChange, an index may receive a change
510 only if no other index has a cached change. Changes are
511 submitted immediately. However, rows that have not yet been
512 inserted in the database may be freely changed and are not
513 submitted automatically. Submitted changes are not reverted upon
514 failure.
515
516 For OnRowChange, an index may receive a change only if no other
517 row has a cached change. Changes are not submitted automatically.
518
519 Returns \c true if \a value is equal to the current value. However,
520 the value will not be submitted to the database.
521
522 Returns \c true if the value could be set or false on error, for
523 example if \a index is out of bounds.
524
525 Returns \c false if the role is not Qt::EditRole. To set data
526 for roles other than EditRole, either use a custom proxy model
527 or subclass QSqlTableModel.
528
529 \sa editStrategy(), data(), submit(), submitAll(), revertRow()
530*/
531bool QSqlTableModel::setData(const QModelIndex &index, const QVariant &value, int role)
532{
533 Q_D(QSqlTableModel);
534 if (d->busyInsertingRows)
535 return false;
536
537 if (role != Qt::EditRole)
538 return QSqlQueryModel::setData(index, value, role);
539
540 if (!index.isValid() || index.column() >= d->rec.count() || index.row() >= rowCount())
541 return false;
542
543 if (!(flags(index) & Qt::ItemIsEditable))
544 return false;
545
546 const QVariant oldValue = QSqlTableModel::data(index, role);
547 if (value == oldValue
548 && value.isNull() == oldValue.isNull()
549 && d->cache.value(key: index.row()).op() != QSqlTableModelPrivate::Insert)
550 return true;
551
552 QSqlTableModelPrivate::ModifiedRow &row = d->cache[index.row()];
553
554 if (row.op() == QSqlTableModelPrivate::None)
555 row = QSqlTableModelPrivate::ModifiedRow(QSqlTableModelPrivate::Update,
556 QSqlQueryModel::record(row: index.row()));
557
558 row.setValue(c: index.column(), v: value);
559 emit dataChanged(topLeft: index, bottomRight: index);
560
561 if (d->strategy == OnFieldChange && row.op() != QSqlTableModelPrivate::Insert)
562 return submit();
563
564 return true;
565}
566
567/*!
568 \reimp
569 */
570bool QSqlTableModel::clearItemData(const QModelIndex &index)
571{
572 return setData(index, value: QVariant(), role: Qt::EditRole);
573}
574
575/*!
576 Updates the given \a row in the currently active database table
577 with the specified \a values. Returns \c true if successful; otherwise
578 returns \c false.
579
580 This is a low-level method that operates directly on the database
581 and should not be called directly. Use setData() to update values.
582 The model will decide depending on its edit strategy when to modify
583 the database.
584
585 Note that only values that have the generated-flag set are updated.
586 The generated-flag can be set with QSqlRecord::setGenerated() and
587 tested with QSqlRecord::isGenerated().
588
589 \sa QSqlRecord::isGenerated(), setData()
590*/
591bool QSqlTableModel::updateRowInTable(int row, const QSqlRecord &values)
592{
593 Q_D(QSqlTableModel);
594 QSqlRecord rec(values);
595 emit beforeUpdate(row, record&: rec);
596
597 const QSqlRecord whereValues = primaryValues(row);
598 const bool prepStatement = d->db.driver()->hasFeature(f: QSqlDriver::PreparedQueries);
599 const QString stmt = d->db.driver()->sqlStatement(type: QSqlDriver::UpdateStatement, tableName: d->tableName,
600 rec, preparedStatement: prepStatement);
601 const QString where = d->db.driver()->sqlStatement(type: QSqlDriver::WhereStatement, tableName: d->tableName,
602 rec: whereValues, preparedStatement: prepStatement);
603
604 if (stmt.isEmpty() || where.isEmpty() || row < 0 || row >= rowCount()) {
605 d->error = QSqlError("No Fields to update"_L1, QString(), QSqlError::StatementError);
606 return false;
607 }
608
609 return d->exec(stmt: SqlTm::concat(a: stmt, b: where), prepStatement, rec, whereValues);
610}
611
612
613/*!
614 Inserts the values \a values into the currently active database table.
615
616 This is a low-level method that operates directly on the database
617 and should not be called directly. Use insertRow() and setData()
618 to insert values. The model will decide depending on its edit strategy
619 when to modify the database.
620
621 Returns \c true if the values could be inserted, otherwise false.
622 Error information can be retrieved with \l lastError().
623
624 \sa lastError(), insertRow(), insertRows()
625*/
626bool QSqlTableModel::insertRowIntoTable(const QSqlRecord &values)
627{
628 Q_D(QSqlTableModel);
629 QSqlRecord rec = values;
630 emit beforeInsert(record&: rec);
631
632 const bool prepStatement = d->db.driver()->hasFeature(f: QSqlDriver::PreparedQueries);
633 const QString stmt = d->db.driver()->sqlStatement(type: QSqlDriver::InsertStatement, tableName: d->tableName,
634 rec, preparedStatement: prepStatement);
635
636 if (stmt.isEmpty()) {
637 d->error = QSqlError("No Fields to update"_L1, QString(), QSqlError::StatementError);
638 return false;
639 }
640
641 return d->exec(stmt, prepStatement, rec, whereValues: QSqlRecord() /* no where values */);
642}
643
644/*!
645 Deletes the given \a row from the currently active database table.
646
647 This is a low-level method that operates directly on the database
648 and should not be called directly. Use removeRow() or removeRows()
649 to delete values. The model will decide depending on its edit strategy
650 when to modify the database.
651
652 Returns \c true if the row was deleted; otherwise returns \c false.
653
654 \sa removeRow(), removeRows()
655*/
656bool QSqlTableModel::deleteRowFromTable(int row)
657{
658 Q_D(QSqlTableModel);
659 emit beforeDelete(row);
660
661 const QSqlRecord whereValues = primaryValues(row);
662 const bool prepStatement = d->db.driver()->hasFeature(f: QSqlDriver::PreparedQueries);
663 const QString stmt = d->db.driver()->sqlStatement(type: QSqlDriver::DeleteStatement,
664 tableName: d->tableName,
665 rec: QSqlRecord(),
666 preparedStatement: prepStatement);
667 const QString where = d->db.driver()->sqlStatement(type: QSqlDriver::WhereStatement,
668 tableName: d->tableName,
669 rec: whereValues,
670 preparedStatement: prepStatement);
671
672 if (stmt.isEmpty() || where.isEmpty()) {
673 d->error = QSqlError("Unable to delete row"_L1, QString(), QSqlError::StatementError);
674 return false;
675 }
676
677 return d->exec(stmt: SqlTm::concat(a: stmt, b: where), prepStatement, rec: QSqlRecord() /* no new values */, whereValues);
678}
679
680/*!
681 Submits all pending changes and returns \c true on success.
682 Returns \c false on error, detailed error information can be
683 obtained with lastError().
684
685 In OnManualSubmit, on success the model will be repopulated.
686 Any views presenting it will lose their selections.
687
688 Note: In OnManualSubmit mode, already submitted changes won't
689 be cleared from the cache when submitAll() fails. This allows
690 transactions to be rolled back and resubmitted without
691 losing data.
692
693 \sa revertAll(), lastError()
694*/
695bool QSqlTableModel::submitAll()
696{
697 Q_D(QSqlTableModel);
698
699 bool success = true;
700
701 const auto cachedKeys = d->cache.keys();
702 for (int row : cachedKeys) {
703 // be sure cache *still* contains the row since overridden selectRow() could have called select()
704 QSqlTableModelPrivate::CacheMap::iterator it = d->cache.find(key: row);
705 if (it == d->cache.end())
706 continue;
707
708 QSqlTableModelPrivate::ModifiedRow &mrow = it.value();
709 if (mrow.submitted())
710 continue;
711
712 switch (mrow.op()) {
713 case QSqlTableModelPrivate::Insert:
714 success = insertRowIntoTable(values: mrow.rec());
715 break;
716 case QSqlTableModelPrivate::Update:
717 success = updateRowInTable(row, values: mrow.rec());
718 break;
719 case QSqlTableModelPrivate::Delete:
720 success = deleteRowFromTable(row);
721 break;
722 case QSqlTableModelPrivate::None:
723 Q_ASSERT_X(false, "QSqlTableModel::submitAll()", "Invalid cache operation");
724 break;
725 }
726
727 if (success) {
728 if (d->strategy != OnManualSubmit && mrow.op() == QSqlTableModelPrivate::Insert) {
729 int c = mrow.rec().indexOf(name: d->autoColumn);
730 if (c != -1 && !mrow.rec().isGenerated(i: c))
731 mrow.setValue(c, v: d->editQuery.lastInsertId());
732 }
733 mrow.setSubmitted();
734 if (d->strategy != OnManualSubmit)
735 success = selectRow(row);
736 }
737
738 if (!success)
739 break;
740 }
741
742 if (success) {
743 if (d->strategy == OnManualSubmit)
744 success = select();
745 }
746
747 return success;
748}
749
750/*!
751 This reimplemented slot is called by the item delegates when the
752 user stopped editing the current row.
753
754 Submits the currently edited row if the model's strategy is set
755 to OnRowChange or OnFieldChange. Does nothing for the OnManualSubmit
756 strategy.
757
758 Use submitAll() to submit all pending changes for the
759 OnManualSubmit strategy.
760
761 Returns \c true on success; otherwise returns \c false. Use lastError()
762 to query detailed error information.
763
764 Does not automatically repopulate the model. Submitted rows are
765 refreshed from the database on success.
766
767 \sa revert(), revertRow(), submitAll(), revertAll(), lastError()
768*/
769bool QSqlTableModel::submit()
770{
771 Q_D(QSqlTableModel);
772 if (d->strategy == OnRowChange || d->strategy == OnFieldChange)
773 return submitAll();
774 return true;
775}
776
777/*!
778 This reimplemented slot is called by the item delegates when the
779 user canceled editing the current row.
780
781 Reverts the changes if the model's strategy is set to
782 OnRowChange or OnFieldChange. Does nothing for the OnManualSubmit
783 strategy.
784
785 Use revertAll() to revert all pending changes for the
786 OnManualSubmit strategy or revertRow() to revert a specific row.
787
788 \sa submit(), submitAll(), revertRow(), revertAll()
789*/
790void QSqlTableModel::revert()
791{
792 Q_D(QSqlTableModel);
793 if (d->strategy == OnRowChange || d->strategy == OnFieldChange)
794 revertAll();
795}
796
797/*!
798 \enum QSqlTableModel::EditStrategy
799
800 This enum type describes which strategy to choose when editing values in the database.
801
802 \value OnFieldChange All changes to the model will be applied immediately to the database.
803 \value OnRowChange Changes to a row will be applied when the user selects a different row.
804 \value OnManualSubmit All changes will be cached in the model until either submitAll()
805 or revertAll() is called.
806
807 Note: To prevent inserting only partly initialized rows into the database,
808 \c OnFieldChange will behave like \c OnRowChange for newly inserted rows.
809
810 \sa setEditStrategy()
811*/
812
813
814/*!
815 Sets the strategy for editing values in the database to \a
816 strategy.
817
818 This will revert any pending changes.
819
820 \sa editStrategy(), revertAll()
821*/
822void QSqlTableModel::setEditStrategy(EditStrategy strategy)
823{
824 Q_D(QSqlTableModel);
825 revertAll();
826 d->strategy = strategy;
827}
828
829/*!
830 Returns the current edit strategy.
831
832 \sa setEditStrategy()
833*/
834QSqlTableModel::EditStrategy QSqlTableModel::editStrategy() const
835{
836 Q_D(const QSqlTableModel);
837 return d->strategy;
838}
839
840/*!
841 Reverts all pending changes.
842
843 \sa revert(), revertRow(), submitAll()
844*/
845void QSqlTableModel::revertAll()
846{
847 Q_D(QSqlTableModel);
848
849 const QList<int> rows(d->cache.keys());
850 for (int i = rows.size() - 1; i >= 0; --i)
851 revertRow(row: rows.value(i));
852}
853
854/*!
855 Reverts all changes for the specified \a row.
856
857 \sa revert(), revertAll(), submit(), submitAll()
858*/
859void QSqlTableModel::revertRow(int row)
860{
861 if (row < 0)
862 return;
863
864 Q_D(QSqlTableModel);
865 d->revertCachedRow(row);
866}
867
868/*!
869 Returns the primary key for the current table, or an empty
870 QSqlIndex if the table is not set or has no primary key.
871
872 \sa setTable(), setPrimaryKey(), QSqlDatabase::primaryIndex()
873*/
874QSqlIndex QSqlTableModel::primaryKey() const
875{
876 Q_D(const QSqlTableModel);
877 return d->primaryIndex;
878}
879
880/*!
881 Protected method that allows subclasses to set the primary key to
882 \a key.
883
884 Normally, the primary index is set automatically whenever you
885 call setTable().
886
887 \sa primaryKey(), QSqlDatabase::primaryIndex()
888*/
889void QSqlTableModel::setPrimaryKey(const QSqlIndex &key)
890{
891 Q_D(QSqlTableModel);
892 d->primaryIndex = key;
893}
894
895/*!
896 Returns the model's database connection.
897*/
898QSqlDatabase QSqlTableModel::database() const
899{
900 Q_D(const QSqlTableModel);
901 return d->db;
902}
903
904/*!
905 Sorts the data by \a column with the sort order \a order.
906 This will immediately select data, use setSort()
907 to set a sort order without populating the model with data.
908
909 \sa setSort(), select(), orderByClause()
910*/
911void QSqlTableModel::sort(int column, Qt::SortOrder order)
912{
913 setSort(column, order);
914 select();
915}
916
917/*!
918 Sets the sort order for \a column to \a order. This does not
919 affect the current data, to refresh the data using the new
920 sort order, call select().
921
922 \sa select(), orderByClause()
923*/
924void QSqlTableModel::setSort(int column, Qt::SortOrder order)
925{
926 Q_D(QSqlTableModel);
927 d->sortColumn = column;
928 d->sortOrder = order;
929}
930
931/*!
932 Returns an SQL \c{ORDER BY} clause based on the currently set
933 sort order.
934
935 \sa setSort(), selectStatement()
936*/
937QString QSqlTableModel::orderByClause() const
938{
939 Q_D(const QSqlTableModel);
940 QSqlField f = d->rec.field(i: d->sortColumn);
941 if (!f.isValid())
942 return QString();
943
944 //we can safely escape the field because it would have been obtained from the database
945 //and have the correct case
946 QString field = d->db.driver()->escapeIdentifier(identifier: d->tableName, type: QSqlDriver::TableName)
947 + u'.'
948 + d->db.driver()->escapeIdentifier(identifier: f.name(), type: QSqlDriver::FieldName);
949 field = d->sortOrder == Qt::AscendingOrder ? SqlTm::asc(s: field) : SqlTm::desc(s: field);
950 return SqlTm::orderBy(s: field);
951}
952
953/*!
954 Returns the index of the field \a fieldName, or -1 if no corresponding field
955 exists in the model.
956*/
957int QSqlTableModel::fieldIndex(const QString &fieldName) const
958{
959 Q_D(const QSqlTableModel);
960 return d->rec.indexOf(name: fieldName);
961}
962
963/*!
964 Returns the SQL \c SELECT statement used internally to populate
965 the model. The statement includes the filter and the \c{ORDER BY}
966 clause.
967
968 \sa filter(), orderByClause()
969*/
970QString QSqlTableModel::selectStatement() const
971{
972 Q_D(const QSqlTableModel);
973 if (d->tableName.isEmpty()) {
974 d->error = QSqlError("No table name given"_L1, QString(), QSqlError::StatementError);
975 return QString();
976 }
977 if (d->rec.isEmpty()) {
978 d->error = QSqlError("Unable to find table "_L1 + d->tableName, QString(),
979 QSqlError::StatementError);
980 return QString();
981 }
982
983 const QString stmt = d->db.driver()->sqlStatement(type: QSqlDriver::SelectStatement,
984 tableName: d->tableName,
985 rec: d->rec,
986 preparedStatement: false);
987 if (stmt.isEmpty()) {
988 d->error = QSqlError("Unable to select fields from table "_L1 + d->tableName,
989 QString(), QSqlError::StatementError);
990 return stmt;
991 }
992 return SqlTm::concat(a: SqlTm::concat(a: stmt, b: SqlTm::where(s: d->filter)), b: orderByClause());
993}
994
995/*!
996 Removes \a count columns from the \a parent model, starting at
997 index \a column.
998
999 Returns if the columns were successfully removed; otherwise
1000 returns \c false.
1001
1002 \sa removeRows()
1003*/
1004bool QSqlTableModel::removeColumns(int column, int count, const QModelIndex &parent)
1005{
1006 Q_D(QSqlTableModel);
1007 if (parent.isValid() || column < 0 || column + count > d->rec.count())
1008 return false;
1009 for (int i = 0; i < count; ++i)
1010 d->rec.remove(pos: column);
1011 if (d->query.isActive())
1012 return select();
1013 return true;
1014}
1015
1016/*!
1017 Removes \a count rows starting at \a row. Since this model
1018 does not support hierarchical structures, \a parent must be
1019 an invalid model index.
1020
1021 When the edit strategy is OnManualSubmit, deletion of rows from
1022 the database is delayed until submitAll() is called.
1023
1024 For OnFieldChange and OnRowChange, only one row may be deleted
1025 at a time and only if no other row has a cached change. Deletions
1026 are submitted immediately to the database. The model retains a
1027 blank row for successfully deleted row until refreshed with select().
1028
1029 After failed deletion, the operation is not reverted in the model.
1030 The application may resubmit or revert.
1031
1032 Inserted but not yet successfully submitted rows in the range to be
1033 removed are immediately removed from the model.
1034
1035 Before a row is deleted from the database, the beforeDelete()
1036 signal is emitted.
1037
1038 If row < 0 or row + count > rowCount(), no action is taken and
1039 false is returned. Returns \c true if all rows could be removed;
1040 otherwise returns \c false. Detailed database error information
1041 can be retrieved using lastError().
1042
1043 \sa removeColumns(), insertRows()
1044*/
1045bool QSqlTableModel::removeRows(int row, int count, const QModelIndex &parent)
1046{
1047 Q_D(QSqlTableModel);
1048 if (parent.isValid() || row < 0 || count <= 0 || row + count > rowCount())
1049 return false;
1050
1051 if (d->strategy != OnManualSubmit)
1052 if (count > 1 || (d->cache.value(key: row).submitted() && isDirty()))
1053 return false;
1054
1055 // Iterate backwards so we don't have to worry about removed rows causing
1056 // higher cache entries to shift downwards.
1057 for (int idx = row + count - 1; idx >= row; --idx) {
1058 QSqlTableModelPrivate::ModifiedRow& mrow = d->cache[idx];
1059 if (mrow.op() == QSqlTableModelPrivate::Insert) {
1060 revertRow(row: idx);
1061 } else {
1062 if (mrow.op() == QSqlTableModelPrivate::None)
1063 mrow = QSqlTableModelPrivate::ModifiedRow(QSqlTableModelPrivate::Delete,
1064 QSqlQueryModel::record(row: idx));
1065 else
1066 mrow.setOp(QSqlTableModelPrivate::Delete);
1067 if (d->strategy == OnManualSubmit)
1068 emit headerDataChanged(orientation: Qt::Vertical, first: idx, last: idx);
1069 }
1070 }
1071
1072 if (d->strategy != OnManualSubmit)
1073 return submit();
1074
1075 return true;
1076}
1077
1078/*!
1079 Inserts \a count empty rows at position \a row. Note that \a
1080 parent must be invalid, since this model does not support
1081 parent-child relations.
1082
1083 For edit strategies OnFieldChange and OnRowChange, only one row
1084 may be inserted at a time and the model may not contain other
1085 cached changes.
1086
1087 The primeInsert() signal will be emitted for each new row.
1088 Connect to it if you want to initialize the new row with default
1089 values.
1090
1091 Does not submit rows, regardless of edit strategy.
1092
1093 Returns \c false if the parameters are out of bounds or the row cannot be
1094 inserted; otherwise returns \c true.
1095
1096 \sa primeInsert(), insertRecord()
1097*/
1098bool QSqlTableModel::insertRows(int row, int count, const QModelIndex &parent)
1099{
1100 Q_D(QSqlTableModel);
1101 if (row < 0 || count <= 0 || row > rowCount() || parent.isValid())
1102 return false;
1103
1104 if (d->strategy != OnManualSubmit)
1105 if (count != 1 || isDirty())
1106 return false;
1107
1108 d->busyInsertingRows = true;
1109 beginInsertRows(parent, first: row, last: row + count - 1);
1110
1111 if (d->strategy != OnManualSubmit)
1112 d->cache.empty();
1113
1114 if (!d->cache.isEmpty()) {
1115 QMap<int, QSqlTableModelPrivate::ModifiedRow>::Iterator it = d->cache.end();
1116 while (it != d->cache.begin() && (--it).key() >= row) {
1117 int oldKey = it.key();
1118 const QSqlTableModelPrivate::ModifiedRow oldValue = it.value();
1119 d->cache.erase(it);
1120 it = d->cache.insert(key: oldKey + count, value: oldValue);
1121 }
1122 }
1123
1124 for (int i = 0; i < count; ++i) {
1125 d->cache[row + i] = QSqlTableModelPrivate::ModifiedRow(QSqlTableModelPrivate::Insert,
1126 d->rec);
1127 emit primeInsert(row: row + i, record&: d->cache[row + i].recRef());
1128 }
1129
1130 endInsertRows();
1131 d->busyInsertingRows = false;
1132 return true;
1133}
1134
1135/*!
1136 Inserts the \a record at position \a row. If \a row is negative,
1137 the record will be appended to the end. Calls insertRows() and
1138 setRecord() internally.
1139
1140 Returns \c true if the record could be inserted, otherwise false.
1141
1142 Changes are submitted immediately for OnFieldChange and
1143 OnRowChange. Failure does not leave a new row in the model.
1144
1145 \sa insertRows(), removeRows(), setRecord()
1146*/
1147bool QSqlTableModel::insertRecord(int row, const QSqlRecord &record)
1148{
1149 if (row < 0)
1150 row = rowCount();
1151 if (!insertRow(arow: row, aparent: QModelIndex()))
1152 return false;
1153 if (!setRecord(row, record)) {
1154 revertRow(row);
1155 return false;
1156 }
1157 return true;
1158}
1159
1160/*! \reimp
1161*/
1162int QSqlTableModel::rowCount(const QModelIndex &parent) const
1163{
1164 Q_D(const QSqlTableModel);
1165
1166 if (parent.isValid())
1167 return 0;
1168
1169 return QSqlQueryModel::rowCount() + d->insertCount();
1170}
1171
1172/*!
1173 Returns the index of the value in the database result set for the
1174 given \a item in the model.
1175
1176 The return value is identical to \a item if no columns or rows
1177 have been inserted, removed, or moved around.
1178
1179 Returns an invalid model index if \a item is out of bounds or if
1180 \a item does not point to a value in the result set.
1181
1182 \sa QSqlQueryModel::indexInQuery()
1183*/
1184QModelIndex QSqlTableModel::indexInQuery(const QModelIndex &item) const
1185{
1186 Q_D(const QSqlTableModel);
1187 const auto it = d->cache.constFind(key: item.row());
1188 if (it != d->cache.constEnd() && it->insert())
1189 return QModelIndex();
1190
1191 const int rowOffset = d->insertCount(maxRow: item.row());
1192 return QSqlQueryModel::indexInQuery(item: createIndex(arow: item.row() - rowOffset, acolumn: item.column(), adata: item.internalPointer()));
1193}
1194
1195/*!
1196 Returns the currently set filter.
1197
1198 \sa setFilter(), select()
1199*/
1200QString QSqlTableModel::filter() const
1201{
1202 Q_D(const QSqlTableModel);
1203 return d->filter;
1204}
1205
1206/*!
1207 Sets the current filter to \a filter.
1208
1209 The filter is a SQL \c WHERE clause without the keyword \c WHERE
1210 (for example, \c{name='Josephine')}.
1211
1212 If the model is already populated with data from a database,
1213 the model re-selects it with the new filter. Otherwise, the filter
1214 will be applied the next time select() is called.
1215
1216 \sa filter(), select(), selectStatement(), orderByClause()
1217*/
1218void QSqlTableModel::setFilter(const QString &filter)
1219{
1220 Q_D(QSqlTableModel);
1221 d->filter = filter;
1222 if (d->query.isActive())
1223 select();
1224}
1225
1226/*! \reimp
1227*/
1228void QSqlTableModel::clear()
1229{
1230 Q_D(QSqlTableModel);
1231 beginResetModel();
1232 d->clear();
1233 QSqlQueryModel::clear();
1234 endResetModel();
1235}
1236
1237/*! \reimp
1238*/
1239Qt::ItemFlags QSqlTableModel::flags(const QModelIndex &index) const
1240{
1241 Q_D(const QSqlTableModel);
1242 if (index.internalPointer() || index.column() < 0 || index.column() >= d->rec.count()
1243 || index.row() < 0)
1244 return { };
1245
1246 bool editable = true;
1247
1248 if (d->rec.field(i: index.column()).isReadOnly()) {
1249 editable = false;
1250 }
1251 else {
1252 const QSqlTableModelPrivate::ModifiedRow mrow = d->cache.value(key: index.row());
1253 if (mrow.op() == QSqlTableModelPrivate::Delete) {
1254 editable = false;
1255 }
1256 else if (d->strategy == OnFieldChange) {
1257 if (mrow.op() != QSqlTableModelPrivate::Insert)
1258 if (!isDirty(index) && isDirty())
1259 editable = false;
1260 }
1261 else if (d->strategy == OnRowChange) {
1262 if (mrow.submitted() && isDirty())
1263 editable = false;
1264 }
1265 }
1266
1267 if (!editable)
1268 return QSqlQueryModel::flags(index);
1269 else
1270 return QSqlQueryModel::flags(index) | Qt::ItemIsEditable;
1271}
1272
1273/*!
1274 This is an overloaded function.
1275
1276 It returns an empty record, having only the field names. This function can be used to
1277 retrieve the field names of a record.
1278
1279 \sa QSqlRecord::isEmpty()
1280*/
1281QSqlRecord QSqlTableModel::record() const
1282{
1283 return QSqlQueryModel::record();
1284}
1285
1286/*!
1287\since 5.0
1288 Returns the record at \a row in the model.
1289
1290 If \a row is the index of a valid row, the record
1291 will be populated with values from that row.
1292
1293 If the model is not initialized, an empty record will be
1294 returned.
1295
1296 \sa QSqlRecord::isEmpty()
1297*/
1298QSqlRecord QSqlTableModel::record(int row) const
1299{
1300 Q_D(const QSqlTableModel);
1301
1302 // the query gets the values from virtual data()
1303 QSqlRecord rec = QSqlQueryModel::record(row);
1304
1305 // get generated flags from the cache
1306 const QSqlTableModelPrivate::ModifiedRow mrow = d->cache.value(key: row);
1307 if (mrow.op() != QSqlTableModelPrivate::None) {
1308 const QSqlRecord &crec = mrow.rec();
1309 for (int i = 0, cnt = rec.count(); i < cnt; ++i)
1310 rec.setGenerated(i, generated: crec.isGenerated(i));
1311 }
1312
1313 return rec;
1314}
1315
1316/*!
1317 Applies \a values to the \a row in the model. The source and
1318 target fields are mapped by field name, not by position in
1319 the record.
1320
1321 Note that the generated flags in \a values are preserved to
1322 determine whether the corresponding fields are used when changes
1323 are submitted to the database. By default, it is set to \c true
1324 for all fields in a QSqlRecord. You must set the flag to \c false
1325 using \l{QSqlRecord::}{setGenerated}(false) for any value in
1326 \a values, to save changes back to the database.
1327
1328 For edit strategies OnFieldChange and OnRowChange, a row may
1329 receive a change only if no other row has a cached change.
1330 Changes are submitted immediately. Submitted changes are not
1331 reverted upon failure.
1332
1333 Returns \c true if all the values could be set; otherwise returns
1334 false.
1335
1336 \sa record(), editStrategy()
1337*/
1338bool QSqlTableModel::setRecord(int row, const QSqlRecord &values)
1339{
1340 Q_D(QSqlTableModel);
1341 Q_ASSERT_X(row >= 0, "QSqlTableModel::setRecord()", "Cannot set a record to a row less than 0");
1342 if (d->busyInsertingRows)
1343 return false;
1344
1345 if (row >= rowCount())
1346 return false;
1347
1348 if (d->cache.value(key: row).op() == QSqlTableModelPrivate::Delete)
1349 return false;
1350
1351 if (d->strategy != OnManualSubmit && d->cache.value(key: row).submitted() && isDirty())
1352 return false;
1353
1354 // Check field names and remember mapping
1355 QMap<int, int> map;
1356 for (int i = 0; i < values.count(); ++i) {
1357 int idx = d->nameToIndex(name: values.fieldName(i));
1358 if (idx == -1)
1359 return false;
1360 map[i] = idx;
1361 }
1362
1363 QSqlTableModelPrivate::ModifiedRow &mrow = d->cache[row];
1364 if (mrow.op() == QSqlTableModelPrivate::None)
1365 mrow = QSqlTableModelPrivate::ModifiedRow(QSqlTableModelPrivate::Update,
1366 QSqlQueryModel::record(row));
1367
1368 for (const auto i : map.asKeyValueRange()) {
1369 // have to use virtual setData() here rather than mrow.setValue()
1370 EditStrategy strategy = d->strategy;
1371 d->strategy = OnManualSubmit;
1372 QModelIndex cIndex = createIndex(arow: row, acolumn: i.second);
1373 setData(index: cIndex, value: values.value(i: i.first));
1374 d->strategy = strategy;
1375 // setData() sets generated to TRUE, but source record should prevail.
1376 if (!values.isGenerated(i: i.first))
1377 mrow.recRef().setGenerated(i: i.second, generated: false);
1378 }
1379
1380 if (d->strategy != OnManualSubmit)
1381 return submit();
1382
1383 return true;
1384}
1385
1386/*!
1387 \since 5.1
1388 Returns a record containing the fields represented in the primary key set to the values
1389 at \a row. If no primary key is defined, the returned record will contain all fields.
1390
1391 \sa primaryKey()
1392*/
1393QSqlRecord QSqlTableModel::primaryValues(int row) const
1394{
1395 Q_D(const QSqlTableModel);
1396
1397 const QSqlRecord &pIndex = d->primaryIndex.isEmpty() ? d->rec : d->primaryIndex;
1398
1399 QSqlTableModelPrivate::ModifiedRow mr = d->cache.value(key: row);
1400 if (mr.op() != QSqlTableModelPrivate::None)
1401 return mr.primaryValues(pi: pIndex);
1402 else
1403 return QSqlQueryModel::record(row).keyValues(keyFields: pIndex);
1404}
1405
1406QT_END_NAMESPACE
1407
1408#include "moc_qsqltablemodel.cpp"
1409

Provided by KDAB

Privacy Policy
Start learning QML with our Intro Training
Find out more

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