何时或如何在 QSqlTableModel 上使用 fetchMore() 和 SQLite 数据库让 rowCount() 工作? [英] When or how to use fetchMore() on QSqlTableModel with a SQLite database for rowCount() to work?

问题描述

我的类 DataTable 派生自 QAbstractTableModel.它在内部使用 QSqlTableModel 对象从数据库表中获取数据.它代表 db 中每一行的一条记录(它做的更多，但记录计数始终是 db 表中的行数).

My class DataTable is derived from QAbstractTableModel. It uses a QSqlTableModel object internally to fetch data from a db table. It represents a record for every row in the db (it does more but the record count is always the number of rows in the db table).

使用 MySql，我的 DataTable::rowCount() 实现只是在 QSqlTableModel 上调用 rowCount()，效果很好.

With MySql, my DataTable::rowCount() implementation just calls rowCount() on the QSqlTableModel, which works nicely.

现在使用 SQLite，如果 db 表中的行数超过 256，Qt 的 SQLite 驱动程序将返回 256 的行数，因此我的 DataTable 类也返回 256 - 这是错误的.文档 告诉我调用 while (sql_model->canFetchMore()) sql_model->fetchMore();.在创建内部 QSqlTableModel 后立即调用 fetchMore() 实际上会导致以下 rowCount() 调用返回正确的值.但是一旦数据库中的某些内容发生更改(我的类将在 QSqlTableModel 上调用 insertRow() 或 setData())，下一个 QSqlTableModel::rowCount() 调用将再次返回 256.

Now with SQLite, Qt's SQLite driver returns a row count of 256 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() right after the internal QSqlTableModel is created actually causes the following rowCount() call to return the correct value. But as soon as something is changed in the database (my class would call insertRow() or setData() on the QSqlTableModel), the next QSqlTableModel::rowCount() call will again return 256.

数据库仅由我的类修改，它反过来使用特定的 QSqlTableModel 对象(或使用我的 DataTable 作为模型的视图，可以更新某些内容).所以没有其他进程可以向数据库中插入行.

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

那么我的 DataTable 类什么时候应该为 rowCount() 调用 fetchMore() 以始终返回实际的行数?
我认为我的类应该将 QSqlTableModel 发出的一些信号连接到一个会调用 fetchMore() 的插槽，尽管 我不确定这是否是正确/可靠的方法?

更新:

这里有一些代码来演示基本问题.

Here's some code to demonstrate the basic issue.

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 

加载sql模型后，rowCount()返回256(1)，所以必须调用fetchMore().rowCount() 然后返回实际的行数.
后来数据发生变化，之后rowCount()再次返回256(3).

After loading the sql model, rowCount() returns 256 (1), so fetchMore() has to be called. rowCount() then returns the actual row count.
Later, data is changed, after which rowCount() again returns 256 (3).

所以似乎每次对 sql 模型进行写操作后都必须调用 fetchMore().但不是将这个 while/canFetchMore()/fetchMore() 循环放在修改模型的每个方法的末尾，我想知道是否足以连接 beforeInsert(QSqlRecord&), beforeUpdate(int, QSqlRecord&) 和 beforeDelete(int) 信号到一个插槽，然后调用 fetchAll()?这是否可靠且合适?

So it seems like fetchMore() has to be called after every write operation on the sql model. But rather than putting this while/canFetchMore()/fetchMore() loop at the end of every single method that modifies the model, I'm wondering if it would suffice to connect the beforeInsert(QSqlRecord&), beforeUpdate(int, QSqlRecord&) and beforeDelete(int) signals to a slot that would then call fetchAll()? Would this be reliable and appropriate?

更正:不是 before* 信号(太早)，但可能是 layoutChanged()、dataChanged()、rowsInserted() 和 rowsRemoved().

Correction: Not before* signals (too early), but probably layoutChanged(), dataChanged(), rowsInserted() and rowsRemoved().

更新 2:

关于 SQL 的注意事项:我知道理论上我可以向数据库发送一个单独的 SELECT COUNT SQL 查询，但这并不能回答问题.只要能避免SQL，我就不会写SQL.在我看来，发送这样的 SQL 查询违背了面向对象的 QAbstractTableModel 类的目的.加上 rowCount() 是 const (不应该发送查询)并且应该很快.无论如何，这不会修复 rowCount().

