When or how to use fetchMore () in QSqlTableModel with a SQLite database for rowCount () to work?

Question

My DataTable class is derived from QAbstractTableModel. It uses the QSqlTableModel object internally to retrieve data from the db table. It represents an entry for each row in db (it does more, but the number of records is always equal to the number of rows in the db table).

With MySql, my implementation of DataTable :: rowCount () just calls rowCount () in the QSqlTableModel, which works well.

Now with SQLite, the Qt SQLite driver returns 256 rows if there are more than 256 rows in the db table, so my DataTable class also returns 256 - which is wrong. the documentation tells me to call while (sql_model->canFetchMore()) sql_model->fetchMore(); . Calling fetchMore () immediately after creating the internal QSqlTableModel actually calls the next call to rowCount () to return the correct value. But as soon as something has changed in the database (my class will call insertRow () or setData () in QSqlTableModel), the next call to QSqlTableModel :: rowCount () will again return 256.

The database is changed only by my class, which in turn uses this particular QSqlTableModel object (or a view that uses my DataTable model as a model, can update something). Thus, there is no other process that could insert rows into the database.

So, when does my DataTable class call fetchMore () for rowCount () to always return the actual row counter?
I think my class should connect some of the signals emitted by QSqlTableModel to a slot that will call fetchMore (), although I'm not sure if this is the correct / reliable way to do this?

---

Update

Here is the code to demonstrate the main problem.

 QSqlTableModel *model = new QSqlTableModel(0, database); //QSqlDatabase model->setTable("tablename"); qDebug() << "0 row count" << model->rowCount(); //0 row count 0 model->setEditStrategy(QSqlTableModel::OnManualSubmit); model->select(); qDebug() << "1 row count" << model->rowCount(); //1 row count 256 while (model->canFetchMore()) model->fetchMore(); qDebug() << "2 row count" << model->rowCount(); //2 row count 1520 //... other methods ... model->setData(model->index(0, 0), "TEST"); model->submitAll(); qDebug() << "3 row count" << model->rowCount(); //3 row count 256 while (model->canFetchMore()) model->fetchMore(); qDebug() << "4 row count" << model->rowCount(); //4 row count 1520 

After loading the sql model, rowCount () returns 256 (1), so you need to call the fetchMore () function. rowCount () then returns the actual number of rows.
Later the data changes, after which rowCount () returns 256 (3) again.

So it seems that fetchMore () needs to be called after every write operation in the sql model. But instead of putting this while / canFetchMore () / fetchMore () loop at the end of every method that modifies the model, I wonder if it is enough to connect beforeInsert (QSqlRecord &), beforeUpdate (int, QSqlRecord &) and beforeDelete (int) to the slot, which then calls fetchAll ()? Would it be reliable and appropriate?

Correction : not earlier than * signals (too early), but probably layoutChanged (), dataChanged (), rowsInserted () and rowsRemoved ().

---

Update 2 :

