Re: [sqlite] WITHOUT ROWID tables

[sky5walk]

'rowid' is a common name and could be in anyone's schema regardless of
table type.

On Mon, Feb 17, 2020 at 2:35 AM x  wrote:

> Re my earlier post (which didn’t go out to mailing list)
>
>
> sqlite3_table_column_metadata(db,dbName,tblName,"rowid",0,0,0,0,0)==SQLITE_OK
> returns false if table tblName is a without rowid table
>
> seems to work for me.
>
> Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for
> Windows 10
>
> 
> From: sqlite-users  on
> behalf of Simon Slavin 
> Sent: Sunday, February 16, 2020 6:11:02 PM
> To: SQLite mailing list 
> Subject: Re: [sqlite] WITHOUT ROWID tables
>
> On 16 Feb 2020, at 6:00pm, Bernardo Ramos  wrote:
>
> > I've got no rows for both with and without rowid tables:
>
> <https://sqlite.org/pragma.html#pragma_index_info>
>
> " as of SQLite version 3.30.0 on 2019-10-04 "
> ___
> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WITHOUT ROWID tables

[x]

Re my earlier post (which didn’t go out to mailing list)

sqlite3_table_column_metadata(db,dbName,tblName,"rowid",0,0,0,0,0)==SQLITE_OK
returns false if table tblName is a without rowid table

seems to work for me.

Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows 10


From: sqlite-users  on behalf of 
Simon Slavin 
Sent: Sunday, February 16, 2020 6:11:02 PM
To: SQLite mailing list 
Subject: Re: [sqlite] WITHOUT ROWID tables

On 16 Feb 2020, at 6:00pm, Bernardo Ramos  wrote:

> I've got no rows for both with and without rowid tables:

<https://sqlite.org/pragma.html#pragma_index_info>

" as of SQLite version 3.30.0 on 2019-10-04 "
___
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

[Peter da Silva]

If you have control over the schema you can put a schema version in some
table.

On Sat, Feb 15, 2020, 13:21 J. King  wrote:

> On February 15, 2020 2:14:30 p.m. EST, Thomas Kurz 
> wrote:
> >Wouldn't be something like
> >
> >SELECT sql FROM sqlite_master WHERE tbl_name='?' AND type='table'
> >contains "WITHOUT ROWID"
> >
> >be sufficient?
> >
> >Just being curious.
> >
> >- Original Message -
> >From: sky5w...@gmail.com 
> >To: SQLite mailing list 
> >Sent: Saturday, February 15, 2020, 18:06:47
> >Subject: [sqlite] WITHOUT ROWID tables
> >
> >Ok, not ideal. Still confusing, but I see the difference.
> >For my code, I know the schemas. I guess a SQL builder could offer up
> >query
> >options to the user browsing new databases.
> >
> >On Sat, Feb 15, 2020 at 11:26 AM Simon Slavin 
> >wrote:
> >
> >> On 15 Feb 2020, at 3:14pm, sky5w...@gmail.com wrote:
> >
> >> >> To determine if table XYZ is a WITHOUT ROWID table, run "PRAGMA
> >> >> index_info('XYZ');".  If you get back one or more rows, then XYZ
> >is a
> >> >> WITHOUT ROWID table.  If you get back no rows, then XYZ is a rowid
> >> >> table.
> >> >>
> >> > Confused...What if I made an index on a ROWID table?
> >> > CREATE INDEX "Z" ON "DOC" ("n_id");
> >
> >> The parameter in index_info() is normally the name of an index.  So
> >if you
> >> create an index "Z" and ask for index_info("Z") you will get
> >information on
> >> that index.
> >
> >> If you create a WITHOUT ROWID table with name 'Y", and ask for
> >> index_info("Y") you will get information on the primary key of that
> >table.
> >
> >> If both exist, you get information about the index.
> >
> >> Simon
> >> ___
> >> 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
> >
> >___
> >sqlite-users mailing list
> >sqlite-users@mailinglists.sqlite.org
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> CREATE TABLE t(
> c TEXT DEFAULT 'WITHOUT ROWID'
> -- this comment mentions something about WITHOUT ROWID
> );
>
>
> Contrived, no question, but possible.
> --
> J. King
> ___
> 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

