QSqlDatabase::commit - meaning of use

Jendker

Running through the example of Cached table http://doc.qt.io/qt-5/qtsql-cachedtable-example.html I found that it is advised to call QSqlDatabase::commit() after QSqlTableModel::submitAll() but I am actually getting what I need for my model with simple QSqlTableModel::submitAll() using the QSQLITE driver.

Should I understand this, that this is generally a good practice to call QSqlDatabase::commit(), but not needed for QSQLITE?

JonB

@Jendker
"Commit" is to do with transactions. I assume SQLite does support transactions(?), in which case it's still relevant. You won't tend to "see" anything happening with commits, whereas you do obviously see things happening with "submit"s.

Jendker

@JonB What would it be then what I will no be able to see by myself? Does submit not save the changes to database?

JonB

@Jendker
submit() does indeed save the changes to your database. That's likely all you see/care about initially, so you may think that's all there is to it. But to be "well-behaved", and cater for other eventualities you may not immediately perceive, you should (probably) use "transactions", which is what commit() is all about.

It's beyond the scope of this question/my answer to explain all about database transactions! You can Google for lots of information. Have a look at Qt doc's http://doc.qt.io/qt-5/sql-sqlstatements.html, there's a sub-topic there entitled Transactions with a brief overview from Qt's POV. You might try QSqlDriver::hasFeature(QSqlDriver::Transactions) to see whether your SQLite supports transactions (I don't know, I assume it does).

P.S.
In "plain English", if you only update one table at a time (i.e. you do not have "when I update this table I also need to update that table") and you do not allow more than one user/instance of your app to access the same database at the same time, you can get away without transactions. It's then up to you whether you regard putting them in as "good practice".

Jendker

@JonB Thank you for extensive answer! I will definitely use the transactions.

For submit we would have something like:

void TableEditor::submit()
{
    model->database().transaction();
    if (model->submitAll()) {
        model->database().commit();
    } else {
        model->database().rollback();
        QMessageBox::warning(this, tr("Cached Table"),
                             tr("The database reported an error: %1")
                             .arg(model->lastError().text()));
    }
}

What would be then the proper way of reverting the changes? I assume:

void TableEditor::revert()
{
    model->database().transaction();
    if (model->revertAll()) {
        model->database().commit();
    } else {
        model->database().rollback();
        QMessageBox::warning(this, tr("Cached Table"),
                             tr("The database reported an error: %1")
                             .arg(model->lastError().text()));
    }
}

or is it actually not needed to call anything after revertAll, because we don't actually have anything to commit / rollback, we just want to throw away any changes?

JonB

@Jendker
If I understand you right: when you commit(), and it succeeds, the database is in the same state as your model, i.e. it has whatever changes you have made. If it fails and you call rollback(), the database throws away your proposed changes and so does the Qt model. There is nothing further you should have to do. You should verify this by forcing a rollback(), and verifying that afterward your model no longer shows the changes you had pending.

P.S.
Sorry, I am assuming that model->database().rollback() will do http://doc.qt.io/qt-5/qsqltablemodel.html#revertAll for you. Don't know what model you're using. You do need to verify behaviour therefore.

Jendker

@JonB It seems, that rollout does not call revertAll as the data is not reverted.

Summing up: I will use the transactions for submitting the changes and will just call revertAll() when need to drop any changes. Thanks!

JonB

@Jendker
[I think you meant to write rollback :)] Then I assumed too much. It seems the code does not link the transaction stuff with the changes stuff, so you have to make separate calls yourself.