CODEWITHSUNDEEP
phpmysqlcakephpqr-codeuuid
Jeroen
Jeroen

Reputation: 1638

mysql add unique uuid to existing table

I have a table with primary key an auto incremental integer. The table holds data of real world objects.

Now I want each row to have a uuid. I don't want to change the primary key. But I do want to be absolutely sure the assigned uuid doesn't get overwritten (So only create UUID on insert, not update)

I want to use this uuid as a QR code, so it needs be as unique to a record as the primary key is.

Any ideas how to do this?

(I'm using cakePHP2, so it might be behaviour like the created timestamp. This also only done with the original INSERT)

Upvotes: 1

Views: 1166

Answers (2)

Nunser
Nunser

Reputation: 4522

Purely cake options

There are many ways to do this...

The least code invasive I think, would be to add a check in beforeSave of the corresponding model, something like

public function beforeSave() {
    if (!$this->id && !isset($this->data[$this->alias][$this->primaryKey])) {
        // insert
        $this->data[$this->alias]['uuid'] = $this->createUUID();
    } else {
        // edit
        // if you don't want to do anything on insert, delete this "else"
    }
    return true;
}

(that code was copied from here).

Now, you didn't provided much details as to what the uuid should be (integer, length, readable word...). But let's assume it's a simple integer, if it's not, all the changes needs to be done here. In the same model, add a function to create the uuid

private function createUUID() {
    do {
        $uuid = rand(); //any logic you want to create the uuid, a md5, substring, hash...
        //check for no repetition
        $help = $this->find('first', array('conditions'=>array('uuid'=>$uuid)));
    } while(!empty($help));
    return $uuid;
}

And that way, you'll have an unique uuid, only added on insert, without changing the rest of the code. This is the solution I like.

Now, another way to do this, for example, would be to create the UUID function that returns a value, and using it in afterSave (check if it was an insert or an update) and do an update of the record with the uuid (you'll have the id of the new created record by then).

There's not much advantage of one versus the other... the beforeSaveone allows you to set the uuid field in the database as NOT NULL, while if you use the afterSave, you need to insert the record first, and the uuid should be null for that.

Without much detail, I recommend the beforeSave option, just because I of the uuid NOT NULL thing.

Upvotes: 2

Neil Hampton
Neil Hampton

Reputation: 1883

You could use an insert trigger on the table to set a uuid using the mysql UUID() function. You could the use a before update trigger to prevent the uuid from being changed (i.e. NEW.uuid=OLD.uuid in the trigger).

Upvotes: 0

Related Questions