Note regarding SQL: I know that I could send a separate SELECT COUNT SQL query to the database in theory, but this doesn't answer the question. As long as I can avoid SQL, I won't write SQL. In my mind, sending such an SQL query defies the purpose of an object-oriented QAbstractTableModel class. Plus rowCount() is const (should not send queries) and should be fast. Anyway, this won't fix rowCount().

我最终将调用 fetchMore() 的插槽连接到相关信号(见上文)AND 断言所有内容都已在 rowCount() 中获取:
assert(!sql_model->canFetchMore())

I ended up connecting a slot that calls fetchMore() to the relevant signals (see above) AND asserting that everything has been fetched in rowCount():
assert(!sql_model->canFetchMore())

这是因为 rowCount() 无法向我报告正确的行计数作为失败状态，因此断言.换句话说，我宁愿我的应用程序崩溃而不是使用不正确的行数.

This is because rowCount() not being able to report the correct row count counts as failure state to me, hence the assertion. In other words, I'd rather want my application to crash than use an incorrect row count.

只需将它连接到 dataChanged() 信号(如第一个答案中所建议的:我可能会尝试使用 dataChanged 信号.) 是不够的.我已经将它连接到 dataChanged(const QModelIndex&, const QModelIndex&), rowsInserted(const QModelIndex&, int, int), rowsRemoved(const QModelIndex&,int, int) 和 layoutChanged().

Just connecting it to the dataChanged() signal (as suggested in the first answer: I would probably try to use dataChanged signal.) isn't sufficient. I have connected it to dataChanged(const QModelIndex&, const QModelIndex&), rowsInserted(const QModelIndex&, int, int), rowsRemoved(const QModelIndex&, int, int) and layoutChanged().

似乎有效，断言还没有失败.

Seems to work, the assertion has not failed yet.

如果有人可以具体确认这一点(或解释为什么它并不总是有效)，我将不胜感激.

If someone could specifically confirm this (or explain why it won't always work), I'd appreciate an answer.

推荐答案

当前的答案并没有完全回答问题(提到 dataChanged() 信号但没有提到其他信号)，所以我正在写我自己的答案.

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

已经有一段时间了，我相信我已经涵盖了所有情况:我最终将调用 fetchMore() 的插槽连接到相关信号，并断言已在我的 DataTable::rowCount() 方法中获取了所有内容:断言(!sql_model->canFetchMore())

It's been a while and I believe I have covered all cases: I ended up connecting a slot that calls fetchMore() to the relevant signals AND asserting that everything has been fetched in my DataTable::rowCount() method: assert(!sql_model->canFetchMore())

(当然 rowCount() 是一个 const 方法，所以如果还没有获取任何东西，我就无法获取，但这无论如何都不是 getter 的工作；断言很好，因为 canFetchMore() 也是 const.)

(Of course rowCount() is a const method, so I couldn't fetch if nothing has been fetched yet, but that wouldn't be the job a getter anyway; the assertion is fine because canFetchMore() is also const.)

信号:dataChanged(const QModelIndex&, const QModelIndex&)、rowsInserted(const QModelIndex&, int, int)、rowsRemoved(const QModelIndex&, int, int) 和 layoutChanged()

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

我使用断言来确保我的模型接收到正确的行数，否则应用程序将崩溃(如果不是所有提到的信号都已连接，例如layoutChanged()，就会发生这种情况).这对我来说很重要，因为在我的情况下，不正确的行数可能会导致数据丢失.

I'm using the assertion to make sure my model receives the correct row count or else the application would crash (which happens if not all of the mentioned signals are connected, for example layoutChanged()). This is important in my case, as an incorrect row count might lead to data loss in my case.

到目前为止，断言还没有失败，所以我假设这可以解决它.

So far, the assertion hasn't failed, so I'm assuming this solves it.

这篇关于何时或如何在 QSqlTableModel 上使用 fetchMore() 和 SQLite 数据库让 rowCount() 工作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！