[Simon Slavin]

On 16 Feb 2020, at 6:00pm, Bernardo Ramos  wrote:

> I've got no rows for both with and without rowid tables:



" as of SQLite version 3.30.0 on 2019-10-04 "
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WITHOUT ROWID tables

[Bernardo Ramos]

I've got no rows for both with and without rowid tables:

SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite> create table t1(name);
sqlite> create table t2(name primary key, phone) without rowid;
sqlite> pragma index_info(t1);
sqlite> pragma index_info(t2);
sqlite> pragma index_info('t1');
sqlite> pragma index_info('t2');
sqlite> .tab
t1  t2
sqlite> select * from t1 where rowid=1;
sqlite> select * from t2 where rowid=1;
Error: no such column: rowid




To determine if table XYZ is a WITHOUT ROWID table, run "PRAGMA
index_info('XYZ');".  If you get back one or more rows, then XYZ is a
WITHOUT ROWID table.  If you get back no rows, then XYZ is a rowid
table.


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

Re: [sqlite] WITHOUT ROWID tables

[J. King]

On February 15, 2020 2:14:30 p.m. EST, Thomas Kurz  
wrote:
>Wouldn't be something like
>
>SELECT sql FROM sqlite_master WHERE tbl_name='?' AND type='table'
>contains "WITHOUT ROWID"
>
>be sufficient?
>
>Just being curious.
>
>- Original Message - 
>From: sky5w...@gmail.com 
>To: SQLite mailing list 
>Sent: Saturday, February 15, 2020, 18:06:47
>Subject: [sqlite] WITHOUT ROWID tables
>
>Ok, not ideal. Still confusing, but I see the difference.
>For my code, I know the schemas. I guess a SQL builder could offer up
>query
>options to the user browsing new databases.
>
>On Sat, Feb 15, 2020 at 11:26 AM Simon Slavin 
>wrote:
>
>> On 15 Feb 2020, at 3:14pm, sky5w...@gmail.com wrote:
>
>> >> To determine if table XYZ is a WITHOUT ROWID table, run "PRAGMA
>> >> index_info('XYZ');".  If you get back one or more rows, then XYZ
>is a
>> >> WITHOUT ROWID table.  If you get back no rows, then XYZ is a rowid
>> >> table.
>> >>
>> > Confused...What if I made an index on a ROWID table?
>> > CREATE INDEX "Z" ON "DOC" ("n_id");
>
>> The parameter in index_info() is normally the name of an index.  So
>if you
>> create an index "Z" and ask for index_info("Z") you will get
>information on
>> that index.
>
>> If you create a WITHOUT ROWID table with name 'Y", and ask for
>> index_info("Y") you will get information on the primary key of that
>table.
>
>> If both exist, you get information about the index.
>
>> Simon
>> ___
>> 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
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

CREATE TABLE t(
c TEXT DEFAULT 'WITHOUT ROWID'
-- this comment mentions something about WITHOUT ROWID
);


Contrived, no question, but possible. 
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WITHOUT ROWID tables

[Thomas Kurz]

Wouldn't be something like

SELECT sql FROM sqlite_master WHERE tbl_name='?' AND type='table'
contains "WITHOUT ROWID"

be sufficient?

Just being curious.

- Original Message - 
From: sky5w...@gmail.com 
To: SQLite mailing list 
Sent: Saturday, February 15, 2020, 18:06:47
Subject: [sqlite] WITHOUT ROWID tables

Ok, not ideal. Still confusing, but I see the difference.
For my code, I know the schemas. I guess a SQL builder could offer up query
options to the user browsing new databases.

On Sat, Feb 15, 2020 at 11:26 AM Simon Slavin  wrote:

> On 15 Feb 2020, at 3:14pm, sky5w...@gmail.com wrote:

> >> To determine if table XYZ is a WITHOUT ROWID table, run "PRAGMA
> >> index_info('XYZ');".  If you get back one or more rows, then XYZ is a
> >> WITHOUT ROWID table.  If you get back no rows, then XYZ is a rowid
> >> table.
> >>
> > Confused...What if I made an index on a ROWID table?
> > CREATE INDEX "Z" ON "DOC" ("n_id");

