Maintaining a sequence that's not rowid

classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

Maintaining a sequence that's not rowid

Clark Christensen
In my app (a perl/web-based on-line training system), I have a table of users with an integer primary key column, tech_id.  The tech_ids are created by a foreign system, and either imported with other data, or inserted as-received by my app.  
 
 In enhancing the app, I'm finding it desirable to insert self-registered technician candidates in this table with a tech_id that's outside the sequence of the current tech_id, a temporary tech ID.  
 
 When the tech passes the on-line exam, the tech_id would be updated with the permanent tech_id from the foreign DB.  I want to have SQLite generate these temporary IDs.  
 
 I'm looking for suggestions on how to do this with SQLite3.
 
 As a test case, I came up with this scanario:
 
  create table t1 (a integer primary key autoincrement, b text);
  create trigger deleteme after insert on t1
      begin
          delete from t1 where a = new.a;
      end;
  insert into t1 values (null, 'a');
 
 then get the last_insert_rowid
 
 which seems to work.  The table itself never holds any data, but, by virtue of 'autoincrement', SQLite keeps track of the next value for column, "a".
 
 So, finally, my questions:  What's wrong with this scheme?  How bulletproof is it?  What would work better?
 
 Thanks!
 
  -Clark
 


Reply | Threaded
Open this post in threaded view
|

Re: Maintaining a sequence that's not rowid

Dan Kennedy
I think what you propose will work fine, but you could just
do it all yourself with some SQL. Arguably clearer, and no
messing about with complex, possibly non-portable, triggers +
auto-increments.

/* Initialize system */
BEGIN;
CREATE TABLE id_allocator(id INTEGER);
INSERT INTO id_allocator(0);
COMMIT;

/* Retrieve next id in sequence: */
BEGIN;
SELECT id FROM id_allocator;          /* This is the id to use */
UPDATE id_allocator SET id = id + 1;
COMMIT;   /* Can't use the id until the transaction successfully commits! */

--- Clark Christensen <[hidden email]> wrote:

> In my app (a perl/web-based on-line training system), I have a table of users with an integer
> primary key column, tech_id.  The tech_ids are created by a foreign system, and either imported
> with other data, or inserted as-received by my app.  
>  
>  In enhancing the app, I'm finding it desirable to insert self-registered technician candidates
> in this table with a tech_id that's outside the sequence of the current tech_id, a temporary
> tech ID.  
>  
>  When the tech passes the on-line exam, the tech_id would be updated with the permanent tech_id
> from the foreign DB.  I want to have SQLite generate these temporary IDs.  
>  
>  I'm looking for suggestions on how to do this with SQLite3.
>  
>  As a test case, I came up with this scanario:
>  
>   create table t1 (a integer primary key autoincrement, b text);
>   create trigger deleteme after insert on t1
>       begin
>           delete from t1 where a = new.a;
>       end;
>   insert into t1 values (null, 'a');
>  
>  then get the last_insert_rowid
>  
>  which seems to work.  The table itself never holds any data, but, by virtue of 'autoincrement',
> SQLite keeps track of the next value for column, "a".
>  
>  So, finally, my questions:  What's wrong with this scheme?  How bulletproof is it?  What would
> work better?
>  
>  Thanks!
>  
>   -Clark
>  
>
>
>



               
__________________________________
Yahoo! Mail - PC Magazine Editors' Choice 2005
http://mail.yahoo.com
Reply | Threaded
Open this post in threaded view
|

Re: Maintaining a sequence that's not rowid

Jay Sprenkle
In reply to this post by Clark Christensen
On 10/5/05, Clark Christensen <[hidden email]> wrote:
>
> In my app (a perl/web-based on-line training system), I have a table of
> users with an integer primary key column, tech_id. The tech_ids are created
> by a foreign system, and either imported with other data, or inserted
> as-received by my app.
>
> In enhancing the app, I'm finding it desirable to insert self-registered
> technician candidates in this table with a tech_id that's outside the
> sequence of the current tech_id, a temporary tech ID.


Why not just add an auto increment primary key field to this table?
Your new primary key field is a 'local' id separate from the tech_id issued
by another system.
Reply | Threaded
Open this post in threaded view
|

