Varien_Db_Adapter_Pdo_Mysql Class Reference

List of all members.

Public Member Functions

beginTransaction ()

commit ()

rollback ()

convertDate ($date)

convertDateTime ($datetime)

raw_query ($sql)

raw_fetchRow ($sql, $field=null)

query ($sql, $bind=array())

proccessBindCallback ($matches)

multi_query ($sql)

dropForeignKey ($tableName, $foreignKey, $schemaName=null)

dropKey ($tableName, $keyName, $shemaName=null)

purgeOrphanRecords ($tableName, $columnName, $refTableName, $refColumnName, $onDelete= 'cascade')

addConstraint ($fkName, $tableName, $columnName, $refTableName, $refColumnName, $onDelete= 'cascade', $onUpdate= 'cascade', $purge=false)

tableColumnExists ($tableName, $columnName, $schemaName=null)

addColumn ($tableName, $columnName, $definition)

dropColumn ($tableName, $columnName, $shemaName=null)

changeColumn ($tableName, $oldColumnName, $newColumnName, $definition, $showStatus=false)

modifyColumn ($tableName, $columnName, $definition, $showStatus=false)

showTableStatus ($tableName)

getKeyList ($tableName, $schemaName=null)

getCreateTable ($tableName, $schemaName=null)

getForeignKeys ($tableName, $schemaName=null)

getIndexList ($tableName, $schemaName=null)

addKey ($tableName, $indexName, $fields, $indexType= 'index')

select ()

quoteInto ($text, $value, $type=null, $count=null)

resetDdlCache ($tableName=null, $schemaName=null)

describeTable ($tableName, $schemaName=null)

truncate ($tableName, $schemaName=null)

changeTableEngine ($tableName, $engine, $schemaName=null)

Public Attributes

const DEBUG_CONNECT = 0

const DEBUG_TRANSACTION = 1

const DEBUG_QUERY = 2

const ISO_DATE_FORMAT = 'yyyy-MM-dd'

const ISO_DATETIME_FORMAT = 'yyyy-MM-dd HH-mm-ss'

const DDL_DESCRIBE = 1

const DDL_CREATE = 2

const DDL_INDEX = 3

const DDL_FOREIGN_KEY = 4

Protected Member Functions

_connect ()

_unQuote ($string)

_splitMultiQuery ($sql)

_removeDuplicateEntry ($table, $fields, $ids)

_debugTimer ()

_debugStat ($type, $sql, $bind=array(), $result=null)

_debugException (Exception $e)

_debugWriteToFile ($str)

_getTableName ($tableName, $schemaName=null)

Protected Attributes

$_transactionLevel = 0

$_connectionFlagsSet = false

$_ddlCache = array()

$_bindParams = array()

$_bindIncrement = 0

$_debug = false

$_logQueryTime = 0.05

$_debugFile = 'var/debug/sql.txt'

$_debugIoAdapter

$_debugTimer = 0

Detailed Description

Mysql PDO DB adapter

Definition at line 30 of file Mysql.php.

Member Function Documentation

_connect ( ) [protected]

Creates a PDO object and connects to the database.

http://bugs.mysql.com/bug.php?id=18551

Definition at line 193 of file Mysql.php.

00194     {
00195         if ($this->_connection) {
00196             return;
00197         }
00198 
00199         if (!extension_loaded('pdo_mysql')) {
00200             throw new Zend_Db_Adapter_Exception('pdo_mysql extension is not installed');
00201         }
00202 
00203         if (strpos($this->_config['host'], '/')!==false) {
00204             $this->_config['unix_socket'] = $this->_config['host'];
00205             unset($this->_config['host']);
00206         } elseif (strpos($this->_config['host'], ':')!==false) {
00207             list($this->_config['host'], $this->_config['port']) = explode(':', $this->_config['host']);
00208         }
00209 
00210         $this->_debugTimer();
00211         parent::_connect();
00212         $this->_debugStat(self::DEBUG_CONNECT, '');
00213 
00214         /** @link http://bugs.mysql.com/bug.php?id=18551 */
00215         $this->_connection->query("SET SQL_MODE=''");
00216 
00217         if (!$this->_connectionFlagsSet) {
00218             $this->_connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
00219             $this->_connection->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
00220             $this->_connectionFlagsSet = true;
00221         }
00222     }

_debugException ( Exception $ e ) [protected]

Write exception and thow

Parameters:

Exception

$e

Exceptions:

Exception

Definition at line 1036 of file Mysql.php.

01037     {
01038         if (!$this->_debug) {
01039             throw $e;
01040         }
01041 
01042         $nl   = "\n";
01043         $code = 'EXCEPTION ' . $e->getMessage() . $nl
01044             . 'E TRACE: ' . print_r($e->getTrace(), true) . $nl . $nl;
01045         $this->_debugWriteToFile($code);
01046 
01047         throw $e;
01048     }

_debugStat	(	$	type,
		$	sql,
		$	bind = `array()`,
		$	result = `null`
	)			`[protected]`

Start debug timer

Returns:: Varien_Db_Adapter_Pdo_Mysql

Definition at line 992 of file Mysql.php.

00993     {
00994         if (!$this->_debug) {
00995             return $this;
00996         }
00997 
00998         $code = '## ' . getmypid() . ' ## ';
00999         $nl   = "\n";
01000         $time = sprintf('%.4f', microtime(true) - $this->_debugTimer);
01001 
01002         if ($time < $this->_logQueryTime) {
01003             return $this;
01004         }
01005         switch ($type) {
01006             case self::DEBUG_CONNECT:
01007                 $code .= 'CONNECT' . $nl;
01008                 break;
01009             case self::DEBUG_TRANSACTION:
01010                 $code .= 'TRANSACTION ' . $sql . $nl;
01011                 break;
01012             case self::DEBUG_QUERY:
01013                 $code .= 'QUERY' . $nl;
01014                 $code .= 'SQL: ' . $sql . $nl;
01015                 if ($bind) {
01016                     $code .= 'BIND: ' . print_r($bind, true) . $nl;
01017                 }
01018                 if ($result instanceof Zend_Db_Statement_Pdo) {
01019                     $code .= 'AFF: ' . $result->rowCount() . $nl;
01020                 }
01021                 break;
01022         }
01023         $code .= 'TIME: ' . $time . $nl . $nl;
01024 
01025         $this->_debugWriteToFile($code);
01026 
01027         return $this;
01028     }

