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

[sqlite] WITHOUT ROWID tables

[Wayne Collins]

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.


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?


Thanks in advance


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

Re: [sqlite] WITHOUT ROWID

[Richard Hipp]

On 9/8/19, t...@qvgps.com  wrote:
>
> One option would be to create a new table with rowid, copy all records from
> the old one, delete old one and rename new table.

That is the quickest solution.

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

[sqlite] WITHOUT ROWID

[t...@qvgps.com]

Hi sqlite-group,

I accidentally created tables WITHOUT ROWID.
These tables are filled with some 100MB of data and delivered to customers 
already.

Now I'm looking for way to fix this bug.
Is it possible to change (ALTER TABLE?) these tables to still include rowid?

One option would be to create a new table with rowid, copy all records from the 
old one, delete old one and rename new table.
But maybe there is more simple, quicker solution?
Like just adding a column?

Thanks,
Tom


/
**   Flemming Software Development CC
**   Thomas Flemming
**   PO Box 81244
**   Windhoek, Namibia
**   http://www.quovadis-gps.com
**   mail  t...@qvgps.com
**   +264 (0)81 3329923Nam mobile
**   +49  (0)175 7313081   D mobile
**   +49  (0)6182 8492599  D office
***/

___
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

[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

Re: [sqlite] without rowid and secondary indexes

[Olivier Mascia]

> Le 10 avr. 2018 à 18:25, Clemens Ladisch  a écrit :
> 
> Olivier Mascia wrote:
>> Would you mean:
>> 
>>  select NAME,count(NAME) from SHEETS where SHEET>? group by NAME having NAME 
>> is not NULL;
>> 
>> is more logically appropriate?
> 
> Probably.  The first query can give quite random results:
> 
>  delete from SHEETS;
>  insert into SHEETS(NODE, SHEET, NAME) values(1, 1, 'a');
>  insert into SHEETS(NODE, SHEET, NAME) values(1, 3, 'a');
>  select NAME,count(NAME) from SHEETS group by NAME having SHEET>2 and NAME is 
> not NULL;
>  a|2
>  select NAME,count(NAME) from SHEETS where SHEET>2 group by NAME having NAME 
> is not NULL;
>  a|1
>  delete from SHEETS;
>  insert into SHEETS(NODE, SHEET, NAME) values(1, 3, 'a');
>  insert into SHEETS(NODE, SHEET, NAME) values(2, 1, 'a');
>  select NAME,count(NAME) from SHEETS group by NAME having SHEET>2 and NAME is 
> not NULL;
>  select NAME,count(NAME) from SHEETS where SHEET>2 group by NAME having NAME 
> is not NULL;
>  a|1
> 
>> Or that I might even go for:
>> 
>>  select NAME,count(NAME) from SHEETS where SHEET>? and NAME is not NULL 
>> group by NAME;
> 
> This always has the same result as the second query.  (But "NAME is not NULL" 
> in the
> WHERE clause applies to the table column, while in the HAVING clause, it 
> applies to
> the result of the grouping.  So the last query makes it more likely that 
> SQLite can
> prove that it can use the index.)

Thanks a lot: this proved wonderfully educative, especially the oddities 
arising from:

> select NAME,count(NAME) from SHEETS group by NAME having SHEET>2 and NAME is 
> not NULL;

-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia


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

Re: [sqlite] without rowid and secondary indexes

[Clemens Ladisch]

Olivier Mascia wrote:
>> Le 10 avr. 2018 à 13:20, Clemens Ladisch  a écrit :
>>> select NAME,count(NAME) from SHEETS group by NAME having SHEET>? and NAME 
>>> is not NULL;
>>
>> The SHEET value is from some random row in the group, and the NAME value is
>> the same for all rows in the group.  Are you sure you do not want to use 
>> WHERE
>> instead of HAVING?
>
> Would you mean:
>
>   select NAME,count(NAME) from SHEETS where SHEET>? group by NAME having NAME 
> is not NULL;
>
> is more logically appropriate?

Probably.  The first query can give quite random results:

  delete from SHEETS;
  insert into SHEETS(NODE, SHEET, NAME) values(1, 1, 'a');
  insert into SHEETS(NODE, SHEET, NAME) values(1, 3, 'a');
  select NAME,count(NAME) from SHEETS group by NAME having SHEET>2 and NAME is 
not NULL;
  a|2
  select NAME,count(NAME) from SHEETS where SHEET>2 group by NAME having NAME 
is not NULL;
  a|1
  delete from SHEETS;
  insert into SHEETS(NODE, SHEET, NAME) values(1, 3, 'a');
  insert into SHEETS(NODE, SHEET, NAME) values(2, 1, 'a');
  select NAME,count(NAME) from SHEETS group by NAME having SHEET>2 and NAME is 
not NULL;
  select NAME,count(NAME) from SHEETS where SHEET>2 group by NAME having NAME 
is not NULL;
  a|1

> Or that I might even go for:
>
>   select NAME,count(NAME) from SHEETS where SHEET>? and NAME is not NULL 
> group by NAME;

This always has the same result as the second query.  (But "NAME is not NULL" 
in the
WHERE clause applies to the table column, while in the HAVING clause, it 
applies to
the result of the grouping.  So the last query makes it more likely that SQLite 
can
prove that it can use the index.)


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

Re: [sqlite] without rowid and secondary indexes

[Olivier Mascia]

> Le 10 avr. 2018 à 13:20, Clemens Ladisch  a écrit :
> 
>> select NAME,count(NAME) from SHEETS group by NAME having SHEET>? and NAME is 
>> not NULL;
> 
> The SHEET value is from some random row in the group, and the NAME value is
> the same for all rows in the group.  Are you sure you do not want to use WHERE
> instead of HAVING?

Thanks for the hint, Clemens.
Would you mean:

  select NAME,count(NAME) from SHEETS where SHEET>? group by NAME having NAME 
is not NULL;

is more logically appropriate? I'm inclined to think yes and that was what you 
meant.

Or that I might even go for:

  select NAME,count(NAME) from SHEETS where SHEET>? and NAME is not NULL group 
by NAME;

On a test table with statistically representative values (only ~95'000 rows), I 
get the same plan in all three cases:

  --EQP-- 0,0,0,SEARCH TABLE SHEETS USING COVERING INDEX SHEETS_NAME (NAME>?)


-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia


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

Re: [sqlite] without rowid and secondary indexes

[Clemens Ladisch]

Olivier Mascia wrote:
> [...]
> So I should probably even better go with:
>
>   create index ... where NAME is not NULL;
>
> as my only queries involving NAME have a 'where NAME is not NULL' restriction.

To be sure, check with EXPLAIN QUERY PLAN whether the index is actually used.

> select NAME,count(NAME) from SHEETS group by NAME having SHEET>? and NAME is 
> not NULL;

The SHEET value is from some random row in the group, and the NAME value is
the same for all rows in the group.  Are you sure you do not want to use WHERE
instead of HAVING?


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

Re: [sqlite] without rowid and secondary indexes

[Olivier Mascia]

Thanks!

> In your example below, your PRIMARY KEY consists of two integer
> instead of just a single rowid integer.  So it will take up slightly
> more space.  Not a lot, but some.
> 
> Will that make a difference in your application?  I do not know. 

Then, I will indeed run some more tests, but I guess I'm OK with adding the 
index while keeping the table without rowid because few rows will actually have 
a NAME value, most (like in more than 95% of them) will be NULL. What's more, 
most queries are PK centric.

So I should probably even better go with:

  create index if not exists SHEETS_NAME on SHEETS(NAME) where NAME is not NULL;

as my only queries involving NAME have a 'where NAME is not NULL' restriction. 
The purpose of the index being only to optimize such occasional dictionary 
building queries:

select NAME,count(NAME) from SHEETS group by NAME having SHEET>? and NAME is 
not NULL;

-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia


> Le 10 avr. 2018 à 12:36, Richard Hipp  a écrit :
> 
> Every index is composed of the columns being indexed followed by the
> primary key of the table.  In a regular ROWID table, the "primary key"
> is the rowid - an integer.  In a WITHOUT ROWID table, the primary key
> is whatever you declare the PRIMARY KEY to be.
> 
> So whether or not there is more overhead in an index on a WITHOUT
> ROWID table versus a normal rowid table depends on your choice of the
> PRIMARY KEY.
> 
> In your example below, your PRIMARY KEY consists of two integer
> instead of just a single rowid integer.  So it will take up slightly
> more space.  Not a lot, but some.
> 
> Will that make a difference in your application?  I do not know.  I
> suggest you try it and see.  This is one of the great beauties of SQL
> (any SQL not just SQLite).  You can make a simple schema change like
> this and try it out, without having to modify your application -
> indeed without having to even recompile your application.  Simply run
> the same build of your application using two different versions of the
> database - one with an ordinary rowid table and the other with a
> WITHOUT ROWID table - and see which one gives you better performance -
> for whatever definition of "performance" is important to you.
> 
> On 4/10/18, Olivier Mascia  wrote:
>> Dear all,
>> 
>> With https://www.sqlite.org/withoutrowid.html in mind, assuming something
>> simple as:
>> 
>>  create table if not exists SHEETS(
>>  NODE integer not null,
>>  SHEET integer not null,
>>  NAME text,   -- often NULL, sometimes short text value
>> (keyword)
>>  ..., -- 2 or 3 other small properties
>>  primary key(NODE,SHEET)
>>  ) without rowid;
>>  -- makes sense because small row and natural primary key is not integer
>> 
>> Now what if an index on NAME becomes valuable?
>> Will such a secondary index have a higher storage cost or performance cost
>> than with an ordinary table?
>> 
>>  create index if not exists SHEETS_NAME on SHEETS(NAME);
>> 
>> --
>> Best Regards, Meilleures salutations, Met vriendelijke groeten,
>> Olivier Mascia


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

Re: [sqlite] without rowid and secondary indexes

[Richard Hipp]

Every index is composed of the columns being indexed followed by the
primary key of the table.  In a regular ROWID table, the "primary key"
is the rowid - an integer.  In a WITHOUT ROWID table, the primary key
is whatever you declare the PRIMARY KEY to be.

So whether or not there is more overhead in an index on a WITHOUT
ROWID table versus a normal rowid table depends on your choice of the
PRIMARY KEY.

In your example below, your PRIMARY KEY consists of two integer
instead of just a single rowid integer.  So it will take up slightly
more space.  Not a lot, but some.

Will that make a difference in your application?  I do not know.  I
suggest you try it and see.  This is one of the great beauties of SQL
(any SQL not just SQLite).  You can make a simple schema change like
this and try it out, without having to modify your application -
indeed without having to even recompile your application.  Simply run
the same build of your application using two different versions of the
database - one with an ordinary rowid table and the other with a
WITHOUT ROWID table - and see which one gives you better performance -
for whatever definition of "performance" is important to you.

On 4/10/18, Olivier Mascia  wrote:
> Dear all,
>
> With https://www.sqlite.org/withoutrowid.html in mind, assuming something
> simple as:
>
>   create table if not exists SHEETS(
>   NODE integer not null,
>   SHEET integer not null,
>   NAME text,   -- often NULL, sometimes short text value
> (keyword)
>   ..., -- 2 or 3 other small properties
>   primary key(NODE,SHEET)
>   ) without rowid;
>   -- makes sense because small row and natural primary key is not integer
>
> Now what if an index on NAME becomes valuable?
> Will such a secondary index have a higher storage cost or performance cost
> than with an ordinary table?
>
>   create index if not exists SHEETS_NAME on SHEETS(NAME);
>
> --
> Best Regards, Meilleures salutations, Met vriendelijke groeten,
> Olivier Mascia
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


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

[sqlite] without rowid and secondary indexes

[Olivier Mascia]

Dear all,

With https://www.sqlite.org/withoutrowid.html in mind, assuming something 
simple as:

  create table if not exists SHEETS(
  NODE integer not null,
  SHEET integer not null,
  NAME text,   -- often NULL, sometimes short text value (keyword)
  ..., -- 2 or 3 other small properties
  primary key(NODE,SHEET)
  ) without rowid;
  -- makes sense because small row and natural primary key is not integer

Now what if an index on NAME becomes valuable?
Will such a secondary index have a higher storage cost or performance cost than 
with an ordinary table?

  create index if not exists SHEETS_NAME on SHEETS(NAME);

-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia


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

[sqlite] WITHOUT ROWID

[R Smith]

On 2016/05/19 2:50 PM, Richard Hipp wrote:
> On 5/19/16, Ertan K???ko?lu  wrote:
>> Hello,
>>
>> I wonder if there are any drawbacks/things to be aware of, etc. for tables
>> created "WITHOUT ROWID". I am considering using such tables in another
>> project.
> Experiment.  Run your application with WITHOUT ROWID and measure
> performance (where "performance" is defined by whatever is important
> to you) then remove the WITHOUT ROWID and run the same experiment.
> Decide which works best for you.
>
> All of the application code should work the same either way, so other
> than removing the "WITHOUT ROWID" text from the "CREATE TABLE" no
> other changes are required.
>

True, though be aware that some SQLite functionality requires tables to 
have rowids, such as AUTOINCREMENT.
Also, SQlite engines prior to 3.8.2 will not work and will report a 
malformed database.
Any table using WITHOUT ROWID must have a primary key (this is otherwise 
not a requirement).
Primary keys cannot have NULL values in WITHOUT ROWID tables - this is 
perfectly possible otherwise, though most of us feel it should never be 
possible anyway.
If you ever use the last_insert_id type of API's in SQLite, it won't 
work on a WITHOUT ROWID table. (But you shouldn't anyway...)
On-Update callbacks from the API doesn't work right (or at all - not 
tested recently) in a WITHOUT ROWID table
The new SESSION extension requires ROWID tables ^[needs citation]. (I 
think - not tested yet)
I think the SQLDIFF might also, but now I'm just guessing.

My point is, check out all the kinds of things you might do in SQLite, 
make sure they can be done with WITHOUT ROWID tables. If they can, the 
change is worth it - the WITHOUT ROWID optimization is quite faster on 
large key lookups and nice and lean in size (no need for rowid 
translation or added rowid key data per table).

Everywhere I use it, it works a charm, and if ever I find I want to use 
some functionality on a table that requires a rowid, the changeover is 
quick and painless, any DB admin system out there would probably have a 
1-click solution to do so.


Best of luck,
Ryan

[sqlite] WITHOUT ROWID

[Ertan Küçükoğlu]

Hello,

I wonder if there are any drawbacks/things to be aware of, etc. for tables
created "WITHOUT ROWID". I am considering using such tables in another
project.

Thanks.

Regards,
Ertan K???ko?lu

[sqlite] WITHOUT ROWID

[Richard Hipp]

On 5/19/16, Ertan K???ko?lu  wrote:
> Hello,
>
> I wonder if there are any drawbacks/things to be aware of, etc. for tables
> created "WITHOUT ROWID". I am considering using such tables in another
> project.

Experiment.  Run your application with WITHOUT ROWID and measure
performance (where "performance" is defined by whatever is important
to you) then remove the WITHOUT ROWID and run the same experiment.
Decide which works best for you.

All of the application code should work the same either way, so other
than removing the "WITHOUT ROWID" text from the "CREATE TABLE" no
other changes are required.

-- 
D. Richard Hipp
drh at sqlite.org

Re: [sqlite] Without ROWID and Autoincrement question

[Mohit Sindhwani]

On 27/12/2014 12:13 AM, Teg wrote:

Because AUTOINCREMENT keyword changes the behavior of the ROWID
selection algorithm, AUTOINCREMENT is not allowed on WITHOUT ROWID
tables or on any table column other than INTEGER PRIMARY KEY. Any
attempt to use AUTOINCREMENT on a WITHOUT ROWID table or on a column
other than the INTEGER PRIMARY KEY column results in an error.--

I think this comment would be clearer if the "without rowid" part was
pulled out and made absolute.



The AUTOINCREMENT keyword is not allowed on WITHOUT ROWID tables.
Because the AUTOINCREMENT keyword changes the behavior of the ROWID
selection algorithm, Any attempt to use AUTOINCREMENT on a column
other than the INTEGER PRIMARY KEY column results in an error.--



I think this misses the point that if you try to apply it on a WITHOUT 
ROWID table, you will get an error.  Here's my attempt at reqriting 
thetext...


The AUTOINCREMENT keyword changes the behavior of the ROWID selection 
algorithm and is only allowed on the INTEGER PRIMARY KEY column and   
Consequently, any attempt to use AUTOINCREMENT on WITHOUT ROWID tables 
or on a column other than the INTEGER PRIMARY KEY column results in an 
error.


Best Regards,
Mohit.


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

[sqlite] Without ROWID and Autoincrement question

[Teg]

Hello General,

I  was  going  to say I thought the docs were ambiguous but, on my 3rd
reading  I  see  they  aren't.  Still,  I think this statement could be
re-written to reduce confusion (at least mine).

https://www.sqlite.org/autoinc.html

>>Because AUTOINCREMENT keyword changes the behavior of the ROWID
>>selection algorithm, AUTOINCREMENT is not allowed on WITHOUT ROWID
>>tables or on any table column other than INTEGER PRIMARY KEY. Any
>>attempt to use AUTOINCREMENT on a WITHOUT ROWID table or on a column
>>other than the INTEGER PRIMARY KEY column results in an error.--

I think this comment would be clearer if the "without rowid" part was
pulled out and made absolute.


>>The AUTOINCREMENT keyword is not allowed on WITHOUT ROWID tables.
>>Because the AUTOINCREMENT keyword changes the behavior of the ROWID
>>selection algorithm, Any attempt to use AUTOINCREMENT on a column
>>other than the INTEGER PRIMARY KEY column results in an error.--



Best regards,
 Conrad  mailto:t...@djii.com

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

Re: [sqlite] WITHOUT ROWID option

[Simon Slavin]

On 9 May 2014, at 11:17am, Sky Meena  wrote:

> i need to transfer sqlite.db from server to client in udp socket

Please start a new thread about this.  It has nothing to do with 'WITHOUT ROWID 
option'.

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

Re: [sqlite] WITHOUT ROWID option

[Hick Gunter]

Judging from the documentation there is not (optional methods may have a NULL 
pointer in the method table). Maybe this will/has change(d)

2.12 The xRowid Method
  int (*xRowid)(sqlite3_vtab_cursor *pCur, sqlite_int64 *pRowid);
A successful invocation of this method will cause *pRowid to be filled with the 
rowid of row that the virtual table cursor pCur is currently pointing at. This 
method returns SQLITE_OK on success. It returns an appropriate error code on 
failure.

The xRowid method is required for every virtual table implementation.


-Ursprüngliche Nachricht-
Von: Max Vlasov [mailto:max.vla...@gmail.com]
Gesendet: Freitag, 09. Mai 2014 12:10
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] WITHOUT ROWID option

On Wed, May 7, 2014 at 6:31 PM, Richard Hipp <d...@sqlite.org> wrote:
> On Wed, May 7, 2014 at 9:00 AM, Marco Bambini <ma...@sqlabs.net> wrote:
>
>> What is the best way to know if a table has been created with the
>> WITHOUT ROWID option?
>>
>
>
> (1) You could send "SELECT rowid FROM table" 
>
> (2) Run both "PRAGMA index_list(table)" 
>
>

Is there a way for a virtual table implementation to report that there's no 
rowid support before first xRowId call takes place?

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


---
Gunter Hick
Software Engineer

Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna,
Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then
delete this message from your system. Please do not copy it or use it for any 
purposes, or disclose its contents to any person as to do so could be a breach 
of confidence. Thank you for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WITHOUT ROWID option

[Sky Meena]

i need to transfer sqlite.db from server to client in udp socket.. working
in c language.. .. i dont know how to transmit db. in client side it
should create copy of db.
if client ask name of db to server . server should transmit..


On Fri, May 9, 2014 at 3:09 PM, Stephan Beal  wrote:

> On Fri, May 9, 2014 at 11:38 AM, Stephan Beal 
> wrote:
>
> > i don't think a WHERE is necessary to improve the efficiency. The
> > statement only gets prepared, not stepped, and i would not expect any
> > analysis of results until the first step() (but maybe i'm assuming too
> > much).
> >
>
> In fact, a WHERE, if it's not optimized out, might even add memory cost for
> the tokens required for parsing it.
>
>
> --
> - stephan beal
> http://wanderinghorse.net/home/stephan/
> http://gplus.to/sgbeal
> "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
> those who insist on a perfect world, freedom will have to do." -- Bigby
> Wolf
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WITHOUT ROWID option

[Max Vlasov]

On Wed, May 7, 2014 at 6:31 PM, Richard Hipp  wrote:
> On Wed, May 7, 2014 at 9:00 AM, Marco Bambini  wrote:
>
>> What is the best way to know if a table has been created with the WITHOUT
>> ROWID option?
>>
>
>
> (1) You could send "SELECT rowid FROM table" 
>
> (2) Run both "PRAGMA index_list(table)" 
>
>

Is there a way for a virtual table implementation to report that
there's no rowid support before first xRowId call takes place?

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

Re: [sqlite] WITHOUT ROWID option

[Stephan Beal]

On Fri, May 9, 2014 at 11:38 AM, Stephan Beal  wrote:

> i don't think a WHERE is necessary to improve the efficiency. The
> statement only gets prepared, not stepped, and i would not expect any
> analysis of results until the first step() (but maybe i'm assuming too
> much).
>

In fact, a WHERE, if it's not optimized out, might even add memory cost for
the tokens required for parsing it.


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WITHOUT ROWID option

[Stephan Beal]

On Fri, May 9, 2014 at 8:36 AM, Andy Goth  wrote:

> On 5/8/2014 10:11 AM, Jim Morris wrote:
>
>> To improve efficiency you could add "where 1=2" to avoid returning any
>> rows.  Should just check validity.
>>
>
> This being SQLite, as previously discussed, you could say "where 0" :^)


