GNOME Bugzilla – Bug 567891
SQLite does not allow iterating backwards in cursor mode
Last modified: 2011-10-30 19:20:56 UTC
The SQLite provider does not implement fetch_prev() and fetch_at() in its recordset. Therefore, accessing data in a GdaDataModel returned from SQLite is only allowed sequentially in forward direction, even if GDA_STATEMENT_MODEL_CURSOR is supplied to gda_connection_statement_execute(). I believe this is a valid use case, since it works for example for the postgres provider.
Created attachment 126531 [details] [review] Implement fetch_prev(), fetch_at() for GdaSqliteRecordset SQLite seems to only allow iterating forward via its sqlite3_step() function. In case fetch_at() needs to iterate backwards, this patch resets the prepared statement via sqlite3_reset(), and then skips the rows up to the requested one.
This won't work as the result is that the SELECT statement is being re-run, resulting in a possibly different results. The only way of having a backward cursor is to store already read rows in memory which is what we want to avoid.
Does it make sense to implement forward-iteration without backwards-iteration? I hope so. In Glom we generally iterate forward only.
Forward-iteration is already implemented for the SQLite provider. If Glom is iterating forward only, then I'm basically wondering why libgda's gda_data_select_iter_at_row() is called at some point, which attempts to move the iterator to an arbitrary row. I'll investigate.
Very possibly I'm just wrong about what Glom does. I can imagine that it might want to jump around (though can't we make it only jump forwards?) when the treeview is paged up and down by large steps. Thanks for investigating. It's not really related, but it would also simplify our code greatly if GdaDataModelArray could be iterated. Then I could use the same code for data and for find criteria, which use the same UI in Glom.
If this can't be done then Glom will need some way to handle both types of GdaDataModel. That shouldn't be too difficult - we can look at the code from before we started using the iter-based model.
There is one point in glom where gda_data_model_iter_move_to_row() is called, in DbTreeModelRow::fill_values_if_necessary(). I tried replacing this with a bunch of gda_data_model_iter_move_next() instead, but this did not work either. The problem behind this is that gda_data_model_create_iter() seems to return an iterator pointing to the same row as the iterator that was used before (if any), and there is no way to do backward-iteration. I'm not sure whether this is the correct behaviour or even whether it is possible to do otherwise with SQLite. If this is intended however, then there should be a remark in the documentation for gda_data_model_create_iter() which currently states that the returned iterator is always one before the first row. Also, if the current behaviour is the intended one, then it would be nice if there was API to detect whether gda_data_model_create_iter() will return a new iterator or one pointing to the same row as an old one, so that Glom can choose GDA_STATEMENT_MODEL_CURSOR in the former case and GDA_STATEMENT_MODEL_RANDOM_ACCESS otherwise.
For data models accessible only through an iterator, the gda_data_model_create_iter() returns the same object all the time (just call g_object_ref() before returning it). The doc. will be corrected to mention that. You can use a GdaDataAccessWrapper data model which wraps a data model and offers a random access to it in the most efficient way possible (it's most usefull if you wrap a data model accessible only through an iterator).
Doc. updated, see commit http://git.gnome.org/browse/libgda/commit/?id=d9eef9203fd86fb3bfc530d0b823e803fec8fdde Closing this bug.