123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189 |
- Mysql Enums
- ===========
- The type system of Doctrine 2 consists of flyweights, which means there is only
- one instance of any given type. Additionally types do not contain state. Both
- assumptions make it rather complicated to work with the Enum Type of MySQL that
- is used quite a lot by developers.
- When using Enums with a non-tweaked Doctrine 2 application you will get
- errors from the Schema-Tool commands due to the unknown database type "enum".
- By default Doctrine does not map the MySQL enum type to a Doctrine type.
- This is because Enums contain state (their allowed values) and Doctrine
- types don't.
- This cookbook entry shows two possible solutions to work with MySQL enums.
- But first a word of warning. The MySQL Enum type has considerable downsides:
- - Adding new values requires to rebuild the whole table, which can take hours
- depending on the size.
- - Enums are ordered in the way the values are specified, not in their "natural" order.
- - Enums validation mechanism for allowed values is not necessarily good,
- specifying invalid values leads to an empty enum for the default MySQL error
- settings. You can easily replicate the "allow only some values" requirement
- in your Doctrine entities.
- Solution 1: Mapping to Varchars
- -------------------------------
- You can map ENUMs to varchars. You can register MySQL ENUMs to map to Doctrine
- varchars. This way Doctrine always resolves ENUMs to Doctrine varchars. It
- will even detect this match correctly when using SchemaTool update commands.
- .. code-block:: php
- <?php
- $conn = $em->getConnection();
- $conn->getDatabasePlatform()->registerDoctrineTypeMapping('enum', 'string');
- In this case you have to ensure that each varchar field that is an enum in the
- database only gets passed the allowed values. You can easily enforce this in your
- entities:
- .. code-block:: php
- <?php
- /** @Entity */
- class Article
- {
- const STATUS_VISIBLE = 'visible';
- const STATUS_INVISIBLE = 'invisible';
- /** @Column(type="string") */
- private $status;
- public function setStatus($status)
- {
- if (!in_array($status, array(self::STATUS_VISIBLE, self::STATUS_INVISIBLE))) {
- throw new \InvalidArgumentException("Invalid status");
- }
- $this->status = $status;
- }
- }
- If you want to actively create enums through the Doctrine Schema-Tool by using
- the **columnDefinition** attribute.
- .. code-block:: php
- <?php
- /** @Entity */
- class Article
- {
- /** @Column(type="string", columnDefinition="ENUM('visible', 'invisible')") */
- private $status;
- }
- In this case however Schema-Tool update will have a hard time not to request changes for this column on each call.
- Solution 2: Defining a Type
- ---------------------------
- You can make a stateless ENUM type by creating a type class for each unique set of ENUM values.
- For example for the previous enum type:
- .. code-block:: php
- <?php
- namespace MyProject\DBAL;
- use Doctrine\DBAL\Types\Type;
- use Doctrine\DBAL\Platforms\AbstractPlatform;
- class EnumVisibilityType extends Type
- {
- const ENUM_VISIBILITY = 'enumvisibility';
- const STATUS_VISIBLE = 'visible';
- const STATUS_INVISIBLE = 'invisible';
- public function getSqlDeclaration(array $fieldDeclaration, AbstractPlatform $platform)
- {
- return "ENUM('visible', 'invisible') COMMENT '(DC2Type:enumvisibility)'";
- }
- public function convertToPHPValue($value, AbstractPlatform $platform)
- {
- return $value;
- }
- public function convertToDatabaseValue($value, AbstractPlatform $platform)
- {
- if (!in_array($value, array(self::STATUS_VISIBLE, self::STATUS_INVISIBLE))) {
- throw new \InvalidArgumentException("Invalid status");
- }
- return $value;
- }
- public function getName()
- {
- return self::ENUM_VISIBILITY;
- }
- }
- You can register this type with ``Type::addType('enumvisibility', 'MyProject\DBAL\EnumVisibilityType');``.
- Then in your entity you can just use this type:
- .. code-block:: php
- <?php
- /** @Entity */
- class Article
- {
- /** @Column(type="enumvisibility") */
- private $status;
- }
- You can generalize this approach easily to create a base class for enums:
- .. code-block:: php
- <?php
- namespace MyProject\DBAL;
- use Doctrine\DBAL\Types\Type;
- use Doctrine\DBAL\Platforms\AbstractPlatform;
- abstract class EnumType extends Type
- {
- protected $name;
- protected $values = array();
- public function getSqlDeclaration(array $fieldDeclaration, AbstractPlatform $platform)
- {
- $values = array_map(function($val) { return "'".$val."'"; }, $this->values);
- return "ENUM(".implode(", ", $values).") COMMENT '(DC2Type:".$this->name.")'";
- }
- public function convertToPHPValue($value, AbstractPlatform $platform)
- {
- return $value;
- }
- public function convertToDatabaseValue($value, AbstractPlatform $platform)
- {
- if (!in_array($value, $this->values)) {
- throw new \InvalidArgumentException("Invalid '".$this->name."' value.");
- }
- return $value;
- }
- public function getName()
- {
- return $this->name;
- }
- }
- With this base class you can define an enum as easily as:
- .. code-block:: php
- <?php
- namespace MyProject\DBAL;
- class EnumVisibilityType extends EnumType
- {
- protected $name = 'enumvisibility';
- protected $values = array('visible', 'invisible');
- }
|