Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

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