How to use setData() in model with a back-end database

Saviz

I have a Database class that operates in a separate thread and communicates with a MySQL database using signals and slots. This setup works well for general operations. However, I recently added a custom model, and now I’m facing a challenge with integrating it properly.

In my QML delegate, there's a Checkbox that represents a completed state. Whenever the checkbox is toggled, I want to send an update request to the database. Naturally, I considered reimplementing the setData() method of my model to handle this interaction.

The issue is that most examples of setData() assume immediate, synchronous changes to the model. But in my case, the database operation is asynchronous and might take some time to complete (Depending on network speed and other factors). It runs on a separate thread and emits a signal when the operation finishes.

This creates a problem: I cannot wait inside setData() for the operation to complete, because it's asynchronous. And I also can't respond to the success or failure of the database update later on, since by then I no longer have access to the QModelIndex that was passed to setData().

Please note that is not complete and is just a sample of what I had in mind:

// 1
bool MyModel::setData(const QModelIndex &index, const QVariant &value, int role)
{
    if (!index.isValid())
    {
        return false;
    }
    
    const int taskID = m_Data[index.row()].taskID;
    
    // Cannot do this! because this would block the thread:
    if(m_Database->updateItem(taskID, value))
    {
        // Other operations...
    }
}

// 2
// React to the return signal of database:
connect(m_Database, &Database::finished, this, [=]() {
    
    // Do operations...
    
    emit dataChanged(index, index, { IsCompleted }); // Don't have access to index !! What to do ???
});

How can I design this properly so that my model reflects the final updates and changes only after the database operation completes successfully ? Is there a recommended pattern in Qt for this kind of asynchronous setData() logic?

JonB

@Saviz
I do not have a perfect answer, as Qt database code really is not set up for this kind of behaviour, but some thoughts you might want to consider.

I see several ways you might handle the situation:

Change the stored value to the new one in setData(). Issue dataChanged() signal. If later the asynchronous call returns failure, call setData() again to reset to the original value, and emit dataChanged() a second time.
Do not change the stored value in setData(). Nonetheless probably have setData() return true, as I suspect the front end won't like it if that returns false? But not dataChanged(). If later the asynchronous call returns success, call setData() again to set to the new value, and emit dataChanged() a second time.
Put the data for an updated row/cell into some "unknown" state between the point where it is changed in the UI and the point where the asynchronous update completes. During this time return some "unknown" value for the data in that row/cell, such as an empty QVariant(). Don't know how well that would play with your UI.
Introduce another layer such as a proxy model. The proxy reflects the data as per the UI while the source model reflects the data as per the backend. For example, changing a value (like the checkbox) is always reflected immediately in the proxy but not in the source. After the asynchronous call returns the source model is updated, now reflect that to the UI model.

You may have to consider "nasty" cases: for example, what do you do if the checkbox/data has been updated but the asynchronous call has not yet completed and the user (or code) updates the same checkbox/data again while the first call is still "pending"?

I regard the "since by then I no longer have access to the QModelIndex that was passed to setData()" as an implementation detail you simply have to handle. You must do whatever you need so that when the asynchronous call returns you can correctly locate the data it changed, be that for a dataChanged() emit or updating/undoing a change in the model. What information does your backend asynchronous call return in its signal to tell you what it did? You might pass around the QModelIndex. (Though in this case I don't know for sure how long its validity lasts, although I have not used it Qt has a QPersistentModelIndex which you might to use.) Or, assuming the asynchronous call returns, say, the updated data row, use a primary key to search the data and re-find the updated row. If you say the async call cannot return any such, then either you must rely on/ensure only one update call is "pending" at any one time, so that you can store information about what it was doing and use that, or you are in trouble if several calls can be pending, some may fail and you cannot tell which is which.

Saviz

Excellent explanation. Thank you.

@JonB said in How to use setData() in model with a back-end database:

Change the stored value to the new one in setData(). Issue dataChanged() signal. If later the asynchronous call returns failure, call setData() again to reset to the original value, and emit dataChanged() a second time.

I think this might be the best idea out of all. I will try to go for this one.

@JonB said in How to use setData() in model with a back-end database:

I do not have a perfect answer, as Qt database code really is not set up for this kind of behaviour

One more question:

The main reason I introduced separate threads and asynchronous handling is because I assumed it would be beneficial. Database operations can be time-consuming due to factors like network latency, query complexity, and other unpredictable conditions. To avoid freezing the GUI and to potentially improve performance, I decided to offload these tasks to background threads.

However, based on your explanation, it seems that this approach might complicate things significantly (or even make them unworkable) in this situation. I find it hard to believe that Qt doesn’t account for such situations, but nonetheless, do you think I should reconsider this design? For cases involving model interaction, would it be better to switch to a BlockingQueuedConnection so that the call behaves like a regular method and waits for a response from the database? Or is there another approach I might have overlooked?

JonB

@Saviz
I hear you about not knowing how long a database call might take, and consequently wanting to move calls to a thread. I can only say that personally I have not found this to be a problem in practice. I am not sure what timeouts the database layer provides. I suspect Qt's own code does not do timeouts. I do not know whether the "professional" Qt-ers here move their database calls off to a thread.

But your moving it to a thread while still handling a UI which is supposed to be up to date and can cause actions adds this layer of complexity. As you have noted, Qt's setData() expects the data to change and the result to return success or failure synchronously.

You cannot expect to have you cake and eat it, though! :) On the one hand you don't want to risk blocking the UI with synchronous database operations while on the other hand you want the UI to be up to date with the state of the database. There is a period of time during your thread database call where we cannot know how synchronised they are if we do not know whether the update has succeeded or failed. What are you going to do if during that time the user initiates another UI action?

If you can go with synchronous things will be much easier. Maybe at least design it that way and see how it goes? If asynchronous I would give the first suggestion, "Change the stored value to the new one ...", as you said, it seems the simplest.

Saviz

Thank you. I will give it a try and only go for async if need be. I just wanted to make sure I am not missing out on some hidden method that I don't know of.