On 21 Feb 2011, at 12:47am, BareFeetWare wrote: > How can I best scroll though the results of an arbitrary select query?
Suppose the results of the SELECT change between your original decision to do the scrolling and the time the user decides to scroll. Should what's shown on the display reflect the data as it originally was, or up-to-date data ? Or could there never be any such changes ? > 1. Is there any significant overhead on SQLite from my selecting from a view > representing the original arbitrary select? That is, will SQLite still use > any indexes etc correctly? Or do I need to dissect/parse the original select > statement, changing the where statement etc? A VIEW is a saved SELECT query -- the query, not the results, are saved. So I think you don't need to make the extra effort you describe. > 2. If the arbitrary select statement already contains an "order by" clause, > then I obviously need to use the order by column(s) specified there as the > keyColumn for the scrolling, rather than override the order by. Is there any > way to get the sort order of an arbitrary select statement? I don't know of any. > 3. This method requires that keyColumn is defined as unique (or primary key), > otherwise it can skip rows of data. Is there any way to allow for a > non-unique keyColumn? No, but instead of using just keyColumn you could use (keyColumn,rowid). This would ensure your key was always unique, and will work on arbitrary SQLite tables unless someone is intentionally messing with how SQLite works. > 4. If the arbitrary select statement does not specify an order by, how can I > least affect the output (ie not impose a sort order) but still facilitate > scrolling? For selecting from a table, the best I can think of is to use > rowid (or its alias), which seems to be the typical result order when no > order is specified. But when selecting from a view (which may contain joins), > by which column(s) can I explicitly sort (for the sake of scrolling) that > will best mimic the usual SQL output order (which I know is "undefined")? > > 5. I understand that "Rule Number 1" is to "not leave queries open". Correct. Don't allow a user to create and close a query just by choosing when they want to scroll through a list. > So what's the best way to minimize the overhead of repeatedly running the > same query but with a different where clause and limit (and order if > reversing). I'm thinking I would be best to actually keep the query (ie > prepared statement) open while live scrolling (eg flicking through rows on an > iPhone/iPad), not using a limit clause at all, but instead just keep getting > more rows as needed to fill the scrolling, until the user stops scrolling, > then finalize, close etc. When they begin scrolling again, fire up a new > prepare (with a new maxVisibleKeyValue) . To get the following or previous line to one which is already being shown, find the key for that row (which you should save in memory as you're displaying the line) and use SELECT <whatever> FROM <table> WHERE (keyColumn||rowid)>lastlineKey ORDER BY keyColumn,rowid LIMIT 1 to get the following line or SELECT <whatever> FROM <table> WHERE (keyColumn||rowid)<firstlineKey ORDER BY keyColumn,rowid LIMIT 1 Note: I just made that code up and have no actually tried it. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
