for InterBase and FireBird

Functions enclosed in FreeAdhocUDF.dll/.so/.dylib :
UUID functions		13 functions
	Preliminary note	look http://en.wikipedia.org/wiki/UUID
	Create	6 functions
	Transform	2 functions
	Read	5 functions
		returns <null> instead of 0, '' (empty string) or '17.11.1858'
		Output RETURN mechanism if nothing other is published: FREE_IT TestSQLs with NULL run only in FireBird 2.0

 

UUID functions: Preliminary note

A Universally Unique Identifier is an identifier standard used in software construction, standardized by the Open Software Foundation (OSF) as part of the Distributed Computing Environment (DCE). The intent of UUIDs is to enable distributed systems to uniquely identify information without significant central coordination. Thus, anyone can create a UUID and use it to identify something with reasonable confidence that the identifier will never be unintentionally used by anyone for anything else. Information labelled with UUIDs can therefore be later combined into a single database without need to resolve name conflicts. The most widespread use of this standard is in Microsoft's Globally Unique Identifiers (GUIDs) which implement this standard. A UUID is essentially a 16-byte (128 bit) number and in its canonical form a UUID may look like this: 550e8400-e29b-41d4-a716-446655440000 The number of theoretically possible UUIDs is therefore 25616 or about 3.4 × 1038. This means that during a 10 billion year lifetime of the Earth, about 1 trillion UUIDs have to be created every nanosecond to exhaust the number of UUIDs. The term GUID usually references Microsoft's implementation of the UUID standard, however, many other pieces of software use the term GUID including Oracle Database and Novell eDirectory. Conceptually, the original (version 1) generation scheme for UUIDs was to concatenate the UUID version with the MAC address of the computer that is generating the UUID, and with the number of 100-nanosecond intervals since the adoption of the Gregorian calendar. In practice, the actual algorithm is more complicated. This scheme has been criticized in that it is not sufficiently 'opaque'; it reveals both the identity of the computer that generated the UUID and the time at which it did so. In the RFC4122 there are 5 UUID-versions descriped:     1    time-based         a. with a real MAC-adress descriped in the IEEE 802-standard         b. with a random-generated MAC-Adresse descriped in the IEEE 802-standard     2    DCE-security-version (with POSIX UIDs)     3    namespace-based (with MD5)     4    random created     5    namespace-based (SHA-1) For purpose of the locally generated unique IDs for records with compatibility under Windows and Linux with the same algorithm only the versions 1 and 4 can be used (version 3 and 5 not because there is not surely a name-space on the computer): Because you prefer a decoding or a non-decoding UUID we made 3 different kinds of UUIDs: F_UUID1MAC     version 1 with a real MAC-Adresse     you can decode the creating-time and the MAC-adress of the computer F_UUID1RAND     version 1 with a randomly generated MAC-adresse     only decode of the creating-time possible F_UUID4     version 4     no decoding possible In the uuidlib from Ian Newby we found a modification of the version 1b (with random generated MAC-adress) which was compressed instead of the output-mask from the RFC4122. This algorithm has advantages on created UUIDs on the same server because it first give out the MAC-adress and then the timestamp. So the index could work quicker.  We also took this algorithm, incl. the transforming-functions, so we have a compressed version of every of the 3 basic-versions. F_UUID1MACCOMPR, UUID1RANDCOMPR, UUID4COMPR.

 

UUID functions: Create
F_UUID1MAC		function from adhoc
Entrypoint	uuid1mac	compatible with UTF-8
Input	nothing
Output	CSTRING(36)	Universally Unique Identifier (UUID) version 1a
Could no MAC-adress found on the DB-server, the function creates an UUID like UUID1RAND TestSQL SELECT F_UUID1MAC() FROM RDB$DATABASE;
F_UUID1RAND		function from adhoc
Entrypoint	uuid1rand	compatible with UTF-8
Input	nothing
Output	CSTRING(36)	Universally Unique Identifier (UUID) version 1b
TestSQL SELECT F_UUID1RAND() FROM RDB$DATABASE;
F_UUID4		function from adhoc input/output-compatibility to rFunc (CREATEGUID)
Entrypoint	uuid4	compatible with UTF-8
Input	nothing
Output	CSTRING(36)	Universally Unique Identifier (UUID) version 4
TestSQL SELECT F_UUID4() FROM RDB$DATABASE;
F_UUID1MACCOMPR		function from adhoc in memorial to uuidlib
Entrypoint	uuid1maccompr	compatible with UTF-8
Input	nothing
Output	CSTRING(22)	compressed Universally Unique Identifier (UUID) version 1a
Notice (from uuidlib): Creates a 22 char string uuid, which is a compressed form where the order of the sections is reversed to allow firebirds index prefix compression to take place. All the characters used in this uuid are valid characters in urls. TestSQL SELECT F_UUID1MACCOMPR() FROM RDB$DATABASE;
F_UUID1RANDCOMPR		return-compatible to function UUID_CREATE in uuidlib
Entrypoint	uuid1randcompr	compatible with UTF-8
Input	nothing
Output	CSTRING(22)	compressed Universally Unique Identifier (UUID) version 1b
TestSQL SELECT F_UUID1RANDCOMPR() FROM RDB$DATABASE;
F_UUID4COMPR		function from adhoc
Entrypoint	uuid4compr	compatible with UTF-8
Input	nothing
Output	CSTRING(22)	compressed Universally Unique Identifier (UUID) version 4
TestSQL SELECT F_UUID4COMPR() FROM RDB$DATABASE;

 

