[Interest] Working with Q*Models and SQLite3 limitations

[Mark Brand]

On 02/26/2014 05:14 PM, Jason H wrote:
> I'm trying to work with SQLite 3 and QSqlQueryModel/QSqlTableModel. 
> Normally I have not problem using these as data sets are small, and 
> I'm interesting in the rows in returned order.
>
> I recently tried to grab arbitrary data from the model sample model 
> index would be (1000,0) - (1100,C) (first C columns of row 1000 to tor 
> 11000) and ran into some snags.
> int C = colMax != -1 ? colMax : colMax= columnCount();
> int R = rowMax != -1 ? rowMax : rowMax= rowCount();
>
> Pseducode runs after setQuery(sql):
> for (int row = rowMin; row< rowMax; row++) {
> for (int col = colMin; row< colMax; col++) {
> Qstring d = model.data(row,col).toString();
> }
> }
>
> However BOTH rowCount() and columnCount() return 0.

The result rows are fetched incrementally as the application advances 
through them. You should not assume that QSqlQuery (or the model) knows 
the final row count. Some DBMSs systems and/or drivers might provide the 
row count under some conditions, but many do not. There are good reasons 
for this. See the documentation of QSqlQuery::size(), 
QSqlQueryModel::rowCount(), and QSqlQueryModel::fetchMore() for some 
ideas. Notice that QAbstractItemModel is expressly designed so that the 
row count does not have to be known.

After the query has been executed, the *column* count should be 
available however.

>
> The only thing I can think of is to come up with a COUNT() query, but 
> this requires me to write and maintain an additional query. The 
> alternative, which I grimaced at, is to run through the query to get 
> the count, then possibly iterate/reset backwards

Usually it's best to avoid having to know the number of rows ahead of 
time. After SELECT COUNT(), the state of the database could change 
before the next query unless both are in a transaction. Scrolling to the 
end of query might be okay if you know already that the number of rows 
won't be too great.

>  I have not yet even seen what happens when I request 
> model.data(1000,0); Insight on how SQLite/Qt will behave for a 
> not-yet-loaded row is appreciated.

Asking for an item in row 1000 will make QSqlQuery scroll to that row if 
it can. There's nothing at all wrong with asking for a row greater than 
QSqlQueryModel::rowCount() - 1.

>
> I'm looking for more elegant solutions than what I can come up with, 
> or a recommendation of approach.

Reconsider whether your truly must know the number of rows ahead of 
processing. Maybe you can process one row at a time  until reaching the 
end, or call data() for just the data you need.

Mark