2 // vim: set et ts=4 sw=4 fdm=marker:
3 // +----------------------------------------------------------------------+
4 // | PHP versions 4 and 5 |
5 // +----------------------------------------------------------------------+
6 // | Copyright (c) 1998-2008 Manuel Lemos, Tomas V.V.Cox, |
7 // | Stig. S. Bakken, Lukas Smith |
8 // | All rights reserved. |
9 // +----------------------------------------------------------------------+
10 // | MDB2 is a merge of PEAR DB and Metabases that provides a unified DB |
11 // | API as well as database abstraction for PHP applications. |
12 // | This LICENSE is in the BSD license style. |
14 // | Redistribution and use in source and binary forms, with or without |
15 // | modification, are permitted provided that the following conditions |
18 // | Redistributions of source code must retain the above copyright |
19 // | notice, this list of conditions and the following disclaimer. |
21 // | Redistributions in binary form must reproduce the above copyright |
22 // | notice, this list of conditions and the following disclaimer in the |
23 // | documentation and/or other materials provided with the distribution. |
25 // | Neither the name of Manuel Lemos, Tomas V.V.Cox, Stig. S. Bakken, |
26 // | Lukas Smith nor the names of his contributors may be used to endorse |
27 // | or promote products derived from this software without specific prior|
28 // | written permission. |
30 // | THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS |
31 // | "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT |
32 // | LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS |
33 // | FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE |
34 // | REGENTS OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, |
35 // | INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, |
36 // | BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS|
37 // | OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED |
38 // | AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT |
39 // | LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY|
40 // | WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE |
41 // | POSSIBILITY OF SUCH DAMAGE. |
42 // +----------------------------------------------------------------------+
43 // | Author: Lukas Smith <smith@pooteeweet.org> |
44 // +----------------------------------------------------------------------+
46 // $Id: mysql.php 327310 2012-08-27 15:16:18Z danielc $
49 require_once 'MDB2/Driver/Datatype/Common.php';
56 * @author Lukas Smith <smith@pooteeweet.org>
58 class MDB2_Driver_Datatype_mysql extends MDB2_Driver_Datatype_Common
60 // {{{ _getCharsetFieldDeclaration()
63 * Obtain DBMS specific SQL code portion needed to set the CHARACTER SET
64 * of a field declaration to be used in statements like CREATE TABLE.
66 * @param string $charset name of the charset
67 * @return string DBMS specific SQL code portion needed to set the CHARACTER SET
68 * of a field declaration.
70 function _getCharsetFieldDeclaration($charset)
72 return 'CHARACTER SET '.$charset;
76 // {{{ _getCollationFieldDeclaration()
79 * Obtain DBMS specific SQL code portion needed to set the COLLATION
80 * of a field declaration to be used in statements like CREATE TABLE.
82 * @param string $collation name of the collation
83 * @return string DBMS specific SQL code portion needed to set the COLLATION
84 * of a field declaration.
86 function _getCollationFieldDeclaration($collation)
88 return 'COLLATE '.$collation;
92 // {{{ getDeclaration()
95 * Obtain DBMS specific SQL code portion needed to declare
98 * @param string $type type to which the value should be converted to
99 * @param string $name name the field to be declared.
100 * @param string $field definition of the field
102 * @return string DBMS-specific SQL code portion that should be used to
103 * declare the specified field.
106 function getDeclaration($type, $name, $field)
108 // MySQL DDL syntax forbids combining NOT NULL with DEFAULT NULL.
109 // To get a default of NULL for NOT NULL columns, omit it.
110 if ( isset($field['notnull'])
111 && !empty($field['notnull'])
112 && array_key_exists('default', $field) // do not use isset() here!
113 && null === $field['default']
115 unset($field['default']);
117 return parent::getDeclaration($type, $name, $field);
121 // {{{ getTypeDeclaration()
124 * Obtain DBMS specific SQL code portion needed to declare an text type
125 * field to be used in statements like CREATE TABLE.
127 * @param array $field associative array with the name of the properties
128 * of the field being declared as array indexes. Currently, the types
129 * of supported field properties are as follows:
132 * Integer value that determines the maximum length of the text
133 * field. If this argument is missing the field should be
134 * declared to have the longest length allowed by the DBMS.
137 * Text value to be used as default for this field.
140 * Boolean flag that indicates whether this field is constrained
141 * to not be set to null.
142 * @return string DBMS specific SQL code portion that should be used to
143 * declare the specified field.
146 function getTypeDeclaration($field)
148 $db = $this->getDBInstance();
149 if (MDB2::isError($db)) {
153 switch ($field['type']) {
155 if (empty($field['length']) && array_key_exists('default', $field)) {
156 $field['length'] = $db->varchar_max_length;
158 $length = !empty($field['length']) ? $field['length'] : false;
159 $fixed = !empty($field['fixed']) ? $field['fixed'] : false;
160 return $fixed ? ($length ? 'CHAR('.$length.')' : 'CHAR(255)')
161 : ($length ? 'VARCHAR('.$length.')' : 'TEXT');
163 if (!empty($field['length'])) {
164 $length = $field['length'];
165 if ($length <= 255) {
167 } elseif ($length <= 65532) {
169 } elseif ($length <= 16777215) {
175 if (!empty($field['length'])) {
176 $length = $field['length'];
177 if ($length <= 255) {
179 } elseif ($length <= 65532) {
181 } elseif ($length <= 16777215) {
187 if (!empty($field['length'])) {
188 $length = $field['length'];
191 } elseif ($length == 2) {
193 } elseif ($length == 3) {
195 } elseif ($length == 4) {
197 } elseif ($length > 4) {
212 if (!empty($field['length'])) {
213 $l = '(' . $field['length'];
214 if (!empty($field['scale'])) {
215 $l .= ',' . $field['scale'];
219 return 'DOUBLE' . $l;
221 $length = !empty($field['length']) ? $field['length'] : 18;
222 $scale = !empty($field['scale']) ? $field['scale'] : $db->options['decimal_places'];
223 return 'DECIMAL('.$length.','.$scale.')';
229 // {{{ _getIntegerDeclaration()
232 * Obtain DBMS specific SQL code portion needed to declare an integer type
233 * field to be used in statements like CREATE TABLE.
235 * @param string $name name the field to be declared.
236 * @param string $field associative array with the name of the properties
237 * of the field being declared as array indexes.
238 * Currently, the types of supported field
239 * properties are as follows:
242 * Boolean flag that indicates whether the field
243 * should be declared as unsigned integer if
247 * Integer value to be used as default for this
251 * Boolean flag that indicates whether this field is
252 * constrained to not be set to null.
253 * @return string DBMS specific SQL code portion that should be used to
254 * declare the specified field.
257 function _getIntegerDeclaration($name, $field)
259 $db = $this->getDBInstance();
260 if (MDB2::isError($db)) {
264 $default = $autoinc = '';
265 if (!empty($field['autoincrement'])) {
266 $autoinc = ' AUTO_INCREMENT PRIMARY KEY';
267 } elseif (array_key_exists('default', $field)) {
268 if ($field['default'] === '') {
269 $field['default'] = empty($field['notnull']) ? null : 0;
271 $default = ' DEFAULT '.$this->quote($field['default'], 'integer');
274 $notnull = empty($field['notnull']) ? '' : ' NOT NULL';
275 $unsigned = empty($field['unsigned']) ? '' : ' UNSIGNED';
276 if (empty($default) && empty($notnull)) {
277 $default = ' DEFAULT NULL';
279 $name = $db->quoteIdentifier($name, true);
280 return $name.' '.$this->getTypeDeclaration($field).$unsigned.$default.$notnull.$autoinc;
284 // {{{ _getFloatDeclaration()
287 * Obtain DBMS specific SQL code portion needed to declare an float type
288 * field to be used in statements like CREATE TABLE.
290 * @param string $name name the field to be declared.
291 * @param string $field associative array with the name of the properties
292 * of the field being declared as array indexes.
293 * Currently, the types of supported field
294 * properties are as follows:
297 * Boolean flag that indicates whether the field
298 * should be declared as unsigned float if
302 * float value to be used as default for this
306 * Boolean flag that indicates whether this field is
307 * constrained to not be set to null.
308 * @return string DBMS specific SQL code portion that should be used to
309 * declare the specified field.
312 function _getFloatDeclaration($name, $field)
314 // Since AUTO_INCREMENT can be used for integer or floating-point types,
315 // reuse the INTEGER declaration
316 // @see http://bugs.mysql.com/bug.php?id=31032
317 return $this->_getIntegerDeclaration($name, $field);
321 // {{{ _getDecimalDeclaration()
324 * Obtain DBMS specific SQL code portion needed to declare an decimal type
325 * field to be used in statements like CREATE TABLE.
327 * @param string $name name the field to be declared.
328 * @param string $field associative array with the name of the properties
329 * of the field being declared as array indexes.
330 * Currently, the types of supported field
331 * properties are as follows:
334 * Boolean flag that indicates whether the field
335 * should be declared as unsigned integer if
339 * Decimal value to be used as default for this
343 * Boolean flag that indicates whether this field is
344 * constrained to not be set to null.
345 * @return string DBMS specific SQL code portion that should be used to
346 * declare the specified field.
349 function _getDecimalDeclaration($name, $field)
351 $db = $this->getDBInstance();
352 if (MDB2::isError($db)) {
357 if (array_key_exists('default', $field)) {
358 if ($field['default'] === '') {
359 $field['default'] = empty($field['notnull']) ? null : 0;
361 $default = ' DEFAULT '.$this->quote($field['default'], 'integer');
362 } elseif (empty($field['notnull'])) {
363 $default = ' DEFAULT NULL';
366 $notnull = empty($field['notnull']) ? '' : ' NOT NULL';
367 $unsigned = empty($field['unsigned']) ? '' : ' UNSIGNED';
368 $name = $db->quoteIdentifier($name, true);
369 return $name.' '.$this->getTypeDeclaration($field).$unsigned.$default.$notnull;
373 // {{{ matchPattern()
376 * build a pattern matching string
380 * @param array $pattern even keys are strings, odd are patterns (% and _)
381 * @param string $operator optional pattern operator (LIKE, ILIKE and maybe others in the future)
382 * @param string $field optional field name that is being matched against
383 * (might be required when emulating ILIKE)
385 * @return string SQL pattern
387 function matchPattern($pattern, $operator = null, $field = null)
389 $db = $this->getDBInstance();
390 if (MDB2::isError($db)) {
395 if (null !== $operator) {
396 $field = (null === $field) ? '' : $field.' ';
397 $operator = strtoupper($operator);
401 $match = $field.'LIKE ';
404 $match = $field.'NOT LIKE ';
408 $match = $field.'LIKE BINARY ';
411 $match = $field.'NOT LIKE BINARY ';
414 return $db->raiseError(MDB2_ERROR_UNSUPPORTED, null, null,
415 'not a supported operator type:'. $operator, __FUNCTION__);
419 foreach ($pattern as $key => $value) {
423 $match.= $db->escapePattern($db->escape($value));
427 $match.= $this->patternEscapeString();
432 // {{{ _mapNativeDatatype()
435 * Maps a native array description of a field to a MDB2 datatype and length
437 * @param array $field native field description
438 * @return array containing the various possible types, length, sign, fixed
441 function _mapNativeDatatype($field)
443 $db_type = strtolower($field['type']);
444 $db_type = strtok($db_type, '(), ');
445 if ($db_type == 'national') {
446 $db_type = strtok('(), ');
448 if (!empty($field['length'])) {
449 $length = strtok($field['length'], ', ');
450 $decimal = strtok(', ');
452 $length = strtok('(), ');
453 $decimal = strtok('(), ');
456 $unsigned = $fixed = null;
461 if (preg_match('/^(is|has)/', $field['name'])) {
462 $type = array_reverse($type);
464 $unsigned = preg_match('/ unsigned/i', $field['type']);
469 $unsigned = preg_match('/ unsigned/i', $field['type']);
474 $unsigned = preg_match('/ unsigned/i', $field['type']);
480 $unsigned = preg_match('/ unsigned/i', $field['type']);
485 $unsigned = preg_match('/ unsigned/i', $field['type']);
497 if ($length == '1') {
499 if (preg_match('/^(is|has)/', $field['name'])) {
500 $type = array_reverse($type);
502 } elseif (strstr($db_type, 'text')) {
504 if ($decimal == 'binary') {
507 $type = array_reverse($type);
509 if ($fixed !== false) {
515 preg_match_all('/\'.+\'/U', $field['type'], $matches);
518 if (is_array($matches)) {
519 foreach ($matches[0] as $value) {
520 $length = max($length, strlen($value)-2);
522 if ($length == '1' && count($matches[0]) == 2) {
524 if (preg_match('/^(is|has)/', $field['name'])) {
525 $type = array_reverse($type);
541 $type[] = 'timestamp';
552 $unsigned = preg_match('/ unsigned/i', $field['type']);
553 if ($decimal !== false) {
554 $length = $length.','.$decimal;
561 $unsigned = preg_match('/ unsigned/i', $field['type']);
562 if ($decimal !== false) {
563 $length = $length.','.$decimal;
583 $db = $this->getDBInstance();
584 if (MDB2::isError($db)) {
588 return $db->raiseError(MDB2_ERROR_UNSUPPORTED, null, null,
589 'unknown database attribute type: '.$db_type, __FUNCTION__);
592 if ((int)$length <= 0) {
596 return array($type, $length, $unsigned, $fixed);