i don't think a WHERE is necessary to improve the efficiency. The statement
only gets prepared, not stepped, and i would not expect any analysis of
results until the first step() (but maybe i'm assuming too much).

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WITHOUT ROWID option

[Andy Goth]

On 5/8/2014 10:11 AM, Jim Morris wrote:

To improve efficiency you could add "where 1=2" to avoid returning any
rows.  Should just check validity.


This being SQLite, as previously discussed, you could say "where 0" :^)

--
Andy Goth | 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WITHOUT ROWID option

[Jim Morris]

To improve efficiency you could add "where 1=2" to avoid returning any 
rows.  Should just check validity.


On 5/7/2014 8:19 AM, Stephan Beal wrote:

On Wed, May 7, 2014 at 4:57 PM, Simon Slavin  wrote:


somehow ?  Perhaps the ROWID field of a table might have its own
particular indication, and if you don't see any rows marked like that you
could deduce that the table had no ROWID column.  I'm sure there are better
ways the


This isn't efficient, but it should work without corner cases: (pseudocode):


function hasRowId(tablename) {
   prepare SELECT 1 FROM tablename; // if this fails, tablename likely does
not exist. else...
   prepare SELECT rowid FROM tablename; // if this fails, rowid missing
   return true only if the second PREPARE succeeds.
}




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

