generating unique id for tuples in a database

saqib

Hi,
I am working on a complex database application that has multiple relations. Right now I am using mySQL. And I am also using auto_increment for the unique ID for each table. How I would prefer not to use auto_increment.

I would like a external program to give me unique IDs and manage all the IDs for me as well. I would like the application to be database independent, i.e. it should work on Oracle etc as well.

Any suggestions?

Thanks
Saqib Ali
http://docbook.sc-icc.org

daphreeek

this is probably not what you want, but for security reasons i use 10 digit random numbers so its harder to guess than numbers in order.

I do this by using PHPs random function and storing the value to a variable then entering it into the different tables I want it in.

eg: i had 2 tables, members and logins - each had an id field and only one entry per user - the id field related these tables. i made a random number then an entry in each table with that number

Hope that helps
- Matt

nemonoman

I missed Oracle's SEQUENCE function and made an artificial MySQL version. I use the following code, which returns a unique sequence. You can specify different sequence entities (e.g. 'product', 'order', 'customer'), or none at all. It looks like it would take forever to run but is in fact quite fast.

function getuniqueid ($type) {

        $table='unique'.$type.'id';

    $theuser = "'".md5(uniqid(rand(), 1))."'";
    $thequery = '';
    $thequery  .= '   INSERT INTO  '.$table ;
    $thequery  .= '   SET         name =';
    $thequery  .= $theuser;
    $result = dosql($thequery);
    if((!$result) && (mysql_errno()==1146)){
       $result=dosql("CREATE TABLE $table (id int(11) NOT NULL auto_increment,
                         name varchar(70) default NULL,
                         PRIMARY KEY  (id)) ");
       $result = dosql($thequery);
       }
    $thequery = '';
    $thequery  .= '   SELECT      MAX( id ) ';
    $thequery  .= '   FROM          '.$table ;
    $thequery  .= '   WHERE       name = ';
    $thequery  .= $theuser;
    $result = dosql($thequery);
    $row = mysql_fetch_array($result);

     $uniqueid = $row[0];

    $thequery = '';
    $thequery  .= '   DELETE ';
    $thequery  .= '   FROM          '.$table ;
    $thequery  .= '   WHERE       name = ';
    $thequery  .= $theuser;
    $result = dosql($thequery);
    return $uniqueid;
}

saqib

Hi nemonoman,
I have tried using MD5 before. However, about 1000+ queries are made each time a user makes a request to our application. With MD5 the session PHP session usually times out. I have even the execution time to 600 in the PHP_INC file. But still it is too slow, even if the processing completes. That is why i don't want to use md5.

Without MD5 our applications run much faster.

In Peace
Saqib Ali
http://docbook.sc-icc.org

nemonoman

you don't need to use Md5, I probably should have left that out entirely

Any unique value applied to the variable $user will do the trick, for example:

$theuser=uniquid();

I had simply cut and pasted from a unique encoded value generator I use for session management. In the case of this code snip, it's way overkill.

[deleted]

Just use auto_increment.

An external program CANNOT give you truely unique id because only the database knows which ID's are in use.

Yes you can generate random numbers, but you MUST insert them into the database before you can be sure that it is unique.

"about 1000+ queries are made each time a user makes a request to our application."

1000+? What kind of application is that, and how drunk were you when you decided to use the world's worst database for that? 🙂

You say it times out, so you claim that you will generate a thousand unique id's for every request?

[deleted]

"Any unique value applied to the variable $user will do the trick, for example: "

No it won't, you must verify the number against the database, because no matter how unique you think the number is, it's really just a random number and random means it can happen any number of times.

nemonoman

Please read before you argue: I said UNIQUE value, not RANDOM value.

from the manual:

" Uniqid() returns a prefixed unique identifier "

Thank you.

[deleted]

"Please read before you argue: I said UNIQUE value, not RANDOM value"

LOL

THINK smartypants, PHP can ofcourse not generate a UNIQUE number if it does not know which numbers it has generated in the past. Or did you think there is some mathematical algorythm that universely generates unique numbers all the time?

The best thing PHP can do is take the current time, which only changes once every millisecond, and md5 that into some cryptic scring. So if you call this routine twice in the same millisecond.. boom, duplicate values.

Thank you.

nemonoman

Oh, gosh, let's look at the actual wording of the manual:

Uniqid() returns a prefixed unique identifier based on the current time in microseconds.

hmmm...microseconds, not milliseconds, think of that. And since these microsecond time numbers are generated sequentially by the same server, they'd be...let's see...UNIQUE! What a revelation!

Comments in the commented manual @ php.net suggests 4 or 5 alternate methods of generating a unique identifier.

Or do you think that the coders of PHP were so confused as to create a function called uniqid() that provided only an kindadifferentid() ?

If so, there is a method for you to comment, bug report, and even fix the language.

'Smartypants', eh? Pretty devestating, that. I haven't been called that since second grade.

[deleted]

Oh dear, did I mistake milli for micro? How delightfully irrelevant.

"And since these microsecond time numbers are generated sequentially by the same server, they'd be...let's see...UNIQUE! What a revelation! "

The are generated by the same SERVER, not by the same PROCESS.
Microtime is just a number that changes every microsecond, it has nothing to make it unique.
There is only an ILLUSION of uniqueness because you have to request the time twice in the same microsecond which to most peope seems impossible but as the manual states, SMP machines can run truely simultaneous processes, which will generate the exact same uniquid.

