QSqlTableModel: support refreshing inserted rows with auto columns

Previously, selectRow() did not work after INSERTing a new row into a
table with an automatically populated column. It did not work because
the model did not know the primary values for the new row. Newly
inserted rows were therefore not refreshed in OnFieldChange and
OnRowChange edit strategies.

This change provides support for the typical simple case where a single
column is populated by the database and can be retrieved with
QSqlQuery::lastInsertId().

Task-Number: QTBUG-29102
Change-Id: Ibf0f0ac8661185bde57034ddf40c2178bece4778
Reviewed-by: Andy Shaw <andy.shaw@digia.com>
Reviewed-by: Lukas Geyer <lgeyer@gmx.at>
Reviewed-by: Mark Brand <mabrand@mabrand.nl>

3 files changed, 84 insertions, 0 deletions

diff --git a/src/sql/models/qsqltablemodel.cpp b/src/sql/models/qsqltablemodel.cpp
index a2a83e6a4b..2822c8bb73 100644
--- a/src/sql/models/qsqltablemodel.cpp
+++ b/src/sql/models/qsqltablemodel.cpp
@@ -355,6 +355,16 @@ void QSqlTableModel::setTable(const QString &tableName)
     if (d->rec.count() == 0)
         d->error = QSqlError(QLatin1String("Unable to find table ") + d->tableName, QString(),
                              QSqlError::StatementError);
+
+    // Remember the auto index column if there is one now.
+    // The record that will be obtained from the query after select lacks this feature.
+    d->autoColumn.clear();
+    for (int c = 0; c < d->rec.count(); ++c) {
+        if (d->rec.field(c).isAutoValue()) {
+            d->autoColumn = d->rec.fieldName(c);
+            break;
+        }
+    }
 }
 
 /*!
@@ -775,6 +785,11 @@ bool QSqlTableModel::submitAll()
         }
 
         if (success) {
+            if (d->strategy != OnManualSubmit && mrow.op() == QSqlTableModelPrivate::Insert) {
+                int c = mrow.rec().indexOf(d->autoColumn);
+                if (c != -1 && !mrow.rec().isGenerated(c))
+                    mrow.setValue(c, d->editQuery.lastInsertId());
+            }
             mrow.setSubmitted();
             if (d->strategy != OnManualSubmit)
                 success = selectRow(row);
diff --git a/src/sql/models/qsqltablemodel_p.h b/src/sql/models/qsqltablemodel_p.h
index 56db09b7e0..825490ea39 100644
--- a/src/sql/models/qsqltablemodel_p.h
+++ b/src/sql/models/qsqltablemodel_p.h
@@ -96,6 +96,7 @@ public:
     QSqlIndex primaryIndex;
     QString tableName;
     QString filter;
+    QString autoColumn;
 
     enum Op { None, Insert, Update, Delete };
 
diff --git a/tests/auto/sql/models/qsqltablemodel/tst_qsqltablemodel.cpp b/tests/auto/sql/models/qsqltablemodel/tst_qsqltablemodel.cpp
index 8a1ff8a24a..2ad680b605 100644
--- a/tests/auto/sql/models/qsqltablemodel/tst_qsqltablemodel.cpp
+++ b/tests/auto/sql/models/qsqltablemodel/tst_qsqltablemodel.cpp
@@ -99,6 +99,8 @@ private slots:
     void insertRecord();
     void insertMultiRecords_data() { generic_data(); }
     void insertMultiRecords();
+    void insertWithAutoColumn_data() { generic_data_with_strategies("QSQLITE"); }
+    void insertWithAutoColumn();
     void removeRow_data() { generic_data(); }
     void removeRow();
     void removeRows_data() { generic_data(); }
@@ -963,6 +965,72 @@ void tst_QSqlTableModel::insertMultiRecords()
     QCOMPARE(model.data(model.index(5, 2)).toInt(), 1);
 }
 
+void tst_QSqlTableModel::insertWithAutoColumn()
+{
+    QFETCH(QString, dbName);
+    QFETCH(int, submitpolicy_i);
+    QSqlTableModel::EditStrategy submitpolicy = (QSqlTableModel::EditStrategy) submitpolicy_i;
+    QSqlDatabase db = QSqlDatabase::database(dbName);
+    CHECK_DATABASE(db);
+
+    QString tbl = qTableName("autoColumnTest", __FILE__);
+    QSqlQuery q(db);
+    q.exec("DROP TABLE " + tbl);
+    QVERIFY_SQL(q, exec("CREATE TABLE " + tbl + "(id INTEGER PRIMARY KEY AUTOINCREMENT, val TEXT)"));
+
+    QSqlTableModel model(0, db);
+    model.setTable(tbl);
+    model.setSort(0, Qt::AscendingOrder);
+    model.setEditStrategy(submitpolicy);
+
+    QVERIFY_SQL(model, select());
+    QCOMPARE(model.rowCount(), 0);
+
+    // For insertRow/insertRows, we have to touch at least one column
+    // or else the generated flag won't be set, which would lead to
+    // an empty column list in the INSERT statement, which generally
+    // does not work.
+    if (submitpolicy != QSqlTableModel::OnManualSubmit) {
+        for (int id = 1; id <= 2; ++id) {
+            QVERIFY_SQL(model, insertRow(0));
+            QVERIFY_SQL(model, setData(model.index(0, 1), QString("foo")));
+            QVERIFY_SQL(model, submit());
+            QCOMPARE(model.data(model.index(0, 0)).toInt(), id);
+        }
+    } else {
+        QVERIFY_SQL(model, insertRows(0, 2));
+        QVERIFY_SQL(model, setData(model.index(0, 1), QString("foo")));
+        QVERIFY_SQL(model, setData(model.index(1, 1), QString("foo")));
+    }
+
+    QCOMPARE(model.rowCount(), 2);
+
+    QSqlRecord rec = db.record(tbl);
+    QVERIFY(rec.field(0).isAutoValue());
+    rec.setGenerated(0, false);
+
+    QVERIFY_SQL(model, insertRecord(0, rec));
+    if (submitpolicy != QSqlTableModel::OnManualSubmit)
+        QCOMPARE(model.data(model.index(0, 0)).toInt(), 3);
+
+    QCOMPARE(model.rowCount(), 3);
+
+    if (submitpolicy != QSqlTableModel::OnManualSubmit) {
+        // Rows updated in original positions after previous submits.
+        QCOMPARE(model.data(model.index(0, 0)).toInt(), 3);
+        QCOMPARE(model.data(model.index(1, 0)).toInt(), 2);
+        QCOMPARE(model.data(model.index(2, 0)).toInt(), 1);
+    } else {
+        // Manual submit is followed by requery.
+        QVERIFY_SQL(model, submitAll());
+        QCOMPARE(model.data(model.index(0, 0)).toInt(), 1);
+        QCOMPARE(model.data(model.index(1, 0)).toInt(), 2);
+        QCOMPARE(model.data(model.index(2, 0)).toInt(), 3);
+    }
+
+    QVERIFY_SQL(q, exec("DROP TABLE " + tbl));
+}
+
 void tst_QSqlTableModel::submitAll()
 {
     QFETCH(QString, dbName);