[SOLVED] Views have no rowid values

Views have no rowid values

Views have no rowid, is that expected? Example:

create table t2 (u text);
insert into t2 values ('x');
insert into t2 values ('y');
create table t1 (t text, i integer);
insert into t1 values ('a',1);
insert into t1 values ('b',2);
insert into t1 values ('c',1);
insert into t1 values ('d',2);
create view v as select * from t1 join t2 on t1.i=t2.rowid;
select rowid,* from t1;
ro t  i
-- -- --
1  a  1
2  b  2
3  c  1
4  d  2
select rowid,* from v;
ro t  i  u
-- -- -- ----------
   a  1  x
   b  2  y
   c  1  x
   d  2  y

I worked around this by adding an 'ID' column to t1 where ID=rowid. On the SQLite VIEW web page I found no reference to rowid.

I wanted to know if I misused the view. The view is to present a single table that links several related tables. I need to access the view by its 'rowid' because my tables are write-once (by a data extractor) then read-only. The data extractor knows what it entered and the rowid, then avoids to create a redundant column.

Solution

rowid values identify table rows; views do not have rows stored in them.

To identify the rows from a table used in the view, you can simply include the rowid value from that table in the view:

CREATE VIEW v AS SELECT t1.rowid, ... FROM t1 ...;

Anyway, a column declared as INTEGER PRIMARY KEY is an alias for the rowid (and therefore does not require extra storage). If you actually want to use the rowid values, it would be a better idea to include such a column explicitly in the table definition:

CREATE TABLE t1 (
    id INTEGER PRIMARY KEY,
    t TEXT,
    i INTEGER
);

(It still behaves as the rowid; you get autoincremented values by inserting NULL values into it.)