TableGenerator.php 5.1 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145
  1. <?php
  2. namespace Doctrine\DBAL\Id;
  3. use Doctrine\DBAL\Connection;
  4. use Doctrine\DBAL\DBALException;
  5. use Doctrine\DBAL\DriverManager;
  6. use Doctrine\DBAL\FetchMode;
  7. use Doctrine\DBAL\LockMode;
  8. use Throwable;
  9. use const CASE_LOWER;
  10. use function array_change_key_case;
  11. /**
  12. * Table ID Generator for those poor languages that are missing sequences.
  13. *
  14. * WARNING: The Table Id Generator clones a second independent database
  15. * connection to work correctly. This means using the generator requests that
  16. * generate IDs will have two open database connections. This is necessary to
  17. * be safe from transaction failures in the main connection. Make sure to only
  18. * ever use one TableGenerator otherwise you end up with many connections.
  19. *
  20. * TableID Generator does not work with SQLite.
  21. *
  22. * The TableGenerator does not take care of creating the SQL Table itself. You
  23. * should look at the `TableGeneratorSchemaVisitor` to do this for you.
  24. * Otherwise the schema for a table looks like:
  25. *
  26. * CREATE sequences (
  27. * sequence_name VARCHAR(255) NOT NULL,
  28. * sequence_value INT NOT NULL DEFAULT 1,
  29. * sequence_increment_by INT NOT NULL DEFAULT 1,
  30. * PRIMARY KEY (sequence_name)
  31. * );
  32. *
  33. * Technically this generator works as follows:
  34. *
  35. * 1. Use a robust transaction serialization level.
  36. * 2. Open transaction
  37. * 3. Acquire a read lock on the table row (SELECT .. FOR UPDATE)
  38. * 4. Increment current value by one and write back to database
  39. * 5. Commit transaction
  40. *
  41. * If you are using a sequence_increment_by value that is larger than one the
  42. * ID Generator will keep incrementing values until it hits the incrementation
  43. * gap before issuing another query.
  44. *
  45. * If no row is present for a given sequence a new one will be created with the
  46. * default values 'value' = 1 and 'increment_by' = 1
  47. */
  48. class TableGenerator
  49. {
  50. /** @var Connection */
  51. private $conn;
  52. /** @var string */
  53. private $generatorTableName;
  54. /** @var mixed[][] */
  55. private $sequences = [];
  56. /**
  57. * @param string $generatorTableName
  58. *
  59. * @throws DBALException
  60. */
  61. public function __construct(Connection $conn, $generatorTableName = 'sequences')
  62. {
  63. $params = $conn->getParams();
  64. if ($params['driver'] === 'pdo_sqlite') {
  65. throw new DBALException('Cannot use TableGenerator with SQLite.');
  66. }
  67. $this->conn = DriverManager::getConnection($params, $conn->getConfiguration(), $conn->getEventManager());
  68. $this->generatorTableName = $generatorTableName;
  69. }
  70. /**
  71. * Generates the next unused value for the given sequence name.
  72. *
  73. * @param string $sequenceName
  74. *
  75. * @return int
  76. *
  77. * @throws DBALException
  78. */
  79. public function nextValue($sequenceName)
  80. {
  81. if (isset($this->sequences[$sequenceName])) {
  82. $value = $this->sequences[$sequenceName]['value'];
  83. $this->sequences[$sequenceName]['value']++;
  84. if ($this->sequences[$sequenceName]['value'] >= $this->sequences[$sequenceName]['max']) {
  85. unset($this->sequences[$sequenceName]);
  86. }
  87. return $value;
  88. }
  89. $this->conn->beginTransaction();
  90. try {
  91. $platform = $this->conn->getDatabasePlatform();
  92. $sql = 'SELECT sequence_value, sequence_increment_by'
  93. . ' FROM ' . $platform->appendLockHint($this->generatorTableName, LockMode::PESSIMISTIC_WRITE)
  94. . ' WHERE sequence_name = ? ' . $platform->getWriteLockSQL();
  95. $stmt = $this->conn->executeQuery($sql, [$sequenceName]);
  96. $row = $stmt->fetch(FetchMode::ASSOCIATIVE);
  97. if ($row !== false) {
  98. $row = array_change_key_case($row, CASE_LOWER);
  99. $value = $row['sequence_value'];
  100. $value++;
  101. if ($row['sequence_increment_by'] > 1) {
  102. $this->sequences[$sequenceName] = [
  103. 'value' => $value,
  104. 'max' => $row['sequence_value'] + $row['sequence_increment_by'],
  105. ];
  106. }
  107. $sql = 'UPDATE ' . $this->generatorTableName . ' ' .
  108. 'SET sequence_value = sequence_value + sequence_increment_by ' .
  109. 'WHERE sequence_name = ? AND sequence_value = ?';
  110. $rows = $this->conn->executeUpdate($sql, [$sequenceName, $row['sequence_value']]);
  111. if ($rows !== 1) {
  112. throw new DBALException('Race-condition detected while updating sequence. Aborting generation');
  113. }
  114. } else {
  115. $this->conn->insert(
  116. $this->generatorTableName,
  117. ['sequence_name' => $sequenceName, 'sequence_value' => 1, 'sequence_increment_by' => 1]
  118. );
  119. $value = 1;
  120. }
  121. $this->conn->commit();
  122. } catch (Throwable $e) {
  123. $this->conn->rollBack();
  124. throw new DBALException('Error occurred while generating ID with TableGenerator, aborted generation: ' . $e->getMessage(), 0, $e);
  125. }
  126. return $value;
  127. }
  128. }