Updating QSqlRelationalDelegate/QSqlTableModel/QSqlTableModel when database changes?

[will49]

I have a Sqlite database that can get changed at any time from another part of my application. I'd like for the UI to be updated automatically as soon as the database is changed. I'm not quite sure how to do this.

So far I'm basing my test app on the "SQL Widget Mapper" demo app.



QSqlTableModel *model;
QDataWidgetMapper *mapper;

model = new QSqlTableModel(this,db);
model->setTable("Results");
model->setEditStrategy(QSqlTableModel::OnManualSubmit);

mapper = new QDataWidgetMapper(this);
mapper->setModel(model);
mapper->setItemDelegate(new QSqlRelationalDelegate(this));
mapper->addMapping(ui.lineEdit, model->fieldIndex("job_id"));
mapper->addMapping(ui.lineEdit_2, model->fieldIndex("date_time"));

connect(ui.pushButton_previous, SIGNAL(clicked()),
mapper, SLOT(toPrevious()));
connect(ui.pushButton_next, SIGNAL(clicked()),
mapper, SLOT(toNext()));


I was thinking I could emit a signal whenever the database is changed, but I'm not sure what needs to be informed of the change, and how to do it. Is it the QDataWidgetMapper that needs to know something has changed?

Thanks

[ChrisW67]

Push your updates through the same model that your view is attached to. This will work for QTableView, which should update affected cells. I don't think that QDataWidgetMapper will dynamically update the widgets though.

[will49]

That certainly works when I update via the model. However in my case the actual database updates are coming from different parts of the app (different threads actually), and it would complicate things to have to keep track of the QSqlTableModel. Is there another way to tell the QSqlTableModel to refresh from the database?

[ChrisW67]

Call reset() on the model (or select() on a QSqlTableModel) when a signal is received from an updating thread or periodically using a timer. You will lose any uncommitted edits, the current item, and any selections on views attached to the models.

[Al_]

If rows were inserted by tableModel.insertRow(), then reset() does not work for me to update the table (but it works if inserting by a QSqlQuery)

Example below:

rowCount is only correct on line 18, after re- setTable(...)
select() is sufficient if I uncomment line 2 and remove rows 3 - 8 (row count correct on line 14)


qDebug() << "original row count" << tableModel->rowCount();
//ok = sqlQuery.exec(QLatin1String("INSERT INTO Table1(Details) VALUES ('item added by query');"));
ok = tableModel->insertRow(0, QModelIndex());
Q_ASSERT(ok);
QSqlRecord record = tableModel->record(0); // retrieve inserted record that is empty
record.setValue(3, QLatin1String("item added by insertRow and setRecord"));
ok = tableModel->setRecord(0, record); // record is no longer empty*/
Q_ASSERT(ok);
ok = tableModel->submitAll();
Q_ASSERT(ok);
qDebug() << "tableModel not yet updated, row count still" << tableModel->rowCount();
ok = tableModel->select();
Q_ASSERT(ok);
qDebug() << "after tableModel->select(), but row count still" << tableModel->rowCount();
tableModel->setTable(QLatin1String("Table1"));
ok = tableModel->select();
Q_ASSERT(ok);
qDebug() << "row count only now increased" << tableModel->rowCount();


Is this behaviour expected?

[ChrisW67]

#include <QtGui>
#include <QtSql>
#include <QDebug>

static bool createTestData()
{
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName(":memory:");
bool ok = db.open();
Q_ASSERT(ok);

QSqlQuery query;
query.exec("create table Table1 (id int primary key, data varchar(100))");
query.exec("insert into Table1 values(1, 'row data')");
query.exec("insert into Table1 values(2, 'row data')");
query.exec("insert into Table1 values(3, 'row data')");
query.exec("insert into Table1 values(4, 'row data')");
query.exec("insert into Table1 values(5, 'row data')");

return true;
}

int main(int argc, char *argv[])
{
bool ok;
QApplication app(argc, argv);

if (!createTestData())
return 1;

QSqlDatabase db = QSqlDatabase::database();
QSqlTableModel tableModel;
tableModel.setTable("Table1");
tableModel.select();

qDebug() << "original row count" << tableModel.rowCount();
ok = tableModel.insertRow(0, QModelIndex());
Q_ASSERT(ok);
QSqlRecord record = tableModel.record(0); // retrieve inserted record that is empty
record.setValue(1, QLatin1String("item added by insertRow and setRecord"));
ok = tableModel.setRecord(0, record); // record is no longer empty*/
Q_ASSERT(ok);
ok = tableModel.submitAll();
Q_ASSERT(ok);
qDebug() << "tableModel not yet updated, row count still" << tableModel.rowCount();
ok = tableModel.select();
Q_ASSERT(ok);
qDebug() << "after tableModel.select(), but row count still" << tableModel.rowCount();
tableModel.setTable(QLatin1String("Table1"));
ok = tableModel.select();
Q_ASSERT(ok);
qDebug() << "row count only now increased" << tableModel.rowCount();

return app.exec();
}

