Improve the way of use uuid [CORE1682]

[firebird-automations]

Submitted by: Le Roy Arnaud (le-roy_a)

Ideas about to improve UUID usage in Firebird
and make easiest usage in different language

Problems with actual uuid store in char(16) generated with gen_uuid() in fb 2.1
--------------------------------------------
All the characters used in this uuid are not valid characters in urls.
so it's very problematic to create sql command in full text with uuid.

In tools like ibexpert you can't do copy/paste on ident
( So solve a problem it's more Headaches )

If you have an existing application and made some query in full string
you can't convert it to use uuid

Maybe solution
-------------
Have a function like create_uuid() in uuidUDF by Ian Newby
creates a 22 char string guid, 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.

[firebird-automations]

Commented by: @dyemanov

GUIDs are intentionally made binary (CHAR(16) OCTETS) to allow better performance when used as primary keys. If you just need a text representation, I think a new built-in function (e.g. FORMAT_UUID) would be a better solution. We already have an appropriate feature request: CORE1656.

[firebird-automations]

Commented by: Le Roy Arnaud (le-roy_a)

what do you mean by FORMAT_UUID

to affect a variable, you see something like that
id = FORMAT_UUID(GEN_UUID())
or
id = FORMAT_UUID()

do you think it's possible to have different format
( like in the udf uuidUDF )
a 36 char string guid representation.
a 22 char string guid (compressed form)
...

Many thanks

[firebird-automations]

Commented by: @dyemanov

I meant FORMAT_UUID(<uuid_field>) or FORMAT_UUID(GET_UUID()). Both are valid and just transform a binary representation to a textual one. Well, something like GEN_UUID(AS TEXT) is also possible, but I'm not sure it's really important.

As for the format, IMO we should support only the RFC4122 one, i.e. 36 chars.

[firebird-automations]

Commented by: Le Roy Arnaud (le-roy_a)

i think it could be a good thing if this function could generate a char(22) version to improve performance without lost any flexibility with unreadable character as we have with char(16) version.

[firebird-automations]

Commented by: @asfernandes

FORMAT_UUID alone is not good.

We need UUID_TO_CHAR and CHAR_TO_UUID functions.

These names seems very verbose (long) but I don't have good ones.

[firebird-automations]

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

[firebird-automations]

Commented by: @asfernandes

I resolved the ticket (as fixed) without intention.

[firebird-automations]

Modified by: @asfernandes

status: Resolved [ 5 ] => Reopened [ 4 ]

resolution: Fixed [ 1 ] =>

[firebird-automations]

Commented by: Le Roy Arnaud (le-roy_a)

Another suggestion is to create an uuid datatype with a size of 16 octet,
and when you select it you obtain a text compliant with the rfc4122 (plain text char 36) and when you do a update on this you can pass a text on this form {72fcfb90-c024-11dc-95ff-0800200c9a66} for exemple.

In sql server, we have this comportment with uuid type, and it's quite easy to use in application .

perhaps it's not a good idea because it's too dificult to implement or not respect the type definition in firebird or other thing !

[firebird-automations]

Modified by: @asfernandes

status: Reopened [ 4 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 2.5 Alpha 1 [ 10224 ]

assignee: Adriano dos Santos Fernandes [ asfernandes ]

[firebird-automations]

Modified by: @pcisar

Workflow: jira [ 13807 ] => Firebird [ 13913 ]

[firebird-automations]

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

[firebird-automations]

Modified by: @pavel-zotov

QA Status: No test