Updating QSqlRelationalDelegate/QSqlTableModel/QSqlTableModel when database changes?

**will49** · 21st March 2011, 21:43

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.

Qt Code:

Switch view

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()));

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()));

To copy to clipboard, switch view to plain text mode

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** · 21st March 2011, 22:38

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** · 21st March 2011, 22:58

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** · 22nd March 2011, 00:47

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_** · 28th March 2011, 19:26

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)

Qt Code:

Switch view

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();

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();

To copy to clipboard, switch view to plain text mode

Is this behaviour expected?

**ChrisW67** · 28th March 2011, 23:30

Qt Code:

Switch view

#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();
}

#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();
}

To copy to clipboard, switch view to plain text mode

Works for me.

Qt Code:

Switch view

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

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

To copy to clipboard, switch view to plain text mode

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_** · 29th March 2011, 19:48

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.

Qt Code:

Switch view

#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();
}

#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();
}

To copy to clipboard, switch view to plain text mode

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** · 29th March 2011, 23:21

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:

Qt Code:

Switch view

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();

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();

To copy to clipboard, switch view to plain text mode

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

**Al_** · 30th March 2011, 20:06

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_

Thread: Updating QSqlRelationalDelegate/QSqlTableModel/QSqlTableModel when database changes?

Thread Tools

Search Thread

Display

Updating QSqlRelationalDelegate/QSqlTableModel/QSqlTableModel when database changes?

Re: Updating QSqlRelationalDelegate/QSqlTableModel/QSqlTableModel when database chang

Re: Updating QSqlRelationalDelegate/QSqlTableModel/QSqlTableModel when database chang

Re: Updating QSqlRelationalDelegate/QSqlTableModel/QSqlTableModel when database chang

The following user says thank you to ChrisW67 for this useful post:

Re: Updating QSqlRelationalDelegate/QSqlTableModel/QSqlTableModel when database chang

Re: Updating QSqlRelationalDelegate/QSqlTableModel/QSqlTableModel when database chang

The following user says thank you to ChrisW67 for this useful post:

Re: Updating QSqlRelationalDelegate/QSqlTableModel/QSqlTableModel when database chang

Re: Updating QSqlRelationalDelegate/QSqlTableModel/QSqlTableModel when database chang

The following user says thank you to ChrisW67 for this useful post:

[SOLVED] Updating QSqlTableModel

Similar Threads

QSqlTableModel the database does not update

Disconnecting from database after using QSqlTableModel

QSqlTableModel and checkboxes with SQLite database

How to manage QSqlTableModel database operations?

QSqlTableModel Help pls

Bookmarks

Bookmarks

Posting Permissions