On Wednesday, 24 May, 2017 07:21 > > there is no system in existence that will do > I was working a lot with Valentina-DB and they have a cursor class: > > var cursor=database.SqlSelect( "SELECT... WHERE... ORDER..."); > > then you can just get any the ListView wants, forward and backwards, very > fast: > > cursor.Position = rownumber; > > I'm quiet new to SQLite and was surprised, that its so difficult to write > this > kind of cursor. > > > Another way is to Query to a temporary table with an automatic > incremented > > This is extremely fast, only the initial query will take some time. > yeah, this might work, but imagine how much time and memory this would > cost > for 10mio records...
This is exactly how things that "pretend" to have cursors work. Except they have the support "built-in" to either the client or the server. Basically, you do the following: pragma journal_mode=WAL; begin; drop table if exists temp.myPhonyCursor; create temporary table if not exists myPhonyCursor as SELECT table.RowID as tableRowID FROM TABLE WHERE <conditions on what to include in cursor> ORDER BY <how you want it sorted> ... your queries to retrieve rows go here -- proceed to drop/commit when you are done with the cursor ... drop table if exists temp.myPhonyCursor; commit; Now, whenever you want to retrieve some data, you can do something like: SELECT table.* FROM myPhonyCursor, table where table.RowID == myPhonyCursor.tableRowID and myPhonyCursor.RowID between @StartingRow and @EndingRow order by myPhonyCursor.RowID; -- for a forwards read and SELECT table.* FROM myPhonyCursor, table where table.RowID == myPhonyCursor.tableRowID and myPhonyCursor.RowID between @StartingRow and @EndingRow order by myPhonyCursor.RowID desc; -- to see the rows in reverse order. of course, for this to be effective you need to be either (a) the only user of the database or (b) have to wrap the whole thing in a transaction and be using WAL mode in order to achieve repeatable read isolation across multiple queries or you are liable to have result rows "disappear" or "appear out of order". if you want "page numbers", zero based, then you can do the following: SELECT table.* FROM myPhonyCursor, table where table.RowID == myPhonyCursor.tableRowID and myPhonyCursor.RowID > (@pageNumber * @PageSize) order by myPhonyCursor.RowID limit @PageSize; When you need to change the sort order or whatever you simply regenerate myPhonyCursor. If the temp tables are in memory and you have the appropriate indexes to process the ordered query, generating the myPhonyCursor table is quite fast, even for millions of rows. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