Works for me.


original row count 5
tableModel not yet updated, row count still 6
after tableModel.select(), but row count still 6
row count only now increased 6


Does you table have a unique key? How many rows are in the table? Are you falling foul of the lazy fetch behaviour of the table model?

[Al_]

Thanks. First I tried your code on my machine (sure enough, your code runs correctly also on my machine). A little bit of modification, starting from your code, and I can reproduce the original error in your code.


#include <QtSql>
#include <QDebug>

static bool createTestData()
{
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName(":memory:");
bool ok = db.open();
Q_ASSERT(ok);

QSqlQuery query;
query.exec("create table Table1 (id int primary key, data varchar(100), abc int)");
query.exec("insert into Table1 values(1, 'row data', 1)");
query.exec("insert into Table1 values(2, 'row data', 1)");
query.exec("insert into Table1 values(3, 'row data', 1)");
query.exec("insert into Table1 values(4, 'row data', 2)");
query.exec("insert into Table1 values(5, 'row data', 2)");

query.exec(QLatin1String("CREATE TABLE Table2 (Number, String);"));
query.exec(QLatin1String("INSERT INTO Table2(Number, String) VALUES (1, 'One');"));
query.exec(QLatin1String("INSERT INTO Table2(Number, String) VALUES (2, 'Two');"));
query.exec(QLatin1String("INSERT INTO Table2(Number, String) VALUES (3, 'Three');"));

return true;
}

int main(int argc, char *argv[])
{
bool ok;
QCoreApplication app(argc, argv);

if (!createTestData())
return 1;

QSqlDatabase db = QSqlDatabase::database();
QSqlRelationalTableModel tableModel;
tableModel.setTable("Table1");
tableModel.setRelation(2, QSqlRelation(QLatin1String("Table2"), QLatin1String("Number"), QLatin1String("String")));
tableModel.select();

qDebug() << "original row count" << tableModel.rowCount();
ok = tableModel.insertRow(0, QModelIndex());
Q_ASSERT(ok);
QSqlRecord record = tableModel.record(0); // retrieve inserted record that is empty
record.setValue(1, QLatin1String("item added by insertRow and setRecord"));
ok = tableModel.setRecord(0, record); // record is no longer empty*/
Q_ASSERT(ok);
ok = tableModel.submitAll();
Q_ASSERT(ok);
qDebug() << "tableModel not yet updated, row count still" << tableModel.rowCount();
ok = tableModel.select();
Q_ASSERT(ok);
qDebug() << "after tableModel.select(), but row count still" << tableModel.rowCount();
tableModel.setTable(QLatin1String("Table1"));
ok = tableModel.select();
Q_ASSERT(ok);
qDebug() << "row count only now increased" << tableModel.rowCount();

return app.exec();
}
I switched to QSqlRelationalTableModel and added setRelation (to do this, I created a second table and added a column to your original table query). Now the error is reproduced. If I comment out line 38, then the program runs correctly.

Any idea what is wrong?

Al_

[ChrisW67]

At line 44 the row count is 6 (not 5) so Qt is keeping the new row up to this point. I initially suspected the issue was purely that you modify the row and do not provide a valid value for the foreign key column (i.e. a 1, 2, or 3). Even with this value set, I could not make it work using the QSqlRecord approach.

However, the behaviour is as expected with this code using the model's setData() method:


qDebug() << "immediately after insertRow()" << tableModel.rowCount();
tableModel.setData(tableModel.index(0, 1),
QLatin1String("item added by insertRow and setData"));
tableModel.setData(tableModel.index(0, 2), 3);
qDebug() << "immediately before submitAll()" << tableModel.rowCount();

This approach also fails if the foreign key column is not populated.

[Al_]

Great, many thanks. I tested your approach, it works also in my hands. I used a pointer to QAbstractTableModel, since this is what I have in my real application (and use qobject_cast< QSqlTableModel*> or <QSqlRelationalTableModel*> as needed). And everything works!

Conclusion: setRecord(...) has a bug or at least an undocumented behavior; setData(...) is a valid and straightforward alternative that works also when QSqlRelations are in use.

I appreciate the time you devoted to dissect this.

Al_