Re: [sqlite] WITHOUT ROWID option

[Stephan Beal]

On Wed, May 7, 2014 at 4:57 PM, Simon Slavin  wrote:

> somehow ?  Perhaps the ROWID field of a table might have its own
> particular indication, and if you don't see any rows marked like that you
> could deduce that the table had no ROWID column.  I'm sure there are better
> ways the
>

This isn't efficient, but it should work without corner cases: (pseudocode):


function hasRowId(tablename) {
  prepare SELECT 1 FROM tablename; // if this fails, tablename likely does
not exist. else...
  prepare SELECT rowid FROM tablename; // if this fails, rowid missing
  return true only if the second PREPARE succeeds.
}


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WITHOUT ROWID option

[Simon Slavin]

On 7 May 2014, at 3:31pm, Richard Hipp  wrote:

> (2) Run both "PRAGMA index_list(table)" and "SELECT name FROM sqlite_master
> WHERE tbl_name='table'".  If the PRAGMA mentions an
> "sqlite_autoindex_table_1" which is not mentioned by the SELECT, then you
> have a WITHOUT ROWID table.  This approach is more complex, but never
> fails, afaik.

Might it be possible in SQLite4 to deduce this information from the output of

PRAGMA table_info(table-name)

somehow ?  Perhaps the ROWID field of a table might have its own particular 
indication, and if you don't see any rows marked like that you could deduce 
that the table had no ROWID column.  I'm sure there are better ways the dev 
team could think up.

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

