Most methods of obtaining a unique value for use as a technical primary key involve some sort of file access, such as:
The technique which is described here does not involve any file access at all, but can create a totally unique value in a field no more than 8 characters long.
I first came across the idea while accessing the Uniface newsgroup and was supplied with some sample code from Andrew Wilkins. After looking at this code I decided to try and make it simpler and more streamlined, and the results of my efforts are given below.
The starting point for this algorithm is the current date and time which should be supplied as a 16 digit number in the format CCYYMMDDHHMMSSTT (where 'TT' stands for 'ticks' or 1/100's of a second). Note that if you use the UNIFACE function $DATIM the 'ticks' portion is always zero, so if you need to generate more than one value each second this has to be compensated for later on.
It may be possible for you to obtain a time value from an alternate source which does provides values of less than a second, in which case it would make life a little easier.
The next step is to convert the date and time into the number of ticks that have elapsed since an arbitrary control date:
Reducing HHMMSSTT to a number of ticks is simple mathematics which should need no explanation (see the sample code below).
Reducing CCYYMMDD to a number of days is a surprisingly simple process if you are aware of the way in which UNIFACE deals with dates.
Thus in order to calculate the number of days which have elapsed since 1-January-2000 you would need no more than the following line of code:
Days = $date - $date(20000101)
For example, if today's date were 12th July 2002 this would produce a result of 923 days. This is equivalent to 7974720000 ticks (923 days x 8640000 ticks per day).
I have managed to reduce an original 16 digit number (CCYYMMDDHHMMSSTT) into a smaller number, but is it possible to reduce it even further? Yes it is. In a previous tip (A Binary-Octal-Decimal-Hexadecimal converter) I provided the code necessary to convert a number from one format to another. This gives me the ability to convert a number in base 10 (decimal) into a smaller number of characters in base 36. The number of characters you will need depends on the number of years you expect the algorithm to continue working, as indicated in the following examples:
Remember that the figures of 25 and 893 years commence from the date that you specify as your control date.
All the code necessary to perform these manipulations is given below. The input is provided by the $DATIM function and ends up as lv_Base36.
variables datetime lv_DateTime numeric lv_DayCount, lv_HH, lv_MM, lv_SS, lv_TT string lv_Ticks, lv_Base36 endvariables lv_DateTime = $datim ; obtain current date+time ; convert to number of days since 2000-01-01 lv_DayCount = $date(lv_DateTime) - $date(20000101) lv_HH = $datim(lv_DateTime) [H] ; extract hours lv_MM = $datim(lv_DateTime) [N] ; extract minutes lv_SS = $datim(lv_DateTime) [S] ; extract seconds lv_TT = $datim(lv_DateTime) [T] ; extract ticks (hundredths) ; convert time to a number of ticks (max 8640000) lv_Ticks = (((((lv_HH * 60) + lv_MM) * 60) + lv_SS) * 100) + lv_TT ; add in the ticks for this number of days lv_Ticks = (lv_DayCount * 8640000) + lv_Ticks ; convert from base 10 (decimal) to base 36 call DEC_TO_STRING(lv_Ticks, 36, lv_Base36)
Remember that if you use the $DATIM function the time value does not include ticks (1/100's of a second), so if this routine is accessed more than once in a second it will provide the same value. This minor annoyance can be compensated for by adjusting your <write> trigger as follows:
variables numeric lv_Decimal endvariables write while ($status = -7) ; duplicate key call STRING_TO_DEC(id.entity, 36, lv_Decimal) lv_Decimal = lv_Decimal + 1 ; increment call DEC_TO_STRING(lv_Decimal, 36, id.entity) write endwhile
If a duplicate key is detected this will convert the base 36 string into decimal, add another tick, then convert back into base 36, then attempt to write again.
Even if you manage to obtain an initial time value with ticks built in it may still be possible for the routine to be called more than once in the same 1/100 of a second, so this final adjustment could still provide a safety net.
An important factor in the ability of this routine to provide unique numbers is the number of physical places from which it obtains its datetime value. For example, if this routine is in a global proc which is executed on individual client PCs you must remember that each of these PCs will have its own clock which is probably not in sync with the others. This greatly increases the possibility of duplicate values.
If this routine were to be placed in a self-contained service which was run on a central application server then the datetime value would always be obtained from that server, thus reducing the possibility of duplicates.
Another possibility would be to use SQL commands to obtain the datetime values from the database server.
If multiple sources for datetime values cannot be avoided then a possible solution would be to add a different prefix to the output from each source. This would make a value obtained from source 'A' totally unique from any value supplied by source 'B'. The only disadvantage would be an increase in the size of the output value.
Tony Marston
12th July 2002
mailto:tony@tonymarston.net
mailto:TonyMarston@hotmail.com
http://www.tonymarston.net