gen_uuid as primary key

[Harald Wolf]

Hello,

https://www.heise.de/select/ct/2022/6/2202713155303246822 (sorry german article)

describes the advantages using UUID as the primary key for tables. So i plan to switch.

My first attempt seems to work with some data records but they look not as expected:

------------------------------------------------------------------------------------------------------------------------

ID                              ©/ïÜMÈF¥?òa´>ô↓o
NAME                            abc
INVENTARNUMMER                  def

how do i fix this to look something similar than: 

SQL> select gen_uuid() from rdb$database;
GEN_UUID                        6986CC174FE14BF1BC542CCE01E78BA7

?

are there any other issues/tips for using the uuid as primary key?

--------------------------------------------------------------------------------------------------------------------------

SET SQL DIALECT 3;

CREATE DATABASE 'TEST.FDB' PAGE_SIZE 16384 DEFAULT CHARACTER SET ISO8859_1;
ALTER CHARACTER SET ISO8859_1 SET DEFAULT COLLATION DE_DE;

CREATE TABLE DATEN (ID VARCHAR(40) NOT NULL PRIMARY KEY,
Name Varchar(500),
...

...);

SET TERM ^ ;
CREATE TRIGGER TRDX FOR DATEN
ACTIVE BEFORE INSERT POSITION 0
AS BEGIN
if (NEW.ID is NULL) then NEW.ID = GEN_UUID();
END ^
COMMIT WORK ^
SET TERM ; ^

[Stefan Heymann]

> https://www.heise.de/select/ct/2022/6/2202713155303246822 (sorry german article) describes the advantages using UUID as the primary key for tables. So i plan to switch.

This article is about the situation of *distributed* databases ("verteilten Datenbanken") where several engines create primary keys that have to be unique. If you have a single, non-distributed, database then using a UUID only makes things more complicated, slower and difficult to read for humans.

Regards

Stefan

[Karol Bieniaszewski]

Hi

 

First to store UUID into database do not use such type of the field.

To have it readable use char_to_uuid

https://firebirdsql.org/refdocs/langrefupd25-intfunc-char_to_uuid.html

 

As you have used VARCHAR(40) CHARACTER SET ISO8859_1 you have stored binary data into char fields.

Use CHAR(16) CHARACTER SET OCTETS for UUID

https://firebirdsql.org/refdocs/langrefupd25-intfunc-gen_uuid.html

 

Regards,

Karol Bieniaszewski

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/CAHAYh%3DoBmHFzpfEqF4x2XQHToYXCJVyPBfL_EMU2OkH%3DX3isgQ%40mail.gmail.com.

 

[Dimitry Sibiryakov]

'Harald Wolf' via firebird-support wrote 03.05.2022 15:29:
> My first attempt seems to work with some data records but they look not as expected:
> ------------------------------------------------------------------------------------------------------------------------
> ID                              ©/ïÜMÈF¥?òa´>ô↓o
> NAME                            abc
> INVENTARNUMMER                  def
>
> how do i fix this to look something similar than:
>
> SQL> select gen_uuid() from rdb$database;
> GEN_UUID                        6986CC174FE14BF1BC542CCE01E78BA7
> ?

You should contact the developer of the application you are trying to use for
UUID display and inform them that their application has a problem with
displaying of type VARBINARY (which is the type to use with UUIDs).

> are there any other issues/tips for using the uuid as primary key?

In some cases for performance reason you should consider to use a massaged
(converted to big-endian form) values from "sequential GUID" generator. On the
other hand if your application doesn't handle insertion of thousands of records
per second (index page split performance problem) or millions of records in the
table total (index depth problem) you have nothing to worry about.

--
WBR, SD.

[Harald Wolf]

@Stefan Heymann: distributed databases. That is exactly what i want. ...i read the article ;-)

@Karol Bieniaszewski: as the documentation shows char_to_uuid just inserts some dashes what makes this mess more readable when displayed.

thanks this seems to be correct.

select * from daten;
ID                              B0326D673E64442590672F7D52F836EA
NAME                            abc

.....

------------------------------------------------------------------------------------------------------------------------

SET SQL DIALECT 3;

/* CREATE DATABASE 'TEST.FDB' PAGE_SIZE 16384 DEFAULT CHARACTER SET ISO8859_1 */

/*  Character sets */

ALTER CHARACTER SET ISO8859_1 SET DEFAULT COLLATION DE_DE;

/* Table: DATEN, Owner: SYSDBA */
CREATE TABLE DATEN (ID CHAR(16) CHARACTER SET OCTETS NOT NULL,
        NAME VARCHAR(500) CHARACTER SET ISO8859_1,
...

...
PRIMARY KEY (ID));
SET TERM ^ ;

