The table you are creating is called a keyset snapshot. That is how all relational databases databases which support scrollable cursors implement them (only navigable databases -- hierarchical or network or network extended for example) support navigation within the database. Relational databases are, well, relational.
The only difference is that SQLite is, well, Lite. It does not create the keyset for you by automagic, you have to do it yourself. It cannot take a parameter on the _prepare of a select statement that indicates to magically create the snapshot for you, just as it does not understand UPDATE <table> SET ... WHERE CURRENT OF CURSOR -- you have to retrieve the rowid youself and UPDATE <table> SET ... WHERE rowid=<rowid you retrieved> ... --- Theory is when you know everything but nothing works. Practice is when everything works but no one knows why. Sometimes theory and practice are combined: nothing works and no one knows why. >-----Original Message----- >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >boun...@sqlite.org] On Behalf Of Max Vasilyev >Sent: Thursday, 8 January, 2015 07:57 >To: General Discussion of SQLite Database >Subject: Re: [sqlite] Scrolling Cursor implementation best practices >(pagination and arbitrary sorting) > >Hi Clemens, > >2015-01-08 13:34 GMT+03:00 Clemens Ladisch <clem...@ladisch.de>: > >> > >> http://stackoverflow.com/questions/21082956/sqlite-scrolling-cursor- >how-to-scroll-correctly-with-duplicate-names >> > and yes, we can use title+rowid as lasttitle. But... it looks too >complex >> > to be 'best practice' pattern. >> >> Feel free to propose something simpler that is still correct. >> >That's why I think to rownum... Now I ended up with simulating index by a >table and use it's PK as rownum. > >CREATE TABLE BookTitleIdx (title_rownum INTEGER PRIMARY KEY >AUTOINCREMENT, >BookID INTEGER NOT NULL); >INSERT INTO BookTitleIdx SELECT NULL, rowid FROM Book ORDER BY Title > >Time: 0.369s Great! 126K records. > >SELECT Book.Id, Author.Name as Author, Book.Title, Genre.Name as Genre >FROM BookTitleIdx >INNER JOIN Book ON BookTitleIdx.BookID = Book.Id >LEFT JOIN Author ON Book.AuthorID = Author.ID >INNER JOIN Genre ON Book.GenreID = Genre.ID >WHERE BookTitleIdx.title_rownum > 120000 >ORDER BY BookTitleIdx.title_rownum >LIMIT 30 > >Time: 0.001s and not surprising! As EXPLAIN QUERY PLAN looks excellent to >me! > >0 0 0 SEARCH TABLE BookTitleIdx USING INTEGER PRIMARY KEY >(rowid>?) >0 1 1 SEARCH TABLE Book USING INTEGER PRIMARY KEY (rowid=?) >0 2 2 SEARCH TABLE Author USING INTEGER PRIMARY KEY (rowid=?) >0 3 3 SEARCH TABLE Genre USING INTEGER PRIMARY KEY (rowid=?) > >BTW, the query can be ...FROM Book INNER JOIN BookTitleIdx... no vice >versa >is required. >But inner join order is critical. This query >SELECT ... >FROM Book >LEFT JOIN Author ON Book.AuthorID = Author.ID >INNER JOIN Genre ON Book.GenreID = Genre.ID >INNER JOIN BookTitleIdx ON Book.Id = BookTitleIdx.BookID >... >runs 0.5s and has Book table scan, etc... Just moving last join up makes >the query plan as I want. > >So, for now I'm happy guys, thank you very much! > > >The latest SQLite supports the OR optimization for this query: >> >> EXPLAIN QUERY PLAN SELECT ...; >> 0|0|0|SCAN TABLE MainBooksView USING INDEX TitleIndex >> >> Run ANALYZE, and update your SQLite. >> >> Updated and got 0.060s (x2 faster)! > ANALYZE does not change anything. > >Thank you very much for EXPLAIN QUERY PLAN! It's much more readable than >just EXPLAIN which i used but did not understand :) > > >Cheers, >Max >_______________________________________________ >sqlite-users mailing list >sqlite-users@sqlite.org >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
