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