> The parameter in index_info() is normally the name of an index.  So if you
> create an index "Z" and ask for index_info("Z") you will get information on
> that index.

> If you create a WITHOUT ROWID table with name 'Y", and ask for
> index_info("Y") you will get information on the primary key of that table.

> If both exist, you get information about the index.

> Simon
> ___
> 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

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

Re: [sqlite] WITHOUT ROWID tables

[curmudgeon]

Does
sqlite3_table_column_metadata(db,dbName,tblName,"rowid",0,0,0,0,0)==SQLITE_OK
return false if table tblName is a without rowid table?

https://sqlite.org/c3ref/table_column_metadata.html





--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WITHOUT ROWID tables

[sky5walk]

Ok, not ideal. Still confusing, but I see the difference.
For my code, I know the schemas. I guess a SQL builder could offer up query
options to the user browsing new databases.

On Sat, Feb 15, 2020 at 11:26 AM Simon Slavin  wrote:

> On 15 Feb 2020, at 3:14pm, sky5w...@gmail.com wrote:
>
> >> To determine if table XYZ is a WITHOUT ROWID table, run "PRAGMA
> >> index_info('XYZ');".  If you get back one or more rows, then XYZ is a
> >> WITHOUT ROWID table.  If you get back no rows, then XYZ is a rowid
> >> table.
> >>
> > Confused...What if I made an index on a ROWID table?
> > CREATE INDEX "Z" ON "DOC" ("n_id");
>
> The parameter in index_info() is normally the name of an index.  So if you
> create an index "Z" and ask for index_info("Z") you will get information on
> that index.
>
> If you create a WITHOUT ROWID table with name 'Y", and ask for
> index_info("Y") you will get information on the primary key of that table.
>
> If both exist, you get information about the index.
>
> Simon
> ___
> 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

[Simon Slavin]

On 15 Feb 2020, at 3:14pm, sky5w...@gmail.com wrote:

>> To determine if table XYZ is a WITHOUT ROWID table, run "PRAGMA
>> index_info('XYZ');".  If you get back one or more rows, then XYZ is a
>> WITHOUT ROWID table.  If you get back no rows, then XYZ is a rowid
>> table.
>> 
> Confused...What if I made an index on a ROWID table?
> CREATE INDEX "Z" ON "DOC" ("n_id");

The parameter in index_info() is normally the name of an index.  So if you 
create an index "Z" and ask for index_info("Z") you will get information on 
that index.

If you create a WITHOUT ROWID table with name 'Y", and ask for index_info("Y") 
you will get information on the primary key of that table.

If both exist, you get information about the index.

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

Re: [sqlite] WITHOUT ROWID tables

[sky5walk]

>
> To determine if table XYZ is a WITHOUT ROWID table, run "PRAGMA
> index_info('XYZ');".  If you get back one or more rows, then XYZ is a
> WITHOUT ROWID table.  If you get back no rows, then XYZ is a rowid
> table.
>
Confused...What if I made an index on a ROWID table?
CREATE INDEX "Z" ON "DOC" ("n_id");
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WITHOUT ROWID tables

[Richard Hipp]

On 2/14/20, Wayne Collins  wrote:
> My first posting so I hope I get the etiquette correct.
>
> 1st question How can I determine from a C-program if a table was
> generated with the "WITHOUT ROWID" option? I have an application where
> it is important to know if a table has a rowid or not.

To determine if table XYZ is a WITHOUT ROWID table, run "PRAGMA
index_info('XYZ');".  If you get back one or more rows, then XYZ is a
WITHOUT ROWID table.  If you get back no rows, then XYZ is a rowid
table.


>
> 2nd Question After a database is defined and the tables created, I have
> an application which automatically generates C++ DAO and DTO objects for
> each table. At run time, I'd like to be certain that the schema and the
> DAO/DTO match. I had thought to use the schema_version pragma but it
> increments when the db is VACUUMED. Is there some hidden cookie or value
> I can use that I have overlooked?
>

There are no magic cookies that change with the schema but not with VACUUM.

Perhaps you could store a strong hash of the sqlite_master.sql column
for each table with your C++ objects, then compare the hash upon
reconnect to see if anything has changed.

-- 
D. Richard Hipp
d...@sqlite.org
___
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 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