/* Triggers only will work for SQL triggers */

CREATE TRIGGER TRDX FOR DATEN
ACTIVE BEFORE INSERT POSITION 0
AS BEGIN
if (NEW.ID is NULL) then NEW.ID = GEN_UUID();
END ^

COMMIT WORK ^
SET TERM ; ^

-----------------------------------------------------------------------------------------------------------------

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/5c7a3f5c-85fd-0c5f-aa0b-73be85314e7e%40ibphoenix.com.

[Mark Rotteveel]

On 03-05-2022 15:29, 'Harald Wolf' via firebird-support wrote:
> My first attempt seems to work with some data records but they look not
> as expected:
> ------------------------------------------------------------------------------------------------------------------------
> ID                              ©/ïÜMÈF¥?òa´>ô↓o
> NAME                            abc
> INVENTARNUMMER                  def
>
> how do i fix this to look something similar than:
>
> SQL> select gen_uuid() from rdb$database;
> GEN_UUID                        6986CC174FE14BF1BC542CCE01E78BA7
> ?
> are there any other issues/tips for using the uuid as primary key?
> --------------------------------------------------------------------------------------------------------------------------
> SET SQL DIALECT 3;
>
> CREATE DATABASE 'TEST.FDB' PAGE_SIZE 16384 DEFAULT CHARACTER SET ISO8859_1;
> ALTER CHARACTER SET ISO8859_1 SET DEFAULT COLLATION DE_DE;
>
> CREATE TABLE DATEN (ID VARCHAR(40) NOT NULL PRIMARY KEY,
> Name Varchar(500),
> ...
> ...);

You're storing the UUID in a VARCHAR field, which means you're assuming
it is in a human-readable format. GEN_UUID returns a *binary* UUID,
which is 16 characters *always*. Store it in a CHAR(16) CHARACTER SET
OCTETS (or since Firebird 4.0, BINARY(16)).

You are selecting it and assuming it be a human-readable, it is not. If
you want the UUID to be human-readable, use `UUID_TO_CHAR` when
selecting. I would recommend not storing the UUID as human-readable in
your database as it inflates the key size.

Mark
--
Mark Rotteveel

[Mark Rotteveel]

On 03-05-2022 18:34, 'Harald Wolf' via firebird-support wrote:
> @Stefan Heymann: distributed databases. That is exactly what i want.
> ...i read the article ;-)
> @Karol Bieniaszewski: as the documentation shows char_to_uuid just
> inserts some dashes what makes this mess more readable when displayed.
>
> thanks this seems to be correct.
> select * from daten;
> ID                              B0326D673E64442590672F7D52F836EA
> NAME                            abc
> .....
> ------------------------------------------------------------------------------------------------------------------------

[..]

> /* Table: DATEN, Owner: SYSDBA */
> CREATE TABLE DATEN (ID CHAR(16) CHARACTER SET OCTETS NOT NULL,
>         NAME VARCHAR(500) CHARACTER SET ISO8859_1,

[..]
Be aware that with this, you're relying on ISQL (or whatever query tool
you're using), is displaying binary values in hex.

UUID_TO_CHAR doesn't only "insert[s] some dashes", it converts the 16
byte binary value to a 36 character human-readable string (by converting
to hex and inserting those dashes).

Mark
--
Mark Rotteveel

[Martijn Tonies (Upscene Productions)]

Hi,

DevArt IBDAC Delphi components allow to map the binary 16 OCTETS to a
human readable format for this purpose.

Perhaps this is what's being used in the query tool Harald uses.


With regards,

Martijn Tonies
Upscene Productions
https://www.upscene.com

Database Workbench - developer tool for Oracle, SQL Server, PostgreSQL,
MySQL, InterBase, NexusDB and Firebird.

--
You received this message because you are subscribed to the Google Groups
"firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit

https://groups.google.com/d/msgid/firebird-support/3cd0bdf8-d0d1-be50-fff5-6cc543e352ea%40lawinegevaar.nl.

[Piergiorgio Valli]

Hello,

if we save uuid as binary for every query we to add uuid_to_char. In other database for example in Postgresql there is type uuid,

in the new Firebird 6, It is possible add a type uuid to avoid every query uuid_to_char?

Thanks

Pier

[Dimitry Sibiryakov]

Piergiorgio Valli wrote 17.04.2024 22:53:
> in the new Firebird 6, It is possible add a type uuid to avoid every query
> uuid_to_char?

You can use type BINARY(16). You can add a domain named UUID that is alias
for BINARY(16).

--
WBR, SD.