On 9 Jul 2014, at 2:03pm, - <mz2n6u7c.temp...@xs4all.nl> wrote: > 1) Is it possible to refer to the columns in a kind of shorthand (index > perhaps) ? > > 2) Is it possible to have the SQLite engine initialize and remember certain > WHERE and ORDER clauses (without creating another database please :-) ), so > they can be used again-and-again (for the duration of a connection). > > 3) Is it possible, for the above 'scrolling cursor' method, to refer to a > starting record other than by sending the exact data of such a record back > to the SQLite engine ?
You've just listed a lot of the concerns involved in the use of scroll-page-by-page. And there are no good answers to them. The convenience functions which would give you "Where am I currently in this index ?" don't exist. If you want to do it you have to roll your own. Nor is it possible to tell SQLite to preserve the temporary index it made up from your query terms (WHERE and ORDER) so you can reuse it. Sorry about that. However, the whole question is almost obsolete. Users now scroll up and down displays so quickly and frequently that grabbing just one screen worth of data from a database is pointless. Similarly, users will frequently start a query with a small window, then make the window larger (fullscreen ?) which means it shows more rows. So rather than the old-style page-by-page listing, with the programming which goes into scrolling, modern systems tend to use a different style which doesn't have some of the concerns you list. This involves storing and refetching different things as follows. For my example I will use the following example SELECT name,phonenumber FROM people WHERE phonenumber LIKE '01707%' ORDER BY name 1) When you need to open the window, collect which rows are returned. Execute SELECT rowid FROM people WHERE phonenumber LIKE '01707%' ORDER BY name and store the array of resulting rowids, even if there are thousands of them. At this point you don't care about column values at all. 2) When you need to display some rows, use your rowid array to figure out which records you need. Once you know which rows you want execute something like one of the following, depending on how your code works and what your user is trying to do. SELECT rowid,name,phonenumber FROM people WHERE rowid BETWEEN this AND that or SELECT rowid,name,phonenumber FROM people WHERE rowid IN (line1rid,line2rid,line3rid,...) At this point you care only about column values and you never need to use SQL to scroll around in a table, which means you don't care about preserving indexes or index points or any of the tricky stuff. You dealt with that all in step (1) and don't need it any more. Lastly, the whole of the above ignores systems where the user (or another user !) may insert or delete a row that was in your foundset in another window, while it's being shown. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
