On 2016/03/04 10:35 AM, Darren Duncan wrote: > On 2016-03-03 11:27 AM, James K. Lowden wrote: >> >> You say, "record ... the condition ... to scroll [the table]". I'm >> sure I don't know what "condition" you mean. >> >> You appear to be doing something like: >> >> offset = 0 >> do >> sqlte3_prepare (limit, offset) >> do >> sqlite3_step >> until SQLITE_DONE >> offset += limit >> ... application stuff ... >> until last row processed >> >> The obvious, better alternative is >> >> sqlte3_prepare >> do >> do >> sqlite3_step >> until LIMIT >> ... application stuff ... >> until last row processed >> >> because in the second example you're using SQLite's cursor to "record >> the condition of the table". There's nothing for you to keep between >> "pages"; you just start from where you stopped. >> >> How exactly is the first way "easiest"? >> >> --jkl > > If these are pages displayed to the user, they may want to scroll > backwards at some point; I don't expect that sqlite3_step is > bidirectional, but if it is that's a pleasant surprise; the approaches > using SQL can go backwards as easily as forwards, but really it > depends on the use case. -- Darren Duncan
When a backward scroll is needed, you can simply re-execute the query and start from the (n-scrollsize)th position in the cursor, which will cost no more I/O than an OFFSET type query, but have the advantage of being able to scroll forward (which is the most often used direction) at near zero cost. This only works when you control the cursor, of course. Main disadvantage of this type of mechanism (to my mind) is that, as far as I know, the scrolling cursor locks up the DB in a transaction so that updating must suspend at the whim of the user's reading leisure - possibly mitigated by WAL mode and non-updating DBs. (I have not tested any of this, it's just my feeling and I could be wrong). Cheers, Ryan
