[Summary view] [Print] [Text view]

   1  <?php
   2  // $Id: schema_mysql.inc,v 1.23.2.4 2010/02/01 19:03:19 mikeryan Exp $
   3  
   4  function schema_mysql_engine_type_map() {
   5    $map = db_type_map();
   6    return $map;
   7  }
   8  
   9  function schema_mysql_schema_type_map() {
  10    static $map;
  11    if (!isset($map)) {
  12      $map = array_flip(array_map('strtolower', schema_mysql_engine_type_map()));
  13    }
  14    return $map;
  15  }
  16  
  17  function schema_mysql_create_table_sql($table) {
  18    $sql_cols = array();
  19    foreach ($table['fields'] as $colname => $col) {
  20      $sql = $colname.' '.schema_engine_type($col, 'mysql');
  21      if (isset($col['unsigned']) && $col['unsigned']) {
  22        $sql .= ' UNSIGNED';
  23      }
  24      unset($col['unsigned']);
  25      if (isset($col['length']) && $col['length']) {
  26        $sql .= '('.$col['length'].')';
  27      }
  28      if (isset($col['type']) && $col['type'] == 'serial') {
  29        $sql .= ' AUTO_INCREMENT';
  30      }
  31      unset($col['type']);
  32      unset($col['length']);
  33  
  34      if (isset($col['not null']) && $col['not null']) {
  35        $sql .= ' NOT NULL';
  36      }
  37      unset($col['not null']);
  38      foreach ($col as $prop => $val) {
  39        switch ($prop) {
  40          case 'default':
  41            $sql .= " $prop ";
  42            if (is_string($val)) {
  43              $sql .= "'$val'";
  44            } else {
  45              $sql .= $val;
  46            }
  47            break;
  48        }
  49      }
  50      $sql_cols[] = $sql;
  51    }
  52  
  53    $sql_keys = array();
  54    if (is_array($table['primary key'])) {
  55      $sql_keys[] = 'PRIMARY KEY ('.implode(', ', $table['primary key']).')';
  56    }
  57    foreach (array('unique keys', 'indexes') as $type) {
  58      if (isset($table[$type]) && is_array($table[$type])) {
  59        foreach ($table[$type] as $keyname => $key) {
  60          $sql = '';
  61          if ($type == 'unique keys') {
  62              $sql = 'UNIQUE ';
  63          }
  64          $sql .= 'KEY '.$keyname.' ';
  65          $sql .= '('.implode(', ', $key).')';
  66          $sql_keys[] = $sql;
  67        }
  68      }
  69    }
  70  
  71    $sql = "CREATE TABLE {".$table['name']."} (\n\t";
  72    $sql .= implode(",\n\t", $sql_cols);
  73    if (count($sql_keys) > 0) {
  74      $sql .= ",\n\t";
  75    }
  76    $sql .= implode(",\n\t", $sql_keys);
  77    $sql .= "\n";
  78    $sql .= ") /*!40100 DEFAULT CHARACTER SET utf8 */;\n\n";
  79    return $sql;
  80  }
  81  
  82  function schema_mysql_inspect($name = NULL) {
  83    global $db_url;
  84  
  85    // Switch to the active database connection.
  86    // The only way to get the active connection's name is as a return value from
  87    // db_set_active(). However, calling this function will automatically switch
  88    // the active connection to 'default', which might not be what we want.
  89    // Therefore, we must immediately call db_set_active() again with the desired
  90    // connection name in order to proceed.
  91    $active_db_connection = db_set_active();
  92    db_set_active($active_db_connection);
  93  
  94    $tables = array();
  95    $url = parse_url(is_array($db_url) ? $db_url[$active_db_connection] : $db_url);
  96    $database = substr($url['path'], 1);
  97  
  98    $sql = 'SELECT TABLE_NAME, TABLE_COMMENT
  99            FROM information_schema.TABLES
 100            WHERE TABLE_SCHEMA="%s" ';
 101    if (isset($name)) {
 102      $sql .= 'AND TABLE_NAME = "%s" ';
 103    }
 104    $res = db_query($sql, $database, $name);
 105    while ($r = db_fetch_array($res)) {
 106      $tables[$r['TABLE_NAME']]['description'] = $r['TABLE_COMMENT'];
 107    }
 108  
 109    $sql = 'SELECT TABLE_NAME, COLUMN_TYPE, COLUMN_NAME, COLUMN_DEFAULT,
 110                   EXTRA, IS_NULLABLE, NUMERIC_SCALE, COLUMN_COMMENT
 111            FROM information_schema.COLUMNS
 112            WHERE TABLE_SCHEMA="%s" ';
 113    if (isset($name)) {
 114      $sql .= 'AND TABLE_NAME = "%s" ';
 115    }
 116    $sql .= 'ORDER BY TABLE_NAME, ORDINAL_POSITION';
 117  
 118    $res = db_query($sql, $database, $name);
 119    while ($r = db_fetch_array($res)) {
 120      $r['NEW_TABLE_NAME'] = schema_unprefix_table($r['TABLE_NAME']);
 121  
 122      $numeric = !is_null($r['NUMERIC_SCALE']);
 123      $col = array();
 124      $col['type'] = $r['COLUMN_TYPE'];
 125      if (preg_match('@([a-z]+)(?:\((\d+)(?:,(\d+))?\))?\s*(unsigned)?@', $col['type'], $matches)) {
 126        list($col['type'], $col['size']) = schema_schema_type($matches[1], $r['TABLE_NAME'], $r['COLUMN_NAME'], 'mysql');
 127        if (isset($matches[2])) {
 128          if ($col['type'] == 'numeric' || $col['type'] == 'float' || $col['type'] == 'double') {
 129            $col['precision'] = $matches[2];
 130            $col['scale'] = $matches[3];
 131          }
 132          else if (!$numeric) {
 133            $col['length'] = $matches[2];
 134          }
 135        }
 136        if (isset($matches[4])) {
 137          $col['unsigned'] = TRUE;
 138        }
 139      }
 140      if ($col['type'] == 'int' && isset($r['EXTRA']) &&
 141        $r['EXTRA'] == 'auto_increment') {
 142        $col['type'] = 'serial';
 143      }
 144      $col['not null'] = ($r['IS_NULLABLE'] == 'YES' ? FALSE : TRUE);
 145      if (! is_null($r['COLUMN_DEFAULT'])) {
 146        if ($numeric) {
 147          // XXX floats!
 148          $col['default'] = intval($r['COLUMN_DEFAULT']);
 149        } else {
 150          $col['default'] = $r['COLUMN_DEFAULT'];
 151        }
 152      }
 153      $col['description'] = $r['COLUMN_COMMENT'];
 154      $tables[$r['TABLE_NAME']]['fields'][$r['COLUMN_NAME']] = $col;
 155      // At this point, $tables is indexed by the raw db table name - save the unprefixed
 156      // name for later use
 157      $tables[$r['TABLE_NAME']]['name'] = $r['NEW_TABLE_NAME'];
 158    }
 159  
 160    $sql = 'SELECT TABLE_NAME, COLUMN_NAME, INDEX_NAME, SUB_PART, NON_UNIQUE
 161            FROM information_schema.STATISTICS
 162            WHERE TABLE_SCHEMA="%s" ';
 163    if (isset($name)) {
 164      $sql .= 'AND TABLE_NAME = "%s" ';
 165    }
 166    $sql .= 'ORDER BY TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX';
 167  
 168    $res = db_query($sql, $database, $name);
 169    while ($r = db_fetch_array($res)) {
 170      if (isset($r['SUB_PART']) && !is_null($r['SUB_PART'])) {
 171        $col = array($r['COLUMN_NAME'], intval($r['SUB_PART']));
 172      } else {
 173        $col = $r['COLUMN_NAME'];
 174      }
 175      if ($r['INDEX_NAME'] == 'PRIMARY') {
 176        $type = 'primary key';
 177        $tables[$r['TABLE_NAME']][$type][] = $col;
 178        continue;
 179      } else if ($r['NON_UNIQUE'] == 0) {
 180        $type = 'unique keys';
 181      } else {
 182        $type = 'indexes';
 183      }
 184      $tables[$r['TABLE_NAME']][$type][$r['INDEX_NAME']][] = $col;
 185    }
 186  
 187    // Now, for tables which we have unprefixed, index $tables by the unprefixed name
 188    foreach ($tables as $tablename => $table) {
 189      $newname = $tables[$tablename]['name'];
 190      if ($tablename != $newname) {
 191        $tables[$newname] = $table;
 192        unset($tables[$tablename]);
 193      }
 194    }
 195  
 196    return $tables;
 197  }