Re: [sqlite] WITHOUT ROWID tables with a ROWID

[Tom Bassel]

Ah I see now. Sorry I should have read the docs more carefully -- it is working 
according to spec in all cases.

Great answers. Thanks guys!
Tom
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WITHOUT ROWID tables with a ROWID

[tom-sqlite]

Ah I see now. Sorry I should have read the docs more carefully -- it is working 
according to spec in all cases.
   
Great answers. Thanks guys!
Tom
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WITHOUT ROWID tables with a ROWID

[David Raymond]

Take a look at
https://www.sqlite.org/lang_createtable.html
Under the section "ROWIDs and the INTEGER PRIMARY KEY"

"If a table contains a user defined column named "rowid", "oid" or "_rowid_", 
then that name always refers the explicitly declared column and cannot be used 
to retrieve the integer rowid value."

So t3 is a "without rowid table" (index b-tree) which happens to have a normal 
field named "rowid"

t4 is a "regular rowid table" (table b-tree) where the identifier "rowid" goes 
to a different field name than the "must be an integer rowid" That table should 
still have the "must be an integer rowid" visible as "oid" or "_rowid_"

t5 is a "without rowid table" (index b-tree) with a primary key of a single 
field with "integer" affinity. Aka "prefer to store it as an integer but don't 
have to" sort.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Tom Bassel
Sent: Thursday, February 21, 2019 3:02 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] WITHOUT ROWID tables with a ROWID

Hi, this is my first post to this group. I am not trying to be difficult. I 
work on an app that uses SQLite as the underlying database engine. At one time 
years ago I remember assuming that the ROWID column in a table in SQLite would 
always be an integer. However, I can see a user who is not familiar with SQLite 
create a table with a column named "rowid" that is not an integer (in table t4 
below). Are tables t3, t4, and t5 below working as designed? 

Thanks and I really enjoy using SQLite and reading this mailing list.
Tom

SQLite version 3.27.1 2019-02-08 13:17:39
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t1(nonrowid);
sqlite> insert into t1(rowid, nonrowid) values ('pluto', 'venus');
Error: datatype mismatch
sqlite> create table t2(nonrowid text primary key) without rowid;
sqlite> insert into t2 values('sun');
sqlite> select rowid, * from t2;
Error: no such column: rowid
sqlite> create table t3(rowid text primary key) without rowid;
sqlite> insert into t3 values('moon');
sqlite> select rowid, * from t3;
moon|moon
sqlite> create table t4(rowid text primary key);
sqlite> insert into t4 values('earth');
sqlite> select rowid, * from t4;
earth|earth
sqlite> create table t5(rowid integer primary key) without rowid;
sqlite> insert into t5 values('mars');
sqlite> select rowid, * from t5;
mars|mars

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WITHOUT ROWID tables with a ROWID

[J. King]

On 2019-02-21 15:02:23, "Tom Bassel"  wrote:


Are tables t3, t4, and t5 below working as designed?


Yes. See :

> The rowid value can be accessed using one of the special 
case-independent names "rowid", "oid", or "_rowid_" in place of a column 
name. If a table contains a user defined column named "rowid", "oid" or 
"_rowid_", then that name always refers the explicitly declared column 
and cannot be used to retrieve the integer rowid value.


--
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] WITHOUT ROWID tables with a ROWID

[Tom Bassel]

Hi, this is my first post to this group. I am not trying to be difficult. I 
work on an app that uses SQLite as the underlying database engine. At one time 
years ago I remember assuming that the ROWID column in a table in SQLite would 
always be an integer. However, I can see a user who is not familiar with SQLite 
create a table with a column named "rowid" that is not an integer (in table t4 
below). Are tables t3, t4, and t5 below working as designed? 

Thanks and I really enjoy using SQLite and reading this mailing list.
Tom

SQLite version 3.27.1 2019-02-08 13:17:39
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t1(nonrowid);
sqlite> insert into t1(rowid, nonrowid) values ('pluto', 'venus');
Error: datatype mismatch
sqlite> create table t2(nonrowid text primary key) without rowid;
sqlite> insert into t2 values('sun');
sqlite> select rowid, * from t2;
Error: no such column: rowid
sqlite> create table t3(rowid text primary key) without rowid;
sqlite> insert into t3 values('moon');
sqlite> select rowid, * from t3;
moon|moon
sqlite> create table t4(rowid text primary key);
sqlite> insert into t4 values('earth');
sqlite> select rowid, * from t4;
earth|earth
sqlite> create table t5(rowid integer primary key) without rowid;
sqlite> insert into t5 values('mars');
sqlite> select rowid, * from t5;
mars|mars

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users