What design for one SQLite table and multiple models ? Bug or design issue ?

Dear all,

I am using QT4.4 to view and update a SQLite table storing few millions of records

I created a QSqlTableModel from this table. This model is displayed in a QTableView. Fine but I need several different editable views of the table simultaneously displayed.

I created some QSortFilterProxyModels to view parts of this model in other QTableViews : first issues arised here. It seems that the sortfilters do not always consider the records that are not yet fetched by the model but I dealt with that

Then, I need to procedurally update some SQL records. When I say procedurally, I mean that the data are modified with the code, not manually with the table view. For that, I need to check first if the record is in the table and there is my BIG problem:

Option 1: If I use the model to select the record with the expected ID (just to check whether it is present) then all the tableviews display the result of the select ... OK I can again select everything after that but this is a pain in the neck. In addition, it losts the tableview position and it is VERY slow (update of the views, rearragement of internal data ...) !!!

Option 2: If I create another model using the same SQL table just to look for a record and update it (as some sort of convenient database front end), everything seems to work fine but the updated record is never committed to the database even with submitall() calls. In addition, modifications made to this model are not always (but they are sometimes) applied to the other model.

My question is to know if there is an option 3 to have a simple and effective design enabling multiple different views of the same table, querying records with SQL requests, (I would like to avoid QSortFilterProxyModels, they are not convenient to use SQL like filtering) and procedural SQL queries at the same time.

Ideally I would like to create several QSQLTableModels and use their SQL request capabilities to filter the records. The only but critical issue is that they do not sync with database modifications and have problems to commit data when there are several refering to the same table... Is this normal because this is not how models should be used (I know a model should be unique, but QSQLTableModels have all the features I need) or is this a bug with SQLite ?

Any help will be greatly appreciated and I thank you for that in advance !

Cheers

Hi Tapioca

My application also works with a large DB and I did not even consider using QSqlTableModel. I inherit my models directly from QAbstractItemModel. The application allows opening multiple instances of various view types over the same instance of a model. And, at the same time, I use different types of models to represent the same data stored in the database. The models are optimized for performance and are specific to the tasks of my application.

The models are connected and notify each other on data modifications. For example, when a user modifies data through a view, setData() is called on the target model. At this point the model notifies related models on what is about to happen and delegates the work to the database. When response arrives from the database, the model notifies its related models about the result of the operation and updates its internal data structures. The attached views pick up the changes. The notified models also update their internal data structures and their views also pick up the changes.

This is a simplified description of what is really happening. But I cannot imagine doing things differently when an application has to deal with millions of database records and at the same time being fast and responsive.

Just my 2 cents.

Hi Yuriry,

thanks a lot for your answer which is another option : Reimplementing TableModels from AbstratItemModem that sync all together ...
but this represents quite some work I presume (including potential bugs ... and they are critical at this level) I know how to do that but I would have prefered a ready to use solution, even though it works up reasonably fast up to some hundreds thousand records ... I said few millions to push the limits :-)

Do you think that there is no way to have several QSqlTableModels sharing the same table and being synced all together and stay up to date even when I insert records with raw QSqlQueries ? I know this would represent some sort of two level model-view pattern but that would be really convenient :-)

Any other suggestion around ?

Cheers

In fact, on reflection, the only function I miss is a mean to update a record in the table for which I don't know its ModelIndex ... I would need to find the ModelIndex of a record that may not be already fetched.

I can reimplement some sort of findRecordByID() function in a subclass of SqlTableView that would fetch all the records and look for the record in the SqlTableView but this will be very slow and will load all the records to the cache.

My other needs will be adressed using SortFilterModels crossing my fingers that this will not be too slow.

Best