_debugTimer ( ) [protected]

Start debug timer

Returns:: Varien_Db_Adapter_Pdo_Mysql

Definition at line 979 of file Mysql.php.

00980     {
00981         if ($this->_debug) {
00982             $this->_debugTimer = microtime(true);
00983         }
00984         return $this;
00985     }

_debugWriteToFile ( $ str ) [protected]

Debug write to file process

Parameters:

string

$str

Definition at line 1055 of file Mysql.php.

01056     {
01057         if (!$this->_debugIoAdapter) {
01058             $this->_debugIoAdapter = new Varien_Io_File();
01059             $dir = $this->_debugIoAdapter->dirname($this->_debugFile);
01060             $this->_debugIoAdapter->checkAndCreateFolder($dir);
01061             $this->_debugIoAdapter->open(array('path' => $dir));
01062             $this->_debugFile = basename($this->_debugFile);
01063         }
01064 
01065         $this->_debugIoAdapter->streamOpen($this->_debugFile, 'a');
01066         $this->_debugIoAdapter->streamLock();
01067         $this->_debugIoAdapter->streamWrite($str);
01068         $this->_debugIoAdapter->streamUnlock();
01069         $this->_debugIoAdapter->streamClose();
01070     }

_getTableName	(	$	tableName,
		$	schemaName = `null`
	)			`[protected]`

Retrieve ddl cache name

Parameters:

	string	$tableName
	string	$schemaName

Definition at line 1097 of file Mysql.php.

01098     {
01099         return ($schemaName ? $schemaName . '.' : '') . $tableName;
01100     }

_removeDuplicateEntry	(	$	table,
		$	fields,
		$	ids
	)			`[protected]`

Remove duplicate entry for create key

Parameters:

	string	$table
	array	$fields
	array	$ids

Returns:: Varien_Db_Adapter_Pdo_Mysql

Definition at line 937 of file Mysql.php.

00938     {
00939         $where = array();
00940         $i = 0;
00941         foreach ($fields as $field) {
00942             $where[] = $this->quoteInto($field . '=?', $ids[$i]);
00943             $i ++;
00944         }
00945 
00946         if (!$where) {
00947             return $this;
00948         }
00949         $whereCond = join(' AND ', $where);
00950         $sql = sprintf('SELECT COUNT(*) as `cnt` FROM `%s` WHERE %s', $table, $whereCond);
00951 
00952         if ($cnt = $this->raw_fetchRow($sql, 'cnt')) {
00953             $sql = sprintf('DELETE FROM `%s` WHERE %s LIMIT %d',
00954                 $table,
00955                 $whereCond,
00956                 $cnt - 1
00957             );
00958             $this->raw_query($sql);
00959         }
00960 
00961         return $this;
00962     }

_splitMultiQuery ( $ sql ) [protected]

Split multi statement query

Parameters:

$sql

string

Returns:: array

Definition at line 358 of file Mysql.php.

00359     {
00360         $parts = preg_split('#(;|\'|"|\\\\|//|--|\n|/\*|\*/)#', $sql, null, PREG_SPLIT_NO_EMPTY | PREG_SPLIT_DELIM_CAPTURE);
00361 
00362         $q = false;
00363         $c = false;
00364         $stmts = array();
00365         $s = '';
00366 
00367         foreach ($parts as $i=>$part) {
00368             // strings
00369             if (($part==="'" || $part==='"') && ($i===0 || $parts[$i-1]!=='\\')) {
00370                 if ($q===false) {
00371                     $q = $part;
00372                 } elseif ($q===$part) {
00373                     $q = false;
00374                 }
00375             }
00376 
00377             // single line comments
00378             if (($part==='//' || $part==='--') && ($i===0 || $parts[$i-1]==="\n")) {
00379                 $c = $part;
00380             } elseif ($part==="\n" && ($c==='//' || $c==='--')) {
00381                 $c = false;
00382             }
00383 
00384             // multi line comments
00385             if ($part==='/*' && $c===false) {
00386                 $c = '/*';
00387             } elseif ($part==='*/' && $c==='/*') {
00388                 $c = false;
00389             }
00390 
00391             // statements
00392             if ($part===';' && $q===false && $c===false) {
00393                 if (trim($s)!=='') {
00394                     $stmts[] = trim($s);
00395                     $s = '';
00396                 }
00397             } else {
00398                 $s .= $part;
00399             }
00400         }
00401         if (trim($s)!=='') {
00402             $stmts[] = trim($s);
00403         }
00404 
00405         return $stmts;
00406     }

_unQuote ( $ string ) [protected]

Unquote raw string (use for auto-bind)

Parameters:

string

$string

Returns:: string

Definition at line 310 of file Mysql.php.

00311     {
00312         $translate = array(
00313             "\\000" => "\000",
00314             "\\n"   => "\n",
00315             "\\r"   => "\r",
00316             "\\\\"  => "\\",
00317             "\'"    => "'",
00318             "\\\""  => "\"",
00319             "\\032" => "\032"
00320         );
00321         return strtr($string, $translate);
00322     }

addColumn	(	$	tableName,
		$	columnName,
		$	definition
	)

Add new column to table

Parameters:

	string	$tableName
	string	$columnName
	string	$definition

Returns:: bool

