[Sommaire] [Imprimer]

   1  <?php
   2  
   3  /*
   4   * This file is part of the symfony package.
   5   * (c) 2004-2006 Fabien Potencier <fabien.potencier@symfony-project.com>
   6   *
   7   * For the full copyright and license information, please view the LICENSE
   8   * file that was distributed with this source code.
   9   */
  10  
  11  /**
  12   *
  13   * @package    symfony
  14   * @subpackage addon
  15   * @author     Fabien Potencier <fabien.potencier@symfony-project.com>
  16   * @author     François Zaninotto <francois.zaninotto@symfony-project.com>
  17   * @version    SVN: $Id$
  18   */
  19  class sfPropelDatabaseSchema
  20  {
  21    protected $connection_name = '';
  22    protected $database        = array();
  23  
  24    public function asArray()
  25    {
  26      return array($this->connection_name => $this->database);
  27    }
  28  
  29    public function loadYAML($file)
  30    {
  31      $schema = sfYaml::load($file);
  32  
  33      if (count($schema) > 1)
  34      {
  35        throw new sfException('A schema.yml must only contain 1 database entry.');
  36      }
  37  
  38      $tmp = array_keys($schema);
  39      $this->connection_name = array_shift($tmp);
  40      if ($this->connection_name)
  41      {
  42        $this->database = $schema[$this->connection_name];
  43  
  44        $this->fixYAMLDatabase();
  45        $this->fixYAMLI18n();
  46        $this->fixYAMLColumns();
  47      }
  48    }
  49  
  50    public function asXML()
  51    {
  52      $xml = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n";
  53  
  54      $xml .= "<database name=\"$this->connection_name\"".$this->getAttributesFor($this->database).">\n";
  55  
  56      // tables
  57      foreach ($this->getChildren($this->database) as $tb_name => $table)
  58      {
  59        $xml .= "\n  <table name=\"$tb_name\"".$this->getAttributesFor($table).">\n";
  60  
  61        // columns
  62        foreach ($this->getChildren($table) as $col_name => $column)
  63        {
  64          $xml .= "    <column name=\"$col_name\"".$this->getAttributesForColumn($tb_name, $col_name, $column);
  65        }
  66  
  67        // indexes
  68        if (isset($table['_indexes']))
  69        {
  70          foreach ($table['_indexes'] as $index_name => $index)
  71          {
  72            $xml .= "    <index name=\"$index_name\">\n";
  73            foreach ($index as $index_column)
  74            {
  75              $xml .= "      <index-column name=\"$index_column\" />\n";
  76            }
  77            $xml .= "    </index>\n";
  78          }
  79        }
  80  
  81        // uniques
  82        if (isset($table['_uniques']))
  83        {
  84          foreach ($table['_uniques'] as $unique_name => $index)
  85          {
  86            $xml .= "    <unique name=\"$unique_name\">\n";
  87            foreach ($index as $unique_column)
  88            {
  89              $xml .= "      <unique-column name=\"$unique_column\" />\n";
  90            }
  91            $xml .= "    </unique>\n";
  92          }
  93        }
  94  
  95        // foreign-keys
  96        if (isset($table['_foreignKeys']))
  97        {
  98          foreach ($table['_foreignKeys'] as $fkey_name => $fkey)
  99          {
 100            $xml .= "    <foreign-key foreignTable=\"$fkey[foreignTable]\"";
 101  
 102            // foreign key name
 103            if (!is_numeric($fkey_name))
 104            {
 105              $xml .= " name=\"$fkey_name\"";
 106            }
 107  
 108            // onDelete
 109            if (isset($fkey['onDelete']))
 110            {
 111              $xml .= " onDelete=\"$fkey[onDelete]\"";
 112            }
 113  
 114            // onUpdate
 115            if (isset($fkey['onUpdate']))
 116            {
 117              $xml .= " onUpdate=\"$fkey[onUpdate]\"";
 118            }
 119            $xml .= ">\n";
 120  
 121            // references
 122            if (isset($fkey['references']))
 123            {
 124              foreach ($fkey['references'] as $reference)
 125              {
 126                $xml .= "      <reference local=\"$reference[local]\" foreign=\"$reference[foreign]\" />\n";
 127              }
 128            }
 129            $xml .= "    </foreign-key>\n";
 130          }
 131        }
 132  
 133        $xml .= "  </table>\n";
 134      }
 135      $xml .= "\n</database>\n";
 136  
 137      return $xml;
 138    }
 139  
 140    protected function fixYAMLDatabase()
 141    {
 142      if (!isset($this->database['_attributes']))
 143      {
 144        $this->database['_attributes'] = array();
 145      }
 146  
 147      // conventions for database attributes
 148      $this->setIfNotSet($this->database['_attributes'], 'defaultIdMethod', 'native');
 149      $this->setIfNotSet($this->database['_attributes'], 'noXsd', true);
 150      $this->setIfNotSet($this->database['_attributes'], 'package', 'lib.model');
 151    }
 152  
 153    protected function fixYAMLI18n()
 154    {
 155      foreach ($this->getTables() as $i18n_table => $columns)
 156      {
 157        $pos = strpos($i18n_table, '_i18n');
 158  
 159        $has_primary_key = false;
 160        foreach ($columns as $column => $attributes)
 161        {
 162          if (is_array($attributes) && array_key_exists('primaryKey', $attributes))
 163          {
 164             $has_primary_key = true;
 165          }
 166        }
 167  
 168        if ($pos > 0 && $pos == strlen($i18n_table) - 5 && !$has_primary_key)
 169        {
 170          // i18n table without primary key
 171          $main_table = $this->findTable(substr($i18n_table, 0, $pos));
 172  
 173          if ($main_table)
 174          {
 175            // set i18n attributes for main table
 176            $this->setIfNotSet($this->database[$main_table]['_attributes'], 'isI18N', 1);
 177            $this->setIfNotSet($this->database[$main_table]['_attributes'], 'i18nTable', $i18n_table);
 178  
 179            // set id and culture columns for i18n table
 180            $this->setIfNotSet($this->database[$i18n_table], 'id', array(
 181              'type'             => 'integer',
 182              'required'         => true, 
 183              'primaryKey'       => true,
 184              'foreignTable'     => $main_table,
 185              'foreignReference' => 'id',
 186              'onDelete'         => 'cascade'
 187            ));
 188            $this->setIfNotSet($this->database[$i18n_table], 'culture', array(
 189              'isCulture'  => true,
 190              'type'       => 'varchar',
 191              'size'       => '7',
 192              'required'   => true,
 193              'primaryKey' => true
 194            ));
 195          }
 196          else
 197          {
 198            throw new sfException(sprintf('Missing main table for internationalized table "%s".', $i18n_table));
 199          }
 200        }
 201      }
 202    }
 203  
 204    protected function fixYAMLColumns()
 205    {
 206      foreach ($this->getTables() as $table => $columns)
 207      {
 208        $has_primary_key = false;
 209        
 210        foreach ($columns as $column => $attributes)
 211        {
 212          if ($attributes == null)
 213          {
 214            // conventions for null attributes
 215            if ($column == 'created_at' || $column == 'updated_at')
 216            {
 217              // timestamp convention
 218              $this->database[$table][$column]['type']= 'timestamp';
 219            }
 220  
 221            if ($column == 'id')
 222            {
 223              // primary key convention
 224              $this->database[$table]['id'] = array(
 225                'type'          => 'integer',
 226                'required'      => true,
 227                'primaryKey'    => true,
 228                'autoincrement' => true
 229              );
 230              $has_primary_key = true;
 231            }
 232            
 233            $pos = strpos($column, '_id');
 234            if ($pos > 0 && $pos == strlen($column) - 3)
 235            {
 236              // foreign key convention
 237              $foreign_table = $this->findTable(substr($column, 0, $pos));
 238              if ($foreign_table)
 239              {
 240                $this->database[$table][$column] = array(
 241                  'type'             => 'integer',
 242                  'foreignTable'     => $foreign_table,
 243                  'foreignReference' => 'id'
 244                );
 245              }
 246              else
 247              {
 248                throw new sfException(sprintf('Unable to resolve foreign table for column "%s"', $column));
 249              }
 250            }
 251            
 252          }
 253          else
 254          {
 255            if (!is_array($attributes))
 256            {
 257              // compact type given as single attribute
 258              $this->database[$table][$column] = $this->getAttributesFromCompactType($attributes);
 259            }
 260            else
 261            {
 262              if (isset($attributes['type']))
 263              {
 264                // compact type given as value of the type attribute
 265                $this->database[$table][$column] = array_merge($this->database[$table][$column], $this->getAttributesFromCompactType($attributes['type']));
 266              }
 267              if (isset($attributes['primaryKey']))
 268              {
 269                $has_primary_key = true;
 270              }
 271            }
 272          }
 273        }
 274  
 275        if (!$has_primary_key)
 276        {
 277          // convention for tables without primary key
 278          $this->database[$table]['id'] = array(
 279            'type'          => 'integer',
 280            'required'      => true,
 281            'primaryKey'    => true,
 282            'autoincrement' => true
 283          );
 284        }
 285      }
 286    }
 287  
 288    protected function getAttributesFromCompactType($type)
 289    {
 290      preg_match('/varchar\(([\d]+)\)/', $type, $matches);
 291      if (isset($matches[1]))
 292      {
 293        return array('type' => 'varchar', 'size' => $matches[1]);
 294      }
 295      else
 296      {
 297        return array('type' => $type);
 298      }
 299    }
 300  
 301    protected function setIfNotSet(&$entry, $key, $value)
 302    {
 303      if (!isset($entry[$key]))
 304      {
 305        $entry[$key] = $value;
 306      }
 307    }
 308  
 309    protected function findTable($table_name)
 310    {
 311      // find a table from a phpName or a name
 312      $table_match = false;
 313      foreach ($this->getTables() as $tb_name => $table)
 314      {
 315        if ((isset($table['_attributes']['phpName']) && $table['_attributes']['phpName'] == sfInflector::camelize($table_name)) || ($tb_name == $table_name))
 316        {
 317          $table_match = $tb_name;
 318        }
 319      }
 320  
 321      return $table_match;
 322    }
 323  
 324    protected function getAttributesForColumn($tb_name, $col_name, $column)
 325    {
 326      $attributes_string = '';
 327      if (is_array($column))
 328      {
 329        foreach ($column as $key => $value)
 330        {
 331          if (!in_array($key, array('foreignTable', 'foreignReference', 'onDelete', 'onUpdate', 'index', 'unique')))
 332          {
 333            $attributes_string .= " $key=\"".htmlspecialchars($this->getCorrectValueFor($key, $value))."\"";
 334          }
 335        }
 336        $attributes_string .= " />\n";
 337      }
 338      else
 339      {
 340        throw new sfException('Incorrect settings for column '.$col_name);
 341      }
 342  
 343      // conventions for foreign key attributes
 344      if (is_array($column) && isset($column['foreignTable']))
 345      {
 346        $attributes_string .= "    <foreign-key foreignTable=\"$column[foreignTable]\"";
 347        if (isset($column['onDelete']))
 348        {
 349          $attributes_string .= " onDelete=\"$column[onDelete]\"";
 350        }
 351        if (isset($column['onUpdate']))
 352        {
 353          $attributes_string .= " onUpdate=\"$column[onUpdate]\"";
 354        }
 355        $attributes_string .= ">\n";
 356        $attributes_string .= "      <reference local=\"$col_name\" foreign=\"$column[foreignReference]\" />\n";
 357        $attributes_string .= "    </foreign-key>\n";
 358      }
 359  
 360      // conventions for index and unique index attributes
 361      if (is_array($column) && isset($column['index']))
 362      {
 363        if ($column['index'] === 'unique')
 364        {
 365          $attributes_string .= "    <unique name=\"$tb_name}_$col_name}_unique\">\n";
 366          $attributes_string .= "      <unique-column name=\"$col_name\" />\n";
 367          $attributes_string .= "    </unique>\n";
 368        }
 369        else
 370        {
 371          $attributes_string .= "    <index name=\"$tb_name}_$col_name}_index\">\n";
 372          $attributes_string .= "      <index-column name=\"$col_name\" />\n";
 373          $attributes_string .= "    </index>\n";
 374        }
 375      }
 376  
 377      // conventions for sequence name attributes
 378      // required for databases using sequences for auto-increment columns (e.g. PostgreSQL or Oracle)
 379      if (is_array($column) && isset($column['sequence'])) 
 380      {
 381        $attributes_string .= "    <id-method-parameter value=\"$column[sequence]\" />\n";
 382      }
 383  
 384      return $attributes_string;
 385    }
 386  
 387    protected function getAttributesFor($tag)
 388    {
 389      if (!isset($tag['_attributes']))
 390      {
 391        return '';
 392      }
 393      $attributes = $tag['_attributes'];
 394      $attributes_string = '';
 395      foreach ($attributes as $key => $value)
 396      {
 397        $attributes_string .= ' '.$key.'="'.htmlspecialchars($this->getCorrectValueFor($key, $value)).'"';
 398      }
 399  
 400      return $attributes_string;
 401    }
 402  
 403    protected function getCorrectValueFor($key, $value)
 404    {
 405      $booleans = array('required', 'primaryKey', 'autoincrement', 'autoIncrement', 'noXsd', 'isI18N', 'isCulture');
 406      if (in_array($key, $booleans))
 407      {
 408        return $value == 1 ? 'true' : 'false';
 409      }
 410      else
 411      {
 412        return is_null($value) ? 'null' : $value;
 413      }
 414    }
 415  
 416    public function getTables()
 417    {
 418      return $this->getChildren($this->database);
 419    }
 420  
 421    public function getChildren($hash)
 422    {
 423      foreach ($hash as $key => $value)
 424      {
 425        // ignore special children (starting with _)
 426        if ($key[0] == '_')
 427        {
 428          unset($hash[$key]);
 429        }
 430      }
 431  
 432      return $hash;
 433    }
 434  
 435    public function loadXML($file)
 436    {
 437      $schema = simplexml_load_file($file);
 438      $database = array();
 439  
 440      // database
 441      list($database_name, $database_attributes) = $this->getNameAndAttributes($schema->attributes());
 442      if ($database_name)
 443      {
 444        $this->connection_name = $database_name;
 445      }
 446      else
 447      {
 448        throw new sfException('The database tag misses a name attribute');
 449      }
 450      if ($database_attributes)
 451      {
 452        $database['_attributes'] = $database_attributes;
 453      }
 454  
 455      // tables
 456      foreach ($schema as $table)
 457      {
 458        list($table_name, $table_attributes) = $this->getNameAndAttributes($table->attributes());
 459        if ($table_name)
 460        {
 461          $database[$table_name] = array();
 462        }
 463        else
 464        {
 465          throw new sfException('A table tag misses the name attribute');
 466        }
 467        if ($table_attributes)
 468        {
 469          $database[$table_name]['_attributes'] = $table_attributes;
 470        }
 471  
 472        // columns
 473        foreach ($table->xpath('column') as $column)
 474        {
 475          list($column_name, $column_attributes) = $this->getNameAndAttributes($column->attributes());
 476          if ($column_name)
 477          {
 478            $database[$table_name][$column_name] = $column_attributes;
 479          }
 480          else
 481          {
 482            throw new sfException('A column tag misses the name attribute');
 483          }
 484        }
 485  
 486        // foreign-keys
 487        $database[$table_name]['_foreign_keys'] = array();
 488        foreach ($table->xpath('foreign-key') as $foreign_key)
 489        {
 490          $foreign_key_table = array();
 491  
 492          // foreign key attributes
 493          if (isset($foreign_key['foreignTable']))
 494          {
 495            $foreign_key_table['foreign_table'] = (string) $foreign_key['foreignTable'];
 496          }
 497          else
 498          {
 499            throw new sfException('A foreign key misses the foreignTable attribute');
 500          } 
 501          if (isset($foreign_key['onDelete']))
 502          {
 503            $foreign_key_table['on_delete'] = (string) $foreign_key['onDelete'];
 504          }
 505          if (isset($foreign_key['onUpdate']))
 506          {
 507            $foreign_key_table['on_update'] = (string) $foreign_key['onUpdate'];
 508          }
 509  
 510          // foreign key references
 511          $foreign_key_table['references'] = array();
 512          foreach ($foreign_key->xpath('reference') as $reference)
 513          {
 514            $reference_attributes = array();
 515            foreach ($reference->attributes() as $reference_attribute_name => $reference_attribute_value)
 516            {
 517              $reference_attributes[$reference_attribute_name] = strval($reference_attribute_value);
 518            }
 519            $foreign_key_table['references'][] = $reference_attributes;
 520          }
 521  
 522          if (isset($foreign_key['name']))
 523          {
 524            $database[$table_name]['_foreign_keys'][(string)$foreign_key['name']] = $foreign_key_table;
 525          }
 526          else
 527          {
 528            $database[$table_name]['_foreign_keys'][] = $foreign_key_table;
 529          }
 530  
 531        }
 532        $this->removeEmptyKey($database[$table_name], '_foreign_keys');
 533  
 534        // indexes
 535        $database[$table_name]['_indexes'] = array();
 536        foreach ($table->xpath('index') as $index)
 537        {
 538          $index_keys = array();
 539          foreach ($index->xpath('index-column') as $index_key)
 540          {
 541            $index_keys[] = strval($index_key['name']);
 542          }
 543          $database[$table_name]['_indexes'][strval($index['name'])] = $index_keys;
 544        }
 545        $this->removeEmptyKey($database[$table_name], '_indexes');
 546  
 547        // unique indexes
 548        $database[$table_name]['_uniques'] = array();
 549        foreach ($table->xpath('unique') as $index)
 550        {
 551          $unique_keys = array();
 552          foreach ($index->xpath('unique-column') as $unique_key)
 553          {
 554            $unique_keys[] = strval($unique_key['name']);
 555          }
 556          $database[$table_name]['_uniques'][strval($index['name'])] = $unique_keys;
 557        }
 558        $this->removeEmptyKey($database[$table_name], '_uniques');
 559      }
 560      $this->database = $database;
 561      
 562      $this->fixXML();
 563    }
 564  
 565    public function fixXML()
 566    {
 567      $this->fixXMLForeignKeys();
 568      $this->fixXMLIndexes();
 569      // $this->fixXMLColumns();
 570    }
 571  
 572    protected function fixXMLForeignKeys()
 573    {
 574      foreach ($this->getTables() as $table => $columns)
 575      {
 576        if (isset($this->database[$table]['_foreign_keys']))
 577        {
 578          $foreign_keys = $this->database[$table]['_foreign_keys'];
 579          foreach ($foreign_keys as $foreign_key_name => $foreign_key_attributes)
 580          {
 581            // Only single foreign keys can be simplified
 582            if (count($foreign_key_attributes['references']) == 1)
 583            {
 584              $reference = $foreign_key_attributes['references'][0];
 585  
 586              // set simple foreign key
 587              $this->database[$table][$reference['local']]['foreignTable'] = $foreign_key_attributes['foreign_table'];
 588              $this->database[$table][$reference['local']]['foreignReference'] = $reference['foreign'];
 589              if (isset($foreign_key_attributes['on_delete']))
 590              {
 591                $this->database[$table][$reference['local']]['onDelete'] = $foreign_key_attributes['on_delete'];
 592              }
 593              if (isset($foreign_key_attributes['on_update']))
 594              {
 595                $this->database[$table][$reference['local']]['onUpdate'] = $foreign_key_attributes['on_update'];
 596              }
 597  
 598              // remove complex foreign key
 599              unset($this->database[$table]['_foreign_keys'][$foreign_key_name]);
 600            }
 601  
 602            $this->removeEmptyKey($this->database[$table], '_foreign_keys');
 603          }
 604        }
 605      }
 606    }
 607  
 608    protected function fixXMLIndexes()
 609    {
 610      foreach ($this->getTables() as $table => $columns)
 611      {
 612        if (isset($this->database[$table]['_indexes']))
 613        {
 614          $indexes = $this->database[$table]['_indexes'];
 615          foreach ($indexes as $index => $references)
 616          {
 617            // Only single indexes can be simplified
 618            if (count($references) == 1 && array_key_exists(substr($index, 0, strlen($index) - 6), $columns))
 619            {
 620              $reference = $references[0];
 621  
 622              // set simple index
 623              $this->database[$table][$reference]['index'] = 'true';
 624  
 625              // remove complex index
 626              unset($this->database[$table]['_indexes'][$index]);
 627            }
 628            
 629            $this->removeEmptyKey($this->database[$table], '_indexes');
 630          }
 631        }
 632        if (isset($this->database[$table]['_uniques']))
 633        {
 634          $uniques = $this->database[$table]['_uniques'];
 635          foreach ($uniques as $index => $references)
 636          {
 637            // Only single unique indexes can be simplified
 638            if (count($references) == 1 && array_key_exists(substr($index, 0, strlen($index) - 7), $columns))
 639            {
 640              $reference = $references[0];
 641  
 642              // set simple index
 643              $this->database[$table][$reference]['index'] = 'unique';
 644  
 645              // remove complex unique index
 646              unset($this->database[$table]['_uniques'][$index]);
 647            }
 648  
 649            $this->removeEmptyKey($this->database[$table], '_uniques');
 650          }
 651        }
 652      }
 653    }
 654  
 655    protected function fixXMLColumns()
 656    {
 657      foreach ($this->getTables() as $table => $columns)
 658      {
 659        foreach ($columns as $column => $attributes)
 660        {
 661          if ($column == 'id' && !array_diff($attributes, array('type' => 'integer', 'required' => 'true', 'primaryKey' => 'true', 'autoincrement' => 'true')))
 662          {
 663            // simplify primary keys
 664            $this->database[$table]['id'] = null;
 665          }
 666  
 667          if (($column == 'created_at') || ($column == 'updated_at') && !array_diff($attributes, array('type' => 'timestamp')))
 668          {
 669            // simplify timestamps
 670            $this->database[$table][$column] = null;
 671          }
 672  
 673          $pos                 = strpos($column, '_id');
 674          $has_fk_name         = $pos > 0 && $pos == strlen($column) - 3;
 675          $is_foreign_key      = isset($attributes['type']) && $attributes['type'] == 'integer' && isset($attributes['foreignReference']) && $attributes['foreignReference'] == 'id';
 676          $has_foreign_table   = isset($attributes['foreignTable']) && array_key_exists($attributes['foreignTable'], $this->getTables());
 677          $has_other_attribute = isset($attributes['onDelete']);
 678          if ($has_fk_name && $has_foreign_table && $is_foreign_key && !$has_other_attribute)
 679          {
 680            // simplify foreign key
 681            $this->database[$table][$column] = null;
 682          }
 683        }
 684      }
 685    }
 686  
 687    public function asYAML()
 688    {
 689      return sfYaml::dump(array($this->connection_name => $this->database));
 690    }
 691  
 692    protected function getNameAndAttributes($hash, $name_attribute = 'name')
 693    {
 694      // tag name
 695      $name = '';
 696      if (isset($hash[$name_attribute]))
 697      {
 698        $name = strval($hash[$name_attribute]);
 699        unset($hash[$name_attribute]);
 700      }
 701  
 702      // tag attributes
 703      $attributes = array();
 704      foreach ($hash as $attribute => $value)
 705      {
 706        $attributes[$attribute] = strval($value);
 707      }
 708  
 709      return array($name, $attributes);
 710    }
 711  
 712    protected function removeEmptyKey(&$hash, $key)
 713    {
 714      if (isset($hash[$key]) && !$hash[$key])
 715      {
 716        unset($hash[$key]);
 717      }
 718    }
 719  }