TOPIC: Primary Keys
Primary Keys 3 Years, 6 Months ago
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?
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):


function (field, log = false)
loops = 0
table = 'counter'
t = Transaction(update:')
if ( (x = t.QueryFirst(table $ " where fieldname='" $ field $ "'"')) is false )
throw "GetSeq failed. No records in: " $ table
if (Number?(x.seq) is false)
throw "GetSeq failed: 'seq' field is not a number."
nextnum = x['seq']+1
try x.Update() // update can throw transaction conflict
if t.Complete()
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:


return GetSeq('myCounterField')

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

Post edited by: Mauro, at: 2007/02/03 14:50
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.