Re: [sqlite] WITHOUT ROWID option

[Marco Bambini]

Thanks a lot Richard, I really appreciate.

--
Marco Bambini
http://www.sqlabs.com
http://twitter.com/sqlabs
http://instagram.com/sqlabs



On 07 May 2014, at 16:31, Richard Hipp  wrote:

> On Wed, May 7, 2014 at 9:00 AM, Marco Bambini  wrote:
> 
>> What is the best way to know if a table has been created with the WITHOUT
>> ROWID option?
>> 
> 
> 
> (1) You could send "SELECT rowid FROM table" to sqlite3_prepare() and see
> if it returns an error.  This might fail on a table like "CREATE TABLE
> xyz(rowid TEXT PRIMARY KEY)WITHOUT ROWID".
> 
> (2) Run both "PRAGMA index_list(table)" and "SELECT name FROM sqlite_master
> WHERE tbl_name='table'".  If the PRAGMA mentions an
> "sqlite_autoindex_table_1" which is not mentioned by the SELECT, then you
> have a WITHOUT ROWID table.  This approach is more complex, but never
> fails, afaik.
> 
> 
>> --
>> Marco Bambini
>> http://www.sqlabs.com
>> http://twitter.com/sqlabs
>> http://instagram.com/sqlabs
>> 
>> 
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> 
> 
> 
> 
> -- 
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] WITHOUT ROWID option

