/wp-includes/library/Varien/Db/Adapter/Pdo/Mysql.php
PHP | 3586 lines | 3339 code | 47 blank | 200 comment | 34 complexity | 49cec4cf8aa903eb9c4cff6bb26d7934 MD5 | raw file
- <?php
- /**
- * Magento
- *
- * NOTICE OF LICENSE
- *
- * This source file is subject to the Open Software License (OSL 3.0)
- * that is bundled with this package in the file LICENSE.txt.
- * It is also available through the world-wide-web at this URL:
- * http://opensource.org/licenses/osl-3.0.php
- * If you did not receive a copy of the license and are unable to
- * obtain it through the world-wide-web, please send an email
- * to license@magentocommerce.com so we can send you a copy immediately.
- *
- * DISCLAIMER
- *
- * Do not edit or add to this file if you wish to upgrade Magento to newer
- * versions in the future. If you wish to customize Magento for your
- * needs please refer to http://www.magentocommerce.com for more information.
- *
- * @category Varien
- * @package Varien_Db
- * @copyright Copyright (c) 2008 Irubin Consulting Inc. DBA Varien (http://www.varien.com)
- * @license http://opensource.org/licenses/osl-3.0.php Open Software License (OSL 3.0)
- */
- /**
- * Mysql PDO DB adapter
- */
- class Varien_Db_Adapter_Pdo_Mysql extends Zend_Db_Adapter_Pdo_Mysql implements Varien_Db_Adapter_Interface
- {
- const DEBUG_CONNECT = 0;
- const DEBUG_TRANSACTION = 1;
- const DEBUG_QUERY = 2;
- const TIMESTAMP_FORMAT = 'Y-m-d H:i:s';
- const DATETIME_FORMAT = 'Y-m-d H:i:s';
- const DATE_FORMAT = 'Y-m-d';
- const DDL_DESCRIBE = 1;
- const DDL_CREATE = 2;
- const DDL_INDEX = 3;
- const DDL_FOREIGN_KEY = 4;
- const DDL_CACHE_PREFIX = 'DB_PDO_MYSQL_DDL';
- const DDL_CACHE_TAG = 'DB_PDO_MYSQL_DDL';
- const LENGTH_TABLE_NAME = 64;
- const LENGTH_INDEX_NAME = 64;
- const LENGTH_FOREIGN_NAME = 64;
- /**
- * Default class name for a DB statement.
- *
- * @var string
- */
- protected $_defaultStmtClass = 'Varien_Db_Statement_Pdo_Mysql';
- /**
- * Current Transaction Level
- *
- * @var int
- */
- protected $_transactionLevel = 0;
- /**
- * Set attribute to connection flag
- *
- * @var bool
- */
- protected $_connectionFlagsSet = false;
- /**
- * Tables DDL cache
- *
- * @var array
- */
- protected $_ddlCache = array();
- /**
- * SQL bind params. Used temporarily by regexp callback.
- *
- * @var array
- */
- protected $_bindParams = array();
- /**
- * Autoincrement for bind value. Used by regexp callback.
- *
- * @var int
- */
- protected $_bindIncrement = 0;
- /**
- * Write SQL debug data to file
- *
- * @var bool
- */
- protected $_debug = false;
- /**
- * Minimum query duration time to be logged
- *
- * @var float
- */
- protected $_logQueryTime = 0.05;
- /**
- * Log all queries (ignored minimum query duration time)
- *
- * @var bool
- */
- protected $_logAllQueries = false;
- /**
- * Add to log call stack data (backtrace)
- *
- * @var bool
- */
- protected $_logCallStack = false;
- /**
- * Path to SQL debug data log
- *
- * @var string
- */
- protected $_debugFile = 'var/debug/pdo_mysql.log';
- /**
- * Io File Adapter
- *
- * @var Varien_Io_File
- */
- protected $_debugIoAdapter;
- /**
- * Debug timer start value
- *
- * @var float
- */
- protected $_debugTimer = 0;
- /**
- * Cache frontend adapter instance
- *
- * @var Zend_Cache_Core
- */
- protected $_cacheAdapter;
- /**
- * DDL cache allowing flag
- * @var bool
- */
- protected $_isDdlCacheAllowed = true;
- /**
- * MySQL column - Table DDL type pairs
- *
- * @var array
- */
- protected $_ddlColumnTypes = array(
- Varien_Db_Ddl_Table::TYPE_BOOLEAN => 'bool',
- Varien_Db_Ddl_Table::TYPE_SMALLINT => 'smallint',
- Varien_Db_Ddl_Table::TYPE_INTEGER => 'int',
- Varien_Db_Ddl_Table::TYPE_BIGINT => 'bigint',
- Varien_Db_Ddl_Table::TYPE_FLOAT => 'float',
- Varien_Db_Ddl_Table::TYPE_DECIMAL => 'decimal',
- Varien_Db_Ddl_Table::TYPE_NUMERIC => 'decimal',
- Varien_Db_Ddl_Table::TYPE_DATE => 'date',
- Varien_Db_Ddl_Table::TYPE_TIMESTAMP => 'timestamp',
- Varien_Db_Ddl_Table::TYPE_DATETIME => 'datetime',
- Varien_Db_Ddl_Table::TYPE_TEXT => 'text',
- Varien_Db_Ddl_Table::TYPE_BLOB => 'blob',
- Varien_Db_Ddl_Table::TYPE_VARBINARY => 'blob'
- );
- /**
- * Allowed interval units array
- *
- * @var array
- */
- protected $_intervalUnits = array(
- self::INTERVAL_YEAR => 'YEAR',
- self::INTERVAL_MONTH => 'MONTH',
- self::INTERVAL_DAY => 'DAY',
- self::INTERVAL_HOUR => 'HOUR',
- self::INTERVAL_MINUTE => 'MINUTE',
- self::INTERVAL_SECOND => 'SECOND',
- );
- /**
- * Hook callback to modify queries. Mysql specific property, designed only for backwards compatibility.
- *
- * @var array|null
- */
- protected $_queryHook = null;
- /**
- * Begin new DB transaction for connection
- *
- * @return Varien_Db_Adapter_Pdo_Mysql
- */
- public function beginTransaction()
- {
- if ($this->_transactionLevel === 0) {
- $this->_debugTimer();
- parent::beginTransaction();
- $this->_debugStat(self::DEBUG_TRANSACTION, 'BEGIN');
- }
- ++$this->_transactionLevel;
- return $this;
- }
- /**
- * Commit DB transaction
- *
- * @return Varien_Db_Adapter_Pdo_Mysql
- */
- public function commit()
- {
- if ($this->_transactionLevel === 1) {
- $this->_debugTimer();
- parent::commit();
- $this->_debugStat(self::DEBUG_TRANSACTION, 'COMMIT');
- }
- --$this->_transactionLevel;
- return $this;
- }
- /**
- * Rollback DB transaction
- *
- * @return Varien_Db_Adapter_Pdo_Mysql
- */
- public function rollback()
- {
- if ($this->_transactionLevel === 1) {
- $this->_debugTimer();
- parent::rollback();
- $this->_debugStat(self::DEBUG_TRANSACTION, 'ROLLBACK');
- }
- --$this->_transactionLevel;
- return $this;
- }
- /**
- * Get adapter transaction level state. Return 0 if all transactions are complete
- *
- * @return int
- */
- public function getTransactionLevel()
- {
- return $this->_transactionLevel;
- }
- /**
- * Convert date to DB format
- *
- * @param mixed $date
- * @return string
- */
- public function convertDate($date)
- {
- return $this->formatDate($date, false);
- }
- /**
- * Convert date and time to DB format
- *
- * @param mixed $date
- * @return string
- */
- public function convertDateTime($datetime)
- {
- return $this->formatDate($datetime, true);
- }
- /**
- * Creates a PDO object and connects to the database.
- *
- * @throws Zend_Db_Adapter_Exception
- */
- protected function _connect()
- {
- if ($this->_connection) {
- return;
- }
- if (!extension_loaded('pdo_mysql')) {
- throw new Zend_Db_Adapter_Exception('pdo_mysql extension is not installed');
- }
- if (strpos($this->_config['host'], '/') !== false) {
- $this->_config['unix_socket'] = $this->_config['host'];
- unset($this->_config['host']);
- } else if (strpos($this->_config['host'], ':') !== false) {
- list($this->_config['host'], $this->_config['port']) = explode(':', $this->_config['host']);
- }
- $this->_debugTimer();
- parent::_connect();
- $this->_debugStat(self::DEBUG_CONNECT, '');
- /** @link http://bugs.mysql.com/bug.php?id=18551 */
- $this->_connection->query("SET SQL_MODE=''");
- if (!$this->_connectionFlagsSet) {
- $this->_connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
- $this->_connection->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
- $this->_connectionFlagsSet = true;
- }
- }
- /**
- * Run RAW Query
- *
- * @param string $sql
- * @return Zend_Db_Statement_Interface
- * @throws PDOException
- */
- public function raw_query($sql)
- {
- $lostConnectionMessage = 'SQLSTATE[HY000]: General error: 2013 Lost connection to MySQL server during query';
- $tries = 0;
- do {
- $retry = false;
- try {
- $result = $this->query($sql);
- } catch (Exception $e) {
- // Convert to PDOException to maintain backwards compatibility with usage of MySQL adapter
- if ($e instanceof Zend_Db_Statement_Exception) {
- $e = $e->getPrevious();
- if (!($e instanceof PDOException)) {
- $e = new PDOException($e->getMessage(), $e->getCode());
- }
- }
- // Check to reconnect
- if ($tries < 10 && $e->getMessage() == $lostConnectionMessage) {
- $retry = true;
- $tries++;
- } else {
- throw $e;
- }
- }
- } while ($retry);
- return $result;
- }
- /**
- * Run RAW query and Fetch First row
- *
- * @param string $sql
- * @param string|int $field
- * @return boolean
- */
- public function raw_fetchRow($sql, $field = null)
- {
- $result = $this->raw_query($sql);
- if (!$result) {
- return false;
- }
- $row = $result->fetch(PDO::FETCH_ASSOC);
- if (!$row) {
- return false;
- }
- if (empty($field)) {
- return $row;
- } else {
- return isset($row[$field]) ? $row[$field] : false;
- }
- }
- /**
- * Special handling for PDO query().
- * All bind parameter names must begin with ':'.
- *
- * @param string|Zend_Db_Select $sql The SQL statement with placeholders.
- * @param mixed $bind An array of data or data itself to bind to the placeholders.
- * @return Zend_Db_Pdo_Statement
- * @throws Zend_Db_Adapter_Exception To re-throw PDOException.
- */
- public function query($sql, $bind = array())
- {
- $this->_debugTimer();
- try {
- $this->_prepareQuery($sql, $bind);
- $result = parent::query($sql, $bind);
- } catch (Exception $e) {
- $this->_debugStat(self::DEBUG_QUERY, $sql, $bind);
- $this->_debugException($e);
- }
- $this->_debugStat(self::DEBUG_QUERY, $sql, $bind, $result);
- return $result;
- }
- /**
- * Prepares SQL query by moving to bind all special parameters that can be confused with bind placeholders
- * (e.g. "foo:bar"). And also changes named bind to positional one, because underlying library has problems
- * with named binds.
- *
- * @param Zend_Db_Select|string $sql
- * @param mixed $bind
- * @return Varien_Db_Adapter_Pdo_Mysql
- */
- protected function _prepareQuery(&$sql, &$bind = array())
- {
- $sql = (string) $sql;
- if (!is_array($bind)) {
- $bind = array($bind);
- }
- // Mixed bind is not supported - so remember whether it is named bind, to normalize later if required
- $isNamedBind = false;
- if ($bind) {
- foreach ($bind as $k => $v) {
- if (!is_int($k)) {
- $isNamedBind = true;
- if ($k[0] != ':') {
- $bind[":{$k}"] = $v;
- unset($bind[$k]);
- }
- }
- }
- }
- if (strpos($sql, ':') !== false || strpos($sql, '?') !== false) {
- $before = count($bind);
- $this->_bindParams = $bind; // Used by callback
- $sql = preg_replace_callback('#(([\'"])((\\2)|((.*?[^\\\\])\\2)))#',
- array($this, 'proccessBindCallback'),
- $sql);
- Varien_Exception::processPcreError();
- $bind = $this->_bindParams;
- // If _processBindCallbacks() has added named entries to positional bind - normalize it to positional
- if (!$isNamedBind && $before && (count($bind) != $before)) {
- $this->_convertMixedBind($sql, $bind);
- }
- }
- // Special query hook
- if ($this->_queryHook) {
- $object = $this->_queryHook['object'];
- $method = $this->_queryHook['method'];
- $object->$method($sql, $bind);
- }
- return $this;
- }
- /**
- * Callback function for preparation of query and bind by regexp.
- * Checks query parameters for special symbols and moves such parameters to bind array as named ones.
- * This method writes to $_bindParams, where query bind parameters are kept.
- * This method requires further normalizing, if bind array is positional.
- *
- * @param array $matches
- * @return string
- */
- public function proccessBindCallback($matches)
- {
- if (isset($matches[6]) && (
- strpos($matches[6], "'") !== false ||
- strpos($matches[6], ':') !== false ||
- strpos($matches[6], '?') !== false)
- ) {
- $bindName = ':_mage_bind_var_' . (++$this->_bindIncrement);
- $this->_bindParams[$bindName] = $this->_unQuote($matches[6]);
- return ' ' . $bindName;
- }
- return $matches[0];
- }
- /**
- * Unquote raw string (use for auto-bind)
- *
- * @param string $string
- * @return string
- */
- protected function _unQuote($string)
- {
- $translate = array(
- "\\000" => "\000",
- "\\n" => "\n",
- "\\r" => "\r",
- "\\\\" => "\\",
- "\'" => "'",
- "\\\"" => "\"",
- "\\032" => "\032"
- );
- return strtr($string, $translate);
- }
- /**
- * Normalizes mixed positional-named bind to positional bind, and replaces named placeholders in query to
- * '?' placeholders.
- *
- * @param string $sql
- * @param array $bind
- * @return Varien_Db_Adapter_Pdo_Mysql
- */
- protected function _convertMixedBind(&$sql, &$bind)
- {
- $positions = array();
- $offset = 0;
- // get positions
- while (true) {
- $pos = strpos($sql, '?', $offset);
- if ($pos !== false) {
- $positions[] = $pos;
- $offset = ++$pos;
- } else {
- break;
- }
- }
- $bindResult = array();
- $map = array();
- foreach ($bind as $k => $v) {
- // positional
- if (is_int($k)) {
- if (!isset($positions[$k])) {
- continue;
- }
- $bindResult[$positions[$k]] = $v;
- } else {
- $offset = 0;
- while (true) {
- $pos = strpos($sql, $k, $offset);
- if ($pos === false) {
- break;
- } else {
- $offset = $pos + strlen($k);
- $bindResult[$pos] = $v;
- }
- }
- $map[$k] = '?';
- }
- }
- ksort($bindResult);
- $bind = array_values($bindResult);
- $sql = strtr($sql, $map);
- return $this;
- }
- /**
- * Sets (removes) query hook.
- *
- * $hook must be either array with 'object' and 'method' entries, or null to remove hook.
- * Previous hook is returned.
- *
- * @param array $hook
- * @return mixed
- */
- public function setQueryHook($hook)
- {
- $prev = $this->_queryHook;
- $this->_queryHook = $hook;
- return $prev;
- }
- /**
- * Executes a SQL statement(s)
- *
- * @param string $sql
- * @throws Zend_Db_Exception
- * @return Varien_Db_Adapter_Pdo_Mysql
- */
- public function multiQuery($sql)
- {
- return $this->multi_query($sql);
- }
- /**
- * Run Multi Query
- *
- * @param string $sql
- * @return array
- */
- public function multi_query($sql)
- {
- ##$result = $this->raw_query($sql);
- #$this->beginTransaction();
- try {
- $stmts = $this->_splitMultiQuery($sql);
- $result = array();
- foreach ($stmts as $stmt) {
- $result[] = $this->raw_query($stmt);
- }
- #$this->commit();
- } catch (Exception $e) {
- #$this->rollback();
- throw $e;
- }
- $this->resetDdlCache();
- return $result;
- }
- /**
- * Split multi statement query
- *
- * @param $sql string
- * @return array
- */
- protected function _splitMultiQuery($sql)
- {
- $parts = preg_split('#(;|\'|"|\\\\|//|--|\n|/\*|\*/)#', $sql, null, PREG_SPLIT_NO_EMPTY | PREG_SPLIT_DELIM_CAPTURE);
- $q = false;
- $c = false;
- $stmts = array();
- $s = '';
- foreach ($parts as $i => $part) {
- // strings
- if (($part === "'" || $part === '"') && ($i === 0 || $parts[$i-1] !== '\\')) {
- if ($q === false) {
- $q = $part;
- } elseif ($q === $part) {
- $q = false;
- }
- }
- // single line comments
- if (($part === '//' || $part === '--') && ($i === 0 || $parts[$i-1] === "\n")) {
- $c = $part;
- } elseif ($part === "\n" && ($c === '//' || $c === '--')) {
- $c = false;
- }
- // multi line comments
- if ($part === '/*' && $c === false) {
- $c = '/*';
- } elseif ($part === '*/' && $c === '/*') {
- $c = false;
- }
- // statements
- if ($part === ';' && $q === false && $c === false) {
- if (trim($s)!=='') {
- $stmts[] = trim($s);
- $s = '';
- }
- } else {
- $s .= $part;
- }
- }
- if (trim($s) !== '') {
- $stmts[] = trim($s);
- }
- return $stmts;
- }
- /**
- * Drop the Foreign Key from table
- *
- * @param string $tableName
- * @param string $fkName
- * @param string $schemaName
- * @return Varien_Db_Adapter_Pdo_Mysql
- */
- public function dropForeignKey($tableName, $fkName, $schemaName = null)
- {
- $foreignKeys = $this->getForeignKeys($tableName, $schemaName);
- $fkName = strtoupper($fkName);
- if (substr($fkName, 0, 3) == 'FK_') {
- $fkName = substr($fkName, 3);
- }
- foreach (array($fkName, 'FK_' . $fkName) as $key) {
- if (isset($foreignKeys[$key])) {
- $sql = sprintf('ALTER TABLE %s DROP FOREIGN KEY %s',
- $this->quoteIdentifier($this->_getTableName($tableName, $schemaName)),
- $this->quoteIdentifier($foreignKeys[$key]['FK_NAME'])
- );
- $this->resetDdlCache($tableName, $schemaName);
- $this->raw_query($sql);
- }
- }
- return $this;
- }
- /**
- * Delete index from a table if it exists
- *
- * @deprecated since 1.4.0.1
- * @param string $tableName
- * @param string $keyName
- * @param string $schemaName
- * @return bool|Zend_Db_Statement_Interface
- */
- public function dropKey($tableName, $keyName, $schemaName = null)
- {
- return $this->dropIndex($tableName, $keyName, $schemaName);
- }
- /**
- * Prepare table before add constraint foreign key
- *
- * @param string $tableName
- * @param string $columnName
- * @param string $refTableName
- * @param string $refColumnName
- * @param string $onDelete
- * @return Varien_Db_Adapter_Pdo_Mysql
- */
- public function purgeOrphanRecords($tableName, $columnName, $refTableName, $refColumnName,
- $onDelete = Varien_Db_Adapter_Interface::FK_ACTION_CASCADE)
- {
- $onDelete = strtoupper($onDelete);
- if ($onDelete == Varien_Db_Adapter_Interface::FK_ACTION_CASCADE
- || $onDelete == Varien_Db_Adapter_Interface::FK_ACTION_RESTRICT
- ) {
- $sql = sprintf("DELETE p.* FROM %s AS p LEFT JOIN %s AS r ON p.%s = r.%s WHERE r.%s IS NULL",
- $this->quoteIdentifier($tableName),
- $this->quoteIdentifier($refTableName),
- $this->quoteIdentifier($columnName),
- $this->quoteIdentifier($refColumnName),
- $this->quoteIdentifier($refColumnName));
- $this->raw_query($sql);
- } elseif ($onDelete == Varien_Db_Adapter_Interface::FK_ACTION_SET_NULL) {
- $sql = sprintf("UPDATE %s AS p LEFT JOIN %s AS r ON p.%s = r.%s SET p.%s = NULL WHERE r.%s IS NULL",
- $this->quoteIdentifier($tableName),
- $this->quoteIdentifier($refTableName),
- $this->quoteIdentifier($columnName),
- $this->quoteIdentifier($refColumnName),
- $this->quoteIdentifier($columnName),
- $this->quoteIdentifier($refColumnName));
- $this->raw_query($sql);
- }
- return $this;
- }
- /**
- * Add foreign key to table. If FK with same name exist - it will be deleted
- *
- * @deprecated since 1.4.0.1
- * @param string $fkName foreign key name
- * @param string $tableName main table name
- * @param string $keyName main table field name
- * @param string $refTableName refered table name
- * @param string $refKeyName refered table field name
- * @param string $onUpdate on update statement
- * @param string $onDelete on delete statement
- * @param bool $purge
- * @return mixed
- */
- public function addConstraint($fkName, $tableName, $columnName,
- $refTableName, $refColumnName, $onDelete = Varien_Db_Adapter_Interface::FK_ACTION_CASCADE,
- $onUpdate = Varien_Db_Adapter_Interface::FK_ACTION_CASCADE, $purge = false)
- {
- return $this->addForeignKey($fkName, $tableName, $columnName, $refTableName, $refColumnName,
- $onDelete, $onUpdate, $purge);
- }
- /**
- * Check does table column exist
- *
- * @param string $tableName
- * @param string $columnName
- * @param string $schemaName
- * @return boolean
- */
- public function tableColumnExists($tableName, $columnName, $schemaName = null)
- {
- $describe = $this->describeTable($tableName, $schemaName);
- foreach ($describe as $column) {
- if ($column['COLUMN_NAME'] == $columnName) {
- return true;
- }
- }
- return false;
- }
- /**
- * Adds new column to table.
- *
- * Generally $defintion must be array with column data to keep this call cross-DB compatible.
- * Using string as $definition is allowed only for concrete DB adapter.
- * Adds primary key if needed
- *
- * @param string $tableName
- * @param string $columnName
- * @param array|string $definition string specific or universal array DB Server definition
- * @param string $schemaName
- * @return int|boolean
- * @throws Zend_Db_Exception
- */
- public function addColumn($tableName, $columnName, $definition, $schemaName = null)
- {
- if ($this->tableColumnExists($tableName, $columnName, $schemaName)) {
- return true;
- }
- $primaryKey = '';
- if (is_array($definition)) {
- $definition = array_change_key_case($definition, CASE_UPPER);
- if (empty($definition['COMMENT'])) {
- throw new Zend_Db_Exception("Impossible to create a column without comment.");
- }
- if (!empty($definition['PRIMARY'])) {
- $primaryKey = sprintf(', ADD PRIMARY KEY (%s)', $this->quoteIdentifier($columnName));
- }
- $definition = $this->_getColumnDefinition($definition);
- }
- $sql = sprintf('ALTER TABLE %s ADD COLUMN %s %s %s',
- $this->quoteIdentifier($this->_getTableName($tableName, $schemaName)),
- $this->quoteIdentifier($columnName),
- $definition,
- $primaryKey
- );
- $result = $this->raw_query($sql);
- $this->resetDdlCache($tableName, $schemaName);
- return $result;
- }
- /**
- * Delete table column
- *
- * @param string $tableName
- * @param string $columnName
- * @param string $schemaName
- * @return bool
- */
- public function dropColumn($tableName, $columnName, $schemaName = null)
- {
- if (!$this->tableColumnExists($tableName, $columnName, $schemaName)) {
- return true;
- }
- $alterDrop = array();
- $foreignKeys = $this->getForeignKeys($tableName, $schemaName);
- foreach ($foreignKeys as $fkProp) {
- if ($fkProp['COLUMN_NAME'] == $columnName) {
- $alterDrop[] = 'DROP FOREIGN KEY ' . $this->quoteIdentifier($fkProp['FK_NAME']);
- }
- }
- $alterDrop[] = 'DROP COLUMN ' . $this->quoteIdentifier($columnName);
- $sql = sprintf('ALTER TABLE %s %s',
- $this->quoteIdentifier($this->_getTableName($tableName, $schemaName)),
- implode(', ', $alterDrop));
- $result = $this->raw_query($sql);
- $this->resetDdlCache($tableName, $schemaName);
- return $result;
- }
- /**
- * Change the column name and definition
- *
- * For change definition of column - use modifyColumn
- *
- * @param string $tableName
- * @param string $oldColumnName
- * @param string $newColumnName
- * @param array $definition
- * @param boolean $flushData flush table statistic
- * @param string $schemaName
- * @return Varien_Db_Adapter_Pdo_Mysql
- * @throws Zend_Db_Exception
- */
- public function changeColumn($tableName, $oldColumnName, $newColumnName, $definition, $flushData = false,
- $schemaName = null)
- {
- if (!$this->tableColumnExists($tableName, $oldColumnName, $schemaName)) {
- throw new Zend_Db_Exception(sprintf('Column "%s" does not exists on table "%s"', $oldColumnName, $tableName));
- }
- if (is_array($definition)) {
- $definition = $this->_getColumnDefinition($definition);
- }
- $sql = sprintf('ALTER TABLE %s CHANGE COLUMN %s %s %s',
- $this->quoteIdentifier($tableName),
- $this->quoteIdentifier($oldColumnName),
- $this->quoteIdentifier($newColumnName),
- $definition);
- $result = $this->raw_query($sql);
- if ($flushData) {
- $this->showTableStatus($tableName, $schemaName);
- }
- $this->resetDdlCache($tableName, $schemaName);
- return $result;
- }
- /**
- * Modify the column definition
- *
- * @param string $tableName
- * @param string $columnName
- * @param array|string $definition
- * @param boolean $flushData
- * @param string $schemaName
- * @return Varien_Db_Adapter_Pdo_Mysql
- * @throws Zend_Db_Exception
- */
- public function modifyColumn($tableName, $columnName, $definition, $flushData = false, $schemaName = null)
- {
- if (!$this->tableColumnExists($tableName, $columnName, $schemaName)) {
- throw new Zend_Db_Exception(sprintf('Column "%s" does not exists on table "%s"', $columnName, $tableName));
- }
- if (is_array($definition)) {
- $definition = $this->_getColumnDefinition($definition);
- }
- $sql = sprintf('ALTER TABLE %s MODIFY COLUMN %s %s',
- $this->quoteIdentifier($tableName),
- $this->quoteIdentifier($columnName),
- $definition);
- $this->raw_query($sql);
- if ($flushData) {
- $this->showTableStatus($tableName, $schemaName);
- }
- $this->resetDdlCache($tableName, $schemaName);
- return $this;
- }
- /**
- * Show table status
- *
- * @param string $tableName
- * @param string $schemaName
- * @return array|false
- */
- public function showTableStatus($tableName, $schemaName = null)
- {
- $fromDbName = null;
- if ($schemaName !== null) {
- $fromDbName = ' FROM ' . $this->quoteIdentifier($schemaName);
- }
- $query = sprintf('SHOW TABLE STATUS%s LIKE %s', $fromDbName, $this->quote($tableName));
- return $this->raw_fetchRow($query);
- }
- /**
- * Retrieve table index key list
- *
- * @deprecated use getIndexList(
- * @param string $tableName
- * @param string $schemaName
- * @return array
- */
- public function getKeyList($tableName, $schemaName = null)
- {
- $keyList = array();
- $indexList = $this->getIndexList($tableName, $schemaName);
- foreach ($indexList as $indexProp) {
- $keyList[$indexProp['KEY_NAME']] = $indexProp['COLUMNS_LIST'];
- }
- return $keyList;
- }
- /**
- * Retrieve Create Table SQL
- *
- * @param string $tableName
- * @param string $schemaName
- * @return string
- */
- public function getCreateTable($tableName, $schemaName = null)
- {
- $cacheKey = $this->_getTableName($tableName, $schemaName);
- $ddl = $this->loadDdlCache($cacheKey, self::DDL_CREATE);
- if ($ddl === false) {
- $sql = 'SHOW CREATE TABLE ' . $this->quoteIdentifier($tableName);
- $ddl = $this->raw_fetchRow($sql, 'Create Table');
- $this->saveDdlCache($cacheKey, self::DDL_CREATE, $ddl);
- }
- return $ddl;
- }
- /**
- * 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
- *
- * @param string $tableName
- * @param string $schemaName
- * @return array
- */
- public function getForeignKeys($tableName, $schemaName = null)
- {
- $cacheKey = $this->_getTableName($tableName, $schemaName);
- $ddl = $this->loadDdlCache($cacheKey, self::DDL_FOREIGN_KEY);
- if ($ddl === false) {
- $ddl = array();
- $createSql = $this->getCreateTable($tableName, $schemaName);
- // collect CONSTRAINT
- $regExp = '#,\s+CONSTRAINT `([^`]*)` FOREIGN KEY \(`([^`]*)`\) '
- . 'REFERENCES (`[^`]*\.)?`([^`]*)` \(`([^`]*)`\)'
- . '( ON DELETE (RESTRICT|CASCADE|SET NULL|NO ACTION))?'
- . '( ON UPDATE (RESTRICT|CASCADE|SET NULL|NO ACTION))?#';
- $matches = array();
- preg_match_all($regExp, $createSql, $matches, PREG_SET_ORDER);
- foreach ($matches as $match) {
- $ddl[strtoupper($match[1])] = array(
- 'FK_NAME' => $match[1],
- 'SCHEMA_NAME' => $schemaName,
- 'TABLE_NAME' => $tableName,
- 'COLUMN_NAME' => $match[2],
- 'REF_SHEMA_NAME' => isset($match[3]) ? $match[3] : $schemaName,
- 'REF_TABLE_NAME' => $match[4],
- 'REF_COLUMN_NAME' => $match[5],
- 'ON_DELETE' => isset($match[6]) ? $match[7] : '',
- 'ON_UPDATE' => isset($match[8]) ? $match[9] : ''
- );
- }
- $this->saveDdlCache($cacheKey, self::DDL_FOREIGN_KEY, $ddl);
- }
- return $ddl;
- }
- /**
- * Retrieve the foreign keys tree for all tables
- *
- * @return array
- */
- public function getForeignKeysTree()
- {
- $tree = array();
- foreach ($this->listTables() as $table) {
- foreach($this->getForeignKeys($table) as $key) {
- $tree[$table][$key['COLUMN_NAME']] = $key;
- }
- }
- return $tree;
- }
- /**
- * Modify tables, used for upgrade process
- * Change columns definitions, reset foreign keys, change tables comments and engines.
- *
- * The value of each array element is an associative array
- * with the following keys:
- *
- * columns => array; list of columns definitions
- * comment => string; table comment
- * engine => string; table engine
- *
- * @return Varien_Db_Adapter_Pdo_Mysql
- */
- public function modifyTables($tables)
- {
- $foreignKeys = $this->getForeignKeysTree();
- foreach ($tables as $table => $tableData) {
- if (!$this->isTableExists($table)) {
- continue;
- }
- foreach ($tableData['columns'] as $column =>$columnDefinition) {
- if (!$this->tableColumnExists($table, $column)) {
- continue;
- }
- $droppedKeys = array();
- foreach($foreignKeys as $keyTable => $columns) {
- foreach($columns as $columnName => $keyOptions) {
- if ($table == $keyOptions['REF_TABLE_NAME'] && $column == $keyOptions['REF_COLUMN_NAME']) {
- $this->dropForeignKey($keyTable, $keyOptions['FK_NAME']);
- $droppedKeys[] = $keyOptions;
- }
- }
- }
- $this->modifyColumn($table, $column, $columnDefinition);
- foreach ($droppedKeys as $options) {
- unset($columnDefinition['identity'], $columnDefinition['primary'], $columnDefinition['comment']);
- $onDelete = $options['ON_DELETE'];
- $onUpdate = $options['ON_UPDATE'];
- if ($onDelete == Varien_Db_Adapter_Interface::FK_ACTION_SET_NULL
- || $onUpdate == Varien_Db_Adapter_Interface::FK_ACTION_SET_NULL) {
- $columnDefinition['nullable'] = true;
- }
- $this->modifyColumn($options['TABLE_NAME'], $options['COLUMN_NAME'], $columnDefinition);
- $this->addForeignKey(
- $options['FK_NAME'],
- $options['TABLE_NAME'],
- $options['COLUMN_NAME'],
- $options['REF_TABLE_NAME'],
- $options['REF_COLUMN_NAME'],
- ($onDelete) ? $onDelete : Varien_Db_Adapter_Interface::FK_ACTION_NO_ACTION,
- ($onUpdate) ? $onUpdate : Varien_Db_Adapter_Interface::FK_ACTION_NO_ACTION
- );
- }
- }
- if (!empty($tableData['comment'])) {
- $this->changeTableComment($table, $tableData['comment']);
- }
- if (!empty($tableData['engine'])) {
- $this->changeTableEngine($table, $tableData['engine']);
- }
- }
- return $this;
- }
- /**
- * Retrieve table index information
- *
- * The return value is an associative array keyed by the UPPERCASE index key (except for primary key,
- * that is always stored under 'PRIMARY' 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; lowercase, create index type
- * INDEX_METHOD => string; index method using
- * type => string; see INDEX_TYPE
- * fields => array; see COLUMNS_LIST
- *
- * @param string $tableName
- * @param string $schemaName
- * @return array
- */
- public function getIndexList($tableName, $schemaName = null)
- {
- $cacheKey = $this->_getTableName($tableName, $schemaName);
- $ddl = $this->loadDdlCache($cacheKey, self::DDL_INDEX);
- if ($ddl === false) {
- $ddl = array();
- $sql = sprintf('SHOW INDEX FROM %s',
- $this->quoteIdentifier($this->_getTableName($tableName, $schemaName)));
- foreach ($this->fetchAll($sql) as $row) {
- $fieldKeyName = 'Key_name';
- $fieldNonUnique = 'Non_unique';
- $fieldColumn = 'Column_name';
- $fieldIndexType = 'Index_type';
- if (strtolower($row[$fieldKeyName]) == Varien_Db_Adapter_Interface::INDEX_TYPE_PRIMARY) {
- $indexType = Varien_Db_Adapter_Interface::INDEX_TYPE_PRIMARY;
- } elseif ($row[$fieldNonUnique] == 0) {
- $indexType = Varien_Db_Adapter_Interface::INDEX_TYPE_UNIQUE;
- } elseif (strtolower($row[$fieldIndexType]) == Varien_Db_Adapter_Interface::INDEX_TYPE_FULLTEXT) {
- $indexType = Varien_Db_Adapter_Interface::INDEX_TYPE_FULLTEXT;
- } else {
- $indexType = Varien_Db_Adapter_Interface::INDEX_TYPE_INDEX;
- }
- $upperKeyName = strtoupper($row[$fieldKeyName]);
- if (isset($ddl[$upperKeyName])) {
- $ddl[$upperKeyName]['fields'][] = $row[$fieldColumn]; // for compatible
- $ddl[$upperKeyName]['COLUMNS_LIST'][] = $row[$fieldColumn];
- } else {
- $ddl[$upperKeyName] = array(
- 'SCHEMA_NAME' => $schemaName,
- 'TABLE_NAME' => $tableName,
- 'KEY_NAME' => $row[$fieldKeyName],
- 'COLUMNS_LIST' => array($row[$fieldColumn]),
- 'INDEX_TYPE' => $indexType,
- 'INDEX_METHOD' => $row[$fieldIndexType],
- 'type' => strtolower($indexType), // for compatibility
- 'fields' => array($row[$fieldColumn]) // for compatibility
- );
- }
- }
- $this->saveDdlCache($cacheKey, self::DDL_INDEX, $ddl);
- }
- return $ddl;
- }
- /**
- * Add Index Key
- *
- * @deprecated since 1.5.0.0
- * @param string $tableName
- * @param string $indexName
- * @param string|array $fields
- * @param string $indexType
- * @param string $schemaName
- * @return Zend_Db_Statement_Interface
- */
- public function addKey($tableName, $indexName, $fields, $indexType = 'index', $schemaName = null)
- {
- return $this->addIndex($tableName, $indexName, $fields, $indexType, $schemaName);
- }
- /**
- * Remove duplicate entry for create key
- *
- * @param string $table
- * @param array $fields
- * @param array $ids
- * @return Varien_Db_Adapter_Pdo_Mysql
- */
- protected function _removeDuplicateEntry($table, $fields, $ids)
- {
- $where = array();
- $i = 0;
- foreach ($fields as $field) {
- $where[] = $this->quoteInto($field . '=?', $ids[$i++]);
- }
- if (!$where) {
- return $this;
- }
- $whereCond = implode(' AND ', $where);
- $sql = sprintf('SELECT COUNT(*) as `cnt` FROM `%s` WHERE %s', $table, $whereCond);
- $cnt = $this->raw_fetchRow($sql, 'cnt');
- if ($cnt > 1) {
- $sql = sprintf('DELETE FROM `%s` WHERE %s LIMIT %d',
- $table,
- $whereCond,
- $cnt - 1
- );
- $this->raw_query($sql);
- }
- return $this;
- }
- /**
- * Creates and returns a new Zend_Db_Select object for this adapter.
- *
- * @return Varien_Db_Select
- */
- public function select()
- {
- return new Varien_Db_Select($this);
- }
- /**
- * Start debug timer
- *
- * @return Varien_Db_Adapter_Pdo_Mysql
- */
- protected function _debugTimer()
- {
- if ($this->_debug) {
- $this->_debugTimer = microtime(true);
- }
- return $this;
- }
- /**
- * Logging debug information
- *
- * @param int $type
- * @param string $sql
- * @param array $bind
- * @param Zend_Db_Statement_Pdo $result
- * @return Varien_Db_Adapter_Pdo_Mysql
- */
- protected function _debugStat($type, $sql, $bind = array(), $result = null)
- {
- if (!$this->_debug) {
- return $this;
- }
- $code = '## ' . getmypid() . ' ## ';
- $nl = "\n";
- $time = sprintf('%.4f', microtime(true) - $this->_debugTimer);
- if (!$this->_logAllQueries && $time < $this->_logQueryTime) {
- return $this;
- }
- switch ($type) {
- case self::DEBUG_CONNECT:
- $code .= 'CONNECT' . $nl;
- break;
- case self::DEBUG_TRANSACTION:
- $code .= 'TRANSACTION ' . $sql . $nl;
- break;
- case self::DEBUG_QUERY:
- $code .= 'QUERY' . $nl;
- $code .= 'SQL: ' . $sql . $nl;
- if ($bind) {
- $code .= 'BIND: ' . var_export($bind, true) . $nl;
- }
- if ($result instanceof Zend_Db_Statement_Pdo) {
- $code .= 'AFF: ' . $result->rowCount() . $nl;
- }
- break;
- }
- $code .= 'TIME: ' . $time . $nl;
- if ($this->_logCallStack) {
- $code .= 'TRACE: ' . Varien_Debug::backtrace(true, false) . $nl;
- }
- $code .= $nl;
- $this->_debugWriteToFile($code);
- return $this;
- }
- /**
- * Write exception and thow
- *
- * @param Exception $e
- * @throws Exception
- */
- protected function _debugException(Exception $e)
- {
- if (!$this->_debug) {
- throw $e;
- }
- $nl = "\n";
- $code = 'EXCEPTION ' . $nl . $e . $nl . $nl;
- $this->_debugWriteToFile($code);
- throw $e;
- }
- /**
- * Debug write to file process
- *
- * @param string $str
- */
- protected function _debugWriteToFile($str)
- {
- $str = '## ' . date('Y-m-d H:i:s') . "\r\n" . $str;
- if (!$this->_debugIoAdapter) {
- $this->_debugIoAdapter = new Varien_Io_File();
- $dir = Mage::getBaseDir() . DS . $this->_debugIoAdapter->dirname($this->_debugFile);
- $this->_debugIoAdapter->checkAndCreateFolder($dir);
- $this->_debugIoAdapter->open(array('path' => $dir));
- $this->_debugFile = basename($this->_debugFile);
- }
- $this->_debugIoAdapter->streamOpen($this->_debugFile, 'a');
- $this->_debugIoAdapter->streamLock();
- $this->_debugIoAdapter->streamWrite($str);
- $this->_debugIoAdapter->streamUnlock();
- $this->_debugIoAdapter->streamClose();
- }
- /**
- * Quotes a value and places into a piece of text at a placeholder.
- *
- * Method revrited for handle empty arrays in value param
- *
- * @param string $text The text with a placeholder.
- * @param mixed $value The value to quote.
- * @param string $type OPTIONAL SQL datatype
- * @param integer $count OPTIONAL count of placeholders to replace
- * @return string An SQL-safe quoted value placed into the orignal text.
- */
- public function quoteInto($text, $value, $type = null, $count = null)
- {
- if (is_array($value) && empty($value)) {
- $value = new Zend_Db_Expr('NULL');
- }
- return parent::quoteInto($text, $value, $type, $count);
- }
- /**
- * Retrieve ddl cache name
- *
- * @param string $tableName
- * @param string $schemaName
- */
- protected function _getTableName($tableName, $schemaName = null)
- {
- return ($schemaName ? $schemaName . '.' : '') . $tableName;
- }
- /**
- * Retrieve Id for cache
- *
- * @param string $tableKey
- * @param int $ddlType
- * @return string
- */
- protected function _getCacheId($tableKey, $ddlType)
- {
- return sprintf('%s_%s_%s', self::DDL_CACHE_PREFIX, $tableKey, $ddlType);
- }
- /**
- * Load DDL data from cache
- * Return false if cache does not exists
- *
- * @param string $tableCacheKey the table cache key
- * @param int $ddlType the DDL constant
- * @return string|array|int|false
- */
- public function loadDdlCache($tableCacheKey, $ddlType)
- {
- if (!$this->_isDdlCacheAllowed) {
- return false;
- }
- if (isset($this->_ddlCache[$ddlType][$tableCacheKey])) {
- return $this->_ddlCache[$ddlType][$tableCacheKey];
- }
- if ($this->_cacheAdapter instanceof Zend_Cache_Core) {
- $cacheId = $this->_getCacheId($tableCacheKey, $ddlType);
- $data = $this->_cacheAdapter->load($cacheId);
- if ($data !== false) {
- $data = unserialize($data);
- $this->_ddlCache[$ddlType][$tableCacheKey] = $data;
- }
- return $data;
- }
- return false;
- }
- /**
- * Save DDL data into cache
- *
- * @param string $tableCacheKey
- * @param int $ddlType
- * @return Varien_Db_Adapter_Pdo_Mysql
- */
- public function saveDdlCache($tableCacheKey, $ddlType, $data)
- {
- if (!$this->_isDdlCacheAllowed) {
- return $this;
- }
- $this->_ddlCache[$ddlType][$tableCacheKey] = $data;
- if ($this->_cacheAdapter instanceof Zend_Cache_Core) {
- $cacheId = $this->_getCacheId($tableCacheKey, $ddlType);
- $data = serialize($data);
- $this->_cacheAdapter->save($data, $cacheId, array(self::DDL_CACHE_TAG));
- }
- return $this;
- }
- /**
- * Reset cached DDL data from cache
- * if table name is null - reset all cached DDL data
- *
- * @param string $tableName
- * @param string $schemaName OPTIONAL
- * @return Varien_Db_Adapter_Pdo_Mysql
- */
- public function resetDdlCache($tableName = null, $schemaName = null)
- {
- if (!$this->_isDdlCacheAllowed) {
- return $this;
- }
- if ($tableName === null) {
- $this->_ddlCache = array();
- if ($this->_cacheAdapter instanceof Zend_Cache_Core) {
- $this->_cacheAdapter->clean(Zend_Cache::CLEANING_MODE_MATCHING_TAG, array(self::DDL_CACHE_TAG));
- }
- } else {
- $cacheKey = $this->_getTableName($tableName, $schemaName);
- $ddlTypes = array(self::DDL_DESCRIBE, self::DDL_CREATE, self::DDL_INDEX, self::DDL_FOREIGN_KEY);
- foreach ($ddlTypes as $ddlType) {
- unset($this->_ddlCache[$ddlType][$cacheKey]);
- }
- if ($this->_cacheAdapter instanceof Zend_Cache_Core) {
- foreach ($ddlTypes as $ddlType) {
- $cacheId = $this->_getCacheId($cacheKey, $ddlType);
- $this->_cacheAdapter->remove($cacheId);
- }
- }
- }
- return $this;
- }
- /**
- * Disallow DDL caching
- * @return Varien_Db_Adapter_Pdo_Mysql
- */
- public function disallowDdlCache()
- {
- $this->_isDdlCacheAllowed = false;
- return $this;
- }
- /**
- * Allow DDL caching
- * @return Varien_Db_Adapter_Pdo_Mysql
- */
- public function allowDdlCache()
- {
- $this->_isDdlCacheAllowed = true;
- return $this;
- }
- /**
- * 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
- *
- * @param string $tableName
- * @param string $schemaName OPTIONAL
- * @return array
- */
- public function describeTable($tableName, $schemaName = null)
- {
- $cacheKey = $this->_getTableName($tableName, $schemaName);
- $ddl = $this->loadDdlCache($cacheKey, self::DDL_DESCRIBE);
- if ($ddl === false) {
- $ddl = parent::describeTable($tableName, $schemaName);
- /**
- * Remove bug in some MySQL versions, when int-column without default value is described as:
- * having default empty string value
- */
- $affected = array('tinyint', 'smallint', 'mediumint', 'int', 'bigint');
- foreach ($ddl as $key => $columnData) {
- if (($columnData['DEFAULT'] === '') && (array_search($columnData['DATA_TYPE'], $affected) !== FALSE)) {
- $ddl[$key]['DEFAULT'] = null;
- }
- }
- $this->saveDdlCache($cacheKey, self::DDL_DESCRIBE, $ddl);
- }
- return $ddl;
- }
- /**
- * Format described column to definition, ready to be added to ddl table.
- * Return array with keys: name, type, length, options, comment
- *
- * @param array $columnData
- * @return array
- */
- public function getColumnCreateByDescribe($columnData)
- {
- $type = $this->_getColumnTypeByDdl($columnData);
- $options = array();
- if ($columnData['IDENTITY'] === true) {
- $options['identity'] = true;
- }
- if ($columnData['UNSIGNED'] === true) {
- $options['unsigned'] = true;
- }
- if ($columnData['NULLABLE'] === false
- && !($type == Varien_Db_Ddl_Table::TYPE_TEXT && strlen($columnData['DEFAULT']) != 0)
- ) {
- $options['nullable'] = false;
- }
- if ($columnData['PRIMARY'] === true) {
- $options['primary'] = true;
- }
- if (!is_null($columnData['DEFAULT'])
- && $type != Varien_Db_Ddl_Table::TYPE_TEXT
- ) {
- $options['default'] = $this->quote($columnData['DEFAULT']);
- }
- if (strlen($columnData['SCALE']) > 0) {
- $options['scale'] = $columnData['SCALE'];
- }
- if (strlen($columnData['PRECISION']) > 0) {
- $options['precision'] = $columnData['PRECISION'];
- }
- $comment = uc_words($columnData['COLUMN_NAME'], ' ');
- $result = array(
- 'name' => $columnData['COLUMN_NAME'],
- 'type' => $type,
- 'length' => $columnData['LENGTH'],
- 'options' => $options,
- 'comment' => $comment
- );
- return $result;
- }
- /**
- * Create Varien_Db_Ddl_Table object by data from describe table
- *
- * @param $tableName
- * @param $newTableName
- * @return Varien_Db_Ddl_Table
- */
- public function createTableByDdl($tableName, $newTableName)
- {
- $describe = $this->describeTable($tableName);
- $table = $this->newTable($newTableName)
- ->setComment(uc_words($newTableName, ' '));
- foreach ($describe as $columnData) {
- $columnInfo = $this->getColumnCreateByDescribe($columnData);
- $table->addColumn(
- $columnInfo['name'],
- $columnInfo['type'],
- $columnInfo['length'],
- $columnInfo['options'],
- $columnInfo['comment']
- );
- }
- $indexes = $this->getIndexList($tableName);
- foreach ($indexes as $indexData) {
- /**
- * Do not create primary index - it is created with identity column.
- * For reliability check both name and type, because these values can start to differ in future.
- */
- if (($indexData['KEY_NAME'] == 'PRIMARY')
- || ($indexData['INDEX_TYPE'] == Varien_Db_Adapter_Interface::INDEX_TYPE_PRIMARY)
- ) {
- continue;
- }
- $fields = $indexData['COLUMNS_LIST'];
- $options = array('type' => $indexData['INDEX_TYPE']);
- $table->addIndex($this->getIndexName($newTableName, $fields, $indexData['INDEX_TYPE']), $fields, $options);
- }
- $foreignKeys = $this->getForeignKeys($tableName);
- foreach ($foreignKeys as $keyData) {
- $fkName = $this->getForeignKeyName(
- $newTableName, $keyData['COLUMN_NAME'], $keyData['REF_TABLE_NAME'], $keyData['REF_COLUMN_NAME']
- );
- $onDelete = $this->_getDdlAction($keyData['ON_DELETE']);
- $onUpdate = $this->_getDdlAction($keyData['ON_UPDATE']);
- $table->addForeignKey(
- $fkName, $keyData['COLUMN_NAME'], $keyData['REF_TABLE_NAME'],
- $keyData['REF_COLUMN_NAME'], $onDelete, $onUpdate
- );
- }
- // Set additional options
- $tableData = $this->showTableStatus($tableName);
- $table->setOption('type', $tableData['Engine']);
- return $table;
- }
- /**
- * Modify the column definition by data from describe table
- *
- * @param string $tableName
- * @param string $columnName
- * @param array $definition
- * @param boolean $flushData
- * @param string $schemaName
- * @return Varien_Db_Adapter_Pdo_Mysql
- */
- public function modifyColumnByDdl($tableName, $columnName, $definition, $flushData = false, $schemaName = null)
- {
- $definition = array_change_key_case($definition, CASE_UPPER);
- $definition['COLUMN_TYPE'] = $this->_getColumnTypeByDdl($definition);
- if (array_key_exists('DEFAULT', $definition) && is_null($definition['DEFAULT'])) {
- unset($definition['DEFAULT']);
- }
- return $this->modifyColumn($tableName, $columnName, $definition, $flushData, $schemaName);
- }
- /**
- * Retrieve column data type by data from describe table
- *
- * @param array $column
- * @return string
- */
- protected function _getColumnTypeByDdl($column)
- {
- switch ($column['DATA_TYPE']) {
- case 'bool':
- return Varien_Db_Ddl_Table::TYPE_BOOLEAN;
- case 'tinytext':
- case 'char':
- case 'varchar':
- case 'text':
- case 'mediumtext':
- case 'longtext':
- return Varien_Db_Ddl_Table::TYPE_TEXT;
- case 'blob':
- case 'mediumblob':
- case 'longblob':
- return Varien_Db_Ddl_Table::TYPE_BLOB;
- case 'tinyint':
- case 'smallint':
- return Varien_Db_Ddl_Table::TYPE_SMALLINT;
- case 'mediumint':
- case 'int':
- return Varien_Db_Ddl_Table::TYPE_INTEGER;
- case 'bigint':
- return Varien_Db_Ddl_Table::TYPE_BIGINT;
- case 'datetime':
- return Varien_Db_Ddl_Table::TYPE_DATETIME;
- case 'timestamp':
- return Varien_Db_Ddl_Table::TYPE_TIMESTAMP;
- case 'date':
- return Varien_Db_Ddl_Table::TYPE_DATE;
- case 'float':
- return Varien_Db_Ddl_Table::TYPE_FLOAT;
- case 'decimal':
- case 'numeric':
- return Varien_Db_Ddl_Table::TYPE_DECIMAL;
- }
- }
- /**
- * Truncate table
- *
- * @deprecated since 1.4.0.1
- * @param string $tableName
- * @param string $schemaName
- * @return Varien_Db_Adapter_Pdo_Mysql
- */
- public function truncate($tableName, $schemaName = null)
- {
- return $this->truncateTable($tableName, $schemaName);
- }
- /**
- * Change table storage engine
- *
- * @param string $tableName
- * @param string $engine
- * @param string $schemaName
- * @return mixed
- */
- public function changeTableEngine($tableName, $engine, $schemaName = null)
- {
- $table = $this->quoteIdentifier($this->_getTableName($tableName, $schemaName));
- $sql = sprintf('ALTER TABLE %s ENGINE=%s', $table, $engine);
- return $this->raw_query($sql);
- }
- /**
- * Change table comment
- *
- * @param string $tableName
- * @param string $comment
- * @param string $schemaName
- * @return mixed
- */
- public function changeTableComment($tableName, $comment, $schemaName = null)
- {
- $table = $this->quoteIdentifier($this->_getTableName($tableName, $schemaName));
- $sql = sprintf("ALTER TABLE %s COMMENT='%s'", $table, $comment);
- return $this->raw_query($sql);
- }
- /**
- * Inserts a table row with specified data
- * Special for Zero values to identity column
- *
- * @param string $table
- * @param array $bind
- * @return int The number of affected rows.
- */
- public function insertForce($table, array $bind)
- {
- $this->raw_query("SET @OLD_INSERT_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO'");
- $result = $this->insert($table, $bind);
- $this->raw_query("SET SQL_MODE=IFNULL(@OLD_INSERT_SQL_MODE,'')");
- return $result;
- }
- /**
- * Inserts a table row with specified data.
- *
- * @param mixed $table The table to insert data into.
- * @param array $data Column-value pairs or array of column-value pairs.
- * @param array $fields update fields pairs or values
- * @return int The number of affected rows.
- * @throws Zend_Db_Exception
- */
- public function insertOnDuplicate($table, array $data, array $fields = array())
- {
- // extract and quote col names from the array keys
- $row = reset($data); // get first element from data array
- $bind = array(); // SQL bind array
- $values = array();
- if (is_array($row)) { // Array of column-value pairs
- $cols = array_keys($row);
- foreach ($data as $row) {
- if (array_diff($cols, array_keys($row))) {
- throw new Zend_Db_Exception('Invalid data for insert');
- }
- $values[] = $this->_prepareInsertData($row, $bind);
- }
- unset($row);
- } else { // Column-value pairs
- $cols = array_keys($data);
- $values[] = $this->_prepareInsertData($data, $bind);
- }
- $updateFields = array();
- if (empty($fields)) {
- $fields = $cols;
- }
- // quote column names
- // $cols = array_map(array($this, 'quoteIdentifier'), $cols);
- // prepare ON DUPLICATE KEY conditions
- foreach ($fields as $k => $v) {
- $field = $value = null;
- if (!is_numeric($k)) {
- $field = $this->quoteIdentifier($k);
- if ($v instanceof Zend_Db_Expr) {
- $value = $v->__toString();
- } elseif (is_string($v)) {
- $value = sprintf('VALUES(%s)', $this->quoteIdentifier($v));
- } elseif (is_numeric($v)) {
- $value = $this->quoteInto('?', $v);
- }
- } elseif (is_string($v)) {
- $value = sprintf('VALUES(%s)', $this->quoteIdentifier($v));
- $field = $v;
- }
- if ($field && $value) {
- $updateFields[] = sprintf('%s = %s', $field, $value);
- }
- }
- $insertSql = $this->_getInsertSqlQuery($table, $cols, $values);
- if ($updateFields) {
- $insertSql .= ' ON DUPLICATE KEY UPDATE ' . implode(', ', $updateFields);
- }
- // execute the statement and return the number of affected rows
- $stmt = $this->query($insertSql, array_values($bind));
- $result = $stmt->rowCount();
- return $result;
- }
- /**
- * Inserts a table multiply rows with specified data.
- *
- * @param mixed $table The table to insert data into.
- * @param array $data Column-value pairs or array of Column-value pairs.
- * @return int The number of affected rows.
- * @throws Zend_Db_Exception
- */
- public function insertMultiple($table, array $data)
- {
- $row = reset($data);
- // support insert syntaxes
- if (!is_array($row)) {
- return $this->insert($table, $data);
- }
- // validate data array
- $cols = array_keys($row);
- $insertArray = array();
- foreach ($data as $row) {
- $line = array();
- if (array_diff($cols, array_keys($row))) {
- throw new Zend_Db_Exception('Invalid data for insert');
- }
- foreach ($cols as $field) {
- $line[] = $row[$field];
- }
- $insertArray[] = $line;
- }
- unset($row);
- return $this->insertArray($table, $cols, $insertArray);
- }
- /**
- * Insert array to table based on columns definition
- *
- * @param string $table
- * @param array $columns
- * @param array $data
- * @return int
- * @throws Zend_Db_Exception
- */
- public function insertArray($table, array $columns, array $data)
- {
- $values = array();
- $bind = array();
- $columnsCount = count($columns);
- foreach ($data as $row) {
- if ($columnsCount != count($row)) {
- throw new Zend_Db_Exception('Invalid data for insert');
- }
- $values[] = $this->_prepareInsertData($row, $bind);
- }
- $insertQuery = $this->_getInsertSqlQuery($table, $columns, $values);
- // execute the statement and return the number of affected rows
- $stmt = $this->query($insertQuery, $bind);
- $result = $stmt->rowCount();
- return $result;
- }
- /**
- * Set cache adapter
- *
- * @param Zend_Cache_Backend_Interface $adapter
- * @return Varien_Db_Adapter_Pdo_Mysql
- */
- public function setCacheAdapter($adapter)
- {
- $this->_cacheAdapter = $adapter;
- return $this;
- }
- /**
- * Return new DDL Table object
- *
- * @param string $tableName the table name
- * @param string $schemaName the database/schema name
- * @return Varien_Db_Ddl_Table
- */
- public function newTable($tableName = null, $schemaName = null)
- {
- $table = new Varien_Db_Ddl_Table();
- if ($tableName !== null) {
- $table->setName($tableName);
- }
- if ($schemaName !== null) {
- $table->setSchema($schemaName);
- }
- return $table;
- }
- /**
- * Create table
- *
- * @param Varien_Db_Ddl_Table $table
- * @throws Zend_Db_Exception
- * @return Zend_Db_Pdo_Statement
- */
- public function createTable(Varien_Db_Ddl_Table $table)
- {
- $columns = $table->getColumns();
- foreach ($columns as $columnEntry) {
- if (empty($columnEntry['COMMENT'])) {
- throw new Zend_Db_Exception("Cannot create table without columns comments");
- }
- }
- $sqlFragment = array_merge(
- $this->_getColumnsDefinition($table),
- $this->_getIndexesDefinition($table),
- $this->_getForeignKeysDefinition($table)
- );
- $tableOptions = $this->_getOptionsDefinition($table);
- $sql = sprintf("CREATE TABLE %s (\n%s\n) %s",
- $this->quoteIdentifier($table->getName()),
- implode(",\n", $sqlFragment),
- implode(" ", $tableOptions));
- return $this->query($sql);
- }
- /**
- * Retrieve columns and primary keys definition array for create table
- *
- * @param Varien_Db_Ddl_Table $table
- * @return array
- * @throws Zend_Db_Exception
- */
- protected function _getColumnsDefinition(Varien_Db_Ddl_Table $table)
- {
- $definition = array();
- $primary = array();
- $columns = $table->getColumns();
- if (empty($columns)) {
- throw new Zend_Db_Exception('Table columns are not defined');
- }
- foreach ($columns as $columnData) {
- $columnDefinition = $this->_getColumnDefinition($columnData);
- if ($columnData['PRIMARY']) {
- $primary[$columnData['COLUMN_NAME']] = $columnData['PRIMARY_POSITION'];
- }
- $definition[] = sprintf(' %s %s',
- $this->quoteIdentifier($columnData['COLUMN_NAME']),
- $columnDefinition
- );
- }
- // PRIMARY KEY
- if (!empty($primary)) {
- asort($primary, SORT_NUMERIC);
- $primary = array_map(array($this, 'quoteIdentifier'), array_keys($primary));
- $definition[] = sprintf(' PRIMARY KEY (%s)', implode(', ', $primary));
- }
- return $definition;
- }
- /**
- * Retrieve table indexes definition array for create table
- *
- * @param Varien_Db_Ddl_Table $table
- * @return array
- */
- protected function _getIndexesDefinition(Varien_Db_Ddl_Table $table)
- {
- $definition = array();
- $indexes = $table->getIndexes();
- if (!empty($indexes)) {
- foreach ($indexes as $indexData) {
- if (!empty($indexData['TYPE'])) {
- switch ($indexData['TYPE']) {
- case 'primary':
- $indexType = 'PRIMARY KEY';
- unset($indexData['INDEX_NAME']);
- break;
- default:
- $indexType = strtoupper($indexData['TYPE']);
- break;
- }
- } else {
- $indexType = 'KEY';
- }
- $columns = array();
- foreach ($indexData['COLUMNS'] as $columnData) {
- $column = $this->quoteIdentifier($columnData['NAME']);
- if (!empty($columnData['SIZE'])) {
- $column .= sprintf('(%d)', $columnData['SIZE']);
- }
- $columns[] = $column;
- }
- $indexName = isset($indexData['INDEX_NAME']) ? $this->quoteIdentifier($indexData['INDEX_NAME']) : '';
- $definition[] = sprintf(' %s %s (%s)',
- $indexType,
- $indexName,
- implode(', ', $columns)
- );
- }
- }
- return $definition;
- }
- /**
- * Retrieve table foreign keys definition array for create table
- *
- * @param Varien_Db_Ddl_Table $table
- * @return array
- */
- protected function _getForeignKeysDefinition(Varien_Db_Ddl_Table $table)
- {
- $definition = array();
- $relations = $table->getForeignKeys();
- if (!empty($relations)) {
- foreach ($relations as $fkData) {
- $onDelete = $this->_getDdlAction($fkData['ON_DELETE']);
- $onUpdate = $this->_getDdlAction($fkData['ON_UPDATE']);
- $definition[] = sprintf(' CONSTRAINT %s FOREIGN KEY (%s) REFERENCES %s (%s) ON DELETE %s ON UPDATE %s',
- $this->quoteIdentifier($fkData['FK_NAME']),
- $this->quoteIdentifier($fkData['COLUMN_NAME']),
- $this->quoteIdentifier($fkData['REF_TABLE_NAME']),
- $this->quoteIdentifier($fkData['REF_COLUMN_NAME']),
- $onDelete,
- $onUpdate
- );
- }
- }
- return $definition;
- }
- /**
- * Retrieve table options definition array for create table
- *
- * @param Varien_Db_Ddl_Table $table
- * @return array
- * @throws Zend_Db_Exception
- */
- protected function _getOptionsDefinition(Varien_Db_Ddl_Table $table)
- {
- $definition = array();
- $comment = $table->getComment();
- if (empty($comment)) {
- throw new Zend_Db_Exception('Comment for table is required and must be defined');
- }
- $definition[] = $this->quoteInto('COMMENT=?', $comment);
- $tableProps = array(
- 'type' => 'ENGINE=%s',
- 'checksum' => 'CHECKSUM=%d',
- 'auto_increment' => 'AUTO_INCREMENT=%d',
- 'avg_row_length' => 'AVG_ROW_LENGTH=%d',
- 'max_rows' => 'MAX_ROWS=%d',
- 'min_rows' => 'MIN_ROWS=%d',
- 'delay_key_write' => 'DELAY_KEY_WRITE=%d',
- 'row_format' => 'row_format=%s',
- 'charset' => 'charset=%s',
- 'collate' => 'COLLATE=%s'
- );
- foreach ($tableProps as $key => $mask) {
- $v = $table->getOption($key);
- if ($v !== null) {
- $definition[] = sprintf($mask, $v);
- }
- }
- return $definition;
- }
- /**
- * Get column definition from description
- *
- * @param array $options
- * @param null|string $ddlType
- * @return string
- */
- public function getColumnDefinitionFromDescribe($options, $ddlType = null)
- {
- $columnInfo = $this->getColumnCreateByDescribe($options);
- foreach ($columnInfo['options'] as $key => $value) {
- $columnInfo[$key] = $value;
- }
- return $this->_getColumnDefinition($columnInfo, $ddlType);
- }
- /**
- * Retrieve column definition fragment
- *
- * @param array $options
- * @param string $ddlType Table DDL Column type constant
- * @throws Varien_Exception
- * @return string
- * @throws Zend_Db_Exception
- */
- protected function _getColumnDefinition($options, $ddlType = null)
- {
- // convert keys to uppercase
- $options = array_change_key_case($options, CASE_UPPER);
- $cType = null;
- $cUnsigned = false;
- $cNullable = true;
- $cDefault = false;
- $cIdentity = false;
- // detect and validate column type
- if ($ddlType === null) {
- $ddlType = $this->_getDdlType($options);
- }
- if (empty($ddlType) || !isset($this->_ddlColumnTypes[$ddlType])) {
- throw new Zend_Db_Exception('Invalid column definition data');
- }
- // column size
- $cType = $this->_ddlColumnTypes[$ddlType];
- switch ($ddlType) {
- case Varien_Db_Ddl_Table::TYPE_SMALLINT:
- case Varien_Db_Ddl_Table::TYPE_INTEGER:
- case Varien_Db_Ddl_Table::TYPE_BIGINT:
- if (!empty($options['UNSIGNED'])) {
- $cUnsigned = true;
- }
- break;
- case Varien_Db_Ddl_Table::TYPE_DECIMAL:
- case Varien_Db_Ddl_Table::TYPE_NUMERIC:
- $precision = 10;
- $scale = 0;
- $match = array();
- if (!empty($options['LENGTH']) && preg_match('#^\(?(\d+),(\d+)\)?$#', $options['LENGTH'], $match)) {
- $precision = $match[1];
- $scale = $match[2];
- } else {
- if (isset($options['SCALE']) && is_numeric($options['SCALE'])) {
- $scale = $options['SCALE'];
- }
- if (isset($options['PRECISION']) && is_numeric($options['PRECISION'])) {
- $precision = $options['PRECISION'];
- }
- }
- $cType .= sprintf('(%d,%d)', $precision, $scale);
- break;
- case Varien_Db_Ddl_Table::TYPE_TEXT:
- case Varien_Db_Ddl_Table::TYPE_BLOB:
- case Varien_Db_Ddl_Table::TYPE_VARBINARY:
- if (empty($options['LENGTH'])) {
- $length = Varien_Db_Ddl_Table::DEFAULT_TEXT_SIZE;
- } else {
- $length = $this->_parseTextSize($options['LENGTH']);
- }
- if ($length <= 255) {
- $cType = $ddlType == Varien_Db_Ddl_Table::TYPE_TEXT ? 'varchar' : 'varbinary';
- $cType = sprintf('%s(%d)', $cType, $length);
- } else if ($length > 255 && $length <= 65536) {
- $cType = $ddlType == Varien_Db_Ddl_Table::TYPE_TEXT ? 'text' : 'blob';
- } else if ($length > 65536 && $length <= 16777216) {
- $cType = $ddlType == Varien_Db_Ddl_Table::TYPE_TEXT ? 'mediumtext' : 'mediumblob';
- } else {
- $cType = $ddlType == Varien_Db_Ddl_Table::TYPE_TEXT ? 'longtext' : 'longblob';
- }
- break;
- }
- if (array_key_exists('DEFAULT', $options)) {
- $cDefault = $options['DEFAULT'];
- }
- if (array_key_exists('NULLABLE', $options)) {
- $cNullable = (bool)$options['NULLABLE'];
- }
- if (!empty($options['IDENTITY']) || !empty($options['AUTO_INCREMENT'])) {
- $cIdentity = true;
- }
- /* For cases when tables created from createTableByDdl()
- * where default value can be quoted already.
- * We need to avoid "double-quoting" here
- */
- if ( $cDefault !== null && strlen($cDefault)) {
- $cDefault = str_replace("'", '', $cDefault);
- }
- // prepare default value string
- if ($ddlType == Varien_Db_Ddl_Table::TYPE_TIMESTAMP) {
- if ($cDefault === null) {
- $cDefault = new Zend_Db_Expr('NULL');
- } elseif ($cDefault == Varien_Db_Ddl_Table::TIMESTAMP_INIT) {
- $cDefault = new Zend_Db_Expr('CURRENT_TIMESTAMP');
- } else if ($cDefault == Varien_Db_Ddl_Table::TIMESTAMP_UPDATE) {
- $cDefault = new Zend_Db_Expr('0 ON UPDATE CURRENT_TIMESTAMP');
- } else if ($cDefault == Varien_Db_Ddl_Table::TIMESTAMP_INIT_UPDATE) {
- $cDefault = new Zend_Db_Expr('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP');
- } else {
- $cDefault = false;
- }
- } else if (is_null($cDefault) && $cNullable) {
- $cDefault = new Zend_Db_Expr('NULL');
- }
- if (empty($options['COMMENT'])) {
- $comment = '';
- } else {
- $comment = $options['COMMENT'];
- }
- return sprintf('%s%s%s%s%s COMMENT %s',
- $cType,
- $cUnsigned ? ' UNSIGNED' : '',
- $cNullable ? ' NULL' : ' NOT NULL',
- $cDefault !== false ? $this->quoteInto(' default ?', $cDefault) : '',
- $cIdentity ? ' auto_increment' : '',
- $this->quote($comment)
- );
- }
- /**
- * Drop table from database
- *
- * @param string $tableName
- * @param string $schemaName
- * @return boolean
- */
- public function dropTable($tableName, $schemaName = null)
- {
- $table = $this->quoteIdentifier($this->_getTableName($tableName, $schemaName));
- $query = 'DROP TABLE IF EXISTS ' . $table;
- $this->query($query);
- return true;
- }
- /**
- * Truncate a table
- *
- * @param string $tableName
- * @param string $schemaName
- * @return Varien_Db_Adapter_Pdo_Mysql
- * @throws Zend_Db_Exception
- */
- public function truncateTable($tableName, $schemaName = null)
- {
- if (!$this->isTableExists($tableName, $schemaName)) {
- throw new Zend_Db_Exception(sprintf('Table "%s" is not exists', $tableName));
- }
- $table = $this->quoteIdentifier($this->_getTableName($tableName, $schemaName));
- $query = 'TRUNCATE TABLE ' . $table;
- $this->query($query);
- return $this;
- }
- /**
- * Check is a table exists
- *
- * @param string $tableName
- * @param string $schemaName
- * @return boolean
- */
- public function isTableExists($tableName, $schemaName = null)
- {
- return $this->showTableStatus($tableName, $schemaName) !== false;
- }
- /**
- * Rename table
- *
- * @param string $oldTableName
- * @param string $newTableName
- * @param string $schemaName
- * @return boolean
- * @throws Zend_Db_Exception
- */
- public function renameTable($oldTableName, $newTableName, $schemaName = null)
- {
- if (!$this->isTableExists($oldTableName, $schemaName)) {
- throw new Zend_Db_Exception(sprintf('Table "%s" is not exists', $oldTableName));
- }
- if ($this->isTableExists($newTableName, $schemaName)) {
- throw new Zend_Db_Exception(sprintf('Table "%s" already exists', $newTableName));
- }
- $oldTable = $this->_getTableName($oldTableName, $schemaName);
- $newTable = $this->_getTableName($newTableName, $schemaName);
- $query = sprintf('ALTER TABLE %s RENAME TO %s', $oldTable, $newTable);
- $this->query($query);
- $this->resetDdlCache($oldTableName, $schemaName);
- return true;
- }
- /**
- * Add new index to table name
- *
- * @param string $tableName
- * @param string $indexName
- * @param string|array $fields the table column name or array of ones
- * @param string $indexType the index type
- * @param string $schemaName
- * @return Zend_Db_Statement_Interface
- * @throws Zend_Db_Exception|Exception
- */
- public function addIndex($tableName, $indexName, $fields,
- $indexType = Varien_Db_Adapter_Interface::INDEX_TYPE_INDEX, $schemaName = null)
- {
- $columns = $this->describeTable($tableName, $schemaName);
- $keyList = $this->getIndexList($tableName, $schemaName);
- $query = sprintf('ALTER TABLE %s', $this->quoteIdentifier($this->_getTableName($tableName, $schemaName)));
- if (isset($keyList[strtoupper($indexName)])) {
- if ($keyList[strtoupper($indexName)]['INDEX_TYPE'] == Varien_Db_Adapter_Interface::INDEX_TYPE_PRIMARY) {
- $query .= ' DROP PRIMARY KEY,';
- } else {
- $query .= sprintf(' DROP INDEX %s,', $this->quoteIdentifier($indexName));
- }
- }
- if (!is_array($fields)) {
- $fields = array($fields);
- }
- $fieldSql = array();
- foreach ($fields as $field) {
- if (!isset($columns[$field])) {
- $msg = sprintf('There is no field "%s" that you are trying to create an index on "%s"',
- $field, $tableName);
- throw new Zend_Db_Exception($msg);
- }
- $fieldSql[] = $this->quoteIdentifier($field);
- }
- $fieldSql = implode(',', $fieldSql);
- switch (strtolower($indexType)) {
- case Varien_Db_Adapter_Interface::INDEX_TYPE_PRIMARY:
- $condition = 'PRIMARY KEY';
- break;
- case Varien_Db_Adapter_Interface::INDEX_TYPE_UNIQUE:
- $condition = 'UNIQUE ' . $this->quoteIdentifier($indexName);
- break;
- case Varien_Db_Adapter_Interface::INDEX_TYPE_FULLTEXT:
- $condition = 'FULLTEXT ' . $this->quoteIdentifier($indexName);
- break;
- default:
- $condition = 'INDEX ' . $this->quoteIdentifier($indexName);
- break;
- }
- $query .= sprintf(' ADD %s (%s)', $condition, $fieldSql);
- $cycle = true;
- while ($cycle === true) {
- try {
- $result = $this->raw_query($query);
- $cycle = false;
- } catch (Exception $e) {
- if (in_array(strtolower($indexType), array('primary', 'unique'))) {
- $match = array();
- if (preg_match('#SQLSTATE\[23000\]: [^:]+: 1062[^\']+\'([\d-\.]+)\'#', $e->getMessage(), $match)) {
- $ids = explode('-', $match[1]);
- $this->_removeDuplicateEntry($tableName, $fields, $ids);
- continue;
- }
- }
- throw $e;
- }
- }
- $this->resetDdlCache($tableName, $schemaName);
- return $result;
- }
- /**
- * Drop the index from table
- *
- * @param string $tableName
- * @param string $keyName
- * @param string $schemaName
- * @return bool|Zend_Db_Statement_Interface
- */
- public function dropIndex($tableName, $keyName, $schemaName = null)
- {
- $indexList = $this->getIndexList($tableName, $schemaName);
- $keyName = strtoupper($keyName);
- if (!isset($indexList[$keyName])) {
- return true;
- }
- if ($keyName == 'PRIMARY') {
- $cond = 'DROP PRIMARY KEY';
- } else {
- $cond = 'DROP KEY ' . $this->quoteIdentifier($indexList[$keyName]['KEY_NAME']);
- }
- $sql = sprintf('ALTER TABLE %s %s',
- $this->quoteIdentifier($this->_getTableName($tableName, $schemaName)),
- $cond);
- $this->resetDdlCache($tableName, $schemaName);
- return $this->raw_query($sql);
- }
- /**
- * Add new Foreign Key to table
- * If Foreign Key with same name is exist - it will be deleted
- *
- * @param string $fkName
- * @param string $tableName
- * @param string $columnName
- * @param string $refTableName
- * @param string $refColumnName
- * @param string $onDelete
- * @param string $onUpdate
- * @param boolean $purge trying remove invalid data
- * @param string $schemaName
- * @param string $refSchemaName
- * @return Varien_Db_Adapter_Pdo_Mysql
- */
- public function addForeignKey($fkName, $tableName, $columnName, $refTableName, $refColumnName,
- $onDelete = Varien_Db_Adapter_Interface::FK_ACTION_CASCADE,
- $onUpdate = Varien_Db_Adapter_Interface::FK_ACTION_CASCADE,
- $purge = false, $schemaName = null, $refSchemaName = null)
- {
- $this->dropForeignKey($tableName, $fkName, $schemaName);
- if ($purge) {
- $this->purgeOrphanRecords($tableName, $columnName, $refTableName, $refColumnName, $onDelete);
- }
- $query = sprintf('ALTER TABLE %s ADD CONSTRAINT %s FOREIGN KEY (%s) REFERENCES %s (%s)',
- $this->quoteIdentifier($this->_getTableName($tableName, $schemaName)),
- $this->quoteIdentifier($fkName),
- $this->quoteIdentifier($columnName),
- $this->quoteIdentifier($this->_getTableName($refTableName, $refSchemaName)),
- $this->quoteIdentifier($refColumnName)
- );
- if ($onDelete !== null) {
- $query .= ' ON DELETE ' . strtoupper($onDelete);
- }
- if ($onUpdate !== null) {
- $query .= ' ON UPDATE ' . strtoupper($onUpdate);
- }
- $result = $this->raw_query($query);
- $this->resetDdlCache($tableName);
- return $result;
- }
- /**
- * Format Date to internal database date format
- *
- * @param int|string|Zend_Date $date
- * @param boolean $includeTime
- * @return Zend_Db_Expr
- */
- public function formatDate($date, $includeTime = true)
- {
- $date = Varien_Date::formatDate($date, $includeTime);
- if ($date === null) {
- return new Zend_Db_Expr('NULL');
- }
- return new Zend_Db_Expr($this->quote($date));
- }
- /**
- * Run additional environment before setup
- *
- * @return Varien_Db_Adapter_Pdo_Mysql
- */
- public function startSetup()
- {
- $this->raw_query("SET SQL_MODE=''");
- $this->raw_query("SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0");
- $this->raw_query("SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO'");
- return $this;
- }
- /**
- * Run additional environment after setup
- *
- * @return Varien_Db_Adapter_Pdo_Mysql
- */
- public function endSetup()
- {
- $this->raw_query("SET SQL_MODE=IFNULL(@OLD_SQL_MODE,'')");
- $this->raw_query("SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS=0, 0, 1)");
- return $this;
- }
- /**
- * Build SQL statement for condition
- *
- * If $condition integer or string - exact value will be filtered ('eq' condition)
- *
- * If $condition is array is - one of the following structures is expected:
- * - array("from" => $fromValue, "to" => $toValue)
- * - array("eq" => $equalValue)
- * - array("neq" => $notEqualValue)
- * - array("like" => $likeValue)
- * - array("in" => array($inValues))
- * - array("nin" => array($notInValues))
- * - array("notnull" => $valueIsNotNull)
- * - array("null" => $valueIsNull)
- * - array("gt" => $greaterValue)
- * - array("lt" => $lessValue)
- * - array("gteq" => $greaterOrEqualValue)
- * - array("lteq" => $lessOrEqualValue)
- * - array("finset" => $valueInSet)
- * - array("regexp" => $regularExpression)
- * - array("seq" => $stringValue)
- * - array("sneq" => $stringValue)
- *
- * If non matched - sequential array is expected and OR conditions
- * will be built using above mentioned structure
- *
- * @param string|array $fieldName
- * @param integer|string|array $condition
- * @return string
- */
- public function prepareSqlCondition($fieldName, $condition)
- {
- $conditionKeyMap = array(
- 'eq' => "{{fieldName}} = ?",
- 'neq' => "{{fieldName}} != ?",
- 'like' => "{{fieldName}} LIKE ?",
- 'nlike' => "{{fieldName}} NOT LIKE ?",
- 'in' => "{{fieldName}} IN(?)",
- 'nin' => "{{fieldName}} NOT IN(?)",
- 'is' => "{{fieldName}} IS ?",
- 'notnull' => "{{fieldName}} IS NOT NULL",
- 'null' => "{{fieldName}} IS NULL",
- 'gt' => "{{fieldName}} > ?",
- 'lt' => "{{fieldName}} < ?",
- 'gteq' => "{{fieldName}} >= ?",
- 'lteq' => "{{fieldName}} <= ?",
- 'finset' => "FIND_IN_SET(?, {{fieldName}})",
- 'regexp' => "{{fieldName}} REGEXP ?",
- 'from' => "{{fieldName}} >= ?",
- 'to' => "{{fieldName}} <= ?",
- 'seq' => null,
- 'sneq' => null
- );
- $query = '';
- if (is_array($condition)) {
- if (isset($condition['field_expr'])) {
- $fieldName = str_replace('#?', $this->quoteIdentifier($fieldName), $condition['field_expr']);
- unset($condition['field_expr']);
- }
- $key = key(array_intersect_key($condition, $conditionKeyMap));
- if (isset($condition['from']) || isset($condition['to'])) {
- if (isset($condition['from'])) {
- $from = $this->_prepareSqlDateCondition($condition, 'from');
- $query = $this->_prepareQuotedSqlCondition($conditionKeyMap['from'], $from, $fieldName);
- }
- if (isset($condition['to'])) {
- $query .= empty($query) ? '' : ' AND ';
- $to = $this->_prepareSqlDateCondition($condition, 'to');
- $query = $this->_prepareQuotedSqlCondition($query . $conditionKeyMap['to'], $to, $fieldName);
- }
- } elseif (array_key_exists($key, $conditionKeyMap)) {
- $value = $condition[$key];
- if (($key == 'seq') || ($key == 'sneq')) {
- $key = $this->_transformStringSqlCondition($key, $value);
- }
- $query = $this->_prepareQuotedSqlCondition($conditionKeyMap[$key], $value, $fieldName);
- } else {
- $queries = array();
- foreach ($condition as $orCondition) {
- $queries[] = sprintf('(%s)', $this->prepareSqlCondition($fieldName, $orCondition));
- }
- $query = sprintf('(%s)', implode(' OR ', $queries));
- }
- } else {
- $query = $this->_prepareQuotedSqlCondition($conditionKeyMap['eq'], (string)$condition, $fieldName);
- }
- return $query;
- }
- /**
- * Prepare Sql condition
- *
- * @param $text Condition value
- * @param mixed $value
- * @param string $fieldName
- * @return string
- */
- protected function _prepareQuotedSqlCondition($text, $value, $fieldName)
- {
- $sql = $this->quoteInto($text, $value);
- $sql = str_replace('{{fieldName}}', $fieldName, $sql);
- return $sql;
- }
- /**
- * Transforms sql condition key 'seq' / 'sneq' that is used for comparing string values to its analog:
- * - 'null' / 'notnull' for empty strings
- * - 'eq' / 'neq' for non-empty strings
- *
- * @param string $conditionKey
- * @param mixed $value
- * @return string
- */
- protected function _transformStringSqlCondition($conditionKey, $value)
- {
- $value = (string) $value;
- if ($value == '') {
- return ($conditionKey == 'seq') ? 'null' : 'notnull';
- } else {
- return ($conditionKey == 'seq') ? 'eq' : 'neq';
- }
- }
- /**
- * Prepare value for save in column
- * Return converted to column data type value
- *
- * @param array $column the column describe array
- * @param mixed $value
- * @return mixed
- */
- public function prepareColumnValue(array $column, $value)
- {
- if ($value instanceof Zend_Db_Expr) {
- return $value;
- }
- if ($value instanceof Varien_Db_Statement_Parameter) {
- return $value;
- }
- // return original value if invalid column describe data
- if (!isset($column['DATA_TYPE'])) {
- return $value;
- }
- // return null
- if (is_null($value) && $column['NULLABLE']) {
- return null;
- }
- switch ($column['DATA_TYPE']) {
- case 'smallint':
- case 'int':
- case 'bigint':
- $value = (int)$value;
- break;
- case 'decimal':
- $precision = 10;
- $scale = 0;
- if (isset($column['SCALE'])) {
- $scale = $column['SCALE'];
- }
- if (isset($column['PRECISION'])) {
- $precision = $column['PRECISION'];
- }
- $format = sprintf('%%%d.%dF', $precision - $scale, $scale);
- $value = (float)sprintf($format, $value);
- break;
- case 'float':
- $value = (float)sprintf('%F', $value);
- break;
- case 'date':
- $value = $this->formatDate($value, false);
- break;
- case 'datetime':
- case 'timestamp':
- $value = $this->formatDate($value);
- break;
- case 'varchar':
- case 'mediumtext':
- case 'text':
- case 'longtext':
- $value = (string)$value;
- if ($column['NULLABLE'] && $value == '') {
- $value = null;
- }
- break;
- case 'varbinary':
- case 'mediumblob':
- case 'blob':
- case 'longblob':
- // No special processing for MySQL is needed
- break;
- }
- return $value;
- }
- /**
- * Generate fragment of SQL, that check condition and return true or false value
- *
- * @param Zend_Db_Expr|Zend_Db_Select|string $expression
- * @param string $true true value
- * @param string $false false value
- */
- public function getCheckSql($expression, $true, $false)
- {
- if ($expression instanceof Zend_Db_Expr || $expression instanceof Zend_Db_Select) {
- $expression = sprintf("IF((%s), %s, %s)", $expression, $true, $false);
- } else {
- $expression = sprintf("IF(%s, %s, %s)", $expression, $true, $false);
- }
- return new Zend_Db_Expr($expression);
- }
- /**
- * Returns valid IFNULL expression
- *
- * @param Zend_Db_Expr|Zend_Db_Select|string $expression
- * @param string $value OPTIONAL. Applies when $expression is NULL
- * @return Zend_Db_Expr
- */
- public function getIfNullSql($expression, $value = 0)
- {
- if ($expression instanceof Zend_Db_Expr || $expression instanceof Zend_Db_Select) {
- $expression = sprintf("IFNULL((%s), %s)", $expression, $value);
- } else {
- $expression = sprintf("IFNULL(%s, %s)", $expression, $value);
- }
- return new Zend_Db_Expr($expression);
- }
- /**
- * Generate fragment of SQL, that check value against multiple condition cases
- * and return different result depends on them
- *
- * @param string $valueName Name of value to check
- * @param array $casesResults Cases and results
- * @param string $defaultValue value to use if value doesn't confirm to any cases
- */
- public function getCaseSql($valueName, $casesResults, $defaultValue = null)
- {
- $expression = 'CASE ' . $valueName;
- foreach ($casesResults as $case => $result) {
- $expression .= ' WHEN ' . $case . ' THEN ' . $result;
- }
- if ($defaultValue !== null) {
- $expression .= ' ELSE ' . $defaultValue;
- }
- $expression .= ' END';
- return new Zend_Db_Expr($expression);
- }
- /**
- * Generate fragment of SQL, that combine together (concatenate) the results from data array
- * All arguments in data must be quoted
- *
- * @param array $data
- * @param string $separator concatenate with separator
- * @return Zend_Db_Expr
- */
- public function getConcatSql(array $data, $separator = null)
- {
- $format = empty($separator) ? 'CONCAT(%s)' : "CONCAT_WS('{$separator}', %s)";
- return new Zend_Db_Expr(sprintf($format, implode(', ', $data)));
- }
- /**
- * Generate fragment of SQL that returns length of character string
- * The string argument must be quoted
- *
- * @param string $string
- * @return Zend_Db_Expr
- */
- public function getLengthSql($string)
- {
- return new Zend_Db_Expr(sprintf('LENGTH(%s)', $string));
- }
- /**
- * Generate fragment of SQL, that compare with two or more arguments, and returns the smallest
- * (minimum-valued) argument
- * All arguments in data must be quoted
- *
- * @param array $data
- * @return Zend_Db_Expr
- */
- public function getLeastSql(array $data)
- {
- return new Zend_Db_Expr(sprintf('LEAST(%s)', implode(', ', $data)));
- }
- /**
- * Generate fragment of SQL, that compare with two or more arguments, and returns the largest
- * (maximum-valued) argument
- * All arguments in data must be quoted
- *
- * @param array $data
- * @return Zend_Db_Expr
- */
- public function getGreatestSql(array $data)
- {
- return new Zend_Db_Expr(sprintf('GREATEST(%s)', implode(', ', $data)));
- }
- /**
- * Get Interval Unit SQL fragment
- *
- * @param int $interval
- * @param string $unit
- * @return string
- * @throws Zend_Db_Exception
- */
- protected function _getIntervalUnitSql($interval, $unit)
- {
- if (!isset($this->_intervalUnits[$unit])) {
- throw new Zend_Db_Exception(sprintf('Undefined interval unit "%s" specified', $unit));
- }
- return sprintf('INTERVAL %d %s', $interval, $this->_intervalUnits[$unit]);
- }
- /**
- * Add time values (intervals) to a date value
- *
- * @see INTERVAL_* constants for $unit
- *
- * @param Zend_Db_Expr|string $date quoted field name or SQL statement
- * @param int $interval
- * @param string $unit
- * @return Zend_Db_Expr
- */
- public function getDateAddSql($date, $interval, $unit)
- {
- $expr = sprintf('DATE_ADD(%s, %s)', $date, $this->_getIntervalUnitSql($interval, $unit));
- return new Zend_Db_Expr($expr);
- }
- /**
- * Subtract time values (intervals) to a date value
- *
- * @see INTERVAL_* constants for $expr
- *
- * @param Zend_Db_Expr|string $date quoted field name or SQL statement
- * @param int|string $interval
- * @param string $unit
- * @return Zend_Db_Expr
- */
- public function getDateSubSql($date, $interval, $unit)
- {
- $expr = sprintf('DATE_SUB(%s, %s)', $date, $this->_getIntervalUnitSql($interval, $unit));
- return new Zend_Db_Expr($expr);
- }
- /**
- * Format date as specified
- *
- * Supported format Specifier
- *
- * %H Hour (00..23)
- * %i Minutes, numeric (00..59)
- * %s Seconds (00..59)
- * %d Day of the month, numeric (00..31)
- * %m Month, numeric (00..12)
- * %Y Year, numeric, four digits
- *
- * @param string $date quoted date value or non quoted SQL statement(field)
- * @param string $format
- * @return Zend_Db_Expr
- */
- public function getDateFormatSql($date, $format)
- {
- $expr = sprintf("DATE_FORMAT(%s, '%s')", $date, $format);
- return new Zend_Db_Expr($expr);
- }
- /**
- * Extract the date part of a date or datetime expression
- *
- * @param Zend_Db_Expr|string $date quoted field name or SQL statement
- * @return Zend_Db_Expr
- */
- public function getDatePartSql($date)
- {
- return new Zend_Db_Expr(sprintf('DATE(%s)', $date));
- }
- /**
- * Extract part of a date
- *
- * @see INTERVAL_* constants for $unit
- *
- * @param Zend_Db_Expr|string $date quoted field name or SQL statement
- * @param string $unit
- * @return Zend_Db_Expr
- * @throws Zend_Db_Exception
- */
- public function getDateExtractSql($date, $unit)
- {
- if (!isset($this->_intervalUnits[$unit])) {
- throw new Zend_Db_Exception(sprintf('Undefined interval unit "%s" specified', $unit));
- }
- $expr = sprintf('EXTRACT(%s FROM %s)', $this->_intervalUnits[$unit], $date);
- return new Zend_Db_Expr($expr);
- }
- /**
- * Minus superfluous characters from hash.
- *
- * @param $hash
- * @param $prefix
- * @param $maxCharacters
- * @return string
- */
- protected function _minusSuperfluous($hash, $prefix, $maxCharacters)
- {
- $diff = strlen($hash) + strlen($prefix) - $maxCharacters;
- $superfluous = $diff / 2;
- $odd = $diff % 2;
- $hash = substr($hash, $superfluous, - ($superfluous + $odd));
- return $hash;
- }
- /**
- * Retrieve valid table name
- * Check table name length and allowed symbols
- *
- * @param string $tableName
- * @return string
- */
- public function getTableName($tableName)
- {
- $prefix = 't_';
- if (strlen($tableName) > self::LENGTH_TABLE_NAME) {
- $shortName = Varien_Db_Helper::shortName($tableName);
- if (strlen($shortName) > self::LENGTH_TABLE_NAME) {
- $hash = md5($tableName);
- if (strlen($prefix.$hash) > self::LENGTH_TABLE_NAME) {
- $tableName = $this->_minusSuperfluous($hash, $prefix, self::LENGTH_TABLE_NAME);
- } else {
- $tableName = $prefix . $hash;
- }
- } else {
- $tableName = $shortName;
- }
- }
- return $tableName;
- }
- /**
- * Retrieve valid index name
- * Check index name length and allowed symbols
- *
- * @param string $tableName
- * @param string|array $fields the columns list
- * @param string $indexType
- * @return string
- */
- public function getIndexName($tableName, $fields, $indexType = '')
- {
- if (is_array($fields)) {
- $fields = implode('_', $fields);
- }
- switch (strtolower($indexType)) {
- case Varien_Db_Adapter_Interface::INDEX_TYPE_UNIQUE:
- $prefix = 'unq_';
- $shortPrefix = 'u_';
- break;
- case Varien_Db_Adapter_Interface::INDEX_TYPE_FULLTEXT:
- $prefix = 'fti_';
- $shortPrefix = 'f_';
- break;
- case Varien_Db_Adapter_Interface::INDEX_TYPE_INDEX:
- default:
- $prefix = 'idx_';
- $shortPrefix = 'i_';
- }
- $hash = $tableName . '_' . $fields;
- if (strlen($hash) + strlen($prefix) > self::LENGTH_INDEX_NAME) {
- $short = Varien_Db_Helper::shortName($prefix . $hash);
- if (strlen($short) > self::LENGTH_INDEX_NAME) {
- $hash = md5($hash);
- if (strlen($hash) + strlen($shortPrefix) > self::LENGTH_INDEX_NAME) {
- $hash = $this->_minusSuperfluous($hash, $shortPrefix, self::LENGTH_INDEX_NAME);
- }
- } else {
- $hash = $short;
- }
- } else {
- $hash = $prefix . $hash;
- }
- return strtoupper($hash);
- }
- /**
- * Retrieve valid foreign key name
- * Check foreign key name length and allowed symbols
- *
- * @param string $priTableName
- * @param string $priColumnName
- * @param string $refTableName
- * @param string $refColumnName
- * @return string
- */
- public function getForeignKeyName($priTableName, $priColumnName, $refTableName, $refColumnName)
- {
- $prefix = 'fk_';
- $hash = sprintf('%s_%s_%s_%s', $priTableName, $priColumnName, $refTableName, $refColumnName);
- if (strlen($prefix.$hash) > self::LENGTH_FOREIGN_NAME) {
- $short = Varien_Db_Helper::shortName($prefix.$hash);
- if (strlen($short) > self::LENGTH_FOREIGN_NAME) {
- $hash = md5($hash);
- if (strlen($prefix.$hash) > self::LENGTH_FOREIGN_NAME) {
- $hash = $this->_minusSuperfluous($hash, $prefix, self::LENGTH_FOREIGN_NAME);
- } else {
- $hash = $prefix . $hash;
- }
- } else {
- $hash = $short;
- }
- } else {
- $hash = $prefix . $hash;
- }
- return strtoupper($hash);
- }
- /**
- * Stop updating indexes
- *
- * @param string $tableName
- * @param string $schemaName
- * @return Varien_Db_Adapter_Pdo_Mysql
- */
- public function disableTableKeys($tableName, $schemaName = null)
- {
- $tableName = $this->_getTableName($tableName, $schemaName);
- $query = sprintf('ALTER TABLE %s DISABLE KEYS', $this->quoteIdentifier($tableName));
- $this->query($query);
- return $this;
- }
- /**
- * Re-create missing indexes
- *
- * @param string $tableName
- * @param string $schemaName
- * @return Varien_Db_Adapter_Pdo_Mysql
- */
- public function enableTableKeys($tableName, $schemaName = null)
- {
- $tableName = $this->_getTableName($tableName, $schemaName);
- $query = sprintf('ALTER TABLE %s ENABLE KEYS', $this->quoteIdentifier($tableName));
- $this->query($query);
- return $this;
- }
- /**
- * Get insert from Select object query
- *
- * @param Varien_Db_Select $select
- * @param string $table insert into table
- * @param array $fields
- * @param int $mode
- * @return string
- */
- public function insertFromSelect(Varien_Db_Select $select, $table, array $fields = array(), $mode = false)
- {
- $query = 'INSERT';
- if ($mode == self::INSERT_IGNORE) {
- $query .= ' IGNORE';
- }
- $query = sprintf('%s INTO %s', $query, $this->quoteIdentifier($table));
- if ($fields) {
- $columns = array_map(array($this, 'quoteIdentifier'), $fields);
- $query = sprintf('%s (%s)', $query, join(', ', $columns));
- }
- $query = sprintf('%s %s', $query, $select->assemble());
- if ($mode == self::INSERT_ON_DUPLICATE) {
- if (!$fields) {
- $describe = $this->describeTable($table);
- foreach ($describe as $column) {
- if ($column['PRIMARY'] === false) {
- $fields[] = $column['COLUMN_NAME'];
- }
- }
- }
- $update = array();
- foreach ($fields as $field) {
- $update[] = sprintf('%1$s = VALUES(%1$s)', $this->quoteIdentifier($field));
- }
- if ($update) {
- $query = sprintf('%s ON DUPLICATE KEY UPDATE %s', $query, join(', ', $update));
- }
- }
- return $query;
- }
- /**
- * Get update table query using select object for join and update
- *
- * @param Varien_Db_Select $select
- * @param string|array $table
- * @return string
- */
- public function updateFromSelect(Varien_Db_Select $select, $table)
- {
- if (!is_array($table)) {
- $table = array($table => $table);
- }
- // get table name and alias
- $keys = array_keys($table);
- $tableAlias = $keys[0];
- $tableName = $table[$keys[0]];
- $query = sprintf('UPDATE %s', $this->quoteTableAs($tableName, $tableAlias));
- // render JOIN conditions (FROM Part)
- $joinConds = array();
- foreach ($select->getPart(Zend_Db_Select::FROM) as $correlationName => $joinProp) {
- if ($joinProp['joinType'] == Zend_Db_Select::FROM) {
- $joinType = strtoupper(Zend_Db_Select::INNER_JOIN);
- } else {
- $joinType = strtoupper($joinProp['joinType']);
- }
- $joinTable = '';
- if ($joinProp['schema'] !== null) {
- $joinTable = sprintf('%s.', $this->quoteIdentifier($joinProp['schema']));
- }
- $joinTable .= $this->quoteTableAs($joinProp['tableName'], $correlationName);
- $join = sprintf(' %s %s', $joinType, $joinTable);
- if (!empty($joinProp['joinCondition'])) {
- $join = sprintf('%s ON %s', $join, $joinProp['joinCondition']);
- }
- $joinConds[] = $join;
- }
- if ($joinConds) {
- $query = sprintf("%s\n%s", $query, implode("\n", $joinConds));
- }
- // render UPDATE SET
- $columns = array();
- foreach ($select->getPart(Zend_Db_Select::COLUMNS) as $columnEntry) {
- list($correlationName, $column, $alias) = $columnEntry;
- if (empty($alias)) {
- $alias = $column;
- }
- if (!$column instanceof Zend_Db_Expr && !empty($correlationName)) {
- $column = $this->quoteIdentifier(array($correlationName, $column));
- }
- $columns[] = sprintf('%s = %s', $this->quoteIdentifier(array($tableAlias, $alias)), $column);
- }
- if (!$columns) {
- throw new Varien_Db_Exception('The columns for UPDATE statement are not defined');
- }
- $query = sprintf("%s\nSET %s", $query, implode(', ', $columns));
- // render WHERE
- $wherePart = $select->getPart(Zend_Db_Select::WHERE);
- if ($wherePart) {
- $query = sprintf("%s\nWHERE %s", $query, implode(' ', $wherePart));
- }
- return $query;
- }
- /**
- * Get delete from select object query
- *
- * @param Varien_Db_Select $select
- * @param string $table the table name or alias used in select
- * @return string|int
- */
- public function deleteFromSelect(Varien_Db_Select $select, $table)
- {
- $select = clone $select;
- $select->reset(Zend_Db_Select::DISTINCT);
- $select->reset(Zend_Db_Select::COLUMNS);
- $query = sprintf('DELETE %s %s', $this->quoteIdentifier($table), $select->assemble());
- return $query;
- }
- /**
- * Calculate checksum for table or for group of tables
- *
- * @param array|string $tableNames array of tables names | table name
- * @param string $schemaName schema name
- * @return arrray
- */
- public function getTablesChecksum($tableNames, $schemaName = null)
- {
- $result = array();
- $tableNames = is_array($tableNames) ? $tableNames : array($tableNames);
- foreach ($tableNames as $tableName) {
- $query = 'CHECKSUM TABLE ' . $this->_getTableName($tableName, $schemaName);
- $checkSumArray = $this->fetchRow($query);
- $result[$tableName] = $checkSumArray['Checksum'];
- }
- return $result;
- }
- /**
- * Check if the database support STRAIGHT JOIN
- *
- * @return boolean
- */
- public function supportStraightJoin()
- {
- return true;
- }
- /**
- * Adds order by random to select object
- * Possible using integer field for optimization
- *
- * @param Varien_Db_Select $select
- * @param string $field
- * @return Varien_Db_Adapter_Pdo_Mysql
- */
- public function orderRand(Varien_Db_Select $select, $field = null)
- {
- if ($field !== null) {
- $expression = new Zend_Db_Expr(sprintf('RAND() * %s', $this->quoteIdentifier($field)));
- $select->columns(array('mage_rand' => $expression));
- $spec = new Zend_Db_Expr('mage_rand');
- } else {
- $spec = new Zend_Db_Expr('RAND()');
- }
- $select->order($spec);
- return $this;
- }
- /**
- * Render SQL FOR UPDATE clause
- *
- * @param string $sql
- * @return string
- */
- public function forUpdate($sql)
- {
- return sprintf('%s FOR UPDATE', $sql);
- }
- /**
- * Prepare insert data
- *
- * @param mixed $row
- * @param array $bind
- * @return string
- */
- protected function _prepareInsertData($row, &$bind)
- {
- if (is_array($row)) {
- $line = array();
- foreach ($row as $value) {
- if ($value instanceof Zend_Db_Expr) {
- $line[] = $value->__toString();
- } else {
- $line[] = '?';
- $bind[] = $value;
- }
- }
- $line = implode(', ', $line);
- } elseif ($row instanceof Zend_Db_Expr) {
- $line = $row->__toString();
- } else {
- $line = '?';
- $bind[] = $row;
- }
- return sprintf('(%s)', $line);
- }
- /**
- * Return insert sql query
- *
- * @param string $tableName
- * @param array $columns
- * @param array $values
- * @return string
- */
- protected function _getInsertSqlQuery($tableName, array $columns, array $values)
- {
- $tableName = $this->quoteIdentifier($tableName, true);
- $columns = array_map(array($this, 'quoteIdentifier'), $columns);
- $columns = implode(',', $columns);
- $values = implode(', ', $values);
- $insertSql = sprintf('INSERT INTO %s (%s) VALUES %s', $tableName, $columns, $values);
- return $insertSql;
- }
- /**
- * Return ddl type
- *
- * @param array $options
- * @return string
- */
- protected function _getDdlType($options)
- {
- $ddlType = null;
- if (isset($options['TYPE'])) {
- $ddlType = $options['TYPE'];
- } elseif (isset($options['COLUMN_TYPE'])) {
- $ddlType = $options['COLUMN_TYPE'];
- }
- return $ddlType;
- }
- /**
- * Return DDL action
- *
- * @param string $action
- * @return string
- */
- protected function _getDdlAction($action)
- {
- switch ($action) {
- case Varien_Db_Adapter_Interface::FK_ACTION_CASCADE:
- return Varien_Db_Ddl_Table::ACTION_CASCADE;
- case Varien_Db_Adapter_Interface::FK_ACTION_SET_NULL:
- return Varien_Db_Ddl_Table::ACTION_SET_NULL;
- case Varien_Db_Adapter_Interface::FK_ACTION_RESTRICT:
- return Varien_Db_Ddl_Table::ACTION_RESTRICT;
- default:
- return Varien_Db_Ddl_Table::ACTION_NO_ACTION;
- }
- }
- /**
- * Prepare sql date condition
- *
- * @param array $condition
- * @param string $key
- * @return string
- */
- protected function _prepareSqlDateCondition($condition, $key)
- {
- if (empty($condition['date'])) {
- if (empty($condition['datetime'])) {
- $result = $condition[$key];
- } else {
- $result = $this->formatDate($condition[$key]);
- }
- } else {
- $result = $this->formatDate($condition[$key]);
- }
- return $result;
- }
- /**
- * Try to find installed primary key name, if not - formate new one.
- *
- * @param string $tableName Table name
- * @param string $schemaName OPTIONAL
- * @return string Primary Key name
- */
- public function getPrimaryKeyName($tableName, $schemaName = null)
- {
- $indexes = $this->getIndexList($tableName, $schemaName);
- if (isset($indexes['PRIMARY'])) {
- return $indexes['PRIMARY']['KEY_NAME'];
- } else {
- return 'PK_' . strtoupper($tableName);
- }
- }
- /**
- * Parse text size
- * Returns max allowed size if value great it
- *
- * @param string|int $size
- * @return int
- */
- protected function _parseTextSize($size)
- {
- $size = trim($size);
- $last = strtolower(substr($size, -1));
- switch ($last) {
- case 'k':
- $size = intval($size) * 1024;
- break;
- case 'm':
- $size = intval($size) * 1024 * 1024;
- break;
- case 'g':
- $size = intval($size) * 1024 * 1024 * 1024;
- break;
- }
- if (empty($size)) {
- return Varien_Db_Ddl_Table::DEFAULT_TEXT_SIZE;
- }
- if ($size >= Varien_Db_Ddl_Table::MAX_TEXT_SIZE) {
- return Varien_Db_Ddl_Table::MAX_TEXT_SIZE;
- }
- return intval($size);
- }
- /**
- * Converts fetched blob into raw binary PHP data.
- * The MySQL drivers do it nice, no processing required.
- *
- * @mixed $value
- * @return mixed
- */
- public function decodeVarbinary($value)
- {
- return $value;
- }
- /**
- * Returns date that fits into TYPE_DATETIME range and is suggested to act as default 'zero' value
- * for a column for current RDBMS. Deprecated and left for compatibility only.
- * In Magento at MySQL there was zero date used for datetime columns. However, zero date it is not supported across
- * different RDBMS. Thus now it is recommended to use same default value equal for all RDBMS - either NULL
- * or specific date supported by all RDBMS.
- *
- * @deprecated after 1.5.1.0
- * @return string
- */
- public function getSuggestedZeroDate()
- {
- return '0000-00-00 00:00:00';
- }
- /**
- * Retrieve Foreign Key name
- *
- * @deprecated after 1.6.0.0
- *
- * @param string $fkName
- * @return string
- */
- protected function _getForeignKeyName($fkName)
- {
- if (substr($fkName, 0, 3) != 'FK_') {
- $fkName = 'FK_' . $fkName;
- }
- return $fkName;
- }
- }