[Summary view]

Differences Between: [Versions 310 and 402] [Versions 311 and 402] [Versions 39 and 402] [Versions 400 and 402] [Versions 401 and 402]

   1  <?php
   2  /**
   3   * PDO MySQL driver
   4   *
   5   * This file is part of ADOdb, a Database Abstraction Layer library for PHP.
   6   *
   7   * @package ADOdb
   8   * @link https://adodb.org Project's web site and documentation
   9   * @link https://github.com/ADOdb/ADOdb Source code and issue tracker
  10   *
  11   * The ADOdb Library is dual-licensed, released under both the BSD 3-Clause
  12   * and the GNU Lesser General Public Licence (LGPL) v2.1 or, at your option,
  13   * any later version. This means you can use it in proprietary products.
  14   * See the LICENSE.md file distributed with this source code for details.
  15   * @license BSD-3-Clause
  16   * @license LGPL-2.1-or-later
  17   *
  18   * @copyright 2000-2013 John Lim
  19   * @copyright 2014 Damien Regad, Mark Newnham and the ADOdb community
  20   */
  21  
  22  class ADODB_pdo_mysql extends ADODB_pdo {
  23  
  24  	 var $metaTablesSQL = "SELECT
  25  	 	 	 TABLE_NAME,
  26  	 	 	 CASE WHEN TABLE_TYPE = 'VIEW' THEN 'V' ELSE 'T' END
  27  	 	 FROM INFORMATION_SCHEMA.TABLES
  28  	 	 WHERE TABLE_SCHEMA=";
  29  	 var $metaColumnsSQL = "SHOW COLUMNS FROM `%s`";
  30  	 var $sysDate = 'CURDATE()';
  31  	 var $sysTimeStamp = 'NOW()';
  32  	 var $hasGenID = true;
  33  	 var $_genIDSQL = "UPDATE %s SET id=LAST_INSERT_ID(id+1);";
  34  	 var $_genSeqSQL = "CREATE TABLE  if NOT EXISTS %s (id int not null)";
  35  	 var $_genSeqCountSQL = "SELECT count(*) FROM %s";
  36  	 var $_genSeq2SQL = "INSERT INTO %s VALUES (%s)";
  37  	 var $_dropSeqSQL = "drop table %s";
  38  	 var $fmtTimeStamp = "'Y-m-d H:i:s'";
  39  	 var $nameQuote = '`';
  40  
  41  	function _init($parentDriver)
  42  	 {
  43  	 	 $parentDriver->hasTransactions = false;
  44  	 	 #$parentDriver->_bindInputArray = false;
  45  	 	 $parentDriver->hasInsertID = true;
  46  	 	 $parentDriver->_connectionID->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
  47  	 }
  48  
  49  	 // dayFraction is a day in floating point
  50  	function OffsetDate($dayFraction, $date=false)
  51  	 {
  52  	 	 if (!$date) {
  53  	 	 	 $date = $this->sysDate;
  54  	 	 }
  55  
  56  	 	 $fraction = $dayFraction * 24 * 3600;
  57  	 	 return $date . ' + INTERVAL ' .	 $fraction . ' SECOND';
  58  //	 	 return "from_unixtime(unix_timestamp($date)+$fraction)";
  59  	 }
  60  	 
  61  	 /**
  62  	  * Get a list of indexes on the specified table.
  63  	  *
  64  	  * @param string $table The name of the table to get indexes for.
  65  	  * @param bool $primary (Optional) Whether or not to include the primary key.
  66  	  * @param bool $owner (Optional) Unused.
  67  	  *
  68  	  * @return array|bool An array of the indexes, or false if the query to get the indexes failed.
  69  	  */
  70  	function metaIndexes($table, $primary = false, $owner = false)
  71  	 {
  72  	 	 // save old fetch mode
  73  	 	 global $ADODB_FETCH_MODE;
  74  
  75  	 	 $false = false;
  76  	 	 $save = $ADODB_FETCH_MODE;
  77  	 	 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
  78  	 	 if ($this->fetchMode !== FALSE) {
  79  	 	 	 $savem = $this->setFetchMode(FALSE);
  80  	 	 }
  81  
  82  	 	 // get index details
  83  	 	 $rs = $this->execute(sprintf('SHOW INDEXES FROM %s',$table));
  84  
  85  	 	 // restore fetchmode
  86  	 	 if (isset($savem)) {
  87  	 	 	 $this->setFetchMode($savem);
  88  	 	 }
  89  	 	 $ADODB_FETCH_MODE = $save;
  90  
  91  	 	 if (!is_object($rs)) {
  92  	 	 	 return $false;
  93  	 	 }
  94  
  95  	 	 $indexes = array ();
  96  
  97  	 	 // parse index data into array
  98  	 	 while ($row = $rs->fetchRow()) {
  99  	 	 	 if ($primary == FALSE AND $row[2] == 'PRIMARY') {
 100  	 	 	 	 continue;
 101  	 	 	 }
 102  
 103  	 	 	 if (!isset($indexes[$row[2]])) {
 104  	 	 	 	 $indexes[$row[2]] = array(
 105  	 	 	 	 	 'unique' => ($row[1] == 0),
 106  	 	 	 	 	 'columns' => array()
 107  	 	 	 	 );
 108  	 	 	 }
 109  
 110  	 	 	 $indexes[$row[2]]['columns'][$row[3] - 1] = $row[4];
 111  	 	 }
 112  
 113  	 	 // sort columns by order in the index
 114  	 	 foreach ( array_keys ($indexes) as $index )
 115  	 	 {
 116  	 	 	 ksort ($indexes[$index]['columns']);
 117  	 	 }
 118  
 119  	 	 return $indexes;
 120  	 }
 121  
 122  	function Concat()
 123  	 {
 124  	 	 $s = '';
 125  	 	 $arr = func_get_args();
 126  
 127  	 	 // suggestion by andrew005#mnogo.ru
 128  	 	 $s = implode(',', $arr);
 129  	 	 if (strlen($s) > 0) {
 130  	 	 	 return "CONCAT($s)";
 131  	 	 }
 132  	 	 return '';
 133  	 }
 134  
 135  	function ServerInfo()
 136  	 {
 137  	 	 $arr['description'] = ADOConnection::GetOne('select version()');
 138  	 	 $arr['version'] = ADOConnection::_findvers($arr['description']);
 139  	 	 return $arr;
 140  	 }
 141  
 142  	function MetaTables($ttype=false, $showSchema=false, $mask=false)
 143  	 {
 144  	 	 $save = $this->metaTablesSQL;
 145  	 	 if ($showSchema && is_string($showSchema)) {
 146  	 	 	 $this->metaTablesSQL .= $this->qstr($showSchema);
 147  	 	 } else {
 148  	 	 	 $this->metaTablesSQL .= 'schema()';
 149  	 	 }
 150  
 151  	 	 if ($mask) {
 152  	 	 	 $mask = $this->qstr($mask);
 153  	 	 	 $this->metaTablesSQL .= " like $mask";
 154  	 	 }
 155  	 	 $ret = ADOConnection::MetaTables($ttype, $showSchema);
 156  
 157  	 	 $this->metaTablesSQL = $save;
 158  	 	 return $ret;
 159  	 }
 160  
 161      /**
 162       * @param bool $auto_commit
 163       * @return void
 164       */
 165      function SetAutoCommit($auto_commit)
 166      {
 167          $this->_connectionID->setAttribute(PDO::ATTR_AUTOCOMMIT, $auto_commit);
 168      }
 169  
 170  	function SetTransactionMode($transaction_mode)
 171  	 {
 172  	 	 $this->_transmode  = $transaction_mode;
 173  	 	 if (empty($transaction_mode)) {
 174  	 	 	 $this->Execute('SET TRANSACTION ISOLATION LEVEL REPEATABLE READ');
 175  	 	 	 return;
 176  	 	 }
 177  	 	 if (!stristr($transaction_mode, 'isolation')) {
 178  	 	 	 $transaction_mode = 'ISOLATION LEVEL ' . $transaction_mode;
 179  	 	 }
 180  	 	 $this->Execute('SET SESSION TRANSACTION ' . $transaction_mode);
 181  	 }
 182  
 183  	function MetaColumns($table, $normalize=true)
 184  	 {
 185  	 	 $this->_findschema($table, $schema);
 186  	 	 if ($schema) {
 187  	 	 	 $dbName = $this->database;
 188  	 	 	 $this->SelectDB($schema);
 189  	 	 }
 190  	 	 global $ADODB_FETCH_MODE;
 191  	 	 $save = $ADODB_FETCH_MODE;
 192  	 	 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 193  
 194  	 	 if ($this->fetchMode !== false) {
 195  	 	 	 $savem = $this->SetFetchMode(false);
 196  	 	 }
 197  	 	 $rs = $this->Execute(sprintf($this->metaColumnsSQL, $table));
 198  
 199  	 	 if ($schema) {
 200  	 	 	 $this->SelectDB($dbName);
 201  	 	 }
 202  
 203  	 	 if (isset($savem)) {
 204  	 	 	 $this->SetFetchMode($savem);
 205  	 	 }
 206  	 	 $ADODB_FETCH_MODE = $save;
 207  	 	 if (!is_object($rs)) {
 208  	 	 	 $false = false;
 209  	 	 	 return $false;
 210  	 	 }
 211  
 212  	 	 $retarr = array();
 213  	 	 while (!$rs->EOF){
 214  	 	 	 $fld = new ADOFieldObject();
 215  	 	 	 $fld->name = $rs->fields[0];
 216  	 	 	 $type = $rs->fields[1];
 217  
 218  	 	 	 // split type into type(length):
 219  	 	 	 $fld->scale = null;
 220  	 	 	 if (preg_match('/^(.+)\((\d+),(\d+)/', $type, $query_array)) {
 221  	 	 	 	 $fld->type = $query_array[1];
 222  	 	 	 	 $fld->max_length = is_numeric($query_array[2]) ? $query_array[2] : -1;
 223  	 	 	 	 $fld->scale = is_numeric($query_array[3]) ? $query_array[3] : -1;
 224  	 	 	 } elseif (preg_match('/^(.+)\((\d+)/', $type, $query_array)) {
 225  	 	 	 	 $fld->type = $query_array[1];
 226  	 	 	 	 $fld->max_length = is_numeric($query_array[2]) ? $query_array[2] : -1;
 227  	 	 	 } elseif (preg_match('/^(enum)\((.*)\)$/i', $type, $query_array)) {
 228  	 	 	 	 $fld->type = $query_array[1];
 229  	 	 	 	 $arr = explode(',', $query_array[2]);
 230  	 	 	 	 $fld->enums = $arr;
 231  	 	 	 	 $zlen = max(array_map('strlen', $arr)) - 2; // PHP >= 4.0.6
 232  	 	 	 	 $fld->max_length = ($zlen > 0) ? $zlen : 1;
 233  	 	 	 } else {
 234  	 	 	 	 $fld->type = $type;
 235  	 	 	 	 $fld->max_length = -1;
 236  	 	 	 }
 237  	 	 	 $fld->not_null = ($rs->fields[2] != 'YES');
 238  	 	 	 $fld->primary_key = ($rs->fields[3] == 'PRI');
 239  	 	 	 $fld->auto_increment = (strpos($rs->fields[5], 'auto_increment') !== false);
 240  	 	 	 $fld->binary = (strpos($type, 'blob') !== false);
 241  	 	 	 $fld->unsigned = (strpos($type, 'unsigned') !== false);
 242  
 243  	 	 	 if (!$fld->binary) {
 244  	 	 	 	 $d = $rs->fields[4];
 245  	 	 	 	 if ($d != '' && $d != 'NULL') {
 246  	 	 	 	 	 $fld->has_default = true;
 247  	 	 	 	 	 $fld->default_value = $d;
 248  	 	 	 	 } else {
 249  	 	 	 	 	 $fld->has_default = false;
 250  	 	 	 	 }
 251  	 	 	 }
 252  
 253  	 	 	 if ($save == ADODB_FETCH_NUM) {
 254  	 	 	 	 $retarr[] = $fld;
 255  	 	 	 } else {
 256  	 	 	 	 $retarr[strtoupper($fld->name)] = $fld;
 257  	 	 	 }
 258  	 	 	 $rs->MoveNext();
 259  	 	 }
 260  
 261  	 	 $rs->Close();
 262  	 	 return $retarr;
 263  	 }
 264  
 265  	 // returns true or false
 266  	function SelectDB($dbName)
 267  	 {
 268  	 	 $this->database = $dbName;
 269  	 	 $try = $this->Execute('use ' . $dbName);
 270  	 	 return ($try !== false);
 271  	 }
 272  
 273  	 // parameters use PostgreSQL convention, not MySQL
 274  	function SelectLimit($sql, $nrows=-1, $offset=-1, $inputarr=false, $secs=0)
 275  	 {
 276  	 	 $nrows = (int) $nrows;
 277  	 	 $offset = (int) $offset;	 	 
 278  	 	 $offsetStr =($offset>=0) ? "$offset," : '';
 279  	 	 // jason judge, see PHPLens Issue No: 9220
 280  	 	 if ($nrows < 0) {
 281  	 	 	 $nrows = '18446744073709551615';
 282  	 	 }
 283  
 284  	 	 if ($secs) {
 285  	 	 	 $rs = $this->CacheExecute($secs, $sql . " LIMIT $offsetStr$nrows", $inputarr);
 286  	 	 } else {
 287  	 	 	 $rs = $this->Execute($sql . " LIMIT $offsetStr$nrows", $inputarr);
 288  	 	 }
 289  	 	 return $rs;
 290  	 }
 291  
 292  	function SQLDate($fmt, $col=false)
 293  	 {
 294  	 	 if (!$col) {
 295  	 	 	 $col = $this->sysTimeStamp;
 296  	 	 }
 297  	 	 $s = 'DATE_FORMAT(' . $col . ",'";
 298  	 	 $concat = false;
 299  	 	 $len = strlen($fmt);
 300  	 	 for ($i=0; $i < $len; $i++) {
 301  	 	 	 $ch = $fmt[$i];
 302  	 	 	 switch($ch) {
 303  
 304  	 	 	 	 default:
 305  	 	 	 	 	 if ($ch == '\\') {
 306  	 	 	 	 	 	 $i++;
 307  	 	 	 	 	 	 $ch = substr($fmt, $i, 1);
 308  	 	 	 	 	 }
 309  	 	 	 	 	 // FALL THROUGH
 310  	 	 	 	 case '-':
 311  	 	 	 	 case '/':
 312  	 	 	 	 	 $s .= $ch;
 313  	 	 	 	 	 break;
 314  
 315  	 	 	 	 case 'Y':
 316  	 	 	 	 case 'y':
 317  	 	 	 	 	 $s .= '%Y';
 318  	 	 	 	 	 break;
 319  
 320  	 	 	 	 case 'M':
 321  	 	 	 	 	 $s .= '%b';
 322  	 	 	 	 	 break;
 323  
 324  	 	 	 	 case 'm':
 325  	 	 	 	 	 $s .= '%m';
 326  	 	 	 	 	 break;
 327  
 328  	 	 	 	 case 'D':
 329  	 	 	 	 case 'd':
 330  	 	 	 	 	 $s .= '%d';
 331  	 	 	 	 	 break;
 332  
 333  	 	 	 	 case 'Q':
 334  	 	 	 	 case 'q':
 335  	 	 	 	 	 $s .= "'),Quarter($col)";
 336  
 337  	 	 	 	 	 if ($len > $i+1) {
 338  	 	 	 	 	 	 $s .= ",DATE_FORMAT($col,'";
 339  	 	 	 	 	 } else {
 340  	 	 	 	 	 	 $s .= ",('";
 341  	 	 	 	 	 }
 342  	 	 	 	 	 $concat = true;
 343  	 	 	 	 	 break;
 344  
 345  	 	 	 	 case 'H':
 346  	 	 	 	 	 $s .= '%H';
 347  	 	 	 	 	 break;
 348  
 349  	 	 	 	 case 'h':
 350  	 	 	 	 	 $s .= '%I';
 351  	 	 	 	 	 break;
 352  
 353  	 	 	 	 case 'i':
 354  	 	 	 	 	 $s .= '%i';
 355  	 	 	 	 	 break;
 356  
 357  	 	 	 	 case 's':
 358  	 	 	 	 	 $s .= '%s';
 359  	 	 	 	 	 break;
 360  
 361  	 	 	 	 case 'a':
 362  	 	 	 	 case 'A':
 363  	 	 	 	 	 $s .= '%p';
 364  	 	 	 	 	 break;
 365  
 366  	 	 	 	 case 'w':
 367  	 	 	 	 	 $s .= '%w';
 368  	 	 	 	 	 break;
 369  
 370  	 	 	 	 case 'W':
 371  	 	 	 	 	 $s .= '%U';
 372  	 	 	 	 	 break;
 373  
 374  	 	 	 	 case 'l':
 375  	 	 	 	 	 $s .= '%W';
 376  	 	 	 	 	 break;
 377  	 	 	 }
 378  	 	 }
 379  	 	 $s .= "')";
 380  	 	 if ($concat) {
 381  	 	 	 $s = "CONCAT($s)";
 382  	 	 }
 383  	 	 return $s;
 384  	 }
 385  
 386  	function GenID($seqname='adodbseq',$startID=1)
 387  	 {
 388  	 	 $getnext = sprintf($this->_genIDSQL,$seqname);
 389  	 	 $holdtransOK = $this->_transOK; // save the current status
 390  	 	 $rs = @$this->Execute($getnext);
 391  	 	 if (!$rs) {
 392  	 	 	 if ($holdtransOK) $this->_transOK = true; //if the status was ok before reset
 393  	 	 	 $this->Execute(sprintf($this->_genSeqSQL,$seqname));
 394  	 	 	 $cnt = $this->GetOne(sprintf($this->_genSeqCountSQL,$seqname));
 395  	 	 	 if (!$cnt) $this->Execute(sprintf($this->_genSeq2SQL,$seqname,$startID-1));
 396  	 	 	 $rs = $this->Execute($getnext);
 397  	 	 }
 398  
 399  	 	 if ($rs) {
 400  	 	 	 $this->genID = $this->_connectionID->lastInsertId($seqname);
 401  	 	 	 $rs->Close();
 402  	 	 } else {
 403  	 	 	 $this->genID = 0;
 404  	 	 }
 405  
 406  	 	 return $this->genID;
 407  	 }
 408  
 409  
 410  	function createSequence($seqname='adodbseq',$startID=1)
 411  	 {
 412  	 	 if (empty($this->_genSeqSQL)) {
 413  	 	 	 return false;
 414  	 	 }
 415  	 	 $ok = $this->Execute(sprintf($this->_genSeqSQL,$seqname,$startID));
 416  	 	 if (!$ok) {
 417  	 	 	 return false;
 418  	 	 }
 419  
 420  	 	 return $this->Execute(sprintf($this->_genSeq2SQL,$seqname,$startID-1));
 421  	 }
 422  }