On 21/02/2011, at 12:41 PM, Simon Slavin 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 ?
Thanks for the reply Simon. Good question. In most or all cases for now, the select results will not change mid scroll. The user interface will be clearly either scrolling or editing. In the longer term I will probably allow editing mid scroll, but that's fine, since I can flag within my app that results need to be re-selected. >> 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. Yes, I understand that a view doesn't save any results. I'm unclear, however, as to how smart is SQLite's query optimizer to, for instance, realise that when it's selecting from a view, it can use the indexes of source columns for the where filter. >> 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. Hmm, OK. I guess I'll need to impose a sort order on otherwise unordered query results. For a table, I'll just sort by rowid. For a view, I'll probably just look for any indexed column. >> 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. Yes, I was thinking along those lines (ie order by keyColumn, rowid). That will work for tables. I'll have to adapt it somehow for views. >> 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 Yes, that's where I was headed, but I wondered if running that query continuously might have unnecessary overhead since it's constantly preparing a new query for each row. I wondered if I'd be better just having SELECT <whatever> FROM <table> WHERE (keyColumn||rowid)>lastlineKey ORDER BY keyColumn,rowid ie with no limit, leaving the query open and getting new rows as the scroll progresses, finally closing the query when the scrolling stops. Obviously I would need to guarantee that no changes are attempted to be made to the database while the query is open and the rows are scrolling. Thanks for your thoughts, Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
