Jay Sprenkle wrote:
Is there a way I can scroll thru a particular index? For example:
1. Scroll forward/backward on a given set of records
2. Start at position X
3. Start at a record that matches a criteria
SQL is optimized to manipulate a set of records. It's much faster to execute
"update mytable set mycolumn = 0 where mycondition = true"
than to iterate through them and set them to zero individually.
Can you do whatever it is with sql instead of code?
The index is used transparently to locate rows faster.
You don't iterate the index, you iterate the rows.
I am thinking more in terms of the query/read part, than on updates.
My application is geared towards users who want to find a specific name
in a list of names, and then want to have the possibility to scroll
backwards or forwards. For example, if I search for "Sprenkle" I want
to show the user a window with "Sprenkle" in the middle, preceded by the
50 names before it, and followed by the 50 names after it, and also to
be able to smoothly scroll in either direction.
I know the index contains sufficient data to do this, but there seems to
be no way to use it from SQLite.
I understand that getting the data by "chunks" or "pages" the way SQL
does is perfect for client/server models. But, given that SQLite is
more geared towards standalone/embedded systems, it wouldn't hurt to
have an extra mechanism for 'local data scrolling', maybe thru standard
SQL cursors, which it currently doesn't support.
Anyway, maybe separate topic, I tried to create a "snapshot" window of
the above using plain SQL, but it doesn't seem to work on Sqlite 3.3.4:
CREATE TABLE clients (custid integer primary key, lastname varchar(50));
CREATE INDEX cidx ON (lastname);
(insert 10,000 records here)
SELECT idx,lastname FROM
(
-- get names before search criteria
SELECT '1' as idx,lastname
FROM (select lastname from customers where lastname<'sprenkle'
ORDER BY lastname DESC LIMIT 50)
UNION
-- get names matching search criteria plus 50 more
SELECT '2' as idx,lastname
FROM (select lastname from customers where lastname>='sprenkle'
ORDER BY lastname ASC LIMIT 50)
)
order by 1,2;
Individually, the queries work fine. In UNION, each seems to lose the
inner order clause and show innacurate results.
jp