UUID functions:  Transform
F_UUID2UUIDCOMPR		return-compatible to function GUID_TO_UUID in uuidlib
Entrypoint	uuid2uuidcompr	compatible with UTF-8
Input	CSTRING(250)	valid UUID every version
Output	CSTRING(22)	compressed UUID
TestSQL (to use with TestISO.GDB) If you take a compressed UUID for input instead of a normal UUID: SELECT UUIDTESTID, 'Input no RFC4122 UUID' AS ISCORRECT, F_UUID2UUIDCOMPR(UUIDCOMPR) FROM UUIDTEST WHERE UUIDCOMPR IS NOT NULL ORDER BY 1; If you take a normal string for input instead of a normal UUID: SELECT 'Input no RFC4122 UUID' AS ISCORRECT, F_UUID2UUIDCOMPR('abcde') FROM RDB$DATABASE; If you take a normal UUID for input (right): SELECT UUIDTESTID, UUIDCOMPR AS ISCORRECT, F_UUID2UUIDCOMPR(UUID) FROM UUIDTEST WHERE UUIDCOMPR IS NOT NULL ORDER BY 1; SELECT NULL AS ISCORRECT, F_UUID2UUIDCOMPR(NULL) FROM RDB$DATABASE;
F_UUIDCOMPR2UUID		return-compatible to function UUID_TO_GUID in uuidlib
Entrypoint	uuidcompr2uuid	compatible with UTF-8
Input	CSTRING(250)	valid compressed UUID
Output	CSTRING(36)	UUID with the same version of the compressed UUID
TestSQL (to use with TestISO.GDB) If you take a normal UUID for input instead of a compressed UUID: SELECT UUIDTESTID, 'Input no compressed UUID' AS ISCORRECT, F_UUIDCOMPR2UUID(UUID) FROM UUIDTEST WHERE UUIDCOMPR IS NOT NULL ORDER BY 1; If you take a normal string for input instead of a compressed UUID: SELECT 'Input no compressed UUID' AS ISCORRECT, F_UUIDCOMPR2UUID('abcde') FROM RDB$DATABASE; If you take a compressed UUID for input (right): SELECT UUIDTESTID, UUID AS ISCORRECT, F_UUIDCOMPR2UUID(UUIDCOMPR) FROM UUIDTEST WHERE UUIDCOMPR IS NOT NULL ORDER BY 1; SELECT NULL AS ISCORRECT, F_UUIDCOMPR2UUID(NULL) FROM RDB$DATABASE;

 