Definition at line 563 of file Mysql.php.

00564     {
00565         if ($this->tableColumnExists($tableName, $columnName)) {
00566             return true;
00567         }
00568 
00569         $sql = sprintf('ALTER TABLE %s ADD COLUMN %s %s',
00570             $this->quoteIdentifier($tableName),
00571             $this->quoteIdentifier($columnName),
00572             $definition
00573         );
00574         $result = $this->raw_query($sql);
00575         $this->resetDdlCache($tableName);
00576         return $result;
00577     }

addConstraint	(	$	fkName,
		$	tableName,
		$	columnName,
		$	refTableName,
		$	refColumnName,
		$	onDelete = `'cascade'`,
		$	onUpdate = `'cascade'`,
		$	purge = `false`
	)

Add foreign key to table. If FK with same name exist - it will be deleted

Parameters:

	string	$fkName foreign key name
	string	$tableName main table name
	string	$keyName main table field name
	string	$refTableName refered table name
	string	$refKeyName refered table field name
	string	$onUpdate on update statement
	string	$onDelete on delete statement
	bool	$purge

Returns:: mixed

Definition at line 510 of file Mysql.php.

00512     {
00513         if (substr($fkName, 0, 3) != 'FK_') {
00514             $fkName = 'FK_' . $fkName;
00515         }
00516 
00517         $this->dropForeignKey($tableName, $fkName);
00518 
00519         if ($purge) {
00520             $this->purgeOrphanRecords($tableName, $columnName, $refTableName, $refColumnName, $onDelete);
00521         }
00522 
00523         $sql = 'ALTER TABLE `'.$tableName.'` ADD CONSTRAINT `'.$fkName.'`'
00524             . ' FOREIGN KEY (`'.$columnName.'`) REFERENCES `'.$refTableName.'` (`'.$refColumnName.'`)';
00525         if (!is_null($onDelete)) {
00526             $sql .= ' ON DELETE ' . strtoupper($onDelete);
00527         }
00528         if (!is_null($onUpdate)) {
00529             $sql .= ' ON UPDATE ' . strtoupper($onUpdate);
00530         }
00531 
00532         $this->resetDdlCache($tableName);
00533         return $this->raw_query($sql);
00534     }

addKey	(	$	tableName,
		$	indexName,
		$	fields,
		$	indexType = `'index'`
	)

Add Index Key

Parameters:

	string	$tableName
	string	$indexName
	string\|array	$fields
	string	$indexType

Returns:

Definition at line 860 of file Mysql.php.

00861     {
00862         $columns = $this->describeTable($tableName);
00863         $keyList = $this->getKeyList($tableName);
00864 
00865         $sql = 'ALTER TABLE '.$this->quoteIdentifier($tableName);
00866         if (isset($keyList[$indexName])) {
00867             $sql .= ' DROP INDEX ' . $this->quoteIdentifier($indexName) . ',';
00868         }
00869 
00870         if (!is_array($fields)) {
00871             $fields = array($fields);
00872         }
00873 
00874         $fieldSql = array();
00875         foreach ($fields as $field) {
00876             if (!isset($columns[$field])) {
00877                 $msg = sprintf('There is no field "%s" that you are trying to create an index on "%s"',
00878                     $field, $tableName);
00879                 throw new Exception($msg);
00880             }
00881             $fieldSql[] = $this->quoteIdentifier($field);
00882         }
00883         $fieldSql = join(',', $fieldSql);
00884 
00885         switch (strtolower($indexType)) {
00886             case 'primary':
00887                 $condition = 'PRIMARY KEY';
00888                 break;
00889             case 'unique':
00890                 $condition = 'UNIQUE ' . $this->quoteIdentifier($indexName);
00891                 break;
00892             case 'fulltext':
00893                 $condition = 'FULLTEXT ' . $this->quoteIdentifier($indexName);
00894                 break;
00895             default:
00896                 $condition = 'INDEX ' . $this->quoteIdentifier($indexName);
00897                 break;
00898         }
00899 
00900         $sql .= ' ADD ' . $condition . ' (' . $fieldSql . ')';
00901 
00902         $cycle = true;
00903         while ($cycle === true) {
00904             try {
00905                 $result = $this->raw_query($sql);
00906                 $cycle  = false;
00907             }
00908             catch (PDOException $e) {
00909                 if (in_array(strtolower($indexType), array('primary', 'unique'))) {
00910                     $match = array();
00911                     if (preg_match('#SQLSTATE\[23000\]: [^:]+: 1062[^\']+\'([\d-]+)\'#', $e->getMessage(), $match)) {
00912                         $ids = explode('-', $match[1]);
00913                         $this->_removeDuplicateEntry($tableName, $fields, $ids);
00914                         continue;
00915                     }
00916                 }
00917                 throw $e;
00918             }
00919             catch (Exception $e) {
00920                 throw $e;
00921             }
00922         }
00923 
00924         $this->resetDdlCache($tableName);
00925 
00926         return $result;
00927     }

beginTransaction ( )

Begin new DB transaction for connection

Returns:: Varien_Db_Adapter_Pdo_Mysql

Definition at line 119 of file Mysql.php.

00120     {
00121         if ($this->_transactionLevel===0) {
00122             $this->_debugTimer();
00123             parent::beginTransaction();
00124             $this->_debugStat(self::DEBUG_TRANSACTION, 'BEGIN');
00125         }
00126         $this->_transactionLevel++;
00127         return $this;
00128     }

changeColumn	(	$	tableName,
		$	oldColumnName,
		$	newColumnName,
		$	definition,
		$	showStatus = `false`
	)

Change column

Parameters:

	string	$tableName
	string	$oldColumnName
	string	$newColumnName
	string	$definition
	bool	$showStatus

Returns:: mixed

Definition at line 622 of file Mysql.php.

