Re: [sqlite] Regenerating ROWID...?
Thanks Simon, Time being I was looking for the same. Thanks again for the help. On 8/31/07, Simon Davies <[EMAIL PROTECTED]> wrote: > On 31/08/2007, Babu, Lokesh <[EMAIL PROTECTED]> wrote: > > I was trying the following piece of code (see below), > > > > The requirement here is very simple, I want the t_id field or ROWID > > field to be regenerated sequentially even after delete has been > > performed. > > > > Hi Lokesh, > > You can achieve this with a trigger as follows: > > SQLite version 3.4.2 > Enter ".help" for instructions > sqlite> > sqlite> create table testTbl( t_id integer, t_name text, t_desc text ); > sqlite> > sqlite> insert into testTbl values( 1, '111', 'd' ); > sqlite> insert into testTbl values( 2, '222', 'd2' ); > sqlite> insert into testTbl values( 3, '3', 'd3' ); > sqlite> insert into testTbl values( 4, '4', 'd4' ); > sqlite> insert into testTbl values( 5, '5', 'd5' ); > sqlite> create trigger testTblTrigger after DELETE on testTbl begin > ...> update testTbl set t_id=t_id-1 where t_id>old.t_id; > ...> end; > sqlite> > sqlite> > sqlite> select * from testTbl; > 1|111|d > 2|222|d2 > 3|3|d3 > 4|4|d4 > 5|5|d5 > sqlite> delete from testTbl where t_id=2; > sqlite> select * from testTbl; > 1|111|d > 2|3|d3 > 3|4|d4 > 4|5|d5 > sqlite> delete from testTbl where t_id>1 and t_id<4; > sqlite> select * from testTbl; > 1|111|d > 2|5|d5 > sqlite> > > Rgds, > Simon > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Regenerating ROWID...?
On 31/08/2007, Babu, Lokesh <[EMAIL PROTECTED]> wrote: > I was trying the following piece of code (see below), > > The requirement here is very simple, I want the t_id field or ROWID > field to be regenerated sequentially even after delete has been > performed. > Hi Lokesh, You can achieve this with a trigger as follows: SQLite version 3.4.2 Enter ".help" for instructions sqlite> sqlite> create table testTbl( t_id integer, t_name text, t_desc text ); sqlite> sqlite> insert into testTbl values( 1, '111', 'd' ); sqlite> insert into testTbl values( 2, '222', 'd2' ); sqlite> insert into testTbl values( 3, '3', 'd3' ); sqlite> insert into testTbl values( 4, '4', 'd4' ); sqlite> insert into testTbl values( 5, '5', 'd5' ); sqlite> create trigger testTblTrigger after DELETE on testTbl begin ...> update testTbl set t_id=t_id-1 where t_id>old.t_id; ...> end; sqlite> sqlite> sqlite> select * from testTbl; 1|111|d 2|222|d2 3|3|d3 4|4|d4 5|5|d5 sqlite> delete from testTbl where t_id=2; sqlite> select * from testTbl; 1|111|d 2|3|d3 3|4|d4 4|5|d5 sqlite> delete from testTbl where t_id>1 and t_id<4; sqlite> select * from testTbl; 1|111|d 2|5|d5 sqlite> Rgds, Simon - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Regenerating ROWID...?
I was trying the following piece of code (see below),
The requirement here is very simple, I want the t_id field or ROWID
field to be regenerated sequentially even after delete has been
performed.
In the below code, I have created a table with 3 fields, Inserted 1000
records, Deleted some middle records,
After deletion, I want the ROWID to be regenrated or I want to UPDATE
the t_id to regenerate the numbers from 0-N.
Please Note: I don't want to create a new table, I don't want to do
VACUUM (I'm working on In-Memory database).
Anyone knows...? Please reply...
Thanks in advance...
static const char *TestSqlStats [] = {
"CREATE TABLE testTbl (t_id INTEGER, t_name TEXT, t_desc TEXT);",
"INSERT INTO testTbl (t_id,t_name,t_desc) VALUES (%d,'%s','%s');",
"SELECT * FROM testTbl WHERE t_name LIKE '%%%s%%' LIMIT 100;",
};
int main(int argc, char *argv[])
{
char *zErrMsg = NULL;
int status , i = 0;
status = sqlite3_open (database_name, & db_handle);
if ( status)
{
printf("%d", status );
return 0;
}
/* Create Table */
status = sqlite3_exec (db_handle, TestSqlStats[0], NULL , 0, );
if (SQLITE_OK == status)
{
char name [30];
char desc [50];
for ( i = 1; i < 1000 && status == SQLITE_OK; i ++)
{
sprintf(name ,"TableName""%d", i);
sprintf(desc ,"Moves the selected control or dialog down""%d", i );
sprintf(queryString ,
TestSqlStats[1], i, name , desc);
status = sqlite3_exec (db_handle, queryString, NULL , 0, );
}
sprintf( queryString,
"DELETE FROM testTbl WHERE t_name LIKE '%%0%%' AND ROWID
BETWEEN 100 AND 200;");
status = sqlite3_exec (db_handle, queryString, callback, 0, );
sprintf(queryString ,
"SELECT ROWID,* FROM testTbl WHERE ROWID BETWEEN 100 AND 200;");
status = sqlite3_exec (db_handle, queryString, callback, 0, );
}
}
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
