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 <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
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.
