SQL and UID

Houge · 04-23-2017 04:01 PM

Hi, i faced a problem:
I have UID in my postgresql DB table, it looks like:

Code:

2b179f70-3c29-11e4-abda-6fbacb471cca

But when i select it to EE application i get the following:

Code:

709f172b-293c-e411-abda-6fbacb471cca

As you see first 8 bytes are swaped, rest 8 bytes are OK, it appears not only in select but in insert also.

When i want to insert it i do:

Code:

id.fromHex("2b179f703c2911e4abda6fbacb471cca");

Tried in 32 and 64bit, Debug and Release.

If i try to select vice versa, it works vice versa.

One more example:

Quote:in db: 12345678-9012-3456-7890-123456789012
in ee: 78563412-1290-5634-7890-123456789012

But if i pass UID as text from EE and to EE, it works fine, but it's bad, because it's way too slower.

UPD:
If i bind UID like this to be inserted into database, it inserts fine:

Code:

sql.commandParam(0, CPtr(&id), 16); // Inserts fine

sql.commandParam(0, id); // inserts swaped

But in any way after select back to EE it swaps no matter how it was inserted

Zervox · 04-23-2017 04:53 PM

interesting, as a workaround you could also save UID.l value and store them in the DB and load it into UID(l1,l2) with a separator and Split, I'd imagine that would be just as fast/close to) as fromHex as Hex uses string as well Split isn't that slow TextULong would probably work just as fast as fromHex uses CharInt for every input.

Would that work? of course I don't think it would take much time for this behavior to get fixed.

Houge · 04-23-2017 04:54 PM

It would be complicated, why shall i use UID then?

Zervox · 04-23-2017 04:55 PM

Not that complicated, of course it would be better for it to get fixed, just thought I'd offer an alternative which isn't that much slower until it got fixed. smile

Houge · 04-23-2017 05:00 PM

If i use UID as primary key and for example Char ID, then i need to change all foreign keys when it gets fixed smile

Zervox · 04-23-2017 05:49 PM

Well, maybe I should've started with depending on your db setup it isn't necessarily that complicated.

Houge · 04-23-2017 06:33 PM

I make normalized database using 3NF, I work with databases for almost 8 years and it's not complicated when it is designed well. There is no need to sacrifice design for temporary bugs smile

Database is a heart of a project, it should have integrity data and i don't need to check it's integrity because DB does everything faster and better.
I use pl/pgsql functions also and keep part of game logic there, because if i need to perform several operations in DB, it's better to make only one call from EE application to DB over network, it saves resources and time.

But all that does not matter in this topic, because i need UID to be fixed smile

Zervox · 04-23-2017 06:50 PM

just came to think of something, if you would check your UID l values, one which has the correct UID and one which has the loaded fromHex is it only one of the longs which doesnt match the value or is it both? I am guessing it just one of them that is not matching in which case a bit shuffling might be the perfect temp solution. smile

Edit:
even if it is both bit shuffling might be better.

Houge · 04-23-2017 06:54 PM

Using id.fromHex("12345678901234567890123456789012");

Before insert L1= 6211610197754262546 L2= 1337701400965189752
After insert L1= 3771360145258600056 L2= 1337701400965189752

To make it be the same after select i need to swap the following bytes:

Code:

Swap(id.b[0], id.b[3]);

Swap(id.b[1], id.b[2]);

Swap(id.b[4], id.b[5]);

Swap(id.b[6], id.b[7]);

L2 always stays the same.

Zervox · 04-23-2017 07:08 PM

well, after fixing that it makes it alot easier and less time for Greg to test. so I am sure he is happy about you doing this. smile

**Esenthel** · 04-23-2017 11:57 PM

Hi,

This is not a bug in the engine, it's only that the "2b179f70-3c29-11e4-abda-6fbacb471cca" is a -different kind- of hexadecimal text format.
If you ask me, this format is ridiculous because some numbers are big endian, some are little endian.

For this type of text format you need to use the hidden method:
Bool UID::fromGuid(C Str &text)
https://github.com/Esenthel/EsenthelEngi...c.cpp#L350

If you need badly this function, I can make access to it public, and not hidden.

**Esenthel** · 04-24-2017 05:42 AM

https://github.com/Esenthel/EsenthelEngi...36a53db52e

This commit makes the functions public

Houge · 04-24-2017 07:55 AM

Thank you very much! I knew that it wasn't a bug but i didn't know how to find it out smile

Yes, i need these functions because i want to keep UIDs in config files and it would be much easier to keep them in the same format as in DB smile

SQL and UID
Author	Message