[sqlite] Auto Increment of Integer Primary Key
Hi,
I am working with sqlite 3.3.6 version.
I have defined the macro SQLITE_OMIT_FLOATING_POINT.
1.
this is my test program
"create table Test(id integer primary key,player char);"
"insert into Test(id,player) values(2,'surya');"
"insert into Test(id,player) values(9223372036854775807,'sree');"
"insert into Test(player) values('sree1');"
A random number is choosen for the player sree1.
What i need is i want to fix the maximum limit to say 64k.
So that it will be easy for me for maintaining the records. is there any
possibility of setting the maximum limit.
I tried changing the value of SQLITE_BIG_DBL it did not worked.
2.
My second doubt.
"create table Test(id integer primary key,player char);"
"insert into Test(id,player) values(2,'surya');"
"insert into Test(id,player) values(9223372036854775807,'sree');"
"insert into Test(id,player) values(9223372036854775808,'sree1');"
"select * from Test;"
The result is
-9223372036854775808sree1
2surya
9223372036854775807sree
I tried inserting 2 power 63 value but the database has converted it to -2
power 63 and stored.
Can anyone explain why this has happened.
Best Regards,
A.Sreedhar.
Jasmin Infotech Pvt. Ltd.
Plot 119, Velachery Tambaram Road,
(Opposite NIOT), Pallikaranai,
Chennai 601 302
India
Tel: +91 44 3061 9600 ext 3057
Fax: + 91 44 3061 9605
***
Information in this email is proprietary and Confidential to
Jasmin Infotech. Any use, copying or dissemination of the
information in any manner is strictly prohibited. If you are
not the intended recipient, please destroy the message and please inform us.
Re: [sqlite] Auto Increment?
> Quoting Dennis Cote ([EMAIL PROTECTED]): > > Doesn't this mean that SQLite only supports 2^63 rows with autoincrement? > > That means you can insert one row per millisecond for 29 million years. Well actually, not quite. The website states that the database size is limited to 2^41 bytes. http://www.sqlite.org/faq.html You need 8 bytes to store the autoincrement field, and let's say you store another 8 bytes of information. So now you've only got 2^37 records minus database overheads, but I don't know how much that is. So you only get about 4 years at 1 record per millisecond! The bigger problem with regard to the initial request is that when records are deleted, the autoincrement field just keeps going up (for 29 million years for argument sake). So really, if you **have** to have 32 bits of Unique ID, then maybe you need to find some other method to manage it. But, as all the prior discussion indicates, it depends on how many updates and for how long. Do the maths, find out how many bits you need. If you're not going to generate more than 2^32 records over the life of the application, then use the inbuilt AUTOINCREMENT field, and don't worry about it. Regards, Carl.
Re: [sqlite] Auto Increment?
Quoting Dennis Cote ([EMAIL PROTECTED]): > Doesn't this mean that SQLite only supports 2^63 rows with autoincrement? That means you can insert one row per millisecond for 29 million years. -- Paul Tomblin <[EMAIL PROTECTED]> http://xcski.com/blogs/pt/ In any business, the customer is always right, except when he calls technical support.
Re: [sqlite] Auto Increment?
[EMAIL PROTECTED] wrote: The rowid does *not* wrap if you specify AUTOINCREMENT. Once the maximum rowid is used, all subsequent insert attempts return SQLITE_FULL. The regression test suite contains a test for this. Different rules apply if you do not use AUTOINCREMENT. There is a #define that may interest the original poster. If you compile with -DSQLITE_32BIT_ROWID=1, then rowids are limited to 32 bits. This feature is not particularly well tested (actually, I'm not sure it is tested at all) but it might work. It is worth a try, I suppose. Doesn't this mean that SQLite only supports 2^63 rows with autoincrement? If I create a table with a with an autoincrement rowid, and then insert a NULL id it assigns a rowid of 1. And if I try to initialize the rowid to a large negative number (I used 2^63 +1 to avoid the largest negative value) in order to use the other 2^63 rowids it lets me insert that one row, but then automatically assigns the next row an id of 1. sqlite> create table t2(i integer primary key autoincrement, d); sqlite> insert into t2 values((1<<63) + 1, 1); sqlite> insert into t2 values(NULL, 2); sqlite> select * from t2; -9223372036854775807|1 1|2 sqlite> insert into t2 values((1<<63) -1, 3); sqlite> select * from t2; -9223372036854775807|1 1|2 9223372036854775807|3 sqlite> insert into t2 values(NULL, 4); SQL error: database or disk is full sqlite> It won't autoincrement through the negative half of the address space. I don't think this is a real problem because of the immense size of the address space, but someone might. Also, does it behave the same with 32 bit values (i.e. only on half the space or 2^31 rows are usable)? This might be more of a practical problem for someone, but they could always switch to 64 (or 63) values if it was. Dennis Cote
Re: [sqlite] Auto Increment?
Dennis Cote <[EMAIL PROTECTED]> wrote: > [EMAIL PROTECTED] wrote: > > > > > > >Hmmm... In the later versions of sqlite with 64-bit ROWID values, doesn't it > >treat them as unsigned? It sure seems that autoincremented rowid values > >should always be positive...??? > > > > > > > No, SQLite treats them as 64 bit signed integers. The first 2^63 values > are positive, the next 2^63 values are negative. The problem I was > alluding to is, that the code that is extracting the range limited > values from SQLite into its own 32 integer values may treat those values > as signed 32 bit integers which will wrap to negative values after 2^31 > rows are inserted. if you are sorting by this id number, suddenly the > newest row has the lowest id not the largest id. SQLite itself will have > the same problem but only after 2^63 rows are inserted (and we don't > have enough time to wait for that to ever happen, its just too large a > number). > The rowid does *not* wrap if you specify AUTOINCREMENT. Once the maximum rowid is used, all subsequent insert attempts return SQLITE_FULL. The regression test suite contains a test for this. Different rules apply if you do not use AUTOINCREMENT. There is a #define that may interest the original poster. If you compile with -DSQLITE_32BIT_ROWID=1, then rowids are limited to 32 bits. This feature is not particularly well tested (actually, I'm not sure it is tested at all) but it might work. It is worth a try, I suppose. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Auto Increment?
Jim C. Nasby wrote: On Tue, Jan 31, 2006 at 10:05:47AM -0700, Dennis Cote wrote: [EMAIL PROTECTED] wrote: CREATE TABLE x(i INTEGER PRIMARY KEY AUTOINCREMENT CHECK(i < (1<<32))); I suspect you'll see better performance if you hard-code the value instead of doing a bit-shift every time you insert. Jim, You are correct, at least until Richard finishes his constant subexpression folding optimizations. :-) Dennis Cote
Re: [sqlite] Auto Increment?
On Tue, Jan 31, 2006 at 10:05:47AM -0700, Dennis Cote wrote: > [EMAIL PROTECTED] wrote: > CREATE TABLE x(i INTEGER PRIMARY KEY AUTOINCREMENT CHECK(i < (1<<32))); I suspect you'll see better performance if you hard-code the value instead of doing a bit-shift every time you insert. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] Auto Increment?
Dennis Cote <[EMAIL PROTECTED]> writes: > Derrell, > > If you are using SQLite 3.3.0 or newer then you can do the same thing in a > more direct manner using a CHECK constraint. > > CREATE TABLE x(i INTEGER PRIMARY KEY AUTOINCREMENT CHECK(i < (1<<32))); Hehe. I'm using 2.8.16 for most of my work, so I don't even have the AUTOINCREMENT keyword available, let alone CHECK. > Also, if you are concerned about signed vs unsigned interpretation of the 32 > bit value you may want to change the maximum to a 31 bit shift which will > restrict i to values that are always positive signed values. Hmmm... In the later versions of sqlite with 64-bit ROWID values, doesn't it treat them as unsigned? It sure seems that autoincremented rowid values should always be positive...??? Cheers, Derrell
Re: [sqlite] Auto Increment?
[EMAIL PROTECTED] wrote: chetana bhargav <[EMAIL PROTECTED]> writes: Auto increment seems to return a unsigned long long is there any way for it to make it as 32 bit, as I am depending on this feilds to generate unique id, and i have a constraint fot the id to be 32 bit only. You'll have to add enough rows to the table to use up all id values that fit in 32 bits before you'll have a problem. You can, however, protect from wrap- around with something like this: CREATE TABLE x(i INTEGER PRIMARY KEY AUTOINCREMENT); CREATE TRIGGER x_insert_tr AFTER INSERT ON x FOR EACH ROW BEGIN SELECT CASE WHEN new.i >= (1<<32) THEN RAISE(ROLLBACK, 'The table is full.') ELSE NULL END; END; Derrell Derrell, If you are using SQLite 3.3.0 or newer then you can do the same thing in a more direct manner using a CHECK constraint. CREATE TABLE x(i INTEGER PRIMARY KEY AUTOINCREMENT CHECK(i < (1<<32))); Also, if you are concerned about signed vs unsigned interpretation of the 32 bit value you may want to change the maximum to a 31 bit shift which will restrict i to values that are always positive signed values. HTH Dennis Cote
Re: [sqlite] Auto Increment?
chetana bhargav <[EMAIL PROTECTED]> writes: > Auto increment seems to return a unsigned long long is there any way for it > to make it as 32 bit, as I am depending on this feilds to generate unique > id, and i have a constraint fot the id to be 32 bit only. You'll have to add enough rows to the table to use up all id values that fit in 32 bits before you'll have a problem. You can, however, protect from wrap- around with something like this: CREATE TABLE x(i INTEGER PRIMARY KEY AUTOINCREMENT); CREATE TRIGGER x_insert_tr AFTER INSERT ON x FOR EACH ROW BEGIN SELECT CASE WHEN new.i >= (1<<32) THEN RAISE(ROLLBACK, 'The table is full.') ELSE NULL END; END; Derrell
[sqlite] Auto Increment?
Auto increment seems to return a unsigned long long is there any way for it to make it as 32 bit, as I am depending on this feilds to generate unique id, and i have a constraint fot the id to be 32 bit only. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [sqlite] Auto Increment?
Clint Bailey wrote: Can you set up a field to auto-increment, and if so how? Details are in the fourth paragraph of: http://sqlite.org/lang_createtable.html Summary: create table tbl(fieldname integer primary key autoincrement, ...) HTH, Gerry
Re: [sqlite] Auto Increment?
http://www.sqlite.org/lang_createtable.html Regards. rayB |-+> | | Clint Bailey | | | <[EMAIL PROTECTED]| | | h.net> | | || | | 31/01/2006 14:24 | | | Please respond to| | | sqlite-users | | || |-+> >--| | | | To: sqlite-users@sqlite.org | | cc: | | Subject: [sqlite] Auto Increment? | >--| Can you set up a field to auto-increment, and if so how? ** PLEASE CONSIDER OUR ENVIRONMENT BEFORE PRINTING * *** Confidentiality and Privilege Notice *** This e-mail is intended only to be read or used by the addressee. It is confidential and may contain legally privileged information. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone, and you should destroy this message and kindly notify the sender by reply e-mail. Confidentiality and legal privilege are not waived or lost by reason of mistaken delivery to you. Qantas Airways Limited ABN 16 009 661 901 Visit Qantas online at http://qantas.com
[sqlite] Auto Increment?
Can you set up a field to auto-increment, and if so how?
Re: [sqlite] AUTO INCREMENT
Hi Kevin, You could also query the table or view but don't return any rows. If you set the option to get the column types in the resultset this should do what you want . AN example of what I am trying to say would help :) PRAGMA show_datatypes = ON; select COl3 from tablename where 0 = 1 Then in the array that is returned you should have the column type as used when the table was created. This may save you the trouble of working through the SQLtext. The where statement should result in no CPU time used for the query regards Greg O - Original Message - From: Kevin Waterson To: [EMAIL PROTECTED] Sent: Monday, October 20, 2003 10:59 AM Subject: Re: [sqlite] AUTO INCREMENT This one time, at band camp, "Ian VanDerPoel" <[EMAIL PROTECTED]> wrote: > You can find the info you want in the sqlite_master > table. There is some doco on it at the sqlite.org the website. I am not sure if the info is held anywhere else but > select * from sqlite_master where name = quotes; will return the sql used to build the table. you can parse the data type from that. yep, guess I will just write up a getFieldType function Thanks for your time. Kind regards Kevin -- __ (_ \ _) ) | / / _ ) / _ | / ___) / _ ) | | ( (/ / ( ( | |( (___ ( (/ / |_| \) \_||_| \) \) Kevin Waterson Port Macquarie, Australia - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] AUTO INCREMENT
This one time, at band camp, "Ian VanDerPoel" <[EMAIL PROTECTED]> wrote: > You can find the info you want in the sqlite_master > table. There is some doco on it at the sqlite.org the website. I am not sure if the > info is held anywhere else but > select * from sqlite_master where name = quotes; will return the sql used to build > the table. you can parse the data type from that. yep, guess I will just write up a getFieldType function Thanks for your time. Kind regards Kevin -- __ (_ \ _) ) | / / _ ) / _ | / ___) / _ ) | | ( (/ / ( ( | |( (___ ( (/ / |_| \) \_||_| \) \) Kevin Waterson Port Macquarie, Australia - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] AUTO INCREMENT
This one time, at band camp, Kevin Waterson <[EMAIL PROTECTED]> wrote: > OK, that sounds fine, but is it possible to determine the type of field so my script > will be able to add the quotes if it is of type INTEGER? I guess what I really need is something like MySQL's mysql_field_type() Kind regards Kevin -- __ (_ \ _) ) | / / _ ) / _ | / ___) / _ ) | | ( (/ / ( ( | |( (___ ( (/ / |_| \) \_||_| \) \) Kevin Waterson Port Macquarie, Australia - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] AUTO INCREMENT
I have a table that looks like this..
#
#SQLite Admin Structure Dump for table quotes
#
create table quotes(id INTEGER PRIMARY KEY, author varchar(50), quote TEXT, category
varchar(20))
I am trying to auto increment the id field with this query
INSERT INTO quotes (id, author, quote, category) VALUES ('NULL', 'blah', 'blah',
'blah')
instead, I get an error saying 'datatype mismatch'
I thought if I inserted a NULL into an INTEGER PRIMARY KEY it would auto
increment for me. Obviously I am doing something wrong/insane here.
Kind regards
Kevin
--
__
(_ \
_) )
| / / _ ) / _ | / ___) / _ )
| | ( (/ / ( ( | |( (___ ( (/ /
|_| \) \_||_| \) \)
Kevin Waterson
Port Macquarie, Australia
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