RE: Maintaining a sequence that's not rowid

Clark, Chris M
In reply to this post by Clark Christensen
>  From: Dan Kennedy [mailto:[hidden email]]
>  Sent: Wed 10/5/2005 11:30 PM

>  /* Retrieve next id in sequence: */
>  BEGIN;
>  SELECT id FROM id_allocator;          /* This is the id to use */
>  UPDATE id_allocator SET id = id + 1;
>  COMMIT;   /* Can't use the id until the transaction successfully commits! */

Just a side note; Traditionally this is done the other way around, i.e. update then select. The reason for this is the (small) window in the critical section for another session to issue the select at the same time causing a deadlock. Locking the table exclusively at the beginnng of the transaction avoids that problem.

Chris

Reply | Threaded
Open this post in threaded view
|

RE: Maintaining a sequence that's not rowid

Dan Kennedy

> >  /* Retrieve next id in sequence: */
> >  BEGIN;
> >  SELECT id FROM id_allocator;          /* This is the id to use */
> >  UPDATE id_allocator SET id = id + 1;
> >  COMMIT;   /* Can't use the id until the transaction successfully commits! */
>
> Just a side note; Traditionally this is done the other way around, i.e. update then select. The
> reason for this is the (small) window in the critical section for another session to issue the
> select at the same time causing a deadlock. Locking the table exclusively at the beginnng of the
> transaction avoids that problem.

True enough. You also need to do "BEGIN EXCLUSIVE;"
instead of begin. Or just handle the busy condition.


               
__________________________________
Yahoo! Mail - PC Magazine Editors' Choice 2005
http://mail.yahoo.com
Reply | Threaded
Open this post in threaded view
|

Re: Maintaining a sequence that's not rowid

Antony Sargent
Alternatively, you might consider making the id_allocator table have an
auto-increment primary key, and then insert null to have sqlite generate
id's for you, and use last_insert_rowid() to find out the value it
generated.

/* Initialize system */
BEGIN;
CREATE TABLE id_allocator(id INTEGER PRIMARY KEY);
COMMIT;


/* Retrieve next id in sequence: */
INSERT INTO id_allocator (id) VALUES (NULL);
SELECT last_insert_rowid(); /* This is the id to use */

The sqlite documentation here:

http://www.sqlite.org/capi3ref.html#sqlite3_last_insert_rowid

doesn't make it entirely clear, but in a quick test I did sqlite seems
to do the right thing, which is to return the last insert rowid for your
database handle irrespective of whether other db handles did inserts
between your insert and select. I think this implies you don't need a
transaction wrapped around the insert/select.



Dan Kennedy wrote:

>>> /* Retrieve next id in sequence: */
>>> BEGIN;
>>> SELECT id FROM id_allocator;          /* This is the id to use */
>>> UPDATE id_allocator SET id = id + 1;
>>> COMMIT;   /* Can't use the id until the transaction successfully commits! */
>>
>>Just a side note; Traditionally this is done the other way around, i.e. update then select. The
>>reason for this is the (small) window in the critical section for another session to issue the
>>select at the same time causing a deadlock. Locking the table exclusively at the beginnng of the
>>transaction avoids that problem.
>
>
> True enough. You also need to do "BEGIN EXCLUSIVE;"
> instead of begin. Or just handle the busy condition.
>
>
>
> __________________________________
> Yahoo! Mail - PC Magazine Editors' Choice 2005
> http://mail.yahoo.com
>

Reply | Threaded
Open this post in threaded view
|

Re: Maintaining a sequence that's not rowid

Darren Duncan
At 11:13 AM -0700 10/6/05, Antony Sargent wrote:

>Alternatively, you might consider making the id_allocator table have
>an auto-increment primary key, and then insert null to have sqlite
>generate id's for you, and use last_insert_rowid() to find out the
>value it generated.
>
>/* Initialize system */
>BEGIN;
>CREATE TABLE id_allocator(id INTEGER PRIMARY KEY);
>COMMIT;
>
>/* Retrieve next id in sequence: */
>INSERT INTO id_allocator (id) VALUES (NULL);
>SELECT last_insert_rowid(); /* This is the id to use */

