TableGenerator.php 5.1 KB

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