SQLITE - RowID "column name"

Databases
1

Is there a way to reference the ROWID column (that 64bit value), in such a way that you are SURE that is what you are truly retrieving?

Here is why I ask…
It seems that SQLite has 3 (that I know of) Alias for “rowid”

  • ROWID
  • OID
  • ROWID

HOWEVER, it is legal to create a table using those a column names!
making it impossible to get to the “true” rowid

create table xxx(rowid text,oid text,_rowid_ text)
2

seems the answer is
Nope, you’re screwed

per SQLite documents

[quote]The rowid is always available as an undeclared column named ROWID, OID, or ROWID
as long as those names are not also used by explicitly declared columns.[/quote]

Note : the term “ALWAYS” assumes also the table was not created with the “WITHOUT ROWID” constraint

3

you could query schema and see if a column is named ROWID, OID or ROWID.

4

And what does that solve? If there is a user defined column with one of those names, the actual ROWID is not accessible… period

5

The way I read that is if, say, ROWID is in use as a column name, you can still use OID or ROWID to get the row id.

6

However, SQLite returns “ROWID” as the fieldname for all alias,
If user defined any one or more, then how can an app decide which is “real”… beside reading the field schema and checking them off a “list”
If the user went bat-sh*t crazy and defined ALL of them… oh well

And I’m not talking about “me” deterimining… I am talking about an app looking at ANY Sqlite database and figuriing it out.

create table xyz (rowid integer);
insert into xyz values(99);
select oid,_rowid_,rowid from xyz;

results
rowid=1 rowid=1 rowid=99
so which is which? since db.idxField(x).name is “ROWID” in all 3 cases

this DOES return the “right name”

select oid as oid,_rowid_ as _rowid_,rowid from xyz;

oid=1 rowid=1 rowid=99

but basically… Dr.Hipp did say “he screwed that up”

7

Given that you have to ask for the rowid explicitly, you know the position of the field on the result set.

8

Uh… no… worst case scenario

CREATE TABLE xyz(rowid as integer,PK as Primary Key) WITHOUT ROWID

First field is “called” ROWID, but it is not the ROWID

The point is. you cannot know for sure without a lot of time consuming analysis

9

Nobody should do that mess.

10

It’s hard to write code to prevent stupid!

11

SHOULD is the key word… and as a developer it is our job to attempt to cover worst case scenarios, if at all possible.

12

FYI… It seems that there are at least a few commerical SQLite Managers that CANNOT handle databases that

  • have tables created using the “WITHOUT ROWID” constraint
  • get “confused” if the table contains a user column named “rowid”

Both of which I am trying to avoid with my Tadpole application :slight_smile:
so far it CAN handle “WITHOUT ROWID”, and Ive managed to minimize the affect of the 2nd situation

Also for those of you who haven’t read as much about SQLite as I seemed to be doing lately. There are indications that the ROWID column will remain for backwards compatiblitly… but MAY NOT BE THE DEFAULT in the future… (ie. Tables will automatically be created assuming “WITHOUT ROWID” constraint)…