DB changes not updating in views

[Jeffb]

Hi Guys

I've just built the following system for testing:

2 different QSqlTableModels
each with its own QTableView
both models are based on the same database table

If I update the database through the first view and model, the changes are not reflected in the second view and model.
Is there anyway that Qt provides to reflect database updates or
Do I have to write it myself.

Jeff

[wysota]

Why not use the same model for both views? The model will not automatically update itself when the database changes.

[Lesiok]

Some DB have notifications (ie. PostgreSQL). Use them.

[Jeffb]

@ Wysota
I'm actually writing a custom model that allows me to take any sql using any number of db tables with joins and update, delete or insert in any of the tables. Therefore if this model (1) uses table A and B and I have another model (2) which uses only table A, then if model (1) changes table A, I want model (2) to know about it and handle it e.g. by asking the user if they want to keep what they've got in the view or update the view.

@ Lesiok
I'll have a look into it. At present I'm using SQLite. But may also have the options for Postgres and MySql. Do you know if all of these use notifications?

[wysota]

@ Wysota
I'm actually writing a custom model that allows me to take any sql using any number of db tables with joins and update, delete or insert in any of the tables. Therefore if this model (1) uses table A and B and I have another model (2) which uses only table A, then if model (1) changes table A, I want model (2) to know about it and handle it e.g. by asking the user if they want to keep what they've got in the view or update the view.

If there is one source for all your models -- meaning that in the end all of them work on the exact single read of data from the database (just like QRelationalTableModel does with its relation models) it will be ok. But if you create each of the models separately, Qt won't help you in any way, especially that submitting changes to the database is usually deferred by Qt.

Do you know if all of these use notifications?

Bear in mind Qt doesn't use these notifications in any way, you'll have to interface with the native API for each specific backend to be able to subscribe for notifications.

[Jeffb]

How do I create one source for all my models?

[wysota]

It depends on how you wish to implement your model. Basically you must make sure all models will use the same data fetched once from the database. Honestly I doubt you'll be able to come up with an easy solution to this problem unless you cache half of the database inside your application... But then what's the point of having the database in the first place...

[Lesiok]

I'll have a look into it. At present I'm using SQLite. But may also have the options for Postgres and MySql. Do you know if all of these use notifications?

Notifications are implemented in drivers for PostgreSQL and Firebird (IBase).

[Jeffb]

@ Wysota

I thought that might be the answer.
A simple alternative I've come up with is to simply write a singleton messenger class that each model must register with (registration creates 2 connections - one from the model's signal to the messenger's signal and one from the messengers signal back to the models slot). The signals and slots have a model pointer and db table name as parameters. Then when a model updates a table it emits the signal with a pointer to itself and the table name as parameters. The messenger receives the signal and resends it to all models that have registered with it. Each model then receives it and decides whether they use the db table passed as a parameter - if they do then they reload themselves and their views. The model which sent the signal in the first place knows that it sent it because it just received a pointer to itself as a parameter in the slot.
A better solution may be for the messenger to maintain what models use what tables and when a signal is received for a change in a particular table, only send a signal to those models that use that table.

@Lesiok
I noticed that QSqlDriver implements db notifications but how do you access this from QSqlDatabase which does not?

[wysota]

If you reload the models then you loose selections and offsets in all the views using them.

[Jeffb]

Selections are not a problem. The model can query the view first and store them before reloading and if the cell is still valid just reselect.

But what do you mean by offsets?

[wysota]

Selections are not a problem. The model can query the view first and store them before reloading and if the cell is still valid just reselect.

And how would you synchronize the two result sets? In particular cases this is of course possible but not in general cases. And it's better to loose selection instead of selecting something that shouldn't be selected behind the user's back.

But what do you mean by offsets?

Scroll bar values.

The only way to resolve the situation properly is to have a single source of information (be it the database or your messenger or whatever) and update all the models simultaneously using proper insert/remove rows/columns calls and other signals. Resetting the model is the last resort that should be avoided in most situations.