00623     {
00624         if (!$this->tableColumnExists($tableName, $oldColumnName)) {
00625             throw new Exception(sprintf('Column "%s" does not exists on table "%s"', $oldColumnName, $tableName));
00626         }
00627 
00628         $sql = sprintf('ALTER TABLE %s CHANGE COLUMN %s %s %s',
00629             $this->quoteIdentifier($tableName),
00630             $this->quoteIdentifier($oldColumnName),
00631             $this->quoteIdentifier($newColumnName),
00632             $definition);
00633 
00634         $result = $this->raw_query($sql);
00635         if ($showStatus) {
00636             $this->showTableStatus($tableName);
00637         }
00638 
00639         $this->resetDdlCache($tableName);
00640         return $result;
00641     }

changeTableEngine	(	$	tableName,
		$	engine,
		$	schemaName = `null`
	)

Change table storage engine

Parameters:

	string	$tableName
	string	$engine
	string	$type

Returns:: mixed

Definition at line 1189 of file Mysql.php.

01190     {
01191         $sql = sprintf('ALTER TABLE %s ENGINE=%s',
01192             $this->quoteIdentifier($this->_getTableName($tableName, $schemaName)),
01193             $engine);
01194         return $this->raw_query($sql);
01195     }

commit ( )

Commit DB transaction

Returns:: Varien_Db_Adapter_Pdo_Mysql

Definition at line 135 of file Mysql.php.

00136     {
00137         if ($this->_transactionLevel===1) {
00138             $this->_debugTimer();
00139             parent::commit();
00140             $this->_debugStat(self::DEBUG_TRANSACTION, 'COMMIT');
00141         }
00142         $this->_transactionLevel--;
00143         return $this;
00144     }

convertDate ( $ date )

Convert date to DB format

Parameters:

mixed

$date

Returns:: string

Definition at line 168 of file Mysql.php.

00169     {
00170         if ($date instanceof Zend_Date) {
00171             return $date->toString(self::ISO_DATE_FORMAT);
00172         }
00173         return strftime('%Y-%m-%d', strtotime($date));
00174     }

convertDateTime ( $ datetime )

Convert date and time to DB format

Parameters:

mixed

$date

Returns:: string

Definition at line 182 of file Mysql.php.

00183     {
00184         if ($datetime instanceof Zend_Date) {
00185             return $datetime->toString(self::ISO_DATETIME_FORMAT);
00186         }
00187         return strftime('%Y-%m-%d %H:%M:%S', strtotime($datetime));
00188     }

describeTable	(	$	tableName,
		$	schemaName = `null`
	)

Returns the column descriptions for a table.

The return value is an associative array keyed by the column name, as returned by the RDBMS.

The value of each array element is an associative array with the following keys:

SCHEMA_NAME => string; name of database or schema TABLE_NAME => string; COLUMN_NAME => string; column name COLUMN_POSITION => number; ordinal position of column in table DATA_TYPE => string; SQL datatype name of column DEFAULT => string; default expression of column, null if none NULLABLE => boolean; true if column can have nulls LENGTH => number; length of CHAR/VARCHAR SCALE => number; scale of NUMERIC/DECIMAL PRECISION => number; precision of NUMERIC/DECIMAL UNSIGNED => boolean; unsigned property of an integer type PRIMARY => boolean; true if column is part of the primary key PRIMARY_POSITION => integer; position of column in primary key IDENTITY => integer; true if column is auto-generated with unique values

Parameters:

	string	$tableName
	string	$schemaName OPTIONAL

Returns:: array

Definition at line 1154 of file Mysql.php.

01155     {
01156         $cacheKey = $this->_getTableName($tableName, $schemaName);
01157 
01158         if (!isset($this->_ddlCache[self::DDL_DESCRIBE][$cacheKey])) {
01159             $this->_ddlCache[self::DDL_DESCRIBE][$cacheKey] = parent::describeTable($tableName, $schemaName);
01160         }
01161 
01162         return $this->_ddlCache[self::DDL_DESCRIBE][$cacheKey];
01163     }

dropColumn	(	$	tableName,
		$	columnName,
		$	shemaName = `null`
	)

Delete table column

Parameters:

	string	$tableName
	string	$columnName
	string	$shemaName

Returns:: bool

Definition at line 587 of file Mysql.php.

00588     {
00589         if (!$this->tableColumnExists($tableName, $columnName, $shemaName)) {
00590             return true;
00591         }
00592 
00593         $alterDrop = array();
00594 
00595         $foreignKeys = $this->getForeignKeys($tableName, $shemaName);
00596         foreach ($foreignKeys as $fkProp) {
00597             if ($fkProp['COLUMN_NAME'] == $columnName) {
00598                 $alterDrop[] = sprintf('DROP FOREIGN KEY %s', $this->quoteIdentifier($fkProp['FK_NAME']));
00599             }
00600         }
00601 
00602         $alterDrop[] = sprintf('DROP COLUMN %s', $this->quoteIdentifier($columnName));
00603         $sql = sprintf('ALTER TABLE %s %s',
00604             $this->quoteIdentifier($this->_getTableName($tableName, $shemaName)),
00605             join(', ', $alterDrop));
00606 
00607         $this->resetDdlCache($tableName, $shemaName);
00608         return $this->raw_query($sql);
00609     }

dropForeignKey	(	$	tableName,
		$	foreignKey,
		$	schemaName = `null`
	)

Delete foreign key if it exist

Parameters:

	string	$tableName
	string	$foreignKey
	string	$shemaName

Returns:: mixed

Definition at line 416 of file Mysql.php.