[Richard Hipp]

On Wed, May 7, 2014 at 9:00 AM, Marco Bambini  wrote:

> What is the best way to know if a table has been created with the WITHOUT
> ROWID option?
>


(1) You could send "SELECT rowid FROM table" to sqlite3_prepare() and see
if it returns an error.  This might fail on a table like "CREATE TABLE
xyz(rowid TEXT PRIMARY KEY)WITHOUT ROWID".

(2) Run both "PRAGMA index_list(table)" and "SELECT name FROM sqlite_master
WHERE tbl_name='table'".  If the PRAGMA mentions an
"sqlite_autoindex_table_1" which is not mentioned by the SELECT, then you
have a WITHOUT ROWID table.  This approach is more complex, but never
fails, afaik.


> --
> Marco Bambini
> http://www.sqlabs.com
> http://twitter.com/sqlabs
> http://instagram.com/sqlabs
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WITHOUT ROWID option

[Marco Bambini]

So, is there an official recommended way? or that check should require a manual 
sql parsing?
--
Marco Bambini
http://www.sqlabs.com
http://twitter.com/sqlabs
http://instagram.com/sqlabs



On 07 May 2014, at 15:51, Igor Tandetnik  wrote:

> On 5/7/2014 9:40 AM, RSmith wrote:
>> SELECT instr(upper(sql),'WITHOUT ROWID')>1 FROM sqlite_master WHERE
>> type='table' AND tbl_name='YourTableName'
>> 
>> Returns 1 for tables made without rowid, 0 for the rest.
> 
> CREATE TABLE t(x text default 'WITHOUT ROWID');
> 
> -- 
> Igor Tandetnik
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] WITHOUT ROWID option

