#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2016
    Location
    Lakewood, WA
    Posts
    199
    Rep Power
    17

    To Transact Or Not To Transact


    I have a block of code that inserts a row.

    Before I insert that row, I check to see if the "event" that row belongs to already has a primary key.

    If it does, the row has a field that will hold a copy of that existing primary key.

    But if this is the initial record that begins to define that "event", there will be no primary key yet, so I create it.

    (Step One) As it stands now, if the key doesn't exist I create one and put it in a master table. Otherwise I query the master table and get the appropriate primary key.

    (Step Two) Then I insert the new row.

    If either of these steps were to fail for whatever reason, things are out of whack and not in a good way, either a master table without a key for an "event", or an "event" without a primary key. Such a situation would cause a cascade of pain.

    Should this be a transaction?

    The alternative is I could write code that checks everything.

    I'm leaning in the direction of transaction.

    Also, for a variety of reasons, I'm generating a UUID in accordance with RFC 4122 as the primary key.
    PHP Code:
    // Create UUID, in accordance with RFC 4122
    public function getUUIDv4() {
            
    $UUID sprintf('%04x%04x-%04x-%04x-%04x-%04x%04x%04x',
            
    mt_rand(00xffff), mt_rand(00xffff),
            
    mt_rand(00xffff),
            
    mt_rand(00x0fff) | 0x4000,
            
    mt_rand(00x3fff) | 0x8000,
            
    mt_rand(00xffff), mt_rand(00xffff), mt_rand(00xffff)
            );
            
    // We would like it all upper-case. Just an OCD thing.
            
    return strtoupper($UUID);
        } 
    Pretty standard...

    Ideas?
    Last edited by Arty Zifferelli; May 13th, 2017 at 12:01 AM.
  2. #2
  3. Lazy Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,322
    Rep Power
    9645
    Note that a bunch of random values together do not make a true UUID.

    Can you put a uniqueness constraint on whatever values you're using to look up records in the master table? Then you can attempt an INSERT IGNORE and use the number of rows inserted (0 or 1) to know whether the record already existed.
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2016
    Location
    Lakewood, WA
    Posts
    199
    Rep Power
    17
    Originally Posted by requinix
    Note that a bunch of random values together do not make a true UUID.
    Is there a better way? I believe this code generates a UUID that meets the specification.

    Notes in the php.net documentation suggest that this will produce numbers equivalent to com_create_guid which I can not use since my code does not run on a Windows platform. As well it is implied that it is equivalent to uuid_create in the PECL uuid extension.

    Alturnativly there is this (from the Notes for com_create_guid):
    PHP Code:
    function UUIDtest() {
        
        if (
    function_exists('openssl_random_pseudo_bytes') === true) {
            
    $data openssl_random_pseudo_bytes(16);
            
    $data[6] = chr(ord($data[6]) & 0x0f 0x40);    // set version to 0100
            
    $data[8] = chr(ord($data[8]) & 0x3f 0x80);    // set bits 6-7 to 10
            
    return vsprintf('%s%s-%s-%s-%s-%s%s%s'str_split(bin2hex($data), 4));
        }    
    }
    echo 
    UUIDtest(); 
    This second method uses better random numbers, but openssl_random_pseudo_bytes has to be available. Therefore:
    PHP Code:
    public function getUUIDv4() {
            if (
    function_exists('openssl_random_pseudo_bytes') === true) { 
                
    $data openssl_random_pseudo_bytes(16); 
                
    $data[6] = chr(ord($data[6]) & 0x0f 0x40);    // set version to 0100 
                
    $data[8] = chr(ord($data[8]) & 0x3f 0x80);    // set bits 6-7 to 10 
                
    $UUID vsprintf('%s%s-%s-%s-%s-%s%s%s'str_split(bin2hex($data), 4)); 
            } else {
                
    $UUID sprintf('%04x%04x-%04x-%04x-%04x-%04x%04x%04x',
                
    mt_rand(00xffff), mt_rand(00xffff),
                
    mt_rand(00xffff),
                
    mt_rand(00x0fff) | 0x4000,
                
    mt_rand(00x3fff) | 0x8000,
                
    mt_rand(00xffff), mt_rand(00xffff), mt_rand(00xffff)
                );
            }
            
    // We would like it all upper-case. Just an OCD thing.
            
    return strtoupper($UUID);
        } 
    What are your specific concerns / suggestions about the UUID I'm generating?

    As to the SQL question, I'm almost 100% thinking transaction.
    Last edited by Arty Zifferelli; May 13th, 2017 at 01:18 AM.
  6. #4
  7. Lazy Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,322
    Rep Power
    9645
    A transaction can certainly do it, but it sounds like a uniqueness constraint will also work and is less complex.

    My concerns are more about calling something a UUID when it isn't. Nit picking. https://en.wikipedia.org/wiki/Univer...que_identifier Fact is it's a randomly-generated identifier so whether it's a UUID or "UUID" isn't as important.
  8. #5
  9. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2016
    Location
    Lakewood, WA
    Posts
    199
    Rep Power
    17
    Originally Posted by requinix
    My concerns are more about calling something a UUID when it isn't.
    From the Wikipedia:
    Version 4 (random)

    A version 4 UUID is randomly generated. As in other UUIDs, four bits are used to indicate version 4, and 2 or 3 bits to indicate the variant (10 or 110 for variants 1 and 2, respectively). Thus, for variant 1 (that is, most UUIDs) a random version 4 UUID will have 6 predetermined variant and version bits, leaving 122 bits for the randomly-generated part, for a total of 2122, or 5.3x1036 (5.3 undecillion) possible version 4 variant 1 UUIDs. There are half as many possible version 4 variant 2 UUIDs (legacy GUIDs) because there is one less random bit available, 3 bits being consumed for the variant.

    Some pseudorandom number generators lack necessary entropy to produce sufficiently pseudorandom numbers. For example, the WinAPI GUID generator, which uses a pseudorandom number generator, has been shown to produce UUIDs which follow a predictable pattern. RFC 4122 advises that "distributed applications generating UUIDs at a variety of hosts must be willing to rely on the random number source at all hosts. If this is not feasible, the namespace variant should be used."
    So Version 4 specification does use random numbers, but that's a weakness based on how good the random number generator is. For my needs, it's vanishingly irrelevant, the unique constraint on the column in the master table, and perhaps the size of the table (probably around 300,000 rows at max), I don't think I'll have any collisions, the master table (and the "events" it tracks) are purged of old "events" according to a schedule.

    As to the SQL problem, the more I think about it, the more I want to acquire as much error data points as possible for the log file, and I can wrap a few things up in the error handler that would record the transaction failure. Or maybe I don't know what I'm doing, I'll find out
    Last edited by Arty Zifferelli; May 13th, 2017 at 01:32 AM.
  10. #6
  11. Lazy Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,322
    Rep Power
    9645
    Right. The second code sample that deals with the version part produces a true UUID, even if not in one of the nicer fancy forms. That said, those are more useful in situations with multiple machines generating them at once - by exchanging those bits for more randomness you're helping yourself out.

    By the way, if you don't have openssl_random_pseudo_bytes() then your machine is severely lacking. I would assume it exists and then let any crash indicate that you have work to do.
  12. #7
  13. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2016
    Location
    Lakewood, WA
    Posts
    199
    Rep Power
    17
    Well...

    By the way, if you don't have openssl_random_pseudo_bytes() then your machine is severely lacking. I would assume it exists and then let any crash indicate that you have work to do.
    This get's into something I don't expect or don't know, but what if my Com guys were to come to me and say "We hear OpenSSL is garbage, we want you to use something else"... I have no reason to believe that it will be a problem though.

    I don't have a reason to believe they would, but as I said to Sepodati in a different thread discussing implementation of Common Access Card authentication, the Com guys (our Communications Squadron manages the network and everything that runs on it) are territorial and I suspect don't like me in their chili so-to-speak in the first place, so I like to have options...
    Last edited by Arty Zifferelli; May 13th, 2017 at 02:21 AM.
  14. #8
  15. Lazy Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,322
    Rep Power
    9645
    PHP should be able to build against LibreSSL too, but otherwise I'd say you can wait cross that bridge until you come to it. Point is that OpenSSL provides crypto support for stuff like https and general SSL/TLS so you kinda need it anyways, and it's unlikely you'd have a machine with openssl installed on the system but not available for PHP too.

IMN logo majestic logo threadwatch logo seochat tools logo