Re: [sqlite] Producing SQLITE_MISMATCH

[J. King]

On 2017-03-09 09:59:25, "Dan Kennedy"  wrote:


On 03/09/2017 09:39 PM, J. King wrote:
Out of curiosity, can you provide some insight as to why it does not 
produce a mismatch for a WITHOUT ROWID table?


The main b-tree in which data for a "rowid table" - any table that is 
not a WITHOUT ROWID table or a virtual table - is stored uses 64-bit 
integers for keys. If you declare a column "INTEGER PRIMARY KEY", it 
becomes an alias for this underlying integer key. So in this case the 
field must be set to an integer value - SQLite could not store it 
otherwise.


With a WITHOUT ROWID table, the main b-tree used to store data is more 
flexible - it can have any type of key. So in this case there is no 
restriction.


https://www.sqlite.org/fileformat.html#representation_of_sql_tables

and:

  https://www.sqlite.org/rowidtable.html
  https://www.sqlite.org/withoutrowid.html


Again, thank you.  I was completely misreading the documentation with 
regard to WITHOUT ROWID. Now a) it makes much more sense and b) I 
understand when it might actually come in handy.


--
J. King

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

Re: [sqlite] Producing SQLITE_MISMATCH

[Dan Kennedy]

On 03/09/2017 09:39 PM, J. King wrote:

On 2017-03-09 09:35:00, "Dan Kennedy"  wrote:


On 03/09/2017 09:23 PM, J. King wrote:
I'm trying to write a minimal test case to produce an 
SQLITE_MISMATCH response.  The documentation suggests the following 
should be sufficient:


CREATE TABLE test(id integer primary key) WITHOUT ROWID;
INSERT INTO test(id) VALUES('ook');

However, SQLite happily accepts the second statement. Is there some 
trick to it that's not obvious?


Leave out the WITHOUT ROWID.

Dan.


Ah, exactly what I needed.  Thanks!

Out of curiosity, can you provide some insight as to why it does not 
produce a mismatch for a WITHOUT ROWID table?


The main b-tree in which data for a "rowid table" - any table that is 
not a WITHOUT ROWID table or a virtual table - is stored uses 64-bit 
integers for keys. If you declare a column "INTEGER PRIMARY KEY", it 
becomes an alias for this underlying integer key. So in this case the 
field must be set to an integer value - SQLite could not store it otherwise.


With a WITHOUT ROWID table, the main b-tree used to store data is more 
flexible - it can have any type of key. So in this case there is no 
restriction.


https://www.sqlite.org/fileformat.html#representation_of_sql_tables

and:

  https://www.sqlite.org/rowidtable.html
  https://www.sqlite.org/withoutrowid.html

Dan.





(apologies if my messages come out looking yucky; I'm still looking a 
mail client that isn't awful dealing with mailing lists. :( )


--
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] Producing SQLITE_MISMATCH

[Dan Kennedy]

On 03/09/2017 09:23 PM, J. King wrote:
I'm trying to write a minimal test case to produce an SQLITE_MISMATCH 
response.  The documentation suggests the following should be sufficient:


CREATE TABLE test(id integer primary key) WITHOUT ROWID;
INSERT INTO test(id) VALUES('ook');

However, SQLite happily accepts the second statement. Is there some 
trick to it that's not obvious?


Leave out the WITHOUT ROWID.

Dan.


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

Re: [sqlite] Producing SQLITE_MISMATCH

[J. King]

On 2017-03-09 09:35:00, "Dan Kennedy"  wrote:


On 03/09/2017 09:23 PM, J. King wrote:
I'm trying to write a minimal test case to produce an SQLITE_MISMATCH 
response.  The documentation suggests the following should be 
sufficient:


CREATE TABLE test(id integer primary key) WITHOUT ROWID;
INSERT INTO test(id) VALUES('ook');

However, SQLite happily accepts the second statement. Is there some 
trick to it that's not obvious?


Leave out the WITHOUT ROWID.

Dan.


Ah, exactly what I needed.  Thanks!

Out of curiosity, can you provide some insight as to why it does not 
produce a mismatch for a WITHOUT ROWID table?


(apologies if my messages come out looking yucky; I'm still looking a 
mail client that isn't awful dealing with mailing lists. :( )


--
J. King

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