CODEWITHSUNDEEP
phpsqlmathdoctrine-orm
user2746634
user2746634

Reputation:

What is the best way to avoid unique constraint collisions with doctrine?

The inner drawback with senquenced identifier for a SQL table is the possiblity for a end user to easily go all over your tables. Sometimes it is a problem.

One solution is to create a non-sequenced id, something non guessable for every row.

This id must be a unique field, obviously. I can use a random function to generate thoses uniques ids for every row but there is a probability that it collides with a previously set id. If it collides, the end user will perceive it as random bug...

Here is one simple solution to overcome this problem:

$keyValid = false;
while(!$keyValid) {
    // Create a new random key
    $newKey = generateKey();

    // Check if the key already exists in database
    $existingPotato = $em->getRepository('Potato')->findOneBy(array('key' => $newKey));
    if (empty($existingPotato)) {
        $keyValid = true;
    }
}

// Hooray, key is unique!

It forces me to make at least one SELECT statement everytime I want a new id.

So, is there a better, widely-accepted solution to this problem?

Alternatively, is there an optimised length for the id that make this problem irrelevant by making the collision probability negligable (for a 3,000,000 rows table)?

Upvotes: 3

Views: 1024

Answers (1)

Federkun
Federkun

Reputation: 36964

You can add a Custom id generation strategy to do it. You can implement it by creating a class that extends AbstractIdGenerator:

use Doctrine\ORM\Id\AbstractIdGenerator;

class NonSequencedIdGenerator extends AbstractIdGenerator
{
    public function generate(\Doctrine\ORM\EntityManager $em, $entity)
    {
        $class  = $em->getClassMetadata(get_class($entity));
        $entityName = $class->getName();

        do {
            // You can use uniqid(), http://php.net/manual/en/function.uniqid.php
            $id = generateKey();
        } while($em->find($entityName, $id));


        return $id;
    }
}

Then add it using annotations in your entity class:

/**
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="CUSTOM")
 * @ORM\CustomIdGenerator(class="NonSequencedIdGenerator")
 */
private $id;

But if your generateKey don't return an unique identifier you should check if it already exists anyway. To avoid this, you can use an UUID generator for the primary keys in your entity as well.

/**
 * @ORM\Id
 * @ORM\Column(type="guid", unique=true)
 * @ORM\GeneratedValue(strategy="UUID")
 */
private $id;

If you don't like this, you can create a new custom id generation that use UUID_SHORT, and use function like this to make it shorter.

use Doctrine\ORM\Id\AbstractIdGenerator;

class UuidShortGenerator extends AbstractIdGenerator
{
    public function generate(EntityManager $em, $entity)
    {
        $conn = $em->getConnection();
        return $conn->query('SELECT UUID_SHORT()')->fetchColumn(0);
    }
}

The problem here is that I don't think it's provides full portability.

Upvotes: 2

Related Questions