A note on SQL . I know that I could send a separate SELECT COUNT SQL query to the database theoretically, but this does not answer the question. While I can avoid SQL, I will not write SQL. In my opinion, sending such an SQL query does not match the purpose of the QAbstractTableModel object-oriented class. Plus rowCount () is const (shouldn't send requests) and should be fast. Anyway, this will not fix rowCount ().

I ended up plugging in a slot that calls fetchMore () for the corresponding signals (see above) And , claiming that everything was selected in rowCount ():
assert(!sql_model->canFetchMore())

This is due to the fact that rowCount () cannot report the correct number of rows, counting as a failure state, therefore, an assertion. In other words, I would rather have my application crash than using the wrong number of rows.

Simply connecting to the dataChanged () signal (as indicated in the first answer : I would probably try to use dataChanged signal. ) I would probably try to use dataChanged signal. not enough. I connected it to dataChanged(const QModelIndex&, const QModelIndex&) , rowsInserted(const QModelIndex&, int, int) , rowsRemoved(const QModelIndex&, int, int) and layoutChanged() .

This seems to work, the statement hasn't worked yet.

If someone can specifically confirm this (or explain why it does not always work), I would appreciate a response.

Answer 1

The current answer does not fully answer the question (mentions the signal dataChanged (), but not other signals), so I am writing my own answer.

It has been a while, and I believe that I have considered all cases: I have finished connecting the slot that calls fetchMore () for the corresponding signals And claiming that everything was extracted in my DataTable :: rowCount () method: assert (! Sql_model -> canFetchMore ())

(Of course, rowCount () is a const method, so I couldn't get it if nothing else was fetched, but it still would not be getter work, the statement is fine, because canFetchMore () is also const.)

Signals: dataChanged (const QModelIndex &, const QModelIndex &), rowsInserted (const QModelIndex &, int, int), rowsRemoved (const QModelIndex &, int, int) and layoutChanged ()

I use this statement to make sure my model gets the correct line counter, otherwise the application will crash (what happens if not all of the mentioned signals are connected, for example layoutChanged ()). This is important in my case, since incorrect row counting can lead to data loss in my case.

So far, the statement has not failed, so I guess that solves it.

Answer 2

In my experience, the Qt SQLite driver inserts rows into the model in 256 line increments. At the end of the data selection, QSqlTableModel::rowCount() will return the correct value, there is no need to call fetchMore (). It just depends on where and when you call rowCount ().

For example, if a user performs some calculations in rowsInserted () , his slot method will be called several times, and each time the index of the last row will be increased to 256.

So, you need to use rowCount () at the end of the data extraction (in some overridden QSqlTableModel methods, the selection is often incomplete) or not rely on the value of the counter of intermediate lines and repeat the calculations every time it changes.

This is a general answer because you have not published any code where we can see what you are trying to do, but you can see what I am pointing to.

EDIT:

After calling QSqlTableModel::submitAll() model is re-populated, which is why fetchMore () is required to repeat it. Where to call fetchMore () depends on the use case. It can be called after submitAll () or in any slot, there is no general answer here. I will probably try to use the dataChanged signal. However, the goal of data collection should always be primary in order to display it in a view, and viewing does this in most cases.

In one of my applications, I relied on a table view to retrieve data for me. After setting the model view to the table, I either call QTableView::scrollToBottom() , which retrieves the data for me (the user should see the last data below in any case, in my case, the number of rows has the correct value after scrolling) or do the calculations in rowInserted () slot when the user scrolls the table, and the data is automatically selected again in increments of 256 lines.

And it is very important to know if the application should show somewhere the number of rows in the table or, for example, the total value of one column, it is often much more efficient to use additional QSqlQuery to get additional information (for example: select count (*) from xyz), then to read data from a large table. Table models are designed to provide data in the form of a table .

Answer 3

The dilemma you encountered was similar to the one I encountered recently. I wrote a QT gui program that did the following:

I. Connect and query the Oracle II database. Show query result in QTableView III. export the QTableView result to a CSV file intravenously; import the CSV file into the loacl sqlite3 v database. Connect to the local sqlite3 database and query its VI. execute the query and show the result in another QTableview

During stage ii. (i.e. export the result to .csv), I noticed that although 543 entries were generated in the QTableView, only 256 odd ones were exported to the CSV file.

I used

 int rows=model->rowCount(); int columns=model->columnCount(); for (int i = 0; i < rows; i++) { for (int j = 0; j < columns; j++) { textData += model->data(model->index(i,j)).toString(); textData += ", "; // for .csv file format } textData += "\n"; // (optional: for new line segmentation) } QFile csvfile("/home/aj/ora_exported.csv"); if(csvfile.open(QIODevice::WriteOnly|QIODevice::Truncate)) { QTextStream out(&csvfile); out<<textData; } csvfile.close(); 

as it turned out, the model read model-> rowcount before all the results were obtained.

So, as suggested by the SO community, I used ---

 while (model->canFetchMore()) model->fetchMore(); int rows=model->rowCount(); int columns=model->columnCount(); for (int i = 0; i < rows; i++) { for (int j = 0; j < columns; j++) { textData += model->data(model->index(i,j)).toString(); textData += ", "; // for .csv file format } textData += "\n"; // (optional: for new line segmentation) } 

and all entries were completed (all 543) in the CSV file.

You can refer to my question in here .