MyRand



  • Just noticed this checkin, which immediately makes me want to throw an old tft at the person responsible:

    function myRand() // original isn't unique!
    {
    $id = rand();
    $sql =' SELECT * FROM tickets WHERE ticket_id='.db_input($id);
    while (($res=db_query($sql)) && db_num_rows($res))
    {
    $id = rand();
    $sql =' SELECT * FROM tickets WHERE ticket_id='.db_input($id);
    }
    if ($res)
    return $id;
    else
    return false;
    }
     

    (no, there wasn't any identation in the original)


  • Trolleybus Mechanic

    @Mole said:

    my

    That's as far as you should have read before firing the developer and having his hands dissolved in lye so he can never type again. I would go so far as a a forced gargle with steel wool, just in case he tries to program via voice recognition.

     



  • Are you trying to tell us that this is going to be the key for the next record in tickets? That is awesome! I don't know how many tickets you generate, but in due time this will grind to a halt, and consequently also get duplicate keys. Then everyone will be stumped and suddenly the original developer will jump in and say he can solve it. And then over the weekend he will just change this to a normal sequence or whatever your db has and everyone will praise him and give him a huge bonus. Perfect.



  •  Why would ticket IDs need to be random anyway?



  • Never mind.

     



  • @Mole said:

    function myRand() // original isn't unique!

    If this is the fixed version...



  • Returning 'false' when expecting an integral number is 'special.' Sometimes I wonder how loosely typed languages survive... (this statement coming from a former perl hack.)

    While not much better, a slightly more sane approach might look like:

    function newRand() {
      $sql = 'SELECT MAX(*) FROM tickets';
      $res = db_query($sql);
      $id = $whatever_php_is_needed_to_get_the_value_asked_for + 1;
      return $id;
    }
    

    However, if you've already got a lot of highish ids, you're in for a world of hurt if you don't redo ALL the ids.



  • @zelmak said:

    Returning 'false' when expecting an integral number is 'special.' Sometimes I wonder how loosely typed languages survive... (this statement coming from a former perl hack.)

    While not much better, a slightly more sane approach might look like:

    function newRand() {
      $sql = 'SELECT MAX(*) FROM tickets';
      $res = db_query($sql);
      $id = $whatever_php_is_needed_to_get_the_value_asked_for + 1;
      return $id;
    }
    

    However, if you've already got a lot of highish ids, you're in for a world of hurt if you don't redo ALL the ids.

    Or just delete the code and:

    NOTE: This is all in Postgres syntax.

    BEGIN;

    ALTER TABLE tickets ADD COLUMN new_ticket_id BIGSERIAL NOT NULL;

    UPDATE tickets SET new_ticket_id = ticket_id;

    ALTER SEQUENCE tickets_new_ticket_id_seq RESTART WITH (SELECT max(new_ticket_id) FROM tickets);

    ALTER TABLE tickets DROP COLUMN ticket_id;

    ALTER TABLE tickets RENAME COLUMN new_ticket_id TO ticket_id;

    COMMIT;

    Reason for BIGSERIAL? The randomly generated IDs have a high probability of generating at least one very high ticket_id value, and thus the highest current ticket_id value may be very close to the maximum value for SERIAL.



  • This is the prime reason I hate Oracle ... no auto-numbering IDENTITY columns. In order to do things that other developers take for granted, you have to be a DBA in order to create (a) the sequence and (b) a trigger to fetch the next sequence id and insert it with the new record. For every stinking table that you want to auto-number.

    Nothing enforces the 'auto-number' field when you use a sequence. Unless 'BIGSERIAL' does that for you ... then why the sequence? /confused

    Disclaimer: I haven't worked with PostgreSql so I know not of its ideosyncracies.

     



  • @zelmak said:

    you have to be a DBA in order to create (a) the sequence and (b) a trigger to fetch the next sequence id and insert it with the new record.
     

    Wha?

    Firstly, you don't need to be DBA to perform those two operations. You just need to have the right privs (CREATE SEQUENCE and CREATE TRIGGER) granted to your account.

    Secondly, you don't need a trigger to fetch the next sequence ID. That's what the psuedo-column SEQUENCE.NEXTVAL is for.

    @zelmak said:

    For every stinking table that you want to auto-number.

    I don't know why you'd want to auto-number a table[1], but I suspect Index-Organised Tables could assist in what you're trying to acheive.

    [1] I presume you mean "auto-number a PK in the table upon each insert", rather than create numerically-named tables. 


Log in to reply