00417     {
00418         $foreignKeys = $this->getForeignKeys($tableName, $schemaName);
00419         if (isset($foreignKeys[strtoupper($foreignKey)])) {
00420             $sql = sprintf('ALTER TABLE %s DROP FOREIGN KEY %s',
00421                 $this->quoteIdentifier($this->_getTableName($tableName, $schemaName)),
00422                 $this->quoteIdentifier($foreignKeys[strtoupper($foreignKey)]['FK_NAME']));
00423 //            echo '<pre>';
00424 //            var_dump($tableName, $schemaName, $foreignKey, $foreignKeys, $sql);
00425 //            echo '</pre>';
00426 
00427             $this->resetDdlCache($tableName, $schemaName);
00428 
00429             return $this->raw_query($sql);
00430         }
00431 
00432         return true;
00433     }

dropKey	(	$	tableName,
		$	keyName,
		$	shemaName = `null`
	)

Delete index from a table if it exist

Parameters:

	string	$tableName
	string	$keyName
	string	$shemaName

Returns:: bool

Definition at line 443 of file Mysql.php.

00444     {
00445         $indexList = $this->getIndexList($tableName, $shemaName);
00446         $keyName = strtoupper($keyName);
00447         if (!isset($indexList[$keyName])) {
00448             return true;
00449         }
00450 
00451         if ($keyName == 'PRIMARY') {
00452             $cond = 'DROP PRIMARY KEY';
00453         }
00454         else {
00455             $cond = sprintf('DROP KEY %s', $this->quoteIdentifier($indexList[$keyName]['KEY_NAME']));
00456         }
00457         $sql = sprintf('ALTER TABLE %s %s',
00458             $this->quoteIdentifier($this->_getTableName($tableName, $shemaName)),
00459             $cond);
00460 
00461         $this->resetDdlCache($tableName, $shemaName);
00462         return $this->raw_query($sql);
00463     }

getCreateTable	(	$	tableName,
		$	schemaName = `null`
	)

Retrieve Create Table SQL

Parameters:

	string	$tableName
	string	$schemaName

Returns:: string

Definition at line 711 of file Mysql.php.

00712     {
00713         $tableName = $this->_getTableName($tableName, $schemaName);
00714         if (!isset($this->_ddlCache[self::DDL_CREATE][$tableName])) {
00715             $sql = sprintf('SHOW CREATE TABLE %s', $this->quoteIdentifier($tableName));
00716             $this->_ddlCache[self::DDL_CREATE][$tableName] = $this->raw_fetchRow($sql, 'Create Table');
00717         }
00718         return $this->_ddlCache[self::DDL_CREATE][$tableName];
00719     }

getForeignKeys	(	$	tableName,
		$	schemaName = `null`
	)

Retrieve the foreign keys descriptions for a table.

The return value is an associative array keyed by the UPPERCASE foreign key, as returned by the RDBMS.

The value of each array element is an associative array with the following keys:

FK_NAME => string; original foreign key name SCHEMA_NAME => string; name of database or schema TABLE_NAME => string; COLUMN_NAME => string; column name REF_SCHEMA_NAME => string; name of reference database or schema REF_TABLE_NAME => string; reference table name REF_COLUMN_NAME => string; reference column name ON_DELETE => string; action type on delete row ON_UPDATE => string; action type on update row

Parameters:

	string	$tableName
	string	$schemaName

Returns:: array

Definition at line 744 of file Mysql.php.

00745     {
00746         $cacheKey = $this->_getTableName($tableName, $schemaName);
00747         if (!isset($this->_ddlCache[self::DDL_FOREIGN_KEY][$cacheKey])) {
00748             $foreignKeys = array();
00749             $createSql = $this->getCreateTable($tableName, $schemaName);
00750 
00751             // collect CONSTRAINT
00752             $regExp  = '#,\s+CONSTRAINT `([^`]*)` FOREIGN KEY \(`([^`]*)`\) '
00753                 . 'REFERENCES (`[^`]*\.)?`([^`]*)` \(`([^`]*)`\)'
00754                 . '( ON DELETE (RESTRICT|CASCADE|SET NULL|NO ACTION))?'
00755                 . '( ON UPDATE (RESTRICT|CASCADE|SET NULL|NO ACTION))?#';
00756             $matches = array();
00757             preg_match_all($regExp, $createSql, $matches, PREG_SET_ORDER);
00758             foreach ($matches as $match) {
00759                 $foreignKeys[strtoupper($match[1])] = array(
00760                     'FK_NAME'           => $match[1],
00761                     'SCHEMA_NAME'       => $schemaName,
00762                     'TABLE_NAME'        => $tableName,
00763                     'COLUMN_NAME'       => $match[2],
00764                     'REF_SHEMA_NAME'    => isset($match[3]) ? $match[3] : $schemaName,
00765                     'REF_TABLE_NAME'    => $match[4],
00766                     'REF_COLUMN_NAME'   => $match[5],
00767                     'ON_DELETE'         => isset($match[6]) ? $match[7] : '',
00768                     'ON_UPDATE'         => isset($match[8]) ? $match[9] : ''
00769                 );
00770             }
00771 
00772             $this->_ddlCache[self::DDL_FOREIGN_KEY][$cacheKey] = $foreignKeys;
00773         }
00774 
00775         return $this->_ddlCache[self::DDL_FOREIGN_KEY][$cacheKey];
00776     }

getIndexList	(	$	tableName,
		$	schemaName = `null`
	)

Retrieve table index information

The return value is an associative array keyed by the UPPERCASE index key, as returned by the RDBMS.

The value of each array element is an associative array with the following keys:

SCHEMA_NAME => string; name of database or schema TABLE_NAME => string; name of the table KEY_NAME => string; the original index name COLUMNS_LIST => array; array of index column names INDEX_TYPE => string; create index type INDEX_METHOD => string; index method using type => string; see INDEX_TYPE fields => array; see COLUMNS_LIST

Parameters:

	string	$tableName
	string	$schemaName

Returns:: array

Definition at line 800 of file Mysql.php.

