[Sommaire] [Imprimer]

   1  <?php
   2  //
   3  // Created on: <30-Jan-2004 10:14:58 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 eZMysqlSchema ezmysqlschema.php
  29    \ingroup eZDBSchema
  30    \brief Handles schemas for MySQL
  31  
  32  */
  33  
  34  include_once ( 'lib/ezdbschema/classes/ezdbschemainterface.php' );
  35  
  36  class eZMysqlSchema extends eZDBSchemaInterface
  37  {
  38  
  39      /*!
  40       \reimp
  41       Constructor
  42  
  43       \param db instance
  44      */
  45      function eZMysqlSchema( $params )
  46      {
  47          $this->eZDBSchemaInterface( $params );
  48      }
  49  
  50      /*!
  51       \reimp
  52      */
  53      function schema( $params = array() )
  54      {
  55          $params = array_merge( array( 'meta_data' => false,
  56                                        'format' => 'generic' ),
  57                                 $params );
  58          $schema = array();
  59  
  60          if ( $this->Schema === false )
  61          {
  62              $tableArray = $this->DBInstance->arrayQuery( "SHOW TABLES" );
  63  
  64              foreach( $tableArray as $tableNameArray )
  65              {
  66                  $table_name = current( $tableNameArray );
  67                  $schema_table['name'] = $table_name;
  68                  $schema_table['fields'] = $this->fetchTableFields( $table_name, $params );
  69                  $schema_table['indexes'] = $this->fetchTableIndexes( $table_name, $params );
  70  
  71                  $schema[$table_name] = $schema_table;
  72              }
  73              $this->transformSchema( $schema, $params['format'] == 'local' );
  74              ksort( $schema );
  75              $this->Schema = $schema;
  76          }
  77          else
  78          {
  79              $this->transformSchema( $this->Schema, $params['format'] == 'local' );
  80              $schema = $this->Schema;
  81          }
  82          return $schema;
  83      }
  84  
  85      /*!
  86       \private
  87  
  88       \param table name
  89       */
  90  	function fetchTableFields( $table, $params )
  91      {
  92          $fields = array();
  93  
  94          $resultArray = $this->DBInstance->arrayQuery( "DESCRIBE $table" );
  95  
  96          foreach( $resultArray as $row )
  97          {
  98              $field = array();
  99              $field['type'] = $this->parseType ( $row['Type'], $field['length'] );
 100              if ( !$field['length'] )
 101              {
 102                  unset( $field['length'] );
 103              }
 104              $field['not_null'] = 0;
 105              if ( $row['Null'] != 'YES' )
 106              {
 107                  $field['not_null'] = '1';
 108              }
 109              $field['default'] = false;
 110              if ( !$field['not_null'] )
 111              {
 112                  if ( $row['Default'] === null )
 113                      $field['default'] = null;
 114                  else
 115                      $field['default'] = (string)$row['Default'];
 116              }
 117              else
 118              {
 119                  $field['default'] = (string)$row['Default'];
 120              }
 121  
 122              $numericTypes = array( 'float', 'int' );
 123              $blobTypes = array( 'tinytext', 'text', 'mediumtext', 'longtext' );
 124              $charTypes = array( 'varchar', 'char' );
 125              if ( in_array( $field['type'], $charTypes ) )
 126              {
 127                  if ( !$field['not_null'] )
 128                  {
 129                      if ( $field['default'] === null )
 130                      {
 131                          $field['default'] = null;
 132                      }
 133                      else if ( $field['default'] === false )
 134                      {
 135                          $field['default'] = '';
 136                      }
 137                  }
 138              }
 139              else if ( in_array( $field['type'], $numericTypes ) )
 140              {
 141                  if ( $field['default'] === false )
 142                  {
 143                      if ( $field['not_null'] )
 144                      {
 145                          $field['default'] = 0;
 146                      }
 147                  }
 148                  else if ( $field['type'] == 'int' )
 149                  {
 150                      if ( $field['not_null'] or
 151                           is_numeric( $field['default'] ) )
 152                          $field['default'] = (int)$field['default'];
 153                  }
 154                  else if ( $field['type'] == 'float' or
 155                            is_numeric( $field['default'] ) )
 156                  {
 157                      if ( $field['not_null'] or
 158                           is_numeric( $field['default'] ) )
 159                          $field['default'] = (float)$field['default'];
 160                  }
 161              }
 162              else if ( in_array( $field['type'], $blobTypes ) )
 163              {
 164                  // We do not want default for blobs.
 165                  $field['default'] = false;
 166              }
 167  
 168              if ( substr ( $row['Extra'], 'auto_increment' ) !== false )
 169              {
 170                  unset( $field['length'] );
 171                  $field['not_null'] = 0;
 172                  $field['default'] = false;
 173                  $field['type'] = 'auto_increment';
 174              }
 175  
 176              if ( !$field['not_null'] )
 177                  unset( $field['not_null'] );
 178  
 179              $fields[$row['Field']] = $field;
 180          }
 181          ksort( $fields );
 182  
 183          return $fields;
 184      }
 185  
 186      /*!
 187       * \private
 188       */
 189  	function fetchTableIndexes( $table, $params )
 190      {
 191          $metaData = false;
 192          if ( isset( $params['meta_data'] ) )
 193          {
 194              $metaData = $params['meta_data'];
 195          }
 196  
 197          $indexes = array();
 198  
 199          $resultArray = $this->DBInstance->arrayQuery( "SHOW INDEX FROM $table" );
 200  
 201          foreach( $resultArray as $row )
 202          {
 203              $kn = $row['Key_name'];
 204  
 205              if ( $kn == 'PRIMARY' )
 206              {
 207                  $indexes[$kn]['type'] = 'primary';
 208              }
 209              else
 210              {
 211                  $indexes[$kn]['type'] = $row['Non_unique'] ? 'non-unique' : 'unique';
 212              }
 213  
 214              $indexFieldDef = array( 'name' => $row['Column_name'] );
 215  
 216              // Include length if one is defined
 217              if ( $row['Sub_part'] )
 218              {
 219                  $indexFieldDef['mysql:length'] = (int)$row['Sub_part'];
 220              }
 221  
 222              // Check if we have any entries other than 'name', if not we skip the array definition
 223              if ( count( array_diff( array_keys( $indexFieldDef ), array( 'name' ) ) ) == 0 )
 224              {
 225                  $indexFieldDef = $indexFieldDef['name'];
 226              }
 227              $indexes[$kn]['fields'][$row['Seq_in_index'] - 1] = $indexFieldDef;
 228          }
 229          ksort( $indexes );
 230  
 231          return $indexes;
 232      }
 233  
 234  	function parseType( $type_info, &$length_info )
 235      {
 236          preg_match ( "@([a-z ]*)(\(([0-9]*|[0-9]*,[0-9]*)\))?@", $type_info, $matches );
 237          if ( isset( $matches[3] ) )
 238          {
 239              $length_info = $matches[3];
 240              if ( is_numeric( $length_info ) )
 241                  $length_info = (int)$length_info;
 242          }
 243          return $matches[1];
 244      }
 245  
 246      /*!
 247       * \private
 248       */
 249  	function generateAddIndexSql( $table_name, $index_name, $def, $params, $isEmbedded = false )
 250      {
 251          $diffFriendly = isset( $params['diff_friendly'] ) ? $params['diff_friendly'] : false;
 252          // If the output should compatible with existing MySQL dumps
 253          $mysqlCompatible = isset( $params['compatible_sql'] ) ? $params['compatible_sql'] : false;
 254          $sql = '';
 255  
 256          // Will be set to true when primary key is inside CREATE TABLE
 257          if ( !$isEmbedded )
 258          {
 259              $sql .= "ALTER TABLE $table_name ADD";
 260              $sql .= " ";
 261          }
 262  
 263          switch ( $def['type'] )
 264          {
 265              case 'primary':
 266              {
 267                  $sql .= 'PRIMARY KEY';
 268                  if ( $mysqlCompatible )
 269                      $sql .= " ";
 270              } break;
 271  
 272              case 'non-unique':
 273              {
 274                  if ( $isEmbedded )
 275                  {
 276                      $sql .= "KEY $index_name";
 277                  }
 278                  else
 279                  {
 280                      $sql .= "INDEX $index_name";
 281                  }
 282              } break;
 283  
 284              case 'unique':
 285              {
 286                  if ( $isEmbedded )
 287                  {
 288                      $sql .= "UNIQUE KEY $index_name";
 289                  }
 290                  else
 291                  {
 292                      $sql .= "UNIQUE $index_name";
 293                  }
 294              } break;
 295          }
 296  
 297          $sql .= ( $diffFriendly ? " (\n    " : ( $mysqlCompatible ? " (" : " ( " ) );
 298          $fields = $def['fields'];
 299          $i = 0;
 300          foreach ( $fields as $fieldDef )
 301          {
 302              if ( $i > 0 )
 303              {
 304                  $sql .= $diffFriendly ? ",\n    " : ( $mysqlCompatible ? ',' : ', ' );
 305              }
 306              if ( is_array( $fieldDef ) )
 307              {
 308                  $sql .= $fieldDef['name'];
 309                  if ( isset( $fieldDef['mysql:length'] ) )
 310                  {
 311                      if ( $diffFriendly )
 312                      {
 313                          $sql .= "(\n";
 314                          $sql .= "    " . str_repeat( ' ', strlen( $fieldDef['name'] ) );
 315                      }
 316                      else
 317                      {
 318                          $sql .= $mysqlCompatible ? "(" : "( ";
 319                      }
 320                      $sql .= $fieldDef['mysql:length'];
 321                      if ( $diffFriendly )
 322                      {
 323                          $sql .= ")";
 324                      }
 325                      else
 326                      {
 327                          $sql .= $mysqlCompatible ? ")" : " )";
 328                      }
 329                  }
 330              }
 331              else
 332              {
 333                  $sql .= $fieldDef;
 334              }
 335              ++$i;
 336          }
 337          $sql .= ( $diffFriendly ? "\n)" : ( $mysqlCompatible ? ")" : " )" ) );
 338  
 339          if ( !$isEmbedded )
 340          {
 341              return $sql . ";\n";
 342          }
 343          return $sql;
 344      }
 345  
 346      /*!
 347       * \private
 348       */
 349  	function generateDropIndexSql( $table_name, $index_name, $def, $params )
 350      {
 351          $sql = '';
 352          $sql .= "ALTER TABLE $table_name DROP ";
 353  
 354          if ( $def['type'] == 'primary' )
 355          {
 356              $sql .= 'PRIMARY KEY';
 357          }
 358          else
 359          {
 360              $sql .= "INDEX $index_name";
 361          }
 362          return $sql . ";\n";
 363      }
 364  
 365      /*!
 366       * \private
 367       */
 368  	function generateFieldDef( $field_name, $def, &$skip_primary, $params = null )
 369      {
 370          $diffFriendly = isset( $params['diff_friendly'] ) ? $params['diff_friendly'] : false;
 371          // If the output should compatible with existing MySQL dumps
 372          $mysqlCompatible = isset( $params['compatible_sql'] ) ? $params['compatible_sql'] : false;
 373  
 374          $sql_def = $field_name . ' ';
 375          $defaultText = $mysqlCompatible ? "default" : "DEFAULT";
 376  
 377          if ( $def['type'] != 'auto_increment' )
 378          {
 379              $defList = array();
 380              $type = $def['type'];
 381              if ( isset( $def['length'] ) )
 382              {
 383                  $type .= "({$def['length']})";
 384              }
 385              $defList[] = $type;
 386              if ( isset( $def['not_null'] ) && ( $def['not_null'] ) )
 387              {
 388                  $defList[] = 'NOT NULL';
 389              }
 390              if ( array_key_exists( 'default', $def ) )
 391              {
 392                  if ( $def['default'] === null )
 393                  {
 394                      $defList[] = "$defaultText NULL";
 395                  }
 396                  else if ( $def['default'] !== false )
 397                  {
 398                      $defList[] = "$defaultText '{$def['default']}'";
 399                  }
 400              }
 401              else if ( $def['type'] == 'varchar' )
 402              {
 403                  $defList[] = "$defaultText ''";
 404              }
 405              $sql_def .= join( $diffFriendly ? "\n    " : " ", $defList );
 406              $skip_primary = false;
 407          }
 408          else
 409          {
 410              $incrementText = $mysqlCompatible ? "auto_increment" : "AUTO_INCREMENT";
 411              if ( $diffFriendly )
 412              {
 413                  $sql_def .= "int(11)\n    NOT NULL\n    $incrementText";
 414              }
 415              else
 416              {
 417                  $sql_def .= "int(11) NOT NULL $incrementText";
 418              }
 419              $skip_primary = true;
 420          }
 421          return $sql_def;
 422      }
 423  
 424      /*!
 425       * \private
 426       */
 427  	function generateAddFieldSql( $table_name, $field_name, $def, $params )
 428      {
 429          $sql = "ALTER TABLE $table_name ADD COLUMN ";
 430          $sql .= eZMysqlSchema::generateFieldDef ( $field_name, $def, $dummy );
 431  
 432          return $sql . ";\n";
 433      }
 434  
 435      /*!
 436       * \private
 437       */
 438  	function generateAlterFieldSql( $table_name, $field_name, $def = array() )
 439      {
 440          $sql = "ALTER TABLE $table_name CHANGE COLUMN $field_name ";
 441          $sql .= eZMysqlSchema::generateFieldDef ( $field_name, $def, $dummy );
 442  
 443          return $sql . ";\n";
 444      }
 445  
 446      /*!
 447       \reimp
 448       \note Calls generateTableSQL() with \a $asArray set to \c false
 449      */
 450  	function generateTableSchema( $tableName, $table, $params )
 451      {
 452          return $this->generateTableSQL( $tableName, $table, $params, false, false );
 453      }
 454  
 455      /*!
 456       \reimp
 457       \note Calls generateTableSQL() with \a $asArray set to \c true
 458      */
 459  	function generateTableSQLList( $tableName, $table, $params, $separateTypes )
 460      {
 461          return $this->generateTableSQL( $tableName, $table, $params, true, $separateTypes );
 462      }
 463  
 464      /*!
 465       \private
 466  
 467       \param $asArray If \c true all SQLs are return in an array,
 468                       if not they are returned as a string.
 469       \note When returned as array the SQLs will not have a semi-colon to end the statement
 470      */
 471  	function generateTableSQL( $tableName, $tableDef, $params, $asArray, $separateTypes = false )
 472      {
 473          $diffFriendly = isset( $params['diff_friendly'] ) ? $params['diff_friendly'] : false;
 474          $mysqlCompatible = isset( $params['compatible_sql'] ) ? $params['compatible_sql'] : false;
 475  
 476          if ( $asArray )
 477          {
 478              if ( $separateTypes )
 479              {
 480                  $sqlList = array( 'tables' => array() );
 481              }
 482              else
 483              {
 484                  $sqlList = array();
 485              }
 486          }
 487  
 488          $sql = '';
 489          $skip_pk = false;
 490          $sql_fields = array();
 491          $sql .= "CREATE TABLE $tableName (\n";
 492  
 493          $fields = $tableDef['fields'];
 494  
 495          foreach ( $fields as $field_name => $field_def )
 496          {
 497              $sql_fields[] = '  ' . eZMysqlSchema::generateFieldDef( $field_name, $field_def, $skip_pk_flag, $params );
 498              if ( $skip_pk_flag )
 499              {
 500                  $skip_pk = true;
 501              }
 502          }
 503  
 504          // Make sure the order is as defined by 'offset'
 505          $indexes = $tableDef['indexes'];
 506  
 507          // We need to add all keys in table definition
 508          foreach ( $indexes as $index_name => $index_def )
 509          {
 510              $sql_fields[] = ( $diffFriendly ? '' : '  ' ) . eZMysqlSchema::generateAddIndexSql( $tableName, $index_name, $index_def, $params, true );
 511          }
 512          $sql .= join( ",\n", $sql_fields );
 513          $sql .= "\n)";
 514  
 515          // Add some extra table options if they are required
 516          $extraOptions = array();
 517          if ( isset( $params['table_type'] ) and $params['table_type'] )
 518          {
 519              $typeName = $this->tableStorageTypeName( $params['table_type'] );
 520              if ( $typeName )
 521              {
 522                  $extraOptions[] = "TYPE=" . $typeName;
 523              }
 524          }
 525          if ( isset( $params['table_charset'] ) and $params['table_charset'] )
 526          {
 527              $charsetName = $this->tableCharsetName( $params['table_charset'] );
 528              if ( $charsetName )
 529              {
 530                  $extraOptions[] = "DEFAULT CHARACTER SET " . $charsetName;
 531              }
 532          }
 533          if ( isset( $tableDef['options'] ) )
 534          {
 535              foreach( $tableDef['options'] as $optionType => $optionValue )
 536              {
 537                  $optionText = $this->generateTableOption( $tableName, $tableDef, $optionType, $optionValue, $params );
 538                  if ( $optionText )
 539                      $extraOptions[] = $optionText;
 540              }
 541          }
 542  
 543          if ( count( $extraOptions ) > 0 )
 544          {
 545              $sql .= " " . implode( $diffFriendly ? "\n" : " ", $extraOptions );
 546          }
 547  
 548          if ( $asArray )
 549          {
 550              if ( $separateTypes )
 551              {
 552                  $sqlList['tables'][] = $sql;
 553              }
 554              else
 555              {
 556                  $sqlList[] = $sql;
 557              }
 558          }
 559          else
 560          {
 561              $sql .= ";\n";
 562  
 563              if ( $mysqlCompatible )
 564              {
 565                  $sql .= "\n\n\n";
 566              }
 567          }
 568  
 569          return $asArray ? $sqlList : $sql;
 570      }
 571  
 572      /*!
 573       Detects known options and generates the MySQL SQL code for it.
 574       \return The SQL code as a string or \c false if not known.
 575       \param $optionType The type of option, the supported ones are:
 576                          - delay_key_write - If \a $optionValue is true then adds DELAY_KEY_WRITE=1
 577      */
 578      function generateTableOption( $tableName, $tableDef, $optionType, $optionValue, $params )
 579      {
 580          switch ( $optionType )
 581          {
 582              case 'mysql:delay_key_write':
 583              {
 584                  if ( $optionValue )
 585                      return 'DELAY_KEY_WRITE=1';
 586              } break;
 587          }
 588          return false;
 589      }
 590  
 591      /*!
 592        \return The name of the charset \a $charset in a format MySQL understands.
 593      */
 594      function tableCharsetName( $charset )
 595      {
 596          include_once ( 'lib/ezi18n/classes/ezcharsetinfo.php' );
 597          $charset = eZCharsetInfo::realCharsetCode( $charset );
 598          // Convert charset names into something MySQL will understand
 599          $charsetMapping = array( 'iso-8859-1' => 'latin1',
 600                                   'iso-8859-2' => 'latin2',
 601                                   'iso-8859-8' => 'hebrew',
 602                                   'iso-8859-7' => 'greek',
 603                                   'iso-8859-9' => 'latin5',
 604                                   'iso-8859-13' => 'latin7',
 605                                   'windows-1250' => 'cp1250',
 606                                   'windows-1251' => 'cp1251',
 607                                   'windows-1256' => 'cp1256',
 608                                   'windows-1257' => 'cp1257',
 609                                   'utf-8' => 'utf8',                 
 610                                   'koi8-r' => 'koi8r',
 611                                   'koi8-u' => 'koi8u' );
 612          $charset = strtolower( $charset );
 613          if ( isset( $charsetMapping ) )
 614              return $charsetMapping[$charset];
 615          return $charset;
 616      }
 617  
 618      /*!
 619        \return The name of storage type \a $type or \c false if not supported.
 620  
 621        \note Currently supports \c bdb, \c myisam and \c innodb.
 622  
 623        See http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html for overview of the types MySQL supports
 624      */
 625      function tableStorageTypeName( $type )
 626      {
 627          $type = strtolower( $type );
 628          switch ( $type )
 629          {
 630              case 'bdb':
 631              {
 632                  return 'BDB';
 633              }
 634  
 635              case 'myisam':
 636              {
 637                  return 'MyISAM';
 638              }
 639  
 640              case 'innodb':
 641              {
 642                  return 'InnoDB';
 643              }
 644          }
 645          return false;
 646      }
 647  
 648      /*!
 649       * \private
 650       */
 651      function generateDropTable( $table, $params )
 652      {
 653          return "DROP TABLE $table;\n";
 654      }
 655  
 656      /*!
 657       \reimp
 658       MySQL 3.22.5 and higher support multi-insert queries so if the current
 659       database has sufficient version we return \c true.
 660       If no database is connected we return \true.
 661      */
 662      function isMultiInsertSupported()
 663      {
 664          if ( is_subclass_of( $this->DBInstance, 'ezdbinterface' ) )
 665          {
 666              $versionInfo = $this->DBInstance->databaseServerVersion();
 667  
 668              // We require MySQL 3.22.5 to use multi-insert queries
 669              // http://dev.mysql.com/doc/mysql/en/INSERT.html
 670              return ( version_compare( $versionInfo['string'], '3.22.5' ) >= 0 );
 671          }
 672          return true;
 673      }
 674  
 675      /*!
 676       \reimp
 677      */
 678      function escapeSQLString( $value )
 679      {
 680          // We use the mysql function if it exists
 681          // if not we use a custom replace method that should sufficient
 682          if ( function_exists( 'mysql_escape_string' ) )
 683              return mysql_escape_string( $value );
 684          else
 685              str_replace( array( "\\",
 686                                  "'",
 687                                  '"',
 688                                  "\x00",
 689                                  "\x1a",
 690                                  "\n",
 691                                  "\r" ),
 692                           array( "\\\\",
 693                                  "\\'",
 694                                  "\\\"",
 695                                  "\\0",
 696                                  "\\Z",
 697                                  "\\n",
 698                                  "\\r" ),
 699                           $value );
 700      }
 701  
 702      /*!
 703       \reimp
 704      */
 705      function schemaType()
 706      {
 707          return 'mysql';
 708      }
 709  
 710      /*!
 711       \reimp
 712      */
 713      function schemaName()
 714      {
 715          return 'MySQL';
 716      }
 717  
 718  }
 719  ?>