About Store Forum Documentation Contact



Post Reply 
SQL and UID
Author Message
Houge Offline
Member

Post: #1
SQL and UID
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
(This post was last modified: 04-23-2017 06:46 PM by Houge.)
04-23-2017 04:01 PM
Visit this user's website Find all posts by this user Quote this message in a reply
Zervox Offline
Member

Post: #2
RE: SQL and UID
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.
(This post was last modified: 04-23-2017 04:54 PM by Zervox.)
04-23-2017 04:53 PM
Find all posts by this user Quote this message in a reply
Houge Offline
Member

Post: #3
RE: SQL and UID
It would be complicated, why shall i use UID then?
04-23-2017 04:54 PM
Visit this user's website Find all posts by this user Quote this message in a reply
Zervox Offline
Member

Post: #4
RE: SQL and UID
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
04-23-2017 04:55 PM
Find all posts by this user Quote this message in a reply
Houge Offline
Member

Post: #5
RE: SQL and UID
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
(This post was last modified: 04-23-2017 05:00 PM by Houge.)
04-23-2017 05:00 PM
Visit this user's website Find all posts by this user Quote this message in a reply
Zervox Offline
Member

Post: #6
RE: SQL and UID
Well, maybe I should've started with depending on your db setup it isn't necessarily that complicated.
(This post was last modified: 04-23-2017 05:49 PM by Zervox.)
04-23-2017 05:49 PM
Find all posts by this user Quote this message in a reply
Houge Offline
Member

Post: #7
RE: SQL and UID
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
(This post was last modified: 04-23-2017 06:33 PM by Houge.)
04-23-2017 06:33 PM
Visit this user's website Find all posts by this user Quote this message in a reply
Zervox Offline
Member

Post: #8
RE: SQL and UID
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.
(This post was last modified: 04-23-2017 06:52 PM by Zervox.)
04-23-2017 06:50 PM
Find all posts by this user Quote this message in a reply
Houge Offline
Member

Post: #9
RE: SQL and UID
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.
(This post was last modified: 04-23-2017 07:04 PM by Houge.)
04-23-2017 06:54 PM
Visit this user's website Find all posts by this user Quote this message in a reply
Zervox Offline
Member

Post: #10
RE: SQL and UID
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
04-23-2017 07:08 PM
Find all posts by this user Quote this message in a reply
Esenthel Offline
Administrator

Post: #11
RE: SQL and UID
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.
04-23-2017 11:57 PM
Find all posts by this user Quote this message in a reply
Esenthel Offline
Administrator

Post: #12
RE: SQL and UID
https://github.com/Esenthel/EsenthelEngi...36a53db52e

This commit makes the functions public
04-24-2017 05:42 AM
Find all posts by this user Quote this message in a reply
Houge Offline
Member

Post: #13
RE: SQL and UID
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
04-24-2017 07:55 AM
Visit this user's website Find all posts by this user Quote this message in a reply
Post Reply