Hello!
I found that the IDs do not match the IDs in PostgreSQL 16.
This is really unexpected behavior, I cannot create and execute custom queries, custom migrations and e.t.c.
I modified the Aurelius demo to demonstrate the issue.
Also, when we talk about smart guids, it's better to use regular UUID v7 handled by PostgreSQL 18 (when applicable), instead of custom implementation.
tms-aurelius-wrong-pg-ids.zip (2.9 MB)
What do you mean by "IDs do not match"?
I tested your demo here with a newly installed PostgreSQL 16 database and all operations in the demo work fine - saving, finding, updating.
All regular operations within the ORM work, but if you try to execute a custom query by object ID (custom migration logic for example), it's impossible because the ORM returns IDs that don't match the IDs in the database.
ORM ID interpretation
<>
actual real ID
This is shown in the screenshots. I also tested with SQLite, and there are no issues there; the IDs from the ORM match the actual IDs in the database.
Delphi 13 (sep, oct patches) + Aurelius 5.27
That is simply the way Postgres (or FireDAC) stores the GUID in the raw database column.
You mapped the id to a TGuid, not string. It really depends on which database access layer you are using and how the GUID is handled by it.
Okay, it's not TMS related.
It turns out that TGUID represents old windows GUID format.
Windows switched to a new format that represents UUID, but continued to use GUID name.
I discovered GUIDEndian FireDAC connection parameter.
When set to TEndian.Big, it does the trick — the IDs are now in sync with PG interpretation.
And even TGUID has a parameter DataEndian
Create(const B: TBytes; DataEndian: TEndian = TEndian.Little): TGUID
When manually set to TEndian.Big, allows the value to be interpreted as a UUID.
