Access underlying rowid even when overwritten

(1) By Adrian (adrian.s) on 2022-10-25 19:19:56 [link] [source]

If I have following database:

CREATE TABLE rowid_test (
  _rowid_ TEXT,
  rowid TEXT,
  oid TEXT
);

INSERT INTO rowid_test
  (_rowid_, rowid, oid)
VALUES
  ('a', 'b', 'c'),
  ('d', 'e', 'f');

Is there still a way to access the underlying rowid?

(2) By Simon Slavin (slavin) on 2022-10-25 22:05:19 in reply to 1 [link] [source]

Please don't use 'rowid', '_rowid_' or 'oid' as a name for a column you create. Consider them reserved words. If you want to store your own id for each row call it something else. If you want to use the rowid column SQLite itself maintains, refer to it by any of its names, but don't include it in your CREATE TABLE command.

If you don't get confused over what 'rowid' means, does your problem go away ? I don't understand what you're actually trying to do.

(3) By anonymous on 2022-10-26 05:51:10 in reply to 2 [link] [source]

This kind of problem shows up when you try to write a general tool that can process any database.

(4) By Adrian (adrian.s) on 2022-10-26 09:20:40 in reply to 3 [link] [source]

Yes, that's exactly what I'm doing. Even worse: User submitted databases. Therefore I must be able to handle this kind of table.

(5) By Gunter Hick (gunter_hick) on 2022-10-26 11:29:13 in reply to 1 [link] [source]

ALTER TABLE rowid_test RENAME rowid TO fubar;

This may cause strange effects if the original rowid is used in foreign keys.

(6.1) By Ryan Smith (cuz) on 2022-10-26 11:56:13 edited from 6.0 in reply to 1 [source]

Is there still a way to access the underlying rowid?

No.

The very reason that "rowid", "oid" and "_rowid_" all exist is to provide alternates in case any of those have been used as a column. But if you are going to exhaust all of those names there is nothing to be done.

For instance, let's say I said: 'oh, you can also use "clockworkorange" as a reference to the row-id', then your next post will surely be: What if I have a table like

  CREATE TABLE x(
     rowid TEXT, 
     oid TEXT,
     _rowid_ TEXT, 
     clockworkorange TEXT,
     .... etc.
)

Rinse and repeat.

There is no amount of aliases that will ensure no table can ever use all of them. Those 3 will have to do. There must be some rules, surely. If someone sends you a table where all of those were used as column names, I would strongly suspect malicious intent.

(7) By ddevienne on 2022-10-26 12:29:45 in reply to 6.1 [link] [source]

Is there still a way to access the underlying rowid?

No.

Yes there is :)

You use the official DB Data extension and access field -1

OK, that's a little roundabout, I'll admit. But it should work. --DD

(8.1) By Adrian (adrian.s) on 2022-11-13 09:15:56 edited from 8.0 in reply to 7 [link] [source]

Thanks, that's a good tip!

(FYI: The location of the file changed and it's now at https://sqlite.org/src/doc/tip/ext/recover/dbdata.c)