QSqlQueryModel and TableView for dynamic data

[KillerSmath]

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

[JonB]

@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....

[XDePedro]

@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.

[XDePedro]

@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.

[JonB]

@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....

[SGaist]

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.

[XDePedro]

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.

[JonB]

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

[XDePedro]

@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?

[JonB]

@XDePedro
For the update, see posts like:

• http://www.qtcentre.org/threads/58700-How-to-update-QTableView-from-SQL-database-after-adding-data-to-database
• https://forum.qt.io/topic/1168/solved-the-best-way-to-programmatically-refresh-a-qsqlquerymodel-when-the-content-of-the-query-changes/13
• https://stackoverflow.com/questions/45359569/how-to-update-qtableview-on-qabstracttablemodel-change

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.

[XDePedro]

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??

[KillerSmath]

@XDePedro

Possible explation for your problem.

[KillerSmath]

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

[XDePedro]

@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.

[KillerSmath]

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

[XDePedro]

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.

[KillerSmath]

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.

[JonB]

@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?

[XDePedro]

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

[JonB]

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