I'm inclined to think that this is a bad idea by itself because your
id_allocator table ends up with a large number of records in it, one
per increment, which take up space but don't serve a useful purpose.
Whereas, an updating approach will not take up any more space than
necessary. -- Darren Duncan
Reply | Threaded
Open this post in threaded view
|

Re: Maintaining a sequence that's not rowid

Jay Sprenkle
In reply to this post by Antony Sargent
On 10/6/05, Darren Duncan <[hidden email]> wrote:
>
>
> I'm inclined to think that this is a bad idea by itself because your
> id_allocator table ends up with a large number of records in it, one
> per increment, which take up space but don't serve a useful purpose.
> Whereas, an updating approach will not take up any more space than
> necessary. -- Darren Duncan
>


Depends on what you're optimizing for. Disk space is cheaper than dirt.
Reply | Threaded
Open this post in threaded view
|

Re: Maintaining a sequence that's not rowid

Clark Christensen
In reply to this post by Dan Kennedy
Thanks to all who replied.  I really appreciate the great feedback!
 
 I will probably end-up using something like the scheme offered below :-)
 
  -Clark
 

----- Original Message ----
From: Dan Kennedy <[hidden email]>
To: [hidden email]
Sent: Thursday, October 06, 2005 09:42:32
Subject: RE: [sqlite] Maintaining a sequence that's not rowid


> >  /* Retrieve next id in sequence: */
> >  BEGIN;
> >  SELECT id FROM id_allocator;          /* This is the id to use */
> >  UPDATE id_allocator SET id = id + 1;
> >  COMMIT;   /* Can't use the id until the transaction successfully commits! */
>
> Just a side note; Traditionally this is done the other way around, i.e. update then select. The
> reason for this is the (small) window in the critical section for another session to issue the
> select at the same time causing a deadlock. Locking the table exclusively at the beginnng of the
> transaction avoids that problem.

True enough. You also need to do "BEGIN EXCLUSIVE;"
instead of begin. Or just handle the busy condition.



Reply | Threaded
Open this post in threaded view
|

Formatting the strftimeFunc function

njhinder
In reply to this post by Dan Kennedy
I am looking to be able to return a string from the function strftimeFunc()
where the month can be returned as "MMM" (like "AUG").  Is this a feature
that could be implemented in SQLITE in the near future?
Thanks,
Nicole Hinderman

Reply | Threaded
Open this post in threaded view
|

Re: Maintaining a sequence that's not rowid

Stephen J. Lombardo
In reply to this post by Clark Christensen

Feel free to take a quick look at ticket 1476 (
http://www.sqlite.org/cvstrac/tktview?tn=1476 ). The attached patch
provides nextval() and curval() functions to manipulate named sequences
in a manner similar to those found in other databases. It was developed
to meet a requirement similar to yours and it supports arbitrary integer
increments (both positive and negative), min/max value constraints, and
cycling. You're welcome to use it as well if meets your needs.

Cheers,
Stephen

Clark Christensen wrote:

>Thanks to all who replied.  I really appreciate the great feedback!
>
> I will probably end-up using something like the scheme offered below :-)
>
>  -Clark
>
>
>----- Original Message ----
>From: Dan Kennedy <[hidden email]>
>To: [hidden email]
>Sent: Thursday, October 06, 2005 09:42:32
>Subject: RE: [sqlite] Maintaining a sequence that's not rowid
>
>
>  
>
>>> /* Retrieve next id in sequence: */
>>> BEGIN;
>>> SELECT id FROM id_allocator;          /* This is the id to use */
>>> UPDATE id_allocator SET id = id + 1;
>>> COMMIT;   /* Can't use the id until the transaction successfully commits! */
>>>      
>>>
>>Just a side note; Traditionally this is done the other way around, i.e. update then select. The
>>reason for this is the (small) window in the critical section for another session to issue the
>>select at the same time causing a deadlock. Locking the table exclusively at the beginnng of the
>>transaction avoids that problem.
>>    
>>
>
>True enough. You also need to do "BEGIN EXCLUSIVE;"
>instead of begin. Or just handle the busy condition.
>
>
>
>  
>