TableGenerator.php 6.11 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
<?php
/*
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
 * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
 * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
 * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
 * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
 * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
 * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
 * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
 * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
 * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 *
 * This software consists of voluntary contributions made by many individuals
Benjamin Eberlei's avatar
Benjamin Eberlei committed
16
 * and is licensed under the MIT license. For more information, see
17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41
 * <http://www.doctrine-project.org>.
 */

namespace Doctrine\DBAL\Id;

use Doctrine\DBAL\DriverManager;
use Doctrine\DBAL\Connection;

/**
 * Table ID Generator for those poor languages that are missing sequences.
 *
 * WARNING: The Table Id Generator clones a second independent database
 * connection to work correctly. This means using the generator requests that
 * generate IDs will have two open database connections. This is necessary to
 * be safe from transaction failures in the main connection. Make sure to only
 * ever use one TableGenerator otherwise you end up with many connections.
 *
 * TableID Generator does not work with SQLite.
 *
 * The TableGenerator does not take care of creating the SQL Table itself. You
 * should look at the `TableGeneratorSchemaVisitor` to do this for you.
 * Otherwise the schema for a table looks like:
 *
 * CREATE sequences (
 *   sequence_name VARCHAR(255) NOT NULL,
42 43 44
 *   sequence_value INT NOT NULL DEFAULT 1,
 *   sequence_increment_by INT NOT NULL DEFAULT 1,
 *   PRIMARY KEY (sequence_name)
45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81
 * );
 *
 * Technically this generator works as follows:
 *
 * 1. Use a robust transaction serialization level.
 * 2. Open transaction
 * 3. Acquire a read lock on the table row (SELECT .. FOR UPDATE)
 * 4. Increment current value by one and write back to database
 * 5. Commit transaction
 *
 * If you are using a sequence_increment_by value that is larger than one the
 * ID Generator will keep incrementing values until it hits the incrementation
 * gap before issuing another query.
 *
 * If no row is present for a given sequence a new one will be created with the
 * default values 'value' = 1 and 'increment_by' = 1
 *
 * @author Benjamin Eberlei <kontakt@beberlei.de>
 */
class TableGenerator
{
    /**
     * @var \Doctrine\DBAL\Connection
     */
    private $conn;

    /**
     * @var string
     */
    private $generatorTableName;

    /**
     * @var array
     */
    private $sequences = array();

    /**
Benjamin Morel's avatar
Benjamin Morel committed
82 83 84 85
     * @param \Doctrine\DBAL\Connection $conn
     * @param string                    $generatorTableName
     *
     * @throws \Doctrine\DBAL\DBALException
86 87 88 89 90 91 92 93 94 95 96 97
     */
    public function __construct(Connection $conn, $generatorTableName = 'sequences')
    {
        $params = $conn->getParams();
        if ($params['driver'] == 'pdo_sqlite') {
            throw new \Doctrine\DBAL\DBALException("Cannot use TableGenerator with SQLite.");
        }
        $this->conn = DriverManager::getConnection($params, $conn->getConfiguration(), $conn->getEventManager());
        $this->generatorTableName = $generatorTableName;
    }

    /**
Benjamin Morel's avatar
Benjamin Morel committed
98 99 100 101 102
     * Generates the next unused value for the given sequence name.
     *
     * @param string $sequenceName
     *
     * @return integer
103
     *
Benjamin Morel's avatar
Benjamin Morel committed
104
     * @throws \Doctrine\DBAL\DBALException
105 106 107 108 109 110 111 112 113
     */
    public function nextValue($sequenceName)
    {
        if (isset($this->sequences[$sequenceName])) {
            $value = $this->sequences[$sequenceName]['value'];
            $this->sequences[$sequenceName]['value']++;
            if ($this->sequences[$sequenceName]['value'] >= $this->sequences[$sequenceName]['max']) {
                unset ($this->sequences[$sequenceName]);
            }
Benjamin Morel's avatar
Benjamin Morel committed
114

115 116 117 118 119 120 121 122 123 124 125 126
            return $value;
        }

        $this->conn->beginTransaction();

        try {
            $platform = $this->conn->getDatabasePlatform();
            $sql = "SELECT sequence_value, sequence_increment_by " .
                   "FROM " . $platform->appendLockHint($this->generatorTableName, \Doctrine\DBAL\LockMode::PESSIMISTIC_WRITE) . " " .
                   "WHERE sequence_name = ? " . $platform->getWriteLockSQL();
            $stmt = $this->conn->executeQuery($sql, array($sequenceName));

127 128 129
            if ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
                $row = array_change_key_case($row, CASE_LOWER);

130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157
                $value = $row['sequence_value'];
                $value++;

                if ($row['sequence_increment_by'] > 1) {
                    $this->sequences[$sequenceName] = array(
                        'value' => $value,
                        'max' => $row['sequence_value'] + $row['sequence_increment_by']
                    );
                }

                $sql = "UPDATE " . $this->generatorTableName . " ".
                       "SET sequence_value = sequence_value + sequence_increment_by " .
                       "WHERE sequence_name = ? AND sequence_value = ?";
                $rows = $this->conn->executeUpdate($sql, array($sequenceName, $row['sequence_value']));

                if ($rows != 1) {
                    throw new \Doctrine\DBAL\DBALException("Race-condition detected while updating sequence. Aborting generation");
                }
            } else {
                $this->conn->insert(
                    $this->generatorTableName,
                    array('sequence_name' => $sequenceName, 'sequence_value' => 1, 'sequence_increment_by' => 1)
                );
                $value = 1;
            }

            $this->conn->commit();

158
        } catch (\Exception $e) {
159
            $this->conn->rollBack();
Pascal Borreli's avatar
Pascal Borreli committed
160
            throw new \Doctrine\DBAL\DBALException("Error occurred while generating ID with TableGenerator, aborted generation: " . $e->getMessage(), 0, $e);
161 162 163 164 165
        }

        return $value;
    }
}