Re: [sqlite] Re: AUTOINCREMENT Functionality by default required in 3.0.8 (Is it possible?)
Hi John You are completely correct, it was just a hasty example to show that triggers may solve teh problem. If you want a unique variable you probably have to use a 1 row table as an index counter. % cat test.sql -- example showing how to use an increment unique value with sqlite .header on create table test ( num integer, num2integer, infotext, primary key (num) ); -- one value index database for unique indexnumber create table myindex ( id integer, num integer, primary key (id) ); -- trigger which increases num2 with a unique index create trigger increment_sum_in_test after insert on test begin update myindex set num = num+1 where id=0; update test set num2 = ( select num from myindex where id=0 ) where num=new.num; end; -- first set up myindex so the trigger wont complain insert into myindex (id,num) values (0,0); insert into test (num,info) values (null,'number one'); insert into test (num,info) values (null,'number two'); insert into test (num,info) values (null,'number three'); delete from test where info='number three'; delete from test where info='number one'; insert into test (num,info) values (null,'number four'); insert into test (num,info) values (null,'number five'); select num,info,num2 from test; $ sqlite3 text.db < test.sql num|info|num2 2|number two|2 3|number four|4 4|number five|5 -- MortenB --- John LeSueur <[EMAIL PROTECTED]> skrev: > what happens If you do: > > delete from test where num = 2; > insert into test (num, info) values(null, 'number > four'); > > Wouldn't you have: > > num|info|num2 > 1|number one|1 > 3|number three|3 > 3|number four|3 > > John LeSueur > >
Re: [sqlite] Re: AUTOINCREMENT Functionality by default required in 3.0.8 (Is it possible?)
[EMAIL PROTECTED] wrote: Hi Morten, Thanks for the help. I got the idea for doing this based on the trigger. I shall implement the same. Thanks once again for the help. Regards, Sankara Narayanan B morten bjoernsvik <[EMAIL PROTECTED]> 02/17/2005 03:06 PM Please respond to sqlite-users@sqlite.org To sqlite-users@sqlite.org cc Subject Re: [sqlite] Re: AUTOINCREMENT Functionality by default required in 3.0.8 (Is it possible?) Hi You can easily fix this trick with the "insert null to and integer primary key" trick or by using a trigger. Use a reference value you either increase or decrease, or count to set for new values. % cat /data/temp/test.sql .header on create table test ( num integer, num2integer, infotext, primary key (num) ); create trigger total_num_for_test after insert on test begin update test set num2 = ( select count(*) from test ) where num=new.num; end; insert into test (num,info) values (null,'number one'); insert into test (num,info) values (null,'number two'); insert into test (num,info) values (null,'number three'); select num,info,num2 from test; % sqlite3 test.db < /data/temp/test.sql num|info|num2 1|number one|1 2|number two|2 3|number three|3 % -- MortenB --- [EMAIL PROTECTED] skrev: Hi, A soft reminder. Please inform if the below mentioned request is a possibility? Regards, Sankara Narayanan. what happens If you do: delete from test where num = 2; insert into test (num, info) values(null, 'number four'); Wouldn't you have: num|info|num2 1|number one|1 3|number three|3 3|number four|3 John LeSueur
Re: [sqlite] Re: AUTOINCREMENT Functionality by default required in 3.0.8 (Is it possible?)
Hi You can easily fix this trick with the "insert null to and integer primary key" trick or by using a trigger. Use a reference value you either increase or decrease, or count to set for new values. % cat /data/temp/test.sql .header on create table test ( num integer, num2integer, infotext, primary key (num) ); create trigger total_num_for_test after insert on test begin update test set num2 = ( select count(*) from test ) where num=new.num; end; insert into test (num,info) values (null,'number one'); insert into test (num,info) values (null,'number two'); insert into test (num,info) values (null,'number three'); select num,info,num2 from test; % sqlite3 test.db < /data/temp/test.sql num|info|num2 1|number one|1 2|number two|2 3|number three|3 % -- MortenB --- [EMAIL PROTECTED] skrev: > Hi, > > A soft reminder. Please inform if the below > mentioned request is a > possibility? > > Regards, > Sankara Narayanan. >
Re: [sqlite] Re: AUTOINCREMENT Functionality by default required in 3.0.8 (Is it possible?)
Didn't SQLite provide commercial support somewhere..? I'm sure this is not an open source issue, since no-one dealing there would "not have time to upgrade". Funny, I'm sad for your timetables. -ak 17.2.2005 kello 10:02, [EMAIL PROTECTED] kirjoitti: Hi, A soft reminder. Please inform if the below mentioned request is a possibility? Regards, Sankara Narayanan. Sankara Narayanan/BTC/PIN/PHILIPS 02/14/2005 05:40 PM To SQLITE cc Subject AUTOINCREMENT Functionality by default required in 3.0.8 Hi, I am currently using Sqlite3.0.8 in our application. We have a functional requirement wherein we need for our tables to have unique Primary key value irrespective of deletions/additions. To be in detail, even if i delete the last row in a table, the subsequent addition should not reuse the integer primary key of the last deleted row. I find that this functionality is available in Sqlite 3.1 by defining AUTOINCREMENT for the primary key column. But porting to 3.1.1 is a tedious task the sqlite database engine is ported onto an ARM 7 platform and I dont have time to perform port to 3.1. I request details of what code needs to be modified so that I get this AUTOINCREMENT functionality implementation as a default functionality in 3.0.8. i.e. for all tables in my database running on the modified Sqlite engine, I require that the integer primary key is never reused. Please help in this regard. Thanks in advance, With regards, Sankara Narayanan Balasubramanian.
