169 self::INTERVAL_YEAR =>
'YEAR',
170 self::INTERVAL_MONTH =>
'MONTH',
171 self::INTERVAL_DAY =>
'DAY',
172 self::INTERVAL_HOUR =>
'HOUR',
173 self::INTERVAL_MINUTE =>
'MINUTE',
174 self::INTERVAL_SECOND =>
'SECOND',
210 private $exceptionMap;
215 private $queryGenerator;
225 private $schemaListener;
250 $this->exceptionMap = [
252 2006 => ConnectionException::class,
254 2013 => ConnectionException::class,
256 1205 => LockWaitException::class,
258 1213 => DeadlockException::class,
260 1062 => DuplicateException::class,
265 throw new \InvalidArgumentException($e->getMessage(), $e->getCode(), $e);
277 if ($this->_isRolledBack) {
280 if ($this->_transactionLevel === 0) {
281 $this->logger->startTimer();
282 parent::beginTransaction();
297 if ($this->_transactionLevel === 1 && !$this->_isRolledBack) {
298 $this->logger->startTimer();
301 }
elseif ($this->_transactionLevel === 0) {
303 }
elseif ($this->_isRolledBack) {
318 if ($this->_transactionLevel === 1) {
319 $this->logger->startTimer();
321 $this->_isRolledBack =
false;
323 }
elseif ($this->_transactionLevel === 0) {
326 $this->_isRolledBack =
true;
376 if ($this->_connection) {
380 if (!extension_loaded(
'pdo_mysql')) {
381 throw new \Zend_Db_Adapter_Exception(
'pdo_mysql extension is not installed');
384 if (!isset($this->_config[
'host'])) {
385 throw new \Zend_Db_Adapter_Exception(
'No host configured to connect');
388 if (isset($this->_config[
'port'])) {
389 throw new \Zend_Db_Adapter_Exception(
'Port must be configured within host parameter (like localhost:3306');
392 unset($this->_config[
'port']);
394 if (strpos($this->_config[
'host'],
'/') !==
false) {
395 $this->_config[
'unix_socket'] = $this->_config[
'host'];
396 unset($this->_config[
'host']);
397 }
elseif (strpos($this->_config[
'host'],
':') !==
false) {
398 list($this->_config[
'host'], $this->_config[
'port']) = explode(
':', $this->_config[
'host']);
401 if (!isset($this->_config[
'driver_options'][\PDO::MYSQL_ATTR_MULTI_STATEMENTS])) {
402 $this->_config[
'driver_options'][\PDO::MYSQL_ATTR_MULTI_STATEMENTS] =
false;
405 $this->logger->startTimer();
410 $this->_connection->query(
"SET SQL_MODE=''");
413 $this->_connection->query(
"SET time_zone = '+00:00'");
415 if (isset($this->_config[
'initStatements'])) {
417 foreach ($statements as $statement) {
418 $this->_query($statement);
422 if (!$this->_connectionFlagsSet) {
423 $this->_connection->setAttribute(\PDO::ATTR_EMULATE_PREPARES,
true);
424 if (isset($this->_config[
'use_buffered_query']) && $this->_config[
'use_buffered_query'] ===
false) {
425 $this->_connection->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY,
false);
427 $this->_connection->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY,
true);
429 $this->_connectionFlagsSet =
true;
438 private function createConnection()
442 $this->_config[
'username'],
443 $this->_config[
'password'],
444 $this->_config[
'driver_options']
462 $e = $e->getPrevious();
463 if (!($e instanceof \PDOException)) {
464 $e = new \PDOException($e->getMessage(), $e->getCode());
494 return $row[$field] ??
false;
508 $sql = ltrim(preg_replace(
'/\s+/',
' ', $sql));
509 $sqlMessage = explode(
' ', $sql, 3);
510 $startSql = strtolower(substr($sqlMessage[0], 0, 3));
511 if (in_array($startSql, $this->_ddlRoutines) && strcasecmp($sqlMessage[1],
'temporary') !== 0) {
528 protected function _query($sql, $bind = [])
530 $connectionErrors = [
537 $this->logger->startTimer();
541 $result = parent::query($sql, $bind);
544 }
catch (\Exception $e) {
547 if ($profiler instanceof
Profiler) {
549 $profiler->queryEndLast();
553 $pdoException =
null;
554 if ($e instanceof \PDOException) {
557 && ($e->getPrevious() instanceof \PDOException)
559 $pdoException = $e->getPrevious();
563 if ($pdoException && $triesCount < self::MAX_CONNECTION_RETRIES
564 && in_array($pdoException->errorInfo[1], $connectionErrors)
573 if (!empty($this->_config[
'port'])) {
574 $this->_config[
'host'] = implode(
':', [$this->_config[
'host'], $this->_config[
'port']]);
575 unset($this->_config[
'port']);
583 $this->logger->critical($e);
585 if ($pdoException && isset($this->exceptionMap[$pdoException->errorInfo[1]])) {
586 $customExceptionClass = $this->exceptionMap[$pdoException->errorInfo[1]];
588 $customException =
new $customExceptionClass($e->getMessage(), $pdoException->errorInfo[1], $e);
589 throw $customException;
608 public function query($sql, $bind = [])
610 if (strpos(rtrim($sql,
" \t\n\r\0;"),
';') !==
false && count($this->
_splitMultiQuery($sql)) > 1) {
611 throw new \Magento\Framework\Exception\LocalizedException(
612 new Phrase(
"Multiple queries can't be executed. Run a single query and try again.")
615 return $this->_query($sql, $bind);
635 return $this->_query($sql, $bind);
649 $sql = (string) $sql;
650 if (!is_array($bind)) {
655 $isNamedBind =
false;
657 foreach ($bind as $k => $v) {
669 if ($this->_queryHook) {
670 $object = $this->_queryHook[
'object'];
671 $method = $this->_queryHook[
'method'];
672 $object->$method($sql, $bind);
689 if (isset($matches[6]) && (
690 strpos($matches[6],
"'") !==
false ||
691 strpos($matches[6],
':') !==
false ||
692 strpos($matches[6],
'?') !==
false)
695 $this->_bindParams[$bindName] = $this->
_unQuote($matches[6]);
696 return ' ' . $bindName;
718 return strtr(
$string, $translate);
735 $pos = strpos($sql,
'?', $offset);
736 if (
$pos !==
false) {
746 foreach ($bind as $k => $v) {
749 if (!isset($positions[$k])) {
752 $bindResult[$positions[$k]] = $v;
756 $pos = strpos($sql, $k, $offset);
757 if (
$pos ===
false) {
760 $offset =
$pos + strlen($k);
761 $bindResult[
$pos] = $v;
769 $bind = array_values($bindResult);
770 $sql = strtr($sql,
$map);
787 $this->_queryHook = $hook;
803 '#(;|\'|"|\\\\|//|--|\n|/\*|\*/)#',
806 PREG_SPLIT_NO_EMPTY | PREG_SPLIT_DELIM_CAPTURE
814 foreach ($parts as
$i => $part) {
816 if (($part ===
"'" || $part ===
'"') && (
$i === 0 || $parts[
$i-1] !==
'\\')) {
825 if (($part ===
'//' || $part ===
'--') && (
$i === 0 || $parts[
$i-1] ===
"\n")) {
827 }
elseif ($part ===
"\n" && ($c ===
'//' || $c ===
'--')) {
832 if ($part ===
'/*' && $c ===
false) {
834 }
elseif ($part ===
'*/' && $c ===
'/*') {
839 if ($part ===
';' && $q ===
false && $c ===
false) {
840 if (trim($s) !==
'') {
848 if (trim($s) !==
'') {
866 $fkName = strtoupper($fkName);
867 if (substr($fkName, 0, 3) ==
'FK_') {
868 $fkName = substr($fkName, 3);
870 foreach ([$fkName,
'FK_' . $fkName] as $key) {
871 if (isset($foreignKeys[$key])) {
873 'ALTER TABLE %s DROP FOREIGN KEY %s',
902 $onDelete = strtoupper($onDelete);
907 "DELETE p.* FROM %s AS p LEFT JOIN %s AS r ON p.%s = r.%s WHERE r.%s IS NULL",
917 "UPDATE %s AS p LEFT JOIN %s AS r ON p.%s = r.%s SET p.%s = NULL WHERE r.%s IS NULL",
942 foreach ($describe as $column) {
943 if ($column[
'COLUMN_NAME'] == $columnName) {
972 if (is_array($definition)) {
973 $definition = array_change_key_case($definition, CASE_UPPER);
974 if (empty($definition[
'COMMENT'])) {
975 throw new \Zend_Db_Exception(
"Impossible to create a column without comment.");
977 if (!empty($definition[
'PRIMARY'])) {
978 $primaryKey = sprintf(
', ADD PRIMARY KEY (%s)', $this->
quoteIdentifier($columnName));
984 'ALTER TABLE %s ADD COLUMN %s %s %s',
1015 foreach ($foreignKeys as $fkProp) {
1016 if ($fkProp[
'COLUMN_NAME'] == $columnName) {
1018 $alterDrop[] =
'DROP FOREIGN KEY ' . $this->
quoteIdentifier($fkProp[
'FK_NAME']);
1024 $idxColumns = $idxData[
'COLUMNS_LIST'];
1025 $idxColumnKey = array_search($columnName, $idxColumns);
1026 if ($idxColumnKey !==
false) {
1027 unset($idxColumns[$idxColumnKey]);
1028 if (empty($idxColumns)) {
1039 'ALTER TABLE %s %s',
1041 implode(
', ', $alterDrop)
1061 if ($idxData[
'COLUMNS_LIST'] ===
$columns) {
1097 throw new \Zend_Db_Exception(
1099 'Column "%s" does not exist in table "%s".',
1106 if (is_array($definition)) {
1111 'ALTER TABLE %s CHANGE COLUMN %s %s %s',
1147 throw new \Zend_Db_Exception(sprintf(
'Column "%s" does not exist in table "%s".', $columnName,
$tableName));
1149 if (is_array($definition)) {
1154 'ALTER TABLE %s MODIFY COLUMN %s %s',
1179 if ($schemaName !==
null) {
1197 $ddl = $this->
loadDdlCache($cacheKey, self::DDL_CREATE);
1198 if ($ddl ===
false) {
1201 $this->
saveDdlCache($cacheKey, self::DDL_CREATE, $ddl);
1232 $ddl = $this->
loadDdlCache($cacheKey, self::DDL_FOREIGN_KEY);
1233 if ($ddl ===
false) {
1238 $regExp =
'#,\s+CONSTRAINT `([^`]*)` FOREIGN KEY ?\(`([^`]*)`\) ' 1239 .
'REFERENCES (`([^`]*)`\.)?`([^`]*)` \(`([^`]*)`\)' 1240 .
'( ON DELETE (RESTRICT|CASCADE|SET NULL|NO ACTION))?' 1241 .
'( ON UPDATE (RESTRICT|CASCADE|SET NULL|NO ACTION))?#';
1243 preg_match_all($regExp, $createSql, $matches, PREG_SET_ORDER);
1244 foreach ($matches as $match) {
1245 $ddl[strtoupper($match[1])] = [
1246 'FK_NAME' => $match[1],
1247 'SCHEMA_NAME' => $schemaName,
1249 'COLUMN_NAME' => $match[2],
1250 'REF_SHEMA_NAME' => isset($match[4]) ? $match[4] : $schemaName,
1251 'REF_TABLE_NAME' => $match[5],
1252 'REF_COLUMN_NAME' => $match[6],
1253 'ON_DELETE' => isset($match[7]) ? $match[8] :
'' 1257 $this->
saveDdlCache($cacheKey, self::DDL_FOREIGN_KEY, $ddl);
1273 $tree[
$table][$key[
'COLUMN_NAME']] = $key;
1300 foreach ($tables as
$table => $tableData) {
1304 foreach ($tableData[
'columns'] as $column => $columnDefinition) {
1309 foreach ($foreignKeys as $keyTable =>
$columns) {
1310 foreach (
$columns as $columnName => $keyOptions) {
1311 if (
$table == $keyOptions[
'REF_TABLE_NAME'] && $column == $keyOptions[
'REF_COLUMN_NAME']) {
1313 $droppedKeys[] = $keyOptions;
1320 foreach ($droppedKeys as
$options) {
1321 unset($columnDefinition[
'identity'], $columnDefinition[
'primary'], $columnDefinition[
'comment']);
1326 $columnDefinition[
'nullable'] =
true;
1330 $options[
'FK_NAME'],
1339 if (!empty($tableData[
'comment'])) {
1342 if (!empty($tableData[
'engine'])) {
1375 $ddl = $this->
loadDdlCache($cacheKey, self::DDL_INDEX);
1376 if ($ddl ===
false) {
1380 'SHOW INDEX FROM %s',
1384 $fieldKeyName =
'Key_name';
1385 $fieldNonUnique =
'Non_unique';
1386 $fieldColumn =
'Column_name';
1387 $fieldIndexType =
'Index_type';
1399 $upperKeyName = strtoupper(
$row[$fieldKeyName]);
1400 if (isset($ddl[$upperKeyName])) {
1401 $ddl[$upperKeyName][
'fields'][] =
$row[$fieldColumn];
1402 $ddl[$upperKeyName][
'COLUMNS_LIST'][] =
$row[$fieldColumn];
1404 $ddl[$upperKeyName] = [
1405 'SCHEMA_NAME' => $schemaName,
1407 'KEY_NAME' =>
$row[$fieldKeyName],
1408 'COLUMNS_LIST' => [
$row[$fieldColumn]],
1409 'INDEX_TYPE' => $indexType,
1410 'INDEX_METHOD' =>
$row[$fieldIndexType],
1411 'type' => strtolower($indexType),
1412 'fields' => [
$row[$fieldColumn]],
1435 $where[] = $this->
quoteInto($field .
'=?', $ids[
$i++]);
1441 $whereCond = implode(
' AND ', $where);
1442 $sql = sprintf(
'SELECT COUNT(*) as `cnt` FROM `%s` WHERE %s',
$table, $whereCond);
1447 'DELETE FROM `%s` WHERE %s LIMIT %d',
1465 return $this->selectFactory->create($this);
1482 $value = new \Zend_Db_Expr(
'NULL');
1485 if (
$value instanceof \DateTimeInterface) {
1501 return ($schemaName ? $schemaName .
'.' :
'') .
$tableName;
1513 return sprintf(
'%s_%s_%s', self::DDL_CACHE_PREFIX, $tableKey, $ddlType);
1526 if (!$this->_isDdlCacheAllowed) {
1529 if (isset($this->_ddlCache[$ddlType][$tableCacheKey])) {
1530 return $this->_ddlCache[$ddlType][$tableCacheKey];
1533 if ($this->_cacheAdapter) {
1534 $cacheId = $this->
_getCacheId($tableCacheKey, $ddlType);
1535 $data = $this->_cacheAdapter->load($cacheId);
1536 if (
$data !==
false) {
1537 $data = $this->serializer->unserialize(
$data);
1538 $this->_ddlCache[$ddlType][$tableCacheKey] =
$data;
1556 if (!$this->_isDdlCacheAllowed) {
1559 $this->_ddlCache[$ddlType][$tableCacheKey] =
$data;
1561 if ($this->_cacheAdapter) {
1562 $cacheId = $this->
_getCacheId($tableCacheKey, $ddlType);
1564 $this->_cacheAdapter->save(
$data, $cacheId, [self::DDL_CACHE_TAG]);
1580 if (!$this->_isDdlCacheAllowed) {
1584 $this->_ddlCache = [];
1585 if ($this->_cacheAdapter) {
1592 foreach ($ddlTypes as $ddlType) {
1593 unset($this->_ddlCache[$ddlType][$cacheKey]);
1596 if ($this->_cacheAdapter) {
1597 foreach ($ddlTypes as $ddlType) {
1598 $cacheId = $this->
_getCacheId($cacheKey, $ddlType);
1599 $this->_cacheAdapter->remove($cacheId);
1613 $this->_isDdlCacheAllowed =
false;
1623 $this->_isDdlCacheAllowed =
true;
1658 $ddl = $this->
loadDdlCache($cacheKey, self::DDL_DESCRIBE);
1659 if ($ddl ===
false) {
1660 $ddl = parent::describeTable(
$tableName, $schemaName);
1665 $affected = [
'tinyint',
'smallint',
'mediumint',
'int',
'bigint'];
1666 foreach ($ddl as $key => $columnData) {
1667 if (($columnData[
'DEFAULT'] ===
'') && (array_search($columnData[
'DATA_TYPE'], $affected) !==
false)) {
1668 $ddl[$key][
'DEFAULT'] =
null;
1671 $this->
saveDdlCache($cacheKey, self::DDL_DESCRIBE, $ddl);
1691 if ($columnData[
'IDENTITY'] ===
true) {
1694 if ($columnData[
'UNSIGNED'] ===
true) {
1697 if ($columnData[
'NULLABLE'] ===
false 1702 if ($columnData[
'PRIMARY'] ===
true) {
1708 if (strlen($columnData[
'SCALE']) > 0) {
1709 $options[
'scale'] = $columnData[
'SCALE'];
1711 if (strlen($columnData[
'PRECISION']) > 0) {
1712 $options[
'precision'] = $columnData[
'PRECISION'];
1715 $comment = $this->
string->upperCaseWords($columnData[
'COLUMN_NAME'],
'_',
' ');
1718 'name' => $columnData[
'COLUMN_NAME'],
1720 'length' => $columnData[
'LENGTH'],
1722 'comment' => $comment,
1739 ->setComment($this->string->upperCaseWords($newTableName,
'_',
' '));
1741 foreach ($describe as $columnData) {
1745 $columnInfo[
'name'],
1746 $columnInfo[
'type'],
1747 $columnInfo[
'length'],
1748 $columnInfo[
'options'],
1749 $columnInfo[
'comment']
1754 foreach ($indexes as $indexData) {
1759 if (($indexData[
'KEY_NAME'] ==
'PRIMARY')
1765 $fields = $indexData[
'COLUMNS_LIST'];
1766 $options = [
'type' => $indexData[
'INDEX_TYPE']];
1771 foreach ($foreignKeys as $keyData) {
1774 $keyData[
'COLUMN_NAME'],
1775 $keyData[
'REF_TABLE_NAME'],
1776 $keyData[
'REF_COLUMN_NAME']
1782 $keyData[
'COLUMN_NAME'],
1783 $keyData[
'REF_TABLE_NAME'],
1784 $keyData[
'REF_COLUMN_NAME'],
1791 $table->setOption(
'type', $tableData[
'Engine']);
1808 $definition = array_change_key_case($definition, CASE_UPPER);
1810 if (array_key_exists(
'DEFAULT', $definition) && $definition[
'DEFAULT'] ===
null) {
1811 unset($definition[
'DEFAULT']);
1826 switch ($column[
'DATA_TYPE']) {
1873 $sql = sprintf(
'ALTER TABLE %s ENGINE=%s',
$table, $engine);
1889 $sql = sprintf(
"ALTER TABLE %s COMMENT='%s'",
$table, $comment);
1904 $this->
rawQuery(
"SET @OLD_INSERT_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO'");
1906 $this->
rawQuery(
"SET SQL_MODE=IFNULL(@OLD_INSERT_SQL_MODE,'')");
1929 if (is_array(
$row)) {
1930 $cols = array_keys(
$row);
1932 if (array_diff($cols, array_keys(
$row))) {
1933 throw new \Zend_Db_Exception(
'Invalid data for insert');
1939 $cols = array_keys(
$data);
1949 foreach (
$fields as $k => $v) {
1951 if (!is_numeric($k)) {
1954 $value = $v->__toString();
1955 }
elseif ($v instanceof \
Zend\Db\Sql\Expression) {
1956 $value = $v->getExpression();
1957 }
elseif (is_string($v)) {
1959 }
elseif (is_numeric($v)) {
1962 }
elseif (is_string($v)) {
1968 $updateFields[] = sprintf(
'%s = %s', $field,
$value);
1973 if ($updateFields) {
1974 $insertSql .=
' ON DUPLICATE KEY UPDATE ' . implode(
', ', $updateFields);
1977 $stmt = $this->
query($insertSql, array_values($bind));
1995 if (!is_array(
$row)) {
2000 $cols = array_keys(
$row);
2004 if (array_diff($cols, array_keys(
$row))) {
2005 throw new \Zend_Db_Exception(
'Invalid data for insert');
2007 foreach ($cols as $field) {
2008 $line[] =
$row[$field];
2010 $insertArray[] = $line;
2042 if (is_array(
$row) && $columnsCount != count(
$row)) {
2043 throw new \Zend_Db_Exception(
'Invalid data for insert');
2048 switch ($strategy) {
2071 $this->_cacheAdapter = $cacheAdapter;
2088 if ($schemaName !==
null) {
2089 $table->setSchema($schemaName);
2091 if (isset($this->_config[
'engine'])) {
2092 $table->setOption(
'type', $this->_config[
'engine']);
2109 foreach (
$columns as $columnEntry) {
2110 if (empty($columnEntry[
'COMMENT'])) {
2111 throw new \Zend_Db_Exception(
"Cannot create table without columns comments");
2115 $sqlFragment = array_merge(
2122 "CREATE TABLE IF NOT EXISTS %s (\n%s\n) %s",
2124 implode(
",\n", $sqlFragment),
2125 implode(
" ", $tableOptions)
2129 $result = $this->createConnection()->query($sql);
2149 $sqlFragment = array_merge(
2156 "CREATE TEMPORARY TABLE %s (\n%s\n) %s",
2158 implode(
",\n", $sqlFragment),
2159 implode(
" ", $tableOptions)
2162 return $this->
query($sql);
2175 $ifNotExistsSql = ($ifNotExists ?
'IF NOT EXISTS' :
'');
2178 $sql = sprintf(
'CREATE TEMPORARY TABLE %s %s LIKE %s', $ifNotExistsSql, $temporaryTable, $originTable);
2180 return $this->
query($sql);
2193 if (count($tablePairs) == 0) {
2194 throw new \Zend_Db_Exception(
'Please provide tables for rename');
2199 foreach ($tablePairs as $pair) {
2200 $oldTableName = $pair[
'oldName'];
2201 $newTableName = $pair[
'newName'];
2202 $renamesList[] = sprintf(
'%s TO %s', $oldTableName, $newTableName);
2204 $tablesList[$oldTableName] = $oldTableName;
2205 $tablesList[$newTableName] = $newTableName;
2208 $query = sprintf(
'RENAME TABLE %s', implode(
',', $renamesList));
2211 foreach ($tablesList as
$table) {
2231 throw new \Zend_Db_Exception(
'Table columns are not defined');
2234 foreach (
$columns as $columnData) {
2236 if ($columnData[
'PRIMARY']) {
2237 $primary[$columnData[
'COLUMN_NAME']] = $columnData[
'PRIMARY_POSITION'];
2240 $definition[] = sprintf(
2248 if (!empty($primary)) {
2249 asort($primary, SORT_NUMERIC);
2250 $primary = array_map([$this,
'quoteIdentifier'], array_keys($primary));
2251 $definition[] = sprintf(
' PRIMARY KEY (%s)', implode(
', ', $primary));
2266 $indexes =
$table->getIndexes();
2267 foreach ($indexes as $indexData) {
2268 if (!empty($indexData[
'TYPE'])) {
2273 switch ($indexData[
'TYPE']) {
2275 $indexType =
'PRIMARY KEY';
2276 unset($indexData[
'INDEX_NAME']);
2279 $indexType = strtoupper($indexData[
'TYPE']);
2287 foreach ($indexData[
'COLUMNS'] as $columnData) {
2289 if (!empty($columnData[
'SIZE'])) {
2290 $column .= sprintf(
'(%d)', $columnData[
'SIZE']);
2294 $indexName = isset($indexData[
'INDEX_NAME']) ? $this->
quoteIdentifier($indexData[
'INDEX_NAME']) :
'';
2295 $definition[] = sprintf(
2314 $engineType = strtolower(
$table->getOption(
'type'));
2315 return $engineType ==
'ndb' || $engineType ==
'ndbcluster';
2327 $relations =
$table->getForeignKeys();
2329 if (!empty($relations)) {
2330 foreach ($relations as $fkData) {
2332 $definition[] = sprintf(
2333 ' CONSTRAINT %s FOREIGN KEY (%s) REFERENCES %s (%s) ON DELETE %s',
2356 $comment =
$table->getComment();
2357 if (empty($comment)) {
2358 throw new \Zend_Db_Exception(
'Comment for table is required and must be defined');
2360 $definition[] = $this->
quoteInto(
'COMMENT=?', $comment);
2363 'type' =>
'ENGINE=%s',
2364 'checksum' =>
'CHECKSUM=%d',
2365 'auto_increment' =>
'AUTO_INCREMENT=%d',
2366 'avg_row_length' =>
'AVG_ROW_LENGTH=%d',
2367 'max_rows' =>
'MAX_ROWS=%d',
2368 'min_rows' =>
'MIN_ROWS=%d',
2369 'delay_key_write' =>
'DELAY_KEY_WRITE=%d',
2370 'row_format' =>
'row_format=%s',
2371 'charset' =>
'charset=%s',
2372 'collate' =>
'COLLATE=%s',
2374 foreach ($tableProps as $key =>
$mask) {
2375 $v =
$table->getOption($key);
2377 $definition[] = sprintf(
$mask, $v);
2394 foreach ($columnInfo[
'options'] as $key =>
$value) {
2395 $columnInfo[$key] =
$value;
2424 if ($ddlType ===
null) {
2428 if (empty($ddlType) || !isset($this->_ddlColumnTypes[$ddlType])) {
2429 throw new \Zend_Db_Exception(
'Invalid column definition data');
2433 $cType = $this->_ddlColumnTypes[$ddlType];
2438 if (!empty(
$options[
'UNSIGNED'])) {
2448 if (!empty(
$options[
'LENGTH']) && preg_match(
'#^\(?(\d+),(\d+)\)?$#',
$options[
'LENGTH'], $match)) {
2449 $precision = $match[1];
2456 $precision =
$options[
'PRECISION'];
2459 $cType .= sprintf(
'(%d,%d)', $precision, $scale);
2460 if (!empty(
$options[
'UNSIGNED'])) {
2472 if ($length <= 255) {
2474 $cType = sprintf(
'%s(%d)', $cType, $length);
2475 }
elseif ($length > 255 && $length <= 65536) {
2477 }
elseif ($length > 65536 && $length <= 16777216) {
2485 if (array_key_exists(
'DEFAULT',
$options)) {
2488 if (array_key_exists(
'NULLABLE',
$options)) {
2489 $cNullable = (bool)
$options[
'NULLABLE'];
2499 if ($cDefault !==
null && is_string($cDefault) && strlen($cDefault)) {
2500 $cDefault = str_replace(
"'",
'', $cDefault);
2505 if ($cDefault ===
null) {
2506 $cDefault = new \Zend_Db_Expr(
'NULL');
2508 $cDefault = new \Zend_Db_Expr(
'CURRENT_TIMESTAMP');
2510 $cDefault = new \Zend_Db_Expr(
'0 ON UPDATE CURRENT_TIMESTAMP');
2512 $cDefault = new \Zend_Db_Expr(
'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP');
2513 }
elseif ($cNullable && !$cDefault) {
2514 $cDefault = new \Zend_Db_Expr(
'NULL');
2518 }
elseif ($cDefault ===
null && $cNullable) {
2519 $cDefault = new \Zend_Db_Expr(
'NULL');
2535 '%s%s%s%s%s COMMENT %s %s',
2537 $cUnsigned ?
' UNSIGNED' :
'',
2538 $cNullable ?
' NULL' :
' NOT NULL',
2539 $cDefault !==
false ? $this->
quoteInto(
' default ?', $cDefault) :
'',
2540 $cIdentity ?
' auto_increment' :
'',
2541 $this->
quote($comment),
2558 $this->createConnection()->query(
$query);
2594 throw new \Zend_Db_Exception(sprintf(
'Table "%s" does not exist',
$tableName));
2625 public function renameTable($oldTableName, $newTableName, $schemaName =
null)
2628 throw new \Zend_Db_Exception(sprintf(
'Table "%s" does not exist', $oldTableName));
2631 throw new \Zend_Db_Exception(sprintf(
'Table "%s" already exists', $newTableName));
2634 $oldTable = $this->
_getTableName($oldTableName, $schemaName);
2635 $newTable = $this->
_getTableName($newTableName, $schemaName);
2637 $query = sprintf(
'ALTER TABLE %s RENAME TO %s', $oldTable, $newTable);
2640 $this->createConnection()->query(
$query);
2680 if (isset($keyList[strtoupper($indexName)])) {
2682 $query .=
' DROP PRIMARY KEY,';
2696 'There is no field "%s" that you are trying to create an index on "%s"',
2700 throw new \Zend_Db_Exception($msg);
2704 $fieldSql = implode(
',', $fieldSql);
2706 switch (strtolower($indexType)) {
2708 $condition =
'PRIMARY KEY';
2721 $query .= sprintf(
' ADD %s (%s)', $condition, $fieldSql);
2724 while ($cycle ===
true) {
2728 }
catch (\Exception $e) {
2729 if (in_array(strtolower($indexType), [
'primary',
'unique'])) {
2731 if (preg_match(
'#SQLSTATE\[23000\]: [^:]+: 1062[^\']+\'([\d-\.]+)\'#', $e->getMessage(), $match)) {
2732 $ids = explode(
'-', $match[1]);
2757 $indexType =
'index';
2758 $keyName = strtoupper($keyName);
2759 if (!isset($indexList[$keyName])) {
2763 if ($keyName ==
'PRIMARY') {
2764 $indexType =
'primary';
2765 $cond =
'DROP PRIMARY KEY';
2767 if (strpos($keyName,
'UNQ_') !==
false) {
2768 $indexType =
'unique';
2770 $cond =
'DROP KEY ' . $this->
quoteIdentifier($indexList[$keyName][
'KEY_NAME']);
2774 'ALTER TABLE %s %s',
2809 $refSchemaName =
null 2818 'ALTER TABLE %s ADD CONSTRAINT %s FOREIGN KEY (%s) REFERENCES %s (%s)',
2826 if ($onDelete !==
null) {
2827 $query .=
' ON DELETE ' . strtoupper($onDelete);
2853 $date = $this->dateTime->formatDate($date, $includeTime);
2855 if ($date ===
null) {
2856 return new \Zend_Db_Expr(
'NULL');
2859 return new \Zend_Db_Expr($this->
quote($date));
2869 $this->
rawQuery(
"SET SQL_MODE=''");
2870 $this->
rawQuery(
"SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0");
2871 $this->
rawQuery(
"SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO'");
2883 $this->
rawQuery(
"SET SQL_MODE=IFNULL(@OLD_SQL_MODE,'')");
2884 $this->
rawQuery(
"SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS=0, 0, 1)");
2922 $conditionKeyMap = [
2923 'eq' =>
"{{fieldName}} = ?",
2924 'neq' =>
"{{fieldName}} != ?",
2925 'like' =>
"{{fieldName}} LIKE ?",
2926 'nlike' =>
"{{fieldName}} NOT LIKE ?",
2927 'in' =>
"{{fieldName}} IN(?)",
2928 'nin' =>
"{{fieldName}} NOT IN(?)",
2929 'is' =>
"{{fieldName}} IS ?",
2930 'notnull' =>
"{{fieldName}} IS NOT NULL",
2931 'null' =>
"{{fieldName}} IS NULL",
2932 'gt' =>
"{{fieldName}} > ?",
2933 'lt' =>
"{{fieldName}} < ?",
2934 'gteq' =>
"{{fieldName}} >= ?",
2935 'lteq' =>
"{{fieldName}} <= ?",
2936 'finset' =>
"FIND_IN_SET(?, {{fieldName}})",
2937 'regexp' =>
"{{fieldName}} REGEXP ?",
2938 'from' =>
"{{fieldName}} >= ?",
2939 'to' =>
"{{fieldName}} <= ?",
2942 'ntoa' =>
"INET_NTOA({{fieldName}}) LIKE ?",
2946 if (is_array($condition)) {
2947 $key = key(array_intersect_key($condition, $conditionKeyMap));
2949 if (isset($condition[
'from']) || isset($condition[
'to'])) {
2950 if (isset($condition[
'from'])) {
2955 if (isset($condition[
'to'])) {
2960 }
elseif (array_key_exists($key, $conditionKeyMap)) {
2961 $value = $condition[$key];
2962 if (($key ==
'seq') || ($key ==
'sneq')) {
2965 if (($key ==
'in' || $key ==
'nin') && is_string(
$value)) {
2971 foreach ($condition as $orCondition) {
2975 $query = sprintf(
'(%s)', implode(
' OR ', $queries));
2995 $sql = str_replace(
'{{fieldName}}', $fieldName, $sql);
3012 return ($conditionKey ==
'seq') ?
'null' :
'notnull';
3014 return ($conditionKey ==
'seq') ?
'eq' :
'neq';
3039 if (!isset($column[
'DATA_TYPE'])) {
3044 if (
$value ===
null && $column[
'NULLABLE']) {
3048 switch ($column[
'DATA_TYPE']) {
3054 if (!is_integer(
$value)) {
3062 if (isset($column[
'SCALE'])) {
3063 $scale = $column[
'SCALE'];
3065 if (isset($column[
'PRECISION'])) {
3066 $precision = $column[
'PRECISION'];
3068 $format = sprintf(
'%%%d.%dF', $precision - $scale, $scale);
3089 if ($column[
'NULLABLE'] &&
$value ==
'') {
3116 $expression = sprintf(
"IF((%s), %s, %s)", $expression, $true, $false);
3118 $expression = sprintf(
"IF(%s, %s, %s)", $expression, $true, $false);
3121 return new \Zend_Db_Expr($expression);
3134 $expression = sprintf(
"IFNULL((%s), %s)", $expression,
$value);
3136 $expression = sprintf(
"IFNULL(%s, %s)", $expression,
$value);
3139 return new \Zend_Db_Expr($expression);
3151 public function getCaseSql($valueName, $casesResults, $defaultValue =
null)
3153 $expression =
'CASE ' . $valueName;
3157 if ($defaultValue !==
null) {
3158 $expression .=
' ELSE ' . $defaultValue;
3160 $expression .=
' END';
3162 return new \Zend_Db_Expr($expression);
3175 $format = empty($separator) ?
'CONCAT(%s)' :
"CONCAT_WS('{$separator}', %s)";
3176 return new \Zend_Db_Expr(sprintf(
$format, implode(
', ',
$data)));
3188 return new \Zend_Db_Expr(sprintf(
'LENGTH(%s)',
$string));
3201 return new \Zend_Db_Expr(sprintf(
'LEAST(%s)', implode(
', ',
$data)));
3214 return new \Zend_Db_Expr(sprintf(
'GREATEST(%s)', implode(
', ',
$data)));
3227 if (!isset($this->_intervalUnits[$unit])) {
3228 throw new \Zend_Db_Exception(sprintf(
'Undefined interval unit "%s" specified', $unit));
3231 return sprintf(
'INTERVAL %d %s', $interval, $this->_intervalUnits[$unit]);
3247 return new \Zend_Db_Expr($expr);
3263 return new \Zend_Db_Expr($expr);
3284 $expr = sprintf(
"DATE_FORMAT(%s, '%s')", $date,
$format);
3285 return new \Zend_Db_Expr($expr);
3296 return new \Zend_Db_Expr(sprintf(
'DATE(%s)', $date));
3309 if ($len ===
null) {
3310 return new \Zend_Db_Expr(sprintf(
'SUBSTRING(%s, %s)', $stringExpression,
$pos));
3312 return new \Zend_Db_Expr(sprintf(
'SUBSTRING(%s, %s, %s)', $stringExpression,
$pos, $len));
3323 return new \Zend_Db_Expr(sprintf(
'STDDEV_SAMP(%s)', $expressionField));
3338 if (!isset($this->_intervalUnits[$unit])) {
3339 throw new \Zend_Db_Exception(sprintf(
'Undefined interval unit "%s" specified', $unit));
3342 $expr = sprintf(
'EXTRACT(%s FROM %s)', $this->_intervalUnits[$unit], $date);
3343 return new \Zend_Db_Expr($expr);
3369 $triggerName =
'trg_' .
$tableName .
'_' . $time .
'_' . $event;
3388 switch (strtolower($indexType)) {
3415 $fkName = sprintf(
'%s_%s_%s_%s', $priTableName, $priColumnName, $refTableName, $refColumnName);
3462 $query =
$mode === self::REPLACE ?
'REPLACE' :
'INSERT';
3464 if (
$mode === self::INSERT_IGNORE) {
3475 if (
$mode === self::INSERT_ON_DUPLICATE) {
3493 foreach ($describe as $column) {
3494 if ($column[
'PRIMARY'] ===
false) {
3495 $fields[] = $column[
'COLUMN_NAME'];
3501 $update[] = sprintf(
'%1$s = VALUES(%1$s)', $this->
quoteIdentifier($field));
3504 return count($update) ?
' ON DUPLICATE KEY UPDATE ' . join(
', ', $update) :
'';
3519 $iterator = $this->getQueryGenerator()->generate($rangeField,
$select, $stepCount);
3533 private function getQueryGenerator()
3535 if ($this->queryGenerator ===
null) {
3538 return $this->queryGenerator;
3558 $keys = array_keys(
$table);
3559 $tableAlias = $keys[0];
3570 $joinType = strtoupper($joinProp[
'joinType']);
3573 if ($joinProp[
'schema'] !==
null) {
3574 $joinTable = sprintf(
'%s.', $this->
quoteIdentifier($joinProp[
'schema']));
3576 $joinTable .= $this->
quoteTableAs($joinProp[
'tableName'], $correlationName);
3578 $join = sprintf(
' %s %s', $joinType, $joinTable);
3580 if (!empty($joinProp[
'joinCondition'])) {
3581 $join = sprintf(
'%s ON %s', $join, $joinProp[
'joinCondition']);
3584 $joinConds[] = $join;
3588 $query = sprintf(
"%s\n%s",
$query, implode(
"\n", $joinConds));
3594 list($correlationName, $column,
$alias) = $columnEntry;
3598 if (!$column instanceof \
Zend_Db_Expr && !empty($correlationName)) {
3606 new \
Magento\Framework\
Phrase(
'The columns for UPDATE statement are not defined')
3615 $query = sprintf(
"%s\nWHERE %s",
$query, implode(
' ', $wherePart));
3649 $tableNames = is_array($tableNames) ? $tableNames : [$tableNames];
3680 if ($field !==
null) {
3681 $expression = new \Zend_Db_Expr(sprintf(
'RAND() * %s', $this->
quoteIdentifier($field)));
3682 $select->columns([
'mage_rand' => $expression]);
3683 $spec = new \Zend_Db_Expr(
'mage_rand');
3685 $spec = new \Zend_Db_Expr(
'RAND()');
3700 return sprintf(
'%s FOR UPDATE', $sql);
3716 $line[] =
$value->__toString();
3722 $line = implode(
', ', $line);
3724 return sprintf(
'(%s)', $line);
3739 $columns = array_map([$this,
'quoteIdentifier'],
$columns);
3742 $strategy = $strategy === self::INSERT_IGNORE ?
'IGNORE' :
'';
3761 $columns = array_map([$this,
'quoteIdentifier'],
$columns);
3782 $ddlType =
$options[
'COLUMN_TYPE'];
3817 if (empty($condition[
'date'])) {
3818 if (empty($condition[
'datetime'])) {
3840 if (isset($indexes[
'PRIMARY'])) {
3841 return $indexes[
'PRIMARY'][
'KEY_NAME'];
3856 $size = trim($size);
3857 $last = strtolower(substr($size, -1));
3861 $size = intval($size) * 1024;
3864 $size = intval($size) * 1024 * 1024;
3867 $size = intval($size) * 1024 * 1024 * 1024;
3878 return intval($size);
3903 throw new \Zend_Db_Exception(
3905 'Trigger %1 has not statements available',
3911 $statements = implode(
"\n",
$trigger->getStatements());
3914 "CREATE TRIGGER %s %s %s ON %s FOR EACH ROW\nBEGIN\n%s\nEND",
3935 if (empty($triggerName)) {
3936 throw new \InvalidArgumentException((
string)
new \
Magento\Framework\
Phrase(
'Trigger name is not defined'));
3939 $triggerName = ($schemaName ? $schemaName .
'.' :
'') . $triggerName;
3941 $sql =
'DROP TRIGGER IF EXISTS ' . $this->
quoteIdentifier($triggerName);
3954 if ($this->_transactionLevel > 0) {
3955 trigger_error(
'Some transactions have not been committed or rolled back', E_USER_ERROR);
3967 $sql = ($likeCondition ===
null) ?
'SHOW TABLES' : sprintf(
"SHOW TABLES LIKE '%s'", $likeCondition);
3988 if ($indexName && count($indexes[$indexName][
'COLUMNS_LIST']) == 1) {
3989 return current($indexes[$indexName][
'COLUMNS_LIST']);
4003 if ($this->schemaListener ===
null) {
4006 return $this->schemaListener;
_checkDdlTransaction($sql)
newTable($tableName=null, $schemaName=null)
createTableByDdl($tableName, $newTableName)
changeColumn( $tableName, $oldColumnName, $newColumnName, $definition, $flushData=false, $schemaName=null)
_prepareQuery(&$sql, &$bind=[])
addForeignKey( $fkName, $tableName, $columnName, $refTableName, $refColumnName, $onDelete=AdapterInterface::FK_ACTION_CASCADE, $purge=false, $schemaName=null, $refSchemaName=null)
_getIndexesDefinition(Table $table)
isTableExists($tableName, $schemaName=null)
rawFetchRow($sql, $field=null)
getTables($likeCondition=null)
getCheckSql($expression, $true, $false)
elseif(isset( $params[ 'redirect_parent']))
disableTableKeys($tableName, $schemaName=null)
quoteInto($text, $value, $type=null, $count=null)
getPrimaryKeyName($tableName, $schemaName=null)
prepareColumnValue(array $column, $value)
getColumnDefinitionFromDescribe($options, $ddlType=null)
const FK_ACTION_NO_ACTION
getSubstringSql($stringExpression, $pos, $len=null)
prepareSqlCondition($fieldName, $condition)
static shortenEntityName($entityName, $prefix)
getDateFormatSql($date, $format)
selectsByRange($rangeField, \Magento\Framework\DB\Select $select, $stepCount=100)
insertFromSelect(Select $select, $table, array $fields=[], $mode=false)
renameTablesBatch(array $tablePairs)
addColumn($tableName, $columnName, $definition, $schemaName=null)
insertArray($table, array $columns, array $data, $strategy=0)
createTrigger(\Magento\Framework\DB\Ddl\Trigger $trigger)
purgeOrphanRecords( $tableName, $columnName, $refTableName, $refColumnName, $onDelete=AdapterInterface::FK_ACTION_CASCADE)
modifyColumnByDdl($tableName, $columnName, $definition, $flushData=false, $schemaName=null)
getIfNullSql($expression, $value=0)
getIndexName($tableName, $fields, $indexType='')
setCacheAdapter(FrontendInterface $cacheAdapter)
const TIMESTAMP_INIT_UPDATE
_getForeignKeysDefinition(Table $table)
_getColumnTypeByDdl($column)
convertDateTime($datetime)
getColumnCreateByDescribe($columnData)
resetDdlCache($tableName=null, $schemaName=null)
showTableStatus($tableName, $schemaName=null)
dropIndex($tableName, $keyName, $schemaName=null)
quoteTableAs($ident, $alias=null, $auto=false)
_getReplaceSqlQuery($tableName, array $columns, array $values)
getStandardDeviationSql($expressionField)
createTemporaryTableLike($temporaryTableName, $originTableName, $ifNotExists=false)
_transformStringSqlCondition($conditionKey, $value)
deleteFromSelect(Select $select, $table)
const ERROR_ASYMMETRIC_ROLLBACK_MESSAGE
insert($table, array $bind)
getDateAddSql($date, $interval, $unit)
getForeignKeys($tableName, $schemaName=null)
renameTable($oldTableName, $newTableName, $schemaName=null)
getAutoIncrementField($tableName, $schemaName=null)
const ERROR_ASYMMETRIC_COMMIT_MESSAGE
createTable(Table $table)
const INDEX_TYPE_FULLTEXT
_removeDuplicateEntry($table, $fields, $ids)
if($exist=($block->getProductCollection() && $block->getProductCollection() ->getSize())) $mode
getDateExtractSql($date, $unit)
changeTableEngine($tableName, $engine, $schemaName=null)
saveDdlCache($tableCacheKey, $ddlType, $data)
_prepareInsertData($row, &$bind)
loadDdlCache($tableCacheKey, $ddlType)
getTriggerName($tableName, $time, $event)
proccessBindCallback($matches)
_getColumnDefinition($options, $ddlType=null)
const ERROR_ROLLBACK_INCOMPLETE_MESSAGE
fetchRow($sql, $bind=[], $fetchMode=null)
tableColumnExists($tableName, $columnName, $schemaName=null)
_prepareQuotedSqlCondition($text, $value, $fieldName)
getTablesChecksum($tableNames, $schemaName=null)
dropTrigger($triggerName, $schemaName=null)
changeTableComment($tableName, $comment, $schemaName=null)
insertOnDuplicate($table, array $data, array $fields=[])
_getIntervalUnitSql($interval, $unit)
getDateSubSql($date, $interval, $unit)
_getInsertSqlQuery($tableName, array $columns, array $values, $strategy=null)
modifyColumn($tableName, $columnName, $definition, $flushData=false, $schemaName=null)
dropTemporaryTable($tableName, $schemaName=null)
const LENGTH_FOREIGN_NAME
dropColumn($tableName, $columnName, $schemaName=null)
getForeignKeyName($priTableName, $priColumnName, $refTableName, $refColumnName)
enableTableKeys($tableName, $schemaName=null)
orderRand(Select $select, $field=null)
dropForeignKey($tableName, $fkName, $schemaName=null)
_getIndexByColumns($tableName, array $columns, $schemaName)
insertForce($table, array $bind)
const MAX_CONNECTION_RETRIES
_getColumnsDefinition(Table $table)
const CLEANING_MODE_MATCHING_TAG
quoteIdentifier($ident, $auto=false)
_getTableName($tableName, $schemaName=null)
quote($value, $type=null)
getCreateTable($tableName, $schemaName=null)
formatDate($date, $includeTime=true)
getCaseSql($valueName, $casesResults, $defaultValue=null)
updateFromSelect(Select $select, $table)
dropTable($tableName, $schemaName=null)
getIndexList($tableName, $schemaName=null)
describeTable($tableName, $schemaName=null)
_convertMixedBind(&$sql, &$bind)
fetchAll($sql, $bind=[], $fetchMode=null)
insertMultiple($table, array $data)
getConcatSql(array $data, $separator=null)
getGreatestSql(array $data)
createTemporaryTable(\Magento\Framework\DB\Ddl\Table $table)
addIndex( $tableName, $indexName, $fields, $indexType=AdapterInterface::INDEX_TYPE_INDEX, $schemaName=null)
_getOptionsDefinition(Table $table)
_prepareSqlDateCondition($condition, $key)
truncateTable($tableName, $schemaName=null)
_getCacheId($tableKey, $ddlType)
__construct(StringUtils $string, DateTime $dateTime, LoggerInterface $logger, SelectFactory $selectFactory, array $config=[], SerializerInterface $serializer=null)
multiQuery($sql, $bind=[])