00801     {
00802         $cacheKey = $this->_getTableName($tableName, $schemaName);
00803         if (!isset($this->_ddlCache[self::DDL_INDEX][$cacheKey])) {
00804             $indexList = array();
00805 
00806             $sql = sprintf('SHOW INDEX FROM %s',
00807                 $this->quoteIdentifier($this->_getTableName($tableName, $schemaName)));
00808             foreach ($this->fetchAll($sql) as $row) {
00809                 $fieldKeyName   = 'Key_name';
00810                 $fieldNonUnique = 'Non_unique';
00811                 $fieldColumn    = 'Column_name';
00812                 $fieldIndexType = 'Index_type';
00813 
00814                 if ($row[$fieldKeyName] == 'PRIMARY') {
00815                     $indexType  = 'primary';
00816                 }
00817                 elseif ($row[$fieldNonUnique] == 0) {
00818                     $indexType  = 'unique';
00819                 }
00820                 elseif ($row[$fieldIndexType] == 'FULLTEXT') {
00821                     $indexType  = 'fulltext';
00822                 }
00823                 else {
00824                     $indexType  = 'index';
00825                 }
00826 
00827                 if (isset($indexList[$row[$fieldKeyName]])) {
00828                     $indexList[$row[$fieldKeyName]]['fields'][] = $row[$fieldColumn]; // for compatible
00829                     $indexList[$row[$fieldKeyName]]['COLUMNS_LIST'][] = $row[$fieldColumn];
00830                 }
00831                 else {
00832                     $indexList[strtoupper($row[$fieldKeyName])] = array(
00833                         'SCHEMA_NAME'   => $schemaName,
00834                         'TABLE_NAME'    => $tableName,
00835                         'KEY_NAME'      => $row[$fieldKeyName],
00836                         'COLUMNS_LIST'  => array($row[$fieldColumn]),
00837                         'INDEX_TYPE'    => strtoupper($indexType),
00838                         'INDEX_METHOD'  => $row[$fieldIndexType],
00839                         'type'          => $indexType, // for compatible
00840                         'fields'        => array($row[$fieldColumn]) // for compatible
00841                     );
00842                 }
00843             }
00844 
00845             $this->_ddlCache[self::DDL_INDEX][$cacheKey] = $indexList;
00846         }
00847 
00848         return $this->_ddlCache[self::DDL_INDEX][$cacheKey];
00849     }

getKeyList	(	$	tableName,
		$	schemaName = `null`
	)

Retrieve table index key list

