Cached and editable QSqlQueryModel

Thombou

Hi,
I want to have a model shared by 2 views. One view would display some information with a custom query, the other one will display more information about the each row with a form to edit it. The information the information displayed gather information from other tables through join, count, max etc, but information that can be edited only belong to one table in the database.

I also would like to cache the changes.

My problem is that I don't know how to set up my model such that it can accept a non standard query, with cached changed. I am aware that using a QSqlTableModel allows to set a submit strategy but not the standard QSqlQueryModel. How to implement it?

I was also thinking of creating 2 models, one QSqlQueryModel read only and one QSqlQueryTableModel to display in the form, connected to the first model with signals/slots. The problem with this option is that the cached changes in the QSqlQueryTableModel will not be visible in the QSqlQueryModel before submitting them to the database.

Any idea?
Thanks a lot!

SGaist

Hi,

QSqlTableModel is built on top of QSqlQueryModel so you might want to check its implementation.

One other possibility could be to have a proxy on top of your model and do your caching there.

QDataWidgetMapper will also interest you.

Thombou

Thanks for you r reply SGaist.
I will investigate this but I think I will go to the widget mapper option, it seems to be the most efficient for me.

Thombou

Hi,
@SGaist Is it actually possible to cache changes using a proxy model? I don't see a submit policy property there...
Thanks

SGaist

Hi,

If you mean that an API exists for that, no, you will have to implement that yourself.

Thombou

This post is deleted!

Thombou

@SGaist said in Cached and editable QSqlQueryModel:

If you mean that an API exists for that, no, you will have to implement that yourself.

Could anybody help me with a starting point on how to do it?
I tried to read QT sources to see how it is implemented in the QSqlTableModel but I could not figure out.
I am new to Qt so I am not really used to read the sources, I would need some guidance...

SGaist

One implementation of the cache is to interact with a copy of the data you are modifying. On submit, you flush that cash to the target.

VRonin

I have this library that does almost everything you need to do.
One of the examples of the library is indeed an editable sqlquerymodel

All you have to do is subclass RoleMaskProxyModel and reimplement the submit() method to take the cached data and run an update query to send it to the relevant table and then call clearMaskedRoles(); to flush the cache.

This proxy model was a submission to the Qt codebase that got rejected as "out of scope"

P.S.
If you use MSVC 2017 or 2019 on windows you can download the pre-compiled binaries direcly from the CI: x86/x64 (Links might become outdated and will be removed 6 months after this post)

Thombou

@VRonin Thank you! But I am not quite sure to understand where is the caching there? I don't have problen to have a qsqlquerymodel editable (I know I have to change the setData and Data function accordingly to my request (I suppose I have to insert some UPDATE sql requests there to fit the way my data are stored).
What do you mean by take the cached data?

VRonin

What RoleMaskProxyModel does is basically store the data in a cache in the proxy model instead of writing it to the source model so RoleMaskProxyModel is the cache. All you have to do at that point is writing a method that puts that data back in the db