1<?php 2/* 3 * $Id: Pgsql.php 7641 2010-06-08 14:50:30Z jwage $ 4 * 5 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS 6 * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT 7 * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR 8 * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT 9 * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, 10 * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT 11 * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, 12 * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY 13 * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT 14 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE 15 * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. 16 * 17 * This software consists of voluntary contributions made by many individuals 18 * and is licensed under the LGPL. For more information, see 19 * <http://www.doctrine-project.org>. 20 */ 21 22/** 23 * @package Doctrine 24 * @subpackage DataDict 25 * @license http://www.opensource.org/licenses/lgpl-license.php LGPL 26 * @author Konsta Vesterinen <kvesteri@cc.hut.fi> 27 * @author Paul Cooper <pgc@ucecom.com> 28 * @author Lukas Smith <smith@pooteeweet.org> (PEAR MDB2 library) 29 * @version $Revision: 7641 $ 30 * @link www.doctrine-project.org 31 * @since 1.0 32 */ 33class Doctrine_DataDict_Pgsql extends Doctrine_DataDict 34{ 35 /** 36 * @param array $reservedKeyWords an array of reserved keywords by pgsql 37 */ 38 protected static $reservedKeyWords = array( 39 'abort', 40 'absolute', 41 'access', 42 'action', 43 'add', 44 'after', 45 'aggregate', 46 'all', 47 'alter', 48 'analyse', 49 'analyze', 50 'and', 51 'any', 52 'as', 53 'asc', 54 'assertion', 55 'assignment', 56 'at', 57 'authorization', 58 'backward', 59 'before', 60 'begin', 61 'between', 62 'bigint', 63 'binary', 64 'bit', 65 'boolean', 66 'both', 67 'by', 68 'cache', 69 'called', 70 'cascade', 71 'case', 72 'cast', 73 'chain', 74 'char', 75 'character', 76 'characteristics', 77 'check', 78 'checkpoint', 79 'class', 80 'close', 81 'cluster', 82 'coalesce', 83 'collate', 84 'column', 85 'comment', 86 'commit', 87 'committed', 88 'constraint', 89 'constraints', 90 'conversion', 91 'convert', 92 'copy', 93 'create', 94 'createdb', 95 'createuser', 96 'cross', 97 'current_date', 98 'current_time', 99 'current_timestamp', 100 'current_user', 101 'cursor', 102 'cycle', 103 'database', 104 'day', 105 'deallocate', 106 'dec', 107 'decimal', 108 'declare', 109 'default', 110 'deferrable', 111 'deferred', 112 'definer', 113 'delete', 114 'delimiter', 115 'delimiters', 116 'desc', 117 'distinct', 118 'do', 119 'domain', 120 'double', 121 'drop', 122 'each', 123 'else', 124 'encoding', 125 'encrypted', 126 'end', 127 'escape', 128 'except', 129 'exclusive', 130 'execute', 131 'exists', 132 'explain', 133 'external', 134 'extract', 135 'false', 136 'fetch', 137 'float', 138 'for', 139 'force', 140 'foreign', 141 'forward', 142 'freeze', 143 'from', 144 'full', 145 'function', 146 'get', 147 'global', 148 'grant', 149 'group', 150 'handler', 151 'having', 152 'hour', 153 'ilike', 154 'immediate', 155 'immutable', 156 'implicit', 157 'in', 158 'increment', 159 'index', 160 'inherits', 161 'initially', 162 'inner', 163 'inout', 164 'input', 165 'insensitive', 166 'insert', 167 'instead', 168 'int', 169 'integer', 170 'intersect', 171 'interval', 172 'into', 173 'invoker', 174 'is', 175 'isnull', 176 'isolation', 177 'join', 178 'key', 179 'lancompiler', 180 'language', 181 'leading', 182 'left', 183 'level', 184 'like', 185 'limit', 186 'listen', 187 'load', 188 'local', 189 'localtime', 190 'localtimestamp', 191 'location', 192 'lock', 193 'match', 194 'maxvalue', 195 'minute', 196 'minvalue', 197 'mode', 198 'month', 199 'move', 200 'names', 201 'national', 202 'natural', 203 'nchar', 204 'new', 205 'next', 206 'no', 207 'nocreatedb', 208 'nocreateuser', 209 'none', 210 'not', 211 'nothing', 212 'notify', 213 'notnull', 214 'null', 215 'nullif', 216 'numeric', 217 'of', 218 'off', 219 'offset', 220 'oids', 221 'old', 222 'on', 223 'only', 224 'operator', 225 'option', 226 'or', 227 'order', 228 'out', 229 'outer', 230 'overlaps', 231 'overlay', 232 'owner', 233 'partial', 234 'password', 235 'path', 236 'pendant', 237 'placing', 238 'position', 239 'precision', 240 'prepare', 241 'primary', 242 'prior', 243 'privileges', 244 'procedural', 245 'procedure', 246 'read', 247 'real', 248 'recheck', 249 'references', 250 'reindex', 251 'relative', 252 'rename', 253 'replace', 254 'reset', 255 'restrict', 256 'returns', 257 'revoke', 258 'right', 259 'rollback', 260 'row', 261 'rule', 262 'schema', 263 'scroll', 264 'second', 265 'security', 266 'select', 267 'sequence', 268 'serializable', 269 'session', 270 'session_user', 271 'set', 272 'setof', 273 'share', 274 'show', 275 'similar', 276 'simple', 277 'smallint', 278 'some', 279 'stable', 280 'start', 281 'statement', 282 'statistics', 283 'stdin', 284 'stdout', 285 'storage', 286 'strict', 287 'substring', 288 'sysid', 289 'table', 290 'temp', 291 'template', 292 'temporary', 293 'then', 294 'time', 295 'timestamp', 296 'to', 297 'toast', 298 'trailing', 299 'transaction', 300 'treat', 301 'trigger', 302 'trim', 303 'true', 304 'truncate', 305 'trusted', 306 'type', 307 'unencrypted', 308 'union', 309 'unique', 310 'unknown', 311 'unlisten', 312 'until', 313 'update', 314 'usage', 315 'user', 316 'using', 317 'vacuum', 318 'valid', 319 'validator', 320 'values', 321 'varchar', 322 'varying', 323 'verbose', 324 'version', 325 'view', 326 'volatile', 327 'when', 328 'where', 329 'with', 330 'without', 331 'work', 332 'write', 333 'year', 334 'zone' 335 ); 336 337 /** 338 * Obtain DBMS specific SQL code portion needed to declare an text type 339 * field to be used in statements like CREATE TABLE. 340 * 341 * @param array $field associative array with the name of the properties 342 * of the field being declared as array indexes. Currently, the types 343 * of supported field properties are as follows: 344 * 345 * length 346 * Integer value that determines the maximum length of the text 347 * field. If this argument is missing the field should be 348 * declared to have the longest length allowed by the DBMS. 349 * 350 * default 351 * Text value to be used as default for this field. 352 * 353 * notnull 354 * Boolean flag that indicates whether this field is constrained 355 * to not be set to null. 356 * 357 * @return string DBMS specific SQL code portion that should be used to 358 * declare the specified field. 359 */ 360 public function getNativeDeclaration(array $field) 361 { 362 if ( ! isset($field['type'])) { 363 throw new Doctrine_DataDict_Exception('Missing column type.'); 364 } 365 366 // Postgres enum type by name containing enum 367 if (strpos($field['type'], 'enum') !== false){ 368 $field['type'] = 'enum'; 369 } 370 371 switch ($field['type']) { 372 case 'enum': 373 $field['length'] = isset($field['length']) && $field['length'] ? $field['length']:255; 374 case 'char': 375 case 'string': 376 case 'array': 377 case 'object': 378 case 'varchar': 379 case 'gzip': 380 // TODO: what is the maximum VARCHAR length in pgsql ? 381 $length = (isset($field['length']) && $field['length'] && $field['length'] < 10000) ? $field['length'] : null; 382 383 $fixed = ((isset($field['fixed']) && $field['fixed']) || $field['type'] == 'char') ? true : false; 384 385 return $fixed ? ($length ? 'CHAR(' . $length . ')' : 'CHAR('.$this->conn->varchar_max_length.')') 386 : ($length ? 'VARCHAR(' .$length . ')' : 'TEXT'); 387 388 case 'clob': 389 return 'TEXT'; 390 case 'blob': 391 return 'BYTEA'; 392 case 'integer': 393 case 'int': 394 if ( ! empty($field['autoincrement'])) { 395 if ( ! empty($field['length'])) { 396 $length = $field['length']; 397 if ($length > 4) { 398 return 'BIGSERIAL'; 399 } 400 } 401 return 'SERIAL'; 402 } 403 if ( ! empty($field['length'])) { 404 $length = $field['length']; 405 if ($length <= 2) { 406 return 'SMALLINT'; 407 } elseif ($length == 3 || $length == 4) { 408 return 'INT'; 409 } elseif ($length > 4) { 410 return 'BIGINT'; 411 } 412 } 413 return 'INT'; 414 case 'inet': 415 return 'INET'; 416 case 'bit': 417 case 'varbit': 418 return 'VARBIT'; 419 case 'boolean': 420 return 'BOOLEAN'; 421 case 'date': 422 return 'DATE'; 423 case 'time': 424 return 'TIME'; 425 case 'timestamp': 426 return 'TIMESTAMP'; 427 case 'float': 428 case 'double': 429 return 'FLOAT'; 430 case 'decimal': 431 $length = !empty($field['length']) ? $field['length'] : 18; 432 $scale = !empty($field['scale']) ? $field['scale'] : $this->conn->getAttribute(Doctrine_Core::ATTR_DECIMAL_PLACES); 433 return 'NUMERIC('.$length.','.$scale.')'; 434 } 435 return $field['type'] . (isset($field['length']) ? '('.$field['length'].')':null); 436 } 437 438 /** 439 * Maps a native array description of a field to a portable Doctrine datatype and length 440 * 441 * @param array $field native field description 442 * 443 * @return array containing the various possible types, length, sign, fixed 444 */ 445 public function getPortableDeclaration(array $field) 446 { 447 $length = (isset($field['length'])) ? $field['length'] : null; 448 if ($length == '-1' && isset($field['atttypmod'])) { 449 $length = $field['atttypmod'] - 4; 450 } 451 if ((int)$length <= 0) { 452 $length = null; 453 } 454 $type = array(); 455 $unsigned = $fixed = null; 456 457 if ( ! isset($field['name'])) { 458 $field['name'] = ''; 459 } 460 461 $dbType = strtolower($field['type']); 462 463 // Default from field for enum support 464 $default = isset($field['default']) ? $field['default'] : null; 465 $enumName = null; 466 if (strpos($dbType, 'enum') !== false){ 467 $enumName = $dbType; 468 $dbType = 'enum'; 469 } 470 471 switch ($dbType) { 472 case 'inet': 473 $type[] = 'inet'; 474 break; 475 case 'bit': 476 case 'varbit': 477 $type[] = 'bit'; 478 break; 479 case 'smallint': 480 case 'int2': 481 $type[] = 'integer'; 482 $unsigned = false; 483 $length = 2; 484 if ($length == '2') { 485 $type[] = 'boolean'; 486 if (preg_match('/^(is|has)/', $field['name'])) { 487 $type = array_reverse($type); 488 } 489 } 490 break; 491 case 'int': 492 case 'int4': 493 case 'integer': 494 case 'serial': 495 case 'serial4': 496 $type[] = 'integer'; 497 $unsigned = false; 498 $length = 4; 499 break; 500 case 'bigint': 501 case 'int8': 502 case 'bigserial': 503 case 'serial8': 504 $type[] = 'integer'; 505 $unsigned = false; 506 $length = 8; 507 break; 508 case 'bool': 509 case 'boolean': 510 $type[] = 'boolean'; 511 $length = 1; 512 break; 513 case 'text': 514 case 'varchar': 515 case 'interval': 516 case '_varchar': 517 $fixed = false; 518 case 'tsvector': 519 case 'unknown': 520 case 'char': 521 case 'character': 522 case 'bpchar': 523 $type[] = 'string'; 524 if ($length == '1') { 525 $type[] = 'boolean'; 526 if (preg_match('/^(is|has)/', $field['name'])) { 527 $type = array_reverse($type); 528 } 529 } elseif (strstr($dbType, 'text')) { 530 $type[] = 'clob'; 531 } 532 if ($fixed !== false) { 533 $fixed = true; 534 } 535 break; 536 case 'enum': 537 $type[] = 'enum'; 538 $length = $length ? $length :255; 539 if($default) { 540 $default = preg_replace('/\'(\w+)\'.*/', '${1}', $default); 541 } 542 break; 543 case 'date': 544 $type[] = 'date'; 545 $length = null; 546 break; 547 case 'datetime': 548 case 'timestamp': 549 case 'timetz': 550 case 'timestamptz': 551 $type[] = 'timestamp'; 552 $length = null; 553 break; 554 case 'time': 555 $type[] = 'time'; 556 $length = null; 557 break; 558 case 'float': 559 case 'float4': 560 case 'float8': 561 case 'double': 562 case 'double precision': 563 case 'real': 564 $type[] = 'float'; 565 break; 566 case 'decimal': 567 case 'money': 568 case 'numeric': 569 $type[] = 'decimal'; 570 break; 571 case 'tinyblob': 572 case 'mediumblob': 573 case 'longblob': 574 case 'blob': 575 case 'bytea': 576 case 'geometry': 577 case 'geometrycollection': 578 case 'point': 579 case 'multipoint': 580 case 'linestring': 581 case 'multilinestring': 582 case 'polygon': 583 case 'multipolygon': 584 $type[] = 'blob'; 585 $length = null; 586 break; 587 case 'oid': 588 $type[] = 'blob'; 589 $type[] = 'clob'; 590 $length = null; 591 break; 592 case 'year': 593 $type[] = 'integer'; 594 $type[] = 'date'; 595 $length = null; 596 break; 597 default: 598 $type[] = $field['type']; 599 $length = isset($field['length']) ? $field['length']:null; 600 } 601 602 $ret = array('type' => $type, 603 'length' => $length, 604 'unsigned' => $unsigned, 605 'fixed' => $fixed); 606 607 // If this is postgresql enum type we will have non-null values here 608 if ($default !== null) { 609 $ret['default'] = $default; 610 } 611 if ($enumName !== null) { 612 $ret['enumName'] = $enumName; 613 } 614 return $ret; 615 } 616 617 /** 618 * Obtain DBMS specific SQL code portion needed to declare an integer type 619 * field to be used in statements like CREATE TABLE. 620 * 621 * @param string $name name the field to be declared. 622 * @param array $field associative array with the name of the properties 623 * of the field being declared as array indexes. Currently, the types 624 * of supported field properties are as follows: 625 * 626 * unsigned 627 * Boolean flag that indicates whether the field should be 628 * declared as unsigned integer if possible. 629 * 630 * default 631 * Integer value to be used as default for this field. 632 * 633 * notnull 634 * Boolean flag that indicates whether this field is constrained 635 * to not be set to null. 636 * @return string DBMS specific SQL code portion that should be used to 637 * declare the specified field. 638 */ 639 public function getIntegerDeclaration($name, $field) 640 { 641 /** 642 if ( ! empty($field['unsigned'])) { 643 $this->conn->warnings[] = "unsigned integer field \"$name\" is being declared as signed integer"; 644 } 645 */ 646 647 if ( ! empty($field['autoincrement'])) { 648 $name = $this->conn->quoteIdentifier($name, true); 649 return $name . ' ' . $this->getNativeDeclaration($field); 650 } 651 652 $default = ''; 653 if (array_key_exists('default', $field)) { 654 if ($field['default'] === '') { 655 $field['default'] = empty($field['notnull']) ? null : 0; 656 } 657 658 $default = ' DEFAULT ' . (is_null($field['default']) 659 ? 'NULL' 660 : $this->conn->quote($field['default'], $field['type'])); 661 } 662 /** 663 TODO: is this needed ? 664 elseif (empty($field['notnull'])) { 665 $default = ' DEFAULT NULL'; 666 } 667 */ 668 669 $notnull = empty($field['notnull']) ? '' : ' NOT NULL'; 670 $name = $this->conn->quoteIdentifier($name, true); 671 return $name . ' ' . $this->getNativeDeclaration($field) . $default . $notnull; 672 } 673 674 /** 675 * parseBoolean 676 * parses a literal boolean value and returns 677 * proper sql equivalent 678 * 679 * @param string $value boolean value to be parsed 680 * @return string parsed boolean value 681 */ 682 public function parseBoolean($value) 683 { 684 return $value; 685 } 686} 687