[Sommaire] [Imprimer]

   1  <?php
   2  /**
   3    V4.65 22 July 2005  (c) 2000-2005 John Lim (jlim@natsoft.com.my). All rights reserved.
   4    Released under both BSD license and Lesser GPL library license. 
   5    Whenever there is any discrepancy between the two licenses, 
   6    the BSD license will take precedence.
   7      
   8    Set tabs to 4 for best viewing.
   9    
  10    Modified 28 August, 2005 for use with ADOdb Lite by Mark Dickenson
  11    
  12  */
  13  
  14  // security - hide paths
  15  if (!defined('ADODB_DIR')) die();
  16  
  17  class ADODB2_mysql extends ADODB_DataDict {
  18      var $dbtype = 'mysql';
  19      var $alterCol = ' MODIFY COLUMN';
  20      var $alterTableAddIndex = true;
  21      var $dropTable = 'DROP TABLE IF EXISTS %s'; // requires mysql 3.22 or later
  22      
  23      var $dropIndex = 'DROP INDEX %s ON %s';
  24      var $renameColumn = 'ALTER TABLE %s CHANGE COLUMN %s %s %s';    // needs column-definition!
  25      var $metaTablesSQL = "SHOW TABLES";    
  26      var $metaColumnsSQL = "SHOW COLUMNS FROM %s";
  27      
  28  	function MetaType($t,$len=-1,$fieldobj=false)
  29      {
  30          if (is_object($t)) {
  31              $fieldobj = $t;
  32              $t = $fieldobj->type;
  33              $len = $fieldobj->max_length;
  34          }
  35          $is_serial = is_object($fieldobj) && $fieldobj->primary_key && $fieldobj->auto_increment;
  36          
  37          $len = -1; // mysql max_length is not accurate
  38          switch (strtoupper($t)) {
  39          case 'STRING': 
  40          case 'CHAR':
  41          case 'VARCHAR': 
  42          case 'TINYBLOB': 
  43          case 'TINYTEXT': 
  44          case 'ENUM': 
  45          case 'SET':
  46              if ($len <= $this->blobSize) return 'C';
  47              
  48          case 'TEXT':
  49          case 'LONGTEXT': 
  50          case 'MEDIUMTEXT':
  51              return 'X';
  52              
  53          // php_mysql extension always returns 'blob' even if 'text'
  54          // so we have to check whether binary...
  55          case 'IMAGE':
  56          case 'LONGBLOB': 
  57          case 'BLOB':
  58          case 'MEDIUMBLOB':
  59              return !empty($fieldobj->binary) ? 'B' : 'X';
  60              
  61          case 'YEAR':
  62          case 'DATE': return 'D';
  63          
  64          case 'TIME':
  65              return 'T';
  66          case 'DATETIME':
  67              return 'DT';
  68          case 'TIMESTAMP': return 'TS';
  69          
  70          case 'FLOAT':
  71          case 'DOUBLE':
  72              return 'F';
  73              
  74          case 'INT': 
  75          case 'INTEGER': return $is_serial ? 'R' : 'I';
  76          case 'TINYINT': return $is_serial ? 'R' : 'I1';
  77          case 'SMALLINT': return $is_serial ? 'R' : 'I2';
  78          case 'MEDIUMINT': return $is_serial ? 'R' : 'I4';
  79          case 'BIGINT':  return $is_serial ? 'R' : 'I8';
  80          default: return 'N';
  81          }
  82      }
  83  
  84  	function ActualType($meta)
  85      {
  86          switch(strtoupper($meta)) {
  87          case 'C': return 'VARCHAR';
  88          case 'XL':return 'LONGTEXT';
  89          case 'X': return 'TEXT';
  90          
  91          case 'C2': return 'VARCHAR';
  92          case 'X2': return 'LONGTEXT';
  93          
  94          case 'B': return 'LONGBLOB';
  95              
  96          case 'D': return 'DATE';
  97          case 'DT': return 'DATETIME';
  98          case 'T': return 'TIME';
  99          case 'TS': return 'TIMESTAMP';
 100          case 'L': return 'TINYINT';
 101          
 102          case 'R':
 103          case 'I4':
 104          case 'I': return 'INTEGER';
 105          case 'I1': return 'TINYINT';
 106          case 'I2': return 'SMALLINT';
 107          case 'I8': return 'BIGINT';
 108          
 109          case 'F': return 'DOUBLE';
 110          case 'N': return 'NUMERIC';
 111          default:
 112              return $meta;
 113          }
 114      }
 115      
 116      // return string must begin with space
 117  	function _CreateSuffix($fname,$ftype,$fnotnull,$fdefault,$fautoinc,$fconstraint,$funsigned)
 118      {    
 119          $suffix = '';
 120          if ($funsigned) $suffix .= ' UNSIGNED';
 121          if ($fnotnull) $suffix .= ' NOT NULL';
 122          if (strlen($fdefault)) $suffix .= " DEFAULT $fdefault";
 123          if ($fautoinc) $suffix .= ' AUTO_INCREMENT';
 124          if ($fconstraint) $suffix .= ' '.$fconstraint;
 125          return $suffix;
 126      }
 127  
 128  	function _IndexSQL($idxname, $tabname, $flds, $idxoptions)
 129      {
 130          $sql = array();
 131          
 132          if ( isset($idxoptions['REPLACE']) || isset($idxoptions['DROP']) ) {
 133              if ($this->alterTableAddIndex) $sql[] = "ALTER TABLE $tabname DROP INDEX $idxname";
 134              else $sql[] = sprintf($this->dropIndex, $idxname, $tabname);
 135  
 136              if ( isset($idxoptions['DROP']) )
 137                  return $sql;
 138          }
 139          
 140          if ( empty ($flds) ) {
 141              return $sql;
 142          }
 143          
 144          if (isset($idxoptions['FULLTEXT'])) {
 145              $unique = ' FULLTEXT';
 146          } elseif (isset($idxoptions['UNIQUE'])) {
 147              $unique = ' UNIQUE';
 148          } else {
 149              $unique = '';
 150          }
 151          
 152          if ( is_array($flds) ) $flds = implode(', ',$flds);
 153          
 154          if ($this->alterTableAddIndex) $s = "ALTER TABLE $tabname ADD $unique INDEX $idxname ";
 155          else $s = 'CREATE' . $unique . ' INDEX ' . $idxname . ' ON ' . $tabname;
 156          
 157          $s .= ' (' . $flds . ')';
 158          
 159          if ( isset($idxoptions[$this->upperName]) )
 160              $s .= $idxoptions[$this->upperName];
 161          
 162          $sql[] = $s;
 163          
 164          return $sql;
 165      }
 166  
 167      /**
 168       * @param ttype can either be 'VIEW' or 'TABLE' or false. 
 169       *         If false, both views and tables are returned.
 170       *        "VIEW" returns only views
 171       *        "TABLE" returns only tables
 172       * @param showSchema returns the schema/user with the table name, eg. USER.TABLE
 173       * @param mask  is the input mask - only supported by oci8 and postgresql
 174       *
 175       * @return  array of tables for current database.
 176       */ 
 177      function &MetaTables($ttype=false,$showSchema=false,$mask=false) 
 178      {    
 179          $save = $this->metaTablesSQL;
 180          if ($showSchema && is_string($showSchema)) {
 181              $this->metaTablesSQL .= " from $showSchema";
 182          }
 183          
 184          if ($mask) {
 185              $mask = $this->connection->qstr($mask);
 186              $this->metaTablesSQL .= " like $mask";
 187          }
 188          $ret =& $this->_MetaTables($ttype,$showSchema);
 189          
 190          $this->metaTablesSQL = $save;
 191          return $ret;
 192      }
 193  
 194      function &_MetaTables($ttype=false,$showSchema=false,$mask=false) 
 195      {
 196      global $ADODB_FETCH_MODE;
 197      
 198          
 199          $false = false;
 200          if ($mask) {
 201              return $false;
 202          }
 203          if ($this->metaTablesSQL) {
 204              $save = $ADODB_FETCH_MODE; 
 205              $ADODB_FETCH_MODE = ADODB_FETCH_NUM; 
 206              
 207              if ($this->fetchMode !== false) $savem = $this->SetFetchMode(false);
 208              
 209              $rs = $this->connection->Execute($this->metaTablesSQL);
 210              if (isset($savem)) $this->SetFetchMode($savem);
 211              $ADODB_FETCH_MODE = $save; 
 212              
 213              if ($rs === false) return $false;
 214              $arr =& $rs->GetArray();
 215              $arr2 = array();
 216              
 217              if ($hast = ($ttype && isset($arr[0][1]))) { 
 218                  $showt = strncmp($ttype,'T',1);
 219              }
 220              
 221              for ($i=0; $i < sizeof($arr); $i++) {
 222                  if ($hast) {
 223                      if ($showt == 0) {
 224                          if (strncmp($arr[$i][1],'T',1) == 0) $arr2[] = trim($arr[$i][0]);
 225                      } else {
 226                          if (strncmp($arr[$i][1],'V',1) == 0) $arr2[] = trim($arr[$i][0]);
 227                      }
 228                  } else
 229                      $arr2[] = trim($arr[$i][0]);
 230              }
 231              $rs->Close();
 232              return $arr2;
 233          }
 234          return $false;
 235      }
 236  
 237      /**
 238       * List columns in a database as an array of ADOFieldObjects. 
 239       * See top of file for definition of object.
 240       *
 241       * @param table    table name to query
 242       * @param upper    uppercase table name (required by some databases)
 243       * @schema is optional database schema to use - not supported by all databases.
 244       *
 245       * @return  array of ADOFieldObjects for current table.
 246       */
 247   	function MetaColumns($table) 
 248      {
 249          $this->_findschema($table,$schema);
 250          if ($schema) {
 251              $dbName = $this->database;
 252              $this->connection->SelectDB($schema);
 253          }
 254          global $ADODB_FETCH_MODE;
 255          $save = $ADODB_FETCH_MODE;
 256          $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 257          
 258          if ($this->fetchMode !== false) $savem = $this->SetFetchMode(false);
 259          $rs = $this->connection->Execute(sprintf($this->metaColumnsSQL,$table));
 260          
 261          if ($schema) {
 262              $this->connection->SelectDB($dbName);
 263          }
 264          
 265          if (isset($savem)) $this->SetFetchMode($savem);
 266          $ADODB_FETCH_MODE = $save;
 267          if (!is_object($rs)) {
 268              $false = false;
 269              return $false;
 270          }
 271              
 272          $retarr = array();
 273          while (!$rs->EOF){
 274              $fld = new ADOFieldObject();
 275              $fld->name = $rs->fields[0];
 276              $type = $rs->fields[1];
 277              
 278              // split type into type(length):
 279              $fld->scale = null;
 280              if (preg_match("/^(.+)\((\d+),(\d+)/", $type, $query_array)) {
 281                  $fld->type = $query_array[1];
 282                  $fld->max_length = is_numeric($query_array[2]) ? $query_array[2] : -1;
 283                  $fld->scale = is_numeric($query_array[3]) ? $query_array[3] : -1;
 284              } elseif (preg_match("/^(.+)\((\d+)/", $type, $query_array)) {
 285                  $fld->type = $query_array[1];
 286                  $fld->max_length = is_numeric($query_array[2]) ? $query_array[2] : -1;
 287              } elseif (preg_match("/^(enum)\((.*)\)$/i", $type, $query_array)) {
 288                  $fld->type = $query_array[1];
 289                  $arr = explode(",",$query_array[2]);
 290                  $fld->enums = $arr;
 291                  $zlen = max(array_map("strlen",$arr)) - 2; // PHP >= 4.0.6
 292                  $fld->max_length = ($zlen > 0) ? $zlen : 1;
 293              } else {
 294                  $fld->type = $type;
 295                  $fld->max_length = -1;
 296              }
 297              $fld->not_null = ($rs->fields[2] != 'YES');
 298              $fld->primary_key = ($rs->fields[3] == 'PRI');
 299              $fld->auto_increment = (strpos($rs->fields[5], 'auto_increment') !== false);
 300              $fld->binary = (strpos($type,'blob') !== false);
 301              $fld->unsigned = (strpos($type,'unsigned') !== false);
 302                  
 303              if (!$fld->binary) {
 304                  $d = $rs->fields[4];
 305                  if ($d != '' && $d != 'NULL') {
 306                      $fld->has_default = true;
 307                      $fld->default_value = $d;
 308                  } else {
 309                      $fld->has_default = false;
 310                  }
 311              }
 312              
 313              if ($save == ADODB_FETCH_NUM) {
 314                  $retarr[] = $fld;
 315              } else {
 316                  $retarr[strtoupper($fld->name)] = $fld;
 317              }
 318                  $rs->MoveNext();
 319              }
 320          
 321              $rs->Close();
 322              return $retarr;    
 323      }
 324  
 325  	function _findschema(&$table,&$schema)
 326      {
 327          if (!$schema && ($at = strpos($table,'.')) !== false) {
 328              $schema = substr($table,0,$at);
 329              $table = substr($table,$at+1);
 330          }
 331      }
 332  
 333      /**
 334       * @returns an array with the primary key columns in it.
 335       */
 336  	function MetaPrimaryKeys($table, $owner=false)
 337      {
 338      // owner not used in base class - see oci8
 339          $p = array();
 340          $objs =& $this->MetaColumns($table);
 341          if ($objs) {
 342              foreach($objs as $v) {
 343                  if (!empty($v->primary_key))
 344                      $p[] = $v->name;
 345              }
 346          }
 347          if (sizeof($p)) return $p;
 348          if (function_exists('ADODB_VIEW_PRIMARYKEYS'))
 349              return ADODB_VIEW_PRIMARYKEYS($this->databaseType, $this->database, $table, $owner);
 350          return false;
 351      }
 352  
 353      /**
 354        * List indexes on a table as an array.
 355        * @param table  table name to query
 356        * @param primary true to only show primary keys. Not actually used for most databases
 357        *
 358        * @return array of indexes on current table. Each element represents an index, and is itself an associative array.
 359        
 360           Array (
 361              [name_of_index] => Array
 362                (
 363                [unique] => true or false
 364                [columns] => Array
 365                (
 366                    [0] => firstname
 367                    [1] => lastname
 368                )
 369          )        
 370        */
 371  	function MetaIndexes ($table, $primary = FALSE, $owner=false)
 372      {
 373          // save old fetch mode
 374          global $ADODB_FETCH_MODE;
 375          
 376          $false = false;
 377          $save = $ADODB_FETCH_MODE;
 378          $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 379          if ($this->fetchMode !== FALSE) {
 380                 $savem = $this->SetFetchMode(FALSE);
 381          }
 382          
 383          // get index details
 384          $rs =& $this->connection->Execute(sprintf('SHOW INDEX FROM %s',$table));
 385          
 386          // restore fetchmode
 387          if (isset($savem)) {
 388                  $this->SetFetchMode($savem);
 389          }
 390          $ADODB_FETCH_MODE = $save;
 391          
 392          if (!is_object($rs)) {
 393                  return $false;
 394          }
 395          
 396          $indexes = array ();
 397          
 398          // parse index data into array
 399          while (!$rs->EOF)
 400          {
 401                  if ($primary == FALSE AND $rs->fields[2] == 'PRIMARY') {
 402                      $rs->MoveNext();
 403                          continue;
 404                  }
 405  
 406                  if (!isset($indexes[$rs->fields[2]])) {
 407                          $indexes[$rs->fields[2]] = array(
 408                                  'unique' => ($rs->fields[1] == 0),
 409                                  'columns' => array()
 410                          );
 411                  }
 412  
 413                  $indexes[$rs->fields[2]]['columns'][$rs->fields[3] - 1] = $rs->fields[4];
 414  
 415              $rs->MoveNext();
 416          }
 417          
 418          // sort columns by order in the index
 419          foreach ( array_keys ($indexes) as $index )
 420          {
 421                  ksort ($indexes[$index]['columns']);
 422          }
 423          
 424          return $indexes;
 425      }
 426  }
 427  
 428  ?>