[Sommaire] [Imprimer]

   1  <?php 
   2  
   3      $_indices = array();
   4      $_previousColumns = array();
   5      // we're building an array of indices here which is smart about multi-column indices.
   6      // for example, if we have to indices foo(ColA) and bar(ColB, ColC), we have actually three indices already defined:
   7      // ColA, ColB+ColC, and ColB (but not ColC!). This is because of the way SQL multi-column indices work.
   8      // we will later match found, defined foreign key and referenced column definitions against this array to know whether we should create a new index for mysql or not
   9      foreach($table->getPrimaryKey() as $_primaryKeyColumn)
  10      {
  11          // do the above for primary keys
  12          $_previousColumns[] = "`" . $_primaryKeyColumn->getName() . "`";
  13          $_indices[] = implode(',', $_previousColumns);
  14      }
  15      $_tableIndices = array_merge($table->getIndices(), $table->getUnices());
  16      foreach($_tableIndices as $_index)
  17      {
  18          // same procedure, this time for unices and indices
  19          $_previousColumns = array();
  20          $_indexColumns = $_index->getColumns();
  21          foreach($_indexColumns as $_indexColumn)
  22          {
  23              $_previousColumns[] = "`" . $_indexColumn . "`";
  24              $_indices[] = implode(',', $_previousColumns);
  25          }
  26      }
  27  
  28      $_tables = $table->getDatabase()->getTables();
  29      $counter = 0;
  30      // we're determining which tables have foreign keys that point to this table, since MySQL needs an index on any column that is referenced by another table (yep, MySQL _is_ a PITA)
  31      foreach($_tables as $_table)
  32      {
  33          foreach($_table->getForeignKeys() as $_foreignKey)
  34          {
  35              if($_foreignKey->getForeignTableName() == $table->getName())
  36              {
  37                  $_foreignColumns = array();
  38                  foreach($_foreignKey->getForeignColumns() as $_foreignColumn)
  39                  {
  40                      $_foreignColumns[] = "`" . $_foreignColumn . "`";
  41                  }
  42                  if(!in_array(implode(',', $_foreignColumns), $_indices))
  43                  {
  44                      // no matching index defined in the schema, so we have to create one
  45                      $counter++;
  46                      if($counter > 1): ?>,<?php endif; 
  47                      ?> 
  48      INDEX `I_referenced_<?php echo $_foreignKey->getName(); ?>_<?php echo $counter; ?>` (<?php echo implode(',',$_foreignColumns); ?>)<?php
  49                  }
  50              }
  51          }
  52      }
  53  
  54              $hasReferencedColumns = $counter > 0;
  55        $counter = 0;
  56        foreach ($table->getForeignKeys() as $fk) {
  57          if($counter > 0 || $hasReferencedColumns): ?>,<?php endif;
  58          $counter++;
  59          $fnames = array();
  60          foreach ($fk->getForeignColumns() as $column) {
  61              $fnames[] = "`" . $column . "`";
  62          }
  63  
  64          $lnames = array();
  65          foreach ($fk->getLocalColumns() as $column) {
  66              $lnames[] = "`" . $column . "`";
  67          }
  68  
  69          $constraintName = "`" . $fk->getName() . "`";
  70                  $indexName = "`" . substr_replace($fk->getName(), 'FI_',  strrpos($fk->getName(), 'FK_'), 3) . "`";
  71                  if(!in_array(implode(',', $lnames), $_indices))
  72                  {
  73                      // no matching index defined in the schema, so we have to create one. MySQL needs indices on any columns that serve as foreign keys. these are not auto-created prior to 4.1.2
  74  ?> 
  75      INDEX <?php echo $indexName; ?> (<?php echo implode(',', $lnames); ?>),<?php
  76                  }
  77  ?> 
  78      CONSTRAINT <?php echo $constraintName ?> 
  79        FOREIGN KEY (<?php echo implode(',', $lnames); ?>)
  80        REFERENCES <?php echo "`" . $fk->getForeignTableName() . "`" ?> (<?php echo implode(',', $fnames); ?>)
  81  <?php if ($fk->hasOnUpdate()) { ?>
  82        ON UPDATE <?php echo $fk->getOnUpdate(); ?> 
  83  <?php } if ($fk->hasOnDelete()) { ?>
  84        ON DELETE <?php echo $fk->getOnDelete();
  85      }
  86  }