Hello Simon, > 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.
Thanks for the above. It means that I did my homework right. :-) > Nor is it possible to tell SQLite to ... Sorry about that. I already got that feeling, but had to make sure (novice and all that). And nothing to be sorry about (although it would have been usefull in this case), just something I have to learn to work with (or rather, without). > 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. Yes, that was also a concern of mine. But although I already had several possible approaches to it (like a bit of caching and buttons scrolling more than a single page, possibly related to the size of the database) I did not want to concern myself and this forum with all of that at the same time. One step at a time keeps things simple. > Similarly, users will frequently start a query with a small > window, then make the window larger (fullscreen ?) > which means it shows more rows. Already considered that, and found LVM_GETCOUNTPERPAGE to work quite well for it. > So rather than the old-style page-by-page listing, .... I was also thinking in that direction. Get all rowIDs first and use them. In that regard, thanks for the "rowid IN (line1rid,line2rid,line3rid,...)" hint, that takes care of one of my concerns of having to send a query for each-and-every record in a page. But it might cause another problem: the database could get/be so large that the ammount of memory needed to store all the rowIDs in could well exeede the ammount of memory available to the program. I could ofcourse use (or at that moment switch over to) a local file (database!) to store them in, but somehow that feels a bit odd. > At this point you care only about column values and you > never need to use SQL to scroll around in a table, It also restores the use of the listviews own slider to move about in the list (do away with the "page up", "page down" buttons). I must say I like that. > 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 I also thought of that, but wasn't prepared to think about the consequences (good or bad) before the preceeding problems where solved. My intended approach to it was to add a "reload" button/key (F5) for it. Thanks for the full (explanation, hints) reply. Remark: I've not seen any reference in your reply to my first question where I wondered if it would be possible to refer (in queries) to columns other than by their full names. Must I assume its not possible ? Regards, Rudy Wieser ----- Original Message ----- From: Simon Slavin <slav...@bigfraud.org> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Sent: Wednesday, July 09, 2014 4:07 PM Subject: Re: [sqlite] Questions from a novice - basic browsing of records ina listview. > > 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
