Anyone, anyone, Bueller? Here's a short version:
How can I best scroll though the results of an arbitrary select query? Below is the detailed version of my question. Any help appreciated. Thanks, Tom BareFeetWare ---- From: BareFeetWare <list....@barefeetware.com> Date: 16 February 2011 12:05:47 AM AEDT To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Subject: [sqlite] Scrolling through results of select Hi all, I'm looking for the best way to scroll through data returned by an arbitrary select statement. That is, I want something that will work for any SQL select statement string I throw at it at runtime. I've looked through: http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor and a few posts on this list. 1. It seems that in order to extract, say, the first n rows from a select, I do this: begin ; create temp view if not exists "Scrolling View" as <sql of arbitrary select statement> ; select * from "Scrolling View" order by keyColumn limit n ; commit ; 2. And then, to get the next n rows: maxVisibleKeyValue = last value of keyColumn in visible set (returned above). begin ; create temp view if not exists "Scrolling View" as <sql of arbitrary select statement> ; select * from "Scrolling View" where keyColumn > :maxVisibleKey order by keyColumn limit n ; commit ; For the next 10 rows, I should repeat step 2. For reverse scrolling, I'll run something like: minVisibleKeyValue = first value of keyColumn in visible set. begin ; create temp view if not exists "Scrolling View" as <sql of arbitrary select statement> ; select * from "Scrolling View" where keyColumn < :minVisibleKeyValue order by keyColumn desc limit n ; commit ; Questions: 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? 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? 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? 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". 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) . Any help appreciated. Thanks, 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