[RSmith]

...or indeed this malicious-but-valid table-schema design would prove 
problematic.

Other that might cause similar headaches are:

CREATE TABLE t(x text // )WITHOUT ROWID;
);

or

CREATE TABLE t(x text);  // )WITHOUT ROWID;

etc.

It would require a rather convoluted check to be very sure but if you do not expect maliciously designed table schemas, this 
should not be a problem - and if you do, I suggest checking in your code after getting the sql schema and doing some minor parsing.



On 2014/05/07 15:51, Igor Tandetnik wrote:

On 5/7/2014 9:40 AM, RSmith wrote:

SELECT instr(upper(sql),'WITHOUT ROWID')>1 FROM sqlite_master WHERE
type='table' AND tbl_name='YourTableName'

Returns 1 for tables made without rowid, 0 for the rest.


CREATE TABLE t(x text default 'WITHOUT ROWID');



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

Re: [sqlite] WITHOUT ROWID option

[RSmith]

Apologies, my answer reads more like a "How to" than a "What is the best way" type answer, so to just elaborate on the brevity - 
SQLite stores no special pointer or memory or setting or even file value anywhere that can give you any clue apart from the actual 
words "Without RowID" which are found only in the Schema of the Table and mostly only at the end of it (barring added comments etc).


Hence my quick-draw solution hereunder is not so much the "best" way as it is the "only" way. Further to this, it is conceivable 
that a comment within the table definition might contain the words "WITHOUT" and "ROWID" together, so a more robust solution would 
probably be to ensure it follows the final right-brace.