Deprecated:: use getIndexList(

Parameters:

	string	$tableName
	string	$schemaName

Returns:: array

Definition at line 692 of file Mysql.php.

00693     {
00694         $keyList   = array();
00695         $indexList = $this->getIndexList($tableName, $schemaName);
00696 
00697         foreach ($indexList as $indexProp) {
00698             $keyList[$indexProp['KEY_NAME']] = $indexProp['COLUMNS_LIST'];
00699         }
00700 
00701         return $keyList;
00702     }

modifyColumn	(	$	tableName,
		$	columnName,
		$	definition,
		$	showStatus = `false`
	)

Modify column defination or position

Parameters:

	string	$tableName
	string	$columnName
	string	$definition
	bool	$showStatus

Returns:: mixed

Definition at line 653 of file Mysql.php.

00654     {
00655         if (!$this->tableColumnExists($tableName, $columnName)) {
00656             throw new Exception(sprintf('Column "%s" does not exists on table "%s"', $columnName, $tableName));
00657         }
00658 
00659         $sql = sprintf('ALTER TABLE %s MODIFY COLUMN %s %s',
00660             $this->quoteIdentifier($tableName),
00661             $this->quoteIdentifier($columnName),
00662             $definition);
00663         $result = $this->raw_query($sql);
00664         if ($showStatus) {
00665             $this->showTableStatus($tableName);
00666         }
00667 
00668         $this->resetDdlCache($tableName);
00669         return $result;
00670     }

multi_query ( $ sql )

Run Multi Query

Parameters:

string

$sql

Returns:: array

Definition at line 330 of file Mysql.php.

00331     {
00332         ##$result = $this->raw_query($sql);
00333 
00334         #$this->beginTransaction();
00335         try {
00336             $stmts = $this->_splitMultiQuery($sql);
00337             $result = array();
00338             foreach ($stmts as $stmt) {
00339                 $result[] = $this->raw_query($stmt);
00340             }
00341             #$this->commit();
00342         } catch (Exception $e) {
00343             #$this->rollback();
00344             throw $e;
00345         }
00346 
00347         $this->resetDdlCache();
00348 
00349         return $result;
00350     }

proccessBindCallback ( $ matches )

Definition at line 291 of file Mysql.php.

00292     {
00293         if (isset($matches[6]) && (
00294             strpos($matches[6], "'") !== false ||
00295             strpos($matches[6], ":") !== false ||
00296             strpos($matches[6], "?") !== false)) {
00297             $bindName = ':_mage_bind_var_' . ( ++ $this->_bindIncrement );
00298             $this->_bindParams[$bindName] = $this->_unQuote($matches[6]);
00299             return ' ' . $bindName;
00300         }
00301         return $matches[0];
00302     }

purgeOrphanRecords	(	$	tableName,
		$	columnName,
		$	refTableName,
		$	refColumnName,
		$	onDelete = `'cascade'`
	)

Prepare table before add constraint foreign key

Parameters:

	string	$tableName
	string	$columnName
	string	$refTableName
	string	$refColumnName
	string	$onDelete

Returns:: Varien_Db_Adapter_Pdo_Mysql

Definition at line 475 of file Mysql.php.

00476     {
00477         if (strtoupper($onDelete) == 'CASCADE'
00478             || strtoupper($onDelete) == 'RESTRICT') {
00479             $sql = "DELETE `p`.* FROM `{$tableName}` AS `p`"
00480                 . " LEFT JOIN `{$refTableName}` AS `r`"
00481                 . " ON `p`.`{$columnName}` = `r`.`{$refColumnName}`"
00482                 . " WHERE `r`.`{$refColumnName}` IS NULL";
00483             $this->raw_query($sql);
00484         }
00485         elseif (strtoupper($onDelete) == 'SET NULL') {
00486             $sql = "UPDATE `{$tableName}` AS `p`"
00487                 . " LEFT JOIN `{$refTableName}` AS `r`"
00488                 . " ON `p`.`{$columnName}` = `r`.`{$refColumnName}`"
00489                 . " SET `p`.`{$columnName}`=NULL"
00490                 . " WHERE `r`.`{$refColumnName}` IS NULL";
00491             $this->raw_query($sql);
00492         }
00493 
00494         return $this;
00495     }

query	(	$	sql,
		$	bind = `array()`
	)

Special handling for PDO query(). All bind parameter names must begin with ':'

Parameters:

	string\|Zend_Db_Select	$sql The SQL statement with placeholders.
	array	$bind An array of data to bind to the placeholders.

Returns:: Zend_Db_Pdo_Statement

Exceptions:

Zend_Db_Adapter_Exception

To re-throw PDOException.

Definition at line 268 of file Mysql.php.

00269     {
00270         $this->_debugTimer();
00271         try {
00272             $sql = (string)$sql;
00273             if (strpos($sql, ':') !== false || strpos($sql, '?') !== false) {
00274                 $this->_bindParams = $bind;
00275                 $sql = preg_replace_callback('#(([\'"])((\\2)|((.*?[^\\\\])\\2)))#', array($this, 'proccessBindCallback'), $sql);
00276                 $bind = $this->_bindParams;
00277             }
00278 
00279 
00280 
00281             $result = parent::query($sql, $bind);
00282         }
00283         catch (Exception $e) {
00284             $this->_debugStat(self::DEBUG_QUERY, $sql, $bind);
00285             $this->_debugException($e);
00286         }
00287         $this->_debugStat(self::DEBUG_QUERY, $sql, $bind, $result);
00288         return $result;
00289     }

quoteInto	(	$	text,
		$	value,
		$	type = `null`,
		$	count = `null`
	)

Quotes a value and places into a piece of text at a placeholder.

Method revrited for handle empty arrays in value param

Parameters:

	string	$text The text with a placeholder.
	mixed	$value The value to quote.
	string	$type OPTIONAL SQL datatype
	integer	$count OPTIONAL count of placeholders to replace

Returns:: string An SQL-safe quoted value placed into the orignal text.

Definition at line 1083 of file Mysql.php.

01084     {
01085         if (is_array($value) && empty($value)) {
01086             $value = new Zend_Db_Expr('NULL');
01087         }
01088         return parent::quoteInto($text, $value, $type, $count);
01089     }

raw_fetchRow	(	$	sql,
		$	field = `null`
	)

Definition at line 244 of file Mysql.php.

00245     {
00246         if (!$result = $this->raw_query($sql)) {
00247             return false;
00248         }
00249         if (!$row = $result->fetch(PDO::FETCH_ASSOC)) {
00250             return false;
00251         }
00252         if (empty($field)) {
00253             return $row;
00254         } else {
00255             return isset($row[$field]) ? $row[$field] : false;
00256         }
00257     }

raw_query ( $ sql )

Definition at line 224 of file Mysql.php.

00225     {
00226         do {
00227             $retry = false;
00228             $tries = 0;
00229             try {
00230                 $result = $this->getConnection()->query($sql);
00231             } catch (PDOException $e) {
00232                 if ($e->getMessage()=='SQLSTATE[HY000]: General error: 2013 Lost connection to MySQL server during query') {
00233                     $retry = true;
00234                 } else {
00235                     throw $e;
00236                 }
00237                 $tries++;
00238             }
00239         } while ($retry && $tries<10);
00240 
00241         return $result;
00242     }

resetDdlCache	(	$	tableName = `null`,
		$	schemaName = `null`
	)

Reset table DDL

Parameters:

	string	$tableName
	string	$schemaName OPTIONAL

Returns:: Varien_Db_Adapter_Pdo_Mysql

Definition at line 1109 of file Mysql.php.

01110     {
01111         if (is_null($tableName)) {
01112             $this->_ddlCache = array();
01113         }
01114         else {
01115             $cacheKey = $this->_getTableName($tableName, $schemaName);
01116 
01117             unset($this->_ddlCache[self::DDL_DESCRIBE][$cacheKey]);
01118             unset($this->_ddlCache[self::DDL_CREATE][$cacheKey]);
01119             unset($this->_ddlCache[self::DDL_INDEX][$cacheKey]);
01120             unset($this->_ddlCache[self::DDL_FOREIGN_KEY][$cacheKey]);
01121         }
01122 
01123         return $this;
01124     }

rollback ( )

Rollback DB transaction

Returns:: Varien_Db_Adapter_Pdo_Mysql

Definition at line 151 of file Mysql.php.

00152     {
00153         if ($this->_transactionLevel===1) {
00154             $this->_debugTimer();
00155             parent::rollback();
00156             $this->_debugStat(self::DEBUG_TRANSACTION, 'ROLLBACK');
00157         }
00158         $this->_transactionLevel--;
00159         return $this;
00160     }

select ( )

Creates and returns a new Zend_Db_Select object for this adapter.

Returns:: Varien_Db_Select

Definition at line 969 of file Mysql.php.

00970     {
00971         return new Varien_Db_Select($this);
00972     }

showTableStatus ( $ tableName )

Show table status

Parameters:

string

$tableName

Returns:: array

Definition at line 678 of file Mysql.php.

00679     {
00680         $sql = $this->quoteInto('SHOW TABLE STATUS LIKE ?', $tableName);
00681         return $this->raw_fetchRow($sql);
00682     }

tableColumnExists	(	$	tableName,
		$	columnName,
		$	schemaName = `null`
	)

Check table column exist

Parameters:

	string	$tableName
	string	$columnName
	string	$schemaName

Returns:: bool

Definition at line 544 of file Mysql.php.

00545     {
00546         $describe = $this->describeTable($tableName, $schemaName);
00547         foreach ($describe as $column) {
00548             if ($column['COLUMN_NAME'] == $columnName) {
00549                 return true;
00550             }
00551         }
00552         return false;
00553     }

truncate	(	$	tableName,
		$	schemaName = `null`
	)

Truncate table

Parameters:

	string	$tableName
	string	$schemaName

Returns:: Varien_Db_Adapter_Pdo_Mysql

Definition at line 1172 of file Mysql.php.

01173     {
01174         $tableName = $this->_getTableName($tableName, $schemaName);
01175         $sql = sprintf('TRUNCATE %s', $this->quoteIdentifier($tableName));
01176         $this->raw_query($sql);
01177 
01178         return $this;
01179     }

Member Data Documentation

$_bindIncrement = 0 [protected]

Definition at line 77 of file Mysql.php.

$_bindParams = array() [protected]

Definition at line 70 of file Mysql.php.

$_connectionFlagsSet = false [protected]

Definition at line 56 of file Mysql.php.

$_ddlCache = array() [protected]

Definition at line 63 of file Mysql.php.

$_debug = false [protected]

Definition at line 84 of file Mysql.php.

$_debugFile = 'var/debug/sql.txt' [protected]

Definition at line 98 of file Mysql.php.

$_debugIoAdapter [protected]

Definition at line 105 of file Mysql.php.

$_debugTimer = 0 [protected]

Definition at line 112 of file Mysql.php.

$_logQueryTime = 0.05 [protected]

Definition at line 91 of file Mysql.php.

$_transactionLevel = 0 [protected]

Definition at line 49 of file Mysql.php.

const DDL_CREATE = 2

Definition at line 40 of file Mysql.php.

const DDL_DESCRIBE = 1

Definition at line 39 of file Mysql.php.

const DDL_FOREIGN_KEY = 4

Definition at line 42 of file Mysql.php.

const DDL_INDEX = 3

Definition at line 41 of file Mysql.php.

const DEBUG_CONNECT = 0

Definition at line 32 of file Mysql.php.

const DEBUG_QUERY = 2

Definition at line 34 of file Mysql.php.

const DEBUG_TRANSACTION = 1

Definition at line 33 of file Mysql.php.

const ISO_DATE_FORMAT = 'yyyy-MM-dd'

Definition at line 36 of file Mysql.php.

const ISO_DATETIME_FORMAT = 'yyyy-MM-dd HH-mm-ss'

Definition at line 37 of file Mysql.php.

The documentation for this class was generated from the following file:

lib/Varien/Db/Adapter/Pdo/Mysql.php


Public Member Functions
	beginTransaction ()
	commit ()
	rollback ()
	convertDate ($date)
	convertDateTime ($datetime)
	raw_query ($sql)
	raw_fetchRow ($sql, $field=null)
	query ($sql, $bind=array())
	proccessBindCallback ($matches)
	multi_query ($sql)
	dropForeignKey ($tableName, $foreignKey, $schemaName=null)
	dropKey ($tableName, $keyName, $shemaName=null)
	purgeOrphanRecords ($tableName, $columnName, $refTableName, $refColumnName, $onDelete= 'cascade')
	addConstraint ($fkName, $tableName, $columnName, $refTableName, $refColumnName, $onDelete= 'cascade', $onUpdate= 'cascade', $purge=false)
	tableColumnExists ($tableName, $columnName, $schemaName=null)
	addColumn ($tableName, $columnName, $definition)
	dropColumn ($tableName, $columnName, $shemaName=null)
	changeColumn ($tableName, $oldColumnName, $newColumnName, $definition, $showStatus=false)
	modifyColumn ($tableName, $columnName, $definition, $showStatus=false)
	showTableStatus ($tableName)
	getKeyList ($tableName, $schemaName=null)
	getCreateTable ($tableName, $schemaName=null)
	getForeignKeys ($tableName, $schemaName=null)
	getIndexList ($tableName, $schemaName=null)
	addKey ($tableName, $indexName, $fields, $indexType= 'index')
	select ()
	quoteInto ($text, $value, $type=null, $count=null)
	resetDdlCache ($tableName=null, $schemaName=null)
	describeTable ($tableName, $schemaName=null)
	truncate ($tableName, $schemaName=null)
	changeTableEngine ($tableName, $engine, $schemaName=null)
Public Attributes
const	DEBUG_CONNECT = 0
const	DEBUG_TRANSACTION = 1
const	DEBUG_QUERY = 2
const	ISO_DATE_FORMAT = 'yyyy-MM-dd'
const	ISO_DATETIME_FORMAT = 'yyyy-MM-dd HH-mm-ss'
const	DDL_DESCRIBE = 1
const	DDL_CREATE = 2
const	DDL_INDEX = 3
const	DDL_FOREIGN_KEY = 4
Protected Member Functions
	_connect ()
	_unQuote ($string)
	_splitMultiQuery ($sql)
	_removeDuplicateEntry ($table, $fields, $ids)
	_debugTimer ()
	_debugStat ($type, $sql, $bind=array(), $result=null)
	_debugException (Exception $e)
	_debugWriteToFile ($str)
	_getTableName ($tableName, $schemaName=null)
Protected Attributes
	$_transactionLevel = 0
	$_connectionFlagsSet = false
	$_ddlCache = array()
	$_bindParams = array()
	$_bindIncrement = 0
	$_debug = false
	$_logQueryTime = 0.05
	$_debugFile = 'var/debug/sql.txt'
	$_debugIoAdapter
	$_debugTimer = 0