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