views may have rowid?
(1) By anonymous on 2021-02-03 21:44:04 [source]
This follows from this hint.
My session:
SQLite version 3.34.0 2020-12-01 16:14:00
Enter ".help" for usage hints.
sqlite> .open 'D:\SQLite32\DB\rosellamodel Sample Bug Report SQLITE.db'
sqlite> select tbl_name,type from sqlite_master where tbl_name = '__magicMaterialiser__';
tbl_name type
--------------------- ----
__magicMaterialiser__ view
sqlite> SELECT count(*)==0 FROM pragma_index_info('__magicMaterialiser__');
count(*)==0
-----------
1
- The database is from a user in this forum; I can't find the link but its the one DRH was 'going to keep'.
- The type is 'view'
- It has rowid according to DRH's tip.
How come views have rowid?
(2) By Keith Medcalf (kmedcalf) on 2021-02-04 00:07:39 in reply to 1 [link] [source]
A view is not a table.
(3.1) By Larry Brasfield (LarryBrasfield) on 2021-02-04 03:39:25 edited from 3.0 in reply to 1 [link] [source]
Your question presumes a fact not in evidence. You mention (vaguely) a view which, for some reason unknown to anybody but you and possibly DRH, provided some basis to believe that one particular view had a rowid. That observation, (whatever it was), does not mean that "views have rowid".
A view might or might not have a column which is named "rowid". If the view's underlying query, whose results the view contains, happens to name one of its columns "rowid", (explicitly or via default naming), then you may see that. You might further conclude, incorrectly, that the view has a "rowid" in the same sense that tables often have a rowid as discussed here and in SQLite documentation. What you would be overlooking by so concluding is that the "rowid" in a SQLite technical discussion is used as a key in a b-tree used for table row storage. A view involves no storage at all, hence it cannot have a rowid in that technical sense. That 5-letter word, by itself, does not mean that an integer b-tree key is involved.
(v.1 Brain fart fixed, s/columns "view"/columns "rowid"/ .)
(4) By anonymous on 2021-02-04 06:39:44 in reply to 3.1 [link] [source]
Brain fart fixed
Obviously not at your end
Look at the steps leading up to my question ... more carefully.
- I am not looking for a column arbitrarily named 'rowid' in a view.
- I am using DRH's code to establish whether a given object has the implicit primary column named 'rowid'.
- Found that for a view, that code returns 1.
Hence the original question.
To make it simpler, my question is this: Is DRH's code
SELECT count(*)==0 FROM pragma_index_info($tablename);
inapplicable/unreliable when dealing with views?
(5) By Keith Medcalf (kmedcalf) on 2021-02-04 07:18:17 in reply to 4 [link] [source]
Inapplicable. A view is not a table.
You can have a blue car and you can have red cars.
Just because something is red or blue does not mean it is a car.
Similarly, a "rowid table" is not an index but a "without rowid table" is an index. A view is not a table and therefore is neither a "rowid table" nor a "without rowid table" because it is a view, not a table.