Re: Unique Fields in Database
Re: Unique Fields in Database
- Subject: Re: Unique Fields in Database
- From: Neil MacLennan <email@hidden>
- Date: Mon, 21 Jan 2008 09:54:29 +0000
Thanks for the suggestions.
__UUID__
The problem is that a UUID is too long at 32 hex chars, and shortening
a freshly generated UUID doesn't guarantee uniqueness any more.
__MySQL AutoIncrement__
This will then give me the next 'guessable' ID in the sequence, which
presents a security issue. My 6-character codes are customer-facing. I
could append an incrementing integer to a random string of characters
to create a 'random' incrementing code number
e.g. DYAV2-56, ICV3G-57 but when I get to large numbers, which I
will, then FS8W8-18532, begins get too long again to be a useful
customer-facing identifier. It also gives away their position in the
sales sequence, which I'd rather avoid.
I could perhaps turn the incrementing integer into hex, I save one
character space on average and the number in the sequence is less
visible to the average Joe. I'll still need to increase the overall
string length so that the next number in the sequence isn't easily
computed.
I'll happily save the discussion on how to implement secure-customer-
facing-fields to an [OT] thread, but in order to stay relevant to this
list, I'm more interested in data uniqueness between WO and the
database.
Cheers,
.neilmac
On 21 Jan 2008, at 03:48, Jerome Chan wrote:
If you are using mysql, can you use an incrementing integer ?
On 21 Jan 2008, at 02:49, Jake MacMullin wrote:
Have you thought about using a Universally Unique Identifier (UUID)?
http://java.sun.com/j2se/1.5.0/docs/api/java/util/UUID.html
Regards,
Jake
On 21/01/2008, at 1:06 PM, Neil MacLennan wrote:
Following on from the postings on subject, "Avoiding duplicate
records" can I get my understanding right? (so that I don't waste
any more time trying to fix what can't be fixed):
I'm looking for, say, a six-character unique field in my database
record. I generate this randomly so that each is unpredictable (at
least within 36^6 guesses).
As I understand it, there's no way to:
i) generate the random character string,
ii) check for uniqueness in the database
iii) insert it into the database with the guarantee that no other
instance has co-incidentally inserted the same string in the
milliseconds between ii) and iii)
Currently I have a stored procedure (in MySQL) finding me a free
random string (I figure that this will be quicker than WO trying to
find me one) and then inserting that String into my EO and saving
it. I then look for a EOGeneralAdaptorException on saveChanges()
and parse it for MySQL's error code for failing a UNIQUE constraint
on the DB (error code: 1062). If an exception is thrown, I go back
an look for another free string from the stored procedure. I do
this in a loop repeating 50 times, at which point I give up and
throw the Exception right up to the user interface level as a
"problem".
I'll only be using 0.1% of the available key space for a 6-
character alpha-num string, so I figure that a loop of 50 will be
sufficient to always find me an available string. But, in the
general case, it just seems "undesirable" as a solution.
Is this the best I can hope for?
.neilmac
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden