FreeAdhocUDF for InterBase and FireBird in deutschin englishen françaisen españolin italianoa português
homeprevious pageÜbersicht • overview • aperçunext pagelast page Seite empfehlen • advice website •Forum mailing-listwas ich vermisse • what I missed •eMail Kommentar • eMail commentprintsitemap
 
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.
 
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
 
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;
nach oben • go top •
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;
nach oben • go top •
 
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
vorige Seite • previous page • passée sitenach oben • go top • vers le hautnächste Seite • next page • prochain site