The bottom line is that no matter how unique you think a number is, it isn't unique untill you have verified it against all the numers you have generated before. Auto_increment and sequences do this by remembering what they generated last time, and then adding one to that. And since there is only ONCE PROCESS and it only handles incoming requests sequencially, the numbers it generates are truely unique.

"Or do you think that the coders of PHP were so confused as to create a function called uniqid() that provided only an kindadifferentid() ? "

Yes, that is exactly what they did.

Think of what happens to your fantastic solution if the server's internal clock is adjusted for dailyight-savings time when an ENTIRE HOUR happens twice. Care to work out how many duplicate microtime values that gives you?
And what about the every-day adjustments that most servers do to keep their clocks in sync in with other servers?

Databases don't offer sequences because there are fullproof alternatives, the offer them because there AREN'T any fullproof solutions. Or at least, none that can mach the speed an accuracy of sequences.

"Smartypants', eh? Pretty devestating, that. I haven't been called that since second grade."

I can think of a some more adult words if that will make you feel more at home.

nemonoman

Silly me. I just assumed, I guess out of complete ignorance, that a function might return the value as defined. Or that the people coding it would consider and work through the potential problems you describe before releasing it.

What other functions are wrong, by your lights? Are session ids also at risk?

Do you calculate your own value for pi?

Tell me, at what point should I refer to the manual for my information? Instead of seeking out your 'wiser' version?

Perhaps you would consider writing an 'amended and approved' PHP manual for all the smartypants out there.

Even so, in point of fact, if all the potential horrible disasters you describe as potential sources of error were to occur, my code would still work correctly.

Or did you not bother to actually read my code?

MySQL, to my knowledge, doesn't provide sequences, hence the workaround I provided.

The workaround is easily adapted to provide equivalents to Oracle's NEXTVAL and CURRVAL, which MySQL lacks.

Maybe that lack in itself is reason enough for you not to use it, in your mind. You have stated in other posts your disatisfaction with MySQL.

MySQL has many benefits which I for one find attractive; its ubiquity, availabiltiy, and cost provide advantages, particularly where it's the right size for the job.

For example, I don't need row locking, as I use other coding strategies that you would, I feel sure, would never approve of.

If you are not already an Oracle DBA, may I recommend that you become one? You certainly have the proper mindset.

The method I posted works as advertised. Use it with any unique id, and I defy you to generate duplicate values. In any real world situation, php's uniqid() function is adequate as a seed.

[deleted]

"Even so, in point of fact, if all the potential horrible disasters you describe as potential sources of error were to occur, my code would still work correctly. "

Nope, it will fail bacause the '$name' you generate is again not unique.

The only unique thing about it is the auto_increment, which you can also retrieve using the built-in function mysql_insert_id().

"MySQL, to my knowledge, doesn't provide sequences, hence the workaround I provided. "

MySQL has auto_increment, which is in effect the same as a sequence, with the only difference that you can only define one of them per table, and you cannot get a value from it without inserting a record first.

"Maybe that lack in itself is reason enough for you not to use it, in your mind. You have stated in other posts your disatisfaction with MySQL."

I use mysql on a daily basis, mainly to port code to something a bit more intelligent, like postgresql. And why? because many developers think like you: "MySQL is cute and cuddly and I don't need all the functions that postgresql and oracle offer"

Unfortunately they think so mainly because they have no clue about what those functions really are and what they can do.

"For example, I don't need row locking, as I use other coding strategies that you would, I feel sure, would never approve of. "

I take it you mean row-locking, and not row-level-locking which mysql already supports.

That's one of my arguments against mysql, you change your coding 'strategy' to fit the shortcomings of mysql. Is that a good thing you think?

Personally I'd rather just do SELECT FOR UPDATE than write lengthy and slow routines to manually lock rows, which mess up bigtime if a script exits prematurely.

" In any real world situation, php's uniqid() function is adequate as a seed."

Adequate perhaps, for small applications, but definately NOT UNIQUE.

nemonoman

Vincente writes:

MySQL has auto_increment, which is in effect the same as a sequence, with the only difference that you can only define one of them per table, and you cannot get a value from it without inserting a record first.

By your very description of it, it is clear that MySQL auto_increment DOESN'T parallel the useful CURRVAL and NEXTVAL sequences available from Oracle, which was what my code -- so much deprecated by you -- mimics.

Vincente, I still challenge you to put my code to the test -- put it on any server you choose, and hit it with whatever query mechanism you choose, and show me in any real-world situation a single reproducible failure. You can even use uniqid() during the Daylight Saving Time period, if you wish. It won't fail.

Not all languages provide all constructs for all purposes. Persons with knowledge and experience can often create hand-built functions that adequately mimic the built-in constructs of other languages. The shared experience of this activity often leads to a greater good -- more understanding, more wisdom.

Many people who post to this board find a combo of PHP and MySQL to be adequate for their needs. I even seen reasonably good websites built on such a platform. Some appear even to be reliable.

How can this be?

Perhaps others without your knowledge that these programs are inadequate, and broken beyond repair, and their very functions based on lies and deceit, simply use the tools, as instructed, and succeed.

Please excuse me if I have disturbed any of your cherished opinions with facts. I know how distrubing this can be for many people.

Surely you can comfort yourself by imagining how stupid I am, and how very, very smart you are by comparison.

dejansen

Embarassing thread to say the least, and I'm just skimming it... 🙁

mattr

The logic is not clear to me, can you explain it a bit?

[deleted]

Emberasseing yes, but a pretty good fight though! 🙂