[sqlite] Are my assumptions about the implyed OID valid?

[Michael Scharf]

Hi,

I am using sqlite as embedded database. I am using
a UI to display the data. I have tables with up to
1 million entries. The user can sort columns and filter
the data. To keep the memory footprint of the application
low I'm using a temporary table in the database for
each table view. I make the assumption that items added
in order to a table have their OID in that order
(this is true in sqlite 3.2.7). Then I just can fetch
the data of OID range that is visible in the UI. This
minimizes data transfer between the database and
the UI:

-- my database
CREATE TABLE data (
   name TEXT;
   number INTEGER;
);

CREATE INDEX dataNameIndex on data(name);
CREATE INDEX dataNumberIndex on data(number);

-- The user sorts on NUMBER and selects NAME that
-- start with 'foo'. I create a temp table view1.
-- I make the assumption that view1.oid is in order
-- (1...size_of_the_table)
-- This is currently true with sqlite 3.2.7

CREATE TEMP TABLE view1 AS
  SELECT oid AS dataid
  FROM data
  WHERE name like 'foo%' ORDER number;

-- when some columns are displayed in the UI I fetch
-- the items. Say row 100-150 is to be displayed:

  SELECT *
  FROM data
  WHERE oid IN
     (SELECT dataid
      FROM view1
      WHERE rowid between 100 AND 150)
  ORDER BY number; -- this sorting is needed, to
                   -- get the result in order!

This work fine currently. The question is, is this an artifact
of the current implementation or can I rely on this in the
future? (I know that's not compatible with the SQL specs)

Michael