The short question: do (temporary) VIEW's have rowid's in SQLITE?
A small description of the problem:
I have a small database of values, where I would like users to be able
to execute simple filters (as (Key,Value) pairs) to return a subset of
the data. On occasion, the result is quite large, and my standard way
of returning results is very slow on large result sets, so I try to
break up how many results I return. Since TOP does not exist in sqlite,
I thought I would create a temporary view using the (Key,Value) pairs in
a SELECT statement. Then I could request results from the view, and
limit the result set size by view.rowid > some_number. From a perusal
of the documentation, SWIKI, and some of my own experiments, it doesn't
seem like this will work, because VIEWs don't seem to have rowids. If
there are not, in fact, rowid's in a VIEW (I can imagine there aren't
since it seems like a VIEW is just a link back to rowid's in the source
tables), is there a decent way to do what I need?
So, a quick source table:
CREATE TABLE KeyValue AS
{
KVID INTEGER PRIMARY KEY,
KeyID INTEGER NOT NULL,
ValueID INTEGER NOT NULL
} ;
And an example of the view:
CREATE TEMPORARY VIEW vwKVFilter AS
SELECT KeyID, ValueID
FROM KeyValue
WHERE KeyID = '1' AND ValueID = '2' ;
And what I would like to be able to do to look at the results in
vwKVFilter, repeatedly, by changing the BETWEEN conditions:
SELECT KeyID, ValueID
FROM vwKVFilter
WHERE vwKVFilter.rowid BETWEEN 10 AND 20
Actually, what I really want to do is return the 'next' 10 items
starting at some id, without having to reconduct the original SELECT
that populated the VIEW (that query is, in the real system, a fairly
large set of inner joins) , but I don't see anything in the structure of
SQLite that allows this kind of behavior. Short of the rowid's, which
already increment, I don't see any way to provide a rowid-like item in
the view, that increments as rows are inserted.
Any help is much appreciated.
Incidentally, the documentation on the web site for CREATE VIEW looks a
bit jacked-up. The sentence beginning 'If a is specified...' and other
parts of that section are missing words, I think. I assume the missing
word is 'database-name' .
--Keith
