Results 1 to 9 of 9

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

  1. #1
    Join Date
    Jul 2007
    Posts
    56
    Thanks
    6
    Thanked 2 Times in 2 Posts
    Qt products
    Qt4
    Platforms
    MacOS X Unix/X11 Windows

    Question Updating QSqlRelationalDelegate/QSqlTableModel/QSqlTableModel when database changes?

    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:
    1.  
    2. model = new QSqlTableModel(this,db);
    3. model->setTable("Results");
    4. model->setEditStrategy(QSqlTableModel::OnManualSubmit);
    5.  
    6. mapper = new QDataWidgetMapper(this);
    7. mapper->setModel(model);
    8. mapper->setItemDelegate(new QSqlRelationalDelegate(this));
    9. mapper->addMapping(ui.lineEdit, model->fieldIndex("job_id"));
    10. mapper->addMapping(ui.lineEdit_2, model->fieldIndex("date_time"));
    11.  
    12. connect(ui.pushButton_previous, SIGNAL(clicked()),
    13. mapper, SLOT(toPrevious()));
    14. connect(ui.pushButton_next, SIGNAL(clicked()),
    15. 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

  2. #2
    Join Date
    Mar 2009
    Location
    Brisbane, Australia
    Posts
    7,729
    Thanks
    13
    Thanked 1,610 Times in 1,537 Posts
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11 Windows
    Wiki edits
    17

    Default Re: Updating QSqlRelationalDelegate/QSqlTableModel/QSqlTableModel when database chang

    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.

  3. #3
    Join Date
    Jul 2007
    Posts
    56
    Thanks
    6
    Thanked 2 Times in 2 Posts
    Qt products
    Qt4
    Platforms
    MacOS X Unix/X11 Windows

    Default Re: Updating QSqlRelationalDelegate/QSqlTableModel/QSqlTableModel when database chang

    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?

  4. #4
    Join Date
    Mar 2009
    Location
    Brisbane, Australia
    Posts
    7,729
    Thanks
    13
    Thanked 1,610 Times in 1,537 Posts
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11 Windows
    Wiki edits
    17

    Default Re: Updating QSqlRelationalDelegate/QSqlTableModel/QSqlTableModel when database chang

    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.

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

    will49 (22nd March 2011)

  6. #5
    Join Date
    Dec 2009
    Posts
    47
    Thanks
    11
    Thanked 1 Time in 1 Post
    Qt products
    Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android

    Default Re: Updating QSqlRelationalDelegate/QSqlTableModel/QSqlTableModel when database chang

    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:
    1. qDebug() << "original row count" << tableModel->rowCount();
    2. //ok = sqlQuery.exec(QLatin1String("INSERT INTO Table1(Details) VALUES ('item added by query');"));
    3. ok = tableModel->insertRow(0, QModelIndex());
    4. Q_ASSERT(ok);
    5. QSqlRecord record = tableModel->record(0); // retrieve inserted record that is empty
    6. record.setValue(3, QLatin1String("item added by insertRow and setRecord"));
    7. ok = tableModel->setRecord(0, record); // record is no longer empty*/
    8. Q_ASSERT(ok);
    9. ok = tableModel->submitAll();
    10. Q_ASSERT(ok);
    11. qDebug() << "tableModel not yet updated, row count still" << tableModel->rowCount();
    12. ok = tableModel->select();
    13. Q_ASSERT(ok);
    14. qDebug() << "after tableModel->select(), but row count still" << tableModel->rowCount();
    15. tableModel->setTable(QLatin1String("Table1"));
    16. ok = tableModel->select();
    17. Q_ASSERT(ok);
    18. qDebug() << "row count only now increased" << tableModel->rowCount();
    To copy to clipboard, switch view to plain text mode 

    Is this behaviour expected?

  7. #6
    Join Date
    Mar 2009
    Location
    Brisbane, Australia
    Posts
    7,729
    Thanks
    13
    Thanked 1,610 Times in 1,537 Posts
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11 Windows
    Wiki edits
    17

    Default Re: Updating QSqlRelationalDelegate/QSqlTableModel/QSqlTableModel when database chang

    Qt Code:
    1. #include <QtGui>
    2. #include <QtSql>
    3. #include <QDebug>
    4.  
    5. static bool createTestData()
    6. {
    7. QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    8. db.setDatabaseName(":memory:");
    9. bool ok = db.open();
    10. Q_ASSERT(ok);
    11.  
    12. QSqlQuery query;
    13. query.exec("create table Table1 (id int primary key, data varchar(100))");
    14. query.exec("insert into Table1 values(1, 'row data')");
    15. query.exec("insert into Table1 values(2, 'row data')");
    16. query.exec("insert into Table1 values(3, 'row data')");
    17. query.exec("insert into Table1 values(4, 'row data')");
    18. query.exec("insert into Table1 values(5, 'row data')");
    19.  
    20. return true;
    21. }
    22.  
    23. int main(int argc, char *argv[])
    24. {
    25. bool ok;
    26. QApplication app(argc, argv);
    27.  
    28. if (!createTestData())
    29. return 1;
    30.  
    31. QSqlDatabase db = QSqlDatabase::database();
    32. QSqlTableModel tableModel;
    33. tableModel.setTable("Table1");
    34. tableModel.select();
    35.  
    36. qDebug() << "original row count" << tableModel.rowCount();
    37. ok = tableModel.insertRow(0, QModelIndex());
    38. Q_ASSERT(ok);
    39. QSqlRecord record = tableModel.record(0); // retrieve inserted record that is empty
    40. record.setValue(1, QLatin1String("item added by insertRow and setRecord"));
    41. ok = tableModel.setRecord(0, record); // record is no longer empty*/
    42. Q_ASSERT(ok);
    43. ok = tableModel.submitAll();
    44. Q_ASSERT(ok);
    45. qDebug() << "tableModel not yet updated, row count still" << tableModel.rowCount();
    46. ok = tableModel.select();
    47. Q_ASSERT(ok);
    48. qDebug() << "after tableModel.select(), but row count still" << tableModel.rowCount();
    49. tableModel.setTable(QLatin1String("Table1"));
    50. ok = tableModel.select();
    51. Q_ASSERT(ok);
    52. qDebug() << "row count only now increased" << tableModel.rowCount();
    53.  
    54. return app.exec();
    55. }
    To copy to clipboard, switch view to plain text mode 
    Works for me.
    Qt Code:
    1. original row count 5
    2. tableModel not yet updated, row count still 6
    3. after tableModel.select(), but row count still 6
    4. 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?

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

    Al_ (29th March 2011)

  9. #7
    Join Date
    Dec 2009
    Posts
    47
    Thanks
    11
    Thanked 1 Time in 1 Post
    Qt products
    Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android

    Default Re: Updating QSqlRelationalDelegate/QSqlTableModel/QSqlTableModel when database chang

    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:
    1. #include <QtSql>
    2. #include <QDebug>
    3.  
    4. static bool createTestData()
    5. {
    6. QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    7. db.setDatabaseName(":memory:");
    8. bool ok = db.open();
    9. Q_ASSERT(ok);
    10.  
    11. QSqlQuery query;
    12. query.exec("create table Table1 (id int primary key, data varchar(100), abc int)");
    13. query.exec("insert into Table1 values(1, 'row data', 1)");
    14. query.exec("insert into Table1 values(2, 'row data', 1)");
    15. query.exec("insert into Table1 values(3, 'row data', 1)");
    16. query.exec("insert into Table1 values(4, 'row data', 2)");
    17. query.exec("insert into Table1 values(5, 'row data', 2)");
    18.  
    19. query.exec(QLatin1String("CREATE TABLE Table2 (Number, String);"));
    20. query.exec(QLatin1String("INSERT INTO Table2(Number, String) VALUES (1, 'One');"));
    21. query.exec(QLatin1String("INSERT INTO Table2(Number, String) VALUES (2, 'Two');"));
    22. query.exec(QLatin1String("INSERT INTO Table2(Number, String) VALUES (3, 'Three');"));
    23.  
    24. return true;
    25. }
    26.  
    27. int main(int argc, char *argv[])
    28. {
    29. bool ok;
    30. QCoreApplication app(argc, argv);
    31.  
    32. if (!createTestData())
    33. return 1;
    34.  
    35. QSqlDatabase db = QSqlDatabase::database();
    36. tableModel.setTable("Table1");
    37. tableModel.setRelation(2, QSqlRelation(QLatin1String("Table2"), QLatin1String("Number"), QLatin1String("String")));
    38. tableModel.select();
    39.  
    40. qDebug() << "original row count" << tableModel.rowCount();
    41. ok = tableModel.insertRow(0, QModelIndex());
    42. Q_ASSERT(ok);
    43. QSqlRecord record = tableModel.record(0); // retrieve inserted record that is empty
    44. record.setValue(1, QLatin1String("item added by insertRow and setRecord"));
    45. ok = tableModel.setRecord(0, record); // record is no longer empty*/
    46. Q_ASSERT(ok);
    47. ok = tableModel.submitAll();
    48. Q_ASSERT(ok);
    49. qDebug() << "tableModel not yet updated, row count still" << tableModel.rowCount();
    50. ok = tableModel.select();
    51. Q_ASSERT(ok);
    52. qDebug() << "after tableModel.select(), but row count still" << tableModel.rowCount();
    53. tableModel.setTable(QLatin1String("Table1"));
    54. ok = tableModel.select();
    55. Q_ASSERT(ok);
    56. qDebug() << "row count only now increased" << tableModel.rowCount();
    57.  
    58. return app.exec();
    59. }
    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_

  10. #8
    Join Date
    Mar 2009
    Location
    Brisbane, Australia
    Posts
    7,729
    Thanks
    13
    Thanked 1,610 Times in 1,537 Posts
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11 Windows
    Wiki edits
    17

    Default Re: Updating QSqlRelationalDelegate/QSqlTableModel/QSqlTableModel when database chang

    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:
    1. qDebug() << "immediately after insertRow()" << tableModel.rowCount();
    2. tableModel.setData(tableModel.index(0, 1),
    3. QLatin1String("item added by insertRow and setData"));
    4. tableModel.setData(tableModel.index(0, 2), 3);
    5. 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.

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

    Al_ (30th March 2011)

  12. #9
    Join Date
    Dec 2009
    Posts
    47
    Thanks
    11
    Thanked 1 Time in 1 Post
    Qt products
    Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android

    Default [SOLVED] Updating QSqlTableModel

    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_

Similar Threads

  1. QSqlTableModel the database does not update
    By Zander87 in forum Qt Programming
    Replies: 4
    Last Post: 27th February 2011, 23:34
  2. Disconnecting from database after using QSqlTableModel
    By RobbieClarken in forum Qt Programming
    Replies: 6
    Last Post: 8th April 2009, 10:51
  3. Replies: 2
    Last Post: 23rd February 2008, 02:58
  4. How to manage QSqlTableModel database operations?
    By Abk in forum Qt Programming
    Replies: 1
    Last Post: 19th September 2007, 11:44
  5. QSqlTableModel Help pls
    By munna in forum Newbie
    Replies: 1
    Last Post: 7th March 2006, 20:40

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Digia, Qt and their respective logos are trademarks of Digia Plc in Finland and/or other countries worldwide.