UUID functions:  Read
F_UUIDVERSION		function from adhoc
Entrypoint	uuidversion	compatible with UTF-8
Input	CSTRING(250)	valid UUID
Output	CSTRING(20)	(variant and) version of the UUID
Possible variants are: (not yet implemented)        0    NCS (reserved for backwards-compatibility)      10    the actuall variant as descriped in the RFC4122    110    Microsoft (reserved for backwards-compatibility)    111    reserved for further variants Possible versions are:     1    time-based         a. with a real MAC-adress descriped in the IEEE 802-standard         b. with a random-generated MAC-Adresse descriped in the IEEE 802-standard     2    DCE-security-version (with POSIX UIDs)     3    namespace-based (with MD5)     4    random created     5    namespace-based (SHA-1) The function returns:     V1a     V1b     V3     V4     V5 TestSQL (für TestISO.GDB) SELECT ART, F_UUIDVERSION(UUID) FROM UUIDTEST ORDER BY UUIDTESTID; SELECT NULL AS ISCORRECT, F_UUIDVERSION(NULL) FROM RDB$DATABASE;
F_UUID1TIMESTAMP		function from adhoc
Entrypoint	uuid1timestamp	compatible with UTF-8
Input	CSTRING(250)	valid UUID version 1
Output	TIMESTAMP	timestamp of the creating date and time of the UUID version 1
Reports the timestamp from the UUID when the UUID was created. If the timestamp isn’t real, the function reports 31.12.1899 00:00:00  (Time = 0 - starttime of the internal InterBase/FireBird time) instead of FireBird 2.0, it returns <null> if it is declaired.   TestSQL (to use with TestISO.GDB) If you take an compressed UUID for input instead of a normal UUID: SELECT UUIDTESTID, F_UUIDVERSION(UUID), ART, NULL AS ISCORRECT, F_UUID1TIMESTAMP(UUIDCOMPR) FROM UUIDTEST ORDER BY 1; If you take a normal UUID for input (right): SELECT UUIDTESTID, F_UUIDVERSION(UUID), ART, ZEITSTEMPEL AS ISCORRECT, F_UUID1TIMESTAMP(UUID) FROM UUIDTEST ORDER BY 1; SELECT NULL AS ISCORRECT, F_UUID1TIMESTAMP(NULL) FROM RDB$DATABASE;
F_UUID1COMPRTIMESTAMP		function from adhoc
Entrypoint	uuid1comprtimestamp	compatible with UTF-8
Input	CSTRING(250)	valid compressed UUID version 1
Output	TIMESTAMP	timestamp of the creating date and time of the compressed UUID version 1
Reports the timestamp from the UUID when the UUID was created. If the timestamp isn’t real, the function reports 31.12.1899 00:00:00  (Time = 0 - starttime of the internal InterBase/FireBird time) instead of FireBird 2.0, it returns <null> if it is declaired.   TestSQL (to use with TestISO.GDB) If you take a normal UUID for input instead of a compressed UUID: SELECT UUIDTESTID, F_UUIDVERSION(UUID), ART, NULL AS ISCORRECT, F_UUID1COMPRTIMESTAMP(UUID) FROM UUIDTEST ORDER BY 1; If you take a compressed UUID for input (right): SELECT UUIDTESTID, F_UUIDVERSION(UUID), ART, ZEITSTEMPEL AS ISCORRECT, F_UUID1COMPRTIMESTAMP(UUIDCOMPR) FROM UUIDTEST ORDER BY 1; SELECT NULL AS ISCORRECT, F_UUID1COMPRTIMESTAMP(NULL) FROM RDB$DATABASE;
F_UUID1MACMAC		function from adhoc
Entrypoint	uuid1macmac	compatible with UTF-8
Input	CSTRING(250)	valid UUID Version 1a
Output	CSTRING(17)	MAC-adress of the computer where UUID was created
TestSQL (to use with TestISO.GDB) If you take a compressed UUID for input instead of a normal UUID: SELECT UUIDTESTID, F_UUIDVERSION(UUID), ART, NULL AS ISCORRECT, F_UUID1MACMAC(UUIDCOMPR) FROM UUIDTEST ORDER BY 1; If you take a normal UUID for input (right): SELECT UUIDTESTID, F_UUIDVERSION(UUID), ART, MACADRESS AS ISCORRECT, F_UUID1MACMAC(UUID) FROM UUIDTEST ORDER BY 1; SELECT NULL AS ISCORRECT, F_UUID1MACMAC(NULL) FROM RDB$DATABASE;
F_UUID1MACCOMPRMAC		function from adhoc
Entrypoint	uuid1maccomprmac	compatible with UTF-8
Input	CSTRING(250)	valid compressed UUID version 1a
Output	CSTRING(17)	MAC-adress of the computer where UUID was created
TestSQL  (to use with TestISO.GDB) If you take a normal UUID for input instead of a compressed UUID: SELECT UUIDTESTID, F_UUIDVERSION(UUID), ART, NULL AS ISCORRECT, F_UUID1MACCOMPRMAC(UUID) FROM UUIDTEST ORDER BY 1; If you take a compressed UUID for input (right): SELECT UUIDTESTID, F_UUIDVERSION(UUID), ART, MACADRESS AS ISCORRECT, F_UUID1MACCOMPRMAC(UUIDCOMPR) FROM UUIDTEST ORDER BY 1; SELECT NULL AS ISCORRECT, F_UUID1MACCOMPRMAC(NULL) FROM RDB$DATABASE;