Re: [sqlite] WITHOUT ROWID option
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
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 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 --- 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
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
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
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
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
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
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
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
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
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
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
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
...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
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
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
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