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 QVector<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.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 21
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, 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, 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#if QT_VERSION >= QT_VERSION_CHECK(6, 0, 0)
611/*!
612 \reimp
613 */
614bool QStringListModel::clearItemData(const QModelIndex &index)
615{
616 return setData(index, QVariant(), Qt::EditRole);
617}
618#endif
619
620/*!
621 This function simply calls QSqlQueryModel::setQuery(\a query).
622 You should normally not call it on a QSqlTableModel. Instead, use
623 setTable(), setSort(), setFilter(), etc., to set up the query.
624
625 \sa selectStatement()
626*/
627void QSqlTableModel::setQuery(const QSqlQuery &query)
628{
629 QSqlQueryModel::setQuery(query);
630}
631
632/*!
633 Updates the given \a row in the currently active database table
634 with the specified \a values. Returns \c true if successful; otherwise
635 returns \c false.
636
637 This is a low-level method that operates directly on the database
638 and should not be called directly. Use setData() to update values.
639 The model will decide depending on its edit strategy when to modify
640 the database.
641
642 Note that only values that have the generated-flag set are updated.
643 The generated-flag can be set with QSqlRecord::setGenerated() and
644 tested with QSqlRecord::isGenerated().
645
646 \sa QSqlRecord::isGenerated(), setData()
647*/
648bool QSqlTableModel::updateRowInTable(int row, const QSqlRecord &values)
649{
650 Q_D(QSqlTableModel);
651 QSqlRecord rec(values);
652 emit beforeUpdate(row, rec);
653
654 const QSqlRecord whereValues = primaryValues(row);
655 const bool prepStatement = d->db.driver()->hasFeature(QSqlDriver::PreparedQueries);
656 const QString stmt = d->db.driver()->sqlStatement(QSqlDriver::UpdateStatement, d->tableName,
657 rec, prepStatement);
658 const QString where = d->db.driver()->sqlStatement(QSqlDriver::WhereStatement, d->tableName,
659 whereValues, prepStatement);
660
661 if (stmt.isEmpty() || where.isEmpty() || row < 0 || row >= rowCount()) {
662 d->error = QSqlError(QLatin1String("No Fields to update"), QString(),
663 QSqlError::StatementError);
664 return false;
665 }
666
667 return d->exec(Sql::concat(stmt, where), prepStatement, rec, whereValues);
668}
669
670
671/*!
672 Inserts the values \a values into the currently active database table.
673
674 This is a low-level method that operates directly on the database
675 and should not be called directly. Use insertRow() and setData()
676 to insert values. The model will decide depending on its edit strategy
677 when to modify the database.
678
679 Returns \c true if the values could be inserted, otherwise false.
680 Error information can be retrieved with \l lastError().
681
682 \sa lastError(), insertRow(), insertRows()
683*/
684bool QSqlTableModel::insertRowIntoTable(const QSqlRecord &values)
685{
686 Q_D(QSqlTableModel);
687 QSqlRecord rec = values;
688 emit beforeInsert(rec);
689
690 const bool prepStatement = d->db.driver()->hasFeature(QSqlDriver::PreparedQueries);
691 const QString stmt = d->db.driver()->sqlStatement(QSqlDriver::InsertStatement, d->tableName,
692 rec, prepStatement);
693
694 if (stmt.isEmpty()) {
695 d->error = QSqlError(QLatin1String("No Fields to update"), QString(),
696 QSqlError::StatementError);
697 return false;
698 }
699
700 return d->exec(stmt, prepStatement, rec, QSqlRecord() /* no where values */);
701}
702
703/*!
704 Deletes the given \a row from the currently active database table.
705
706 This is a low-level method that operates directly on the database
707 and should not be called directly. Use removeRow() or removeRows()
708 to delete values. The model will decide depending on its edit strategy
709 when to modify the database.
710
711 Returns \c true if the row was deleted; otherwise returns \c false.
712
713 \sa removeRow(), removeRows()
714*/
715bool QSqlTableModel::deleteRowFromTable(int row)
716{
717 Q_D(QSqlTableModel);
718 emit beforeDelete(row);
719
720 const QSqlRecord whereValues = primaryValues(row);
721 const bool prepStatement = d->db.driver()->hasFeature(QSqlDriver::PreparedQueries);
722 const QString stmt = d->db.driver()->sqlStatement(QSqlDriver::DeleteStatement,
723 d->tableName,
724 QSqlRecord(),
725 prepStatement);
726 const QString where = d->db.driver()->sqlStatement(QSqlDriver::WhereStatement,
727 d->tableName,
728 whereValues,
729 prepStatement);
730
731 if (stmt.isEmpty() || where.isEmpty()) {
732 d->error = QSqlError(QLatin1String("Unable to delete row"), QString(),
733 QSqlError::StatementError);
734 return false;
735 }
736
737 return d->exec(Sql::concat(stmt, where), prepStatement, QSqlRecord() /* no new values */, whereValues);
738}
739
740/*!
741 Submits all pending changes and returns \c true on success.
742 Returns \c false on error, detailed error information can be
743 obtained with lastError().
744
745 In OnManualSubmit, on success the model will be repopulated.
746 Any views presenting it will lose their selections.
747
748 Note: In OnManualSubmit mode, already submitted changes won't
749 be cleared from the cache when submitAll() fails. This allows
750 transactions to be rolled back and resubmitted without
751 losing data.
752
753 \sa revertAll(), lastError()
754*/
755bool QSqlTableModel::submitAll()
756{
757 Q_D(QSqlTableModel);
758
759 bool success = true;
760
761 const auto cachedKeys = d->cache.keys();
762 for (int row : cachedKeys) {
763 // be sure cache *still* contains the row since overridden selectRow() could have called select()
764 QSqlTableModelPrivate::CacheMap::iterator it = d->cache.find(row);
765 if (it == d->cache.end())
766 continue;
767
768 QSqlTableModelPrivate::ModifiedRow &mrow = it.value();
769 if (mrow.submitted())
770 continue;
771
772 switch (mrow.op()) {
773 case QSqlTableModelPrivate::Insert:
774 success = insertRowIntoTable(mrow.rec());
775 break;
776 case QSqlTableModelPrivate::Update:
777 success = updateRowInTable(row, mrow.rec());
778 break;
779 case QSqlTableModelPrivate::Delete:
780 success = deleteRowFromTable(row);
781 break;
782 case QSqlTableModelPrivate::None:
783 Q_ASSERT_X(false, "QSqlTableModel::submitAll()", "Invalid cache operation");
784 break;
785 }
786
787 if (success) {
788 if (d->strategy != OnManualSubmit && mrow.op() == QSqlTableModelPrivate::Insert) {
789 int c = mrow.rec().indexOf(d->autoColumn);
790 if (c != -1 && !mrow.rec().isGenerated(c))
791 mrow.setValue(c, d->editQuery.lastInsertId());
792 }
793 mrow.setSubmitted();
794 if (d->strategy != OnManualSubmit)
795 success = selectRow(row);
796 }
797
798 if (!success)
799 break;
800 }
801
802 if (success) {
803 if (d->strategy == OnManualSubmit)
804 success = select();
805 }
806
807 return success;
808}
809
810/*!
811 This reimplemented slot is called by the item delegates when the
812 user stopped editing the current row.
813
814 Submits the currently edited row if the model's strategy is set
815 to OnRowChange or OnFieldChange. Does nothing for the OnManualSubmit
816 strategy.
817
818 Use submitAll() to submit all pending changes for the
819 OnManualSubmit strategy.
820
821 Returns \c true on success; otherwise returns \c false. Use lastError()
822 to query detailed error information.
823
824 Does not automatically repopulate the model. Submitted rows are
825 refreshed from the database on success.
826
827 \sa revert(), revertRow(), submitAll(), revertAll(), lastError()
828*/
829bool QSqlTableModel::submit()
830{
831 Q_D(QSqlTableModel);
832 if (d->strategy == OnRowChange || d->strategy == OnFieldChange)
833 return submitAll();
834 return true;
835}
836
837/*!
838 This reimplemented slot is called by the item delegates when the
839 user canceled editing the current row.
840
841 Reverts the changes if the model's strategy is set to
842 OnRowChange or OnFieldChange. Does nothing for the OnManualSubmit
843 strategy.
844
845 Use revertAll() to revert all pending changes for the
846 OnManualSubmit strategy or revertRow() to revert a specific row.
847
848 \sa submit(), submitAll(), revertRow(), revertAll()
849*/
850void QSqlTableModel::revert()
851{
852 Q_D(QSqlTableModel);
853 if (d->strategy == OnRowChange || d->strategy == OnFieldChange)
854 revertAll();
855}
856
857/*!
858 \enum QSqlTableModel::EditStrategy
859
860 This enum type describes which strategy to choose when editing values in the database.
861
862 \value OnFieldChange All changes to the model will be applied immediately to the database.
863 \value OnRowChange Changes to a row will be applied when the user selects a different row.
864 \value OnManualSubmit All changes will be cached in the model until either submitAll()
865 or revertAll() is called.
866
867 Note: To prevent inserting only partly initialized rows into the database,
868 \c OnFieldChange will behave like \c OnRowChange for newly inserted rows.
869
870 \sa setEditStrategy()
871*/
872
873
874/*!
875 Sets the strategy for editing values in the database to \a
876 strategy.
877
878 This will revert any pending changes.
879
880 \sa editStrategy(), revertAll()
881*/
882void QSqlTableModel::setEditStrategy(EditStrategy strategy)
883{
884 Q_D(QSqlTableModel);
885 revertAll();
886 d->strategy = strategy;
887}
888
889/*!
890 Returns the current edit strategy.
891
892 \sa setEditStrategy()
893*/
894QSqlTableModel::EditStrategy QSqlTableModel::editStrategy() const
895{
896 Q_D(const QSqlTableModel);
897 return d->strategy;
898}
899
900/*!
901 Reverts all pending changes.
902
903 \sa revert(), revertRow(), submitAll()
904*/
905void QSqlTableModel::revertAll()
906{
907 Q_D(QSqlTableModel);
908
909 const QList<int> rows(d->cache.keys());
910 for (int i = rows.size() - 1; i >= 0; --i)
911 revertRow(rows.value(i));
912}
913
914/*!
915 Reverts all changes for the specified \a row.
916
917 \sa revert(), revertAll(), submit(), submitAll()
918*/
919void QSqlTableModel::revertRow(int row)
920{
921 if (row < 0)
922 return;
923
924 Q_D(QSqlTableModel);
925 d->revertCachedRow(row);
926}
927
928/*!
929 Returns the primary key for the current table, or an empty
930 QSqlIndex if the table is not set or has no primary key.
931
932 \sa setTable(), setPrimaryKey(), QSqlDatabase::primaryIndex()
933*/
934QSqlIndex QSqlTableModel::primaryKey() const
935{
936 Q_D(const QSqlTableModel);
937 return d->primaryIndex;
938}
939
940/*!
941 Protected method that allows subclasses to set the primary key to
942 \a key.
943
944 Normally, the primary index is set automatically whenever you
945 call setTable().
946
947 \sa primaryKey(), QSqlDatabase::primaryIndex()
948*/
949void QSqlTableModel::setPrimaryKey(const QSqlIndex &key)
950{
951 Q_D(QSqlTableModel);
952 d->primaryIndex = key;
953}
954
955/*!
956 Returns the model's database connection.
957*/
958QSqlDatabase QSqlTableModel::database() const
959{
960 Q_D(const QSqlTableModel);
961 return d->db;
962}
963
964/*!
965 Sorts the data by \a column with the sort order \a order.
966 This will immediately select data, use setSort()
967 to set a sort order without populating the model with data.
968
969 \sa setSort(), select(), orderByClause()
970*/
971void QSqlTableModel::sort(int column, Qt::SortOrder order)
972{
973 setSort(column, order);
974 select();
975}
976
977/*!
978 Sets the sort order for \a column to \a order. This does not
979 affect the current data, to refresh the data using the new
980 sort order, call select().
981
982 \sa select(), orderByClause()
983*/
984void QSqlTableModel::setSort(int column, Qt::SortOrder order)
985{
986 Q_D(QSqlTableModel);
987 d->sortColumn = column;
988 d->sortOrder = order;
989}
990
991/*!
992 Returns an SQL \c{ORDER BY} clause based on the currently set
993 sort order.
994
995 \sa setSort(), selectStatement()
996*/
997QString QSqlTableModel::orderByClause() const
998{
999 Q_D(const QSqlTableModel);
1000 QSqlField f = d->rec.field(d->sortColumn);
1001 if (!f.isValid())
1002 return QString();
1003
1004 //we can safely escape the field because it would have been obtained from the database
1005 //and have the correct case
1006 QString field = d->tableName + QLatin1Char('.')
1007 + d->db.driver()->escapeIdentifier(f.name(), QSqlDriver::FieldName);
1008 field = d->sortOrder == Qt::AscendingOrder ? Sql::asc(field) : Sql::desc(field);
1009 return Sql::orderBy(field);
1010}
1011
1012/*!
1013 Returns the index of the field \a fieldName, or -1 if no corresponding field
1014 exists in the model.
1015*/
1016int QSqlTableModel::fieldIndex(const QString &fieldName) const
1017{
1018 Q_D(const QSqlTableModel);
1019 return d->rec.indexOf(fieldName);
1020}
1021
1022/*!
1023 Returns the SQL \c SELECT statement used internally to populate
1024 the model. The statement includes the filter and the \c{ORDER BY}
1025 clause.
1026
1027 \sa filter(), orderByClause()
1028*/
1029QString QSqlTableModel::selectStatement() const
1030{
1031 Q_D(const QSqlTableModel);
1032 if (d->tableName.isEmpty()) {
1033 d->error = QSqlError(QLatin1String("No table name given"), QString(),
1034 QSqlError::StatementError);
1035 return QString();
1036 }
1037 if (d->rec.isEmpty()) {
1038 d->error = QSqlError(QLatin1String("Unable to find table ") + d->tableName, QString(),
1039 QSqlError::StatementError);
1040 return QString();
1041 }
1042
1043 const QString stmt = d->db.driver()->sqlStatement(QSqlDriver::SelectStatement,
1044 d->tableName,
1045 d->rec,
1046 false);
1047 if (stmt.isEmpty()) {
1048 d->error = QSqlError(QLatin1String("Unable to select fields from table ") + d->tableName,
1049 QString(), QSqlError::StatementError);
1050 return stmt;
1051 }
1052 return Sql::concat(Sql::concat(stmt, Sql::where(d->filter)), orderByClause());
1053}
1054
1055/*!
1056 Removes \a count columns from the \a parent model, starting at
1057 index \a column.
1058
1059 Returns if the columns were successfully removed; otherwise
1060 returns \c false.
1061
1062 \sa removeRows()
1063*/
1064bool QSqlTableModel::removeColumns(int column, int count, const QModelIndex &parent)
1065{
1066 Q_D(QSqlTableModel);
1067 if (parent.isValid() || column < 0 || column + count > d->rec.count())
1068 return false;
1069 for (int i = 0; i < count; ++i)
1070 d->rec.remove(column);
1071 if (d->query.isActive())
1072 return select();
1073 return true;
1074}
1075
1076/*!
1077 Removes \a count rows starting at \a row. Since this model
1078 does not support hierarchical structures, \a parent must be
1079 an invalid model index.
1080
1081 When the edit strategy is OnManualSubmit, deletion of rows from
1082 the database is delayed until submitAll() is called.
1083
1084 For OnFieldChange and OnRowChange, only one row may be deleted
1085 at a time and only if no other row has a cached change. Deletions
1086 are submitted immediately to the database. The model retains a
1087 blank row for successfully deleted row until refreshed with select().
1088
1089 After failed deletion, the operation is not reverted in the model.
1090 The application may resubmit or revert.
1091
1092 Inserted but not yet successfully submitted rows in the range to be
1093 removed are immediately removed from the model.
1094
1095 Before a row is deleted from the database, the beforeDelete()
1096 signal is emitted.
1097
1098 If row < 0 or row + count > rowCount(), no action is taken and
1099 false is returned. Returns \c true if all rows could be removed;
1100 otherwise returns \c false. Detailed database error information
1101 can be retrieved using lastError().
1102
1103 \sa removeColumns(), insertRows()
1104*/
1105bool QSqlTableModel::removeRows(int row, int count, const QModelIndex &parent)
1106{
1107 Q_D(QSqlTableModel);
1108 if (parent.isValid() || row < 0 || count <= 0)
1109 return false;
1110 else if (row + count > rowCount())
1111 return false;
1112 else if (!count)
1113 return true;
1114
1115 if (d->strategy != OnManualSubmit)
1116 if (count > 1 || (d->cache.value(row).submitted() && isDirty()))
1117 return false;
1118
1119 // Iterate backwards so we don't have to worry about removed rows causing
1120 // higher cache entries to shift downwards.
1121 for (int idx = row + count - 1; idx >= row; --idx) {
1122 QSqlTableModelPrivate::ModifiedRow& mrow = d->cache[idx];
1123 if (mrow.op() == QSqlTableModelPrivate::Insert) {
1124 revertRow(idx);
1125 } else {
1126 if (mrow.op() == QSqlTableModelPrivate::None)
1127 mrow = QSqlTableModelPrivate::ModifiedRow(QSqlTableModelPrivate::Delete,
1128 QSqlQueryModel::record(idx));
1129 else
1130 mrow.setOp(QSqlTableModelPrivate::Delete);
1131 if (d->strategy == OnManualSubmit)
1132 emit headerDataChanged(Qt::Vertical, idx, idx);
1133 }
1134 }
1135
1136 if (d->strategy != OnManualSubmit)
1137 return submit();
1138
1139 return true;
1140}
1141
1142/*!
1143 Inserts \a count empty rows at position \a row. Note that \a
1144 parent must be invalid, since this model does not support
1145 parent-child relations.
1146
1147 For edit strategies OnFieldChange and OnRowChange, only one row
1148 may be inserted at a time and the model may not contain other
1149 cached changes.
1150
1151 The primeInsert() signal will be emitted for each new row.
1152 Connect to it if you want to initialize the new row with default
1153 values.
1154
1155 Does not submit rows, regardless of edit strategy.
1156
1157 Returns \c false if the parameters are out of bounds or the row cannot be
1158 inserted; otherwise returns \c true.
1159
1160 \sa primeInsert(), insertRecord()
1161*/
1162bool QSqlTableModel::insertRows(int row, int count, const QModelIndex &parent)
1163{
1164 Q_D(QSqlTableModel);
1165 if (row < 0 || count <= 0 || row > rowCount() || parent.isValid())
1166 return false;
1167
1168 if (d->strategy != OnManualSubmit)
1169 if (count != 1 || isDirty())
1170 return false;
1171
1172 d->busyInsertingRows = true;
1173 beginInsertRows(parent, row, row + count - 1);
1174
1175 if (d->strategy != OnManualSubmit)
1176 d->cache.empty();
1177
1178 if (!d->cache.isEmpty()) {
1179 QMap<int, QSqlTableModelPrivate::ModifiedRow>::Iterator it = d->cache.end();
1180 while (it != d->cache.begin() && (--it).key() >= row) {
1181 int oldKey = it.key();
1182 const QSqlTableModelPrivate::ModifiedRow oldValue = it.value();
1183 d->cache.erase(it);
1184 it = d->cache.insert(oldKey + count, oldValue);
1185 }
1186 }
1187
1188 for (int i = 0; i < count; ++i) {
1189 d->cache[row + i] = QSqlTableModelPrivate::ModifiedRow(QSqlTableModelPrivate::Insert,
1190 d->rec);
1191 emit primeInsert(row + i, d->cache[row + i].recRef());
1192 }
1193
1194 endInsertRows();
1195 d->busyInsertingRows = false;
1196 return true;
1197}
1198
1199/*!
1200 Inserts the \a record at position \a row. If \a row is negative,
1201 the record will be appended to the end. Calls insertRows() and
1202 setRecord() internally.
1203
1204 Returns \c true if the record could be inserted, otherwise false.
1205
1206 Changes are submitted immediately for OnFieldChange and
1207 OnRowChange. Failure does not leave a new row in the model.
1208
1209 \sa insertRows(), removeRows(), setRecord()
1210*/
1211bool QSqlTableModel::insertRecord(int row, const QSqlRecord &record)
1212{
1213 if (row < 0)
1214 row = rowCount();
1215 if (!insertRow(row, QModelIndex()))
1216 return false;
1217 if (!setRecord(row, record)) {
1218 revertRow(row);
1219 return false;
1220 }
1221 return true;
1222}
1223
1224/*! \reimp
1225*/
1226int QSqlTableModel::rowCount(const QModelIndex &parent) const
1227{
1228 Q_D(const QSqlTableModel);
1229
1230 if (parent.isValid())
1231 return 0;
1232
1233 return QSqlQueryModel::rowCount() + d->insertCount();
1234}
1235
1236/*!
1237 Returns the index of the value in the database result set for the
1238 given \a item in the model.
1239
1240 The return value is identical to \a item if no columns or rows
1241 have been inserted, removed, or moved around.
1242
1243 Returns an invalid model index if \a item is out of bounds or if
1244 \a item does not point to a value in the result set.
1245
1246 \sa QSqlQueryModel::indexInQuery()
1247*/
1248QModelIndex QSqlTableModel::indexInQuery(const QModelIndex &item) const
1249{
1250 Q_D(const QSqlTableModel);
1251 const auto it = d->cache.constFind(item.row());
1252 if (it != d->cache.constEnd() && it->insert())
1253 return QModelIndex();
1254
1255 const int rowOffset = d->insertCount(item.row());
1256 return QSqlQueryModel::indexInQuery(createIndex(item.row() - rowOffset, item.column(), item.internalPointer()));
1257}
1258
1259/*!
1260 Returns the currently set filter.
1261
1262 \sa setFilter(), select()
1263*/
1264QString QSqlTableModel::filter() const
1265{
1266 Q_D(const QSqlTableModel);
1267 return d->filter;
1268}
1269
1270/*!
1271 Sets the current filter to \a filter.
1272
1273 The filter is a SQL \c WHERE clause without the keyword \c WHERE
1274 (for example, \c{name='Josephine')}.
1275
1276 If the model is already populated with data from a database,
1277 the model re-selects it with the new filter. Otherwise, the filter
1278 will be applied the next time select() is called.
1279
1280 \sa filter(), select(), selectStatement(), orderByClause()
1281*/
1282void QSqlTableModel::setFilter(const QString &filter)
1283{
1284 Q_D(QSqlTableModel);
1285 d->filter = filter;
1286 if (d->query.isActive())
1287 select();
1288}
1289
1290/*! \reimp
1291*/
1292void QSqlTableModel::clear()
1293{
1294 Q_D(QSqlTableModel);
1295 beginResetModel();
1296 d->clear();
1297 QSqlQueryModel::clear();
1298 endResetModel();
1299}
1300
1301/*! \reimp
1302*/
1303Qt::ItemFlags QSqlTableModel::flags(const QModelIndex &index) const
1304{
1305 Q_D(const QSqlTableModel);
1306 if (index.internalPointer() || index.column() < 0 || index.column() >= d->rec.count()
1307 || index.row() < 0)
1308 return 0;
1309
1310 bool editable = true;
1311
1312 if (d->rec.field(index.column()).isReadOnly()) {
1313 editable = false;
1314 }
1315 else {
1316 const QSqlTableModelPrivate::ModifiedRow mrow = d->cache.value(index.row());
1317 if (mrow.op() == QSqlTableModelPrivate::Delete) {
1318 editable = false;
1319 }
1320 else if (d->strategy == OnFieldChange) {
1321 if (mrow.op() != QSqlTableModelPrivate::Insert)
1322 if (!isDirty(index) && isDirty())
1323 editable = false;
1324 }
1325 else if (d->strategy == OnRowChange) {
1326 if (mrow.submitted() && isDirty())
1327 editable = false;
1328 }
1329 }
1330
1331 if (!editable)
1332 return QSqlQueryModel::flags(index);
1333 else
1334 return QSqlQueryModel::flags(index) | Qt::ItemIsEditable;
1335}
1336
1337/*!
1338 This is an overloaded function.
1339
1340 It returns an empty record, having only the field names. This function can be used to
1341 retrieve the field names of a record.
1342
1343 \sa QSqlRecord::isEmpty()
1344*/
1345QSqlRecord QSqlTableModel::record() const
1346{
1347 return QSqlQueryModel::record();
1348}
1349
1350/*!
1351\since 5.0
1352 Returns the record at \a row in the model.
1353
1354 If \a row is the index of a valid row, the record
1355 will be populated with values from that row.
1356
1357 If the model is not initialized, an empty record will be
1358 returned.
1359
1360 \sa QSqlRecord::isEmpty()
1361*/
1362QSqlRecord QSqlTableModel::record(int row) const
1363{
1364 Q_D(const QSqlTableModel);
1365
1366 // the query gets the values from virtual data()
1367 QSqlRecord rec = QSqlQueryModel::record(row);
1368
1369 // get generated flags from the cache
1370 const QSqlTableModelPrivate::ModifiedRow mrow = d->cache.value(row);
1371 if (mrow.op() != QSqlTableModelPrivate::None) {
1372 const QSqlRecord crec = mrow.rec();
1373 for (int i = 0, cnt = rec.count(); i < cnt; ++i)
1374 rec.setGenerated(i, crec.isGenerated(i));
1375 }
1376
1377 return rec;
1378}
1379
1380/*!
1381 Applies \a values to the \a row in the model. The source and
1382 target fields are mapped by field name, not by position in
1383 the record.
1384
1385 Note that the generated flags in \a values are preserved to
1386 determine whether the corresponding fields are used when changes
1387 are submitted to the database. By default, it is set to \c true
1388 for all fields in a QSqlRecord. You must set the flag to \c false
1389 using \l{QSqlRecord::}{setGenerated}(false) for any value in
1390 \a values, to save changes back to the database.
1391
1392 For edit strategies OnFieldChange and OnRowChange, a row may
1393 receive a change only if no other row has a cached change.
1394 Changes are submitted immediately. Submitted changes are not
1395 reverted upon failure.
1396
1397 Returns \c true if all the values could be set; otherwise returns
1398 false.
1399
1400 \sa record(), editStrategy()
1401*/
1402bool QSqlTableModel::setRecord(int row, const QSqlRecord &values)
1403{
1404 Q_D(QSqlTableModel);
1405 Q_ASSERT_X(row >= 0, "QSqlTableModel::setRecord()", "Cannot set a record to a row less than 0");
1406 if (d->busyInsertingRows)
1407 return false;
1408
1409 if (row >= rowCount())
1410 return false;
1411
1412 if (d->cache.value(row).op() == QSqlTableModelPrivate::Delete)
1413 return false;
1414
1415 if (d->strategy != OnManualSubmit && d->cache.value(row).submitted() && isDirty())
1416 return false;
1417
1418 // Check field names and remember mapping
1419 typedef QMap<int, int> Map;
1420 Map map;
1421 for (int i = 0; i < values.count(); ++i) {
1422 int idx = d->nameToIndex(values.fieldName(i));
1423 if (idx == -1)
1424 return false;
1425 map[i] = idx;
1426 }
1427
1428 QSqlTableModelPrivate::ModifiedRow &mrow = d->cache[row];
1429 if (mrow.op() == QSqlTableModelPrivate::None)
1430 mrow = QSqlTableModelPrivate::ModifiedRow(QSqlTableModelPrivate::Update,
1431 QSqlQueryModel::record(row));
1432
1433 Map::const_iterator i = map.constBegin();
1434 const Map::const_iterator e = map.constEnd();
1435 for ( ; i != e; ++i) {
1436 // have to use virtual setData() here rather than mrow.setValue()
1437 EditStrategy strategy = d->strategy;
1438 d->strategy = OnManualSubmit;
1439 QModelIndex cIndex = createIndex(row, i.value());
1440 setData(cIndex, values.value(i.key()));
1441 d->strategy = strategy;
1442 // setData() sets generated to TRUE, but source record should prevail.
1443 if (!values.isGenerated(i.key()))
1444 mrow.recRef().setGenerated(i.value(), false);
1445 }
1446
1447 if (d->strategy != OnManualSubmit)
1448 return submit();
1449
1450 return true;
1451}
1452
1453/*!
1454 \since 5.1
1455 Returns a record containing the fields represented in the primary key set to the values
1456 at \a row. If no primary key is defined, the returned record will contain all fields.
1457
1458 \sa primaryKey()
1459*/
1460QSqlRecord QSqlTableModel::primaryValues(int row) const
1461{
1462 Q_D(const QSqlTableModel);
1463
1464 const QSqlRecord &pIndex = d->primaryIndex.isEmpty() ? d->rec : d->primaryIndex;
1465
1466 QSqlTableModelPrivate::ModifiedRow mr = d->cache.value(row);
1467 if (mr.op() != QSqlTableModelPrivate::None)
1468 return mr.primaryValues(pIndex);
1469 else
1470 return QSqlQueryModel::record(row).keyValues(pIndex);
1471}
1472
1473QT_END_NAMESPACE
1474