Hope that answers the question more directly.


On 2014/05/07 15:40, RSmith wrote:

SELECT instr(upper(sql),'WITHOUT ROWID')>1 FROM sqlite_master WHERE 
type='table' AND tbl_name='YourTableName'

Returns 1 for tables made without rowid, 0 for the rest.


On 2014/05/07 15:00, Marco Bambini wrote:

What is the best way to know if a table has been created with the WITHOUT ROWID 
option?
--
Marco Bambini
http://www.sqlabs.com
http://twitter.com/sqlabs
http://instagram.com/sqlabs



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


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


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

Re: [sqlite] WITHOUT ROWID option

[Igor Tandetnik]

On 5/7/2014 9:40 AM, RSmith wrote:

SELECT instr(upper(sql),'WITHOUT ROWID')>1 FROM sqlite_master WHERE
type='table' AND tbl_name='YourTableName'

Returns 1 for tables made without rowid, 0 for the rest.


CREATE TABLE t(x text default 'WITHOUT ROWID');

--
Igor Tandetnik

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

Re: [sqlite] WITHOUT ROWID option

[RSmith]

SELECT instr(upper(sql),'WITHOUT ROWID')>1 FROM sqlite_master WHERE 
type='table' AND tbl_name='YourTableName'

Returns 1 for tables made without rowid, 0 for the rest.


On 2014/05/07 15:00, Marco Bambini wrote:

What is the best way to know if a table has been created with the WITHOUT ROWID 
option?
--
Marco Bambini
http://www.sqlabs.com
http://twitter.com/sqlabs
http://instagram.com/sqlabs



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


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

[sqlite] WITHOUT ROWID option

[Marco Bambini]

