TOPIC: Primary Keys
#626
Primary Keys 3 Years, 6 Months ago
Hi,
I have just found Sunedio today and have been going through the user manual. I am wondering how one would create a primary key that auto increments by 1 for each new row inserted into the database. From what I can tell, the closest Sunedio comes to this is by setting a timestamp to a field declared as the primary key.

Is there a way to create auto incrementing primary keys?
 
 
#628
Re:Primary Keys 3 Years, 6 Months ago
Yes, usually in Suneido you use a Timestamp to get an unique ID field (that is very useful as a primary key). If you wanted just a sequential counter, then you have to implement it. It's not too hard...

You have first to define a counter table:

create counter(fieldname, seq)

In this table you have to insert a record with fieldname set to your counter field and seq to an initial value.

Then you have to define a function like my GetSeq() (that is inspired from the GetNextNumber function in stdlib):

Code:

GetSeq
function (field, log = false)
{
loops = 0
table = 'counter'
forever
{
t = Transaction(update:')
if ( (x = t.QueryFirst(table $ " where fieldname='" $ field $ "'"')) is false )
{
t.Rollback()
throw "GetSeq failed. No records in: " $ table
}
if (Number?(x.seq) is false)
{
t.Rollback()
throw "GetSeq failed: 'seq' field is not a number."
}
nextnum = x['seq']+1
x['seq']++
try x.Update() // update can throw transaction conflict
if t.Complete()
break
if (++loops > 10)
throw "GetSeq failed, too many tries on: " $ table
Sleep(Random(47)) //  avoid collision on retry
}
if log
SuneidoLog("GetSeq from " $ table $ ": " $ String(nextnum))
return nextnum
}



This function appears a little complicated because it manages transaction conflicts too (in a client/server environment).

Now you can define a rule for your field:

Code:

Rule_myCounterField
function()
{
return GetSeq('myCounterField')
}



And that's all... You have your automatic counter field!

Post edited by: Mauro, at: 2007/02/03 14:50
 
 
Mauro
 
#630
Re:Primary Keys 3 Years, 6 Months ago
Thanks for the infomation. Looks like using the timestamp is the cleanest way to generate the primary keys.