Auto Increment is for WHIMPS!!!!



  • Have you ever needed
    to make an id field that changes automatically every time you add a row? Isn't
    it frustrating to come up with an algorithm for it?<o:p></o:p>

    <o:p> </o:p>

    Well, worry you should not! I have inherrited some code that takes care of all that nonsense, that I am sure can be implemented in .NET and Java just as well as PHP:<o:p></o:p>

    <o:p> </o:p>

    <?<o:p></o:p>

    require_once "real_mysql.inc";<o:p></o:p>

    /**<o:p></o:p>

     * Return the next id of a specified type<o:p></o:p>

     *<o:p></o:p>

     * @param string $type the group of the desired id<o:p></o:p>

     */<o:p></o:p>

    function get_unique_id($site,$type) {<o:p></o:p>

          $type_id=array(<o:p></o:p>

                'billing'=>1,<o:p></o:p>

          );<o:p></o:p>

          if(!isset($type_id[$type])) {<o:p></o:p>

                return false;<o:p></o:p>

          }<o:p></o:p>

          $id=$type_id[$type];<o:p></o:p>

          $db=new sitedb($site,'master');<o:p></o:p>

          $sql="update unique_id set row_id=last_insert_id(row_id+1) where id=$id";<o:p></o:p>

          $db->query($sql);<o:p></o:p>

          $ret=$db->last_inserted();<o:p></o:p>

          return $ret;<o:p></o:p>

    }<o:p></o:p>

    ?><o:p></o:p>

    <o:p> </o:p>

    <o:p> </o:p>

    On the other hand, you can try altering the table to have the field auto_increment…. I am sure it takes less CPU time, and is easier to maintain. Up to you. <o:p></o:p>

    <o:p> </o:p>

    <o:p> </o:p>

    Now – back to coding….<o:p></o:p>

    <o:p></o:p>



  • @YishTish said:

    require_once "real_mysql.inc";
     

    i would like to see what is in the "fake_mysql.inc" or does he have a "float_mysql.inc" as well ...



  • I needed to implement several of these auto-incrementing algorithms. Of course, most of current database systems offer some way of automagically assigning unique ids ("sequencing") to new rows in a table, but only some of them are useful, at least in some contexts.

    As far as I know,  sequencing is implemented in Access and IBM DB2 such that you define a field in a table as being the unique index and auto-indexing, and when inserting new rows, you just leave out that field, and the database fills it in automatically. The problem is that you don't know what id has been assigned until "after the fact". If you don't have other unique field combinations in that table, then you don't have an easy way to find out the exact id assigned to the last row inserted. Which means that you can't easily insert rows in several, linked tables at once.

    Oracle is the only database I know which has a way to find out a new id before adding the row, and thus you are able to use the new id both for inserting the row in table 1, and use that id as a foreign key to table 1 in a new record in table 2. 

    So, yes, I implemented sequencing mechanisms in several different projects several times, simply to be sure that whatever underlying database we use, this will always work, not only under Oracle. In that sense: No, I don't consider this a WTF.



  • @TheRider said:

    The problem is that you don't know what id has been assigned until "after the fact".
    Given a reasonable database schema, I'm failing to see the problem.

    Do you have a live/contrived example where you feel this is a problem? 



  • @Nelle said:

    @YishTish said:

    require_once "real_mysql.inc";
     

    i would like to see what is in the "fake_mysql.inc" or does he have a "float_mysql.inc" as well ...

    It's worse than than. real_mysql.inc contains some 'improvements' to the normal MySQL by, of course, RealMedia.



  • @TheRider said:

    and thus you are able to use the new id both for inserting the row in table 1, and use that id as a foreign key to table 1 in a new record in table 2.

    Some other databases have a query for retrieving the last ID generated, such as SELECT @@identity



  • @PJH said:

    @TheRider said:

    The problem is that you don't know what id has been assigned until "after the fact".
    Given a reasonable database schema, I'm failing to see the problem.

    Do you have a live/contrived example where you feel this is a problem? 

    Yes, I do

    Imagine the following two tables:

    table ORDER, with fields id (autoincrement), customerid (foreign key to CUSTOMER), orderdate

    table ORDERENTRY, with fields id (autoincrement), orderid (foreign key to ORDER), articleid (foreign key to ARTICLE), itemcount, price

     

    To enter a new order with an order entry into the database, you have to do something like:

    insert into ORDER (customerid, orderdate) values ('cust-1234', current date);

    insert into ORDERENTRY (orderid, articleid, itemcount, price) values (?!?, 'article-5678', 5, 99.95);

    ==> what is the number that you insert into the orderid field? You cannot reliably find out what id was assigned to the new ORDER row, particularly if, for example, two orders have been entered for the same customer on the same date. Except, of course, if there is some mechanism like the one AbbydonKrafts is describing here:

    @AbbydonKrafts said:

    @TheRider said:
    and thus you are
    able to use the new id both for inserting the row in table 1, and use
    that id as a foreign key to table 1 in a new record in table
    2. 

    Some other databases have a query for retrieving the last ID generated,
    such as SELECT @@identity

    I don't know by heart, but in oracle you would do something like this:

    select new-sequence-number for table ORDER  -- the exact syntax can surely be found in the Oracle manuals...

    ==> this would return a new uniqe key for said table, such as 4711. 

    insert into ORDER (id, customerid, orderdate) values (4711, 'cust-1234', current date);

    insert into ORDERENTRY (orderid, articleid, itemcount, price) values (4711, 'article-5678', 5, 99.95); 



  • @TheRider said:

    Oracle is the only database I know which has a way to find out a new id before adding the row, and thus you are able to use the new id both for inserting the row in table 1, and use that id as a foreign key to table 1 in a new record in table 2.

    I'm hoping the Oracle guys were smart enough to increment the ID the moment it is requested, because otherwise: race conditions ahoy!

    DB        :  new ID;
    DB->ProcA : ID;
    DB->ProcB : ID;
    ProcA->DB : (ID, ...);
    DB : new ID;
    procB->DB : (ID, ...); // Uh-oh!


  • @PJH said:

    Given a reasonable database schema...

    Therein is often the problem. In my last job, I had to integrate with a rubbish call logging "system" that didn't do autoincrements. Instead, it stored a "last_used" value in a totally separate database. It was one of the many causes of my work-related Tourette's at the time.



  • @TheRider said:

     

    I don't know by heart, but in oracle you would do something like this:

    select new-sequence-number for table ORDER  -- the exact syntax can surely be found in the Oracle manuals...

    ==> this would return a new uniqe key for said table, such as 4711. 

    I don't know oracle, but doesn't this give the opportunity for two process to have the same ID?

    Process1 askes for next ID, Process2 asks for next ID.  Process1 saves, Process2 saves.

    Do they have the same ID?  If they don't, what happens to the ID process1 grabbed if it decides not to save or fails?  This method seems to cause problems.

    MS SQL uses @@Scope_Identity (don't use @@identity as that is not session specific) to grab the latest ID the current session created.  So you insert your parent, request it's ID and save the child.  No loss of ID's and no stepping on other process and all this without abiguity.



  • @TheRider said:

    insert into ORDER (customerid, orderdate) values ('cust-1234', current date);

    insert into ORDERENTRY (orderid, articleid, itemcount, price) values (?!?, 'article-5678', 5, 99.95);

     

    MySQL :

    START TRANSACTION;
    INSERT INTO ORDER (customerid, orderdate) VALUES ('cust-1234', current date);
    SELECT @order_id = LAST_INSERT_ID();
    INSERT INTO ORDERENTRY (orderid, articleid, itemcount, price) VALUES (@order_id, 11, 1, 2.0)
    COMMIT;

    TransactSQL: 

    DECLARE @order_id INT;
    BEGIN TRANSACTION;
    INSERT INTO ORDER (customerid, orderdate) VALUES ('cust-1234', current date); SELECT @order_id = SCOPE_IDENTITY(); INSERT INTO ORDERENTRY (orderid, articleid, itemcount, price) VALUES (@order_id, 11, 1, 2.0) COMMIT TRANSACTION;

    So what exactly is the problem ?



  • @Nelle said:

    So what exactly is the problem ?

    Lack of knowledge of the tools people use.



  • @TheRider said:

    To enter a new order with an order entry into the database, you have to do something like:

    insert into ORDER (customerid, orderdate) values ('cust-1234', current date);

    ... retreive id from last inserted record and use in the next statement...


    insert into ORDERENTRY (orderid, articleid, itemcount, price) values (?!?, 'article-5678',<wbr> 5, 99.95);

    what is the number that you insert into the orderid field?

      The one retrieved after the first insert. I'm still failing to see the problem.

     @TheRider said:

    You cannot reliably find out what id was assigned to the new ORDER row,
    In which database product? MySQL for example provides mysql_insert_id() which would be used in the above situtation

    particularly if, for example, two orders have been entered for the same customer on the same date.
    The two orders would be presumably be created in distinct threads/whatever with their own order ids and order items (and results from mysql_insert_id() since they are per connection, and one would assume the insert then id-retrieval above would be in the same context.) I don't see a conflict with this example.

     



  • @m0ffx said:

    It's worse than than. real_mysql.inc contains some 'improvements' to the normal MySQL by, of course, RealMedia.
     

    Uhoh...

    INSERT INTO sometable {BUFFERING.... BUFFERING... BUFFERING...} (id, somefield) {WOULD YOU LIKE TO UPGRADE TO MYSQLPLUS?} VALUES (12, '...{ARE YOU SURE YOU DON''T WANT TO UPGRADe!?!?!?!?! IT REALLY IS SO MUCH BETTER});

     



  • I don't think we can lay all the blame on the original coder.  The MySQL manual actually [url=http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id]suggests this technique[/url] (scroll down a bit)



  • @jnz said:

    I don't think we can lay all the blame on the original coder.  The MySQL manual actually suggests this technique (scroll down a bit)

    It looks like that's just a method to emulate sequences which normally shouldn't be needed in MySQL.  Auto-increment works just fine as-is and I can't imagine a situation where "rolling your own" would be preferable.  Just because MySQL provides a way to do it doesn't mean it's a great idea. 



  • @jnz said:

    The MySQL manual actually suggests this technique (scroll down a bit)
    Could you identify the start of the section you allude to? The closest I can see is the part starting


    Important

              If you insert multiple rows using a single
              <code class="literal">INSERT</code> statement,
              <a href="http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id"><code class="literal">LAST_INSERT_ID(<wbr>)</code></a> returns
              the value generated for the <span class="emphasis"><i>first</i></span>
              inserted row <span class="emphasis"><i>only</i></span>.</blockquote></p><p>Which isn't a reason for the code used in the OP.&nbsp;</p>


  • @TheRider said:

    select new-sequence-number for table ORDER  -- the exact syntax can surely be found in the Oracle manuals...

    ==> this would return a new uniqe key for said table, such as 4711. 

    insert into ORDER (id, customerid, orderdate) values (4711, 'cust-1234', current date);

    insert into ORDERENTRY (orderid, articleid, itemcount, price) values (4711, 'article-5678', 5, 99.95); 

     

     Oracle has it much easier. You can always do...

    insert into table ... returning fieldlist into variablelist;

     eg: insert into ORDER (customerid, orderdate) values ('cust-1234', current date) returning id into varID;

    the returning clause will fill the variables after the row has been inserted, all default fields populated and all triggers have run

     Cheers!



  • @Ragnax said:

    I'm hoping the Oracle guys were smart enough to increment the ID the moment it is requested, because otherwise: race conditions ahoy!

    You must be joking.

    Of course sequences (that's how those number generators are called) are smart enough. In fact, it would be hard (though not impossible) to build something like that on your own.  Sequences are incremented the moment they are queried, but the access to the sequence is non-blocking. So another transaction can query the next number withouth ever creating a collission. If the transaction is rolled back, the number is still gone, obviously. If a sequence is queried very often, it can be set to "caching"; so if you retrieve one number, the sequence is increased by e.g. 20; the next 19 numbers can than be retrieved with less effort. Sequences can have a lower bound and an upper bound, they can cycle or simply stop if they reach the upper bound.



  • @Nelle said:

    MySQL :

    SELECT @order_id = LAST_INSERT_ID();

    TransactSQL: 

    SELECT @order_id = SCOPE_IDENTITY();
    

    So what exactly is the problem ?

    Ok, I fold.

    TRWTF is, then, that there is no standardized way to do it (like, in a SQL standard) so that every db maker had to roll their own. And I still maintain that MySQL and TransactSQL's way is more quirky than Oracle's.

    @Ragnax said:

    I'm hoping the Oracle guys were smart enough to increment the ID the moment it is requested, because otherwise: race conditions ahoy!
    Yes, they were. That function is not implemented as a simple SELECT MAX(id)+1 FROM TABLE. Thanks, ammoQ, for more in-depth.

     


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.