[Sommaire] [Imprimer]

   1  <?php
   2  /*
   3   *  $Id: MySQLTableInfo.php,v 1.20 2006/01/17 19:44:39 hlellelid Exp $
   4   *
   5   * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
   6   * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
   7   * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
   8   * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
   9   * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
  10   * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
  11   * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
  12   * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
  13   * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
  14   * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
  15   * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
  16   *
  17   * This software consists of voluntary contributions made by many individuals
  18   * and is licensed under the LGPL. For more information please see
  19   * <http://creole.phpdb.org>.
  20   */
  21  
  22  require_once 'creole/metadata/TableInfo.php';
  23  
  24  /**
  25   * MySQL implementation of TableInfo.
  26   *
  27   * @author    Hans Lellelid <hans@xmpl.org>
  28   * @version   $Revision: 1.20 $
  29   * @package   creole.drivers.mysql.metadata
  30   */
  31  class MySQLTableInfo extends TableInfo {
  32  
  33      /** Loads the columns for this table. */
  34      protected function initColumns()
  35      {
  36          include_once 'creole/metadata/ColumnInfo.php';
  37          include_once 'creole/drivers/mysql/MySQLTypes.php';
  38  
  39          if (!@mysql_select_db($this->dbname, $this->conn->getResource())) {
  40              throw new SQLException('No database selected');
  41          }
  42  
  43          // To get all of the attributes we need, we use
  44          // the MySQL "SHOW COLUMNS FROM $tablename" SQL.  We cannot
  45          // use the API functions (e.g. mysql_list_fields() because they
  46          // do not return complete information -- e.g. precision / scale, default
  47          // values).
  48  
  49          $res = mysql_query("SHOW COLUMNS FROM `" . $this->name . "`", $this->conn->getResource());
  50  
  51          $defaults = array();
  52          $nativeTypes = array();
  53          $precisions = array();
  54  
  55          while($row = mysql_fetch_assoc($res)) {
  56              $name = $row['Field'];
  57              $is_nullable = ($row['Null'] == 'YES');
  58              $is_auto_increment = (strpos($row['Extra'], 'auto_increment') !== false);
  59              $size = null;
  60              $precision = null;
  61              $scale = null;
  62              
  63              if (preg_match('/^(\w+)[\(]?([\d,]*)[\)]?( |$)/', $row['Type'], $matches)) {
  64                  //            colname[1]   size/precision[2]
  65                  $nativeType = $matches[1];
  66                  if ($matches[2]) {
  67                      if ( ($cpos = strpos($matches[2], ',')) !== false) {
  68                          $size = (int) substr($matches[2], 0, $cpos);
  69                          $precision = $size;
  70                          $scale = (int) substr($matches[2], $cpos + 1);
  71                      } else {
  72                          $size = (int) $matches[2];
  73                      }
  74                  }
  75              } elseif (preg_match('/^(\w+)\(/', $row['Type'], $matches)) {
  76                  $nativeType = $matches[1];
  77              } else {
  78                  $nativeType = $row['Type'];
  79              }
  80              //BLOBs can't have any default values in MySQL
  81              $default = preg_match('~blob|text~', $nativeType) ? null : $row['Default'];
  82              $this->columns[$name] = new ColumnInfo($this,
  83                                                     $name,
  84                                                     MySQLTypes::getType($nativeType),
  85                                                     $nativeType,
  86                                                     $size,
  87                                                     $precision,
  88                                                     $scale,
  89                                                     $is_nullable,
  90                                                     $default,
  91                                                     $is_auto_increment,
  92                                                     $row);
  93          }
  94  
  95          $this->colsLoaded = true;
  96      }
  97  
  98      /** Loads the primary key information for this table. */
  99      protected function initPrimaryKey()
 100      {
 101          include_once 'creole/metadata/PrimaryKeyInfo.php';
 102  
 103          // columns have to be loaded first
 104          if (!$this->colsLoaded) $this->initColumns();
 105  
 106          if (!@mysql_select_db($this->dbname, $this->conn->getResource())) {
 107              throw new SQLException('No database selected');
 108          }
 109  
 110          // Primary Keys
 111          $res = mysql_query("SHOW KEYS FROM `" . $this->name . "`", $this->conn->getResource());
 112  
 113          // Loop through the returned results, grouping the same key_name together
 114          // adding each column for that key.
 115  
 116          while($row = mysql_fetch_assoc($res)) {
 117              // Skip any non-primary keys.
 118              if ($row['Key_name'] !== 'PRIMARY') {
 119                  continue;
 120              }
 121              $name = $row["Column_name"];
 122              if (!isset($this->primaryKey)) {
 123                  $this->primaryKey = new PrimaryKeyInfo($name, $row);
 124              }
 125              $this->primaryKey->addColumn($this->columns[$name]);
 126          }
 127  
 128          $this->pkLoaded = true;
 129      }
 130  
 131      /** Loads the indexes for this table. */
 132      protected function initIndexes() {
 133  
 134          include_once 'creole/metadata/IndexInfo.php';
 135  
 136          // columns have to be loaded first
 137          if (!$this->colsLoaded) $this->initColumns();
 138  
 139          if (!@mysql_select_db($this->dbname, $this->conn->getResource())) {
 140              throw new SQLException('No database selected');
 141          }
 142  
 143          // Indexes
 144          $res = mysql_query("SHOW INDEX FROM `" . $this->name . "`", $this->conn->getResource());
 145  
 146          // Loop through the returned results, grouping the same key_name together
 147          // adding each column for that key.
 148  
 149          while($row = mysql_fetch_assoc($res)) {
 150              $colName = $row["Column_name"];
 151              $name = $row["Key_name"];
 152  
 153              if($name == "PRIMARY") {
 154                  continue;
 155              }
 156  
 157              if (!isset($this->indexes[$name])) {
 158                  $isUnique = ($row["Non_unique"] == 0);
 159                  $this->indexes[$name] = new IndexInfo($name, $isUnique, $row);
 160              }
 161              $this->indexes[$name]->addColumn($this->columns[$colName]);
 162          }
 163  
 164          $this->indexesLoaded = true;
 165      }
 166  
 167    /**
 168     * Load foreign keys for supporting versions of MySQL.
 169     * @author Tony Bibbs
 170     */
 171    protected function initForeignKeys() {
 172  
 173      // First make sure we have supported version of MySQL:
 174      $res = mysql_query("SELECT VERSION()");
 175      $row = mysql_fetch_row($res);
 176  
 177      // Yes, it is OK to hardcode this...this was the first version of MySQL
 178      // that supported foreign keys
 179      if ($row[0] < '3.23.44') {
 180         $this->fksLoaded = true;
 181         return;
 182      }
 183  
 184      include_once 'creole/metadata/ForeignKeyInfo.php';
 185  
 186      // columns have to be loaded first
 187      if (!$this->colsLoaded) $this->initColumns();
 188      if (!@mysql_select_db($this->dbname, $this->conn->getResource())) {
 189        throw new SQLException('No database selected');
 190      }
 191         // Get the CREATE TABLE syntax
 192      $res = mysql_query("SHOW CREATE TABLE `" . $this->name . "`", $this->conn->getResource());
 193      $row = mysql_fetch_row($res);
 194  
 195      // Get the information on all the foreign keys
 196      $regEx = '/FOREIGN KEY \(`([^`]*)`\) REFERENCES `([^`]*)` \(`([^`]*)`\)(.*)/';
 197      if (preg_match_all($regEx,$row[1],$matches)) {
 198        $tmpArray = array_keys($matches[0]);
 199        foreach ($tmpArray as $curKey) {
 200          $name = $matches[1][$curKey];
 201          $ftbl = $matches[2][$curKey];
 202          $fcol = $matches[3][$curKey];
 203          $fkey = $matches[4][$curKey];
 204          if (!isset($this->foreignKeys[$name])) {
 205            $this->foreignKeys[$name] = new ForeignKeyInfo($name);
 206            if ($this->database->hasTable($ftbl)) {
 207              $foreignTable = $this->database->getTable($ftbl);
 208            } else {
 209              $foreignTable = new MySQLTableInfo($this->database, $ftbl);
 210              $this->database->addTable($foreignTable);
 211            }
 212            if ($foreignTable->hasColumn($fcol)) {
 213              $foreignCol = $foreignTable->getColumn($fcol);
 214            } else {
 215              $foreignCol = new ColumnInfo($foreignTable, $fcol);
 216              $foreignTable->addColumn($foreignCol);
 217            }
 218  
 219            //typical for mysql is RESTRICT
 220            $fkactions = array(
 221              'ON DELETE'    => ForeignKeyInfo::RESTRICT,
 222              'ON UPDATE'    => ForeignKeyInfo::RESTRICT,
 223            );
 224                                
 225            if ($fkey) {
 226              //split foreign key information -> search for ON DELETE and afterwords for ON UPDATE action
 227              foreach (array_keys($fkactions) as $fkaction) {
 228                $result = NULL;
 229                preg_match('/' . $fkaction . ' (' . ForeignKeyInfo::CASCADE . '|' . ForeignKeyInfo::SETNULL . ')/', $fkey, $result);
 230                if ($result && is_array($result) && isset($result[1])) {
 231                  $fkactions[$fkaction] = $result[1];
 232                }
 233              }
 234            }
 235  
 236            $this->foreignKeys[$name]->addReference($this->columns[$name], $foreignCol, $fkactions['ON DELETE'], $fkactions['ON UPDATE']);
 237          }
 238        }
 239      }
 240      $this->fksLoaded = true;
 241      
 242    }
 243  
 244    protected function initVendorSpecificInfo()
 245    {
 246        $res = mysql_query("SHOW TABLE STATUS LIKE '" . $this->name . "'", $this->conn->getResource());
 247        $this->vendorSpecificInfo = mysql_fetch_assoc($res);
 248  
 249        $this->vendorLoaded = true;
 250    }
 251  
 252  }