Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

QSqlQueryModel and TableView for dynamic data



  • I am pretty new in Qt/QML.

    Trying to build an application with a UI in QML and accessing data from a Sqlite database.

    The QML view has a TableView to display information in a data grid wiht a model in C++.

    The model is a class inheriting from QSqlQueryModel with a query to the database: setQuery("SELECT * FROM Samples", GetDataBase())

    I am adding a new row in the database table every second in another thread.

    When I navigate to the view where the TableView is placed the grid is populated with the information in the table. All good. But then, everytime I add a new row I call setQuery again but the table is not updated. If I go back and forth to the view again it is populated with all the new rows.

    I thought the view was "automaticaly" notified whenever the model changed but I suppose I am missing any kind of notification to let the view know that there is new rows or something??

    Also...this is just a testing application but I forsee that in my real app the information to be displayed in the grid will change very fast and the table can have several rows of information (order handred thousand rows) so I wonder if QSqlQueryModel is the right model for this kind data.

    Thanks in advance.



  • @XDePedro Can you show a piece of your Model code ?



  • @XDePedro said in QSqlQueryModel and TableView for dynamic data:

    Also...this is just a testing application but I forsee that in my real app the information to be displayed in the grid will change very fast and the table can have several rows of information (order handred thousand rows) so I wonder if QSqlQueryModel is the right model for this kind data.

    I hope you're not intending to display 100,000 rows to your user in the grid??

    I don't know whether you intend to display all rows including those which were previously shown, as well as your new rows? Or new rows only? To save on database query bandwidth, with the kind of numbers you are talking about you really only want to be fetching extra, new rows, not all of them each time....



  • @KillerSmath

    Is just a testing app:

    In the model constructor (it subclass QSqlQueryModel) I create the db and set the query:

            QSqlDatabase *pDB = new QSqlDatabase();
            *pDB = QSqlDatabase::addDatabase("QSQLITE");
            pDB->setDatabaseName(":memory:");
            if(!pDB->open())
            {
                assert(false);
            }
    
            QSqlQuery query;
            query.prepare("CREATE TABLE samples(id PRIMARY KEY, sampleId TEXT);");
            if(!query.exec())
            {
                assert(false);
            }
            pFakeDB = std::unique_ptr<QSqlDatabase>(pDB);
    
    

    Then start the thread to update the database and connect a slot to the database change notification:

       moveToThread(&fakeDBWorkingThread);
        connect(&fakeDBWorkingThread, SIGNAL(started()), this, SLOT(GenerateFakeData()));
        connect(this, SIGNAL(finished()), &fakeDBWorkingThread, SLOT(quit()));
        connect(this, SIGNAL(dbChanged()), this, SLOT(ChangeDBQuery()));
        fakeDBWorkingThread.start();
    

    And set the query:

      QSqlQuery query(GetDataBase());
       query.exec("SELECT * FROM Samples");
       setQuery(query);
    

    In the slot that is called when a new db item is added:

        setQuery(query());
    

    But I tried several things...as I told you, the table is populated correctly when I enter the view...is jus the update mechanism not working.



  • @JonB
    I want to display all but not at the same time....I though the table has its own pagination mechanism and is not getting all the information from the db if the user does not scroll it.

    If not then this is not the right control/model.



  • @XDePedro
    Unfortunately, none of the QSql... classes do virtual pagination, which you have rightly said is what you need. This is for you to add if wanted! You can make the code simpler if it suits to only allow append and page-forward in the table view.... I think I may have seen some project somewhere which offers code for pagination, but you'll have to Google/stackoverflow....


  • Lifetime Qt Champion

    Hi,

    Just a side note, you're using QSqlDatabase wrongly. There's no need for that pointer.

    See the class documentation for how to handle it.



  • If the db has enough elements to get the scrollbar in the table, then just scrolling the table the new elements are populated....so I suppose I'm missing any kind of notification so that the TableView is updated...just can't figure out how.



  • @XDePedro
    Not sure what you mean. Scrolling a table view does not fetch new elements via a new SQL query on the database....



  • @JonB

    Is not the table view fetching for new elements. If I just resize it just to make it redraw...the new elements are also displayed.

    I am calling setQuery in the model everytime the database changes so I suppose the query is ok and the data is there....but just calling setQuery doesn't make the TableView to update.

    How can I force the table view to update from the model?



  • @XDePedro
    For the update, see posts like:

    However, I don't see how this addresses what I see as your issue, which is that you have "hundreds of thousands of rows" to display. You do not want to fetch all of them each time, you do not want to store them all in your model, nor in your view.... IMHO you only want to fetch some kind of "page" at a time of rows, or something along those lines.



  • I know maybe this not the right model but anyway I would like to make it work before looking for another solution.

    I notice that I'm getting this error message:

    QObject::connect: Cannot queue arguments of type 'QQmlChangeSet'
    (Make sure 'QQmlChangeSet' is registered using qRegisterMetaType().)

    can it be the root cause? Does naybody know why I'm getting this? Might it be related with the fact that is another thread who is calling the setQuery??





  • Executing query in Main Thread after DB Changed

     connect(populateWorker, &populateWorker::doneSignal, [this](){
       QSqlQuery query(*database);
       database->open();
    
       query->setQuery("SELECT * FROM Samples");
       query->exec();
       
       model->setQuery(query);
    
       database->close();
    });
    


  • @KillerSmath
    How is this different that I was doing?

    You added the slot in a lambda but is still executed in the working thread, as far as I know, Or I am missing anything.

    I tried and seems not to work.



  • I am not expert with QThread but my suggest is create a class (DataManager) that will be the bridge between QML and C++ Model.

    • You can create a QThread inside this class to move the worker object to this thread.

    • Run your GenerateFakeData function from this worker and connect the doneSignal to setQuery Slot in DataManager

    • Allow the acess to model by this class



  • Ok. Now is working. It was a problem with the worker thread. I stopped the code in the code that updated the query (setQuery) in my previous implementation and was executed in the working thread. With current implementation the setQuery is executed in the UI thread.

    In fact with current implementation the code is executed in one thread or the other just depending on how I made the signal/slot connection. So if I connect them this way:

            connect(&insertWorker, SIGNAL(dbChanged()), this, SLOT(DBUpdated()));
    
    
        void SampleListModel::DBUpdated()
        {
            QSqlQuery query;/
            query.exec("SELECT * FROM Samples");
            setQuery(query);
        }
    

    Everything works and the setQuery is executed in the UI thread.

    But if I do it this way:

            connect(    &insertWorker,
                        &DBWorker::dbChanged,
                        [this]()
                        {
                            QSqlQuery query(GetDataBase());
                            query.exec("SELECT * FROM Samples");
                            setQuery(query);
                        });
    

    Then the lambda code is executed in the worker thread and the real-time updates are not working.

    As I said I am pretty new in Qt so I wonder whats the difference and if someone can recommend a good article explainning how the thread model works in Qt.

    Thanks for all your help.



  • You welcome.
    I also had difficulty with threads when i was starting to coding in QT but as time goes on, you'll understand why threads are very limited because of their unpredictability.



  • @XDePedro
    I can't get my head around each of your two code approaches, but it's important to understand that all database objects/operations are constructed/performed in the same thread as each other, not in/from different threads. Is that the situation you are in now/does that explain anything not working?



  • @JonB
    Database insertions/write are done in the working thread.
    Database queries/reads are done in the UI thread.



  • @XDePedro
    So as far as I understand you must not do that, as per what I wrote above....



  • @JonB
    Seems to work, but I will not do it this way...teh database here is just a fake database in order to be able to work in the model while the real db is yet not implemented.

    Thanks.

    Still wondering which model approach will feet better in what I ultimately have to implement. A lists of data that can have thousand of rows and can be updated by another thread (not user interaction). Any suggestions.



  • @XDePedro
    For the "updated by another thread (not user interaction)", my understanding is that you must not do that directly. If you need to, use signals & slots. You may get away without now, but supposedly not in the long run, under whatever circumstances.

    Read up on this, e.g. https://stackoverflow.com/questions/20793689/correctly-using-qsqldatabase-in-multi-threaded-programs, and many others.

    For the "model approach", I still think you need to answer my earlier question: do you fetch a brand new set of rows and those are the only ones to be shown in the table view, or do you "incrementally" fetch some additional new rows, and want those to be appended to what you already have in the model & the view?



  • Any help about which model should I use? Any help on how implement it?


Log in to reply