eZ components PHP Cross Reference Developer Tools

Source: /DatabaseSchema/src/handlers/sqlite/writer.php - 598 lines - 19829 bytes - Summary - Text - Print

Description: File containing the ezcDbSchemaSqliteWriter class.

   1  <?php
   2  /**
   3   * File containing the ezcDbSchemaSqliteWriter class.
   4   *
   5   * @package DatabaseSchema
   6   * @version 1.4.4
   7   * @copyright Copyright (C) 2005-2010 eZ Systems AS. All rights reserved.
   8   * @license http://ez.no/licenses/new_bsd New BSD License
   9   */
  10  
  11  /**
  12   * Handler for storing database schemas and applying differences that uses SQLite as backend.
  13   *
  14   * @package DatabaseSchema
  15   * @version 1.4.4
  16   */
  17  class ezcDbSchemaSqliteWriter extends ezcDbSchemaCommonSqlWriter
  18  {
  19      /**
  20       * Contains a type map from DbSchema types to SQLite native types.
  21       *
  22       * @var array
  23       */
  24      private $typeMap = array(
  25          'integer' => 'integer',
  26          'boolean' => 'integer',
  27          'float' => 'real',
  28          'decimal' => 'numeric',
  29          'date' => 'date',
  30          'timestamp' => 'timestamp',
  31          'text' => 'text',
  32          'blob' => 'blob',
  33          'clob' => 'clob'
  34      );
  35  
  36      /**
  37       * Returns what type of schema writer this class implements.
  38       *
  39       * This method always returns ezcDbSchema::DATABASE
  40       *
  41       * @return int
  42       */
  43      public function getWriterType()
  44      {
  45          return ezcDbSchema::DATABASE;
  46      }
  47  
  48      /**
  49       * Checks if certain query allowed.
  50       *
  51       * Perform testing if table exist for DROP TABLE query 
  52       * to avoid stoping execution while try to drop not existent table.
  53       * 
  54       * @param ezcDbHandler $db
  55       * @param string       $query
  56       *
  57       * @return boolean false if query should not be executed.
  58       */
  59      public function isQueryAllowed( ezcDbHandler $db, $query )
  60      {
  61          if ( strstr($query, 'DROP COLUMN') || strstr($query, 'CHANGE') ) // detecting DROP COLUMN clause or field CHANGE clause 
  62          {
  63              return false;
  64          }
  65  
  66          if ( substr( $query, 0, 10 ) == 'DROP TABLE' )
  67          {
  68              $tableName = substr( $query, strlen( 'DROP TABLE ' ) );
  69              $result = $db->query( "SELECT count(*) AS count FROM 
  70                                      (SELECT * FROM sqlite_master UNION ALL 
  71                                       SELECT * FROM sqlite_temp_master)
  72                                     WHERE type='table' AND tbl_name={$tableName}" )->fetchAll();
  73              if ( $result[0]['count'] == 1 )
  74              {
  75                  return true;
  76              }
  77              else
  78              {
  79                  return false;
  80              }
  81          }
  82  
  83          return true;
  84      }
  85  
  86      /**
  87       * Returns what type of schema difference writer this class implements.
  88       *
  89       * This method always returns ezcDbSchema::DATABASE
  90       *
  91       * @return int
  92       */
  93      public function getDiffWriterType()
  94      {
  95          return ezcDbSchema::DATABASE;
  96      }
  97      /**
  98       * Applies the differences defined in $dbSchemaDiff to the database referenced by $db.
  99       *
 100       * This method uses {@link convertDiffToDDL} to create SQL for the
 101       * differences and then executes the returned SQL statements on the
 102       * database handler $db.
 103       *
 104       * @todo check for failed transaction
 105       *
 106       * @param ezcDbHandler    $db
 107       * @param ezcDbSchemaDiff $dbSchemaDiff
 108       */
 109      public function applyDiffToDb( ezcDbHandler $db, ezcDbSchemaDiff $dbSchemaDiff )
 110      {
 111          $db->beginTransaction();
 112          foreach ( $this->convertDiffToDDL( $dbSchemaDiff ) as $query )
 113          {
 114              if ( $this->isQueryAllowed( $db, $query ) ) 
 115              {
 116                  $db->exec( $query );
 117              }
 118              else
 119              {
 120                  // SQLite don't support SQL clause for removing columns
 121                  // perform emulation for this
 122                  if ( strstr( $query, 'DROP COLUMN' ) ) 
 123                  {
 124                      $db->commit();
 125                      $db->beginTransaction();
 126                      try
 127                      {
 128                          preg_match ( "/ALTER TABLE (.*) DROP COLUMN (.*)/" , $query, $matches );
 129                          if ( !$matches ) 
 130                          {
 131                              throw new ezcDbSchemaSqliteDropFieldException( 
 132                                              "Can't fetch field for droping from SQL query: $query" );
 133                          }
 134  
 135                          $tableName = trim( $matches[1], "'" );
 136                          $dropFieldName = trim( $matches[2], "'" );
 137  
 138                          $this->dropField( $db, $tableName , $dropFieldName );
 139                      }
 140                      catch ( ezcDbSchemaSqliteDropFieldException $e )
 141                      {
 142                      }
 143                      $db->commit();
 144                      $db->beginTransaction();
 145                  }
 146                  else if ( strstr( $query, 'CHANGE' ) ) // SQLite don't support SQL clause for changing columns 
 147                                                         // perform emulation for this
 148  
 149                  {
 150                      $db->commit();
 151                      $db->beginTransaction();
 152                      try
 153                      {
 154                          preg_match( "/ALTER TABLE (.*) CHANGE (.*?) (.*?) (.*)/" , $query, $matches );
 155                          $tableName = trim( $matches[1], "'" );
 156                          $changeFieldName = trim( $matches[2], "'" );
 157                          $changeFieldNewName = trim( $matches[3], "'" );
 158                          $changeFieldNewType = $matches[4];
 159                          $this->changeField( $db, $tableName, $changeFieldName, $changeFieldNewName, $changeFieldNewType );
 160  
 161                      }
 162                      catch ( ezcDbSchemaSqliteDropFieldException $e )
 163                      {
 164                      }
 165                      $db->commit();
 166                      $db->beginTransaction();
 167                  }
 168  
 169              }
 170          }
 171          $db->commit();
 172      }
 173  
 174      /**
 175       * Performs changing field in SQLite table.
 176       * (workaround for "ALTER TABLE table CHANGE field fieldDefinition" that not alowed in SQLite ).
 177       * 
 178       * @param ezcDbHandler    $db
 179       * @param string          $tableName
 180       * @param string          $changeFieldName
 181       * @param string          $changeFieldNewName
 182       * @param string          $changeFieldNewDefinition
 183       */
 184      private function changeField( ezcDbHandler $db, $tableName, $changeFieldName, $changeFieldNewName, $changeFieldNewDefinition )
 185      {
 186          $tmpTableName = $tableName.'_ezcbackup';
 187  
 188          $resultArray = $db->query( "PRAGMA TABLE_INFO( '$tableName' )" );
 189          $resultArray->setFetchMode( PDO::FETCH_NUM );
 190  
 191          $fieldsDefinitions = array();
 192          $fieldsList = array();
 193  
 194          foreach ( $resultArray as $row )
 195          {
 196              $fieldSql = array();
 197              $fieldSql[] = $row[1]; // name
 198              if ( $row[1] == $changeFieldName )
 199              {
 200                  // will recreate changed field with new definition
 201                  $fieldsDefinitions[] = "'$changeFieldNewName' $changeFieldNewDefinition";
 202                  $fieldsList[] = $fieldSql[0];
 203                  continue; 
 204              }
 205  
 206              $fieldSql[] = $row[2]; // type
 207  
 208              if ( $row[3] == '99' )
 209              {
 210                  $fieldSql[] = 'NOT NULL';
 211              }
 212  
 213              $fieldDefault = null;
 214              if ( $row[4] != '' )
 215              {
 216                  $fieldSql[]= "DEFAULT '{$row[4]}'";
 217              }
 218  
 219              if ( $row[5] =='1' )
 220              {
 221                  $fieldSql[] = 'PRIMARY KEY AUTOINCREMENT';
 222              }
 223  
 224              // FIXME: unsigned needs to be implemented
 225              $fieldUnsigned = false;
 226  
 227              $fieldsDefinitions[] = join ( ' ', $fieldSql );
 228              $fieldsList[] = $fieldSql[0];
 229          }
 230  
 231          if ( count( $fieldsDefinitions ) > 0 )
 232          {
 233              $fields = join( ', ', $fieldsDefinitions );
 234              $tmpTableCreateSql = "CREATE TEMPORARY TABLE '$tmpTableName'( $fields  );";
 235              $newTableCreateSql = "CREATE TABLE '$tableName'( $fields )" ;
 236              if ( count($fieldsList)>0 ) 
 237              {
 238                  $db->exec( $tmpTableCreateSql );
 239                  $db->exec( "INSERT INTO '$tmpTableName' SELECT ". join( ', ', $fieldsList )." FROM '$tableName';" );
 240                  $db->exec( "DROP TABLE '$tableName';" );
 241                  $db->exec( $newTableCreateSql );
 242                  $db->exec( "INSERT INTO '$tableName' SELECT ". join( ', ', $fieldsList )." FROM '$tmpTableName';" );
 243                  $db->exec( "DROP TABLE '$tmpTableName';" );
 244              }
 245              else
 246              {
 247                  // we had table with one column will drop table and recreate with changed column.
 248                  $db->exec( "DROP TABLE '$tableName';" );
 249                  $newTableCreateSql = "CREATE TABLE '$tableName'( $changeFieldNewName $changeFieldNewDefinition )" ;
 250                  $db->exec( $newTableCreateSql );
 251              }
 252          }
 253      }
 254  
 255      /**
 256       * Performs droping field from SQLite table using temporary table
 257       * (workaround for "ALTER TABLE table DROP field" that not alowed in SQLite ).
 258       *
 259       * @param ezcDbHandler    $db
 260       * @param string          $tableName
 261       * @param string          $dropFieldName
 262       */
 263      private function dropField( $db,  $tableName , $dropFieldName )
 264      {
 265          $tmpTableName = $tableName.'_ezcbackup';
 266  
 267          $resultArray = $db->query( "PRAGMA TABLE_INFO( $tableName )" );
 268          $resultArray->setFetchMode( PDO::FETCH_NUM );
 269  
 270          $fieldsDefinitions = array();
 271          $fieldsList = array();
 272  
 273          foreach ( $resultArray as $row )
 274          {
 275              $fieldSql = array();
 276              $fieldSql[] = "'{$row[1]}'"; // name
 277              if ( $row[1] == $dropFieldName )
 278              {
 279                  continue; // don't include droped fileld in temporary table
 280              }
 281  
 282              $fieldSql[] = $row[2]; // type
 283  
 284              if ( $row[3] == '99' )
 285              {
 286                  $fieldSql[] = 'NOT NULL';
 287              }
 288  
 289              $fieldDefault = null;
 290              if ( $row[4] != '' )
 291              {
 292                  $fieldSql[]= "DEFAULT '{$row[4]}'";
 293              }
 294  
 295              if ( $row[5] =='1' )
 296              {
 297                  $fieldSql[] = 'PRIMARY KEY AUTOINCREMENT';
 298              }
 299  
 300              // FIXME: unsigned needs to be implemented
 301              $fieldUnsigned = false;
 302  
 303              $fieldsDefinitions[] = join ( ' ', $fieldSql );
 304              $fieldsList[] = $fieldSql[0];
 305          }
 306  
 307          $fields = join( ', ', $fieldsDefinitions );
 308          $tmpTableCreateSql = "CREATE TEMPORARY TABLE '$tmpTableName'( $fields  );";
 309          $newTableCreateSql = "CREATE TABLE '$tableName'( $fields )" ;
 310          if ( count( $fieldsList ) > 0 ) 
 311          {
 312              $db->exec( $tmpTableCreateSql );
 313              $db->exec( "INSERT INTO '$tmpTableName' SELECT ". join( ', ', $fieldsList )." FROM '$tableName';" );
 314              $db->exec( "DROP TABLE '$tableName';" );
 315              $db->exec( $newTableCreateSql );
 316              $db->exec( "INSERT INTO '$tableName' SELECT ". join( ', ', $fieldsList )." FROM '$tmpTableName';" );
 317              $db->exec( "DROP TABLE '$tmpTableName';" );
 318          }
 319          else
 320          {
 321              throw new ezcDbSchemaDropAllColumnsException( 
 322                              "Trying to delete all columns in table: $tableName" );
 323          }
 324      }
 325  
 326  
 327      /**
 328       * Returns the differences definition in $dbSchema as database specific SQL DDL queries.
 329       *
 330       * @param ezcDbSchemaDiff $dbSchemaDiff
 331       *
 332       * @return array(string)
 333       */
 334      public function convertDiffToDDL( ezcDbSchemaDiff $dbSchemaDiff )
 335      {
 336          $this->diffSchema = $dbSchemaDiff;
 337  
 338          // reset queries
 339          $this->queries = array();
 340          $this->context = array();
 341  
 342          $this->generateDiffSchemaAsSql();
 343          return $this->queries;
 344      }
 345  
 346      /**
 347       * Adds a "drop table" query for the table $tableName to the internal list of queries.
 348       * 
 349       * @todo use DROP IF EXISTS that supported since SQLite 3.3
 350       *
 351       * @param string $tableName
 352       */
 353      protected function generateDropTableSql( $tableName )
 354      {
 355          // use DROP TABLE and isQueryAllowed() workaround to emulate DROP TABLE IF EXISTS.
 356          $this->queries[] = "DROP TABLE '$tableName'";
 357      }
 358  
 359      /**
 360       * Returns an appropriate default value for $type with $value.
 361       *
 362       * @param string $type
 363       * @param mixed  $value
 364       * @return string
 365       */
 366      protected function generateDefault( $type, $value )
 367      {
 368          switch ( $type )
 369          {
 370              case 'boolean':
 371                  return ( $value && $value !== 'false' ) ? '1' : '0';
 372  
 373              case 'integer':
 374                  return (int) $value;
 375  
 376              case 'float':
 377              case 'decimal':
 378                  return (float) $value;
 379  
 380              default:
 381                  return "'$value'";
 382          }
 383      }
 384  
 385      /**
 386       * Converts the generic field type contained in $fieldDefinition to a database specific field definition.
 387       *
 388       * @param ezcDbSchemaField $fieldDefinition
 389       * @return string
 390       */
 391      protected function convertFromGenericType( ezcDbSchemaField $fieldDefinition )
 392      {
 393          $typeAddition = '';
 394          if ( in_array( $fieldDefinition->type, array( 'decimal', 'text' ) ) )
 395          {
 396              if ( $fieldDefinition->length !== false && $fieldDefinition->length !== 0 )
 397              {
 398                  $typeAddition = "({$fieldDefinition->length})";
 399              }
 400          }
 401          if ( $fieldDefinition->type == 'text' && !$fieldDefinition->length )
 402          {
 403              $typeAddition = "(255)";
 404          }
 405          if ( $fieldDefinition->type == 'boolean' )
 406          {
 407              $typeAddition = '(1)';
 408          }
 409  
 410          if ( !isset( $this->typeMap[$fieldDefinition->type] ) )
 411          {
 412              throw new ezcDbSchemaUnsupportedTypeException( 'SQLite', $fieldDefinition->type );
 413          }
 414          $type = $this->typeMap[$fieldDefinition->type];
 415  
 416          return "$type$typeAddition";
 417      }
 418  
 419      /**
 420       * Returns a "CREATE TABLE" SQL statement part for the table $tableName.
 421       *
 422       * @param string  $tableName
 423       * @return string
 424       */
 425      protected function generateCreateTableSqlStatement( $tableName )
 426      {
 427          return "CREATE TABLE '{$tableName}'";
 428      }
 429  
 430      /**
 431       * Adds a "create table" query for the table $tableName with definition $tableDefinition to the internal list of queries.
 432       *
 433       * @param string           $tableName
 434       * @param ezcDbSchemaTable $tableDefinition
 435       */
 436      protected function generateCreateTableSql( $tableName, ezcDbSchemaTable $tableDefinition )
 437      {
 438          $this->context['skip_primary'] = false;
 439          parent::generateCreateTableSql( $tableName, $tableDefinition );
 440      }
 441  
 442      /**
 443       * Generates queries to upgrade a the table $tableName with the differences in $tableDiff.
 444       *
 445       * This method generates queries to migrate a table to a new version
 446       * with the changes that are stored in the $tableDiff property. It
 447       * will call different subfunctions for the different types of changes, and
 448       * those functions will add queries to the internal list of queries that is
 449       * stored in $this->queries.
 450       *
 451       * @param string $tableName
 452       * @param ezcDbSchemaTableDiff $tableDiff
 453       */
 454      protected function generateDiffSchemaTableAsSql( $tableName, ezcDbSchemaTableDiff $tableDiff )
 455      {
 456          $this->context['skip_primary'] = false;
 457          parent::generateDiffSchemaTableAsSql( $tableName, $tableDiff );
 458      }
 459  
 460      /**
 461       * Adds a "alter table" query to add the field $fieldName to $tableName with the definition $fieldDefinition.
 462       *
 463       * @param string           $tableName
 464       * @param string           $fieldName
 465       * @param ezcDbSchemaField $fieldDefinition
 466       */
 467      protected function generateAddFieldSql( $tableName, $fieldName, ezcDbSchemaField $fieldDefinition )
 468      {
 469          if ( $fieldDefinition->notNull && $fieldDefinition->default == null ) 
 470          {
 471              $fieldDefinition->default = $this->generateDefault( $fieldDefinition->type, 0 );
 472  
 473          }
 474          $this->queries[] = "ALTER TABLE '$tableName' ADD " . $this->generateFieldSql( $fieldName, $fieldDefinition );
 475      }
 476  
 477      /**
 478       * Adds a "alter table" query to change the field $fieldName to $tableName with the definition $fieldDefinition.
 479       *
 480       * @param string           $tableName
 481       * @param string           $fieldName
 482       * @param ezcDbSchemaField $fieldDefinition
 483       */
 484      protected function generateChangeFieldSql( $tableName, $fieldName, ezcDbSchemaField $fieldDefinition )
 485      {
 486          $this->queries[] = "ALTER TABLE '$tableName' CHANGE '$fieldName' " . $this->generateFieldSql( $fieldName, $fieldDefinition );
 487      }
 488  
 489      /**
 490       * Adds a "alter table" query to drop the field $fieldName from $tableName.
 491       * will be hooked on execution stage and workaround using temporary 
 492       * table will be performed.
 493       *
 494       * @param string $tableName
 495       * @param string $fieldName
 496       */
 497      protected function generateDropFieldSql( $tableName, $fieldName )
 498      {
 499          $this->queries[] = "ALTER TABLE '$tableName' DROP COLUMN '$fieldName'";
 500      }
 501  
 502      /**
 503       * Returns a column definition for $fieldName with definition $fieldDefinition.
 504       *
 505       * @param  string           $fieldName
 506       * @param  ezcDbSchemaField $fieldDefinition
 507       * @return string
 508       */
 509      protected function generateFieldSql( $fieldName, ezcDbSchemaField $fieldDefinition )
 510      {
 511          $sqlDefinition = "'$fieldName' ";
 512          $defList = array();
 513  
 514          $type = $this->convertFromGenericType( $fieldDefinition );
 515          $defList[] = $type;
 516  
 517          if ( $fieldDefinition->notNull )
 518          {
 519              $defList[] = 'NOT NULL';
 520          }
 521  
 522          if ( $fieldDefinition->autoIncrement )
 523          {
 524              $defList[] = "PRIMARY KEY AUTOINCREMENT";
 525              $this->context['skip_primary'] = true;
 526          }
 527          
 528          if ( !is_null( $fieldDefinition->default ) && !$fieldDefinition->autoIncrement )
 529          {
 530              $default = $this->generateDefault( $fieldDefinition->type, $fieldDefinition->default );
 531              $defList[] = "DEFAULT $default";
 532          }
 533  
 534          $sqlDefinition .= join( ' ', $defList );
 535  
 536          return $sqlDefinition;
 537      }
 538  
 539      /**
 540       * Adds a "create index" query to add the index $indexName to the 
 541       * table $tableName with definition $indexDefinition to the internal list of queries
 542       *
 543       * @param string           $tableName
 544       * @param string           $indexName
 545       * @param ezcDbSchemaIndex $indexDefinition
 546       */
 547      protected function generateAddIndexSql( $tableName, $indexName, ezcDbSchemaIndex $indexDefinition )
 548      {
 549          $sql = "";
 550          if ( $indexDefinition->primary )
 551          {
 552              if ( $this->context['skip_primary'] )
 553              {
 554                  return;
 555              }
 556              if ( $indexName == 'primary' ) 
 557              {
 558                  $indexName = $tableName.'_pri';
 559              }
 560              $sql = "CREATE UNIQUE INDEX '$indexName' ON '$tableName'";
 561          }
 562          else if ( $indexDefinition->unique )
 563          {
 564              $sql = "CREATE UNIQUE INDEX '$indexName' ON '$tableName'";
 565          }
 566          else
 567          {
 568              $sql = "CREATE INDEX '$indexName' ON '$tableName'";
 569          }
 570  
 571          $sql .= " ( ";
 572  
 573          $indexFieldSql = array();
 574          foreach ( $indexDefinition->indexFields as $indexFieldName => $dummy )
 575          {
 576              $indexFieldSql[] = "'$indexFieldName'";
 577          }
 578          $sql .= join( ', ', $indexFieldSql ) . " )";
 579  
 580          $this->queries[] = $sql;
 581      }
 582      
 583      /**
 584       * Adds a "alter table" query to revote the index $indexName from the table $tableName to the internal list of queries.
 585       *
 586       * @param string           $tableName
 587       * @param string           $indexName
 588       */
 589      protected function generateDropIndexSql( $tableName, $indexName )
 590      {
 591          if ( $indexName == 'primary') 
 592          {
 593              $indexName = $tableName.'_pri';
 594          }
 595          $this->queries[] = "DROP INDEX '$indexName'";
 596      }
 597  }
 598  ?>

title

Description

title

Description

title

Description

title

title

Body