What is the best way to know if a table has been created with the WITHOUT ROWID 
option?
--
Marco Bambini
http://www.sqlabs.com
http://twitter.com/sqlabs
http://instagram.com/sqlabs



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

Re: [sqlite] WITHOUT ROWID problem and large number of row updating

[James K. Lowden]

On Wed, 16 Apr 2014 10:24:51 -0700
Joseph Yesselman  wrote:

> sql = "CREATE TABLE two_way("   \
>   "id VARCHAR(50) NOT NULL,"  \
>   "names VARCHAR(2000),"  \
>   "rs VARCHAR(3000),"  \
>   "ds VARCHAR(2000)," \
>   "sugs VARCHAR(2000), " \
>   "PRIMARY KEY (id));";
...
> if names is currently "motif_1,motif_2;motif_5,motif6"
> and the new data I have is "motif_10,motif_11", after the update i
> need names to be:
> "motif_1,motif_2;motif_5,motif6;motif_10,motif_11"
...
> Is there a way to do this for a large number of rows in a single
> transaction, since I am currently doing it for one row per
> transaction and its very very slow.

Three words: first normal form.  

I guess, from your descripion of the data, that you've packed 4 or 5
tables into one.  To nomalize it, you would made a bunch of tables in
the form

create table two_way_names 
( id VARCHAR(50) NOT NULL -- (integer would be better ...)
, name VARCHAR(12)
, primary key (id, name)
);

where name is a single name instead of a list.  Then, the updates would
be very fast, and you'd need one update per table instead of per row.  

Contrary to a popular misconception, normal forms were invented -- or
perhaps discovered -- in part *for* efficiency.  By minimizing
redunancy, you minimize what needs to be searched and updated.  

if you take my advice, you'll also confront another question, namely,
what is the meaning, if any, implicit in the order of the existing
column two_way.names?  Does 'motif_11' "align" to anything in the
other columns, or is it just another name belonging to id?  

If the order has implicit meaning -- if 'motif_11' relates to some
specific position in the list in another column -- then that column
becomes part of the new table, but holding only a single value per
row.  If the order is meaningless, your existing table can be
reconstituted using string concatenation and JOIN.  

HTH.  

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

[sqlite] WITHOUT ROWID problem and large number of row updating

[Joseph Yesselman]

Hi All,

I have two problems, the first one is probably very simple but I could not
find anything online telling me how to fix it. I would like to use the
WITHOUT ROWID statement as my primary key is a string in the format of 6
coordinates.

example:
"0.0 1.0 3.0 1.6 2.4 0.0"

I am using c++, but not using a wrapper even if I try the example given on
the website

char * sql = "CREATE TABLE IF NOT EXISTS wordcount(word TEXT PRIMARY KEY,
cnt INTEGER) WITHOUT ROWID;";

rc = sqlite3_exec(db, sql, callback, 0, );
if( rc != SQLITE_OK ){
  fprintf(stderr, "SQL error: %s\n", zErrMsg);
  sqlite3_free(zErrMsg);
}else{
  fprintf(stdout, "Table created successfully\n");
}

I get a SQL error near WITHOUT. I have tried going into sqlite3 and
creating the same table there without c++ and it works fine and I have
downloaded the most recent version of the c++ files from the website so I
really have no idea. I have created other tables just fine using the same
method.

My other problem is maybe more a of a methodological problem but I will
throw it out there. I am building a database of approximately 50-100gb of
data per c++ program run, after each round (~500 rounds) in my program I
need to go back and update my sqlite database, most of the data will be new
rows but still many previous rows will need to be updated. I am not sure
how efficiently update a large number of rows at once.

Here is my table definition:

sql = "CREATE TABLE two_way("   \
  "id VARCHAR(50) NOT NULL,"  \
  "names VARCHAR(2000),"  \
  "rs VARCHAR(3000),"  \
  "ds VARCHAR(2000)," \
  "sugs VARCHAR(2000), " \
  "PRIMARY KEY (id));";

When I need to update a row I need to take the previous values from
names,rs,ds and sugs and add the new values I have to the end of the
strings seperated by a ;.

example:
if names is currently "motif_1,motif_2;motif_5,motif6"
and the new data I have is "motif_10,motif_11", after the update i need
names to be:
"motif_1,motif_2;motif_5,motif6;motif_10,motif_11"

This same thing needs to be done for names,rs,ds and sugs.
*So sorry my questions is:*
Is there a way to do this for a large number of rows in a single
transaction, since I am currently doing it for one row per transaction and
its very very slow.

Thanks a lot,
Joe
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users