mysql-enums.rst 5.6 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189
  1. Mysql Enums
  2. ===========
  3. The type system of Doctrine 2 consists of flyweights, which means there is only
  4. one instance of any given type. Additionally types do not contain state. Both
  5. assumptions make it rather complicated to work with the Enum Type of MySQL that
  6. is used quite a lot by developers.
  7. When using Enums with a non-tweaked Doctrine 2 application you will get
  8. errors from the Schema-Tool commands due to the unknown database type "enum".
  9. By default Doctrine does not map the MySQL enum type to a Doctrine type.
  10. This is because Enums contain state (their allowed values) and Doctrine
  11. types don't.
  12. This cookbook entry shows two possible solutions to work with MySQL enums.
  13. But first a word of warning. The MySQL Enum type has considerable downsides:
  14. - Adding new values requires to rebuild the whole table, which can take hours
  15. depending on the size.
  16. - Enums are ordered in the way the values are specified, not in their "natural" order.
  17. - Enums validation mechanism for allowed values is not necessarily good,
  18. specifying invalid values leads to an empty enum for the default MySQL error
  19. settings. You can easily replicate the "allow only some values" requirement
  20. in your Doctrine entities.
  21. Solution 1: Mapping to Varchars
  22. -------------------------------
  23. You can map ENUMs to varchars. You can register MySQL ENUMs to map to Doctrine
  24. varchars. This way Doctrine always resolves ENUMs to Doctrine varchars. It
  25. will even detect this match correctly when using SchemaTool update commands.
  26. .. code-block:: php
  27. <?php
  28. $conn = $em->getConnection();
  29. $conn->getDatabasePlatform()->registerDoctrineTypeMapping('enum', 'string');
  30. In this case you have to ensure that each varchar field that is an enum in the
  31. database only gets passed the allowed values. You can easily enforce this in your
  32. entities:
  33. .. code-block:: php
  34. <?php
  35. /** @Entity */
  36. class Article
  37. {
  38. const STATUS_VISIBLE = 'visible';
  39. const STATUS_INVISIBLE = 'invisible';
  40. /** @Column(type="string") */
  41. private $status;
  42. public function setStatus($status)
  43. {
  44. if (!in_array($status, array(self::STATUS_VISIBLE, self::STATUS_INVISIBLE))) {
  45. throw new \InvalidArgumentException("Invalid status");
  46. }
  47. $this->status = $status;
  48. }
  49. }
  50. If you want to actively create enums through the Doctrine Schema-Tool by using
  51. the **columnDefinition** attribute.
  52. .. code-block:: php
  53. <?php
  54. /** @Entity */
  55. class Article
  56. {
  57. /** @Column(type="string", columnDefinition="ENUM('visible', 'invisible')") */
  58. private $status;
  59. }
  60. In this case however Schema-Tool update will have a hard time not to request changes for this column on each call.
  61. Solution 2: Defining a Type
  62. ---------------------------
  63. You can make a stateless ENUM type by creating a type class for each unique set of ENUM values.
  64. For example for the previous enum type:
  65. .. code-block:: php
  66. <?php
  67. namespace MyProject\DBAL;
  68. use Doctrine\DBAL\Types\Type;
  69. use Doctrine\DBAL\Platforms\AbstractPlatform;
  70. class EnumVisibilityType extends Type
  71. {
  72. const ENUM_VISIBILITY = 'enumvisibility';
  73. const STATUS_VISIBLE = 'visible';
  74. const STATUS_INVISIBLE = 'invisible';
  75. public function getSqlDeclaration(array $fieldDeclaration, AbstractPlatform $platform)
  76. {
  77. return "ENUM('visible', 'invisible') COMMENT '(DC2Type:enumvisibility)'";
  78. }
  79. public function convertToPHPValue($value, AbstractPlatform $platform)
  80. {
  81. return $value;
  82. }
  83. public function convertToDatabaseValue($value, AbstractPlatform $platform)
  84. {
  85. if (!in_array($value, array(self::STATUS_VISIBLE, self::STATUS_INVISIBLE))) {
  86. throw new \InvalidArgumentException("Invalid status");
  87. }
  88. return $value;
  89. }
  90. public function getName()
  91. {
  92. return self::ENUM_VISIBILITY;
  93. }
  94. }
  95. You can register this type with ``Type::addType('enumvisibility', 'MyProject\DBAL\EnumVisibilityType');``.
  96. Then in your entity you can just use this type:
  97. .. code-block:: php
  98. <?php
  99. /** @Entity */
  100. class Article
  101. {
  102. /** @Column(type="enumvisibility") */
  103. private $status;
  104. }
  105. You can generalize this approach easily to create a base class for enums:
  106. .. code-block:: php
  107. <?php
  108. namespace MyProject\DBAL;
  109. use Doctrine\DBAL\Types\Type;
  110. use Doctrine\DBAL\Platforms\AbstractPlatform;
  111. abstract class EnumType extends Type
  112. {
  113. protected $name;
  114. protected $values = array();
  115. public function getSqlDeclaration(array $fieldDeclaration, AbstractPlatform $platform)
  116. {
  117. $values = array_map(function($val) { return "'".$val."'"; }, $this->values);
  118. return "ENUM(".implode(", ", $values).") COMMENT '(DC2Type:".$this->name.")'";
  119. }
  120. public function convertToPHPValue($value, AbstractPlatform $platform)
  121. {
  122. return $value;
  123. }
  124. public function convertToDatabaseValue($value, AbstractPlatform $platform)
  125. {
  126. if (!in_array($value, $this->values)) {
  127. throw new \InvalidArgumentException("Invalid '".$this->name."' value.");
  128. }
  129. return $value;
  130. }
  131. public function getName()
  132. {
  133. return $this->name;
  134. }
  135. }
  136. With this base class you can define an enum as easily as:
  137. .. code-block:: php
  138. <?php
  139. namespace MyProject\DBAL;
  140. class EnumVisibilityType extends EnumType
  141. {
  142. protected $name = 'enumvisibility';
  143. protected $values = array('visible', 'invisible');
  144. }