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