| [ Index ] |
|
Code source de eZ Publish 3.9.0 |
1 <?php 2 // 3 // Created on: <09-Feb-2004 09:06:24 dr> 4 // 5 // SOFTWARE NAME: eZ publish 6 // SOFTWARE RELEASE: 3.9.0 7 // BUILD VERSION: 17785 8 // COPYRIGHT NOTICE: Copyright (C) 1999-2006 eZ systems AS 9 // SOFTWARE LICENSE: GNU General Public License v2.0 10 // NOTICE: > 11 // This program is free software; you can redistribute it and/or 12 // modify it under the terms of version 2.0 of the GNU General 13 // Public License as published by the Free Software Foundation. 14 // 15 // This program is distributed in the hope that it will be useful, 16 // but WITHOUT ANY WARRANTY; without even the implied warranty of 17 // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 18 // GNU General Public License for more details. 19 // 20 // You should have received a copy of version 2.0 of the GNU General 21 // Public License along with this program; if not, write to the Free 22 // Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, 23 // MA 02110-1301, USA. 24 // 25 // 26 27 /*! 28 \class eZPgsqlSchema ezpgsqlschema.php 29 \ingroup eZDBSchema 30 \brief Handles schemas for PostgreSQL 31 32 */ 33 34 define( 'SHOW_TABLES_QUERY', <<<END 35 SELECT n.nspname as "Schema", 36 c.relname as "Name", 37 CASE c.relkind 38 WHEN 'r' THEN 'table' 39 WHEN 'v' THEN 'view' 40 WHEN 'i' THEN 'index' 41 WHEN 'S' THEN 'sequence' 42 WHEN 's' THEN 'special' 43 END as "Type", 44 u.usename as "Owner" 45 FROM pg_catalog.pg_class c 46 LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner 47 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 48 WHERE c.relkind IN ('r','') 49 AND n.nspname NOT IN ('pg_catalog', 'pg_toast') 50 AND pg_catalog.pg_table_is_visible(c.oid) 51 ORDER BY 1,2 52 END 53 ); 54 55 define( 'FETCH_TABLE_OID_QUERY', <<<END 56 SELECT c.oid, 57 n.nspname, 58 c.relname 59 FROM pg_catalog.pg_class c 60 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 61 WHERE pg_catalog.pg_table_is_visible(c.oid) 62 AND c.relname ~ '^<<tablename>>$' 63 ORDER BY 2, 3; 64 END 65 ); 66 67 define( 'FETCH_TABLE_DEF_QUERY', <<<END 68 SELECT a.attname, 69 pg_catalog.format_type(a.atttypid, a.atttypmod), 70 (SELECT substring(d.adsrc for 128) FROM pg_catalog.pg_attrdef d 71 WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) as default, 72 a.attnotnull, a.attnum 73 FROM pg_catalog.pg_attribute a 74 WHERE a.attrelid = '<<oid>>' AND a.attnum > 0 AND NOT a.attisdropped 75 ORDER BY a.attnum 76 END 77 ); 78 79 define( 'FETCH_INDEX_DEF_QUERY', <<<END 80 SELECT c.relname, i.* 81 FROM pg_catalog.pg_index i, pg_catalog.pg_class c 82 WHERE indrelid = '<<oid>>' 83 AND i.indexrelid = c.oid 84 END 85 ); 86 87 define( 'FETCH_INDEX_COL_NAMES_QUERY', <<<END 88 SELECT a.attnum, a.attname 89 FROM pg_catalog.pg_attribute a 90 WHERE a.attrelid = '<<indexrelid>>' AND a.attnum IN (<<attids>>) AND NOT a.attisdropped 91 ORDER BY a.attnum 92 END 93 ); 94 95 include_once ( 'lib/ezdbschema/classes/ezdbschemainterface.php' ); 96 97 class eZPgsqlSchema extends eZDBSchemaInterface 98 { 99 /*! 100 \reimp 101 Constructor 102 103 \param db instance 104 */ 105 function eZPgsqlSchema( $db ) 106 { 107 $this->eZDBSchemaInterface( $db ); 108 } 109 110 /*! 111 \reimp 112 */ 113 function schema( $params = array() ) 114 { 115 $params = array_merge( array( 'meta_data' => false, 116 'format' => 'generic' ), 117 $params ); 118 $schema = array(); 119 120 if ( $this->Schema === false ) 121 { 122 $resultArray = $this->DBInstance->arrayQuery( SHOW_TABLES_QUERY ); 123 124 foreach( $resultArray as $row ) 125 { 126 $table_name = $row['Name']; 127 $schema_table['name'] = $table_name; 128 $schema_table['fields'] = $this->fetchTableFields( $table_name, $params ); 129 $schema_table['indexes'] = $this->fetchTableIndexes( $table_name, $params ); 130 131 $schema[$table_name] = $schema_table; 132 } 133 $this->transformSchema( $schema, $params['format'] == 'local' ); 134 ksort( $schema ); 135 $this->Schema = $schema; 136 } 137 else 138 { 139 $this->transformSchema( $this->Schema, $params['format'] == 'local' ); 140 $schema = $this->Schema; 141 } 142 return $schema; 143 } 144 145 /*! 146 * \private 147 */ 148 function fetchTableFields( $table, $params ) 149 { 150 $fields = array(); 151 152 $resultArray = $this->DBInstance->arrayQuery( str_replace( '<<tablename>>', $table, FETCH_TABLE_OID_QUERY ) ); 153 $row = $resultArray[0]; 154 $oid = $row['oid']; 155 156 $resultArray = $this->DBInstance->arrayQuery( str_replace( '<<oid>>', $oid, FETCH_TABLE_DEF_QUERY ) ); 157 foreach( $resultArray as $row ) 158 { 159 $field = array(); 160 $autoinc = false; 161 $field['type'] = $this->parseType( $row['format_type'], $field['length'] ); 162 if ( !$field['length'] ) 163 { 164 unset( $field['length'] ); 165 } 166 167 $field['not_null'] = 0; 168 if ( $row['attnotnull'] == 't' ) 169 { 170 $field['not_null'] = '1'; 171 } 172 173 $field['default'] = false; 174 if ( !$field['not_null'] ) 175 { 176 if ( $row['default'] === null ) 177 $field['default'] = null; 178 else 179 $field['default'] = (string)$this->parseDefault ( $row['default'], $autoinc ); 180 } 181 else 182 { 183 $field['default'] = (string)$this->parseDefault ( $row['default'], $autoinc ); 184 } 185 186 $numericTypes = array( 'float', 'int' ); 187 $blobTypes = array( 'tinytext', 'text', 'mediumtext', 'longtext' ); 188 $charTypes = array( 'varchar', 'char' ); 189 if ( in_array( $field['type'], $charTypes ) ) 190 { 191 if ( !$field['not_null'] ) 192 { 193 if ( $field['default'] === null ) 194 { 195 $field['default'] = null; 196 } 197 else if ( $field['default'] === false ) 198 { 199 $field['default'] = ''; 200 } 201 } 202 } 203 else if ( in_array( $field['type'], $numericTypes ) ) 204 { 205 if ( $field['default'] === false ) 206 { 207 if ( $field['not_null'] ) 208 { 209 $field['default'] = 0; 210 } 211 } 212 else if ( $field['type'] == 'int' ) 213 { 214 if ( $field['not_null'] or 215 is_numeric( $field['default'] ) ) 216 { 217 $field['default'] = (int)$field['default']; 218 } 219 } 220 else if ( $field['type'] == 'float' ) 221 { 222 if ( $field['not_null'] or 223 is_numeric( $field['default'] ) ) 224 { 225 $field['default'] = (float)$field['default']; 226 } 227 } 228 } 229 else if ( in_array( $field['type'], $blobTypes ) ) 230 { 231 // We do not want default for blobs. 232 $field['default'] = false; 233 } 234 235 if ( $autoinc ) 236 { 237 unset( $field['length'] ); 238 $field['not_null'] = 0; 239 $field['default'] = false; 240 $field['type'] = 'auto_increment'; 241 } 242 243 if ( !$field['not_null'] ) 244 unset( $field['not_null'] ); 245 246 $fields[$row['attname']] = $field; 247 } 248 ksort( $fields ); 249 250 return $fields; 251 } 252 253 /*! 254 * \private 255 */ 256 function fetchTableIndexes( $table, $params ) 257 { 258 $metaData = false; 259 if ( isset( $params['meta_data'] ) ) 260 { 261 $metaData = $params['meta_data']; 262 } 263 264 $indexes = array(); 265 266 $resultArray = $this->DBInstance->arrayQuery( str_replace( '<<tablename>>', $table, FETCH_TABLE_OID_QUERY ) ); 267 $row = $resultArray[0]; 268 $oid = $row['oid']; 269 270 $resultArray = $this->DBInstance->arrayQuery( str_replace( '<<oid>>', $oid, FETCH_INDEX_DEF_QUERY ) ); 271 272 foreach( $resultArray as $row ) 273 { 274 $fields = array(); 275 $kn = $row['relname']; 276 277 $column_id_array = split( ' ', $row['indkey'] ); 278 if ( $row['indisprimary'] == 't' ) 279 { 280 // If the name of the key matches our primary key naming standard 281 // we change the name to PRIMARY, this makes it 100% similar to 282 // primary keys in MySQL 283 $correctName = $this->primaryKeyIndexName( $table, $kn, $column_id_array ); 284 if ( strlen( $correctName ) > 63 ) 285 { 286 eZDebug::writeError( "The index name '$correctName' (" . strlen( $correctName ) . ") exceeds 63 characters which is the PostgreSQL limit for names" ); 287 } 288 if ( $kn == $correctName ) 289 { 290 $kn = 'PRIMARY'; 291 } 292 293 // Extra meta data: 294 // Include the name of the index that postgresql will use 295 if ( $metaData ) 296 { 297 $indexes[$kn]['postgresql:name'] = $correctName; 298 } 299 300 $indexes[$kn]['type'] = 'primary'; 301 } 302 else 303 { 304 $indexes[$kn]['type'] = $row['indisunique'] == 't' ? 'unique' : 'non-unique'; 305 } 306 307 /* getting fieldnames requires yet another query and it doesn't return it 'in order' either. 308 * grumbl, stupid pgsql :) */ 309 $att_ids = join( ', ', $column_id_array ); 310 $query = str_replace( '<<indexrelid>>', $row['indrelid'], FETCH_INDEX_COL_NAMES_QUERY ); 311 $query = str_replace( '<<attids>>', $att_ids, $query ); 312 313 $fieldsArray = $this->DBInstance->arrayQuery( $query ); 314 foreach( $fieldsArray as $fields_row ) 315 { 316 $fields[$fields_row['attnum']] = $fields_row['attname']; 317 } 318 foreach ( $column_id_array as $rank => $id ) 319 { 320 $indexes[$kn]['fields'][$rank] = $fields[$id]; 321 } 322 } 323 ksort( $indexes ); 324 325 return $indexes; 326 } 327 328 function parseType( $type_info, &$length_info ) 329 { 330 preg_match ( "@([a-z ]*)(\(([0-9]*|[0-9]*,[0-9]*)\))?@", $type_info, $matches ); 331 if ( isset( $matches[3] ) ) 332 { 333 $length_info = $matches[3]; 334 if ( is_numeric( $length_info ) ) 335 $length_info = (int)$length_info; 336 } 337 $type = $this->convertToStandardType ( $matches[1], $length_info ); 338 return $type; 339 } 340 341 function isTypeLengthSupported( $pgType ) 342 { 343 switch ( $pgType ) 344 { 345 case 'integer': 346 case 'double precision': 347 case 'real': 348 { 349 return false; 350 } break; 351 } 352 return true; 353 } 354 355 function convertFromStandardType( $type, &$length ) 356 { 357 switch ( $type ) 358 { 359 case 'char': 360 { 361 if ( $length == 1 ) 362 { 363 return 'character'; 364 } 365 else 366 { 367 return 'character varying'; 368 } 369 } break; 370 case 'int': 371 { 372 return 'integer'; 373 } break; 374 case 'varchar': 375 { 376 return 'character varying'; 377 } break; 378 case 'longtext': 379 { 380 return 'text'; 381 } break; 382 case 'mediumtext': 383 { 384 return 'text'; 385 } break; 386 case 'text': 387 { 388 return 'text'; 389 } break; 390 case 'float': 391 case 'double': 392 { 393 return 'double precision'; 394 } break; 395 case 'decimal': 396 { 397 return 'numeric'; 398 } break; 399 default: 400 die ( "ERROR UNHANDLED TYPE: $type\n" ); 401 } 402 } 403 404 /*! 405 \private 406 The name will consist of the table name and _pkey, since it is only allowed 407 to have one primary key pre table that shouldn't be a problem. 408 409 \return A string representing the name of the primary key index. 410 */ 411 function primaryKeyIndexName( $tableName, $indexName, $fields ) 412 { 413 return $tableName . '_pkey'; 414 } 415 416 function convertToStandardType( $type, &$length ) 417 { 418 switch ( $type ) 419 { 420 case 'bigint': 421 { 422 return 'int'; 423 } break; 424 case 'integer': 425 { 426 $length = 11; 427 return 'int'; 428 } break; 429 case 'character varying': 430 { 431 return 'varchar'; 432 } break; 433 case 'text': 434 { 435 return 'longtext'; 436 } break; 437 case 'double precision': 438 { 439 return 'float'; 440 } break; 441 case 'character': 442 { 443 $lenght = 1; 444 return 'char'; 445 } break; 446 case 'numeric': 447 { 448 return 'decimal'; 449 } break; 450 default: 451 die ( "ERROR UNHANDLED TYPE: $type\n" ); 452 } 453 } 454 455 function parseDefault( $default, &$autoinc ) 456 { 457 if ( preg_match( "@^nextval\('([a-z_]+_s)'::text\)$@", $default ) ) 458 { 459 $autoinc = 1; 460 return ''; 461 } 462 463 if ( preg_match( "@^\(?([^()]*)\)?::double precision@", $default, $matches ) ) 464 { 465 return $matches[1]; 466 } 467 468 if ( preg_match( "@^(.*)::bigint@", $default, $matches ) ) 469 { 470 return $matches[1]; 471 } 472 473 if ( preg_match( "@^'(.*)'::character\ varying$@", $default, $matches ) ) 474 { 475 return $matches[1]; 476 } 477 478 if ( preg_match( "@^'(.*)'::[a-zA-Z ]+$@", $default, $matches ) ) 479 { 480 return $matches[1]; 481 } 482 483 if ( preg_match( "@^'(.*)'$@", $default, $matches ) ) 484 { 485 return $matches[1]; 486 } 487 488 return $default; 489 } 490 491 /*! 492 \private 493 \param $table_name The table name 494 \param $index_name The index name 495 \param $def The index structure, see eZDBSchemaInterface for more details 496 \param $params An associative array with optional parameters which controls the output of SQLs 497 \param $withClosure If \c true then the SQLs will contain semi-colons to close them. 498 */ 499 function generateAddIndexSql( $table_name, $index_name, $def, $params, $withClosure ) 500 { 501 $diffFriendly = isset( $params['diff_friendly'] ) ? $params['diff_friendly'] : false; 502 $postgresqlCompatible = isset( $params['compatible_sql'] ) ? $params['compatible_sql'] : false; 503 504 $spacing = $postgresqlCompatible ? "\n " : " "; 505 switch ( $def['type'] ) 506 { 507 case 'primary': 508 { 509 $pkeyName = $this->primaryKeyIndexName( $table_name, $index_name, $def['fields'] ); 510 if ( strlen( $pkeyName ) > 63 ) 511 { 512 eZDebug::writeError( "The primary key '$pkeyName' (" . strlen( $pkeyName ) . ") exceeds 63 characters which is the PostgreSQL limit for names" ); 513 } 514 $sql = "ALTER TABLE ONLY $table_name" . $spacing . "ADD CONSTRAINT $pkeyName PRIMARY KEY"; 515 } break; 516 517 case 'non-unique': 518 { 519 $sql = "CREATE INDEX $index_name ON $table_name USING btree"; 520 } break; 521 522 case 'unique': 523 { 524 $sql = "CREATE UNIQUE INDEX $index_name ON $table_name USING btree"; 525 } break; 526 } 527 528 $sql .= ( $diffFriendly ? " (\n " : ( $postgresqlCompatible ? ' (' : ' ( ' ) ); 529 $i = 0; 530 foreach ( $def['fields'] as $fieldDef ) 531 { 532 if ( $i > 0 ) 533 { 534 $sql .= $diffFriendly ? ",\n " : ', '; 535 } 536 if ( is_array( $fieldDef ) ) 537 { 538 $fieldName = $fieldDef['name']; 539 } 540 else 541 { 542 $fieldName = $fieldDef; 543 } 544 if ( in_array( $fieldName, $this->reservedKeywordList() ) ) 545 { 546 $sql .= '"' . $fieldName . '"'; 547 } 548 else 549 { 550 $sql .= $fieldName; 551 } 552 ++$i; 553 } 554 555 $sql .= ( $diffFriendly ? "\n)" : ( $postgresqlCompatible ? ')' : ' )' ) ); 556 557 return $sql . ( $withClosure ? ";\n" : "" ); 558 } 559 560 /*! 561 * \private 562 */ 563 function generateDropIndexSql( $table_name, $index_name, $def, $withClosure ) 564 { 565 if ($def['type'] == 'primary' ) 566 { 567 $sql = "ALTER TABLE $table_name DROP CONSTRAINT $index_name"; 568 } 569 else 570 { 571 $sql = "DROP INDEX $index_name"; 572 } 573 return $sql . ( $withClosure ? ";\n" : "" ); 574 } 575 576 /*! 577 * \private 578 */ 579 function generateFieldDef( $table_name, $field_name, $def, $add_default_not_null = true, $params ) 580 { 581 $diffFriendly = isset( $params['diff_friendly'] ) ? $params['diff_friendly'] : false; 582 583 if ( in_array( $field_name, $this->reservedKeywordList() ) ) 584 { 585 $sql_def = '"' . $field_name . '"'; 586 } 587 else 588 { 589 $sql_def = $field_name; 590 } 591 592 $sql_def .= ( $diffFriendly ? "\n " : " " ); 593 if ( $def['type'] != 'auto_increment' ) 594 { 595 $pgType = eZPgsqlSchema::convertFromStandardType( $def['type'], $def['length'] ); 596 $sql_def .= $pgType; 597 if ( eZPgsqlSchema::isTypeLengthSupported( $pgType ) and isset( $def['length'] ) && $def['length'] ) 598 { 599 $sql_def .= "({$def['length']})"; 600 } 601 if ( $add_default_not_null ) 602 { 603 $defaultDef = eZPGSQLSchema::generateDefaultDef( false, false, $def, $params ); 604 if ( $defaultDef ) 605 { 606 $sql_def .= ( $diffFriendly ? "\n " : " " ); 607 $sql_def .= rtrim( $defaultDef ); 608 } 609 $nullDef = eZPGSQLSchema::generateNullDef( false, false, $def, $params ); 610 if ( $nullDef ) 611 { 612 $sql_def .= ( $diffFriendly ? "\n " : " " ); 613 $sql_def .= trim( $nullDef ); 614 } 615 } 616 } 617 else 618 { 619 if ( $diffFriendly ) 620 { 621 $sql_def .= "integer\n DEFAULT nextval('{$table_name}_s'::text)\n NOT NULL"; 622 } 623 else 624 { 625 $sql_def .= "integer DEFAULT nextval('{$table_name}_s'::text) NOT NULL"; 626 } 627 } 628 return $sql_def; 629 } 630 631 /*! 632 \private 633 */ 634 function generateDefaultDef( $table_name, $field_name, $def, $params ) 635 { 636 $postgresqlCompatible = isset( $params['compatible_sql'] ) ? $params['compatible_sql'] : false; 637 $sql_def = ''; 638 if ( $table_name and $field_name ) 639 { 640 $sql_def .= "ALTER TABLE $table_name ALTER $field_name SET "; 641 } 642 if ( array_key_exists( 'default', $def ) and 643 $def['default'] !== false ) 644 { 645 if ( $def['default'] === null ) 646 { 647 if ( !$postgresqlCompatible ) 648 $sql_def .= "DEFAULT NULL "; 649 } 650 else if ( $def['default'] !== false ) 651 { 652 if ( $def['type'] == 'int' ) 653 { 654 $sql_def .= "DEFAULT {$def['default']} "; 655 } 656 else if ( $def['type'] == 'float' ) 657 { 658 $sql_def .= "DEFAULT {$def['default']}::double precision "; 659 } 660 else if ( $def['type'] == 'varchar' ) 661 { 662 $sql_def .= "DEFAULT '{$def['default']}'::character varying "; 663 } 664 else if ( $def['type'] == 'char' ) 665 { 666 $sql_def .= "DEFAULT '{$def['default']}'::bpchar "; 667 } 668 else 669 { 670 $sql_def .= "DEFAULT '{$def['default']}' "; 671 } 672 } 673 } 674 else if ( $table_name and $field_name ) 675 { 676 return false; 677 } 678 return $sql_def; 679 } 680 681 /*! 682 \private 683 */ 684 function generateNullDef( $table_name, $field_name, $def, $params ) 685 { 686 $sql_def = ''; 687 if ( $table_name and $field_name ) 688 { 689 $sql_def .= "ALTER TABLE $table_name ALTER $field_name SET "; 690 } 691 if ( isset( $def['not_null'] ) && ( $def['not_null'] ) ) 692 { 693 $sql_def .= 'NOT NULL '; 694 } 695 else if ( $table_name and $field_name ) 696 { 697 return false; 698 } 699 return $sql_def; 700 } 701 702 /*! 703 * \private 704 */ 705 function generateAddFieldSql( $table_name, $field_name, $def, $params ) 706 { 707 $sql = "ALTER TABLE $table_name ADD COLUMN "; 708 $sql .= eZPgsqlSchema::generateFieldDef( $table_name, $field_name, $def, false, $params ) . ";\n"; 709 $defaultSQL = eZPGSQLSchema::generateDefaultDef( $table_name, $field_name, $def, $params ); 710 if ( $defaultSQL ) 711 $sql .= $defaultSQL . ";\n"; 712 $nullSQL = eZPGSQLSchema::generateNullDef( $table_name, $field_name, $def, $params ); 713 if ( $nullSQL ) 714 $sql .= $nullSQL . ";\n"; 715 $sql .= "\n"; 716 return $sql; 717 } 718 719 /*! 720 * \private 721 */ 722 function generateAlterFieldSql( $table_name, $field_name, $def, $params ) 723 { 724 $sql = "ALTER TABLE $table_name RENAME COLUMN $field_name TO " . $field_name . "_tmp;\n"; 725 $sql .= "ALTER TABLE $table_name ADD COLUMN "; 726 $sql .= eZPgsqlSchema::generateFieldDef( $table_name, $field_name, $def, false, $params ) . ";\n"; 727 $defaultSQL = eZPGSQLSchema::generateDefaultDef( $table_name, $field_name, $def, $params ); 728 if ( $defaultSQL ) 729 $sql .= $defaultSQL . ";\n"; 730 $nullSQL = eZPGSQLSchema::generateNullDef( $table_name, $field_name, $def, $params ); 731 if ( $nullSQL ) 732 $sql .= $nullSQL . ";\n"; 733 $sql .= "UPDATE $table_name SET $field_name=" . $field_name . "_tmp;\n"; 734 $sql .= "ALTER TABLE $table_name DROP COLUMN " . $field_name . "_tmp;\n\n"; 735 return $sql; 736 } 737 738 /*! 739 \reimp 740 */ 741 function generateTableSchema( $table, $table_def, $params ) 742 { 743 $arrays = $this->generateTableArrays( $table, $table_def, $params, true ); 744 return ( join( "\n\n", $arrays['sequences'] ) . "\n" . 745 join( "\n\n", $arrays['tables'] ) . "\n" . 746 join( "\n\n", $arrays['indexes'] ) . "\n" . 747 join( "\n\n", $arrays['constraints'] ) . "\n" ); 748 } 749 750 /*! 751 \reimp 752 */ 753 function generateTableSQLList( $table, $table_def, $params, $separateTypes ) 754 { 755 $arrays = $this->generateTableArrays( $table, $table_def, $params, false ); 756 757 // If we have to separate the types the current array is sufficient 758 if ( $separateTypes ) 759 return $arrays; 760 return array_merge( $arrays['sequences'], 761 $arrays['tables'], 762 $arrays['indexes'], 763 $arrays['constraints'] ); 764 } 765 766 /*! 767 \private 768 \param $table The table name 769 \param $table_def The table structure, see eZDBSchemaInterface for more details 770 \param $params An associative array with optional parameters which controls the output of SQLs 771 \param $withClosure If \c true then the SQLs will contain semi-colons to close them. 772 */ 773 function generateTableArrays( $table, $table_def, $params, $withClosure ) 774 { 775 $diffFriendly = isset( $params['diff_friendly'] ) ? $params['diff_friendly'] : false; 776 $postgresqlCompatible = isset( $params['compatible_sql'] ) ? $params['compatible_sql'] : false; 777 778 $arrays = array( 'sequences' => array(), 779 'tables' => array(), 780 'indexes' => array(), 781 'constraints' => array() ); 782 783 $sql_fields = array(); 784 785 $spacing = $postgresqlCompatible ? ' ' : ' '; 786 787 // First we need to check if we use auto increment fields as 788 // sequences need to exist before we use them 789 foreach ( $table_def['fields'] as $field_name => $field_def ) 790 { 791 if ( $field_def['type'] == 'auto_increment' ) 792 { 793 $sequenceFields = array( "CREATE SEQUENCE {$table}_s", 794 "START 1", 795 "INCREMENT 1", 796 "MAXVALUE 9223372036854775807", 797 "MINVALUE 1", 798 "CACHE 1" ); 799 $arrays['sequences'][] = join( "\n$spacing", $sequenceFields ) . ( $withClosure ? ';' : '' ); 800 } 801 } 802 803 $sql = "CREATE TABLE $table (\n"; 804 $fields = $table_def['fields']; 805 foreach ( $fields as $field_name => $field_def ) 806 { 807 $sql_fields[] = $spacing . eZPgsqlSchema::generateFieldDef( $table, $field_name, $field_def, true, $params ); 808 } 809 $sql .= join( ",\n", $sql_fields ) . ( $withClosure ? "\n);" : "\n)" ); 810 $arrays['tables'][] = $sql; 811 812 foreach ( $table_def['indexes'] as $index_name => $index_def ) 813 { 814 if ( $index_def['type'] != 'primary' ) 815 { 816 $arrays['indexes'][] = eZPgsqlSchema::generateAddIndexSql( $table, $index_name, $index_def, $params, $withClosure ); 817 } 818 } 819 foreach ( $table_def['indexes'] as $index_name => $index_def ) 820 { 821 if ( $index_def['type'] == 'primary' ) 822 { 823 $arrays['constraints'][] = eZPgsqlSchema::generateAddIndexSql( $table, $index_name, $index_def, $params, $withClosure ); 824 } 825 } 826 827 return $arrays; 828 } 829 830 831 /*! 832 \reimp 833 834 This calls eZDBSchemaInterface::generateTableInsertSQLList() and adds a setval SQL if 835 the table has auto increments. 836 */ 837 function generateTableInsertSQLList( $tableName, $tableDef, $dataEntries, $params, $withClosure = true ) 838 { 839 $sqlList = eZDBSchemaInterface::generateTableInsertSQLList( $tableName, $tableDef, $dataEntries, $params, $withClosure ); 840 841 foreach ( $tableDef['fields'] as $fieldName => $fieldDef ) 842 { 843 if ( $fieldDef['type'] == 'auto_increment' ) 844 { 845 $sql = "SELECT setval('" . $tableName . "_s',max(" . $fieldName . ")+1) FROM " . $tableName; 846 if ( $withClosure ) 847 $sql .= ";"; 848 $sqlList[] = $sql; 849 } 850 } 851 return $sqlList; 852 } 853 854 /*! 855 \reimp 856 */ 857 function generateSchemaFile( $schema, $params = array() ) 858 { 859 $sql = ''; 860 $postgresqlCompatible = isset( $params['compatible_sql'] ) ? $params['compatible_sql'] : false; 861 862 $i = 0; 863 $allArrays = array( 'sequences' => array(), 864 'tables' => array(), 865 'indexes' => array(), 866 'constraints' => array() ); 867 868 foreach ( $schema as $table => $tableDef ) 869 { 870 // Skip the info structure, this is not a table 871 if ( $table == '_info' ) 872 continue; 873 874 $arrays = $this->generateTableArrays( $table, $tableDef, $params, true ); 875 if ( $postgresqlCompatible ) 876 { 877 $allArrays['sequences'] = array_merge( $allArrays['sequences'], 878 $arrays['sequences'] ); 879 $allArrays['tables'] = array_merge( $allArrays['tables'], 880 $arrays['tables'] ); 881 $allArrays['indexes'] = array_merge( $allArrays['indexes'], 882 $arrays['indexes'] ); 883 $allArrays['constraints'] = array_merge( $allArrays['constraints'], 884 $arrays['constraints'] ); 885 } 886 else 887 { 888 if ( $i > 0 ) 889 $sql .= "\n\n"; 890 ++$i; 891 892 $sql .= ( join( "\n", $arrays['sequences'] ) . "\n" . 893 join( "\n", $arrays['tables'] ) . "\n" . 894 join( "\n", $arrays['indexes'] ) . "\n" . 895 join( "\n", $arrays['constraints'] ) . "\n" ); 896 } 897 } 898 899 if ( $postgresqlCompatible ) 900 { 901 $sql = ( str_repeat( "\n", 11 ) . 902 join( str_repeat( "\n", 8 ), $allArrays['sequences'] ) . str_repeat( "\n", 8 ) . 903 join( str_repeat( "\n", 8 ), $allArrays['tables'] ) . str_repeat( "\n", 8 ) . 904 join( str_repeat( "\n", 7 ), $allArrays['indexes'] ) . str_repeat( "\n", 8 ) . 905 join( str_repeat( "\n", 7 ), $allArrays['constraints'] ) . str_repeat( "\n", 8 ) ); 906 } 907 908 return $sql; 909 } 910 911 /*! 912 * \private 913 */ 914 function generateDropTable( $table ) 915 { 916 return "DROP TABLE $table;\n"; 917 } 918 919 /*! 920 \reimp 921 */ 922 function escapeSQLString( $value ) 923 { 924 $value = str_replace( "'", "\'", $value ); 925 $value = str_replace( "\"", "\\\"", $value ); 926 return $value; 927 } 928 929 /*! 930 \reimp 931 */ 932 function schemaType() 933 { 934 return 'postgresql'; 935 } 936 937 /*! 938 \reimp 939 */ 940 function schemaName() 941 { 942 return 'PostgreSQL'; 943 } 944 945 /*! 946 \return An array with keywords that are reserved by PostgreSQL. 947 */ 948 function reservedKeywordList() 949 { 950 return array( 'abort', 951 'absolute', 952 'access', 953 'action', 954 'add', 955 'after', 956 'aggregate', 957 'all', 958 'alter', 959 'analyse', 960 'analyze', 961 'and', 962 'any', 963 'as', 964 'asc', 965 'assertion', 966 'assignment', 967 'at', 968 'authorization', 969 'backward', 970 'before', 971 'begin', 972 'between', 973 'bigint', 974 'binary', 975 'bit', 976 'boolean', 977 'both', 978 'by', 979 'cache', 980 'called', 981 'cascade', 982 'case', 983 'cast', 984 'chain', 985 'char', 986 'character', 987 'characteristics', 988 'check', 989 'checkpoint', 990 'class', 991 'close', 992 'cluster', 993 'coalesce', 994 'collate', 995 'column', 996 'comment', 997 'commit', 998 'committed', 999 'constraint', 1000 'constraints', 1001 'conversion', 1002 'convert', 1003 'copy', 1004 'create', 1005 'createdb', 1006 'createuser', 1007 'cross', 1008 'current_date', 1009 'current_time', 1010 'current_timestamp', 1011 'current_user', 1012 'cursor', 1013 'cycle', 1014 'database', 1015 'day', 1016 'deallocate', 1017 'dec', 1018 'decimal', 1019 'declare', 1020 'default', 1021 'deferrable', 1022 'deferred', 1023 'definer', 1024 'delete', 1025 'delimiter', 1026 'delimiters', 1027 'desc', 1028 'distinct', 1029 'do', 1030 'domain', 1031 'double', 1032 'drop', 1033 'each', 1034 'else', 1035 'encoding', 1036 'encrypted', 1037 'end', 1038 'escape', 1039 'except', 1040 'exclusive', 1041 'execute', 1042 'exists', 1043 'explain', 1044 'external', 1045 'extract', 1046 'false', 1047 'fetch', 1048 'float', 1049 'for', 1050 'force', 1051 'foreign', 1052 'forward', 1053 'freeze', 1054 'from', 1055 'full', 1056 'function', 1057 'get', 1058 'global', 1059 'grant', 1060 'group', 1061 'handler', 1062 'having', 1063 'hour', 1064 'ilike', 1065 'immediate', 1066 'immutable', 1067 'implicit', 1068 'in', 1069 'increment', 1070 'index', 1071 'inherits', 1072 'initially', 1073 'inner', 1074 'inout', 1075 'input', 1076 'insensitive', 1077 'insert', 1078 'instead', 1079 'int', 1080 'integer', 1081 'intersect', 1082 'interval', 1083 'into', 1084 'invoker', 1085 'is', 1086 'isnull', 1087 'isolation', 1088 'join', 1089 'key', 1090 'lancompiler', 1091 'language', 1092 'leading', 1093 'left', 1094 'level', 1095 'like', 1096 'limit', 1097 'listen', 1098 'load', 1099 'local', 1100 'localtime', 1101 'localtimestamp', 1102 'location', 1103 'lock', 1104 'match', 1105 'maxvalue', 1106 'minute', 1107 'minvalue', 1108 'mode', 1109 'month', 1110 'move', 1111 'names', 1112 'national', 1113 'natural', 1114 'nchar', 1115 'new', 1116 'next', 1117 'no', 1118 'nocreatedb', 1119 'nocreateuser', 1120 'none', 1121 'not', 1122 'nothing', 1123 'notify', 1124 'notnull', 1125 'null', 1126 'nullif', 1127 'numeric', 1128 'of', 1129 'off', 1130 'offset', 1131 'oids', 1132 'old', 1133 'on', 1134 'only', 1135 'operator', 1136 'option', 1137 'or', 1138 'order', 1139 'out', 1140 'outer', 1141 'overlaps', 1142 'overlay', 1143 'owner', 1144 'partial', 1145 'password', 1146 'path', 1147 'pendant', 1148 'placing', 1149 'position', 1150 'precision', 1151 'prepare', 1152 'primary', 1153 'prior', 1154 'privileges', 1155 'procedural', 1156 'procedure', 1157 'read', 1158 'real', 1159 'recheck', 1160 'references', 1161 'reindex', 1162 'relative', 1163 'rename', 1164 'replace', 1165 'reset', 1166 'restrict', 1167 'returns', 1168 'revoke', 1169 'right', 1170 'rollback', 1171 'row', 1172 'rule', 1173 'schema', 1174 'scroll', 1175 'second', 1176 'security', 1177 'select', 1178 'sequence', 1179 'serializable', 1180 'session', 1181 'session_user', 1182 'set', 1183 'setof', 1184 'share', 1185 'show', 1186 'similar', 1187 'simple', 1188 'smallint', 1189 'some', 1190 'stable', 1191 'start', 1192 'statement', 1193 'statistics', 1194 'stdin', 1195 'stdout', 1196 'storage', 1197 'strict', 1198 'substring', 1199 'sysid', 1200 'table', 1201 'temp', 1202 'template', 1203 'temporary', 1204 'then', 1205 'time', 1206 'timestamp', 1207 'to', 1208 'toast', 1209 'trailing', 1210 'transaction', 1211 'treat', 1212 'trigger', 1213 'trim', 1214 'true', 1215 'truncate', 1216 'trusted', 1217 'type', 1218 'unencrypted', 1219 'union', 1220 'unique', 1221 'unknown', 1222 'unlisten', 1223 'until', 1224 'update', 1225 'usage', 1226 'user', 1227 'using', 1228 'vacuum', 1229 'valid', 1230 'validator', 1231 'values', 1232 'varchar', 1233 'varying', 1234 'verbose', 1235 'version', 1236 'view', 1237 'volatile', 1238 'when', 1239 'where', 1240 'with', 1241 'without', 1242 'work', 1243 'write', 1244 'year', 1245 'zone' ); 1246 } 1247 } 1248 ?>
titre
Description
Corps
titre
Description
Corps
titre
Description
Corps
titre
Corps
| Généré le : Sat Feb 24 10:30:04 2007 | par Balluche grâce à PHPXref 0.7 |