Hi Keith, 2015-01-09 2:02 GMT+03:00 Keith Medcalf <kmedc...@dessus.com>:
> > 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. > Thank you, good to know. So, it seems I've re-invented 'keyset snapshot' pattern or technique? It's great idea to document different pagination approaches as I think it is a very popular qustion. And this page http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor is out of date and very hard to find. I did not know term 'ScrollingCursor'. I propose to add new article at the main doc list (Categorical Index Of SQLite Documents). It can be called like 'Pagination in SQLite' or 'Best way to paginate result set in SQLite'. Also where OFFSET is described https://www.sqlite.org/lang_select.html#limitoffset I'd like to add a note describing that it iterates all the records and link to Pagination article. First, it should warn against using OFFSET for pagination. *Rule Number 1:* Do not try to implement a pagination using LIMIT and OFFSET. Then it should describe WHERE approach from old article, It's great, but it does not support random page accees. It can be suitable for most cases. And not unique column is not an issue: SELECT * FROM tracks WHERE (*title = :lasttitle AND rowid > :lastrowid* OR title > :lasttitle) AND (singer='Madonna' OR singer='Rick Wakeman') ORDER BY title DESC LIMIT 5; And next, describe keyset pagination and keyset snapshot. As I got it involes rownum and allows random accees. But I think it's not suitable for continues updatable DB. But for me it's suitable as I have no updates in background. BTW, cool material http://use-the-index-luke.com/no-offset Also I've read your previous answer here: http://sqlite.1065341.n5.nabble.com/I-m-trying-to-figure-out-how-to-td78018.html The word 'cursor' looks like as awful as 'goto'. Ok, let's don't say cursor, let's talk about _pagination_! Nobody wants to fetch million records, so I think, pagination is 'must have' feature of all clients. First solution for pagination every beginner comes to is OFFSET. It's great that SQLite has OFFSET, I remember from MS SQL that we have to use some magic CTE (common table expression) like this. SELECT *FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, * FROM Orders WHERE OrderDate >= '1980-01-01' ) AS RowConstrainedResultWHERE RowNum >= 1 AND RowNum < 20ORDER BY RowNum But it turns out that OFFSET work awful in SQLite and I guess in all DBMS :) I consider OFFSET is a driver 'kludge' using your words. I don't know when is it suitable. For small database? > 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's good, I don't complain. 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> ... > > Sorry, I did not understand that. When I insert or delete something, I'm going to drop my snapshot and recreate it again when I need the sorting. I think that rowid does not changes and updatin snapshot is quite difficult. What UPDATE do you propose? Thanks, Max _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
