Magento Extensions Rating 2024
Magento 2 Documentation  2.3
Documentation for Magento 2 CMS v2.3 (December 2018)
Mysql.php
Go to the documentation of this file.
1 <?php
7 namespace Magento\Framework\DB\Adapter\Pdo;
8 
9 use Magento\Framework\App\ObjectManager;
10 use Magento\Framework\Cache\FrontendInterface;
11 use Magento\Framework\DB\Adapter\AdapterInterface;
12 use Magento\Framework\DB\Adapter\ConnectionException;
13 use Magento\Framework\DB\Adapter\DeadlockException;
14 use Magento\Framework\DB\Adapter\DuplicateException;
15 use Magento\Framework\DB\Adapter\LockWaitException;
16 use Magento\Framework\DB\Ddl\Table;
17 use Magento\Framework\DB\ExpressionConverter;
18 use Magento\Framework\DB\LoggerInterface;
19 use Magento\Framework\DB\Profiler;
20 use Magento\Framework\DB\Query\Generator as QueryGenerator;
21 use Magento\Framework\DB\Select;
22 use Magento\Framework\DB\SelectFactory;
23 use Magento\Framework\DB\Statement\Parameter;
24 use Magento\Framework\Exception\LocalizedException;
25 use Magento\Framework\Phrase;
26 use Magento\Framework\Serialize\SerializerInterface;
27 use Magento\Framework\Stdlib\DateTime;
28 use Magento\Framework\Stdlib\StringUtils;
29 use Magento\Framework\Setup\SchemaListener;
30 
31 // @codingStandardsIgnoreStart
32 
43 class Mysql extends \Zend_Db_Adapter_Pdo_Mysql implements AdapterInterface
44 {
45  // @codingStandardsIgnoreEnd
46 
47  const TIMESTAMP_FORMAT = 'Y-m-d H:i:s';
48  const DATETIME_FORMAT = 'Y-m-d H:i:s';
49  const DATE_FORMAT = 'Y-m-d';
50 
51  const DDL_DESCRIBE = 1;
52  const DDL_CREATE = 2;
53  const DDL_INDEX = 3;
54  const DDL_FOREIGN_KEY = 4;
55  const DDL_CACHE_PREFIX = 'DB_PDO_MYSQL_DDL';
56  const DDL_CACHE_TAG = 'DB_PDO_MYSQL_DDL';
57 
58  const LENGTH_TABLE_NAME = 64;
59  const LENGTH_INDEX_NAME = 64;
60  const LENGTH_FOREIGN_NAME = 64;
61 
65  const ENGINE_MEMORY = 'MEMORY';
66 
70  const MAX_CONNECTION_RETRIES = 10;
71 
77  protected $_defaultStmtClass = \Magento\Framework\DB\Statement\Pdo\Mysql::class;
78 
84  protected $_transactionLevel = 0;
85 
91  protected $_isRolledBack = false;
92 
98  protected $_connectionFlagsSet = false;
99 
105  protected $_ddlCache = [];
106 
112  protected $_bindParams = [];
113 
119  protected $_bindIncrement = 0;
120 
126  protected $_cacheAdapter;
127 
132  protected $_isDdlCacheAllowed = true;
133 
139  protected $_ddlColumnTypes = [
140  Table::TYPE_BOOLEAN => 'bool',
141  Table::TYPE_SMALLINT => 'smallint',
142  Table::TYPE_INTEGER => 'int',
143  Table::TYPE_BIGINT => 'bigint',
144  Table::TYPE_FLOAT => 'float',
145  Table::TYPE_DECIMAL => 'decimal',
146  Table::TYPE_NUMERIC => 'decimal',
147  Table::TYPE_DATE => 'date',
148  Table::TYPE_TIMESTAMP => 'timestamp',
149  Table::TYPE_DATETIME => 'datetime',
150  Table::TYPE_TEXT => 'text',
151  Table::TYPE_BLOB => 'blob',
152  Table::TYPE_VARBINARY => 'blob',
153  ];
154 
161  protected $_ddlRoutines = ['alt', 'cre', 'ren', 'dro', 'tru'];
162 
168  protected $_intervalUnits = [
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',
175  ];
176 
182  protected $_queryHook = null;
183 
187  protected $string;
188 
192  protected $dateTime;
193 
198  protected $selectFactory;
199 
203  protected $logger;
204 
210  private $exceptionMap;
211 
215  private $queryGenerator;
216 
220  private $serializer;
221 
225  private $schemaListener;
226 
237  public function __construct(
238  StringUtils $string,
239  DateTime $dateTime,
240  LoggerInterface $logger,
241  SelectFactory $selectFactory,
242  array $config = [],
243  SerializerInterface $serializer = null
244  ) {
245  $this->string = $string;
246  $this->dateTime = $dateTime;
247  $this->logger = $logger;
248  $this->selectFactory = $selectFactory;
249  $this->serializer = $serializer ?: ObjectManager::getInstance()->get(SerializerInterface::class);
250  $this->exceptionMap = [
251  // SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
252  2006 => ConnectionException::class,
253  // SQLSTATE[HY000]: General error: 2013 Lost connection to MySQL server during query
254  2013 => ConnectionException::class,
255  // SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded
256  1205 => LockWaitException::class,
257  // SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock
258  1213 => DeadlockException::class,
259  // SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry
260  1062 => DuplicateException::class,
261  ];
262  try {
263  parent::__construct($config);
264  } catch (\Zend_Db_Adapter_Exception $e) {
265  throw new \InvalidArgumentException($e->getMessage(), $e->getCode(), $e);
266  }
267  }
268 
275  public function beginTransaction()
276  {
277  if ($this->_isRolledBack) {
278  throw new \Exception(AdapterInterface::ERROR_ROLLBACK_INCOMPLETE_MESSAGE);
279  }
280  if ($this->_transactionLevel === 0) {
281  $this->logger->startTimer();
282  parent::beginTransaction();
283  $this->logger->logStats(LoggerInterface::TYPE_TRANSACTION, 'BEGIN');
284  }
285  ++$this->_transactionLevel;
286  return $this;
287  }
288 
295  public function commit()
296  {
297  if ($this->_transactionLevel === 1 && !$this->_isRolledBack) {
298  $this->logger->startTimer();
299  parent::commit();
300  $this->logger->logStats(LoggerInterface::TYPE_TRANSACTION, 'COMMIT');
301  } elseif ($this->_transactionLevel === 0) {
302  throw new \Exception(AdapterInterface::ERROR_ASYMMETRIC_COMMIT_MESSAGE);
303  } elseif ($this->_isRolledBack) {
304  throw new \Exception(AdapterInterface::ERROR_ROLLBACK_INCOMPLETE_MESSAGE);
305  }
306  --$this->_transactionLevel;
307  return $this;
308  }
309 
316  public function rollBack()
317  {
318  if ($this->_transactionLevel === 1) {
319  $this->logger->startTimer();
320  parent::rollBack();
321  $this->_isRolledBack = false;
322  $this->logger->logStats(LoggerInterface::TYPE_TRANSACTION, 'ROLLBACK');
323  } elseif ($this->_transactionLevel === 0) {
324  throw new \Exception(AdapterInterface::ERROR_ASYMMETRIC_ROLLBACK_MESSAGE);
325  } else {
326  $this->_isRolledBack = true;
327  }
328  --$this->_transactionLevel;
329  return $this;
330  }
331 
337  public function getTransactionLevel()
338  {
339  return $this->_transactionLevel;
340  }
341 
348  public function convertDate($date)
349  {
350  return $this->formatDate($date, false);
351  }
352 
359  public function convertDateTime($datetime)
360  {
361  return $this->formatDate($datetime, true);
362  }
363 
374  protected function _connect()
375  {
376  if ($this->_connection) {
377  return;
378  }
379 
380  if (!extension_loaded('pdo_mysql')) {
381  throw new \Zend_Db_Adapter_Exception('pdo_mysql extension is not installed');
382  }
383 
384  if (!isset($this->_config['host'])) {
385  throw new \Zend_Db_Adapter_Exception('No host configured to connect');
386  }
387 
388  if (isset($this->_config['port'])) {
389  throw new \Zend_Db_Adapter_Exception('Port must be configured within host parameter (like localhost:3306');
390  }
391 
392  unset($this->_config['port']);
393 
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']);
399  }
400 
401  if (!isset($this->_config['driver_options'][\PDO::MYSQL_ATTR_MULTI_STATEMENTS])) {
402  $this->_config['driver_options'][\PDO::MYSQL_ATTR_MULTI_STATEMENTS] = false;
403  }
404 
405  $this->logger->startTimer();
406  parent::_connect();
407  $this->logger->logStats(LoggerInterface::TYPE_CONNECT, '');
408 
410  $this->_connection->query("SET SQL_MODE=''");
411 
412  // As we use default value CURRENT_TIMESTAMP for TIMESTAMP type columns we need to set GMT timezone
413  $this->_connection->query("SET time_zone = '+00:00'");
414 
415  if (isset($this->_config['initStatements'])) {
416  $statements = $this->_splitMultiQuery($this->_config['initStatements']);
417  foreach ($statements as $statement) {
418  $this->_query($statement);
419  }
420  }
421 
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);
426  } else {
427  $this->_connection->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
428  }
429  $this->_connectionFlagsSet = true;
430  }
431  }
432 
438  private function createConnection()
439  {
440  $connection = new \PDO(
441  $this->_dsn(),
442  $this->_config['username'],
443  $this->_config['password'],
444  $this->_config['driver_options']
445  );
446  return $connection;
447  }
448 
456  public function rawQuery($sql)
457  {
458  try {
459  $result = $this->query($sql);
460  } catch (\Zend_Db_Statement_Exception $e) {
461  // Convert to \PDOException to maintain backwards compatibility with usage of MySQL adapter
462  $e = $e->getPrevious();
463  if (!($e instanceof \PDOException)) {
464  $e = new \PDOException($e->getMessage(), $e->getCode());
465  }
466  throw $e;
467  }
468 
469  return $result;
470  }
471 
479  public function rawFetchRow($sql, $field = null)
480  {
481  $result = $this->rawQuery($sql);
482  if (!$result) {
483  return false;
484  }
485 
486  $row = $result->fetch(\PDO::FETCH_ASSOC);
487  if (!$row) {
488  return false;
489  }
490 
491  if (empty($field)) {
492  return $row;
493  } else {
494  return $row[$field] ?? false;
495  }
496  }
497 
505  protected function _checkDdlTransaction($sql)
506  {
507  if ($this->getTransactionLevel() > 0) {
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) {
512  throw new ConnectionException(AdapterInterface::ERROR_DDL_MESSAGE, E_USER_ERROR);
513  }
514  }
515  }
516 
528  protected function _query($sql, $bind = [])
529  {
530  $connectionErrors = [
531  2006, // SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
532  2013, // SQLSTATE[HY000]: General error: 2013 Lost connection to MySQL server during query
533  ];
534  $triesCount = 0;
535  do {
536  $retry = false;
537  $this->logger->startTimer();
538  try {
539  $this->_checkDdlTransaction($sql);
540  $this->_prepareQuery($sql, $bind);
541  $result = parent::query($sql, $bind);
542  $this->logger->logStats(LoggerInterface::TYPE_QUERY, $sql, $bind, $result);
543  return $result;
544  } catch (\Exception $e) {
545  // Finalize broken query
546  $profiler = $this->getProfiler();
547  if ($profiler instanceof Profiler) {
549  $profiler->queryEndLast();
550  }
551 
553  $pdoException = null;
554  if ($e instanceof \PDOException) {
555  $pdoException = $e;
556  } elseif (($e instanceof \Zend_Db_Statement_Exception)
557  && ($e->getPrevious() instanceof \PDOException)
558  ) {
559  $pdoException = $e->getPrevious();
560  }
561 
562  // Check to reconnect
563  if ($pdoException && $triesCount < self::MAX_CONNECTION_RETRIES
564  && in_array($pdoException->errorInfo[1], $connectionErrors)
565  ) {
566  $retry = true;
567  $triesCount++;
568  $this->closeConnection();
569 
573  if (!empty($this->_config['port'])) {
574  $this->_config['host'] = implode(':', [$this->_config['host'], $this->_config['port']]);
575  unset($this->_config['port']);
576  }
577 
578  $this->_connect();
579  }
580 
581  if (!$retry) {
582  $this->logger->logStats(LoggerInterface::TYPE_QUERY, $sql, $bind);
583  $this->logger->critical($e);
584  // rethrow custom exception if needed
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;
590  }
591  throw $e;
592  }
593  }
594  } while ($retry);
595  }
596 
608  public function query($sql, $bind = [])
609  {
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.")
613  );
614  }
615  return $this->_query($sql, $bind);
616  }
617 
633  public function multiQuery($sql, $bind = [])
634  {
635  return $this->_query($sql, $bind);
636  }
637 
647  protected function _prepareQuery(&$sql, &$bind = [])
648  {
649  $sql = (string) $sql;
650  if (!is_array($bind)) {
651  $bind = [$bind];
652  }
653 
654  // Mixed bind is not supported - so remember whether it is named bind, to normalize later if required
655  $isNamedBind = false;
656  if ($bind) {
657  foreach ($bind as $k => $v) {
658  if (!is_int($k)) {
659  $isNamedBind = true;
660  if ($k[0] != ':') {
661  $bind[":{$k}"] = $v;
662  unset($bind[$k]);
663  }
664  }
665  }
666  }
667 
668  // Special query hook
669  if ($this->_queryHook) {
670  $object = $this->_queryHook['object'];
671  $method = $this->_queryHook['method'];
672  $object->$method($sql, $bind);
673  }
674 
675  return $this;
676  }
677 
687  public function proccessBindCallback($matches)
688  {
689  if (isset($matches[6]) && (
690  strpos($matches[6], "'") !== false ||
691  strpos($matches[6], ':') !== false ||
692  strpos($matches[6], '?') !== false)
693  ) {
694  $bindName = ':_mage_bind_var_' . (++$this->_bindIncrement);
695  $this->_bindParams[$bindName] = $this->_unQuote($matches[6]);
696  return ' ' . $bindName;
697  }
698  return $matches[0];
699  }
700 
707  protected function _unQuote($string)
708  {
709  $translate = [
710  "\\000" => "\000",
711  "\\n" => "\n",
712  "\\r" => "\r",
713  "\\\\" => "\\",
714  "\'" => "'",
715  "\\\"" => "\"",
716  "\\032" => "\032",
717  ];
718  return strtr($string, $translate);
719  }
720 
729  protected function _convertMixedBind(&$sql, &$bind)
730  {
731  $positions = [];
732  $offset = 0;
733  // get positions
734  while (true) {
735  $pos = strpos($sql, '?', $offset);
736  if ($pos !== false) {
737  $positions[] = $pos;
738  $offset = ++$pos;
739  } else {
740  break;
741  }
742  }
743 
744  $bindResult = [];
745  $map = [];
746  foreach ($bind as $k => $v) {
747  // positional
748  if (is_int($k)) {
749  if (!isset($positions[$k])) {
750  continue;
751  }
752  $bindResult[$positions[$k]] = $v;
753  } else {
754  $offset = 0;
755  while (true) {
756  $pos = strpos($sql, $k, $offset);
757  if ($pos === false) {
758  break;
759  } else {
760  $offset = $pos + strlen($k);
761  $bindResult[$pos] = $v;
762  }
763  }
764  $map[$k] = '?';
765  }
766  }
767 
768  ksort($bindResult);
769  $bind = array_values($bindResult);
770  $sql = strtr($sql, $map);
771 
772  return $this;
773  }
774 
784  public function setQueryHook($hook)
785  {
786  $prev = $this->_queryHook;
787  $this->_queryHook = $hook;
788  return $prev;
789  }
790 
800  protected function _splitMultiQuery($sql)
801  {
802  $parts = preg_split(
803  '#(;|\'|"|\\\\|//|--|\n|/\*|\*/)#',
804  $sql,
805  null,
806  PREG_SPLIT_NO_EMPTY | PREG_SPLIT_DELIM_CAPTURE
807  );
808 
809  $q = false;
810  $c = false;
811  $stmts = [];
812  $s = '';
813 
814  foreach ($parts as $i => $part) {
815  // strings
816  if (($part === "'" || $part === '"') && ($i === 0 || $parts[$i-1] !== '\\')) {
817  if ($q === false) {
818  $q = $part;
819  } elseif ($q === $part) {
820  $q = false;
821  }
822  }
823 
824  // single line comments
825  if (($part === '//' || $part === '--') && ($i === 0 || $parts[$i-1] === "\n")) {
826  $c = $part;
827  } elseif ($part === "\n" && ($c === '//' || $c === '--')) {
828  $c = false;
829  }
830 
831  // multi line comments
832  if ($part === '/*' && $c === false) {
833  $c = '/*';
834  } elseif ($part === '*/' && $c === '/*') {
835  $c = false;
836  }
837 
838  // statements
839  if ($part === ';' && $q === false && $c === false) {
840  if (trim($s) !== '') {
841  $stmts[] = trim($s);
842  $s = '';
843  }
844  } else {
845  $s .= $part;
846  }
847  }
848  if (trim($s) !== '') {
849  $stmts[] = trim($s);
850  }
851 
852  return $stmts;
853  }
854 
863  public function dropForeignKey($tableName, $fkName, $schemaName = null)
864  {
865  $foreignKeys = $this->getForeignKeys($tableName, $schemaName);
866  $fkName = strtoupper($fkName);
867  if (substr($fkName, 0, 3) == 'FK_') {
868  $fkName = substr($fkName, 3);
869  }
870  foreach ([$fkName, 'FK_' . $fkName] as $key) {
871  if (isset($foreignKeys[$key])) {
872  $sql = sprintf(
873  'ALTER TABLE %s DROP FOREIGN KEY %s',
874  $this->quoteIdentifier($this->_getTableName($tableName, $schemaName)),
875  $this->quoteIdentifier($foreignKeys[$key]['FK_NAME'])
876  );
877  $this->resetDdlCache($tableName, $schemaName);
878  $this->rawQuery($sql);
879  $this->getSchemaListener()->dropForeignKey($tableName, $fkName);
880  }
881  }
882  return $this;
883  }
884 
895  public function purgeOrphanRecords(
896  $tableName,
897  $columnName,
898  $refTableName,
899  $refColumnName,
900  $onDelete = AdapterInterface::FK_ACTION_CASCADE
901  ) {
902  $onDelete = strtoupper($onDelete);
903  if ($onDelete == AdapterInterface::FK_ACTION_CASCADE
904  || $onDelete == AdapterInterface::FK_ACTION_RESTRICT
905  ) {
906  $sql = sprintf(
907  "DELETE p.* FROM %s AS p LEFT JOIN %s AS r ON p.%s = r.%s WHERE r.%s IS NULL",
908  $this->quoteIdentifier($tableName),
909  $this->quoteIdentifier($refTableName),
910  $this->quoteIdentifier($columnName),
911  $this->quoteIdentifier($refColumnName),
912  $this->quoteIdentifier($refColumnName)
913  );
914  $this->rawQuery($sql);
915  } elseif ($onDelete == AdapterInterface::FK_ACTION_SET_NULL) {
916  $sql = sprintf(
917  "UPDATE %s AS p LEFT JOIN %s AS r ON p.%s = r.%s SET p.%s = NULL WHERE r.%s IS NULL",
918  $this->quoteIdentifier($tableName),
919  $this->quoteIdentifier($refTableName),
920  $this->quoteIdentifier($columnName),
921  $this->quoteIdentifier($refColumnName),
922  $this->quoteIdentifier($columnName),
923  $this->quoteIdentifier($refColumnName)
924  );
925  $this->rawQuery($sql);
926  }
927 
928  return $this;
929  }
930 
939  public function tableColumnExists($tableName, $columnName, $schemaName = null)
940  {
941  $describe = $this->describeTable($tableName, $schemaName);
942  foreach ($describe as $column) {
943  if ($column['COLUMN_NAME'] == $columnName) {
944  return true;
945  }
946  }
947  return false;
948  }
949 
964  public function addColumn($tableName, $columnName, $definition, $schemaName = null)
965  {
966  $this->getSchemaListener()->addColumn($tableName, $columnName, $definition);
967  if ($this->tableColumnExists($tableName, $columnName, $schemaName)) {
968  return true;
969  }
970 
971  $primaryKey = '';
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.");
976  }
977  if (!empty($definition['PRIMARY'])) {
978  $primaryKey = sprintf(', ADD PRIMARY KEY (%s)', $this->quoteIdentifier($columnName));
979  }
980  $definition = $this->_getColumnDefinition($definition);
981  }
982 
983  $sql = sprintf(
984  'ALTER TABLE %s ADD COLUMN %s %s %s',
985  $this->quoteIdentifier($this->_getTableName($tableName, $schemaName)),
986  $this->quoteIdentifier($columnName),
987  $definition,
988  $primaryKey
989  );
990 
991  $result = $this->rawQuery($sql);
992 
993  $this->resetDdlCache($tableName, $schemaName);
994 
995  return $result;
996  }
997 
1006  public function dropColumn($tableName, $columnName, $schemaName = null)
1007  {
1008  if (!$this->tableColumnExists($tableName, $columnName, $schemaName)) {
1009  return true;
1010  }
1011  $this->getSchemaListener()->dropColumn($tableName, $columnName);
1012  $alterDrop = [];
1013 
1014  $foreignKeys = $this->getForeignKeys($tableName, $schemaName);
1015  foreach ($foreignKeys as $fkProp) {
1016  if ($fkProp['COLUMN_NAME'] == $columnName) {
1017  $this->getSchemaListener()->dropForeignKey($tableName, $fkProp['FK_NAME']);
1018  $alterDrop[] = 'DROP FOREIGN KEY ' . $this->quoteIdentifier($fkProp['FK_NAME']);
1019  }
1020  }
1021 
1022  /* drop index that after column removal would coincide with the existing index by indexed columns */
1023  foreach ($this->getIndexList($tableName, $schemaName) as $idxData) {
1024  $idxColumns = $idxData['COLUMNS_LIST'];
1025  $idxColumnKey = array_search($columnName, $idxColumns);
1026  if ($idxColumnKey !== false) {
1027  unset($idxColumns[$idxColumnKey]);
1028  if (empty($idxColumns)) {
1029  $this->getSchemaListener()->dropIndex($tableName, $idxData['KEY_NAME'], 'index');
1030  }
1031  if ($idxColumns && $this->_getIndexByColumns($tableName, $idxColumns, $schemaName)) {
1032  $this->dropIndex($tableName, $idxData['KEY_NAME'], $schemaName);
1033  }
1034  }
1035  }
1036 
1037  $alterDrop[] = 'DROP COLUMN ' . $this->quoteIdentifier($columnName);
1038  $sql = sprintf(
1039  'ALTER TABLE %s %s',
1040  $this->quoteIdentifier($this->_getTableName($tableName, $schemaName)),
1041  implode(', ', $alterDrop)
1042  );
1043 
1044  $result = $this->rawQuery($sql);
1045  $this->resetDdlCache($tableName, $schemaName);
1046 
1047  return $result;
1048  }
1049 
1058  protected function _getIndexByColumns($tableName, array $columns, $schemaName)
1059  {
1060  foreach ($this->getIndexList($tableName, $schemaName) as $idxData) {
1061  if ($idxData['COLUMNS_LIST'] === $columns) {
1062  return $idxData;
1063  }
1064  }
1065  return null;
1066  }
1067 
1082  public function changeColumn(
1083  $tableName,
1084  $oldColumnName,
1085  $newColumnName,
1086  $definition,
1087  $flushData = false,
1088  $schemaName = null
1089  ) {
1090  $this->getSchemaListener()->changeColumn(
1091  $tableName,
1092  $oldColumnName,
1093  $newColumnName,
1094  $definition
1095  );
1096  if (!$this->tableColumnExists($tableName, $oldColumnName, $schemaName)) {
1097  throw new \Zend_Db_Exception(
1098  sprintf(
1099  'Column "%s" does not exist in table "%s".',
1100  $oldColumnName,
1101  $tableName
1102  )
1103  );
1104  }
1105 
1106  if (is_array($definition)) {
1107  $definition = $this->_getColumnDefinition($definition);
1108  }
1109 
1110  $sql = sprintf(
1111  'ALTER TABLE %s CHANGE COLUMN %s %s %s',
1112  $this->quoteIdentifier($tableName),
1113  $this->quoteIdentifier($oldColumnName),
1114  $this->quoteIdentifier($newColumnName),
1115  $definition
1116  );
1117 
1118  $result = $this->rawQuery($sql);
1119 
1120  if ($flushData) {
1121  $this->showTableStatus($tableName, $schemaName);
1122  }
1123  $this->resetDdlCache($tableName, $schemaName);
1124 
1125  return $result;
1126  }
1127 
1139  public function modifyColumn($tableName, $columnName, $definition, $flushData = false, $schemaName = null)
1140  {
1141  $this->getSchemaListener()->modifyColumn(
1142  $tableName,
1143  $columnName,
1144  $definition
1145  );
1146  if (!$this->tableColumnExists($tableName, $columnName, $schemaName)) {
1147  throw new \Zend_Db_Exception(sprintf('Column "%s" does not exist in table "%s".', $columnName, $tableName));
1148  }
1149  if (is_array($definition)) {
1150  $definition = $this->_getColumnDefinition($definition);
1151  }
1152 
1153  $sql = sprintf(
1154  'ALTER TABLE %s MODIFY COLUMN %s %s',
1155  $this->quoteIdentifier($tableName),
1156  $this->quoteIdentifier($columnName),
1157  $definition
1158  );
1159 
1160  $this->rawQuery($sql);
1161  if ($flushData) {
1162  $this->showTableStatus($tableName, $schemaName);
1163  }
1164  $this->resetDdlCache($tableName, $schemaName);
1165 
1166  return $this;
1167  }
1168 
1176  public function showTableStatus($tableName, $schemaName = null)
1177  {
1178  $fromDbName = null;
1179  if ($schemaName !== null) {
1180  $fromDbName = ' FROM ' . $this->quoteIdentifier($schemaName);
1181  }
1182  $query = sprintf('SHOW TABLE STATUS%s LIKE %s', $fromDbName, $this->quote($tableName));
1183 
1184  return $this->rawFetchRow($query);
1185  }
1186 
1194  public function getCreateTable($tableName, $schemaName = null)
1195  {
1196  $cacheKey = $this->_getTableName($tableName, $schemaName);
1197  $ddl = $this->loadDdlCache($cacheKey, self::DDL_CREATE);
1198  if ($ddl === false) {
1199  $sql = 'SHOW CREATE TABLE ' . $this->quoteIdentifier($this->_getTableName($tableName, $schemaName));
1200  $ddl = $this->rawFetchRow($sql, 'Create Table');
1201  $this->saveDdlCache($cacheKey, self::DDL_CREATE, $ddl);
1202  }
1203 
1204  return $ddl;
1205  }
1206 
1229  public function getForeignKeys($tableName, $schemaName = null)
1230  {
1231  $cacheKey = $this->_getTableName($tableName, $schemaName);
1232  $ddl = $this->loadDdlCache($cacheKey, self::DDL_FOREIGN_KEY);
1233  if ($ddl === false) {
1234  $ddl = [];
1235  $createSql = $this->getCreateTable($tableName, $schemaName);
1236 
1237  // collect CONSTRAINT
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))?#';
1242  $matches = [];
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,
1248  'TABLE_NAME' => $tableName,
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] : ''
1254  ];
1255  }
1256 
1257  $this->saveDdlCache($cacheKey, self::DDL_FOREIGN_KEY, $ddl);
1258  }
1259 
1260  return $ddl;
1261  }
1262 
1268  public function getForeignKeysTree()
1269  {
1270  $tree = [];
1271  foreach ($this->listTables() as $table) {
1272  foreach ($this->getForeignKeys($table) as $key) {
1273  $tree[$table][$key['COLUMN_NAME']] = $key;
1274  }
1275  }
1276 
1277  return $tree;
1278  }
1279 
1297  public function modifyTables($tables)
1298  {
1299  $foreignKeys = $this->getForeignKeysTree();
1300  foreach ($tables as $table => $tableData) {
1301  if (!$this->isTableExists($table)) {
1302  continue;
1303  }
1304  foreach ($tableData['columns'] as $column => $columnDefinition) {
1305  if (!$this->tableColumnExists($table, $column)) {
1306  continue;
1307  }
1308  $droppedKeys = [];
1309  foreach ($foreignKeys as $keyTable => $columns) {
1310  foreach ($columns as $columnName => $keyOptions) {
1311  if ($table == $keyOptions['REF_TABLE_NAME'] && $column == $keyOptions['REF_COLUMN_NAME']) {
1312  $this->dropForeignKey($keyTable, $keyOptions['FK_NAME']);
1313  $droppedKeys[] = $keyOptions;
1314  }
1315  }
1316  }
1317 
1318  $this->modifyColumn($table, $column, $columnDefinition);
1319 
1320  foreach ($droppedKeys as $options) {
1321  unset($columnDefinition['identity'], $columnDefinition['primary'], $columnDefinition['comment']);
1322 
1323  $onDelete = $options['ON_DELETE'];
1324 
1325  if ($onDelete == AdapterInterface::FK_ACTION_SET_NULL) {
1326  $columnDefinition['nullable'] = true;
1327  }
1328  $this->modifyColumn($options['TABLE_NAME'], $options['COLUMN_NAME'], $columnDefinition);
1329  $this->addForeignKey(
1330  $options['FK_NAME'],
1331  $options['TABLE_NAME'],
1332  $options['COLUMN_NAME'],
1333  $options['REF_TABLE_NAME'],
1334  $options['REF_COLUMN_NAME'],
1335  ($onDelete) ? $onDelete : AdapterInterface::FK_ACTION_NO_ACTION
1336  );
1337  }
1338  }
1339  if (!empty($tableData['comment'])) {
1340  $this->changeTableComment($table, $tableData['comment']);
1341  }
1342  if (!empty($tableData['engine'])) {
1343  $this->changeTableEngine($table, $tableData['engine']);
1344  }
1345  }
1346 
1347  return $this;
1348  }
1349 
1372  public function getIndexList($tableName, $schemaName = null)
1373  {
1374  $cacheKey = $this->_getTableName($tableName, $schemaName);
1375  $ddl = $this->loadDdlCache($cacheKey, self::DDL_INDEX);
1376  if ($ddl === false) {
1377  $ddl = [];
1378 
1379  $sql = sprintf(
1380  'SHOW INDEX FROM %s',
1381  $this->quoteIdentifier($this->_getTableName($tableName, $schemaName))
1382  );
1383  foreach ($this->fetchAll($sql) as $row) {
1384  $fieldKeyName = 'Key_name';
1385  $fieldNonUnique = 'Non_unique';
1386  $fieldColumn = 'Column_name';
1387  $fieldIndexType = 'Index_type';
1388 
1389  if (strtolower($row[$fieldKeyName]) == AdapterInterface::INDEX_TYPE_PRIMARY) {
1390  $indexType = AdapterInterface::INDEX_TYPE_PRIMARY;
1391  } elseif ($row[$fieldNonUnique] == 0) {
1392  $indexType = AdapterInterface::INDEX_TYPE_UNIQUE;
1393  } elseif (strtolower($row[$fieldIndexType]) == AdapterInterface::INDEX_TYPE_FULLTEXT) {
1394  $indexType = AdapterInterface::INDEX_TYPE_FULLTEXT;
1395  } else {
1396  $indexType = AdapterInterface::INDEX_TYPE_INDEX;
1397  }
1398 
1399  $upperKeyName = strtoupper($row[$fieldKeyName]);
1400  if (isset($ddl[$upperKeyName])) {
1401  $ddl[$upperKeyName]['fields'][] = $row[$fieldColumn]; // for compatible
1402  $ddl[$upperKeyName]['COLUMNS_LIST'][] = $row[$fieldColumn];
1403  } else {
1404  $ddl[$upperKeyName] = [
1405  'SCHEMA_NAME' => $schemaName,
1406  'TABLE_NAME' => $tableName,
1407  'KEY_NAME' => $row[$fieldKeyName],
1408  'COLUMNS_LIST' => [$row[$fieldColumn]],
1409  'INDEX_TYPE' => $indexType,
1410  'INDEX_METHOD' => $row[$fieldIndexType],
1411  'type' => strtolower($indexType), // for compatibility
1412  'fields' => [$row[$fieldColumn]], // for compatibility
1413  ];
1414  }
1415  }
1416  $this->saveDdlCache($cacheKey, self::DDL_INDEX, $ddl);
1417  }
1418 
1419  return $ddl;
1420  }
1421 
1430  protected function _removeDuplicateEntry($table, $fields, $ids)
1431  {
1432  $where = [];
1433  $i = 0;
1434  foreach ($fields as $field) {
1435  $where[] = $this->quoteInto($field . '=?', $ids[$i++]);
1436  }
1437 
1438  if (!$where) {
1439  return $this;
1440  }
1441  $whereCond = implode(' AND ', $where);
1442  $sql = sprintf('SELECT COUNT(*) as `cnt` FROM `%s` WHERE %s', $table, $whereCond);
1443 
1444  $cnt = $this->rawFetchRow($sql, 'cnt');
1445  if ($cnt > 1) {
1446  $sql = sprintf(
1447  'DELETE FROM `%s` WHERE %s LIMIT %d',
1448  $table,
1449  $whereCond,
1450  $cnt - 1
1451  );
1452  $this->rawQuery($sql);
1453  }
1454 
1455  return $this;
1456  }
1457 
1463  public function select()
1464  {
1465  return $this->selectFactory->create($this);
1466  }
1467 
1479  public function quoteInto($text, $value, $type = null, $count = null)
1480  {
1481  if (is_array($value) && empty($value)) {
1482  $value = new \Zend_Db_Expr('NULL');
1483  }
1484 
1485  if ($value instanceof \DateTimeInterface) {
1486  $value = $value->format('Y-m-d H:i:s');
1487  }
1488 
1489  return parent::quoteInto($text, $value, $type, $count);
1490  }
1491 
1499  protected function _getTableName($tableName, $schemaName = null)
1500  {
1501  return ($schemaName ? $schemaName . '.' : '') . $tableName;
1502  }
1503 
1511  protected function _getCacheId($tableKey, $ddlType)
1512  {
1513  return sprintf('%s_%s_%s', self::DDL_CACHE_PREFIX, $tableKey, $ddlType);
1514  }
1515 
1524  public function loadDdlCache($tableCacheKey, $ddlType)
1525  {
1526  if (!$this->_isDdlCacheAllowed) {
1527  return false;
1528  }
1529  if (isset($this->_ddlCache[$ddlType][$tableCacheKey])) {
1530  return $this->_ddlCache[$ddlType][$tableCacheKey];
1531  }
1532 
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;
1539  }
1540  return $data;
1541  }
1542 
1543  return false;
1544  }
1545 
1554  public function saveDdlCache($tableCacheKey, $ddlType, $data)
1555  {
1556  if (!$this->_isDdlCacheAllowed) {
1557  return $this;
1558  }
1559  $this->_ddlCache[$ddlType][$tableCacheKey] = $data;
1560 
1561  if ($this->_cacheAdapter) {
1562  $cacheId = $this->_getCacheId($tableCacheKey, $ddlType);
1563  $data = $this->serializer->serialize($data);
1564  $this->_cacheAdapter->save($data, $cacheId, [self::DDL_CACHE_TAG]);
1565  }
1566 
1567  return $this;
1568  }
1569 
1578  public function resetDdlCache($tableName = null, $schemaName = null)
1579  {
1580  if (!$this->_isDdlCacheAllowed) {
1581  return $this;
1582  }
1583  if ($tableName === null) {
1584  $this->_ddlCache = [];
1585  if ($this->_cacheAdapter) {
1586  $this->_cacheAdapter->clean(\Zend_Cache::CLEANING_MODE_MATCHING_TAG, [self::DDL_CACHE_TAG]);
1587  }
1588  } else {
1589  $cacheKey = $this->_getTableName($tableName, $schemaName);
1590 
1591  $ddlTypes = [self::DDL_DESCRIBE, self::DDL_CREATE, self::DDL_INDEX, self::DDL_FOREIGN_KEY];
1592  foreach ($ddlTypes as $ddlType) {
1593  unset($this->_ddlCache[$ddlType][$cacheKey]);
1594  }
1595 
1596  if ($this->_cacheAdapter) {
1597  foreach ($ddlTypes as $ddlType) {
1598  $cacheId = $this->_getCacheId($cacheKey, $ddlType);
1599  $this->_cacheAdapter->remove($cacheId);
1600  }
1601  }
1602  }
1603 
1604  return $this;
1605  }
1606 
1611  public function disallowDdlCache()
1612  {
1613  $this->_isDdlCacheAllowed = false;
1614  return $this;
1615  }
1616 
1621  public function allowDdlCache()
1622  {
1623  $this->_isDdlCacheAllowed = true;
1624  return $this;
1625  }
1626 
1655  public function describeTable($tableName, $schemaName = null)
1656  {
1657  $cacheKey = $this->_getTableName($tableName, $schemaName);
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;
1669  }
1670  }
1671  $this->saveDdlCache($cacheKey, self::DDL_DESCRIBE, $ddl);
1672  }
1673 
1674  return $ddl;
1675  }
1676 
1686  public function getColumnCreateByDescribe($columnData)
1687  {
1688  $type = $this->_getColumnTypeByDdl($columnData);
1689  $options = [];
1690 
1691  if ($columnData['IDENTITY'] === true) {
1692  $options['identity'] = true;
1693  }
1694  if ($columnData['UNSIGNED'] === true) {
1695  $options['unsigned'] = true;
1696  }
1697  if ($columnData['NULLABLE'] === false
1698  && !($type == Table::TYPE_TEXT && strlen($columnData['DEFAULT']) != 0)
1699  ) {
1700  $options['nullable'] = false;
1701  }
1702  if ($columnData['PRIMARY'] === true) {
1703  $options['primary'] = true;
1704  }
1705  if ($columnData['DEFAULT'] !== null && $type != Table::TYPE_TEXT) {
1706  $options['default'] = $this->quote($columnData['DEFAULT']);
1707  }
1708  if (strlen($columnData['SCALE']) > 0) {
1709  $options['scale'] = $columnData['SCALE'];
1710  }
1711  if (strlen($columnData['PRECISION']) > 0) {
1712  $options['precision'] = $columnData['PRECISION'];
1713  }
1714 
1715  $comment = $this->string->upperCaseWords($columnData['COLUMN_NAME'], '_', ' ');
1716 
1717  $result = [
1718  'name' => $columnData['COLUMN_NAME'],
1719  'type' => $type,
1720  'length' => $columnData['LENGTH'],
1721  'options' => $options,
1722  'comment' => $comment,
1723  ];
1724 
1725  return $result;
1726  }
1727 
1735  public function createTableByDdl($tableName, $newTableName)
1736  {
1737  $describe = $this->describeTable($tableName);
1738  $table = $this->newTable($newTableName)
1739  ->setComment($this->string->upperCaseWords($newTableName, '_', ' '));
1740 
1741  foreach ($describe as $columnData) {
1742  $columnInfo = $this->getColumnCreateByDescribe($columnData);
1743 
1744  $table->addColumn(
1745  $columnInfo['name'],
1746  $columnInfo['type'],
1747  $columnInfo['length'],
1748  $columnInfo['options'],
1749  $columnInfo['comment']
1750  );
1751  }
1752 
1753  $indexes = $this->getIndexList($tableName);
1754  foreach ($indexes as $indexData) {
1759  if (($indexData['KEY_NAME'] == 'PRIMARY')
1760  || ($indexData['INDEX_TYPE'] == AdapterInterface::INDEX_TYPE_PRIMARY)
1761  ) {
1762  continue;
1763  }
1764 
1765  $fields = $indexData['COLUMNS_LIST'];
1766  $options = ['type' => $indexData['INDEX_TYPE']];
1767  $table->addIndex($this->getIndexName($newTableName, $fields, $indexData['INDEX_TYPE']), $fields, $options);
1768  }
1769 
1770  $foreignKeys = $this->getForeignKeys($tableName);
1771  foreach ($foreignKeys as $keyData) {
1772  $fkName = $this->getForeignKeyName(
1773  $newTableName,
1774  $keyData['COLUMN_NAME'],
1775  $keyData['REF_TABLE_NAME'],
1776  $keyData['REF_COLUMN_NAME']
1777  );
1778  $onDelete = $this->_getDdlAction($keyData['ON_DELETE']);
1779 
1780  $table->addForeignKey(
1781  $fkName,
1782  $keyData['COLUMN_NAME'],
1783  $keyData['REF_TABLE_NAME'],
1784  $keyData['REF_COLUMN_NAME'],
1785  $onDelete
1786  );
1787  }
1788 
1789  // Set additional options
1790  $tableData = $this->showTableStatus($tableName);
1791  $table->setOption('type', $tableData['Engine']);
1792 
1793  return $table;
1794  }
1795 
1806  public function modifyColumnByDdl($tableName, $columnName, $definition, $flushData = false, $schemaName = null)
1807  {
1808  $definition = array_change_key_case($definition, CASE_UPPER);
1809  $definition['COLUMN_TYPE'] = $this->_getColumnTypeByDdl($definition);
1810  if (array_key_exists('DEFAULT', $definition) && $definition['DEFAULT'] === null) {
1811  unset($definition['DEFAULT']);
1812  }
1813 
1814  return $this->modifyColumn($tableName, $columnName, $definition, $flushData, $schemaName);
1815  }
1816 
1824  protected function _getColumnTypeByDdl($column)
1825  {
1826  switch ($column['DATA_TYPE']) {
1827  case 'bool':
1828  return Table::TYPE_BOOLEAN;
1829  case 'tinytext':
1830  case 'char':
1831  case 'varchar':
1832  case 'text':
1833  case 'mediumtext':
1834  case 'longtext':
1835  return Table::TYPE_TEXT;
1836  case 'blob':
1837  case 'mediumblob':
1838  case 'longblob':
1839  return Table::TYPE_BLOB;
1840  case 'tinyint':
1841  case 'smallint':
1842  return Table::TYPE_SMALLINT;
1843  case 'mediumint':
1844  case 'int':
1845  return Table::TYPE_INTEGER;
1846  case 'bigint':
1847  return Table::TYPE_BIGINT;
1848  case 'datetime':
1849  return Table::TYPE_DATETIME;
1850  case 'timestamp':
1851  return Table::TYPE_TIMESTAMP;
1852  case 'date':
1853  return Table::TYPE_DATE;
1854  case 'float':
1855  return Table::TYPE_FLOAT;
1856  case 'decimal':
1857  case 'numeric':
1858  return Table::TYPE_DECIMAL;
1859  }
1860  }
1861 
1870  public function changeTableEngine($tableName, $engine, $schemaName = null)
1871  {
1872  $table = $this->quoteIdentifier($this->_getTableName($tableName, $schemaName));
1873  $sql = sprintf('ALTER TABLE %s ENGINE=%s', $table, $engine);
1874 
1875  return $this->rawQuery($sql);
1876  }
1877 
1886  public function changeTableComment($tableName, $comment, $schemaName = null)
1887  {
1888  $table = $this->quoteIdentifier($this->_getTableName($tableName, $schemaName));
1889  $sql = sprintf("ALTER TABLE %s COMMENT='%s'", $table, $comment);
1890 
1891  return $this->rawQuery($sql);
1892  }
1893 
1902  public function insertForce($table, array $bind)
1903  {
1904  $this->rawQuery("SET @OLD_INSERT_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO'");
1905  $result = $this->insert($table, $bind);
1906  $this->rawQuery("SET SQL_MODE=IFNULL(@OLD_INSERT_SQL_MODE,'')");
1907 
1908  return $result;
1909  }
1910 
1922  public function insertOnDuplicate($table, array $data, array $fields = [])
1923  {
1924  // extract and quote col names from the array keys
1925  $row = reset($data); // get first element from data array
1926  $bind = []; // SQL bind array
1927  $values = [];
1928 
1929  if (is_array($row)) { // Array of column-value pairs
1930  $cols = array_keys($row);
1931  foreach ($data as $row) {
1932  if (array_diff($cols, array_keys($row))) {
1933  throw new \Zend_Db_Exception('Invalid data for insert');
1934  }
1935  $values[] = $this->_prepareInsertData($row, $bind);
1936  }
1937  unset($row);
1938  } else { // Column-value pairs
1939  $cols = array_keys($data);
1940  $values[] = $this->_prepareInsertData($data, $bind);
1941  }
1942 
1943  $updateFields = [];
1944  if (empty($fields)) {
1945  $fields = $cols;
1946  }
1947 
1948  // prepare ON DUPLICATE KEY conditions
1949  foreach ($fields as $k => $v) {
1950  $field = $value = null;
1951  if (!is_numeric($k)) {
1952  $field = $this->quoteIdentifier($k);
1953  if ($v instanceof \Zend_Db_Expr) {
1954  $value = $v->__toString();
1955  } elseif ($v instanceof \Zend\Db\Sql\Expression) {
1956  $value = $v->getExpression();
1957  } elseif (is_string($v)) {
1958  $value = sprintf('VALUES(%s)', $this->quoteIdentifier($v));
1959  } elseif (is_numeric($v)) {
1960  $value = $this->quoteInto('?', $v);
1961  }
1962  } elseif (is_string($v)) {
1963  $value = sprintf('VALUES(%s)', $this->quoteIdentifier($v));
1964  $field = $this->quoteIdentifier($v);
1965  }
1966 
1967  if ($field && is_string($value) && $value !== '') {
1968  $updateFields[] = sprintf('%s = %s', $field, $value);
1969  }
1970  }
1971 
1972  $insertSql = $this->_getInsertSqlQuery($table, $cols, $values);
1973  if ($updateFields) {
1974  $insertSql .= ' ON DUPLICATE KEY UPDATE ' . implode(', ', $updateFields);
1975  }
1976  // execute the statement and return the number of affected rows
1977  $stmt = $this->query($insertSql, array_values($bind));
1978  $result = $stmt->rowCount();
1979 
1980  return $result;
1981  }
1982 
1991  public function insertMultiple($table, array $data)
1992  {
1993  $row = reset($data);
1994  // support insert syntaxes
1995  if (!is_array($row)) {
1996  return $this->insert($table, $data);
1997  }
1998 
1999  // validate data array
2000  $cols = array_keys($row);
2001  $insertArray = [];
2002  foreach ($data as $row) {
2003  $line = [];
2004  if (array_diff($cols, array_keys($row))) {
2005  throw new \Zend_Db_Exception('Invalid data for insert');
2006  }
2007  foreach ($cols as $field) {
2008  $line[] = $row[$field];
2009  }
2010  $insertArray[] = $line;
2011  }
2012  unset($row);
2013 
2014  return $this->insertArray($table, $cols, $insertArray);
2015  }
2016 
2036  public function insertArray($table, array $columns, array $data, $strategy = 0)
2037  {
2038  $values = [];
2039  $bind = [];
2040  $columnsCount = count($columns);
2041  foreach ($data as $row) {
2042  if (is_array($row) && $columnsCount != count($row)) {
2043  throw new \Zend_Db_Exception('Invalid data for insert');
2044  }
2045  $values[] = $this->_prepareInsertData($row, $bind);
2046  }
2047 
2048  switch ($strategy) {
2049  case self::REPLACE:
2050  $query = $this->_getReplaceSqlQuery($table, $columns, $values);
2051  break;
2052  default:
2053  $query = $this->_getInsertSqlQuery($table, $columns, $values, $strategy);
2054  }
2055 
2056  // execute the statement and return the number of affected rows
2057  $stmt = $this->query($query, $bind);
2058  $result = $stmt->rowCount();
2059 
2060  return $result;
2061  }
2062 
2069  public function setCacheAdapter(FrontendInterface $cacheAdapter)
2070  {
2071  $this->_cacheAdapter = $cacheAdapter;
2072  return $this;
2073  }
2074 
2082  public function newTable($tableName = null, $schemaName = null)
2083  {
2084  $table = new Table();
2085  if ($tableName !== null) {
2086  $table->setName($tableName);
2087  }
2088  if ($schemaName !== null) {
2089  $table->setSchema($schemaName);
2090  }
2091  if (isset($this->_config['engine'])) {
2092  $table->setOption('type', $this->_config['engine']);
2093  }
2094 
2095  return $table;
2096  }
2097 
2105  public function createTable(Table $table)
2106  {
2107  $this->getSchemaListener()->createTable($table);
2108  $columns = $table->getColumns();
2109  foreach ($columns as $columnEntry) {
2110  if (empty($columnEntry['COMMENT'])) {
2111  throw new \Zend_Db_Exception("Cannot create table without columns comments");
2112  }
2113  }
2114 
2115  $sqlFragment = array_merge(
2116  $this->_getColumnsDefinition($table),
2117  $this->_getIndexesDefinition($table),
2118  $this->_getForeignKeysDefinition($table)
2119  );
2120  $tableOptions = $this->_getOptionsDefinition($table);
2121  $sql = sprintf(
2122  "CREATE TABLE IF NOT EXISTS %s (\n%s\n) %s",
2123  $this->quoteIdentifier($table->getName()),
2124  implode(",\n", $sqlFragment),
2125  implode(" ", $tableOptions)
2126  );
2127 
2128  if ($this->getTransactionLevel() > 0) {
2129  $result = $this->createConnection()->query($sql);
2130  } else {
2131  $result = $this->query($sql);
2132  }
2133  $this->resetDdlCache($table->getName(), $table->getSchema());
2134 
2135  return $result;
2136  }
2137 
2146  public function createTemporaryTable(\Magento\Framework\DB\Ddl\Table $table)
2147  {
2148  $columns = $table->getColumns();
2149  $sqlFragment = array_merge(
2150  $this->_getColumnsDefinition($table),
2151  $this->_getIndexesDefinition($table),
2152  $this->_getForeignKeysDefinition($table)
2153  );
2154  $tableOptions = $this->_getOptionsDefinition($table);
2155  $sql = sprintf(
2156  "CREATE TEMPORARY TABLE %s (\n%s\n) %s",
2157  $this->quoteIdentifier($table->getName()),
2158  implode(",\n", $sqlFragment),
2159  implode(" ", $tableOptions)
2160  );
2161 
2162  return $this->query($sql);
2163  }
2164 
2173  public function createTemporaryTableLike($temporaryTableName, $originTableName, $ifNotExists = false)
2174  {
2175  $ifNotExistsSql = ($ifNotExists ? 'IF NOT EXISTS' : '');
2176  $temporaryTable = $this->quoteIdentifier($this->_getTableName($temporaryTableName));
2177  $originTable = $this->quoteIdentifier($this->_getTableName($originTableName));
2178  $sql = sprintf('CREATE TEMPORARY TABLE %s %s LIKE %s', $ifNotExistsSql, $temporaryTable, $originTable);
2179 
2180  return $this->query($sql);
2181  }
2182 
2191  public function renameTablesBatch(array $tablePairs)
2192  {
2193  if (count($tablePairs) == 0) {
2194  throw new \Zend_Db_Exception('Please provide tables for rename');
2195  }
2196 
2197  $renamesList = [];
2198  $tablesList = [];
2199  foreach ($tablePairs as $pair) {
2200  $oldTableName = $pair['oldName'];
2201  $newTableName = $pair['newName'];
2202  $renamesList[] = sprintf('%s TO %s', $oldTableName, $newTableName);
2203 
2204  $tablesList[$oldTableName] = $oldTableName;
2205  $tablesList[$newTableName] = $newTableName;
2206  }
2207 
2208  $query = sprintf('RENAME TABLE %s', implode(',', $renamesList));
2209  $this->query($query);
2210 
2211  foreach ($tablesList as $table) {
2212  $this->resetDdlCache($table);
2213  }
2214 
2215  return true;
2216  }
2217 
2225  protected function _getColumnsDefinition(Table $table)
2226  {
2227  $definition = [];
2228  $primary = [];
2229  $columns = $table->getColumns();
2230  if (empty($columns)) {
2231  throw new \Zend_Db_Exception('Table columns are not defined');
2232  }
2233 
2234  foreach ($columns as $columnData) {
2235  $columnDefinition = $this->_getColumnDefinition($columnData);
2236  if ($columnData['PRIMARY']) {
2237  $primary[$columnData['COLUMN_NAME']] = $columnData['PRIMARY_POSITION'];
2238  }
2239 
2240  $definition[] = sprintf(
2241  ' %s %s',
2242  $this->quoteIdentifier($columnData['COLUMN_NAME']),
2243  $columnDefinition
2244  );
2245  }
2246 
2247  // PRIMARY KEY
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));
2252  }
2253 
2254  return $definition;
2255  }
2256 
2263  protected function _getIndexesDefinition(Table $table)
2264  {
2265  $definition = [];
2266  $indexes = $table->getIndexes();
2267  foreach ($indexes as $indexData) {
2268  if (!empty($indexData['TYPE'])) {
2269  //Skipping not supported fulltext indexes for NDB
2270  if (($indexData['TYPE'] == AdapterInterface::INDEX_TYPE_FULLTEXT) && $this->isNdb($table)) {
2271  continue;
2272  }
2273  switch ($indexData['TYPE']) {
2274  case AdapterInterface::INDEX_TYPE_PRIMARY:
2275  $indexType = 'PRIMARY KEY';
2276  unset($indexData['INDEX_NAME']);
2277  break;
2278  default:
2279  $indexType = strtoupper($indexData['TYPE']);
2280  break;
2281  }
2282  } else {
2283  $indexType = 'KEY';
2284  }
2285 
2286  $columns = [];
2287  foreach ($indexData['COLUMNS'] as $columnData) {
2288  $column = $this->quoteIdentifier($columnData['NAME']);
2289  if (!empty($columnData['SIZE'])) {
2290  $column .= sprintf('(%d)', $columnData['SIZE']);
2291  }
2292  $columns[] = $column;
2293  }
2294  $indexName = isset($indexData['INDEX_NAME']) ? $this->quoteIdentifier($indexData['INDEX_NAME']) : '';
2295  $definition[] = sprintf(
2296  ' %s %s (%s)',
2297  $indexType,
2298  $indexName,
2299  implode(', ', $columns)
2300  );
2301  }
2302 
2303  return $definition;
2304  }
2305 
2312  protected function isNdb(Table $table)
2313  {
2314  $engineType = strtolower($table->getOption('type'));
2315  return $engineType == 'ndb' || $engineType == 'ndbcluster';
2316  }
2317 
2324  protected function _getForeignKeysDefinition(Table $table)
2325  {
2326  $definition = [];
2327  $relations = $table->getForeignKeys();
2328 
2329  if (!empty($relations)) {
2330  foreach ($relations as $fkData) {
2331  $onDelete = $this->_getDdlAction($fkData['ON_DELETE']);
2332  $definition[] = sprintf(
2333  ' CONSTRAINT %s FOREIGN KEY (%s) REFERENCES %s (%s) ON DELETE %s',
2334  $this->quoteIdentifier($fkData['FK_NAME']),
2335  $this->quoteIdentifier($fkData['COLUMN_NAME']),
2336  $this->quoteIdentifier($fkData['REF_TABLE_NAME']),
2337  $this->quoteIdentifier($fkData['REF_COLUMN_NAME']),
2338  $onDelete
2339  );
2340  }
2341  }
2342 
2343  return $definition;
2344  }
2345 
2353  protected function _getOptionsDefinition(Table $table)
2354  {
2355  $definition = [];
2356  $comment = $table->getComment();
2357  if (empty($comment)) {
2358  throw new \Zend_Db_Exception('Comment for table is required and must be defined');
2359  }
2360  $definition[] = $this->quoteInto('COMMENT=?', $comment);
2361 
2362  $tableProps = [
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',
2373  ];
2374  foreach ($tableProps as $key => $mask) {
2375  $v = $table->getOption($key);
2376  if ($v !== null) {
2377  $definition[] = sprintf($mask, $v);
2378  }
2379  }
2380 
2381  return $definition;
2382  }
2383 
2391  public function getColumnDefinitionFromDescribe($options, $ddlType = null)
2392  {
2393  $columnInfo = $this->getColumnCreateByDescribe($options);
2394  foreach ($columnInfo['options'] as $key => $value) {
2395  $columnInfo[$key] = $value;
2396  }
2397  return $this->_getColumnDefinition($columnInfo, $ddlType);
2398  }
2399 
2413  protected function _getColumnDefinition($options, $ddlType = null)
2414  {
2415  // convert keys to uppercase
2416  $options = array_change_key_case($options, CASE_UPPER);
2417  $cType = null;
2418  $cUnsigned = false;
2419  $cNullable = true;
2420  $cDefault = false;
2421  $cIdentity = false;
2422 
2423  // detect and validate column type
2424  if ($ddlType === null) {
2425  $ddlType = $this->_getDdlType($options);
2426  }
2427 
2428  if (empty($ddlType) || !isset($this->_ddlColumnTypes[$ddlType])) {
2429  throw new \Zend_Db_Exception('Invalid column definition data');
2430  }
2431 
2432  // column size
2433  $cType = $this->_ddlColumnTypes[$ddlType];
2434  switch ($ddlType) {
2435  case Table::TYPE_SMALLINT:
2436  case Table::TYPE_INTEGER:
2437  case Table::TYPE_BIGINT:
2438  if (!empty($options['UNSIGNED'])) {
2439  $cUnsigned = true;
2440  }
2441  break;
2442  case Table::TYPE_DECIMAL:
2443  case Table::TYPE_FLOAT:
2444  case Table::TYPE_NUMERIC:
2445  $precision = 10;
2446  $scale = 0;
2447  $match = [];
2448  if (!empty($options['LENGTH']) && preg_match('#^\(?(\d+),(\d+)\)?$#', $options['LENGTH'], $match)) {
2449  $precision = $match[1];
2450  $scale = $match[2];
2451  } else {
2452  if (isset($options['SCALE']) && is_numeric($options['SCALE'])) {
2453  $scale = $options['SCALE'];
2454  }
2455  if (isset($options['PRECISION']) && is_numeric($options['PRECISION'])) {
2456  $precision = $options['PRECISION'];
2457  }
2458  }
2459  $cType .= sprintf('(%d,%d)', $precision, $scale);
2460  if (!empty($options['UNSIGNED'])) {
2461  $cUnsigned = true;
2462  }
2463  break;
2464  case Table::TYPE_TEXT:
2465  case Table::TYPE_BLOB:
2466  case Table::TYPE_VARBINARY:
2467  if (empty($options['LENGTH'])) {
2468  $length = Table::DEFAULT_TEXT_SIZE;
2469  } else {
2470  $length = $this->_parseTextSize($options['LENGTH']);
2471  }
2472  if ($length <= 255) {
2473  $cType = $ddlType == Table::TYPE_TEXT ? 'varchar' : 'varbinary';
2474  $cType = sprintf('%s(%d)', $cType, $length);
2475  } elseif ($length > 255 && $length <= 65536) {
2476  $cType = $ddlType == Table::TYPE_TEXT ? 'text' : 'blob';
2477  } elseif ($length > 65536 && $length <= 16777216) {
2478  $cType = $ddlType == Table::TYPE_TEXT ? 'mediumtext' : 'mediumblob';
2479  } else {
2480  $cType = $ddlType == Table::TYPE_TEXT ? 'longtext' : 'longblob';
2481  }
2482  break;
2483  }
2484 
2485  if (array_key_exists('DEFAULT', $options)) {
2486  $cDefault = $options['DEFAULT'];
2487  }
2488  if (array_key_exists('NULLABLE', $options)) {
2489  $cNullable = (bool)$options['NULLABLE'];
2490  }
2491  if (!empty($options['IDENTITY']) || !empty($options['AUTO_INCREMENT'])) {
2492  $cIdentity = true;
2493  }
2494 
2495  /* For cases when tables created from createTableByDdl()
2496  * where default value can be quoted already.
2497  * We need to avoid "double-quoting" here
2498  */
2499  if ($cDefault !== null && is_string($cDefault) && strlen($cDefault)) {
2500  $cDefault = str_replace("'", '', $cDefault);
2501  }
2502 
2503  // prepare default value string
2504  if ($ddlType == Table::TYPE_TIMESTAMP) {
2505  if ($cDefault === null) {
2506  $cDefault = new \Zend_Db_Expr('NULL');
2507  } elseif ($cDefault == Table::TIMESTAMP_INIT) {
2508  $cDefault = new \Zend_Db_Expr('CURRENT_TIMESTAMP');
2509  } elseif ($cDefault == Table::TIMESTAMP_UPDATE) {
2510  $cDefault = new \Zend_Db_Expr('0 ON UPDATE CURRENT_TIMESTAMP');
2511  } elseif ($cDefault == Table::TIMESTAMP_INIT_UPDATE) {
2512  $cDefault = new \Zend_Db_Expr('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP');
2513  } elseif ($cNullable && !$cDefault) {
2514  $cDefault = new \Zend_Db_Expr('NULL');
2515  } else {
2516  $cDefault = false;
2517  }
2518  } elseif ($cDefault === null && $cNullable) {
2519  $cDefault = new \Zend_Db_Expr('NULL');
2520  }
2521 
2522  if (empty($options['COMMENT'])) {
2523  $comment = '';
2524  } else {
2525  $comment = $options['COMMENT'];
2526  }
2527 
2528  //set column position
2529  $after = null;
2530  if (!empty($options['AFTER'])) {
2531  $after = $options['AFTER'];
2532  }
2533 
2534  return sprintf(
2535  '%s%s%s%s%s COMMENT %s %s',
2536  $cType,
2537  $cUnsigned ? ' UNSIGNED' : '',
2538  $cNullable ? ' NULL' : ' NOT NULL',
2539  $cDefault !== false ? $this->quoteInto(' default ?', $cDefault) : '',
2540  $cIdentity ? ' auto_increment' : '',
2541  $this->quote($comment),
2542  $after ? 'AFTER ' . $this->quoteIdentifier($after) : ''
2543  );
2544  }
2545 
2553  public function dropTable($tableName, $schemaName = null)
2554  {
2555  $table = $this->quoteIdentifier($this->_getTableName($tableName, $schemaName));
2556  $query = 'DROP TABLE IF EXISTS ' . $table;
2557  if ($this->getTransactionLevel() > 0) {
2558  $this->createConnection()->query($query);
2559  } else {
2560  $this->query($query);
2561  }
2562  $this->resetDdlCache($tableName, $schemaName);
2563  $this->getSchemaListener()->dropTable($tableName);
2564  return true;
2565  }
2566 
2574  public function dropTemporaryTable($tableName, $schemaName = null)
2575  {
2576  $table = $this->quoteIdentifier($this->_getTableName($tableName, $schemaName));
2577  $query = 'DROP TEMPORARY TABLE IF EXISTS ' . $table;
2578  $this->query($query);
2579 
2580  return true;
2581  }
2582 
2591  public function truncateTable($tableName, $schemaName = null)
2592  {
2593  if (!$this->isTableExists($tableName, $schemaName)) {
2594  throw new \Zend_Db_Exception(sprintf('Table "%s" does not exist', $tableName));
2595  }
2596 
2597  $table = $this->quoteIdentifier($this->_getTableName($tableName, $schemaName));
2598  $query = 'TRUNCATE TABLE ' . $table;
2599  $this->query($query);
2600 
2601  return $this;
2602  }
2603 
2611  public function isTableExists($tableName, $schemaName = null)
2612  {
2613  return $this->showTableStatus($tableName, $schemaName) !== false;
2614  }
2615 
2625  public function renameTable($oldTableName, $newTableName, $schemaName = null)
2626  {
2627  if (!$this->isTableExists($oldTableName, $schemaName)) {
2628  throw new \Zend_Db_Exception(sprintf('Table "%s" does not exist', $oldTableName));
2629  }
2630  if ($this->isTableExists($newTableName, $schemaName)) {
2631  throw new \Zend_Db_Exception(sprintf('Table "%s" already exists', $newTableName));
2632  }
2633  $this->getSchemaListener()->renameTable($oldTableName, $newTableName);
2634  $oldTable = $this->_getTableName($oldTableName, $schemaName);
2635  $newTable = $this->_getTableName($newTableName, $schemaName);
2636 
2637  $query = sprintf('ALTER TABLE %s RENAME TO %s', $oldTable, $newTable);
2638 
2639  if ($this->getTransactionLevel() > 0) {
2640  $this->createConnection()->query($query);
2641  } else {
2642  $this->query($query);
2643  }
2644  $this->resetDdlCache($oldTableName, $schemaName);
2645 
2646  return true;
2647  }
2648 
2663  public function addIndex(
2664  $tableName,
2665  $indexName,
2666  $fields,
2667  $indexType = AdapterInterface::INDEX_TYPE_INDEX,
2668  $schemaName = null
2669  ) {
2670  $this->getSchemaListener()->addIndex(
2671  $tableName,
2672  $indexName,
2673  $fields,
2674  $indexType
2675  );
2676  $columns = $this->describeTable($tableName, $schemaName);
2677  $keyList = $this->getIndexList($tableName, $schemaName);
2678 
2679  $query = sprintf('ALTER TABLE %s', $this->quoteIdentifier($this->_getTableName($tableName, $schemaName)));
2680  if (isset($keyList[strtoupper($indexName)])) {
2681  if ($keyList[strtoupper($indexName)]['INDEX_TYPE'] == AdapterInterface::INDEX_TYPE_PRIMARY) {
2682  $query .= ' DROP PRIMARY KEY,';
2683  } else {
2684  $query .= sprintf(' DROP INDEX %s,', $this->quoteIdentifier($indexName));
2685  }
2686  }
2687 
2688  if (!is_array($fields)) {
2689  $fields = [$fields];
2690  }
2691 
2692  $fieldSql = [];
2693  foreach ($fields as $field) {
2694  if (!isset($columns[$field])) {
2695  $msg = sprintf(
2696  'There is no field "%s" that you are trying to create an index on "%s"',
2697  $field,
2698  $tableName
2699  );
2700  throw new \Zend_Db_Exception($msg);
2701  }
2702  $fieldSql[] = $this->quoteIdentifier($field);
2703  }
2704  $fieldSql = implode(',', $fieldSql);
2705 
2706  switch (strtolower($indexType)) {
2707  case AdapterInterface::INDEX_TYPE_PRIMARY:
2708  $condition = 'PRIMARY KEY';
2709  break;
2710  case AdapterInterface::INDEX_TYPE_UNIQUE:
2711  $condition = 'UNIQUE ' . $this->quoteIdentifier($indexName);
2712  break;
2713  case AdapterInterface::INDEX_TYPE_FULLTEXT:
2714  $condition = 'FULLTEXT ' . $this->quoteIdentifier($indexName);
2715  break;
2716  default:
2717  $condition = 'INDEX ' . $this->quoteIdentifier($indexName);
2718  break;
2719  }
2720 
2721  $query .= sprintf(' ADD %s (%s)', $condition, $fieldSql);
2722 
2723  $cycle = true;
2724  while ($cycle === true) {
2725  try {
2726  $result = $this->rawQuery($query);
2727  $cycle = false;
2728  } catch (\Exception $e) {
2729  if (in_array(strtolower($indexType), ['primary', 'unique'])) {
2730  $match = [];
2731  if (preg_match('#SQLSTATE\[23000\]: [^:]+: 1062[^\']+\'([\d-\.]+)\'#', $e->getMessage(), $match)) {
2732  $ids = explode('-', $match[1]);
2733  $this->_removeDuplicateEntry($tableName, $fields, $ids);
2734  continue;
2735  }
2736  }
2737  throw $e;
2738  }
2739  }
2740 
2741  $this->resetDdlCache($tableName, $schemaName);
2742 
2743  return $result;
2744  }
2745 
2754  public function dropIndex($tableName, $keyName, $schemaName = null)
2755  {
2756  $indexList = $this->getIndexList($tableName, $schemaName);
2757  $indexType = 'index';
2758  $keyName = strtoupper($keyName);
2759  if (!isset($indexList[$keyName])) {
2760  return true;
2761  }
2762 
2763  if ($keyName == 'PRIMARY') {
2764  $indexType = 'primary';
2765  $cond = 'DROP PRIMARY KEY';
2766  } else {
2767  if (strpos($keyName, 'UNQ_') !== false) {
2768  $indexType = 'unique';
2769  }
2770  $cond = 'DROP KEY ' . $this->quoteIdentifier($indexList[$keyName]['KEY_NAME']);
2771  }
2772 
2773  $sql = sprintf(
2774  'ALTER TABLE %s %s',
2775  $this->quoteIdentifier($this->_getTableName($tableName, $schemaName)),
2776  $cond
2777  );
2778  $this->getSchemaListener()->dropIndex($tableName, $keyName, $indexType);
2779  $this->resetDdlCache($tableName, $schemaName);
2780 
2781  return $this->rawQuery($sql);
2782  }
2783 
2800  public function addForeignKey(
2801  $fkName,
2802  $tableName,
2803  $columnName,
2804  $refTableName,
2805  $refColumnName,
2806  $onDelete = AdapterInterface::FK_ACTION_CASCADE,
2807  $purge = false,
2808  $schemaName = null,
2809  $refSchemaName = null
2810  ) {
2811  $this->dropForeignKey($tableName, $fkName, $schemaName);
2812 
2813  if ($purge) {
2814  $this->purgeOrphanRecords($tableName, $columnName, $refTableName, $refColumnName, $onDelete);
2815  }
2816 
2817  $query = sprintf(
2818  'ALTER TABLE %s ADD CONSTRAINT %s FOREIGN KEY (%s) REFERENCES %s (%s)',
2819  $this->quoteIdentifier($this->_getTableName($tableName, $schemaName)),
2820  $this->quoteIdentifier($fkName),
2821  $this->quoteIdentifier($columnName),
2822  $this->quoteIdentifier($this->_getTableName($refTableName, $refSchemaName)),
2823  $this->quoteIdentifier($refColumnName)
2824  );
2825 
2826  if ($onDelete !== null) {
2827  $query .= ' ON DELETE ' . strtoupper($onDelete);
2828  }
2829 
2830  $this->getSchemaListener()->addForeignKey(
2831  $fkName,
2832  $tableName,
2833  $columnName,
2834  $refTableName,
2835  $refColumnName,
2836  $onDelete
2837  );
2838 
2839  $result = $this->rawQuery($query);
2840  $this->resetDdlCache($tableName);
2841  return $result;
2842  }
2843 
2851  public function formatDate($date, $includeTime = true)
2852  {
2853  $date = $this->dateTime->formatDate($date, $includeTime);
2854 
2855  if ($date === null) {
2856  return new \Zend_Db_Expr('NULL');
2857  }
2858 
2859  return new \Zend_Db_Expr($this->quote($date));
2860  }
2861 
2867  public function startSetup()
2868  {
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'");
2872 
2873  return $this;
2874  }
2875 
2881  public function endSetup()
2882  {
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)");
2885 
2886  return $this;
2887  }
2888 
2920  public function prepareSqlCondition($fieldName, $condition)
2921  {
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}} <= ?",
2940  'seq' => null,
2941  'sneq' => null,
2942  'ntoa' => "INET_NTOA({{fieldName}}) LIKE ?",
2943  ];
2944 
2945  $query = '';
2946  if (is_array($condition)) {
2947  $key = key(array_intersect_key($condition, $conditionKeyMap));
2948 
2949  if (isset($condition['from']) || isset($condition['to'])) {
2950  if (isset($condition['from'])) {
2951  $from = $this->_prepareSqlDateCondition($condition, 'from');
2952  $query = $this->_prepareQuotedSqlCondition($conditionKeyMap['from'], $from, $fieldName);
2953  }
2954 
2955  if (isset($condition['to'])) {
2956  $query .= empty($query) ? '' : ' AND ';
2957  $to = $this->_prepareSqlDateCondition($condition, 'to');
2958  $query = $this->_prepareQuotedSqlCondition($query . $conditionKeyMap['to'], $to, $fieldName);
2959  }
2960  } elseif (array_key_exists($key, $conditionKeyMap)) {
2961  $value = $condition[$key];
2962  if (($key == 'seq') || ($key == 'sneq')) {
2963  $key = $this->_transformStringSqlCondition($key, $value);
2964  }
2965  if (($key == 'in' || $key == 'nin') && is_string($value)) {
2966  $value = explode(',', $value);
2967  }
2968  $query = $this->_prepareQuotedSqlCondition($conditionKeyMap[$key], $value, $fieldName);
2969  } else {
2970  $queries = [];
2971  foreach ($condition as $orCondition) {
2972  $queries[] = sprintf('(%s)', $this->prepareSqlCondition($fieldName, $orCondition));
2973  }
2974 
2975  $query = sprintf('(%s)', implode(' OR ', $queries));
2976  }
2977  } else {
2978  $query = $this->_prepareQuotedSqlCondition($conditionKeyMap['eq'], (string)$condition, $fieldName);
2979  }
2980 
2981  return $query;
2982  }
2983 
2992  protected function _prepareQuotedSqlCondition($text, $value, $fieldName)
2993  {
2994  $sql = $this->quoteInto($text, $value);
2995  $sql = str_replace('{{fieldName}}', $fieldName, $sql);
2996  return $sql;
2997  }
2998 
3008  protected function _transformStringSqlCondition($conditionKey, $value)
3009  {
3010  $value = (string) $value;
3011  if ($value == '') {
3012  return ($conditionKey == 'seq') ? 'null' : 'notnull';
3013  } else {
3014  return ($conditionKey == 'seq') ? 'eq' : 'neq';
3015  }
3016  }
3017 
3029  public function prepareColumnValue(array $column, $value)
3030  {
3031  if ($value instanceof \Zend_Db_Expr) {
3032  return $value;
3033  }
3034  if ($value instanceof Parameter) {
3035  return $value;
3036  }
3037 
3038  // return original value if invalid column describe data
3039  if (!isset($column['DATA_TYPE'])) {
3040  return $value;
3041  }
3042 
3043  // return null
3044  if ($value === null && $column['NULLABLE']) {
3045  return null;
3046  }
3047 
3048  switch ($column['DATA_TYPE']) {
3049  case 'smallint':
3050  case 'int':
3051  $value = (int)$value;
3052  break;
3053  case 'bigint':
3054  if (!is_integer($value)) {
3055  $value = sprintf('%.0f', (float)$value);
3056  }
3057  break;
3058 
3059  case 'decimal':
3060  $precision = 10;
3061  $scale = 0;
3062  if (isset($column['SCALE'])) {
3063  $scale = $column['SCALE'];
3064  }
3065  if (isset($column['PRECISION'])) {
3066  $precision = $column['PRECISION'];
3067  }
3068  $format = sprintf('%%%d.%dF', $precision - $scale, $scale);
3069  $value = (float)sprintf($format, $value);
3070  break;
3071 
3072  case 'float':
3073  $value = (float)sprintf('%F', $value);
3074  break;
3075 
3076  case 'date':
3077  $value = $this->formatDate($value, false);
3078  break;
3079  case 'datetime':
3080  case 'timestamp':
3081  $value = $this->formatDate($value);
3082  break;
3083 
3084  case 'varchar':
3085  case 'mediumtext':
3086  case 'text':
3087  case 'longtext':
3088  $value = (string)$value;
3089  if ($column['NULLABLE'] && $value == '') {
3090  $value = null;
3091  }
3092  break;
3093 
3094  case 'varbinary':
3095  case 'mediumblob':
3096  case 'blob':
3097  case 'longblob':
3098  // No special processing for MySQL is needed
3099  break;
3100  }
3101 
3102  return $value;
3103  }
3104 
3113  public function getCheckSql($expression, $true, $false)
3114  {
3115  if ($expression instanceof \Zend_Db_Expr || $expression instanceof \Zend_Db_Select) {
3116  $expression = sprintf("IF((%s), %s, %s)", $expression, $true, $false);
3117  } else {
3118  $expression = sprintf("IF(%s, %s, %s)", $expression, $true, $false);
3119  }
3120 
3121  return new \Zend_Db_Expr($expression);
3122  }
3123 
3131  public function getIfNullSql($expression, $value = 0)
3132  {
3133  if ($expression instanceof \Zend_Db_Expr || $expression instanceof \Zend_Db_Select) {
3134  $expression = sprintf("IFNULL((%s), %s)", $expression, $value);
3135  } else {
3136  $expression = sprintf("IFNULL(%s, %s)", $expression, $value);
3137  }
3138 
3139  return new \Zend_Db_Expr($expression);
3140  }
3141 
3151  public function getCaseSql($valueName, $casesResults, $defaultValue = null)
3152  {
3153  $expression = 'CASE ' . $valueName;
3154  foreach ($casesResults as $case => $result) {
3155  $expression .= ' WHEN ' . $case . ' THEN ' . $result;
3156  }
3157  if ($defaultValue !== null) {
3158  $expression .= ' ELSE ' . $defaultValue;
3159  }
3160  $expression .= ' END';
3161 
3162  return new \Zend_Db_Expr($expression);
3163  }
3164 
3173  public function getConcatSql(array $data, $separator = null)
3174  {
3175  $format = empty($separator) ? 'CONCAT(%s)' : "CONCAT_WS('{$separator}', %s)";
3176  return new \Zend_Db_Expr(sprintf($format, implode(', ', $data)));
3177  }
3178 
3186  public function getLengthSql($string)
3187  {
3188  return new \Zend_Db_Expr(sprintf('LENGTH(%s)', $string));
3189  }
3190 
3199  public function getLeastSql(array $data)
3200  {
3201  return new \Zend_Db_Expr(sprintf('LEAST(%s)', implode(', ', $data)));
3202  }
3203 
3212  public function getGreatestSql(array $data)
3213  {
3214  return new \Zend_Db_Expr(sprintf('GREATEST(%s)', implode(', ', $data)));
3215  }
3216 
3225  protected function _getIntervalUnitSql($interval, $unit)
3226  {
3227  if (!isset($this->_intervalUnits[$unit])) {
3228  throw new \Zend_Db_Exception(sprintf('Undefined interval unit "%s" specified', $unit));
3229  }
3230 
3231  return sprintf('INTERVAL %d %s', $interval, $this->_intervalUnits[$unit]);
3232  }
3233 
3244  public function getDateAddSql($date, $interval, $unit)
3245  {
3246  $expr = sprintf('DATE_ADD(%s, %s)', $date, $this->_getIntervalUnitSql($interval, $unit));
3247  return new \Zend_Db_Expr($expr);
3248  }
3249 
3260  public function getDateSubSql($date, $interval, $unit)
3261  {
3262  $expr = sprintf('DATE_SUB(%s, %s)', $date, $this->_getIntervalUnitSql($interval, $unit));
3263  return new \Zend_Db_Expr($expr);
3264  }
3265 
3282  public function getDateFormatSql($date, $format)
3283  {
3284  $expr = sprintf("DATE_FORMAT(%s, '%s')", $date, $format);
3285  return new \Zend_Db_Expr($expr);
3286  }
3287 
3294  public function getDatePartSql($date)
3295  {
3296  return new \Zend_Db_Expr(sprintf('DATE(%s)', $date));
3297  }
3298 
3307  public function getSubstringSql($stringExpression, $pos, $len = null)
3308  {
3309  if ($len === null) {
3310  return new \Zend_Db_Expr(sprintf('SUBSTRING(%s, %s)', $stringExpression, $pos));
3311  }
3312  return new \Zend_Db_Expr(sprintf('SUBSTRING(%s, %s, %s)', $stringExpression, $pos, $len));
3313  }
3314 
3321  public function getStandardDeviationSql($expressionField)
3322  {
3323  return new \Zend_Db_Expr(sprintf('STDDEV_SAMP(%s)', $expressionField));
3324  }
3325 
3336  public function getDateExtractSql($date, $unit)
3337  {
3338  if (!isset($this->_intervalUnits[$unit])) {
3339  throw new \Zend_Db_Exception(sprintf('Undefined interval unit "%s" specified', $unit));
3340  }
3341 
3342  $expr = sprintf('EXTRACT(%s FROM %s)', $this->_intervalUnits[$unit], $date);
3343  return new \Zend_Db_Expr($expr);
3344  }
3345 
3353  public function getTableName($tableName)
3354  {
3355  return ExpressionConverter::shortenEntityName($tableName, 't_');
3356  }
3357 
3367  public function getTriggerName($tableName, $time, $event)
3368  {
3369  $triggerName = 'trg_' . $tableName . '_' . $time . '_' . $event;
3370  return ExpressionConverter::shortenEntityName($triggerName, 'trg_');
3371  }
3372 
3382  public function getIndexName($tableName, $fields, $indexType = '')
3383  {
3384  if (is_array($fields)) {
3385  $fields = implode('_', $fields);
3386  }
3387 
3388  switch (strtolower($indexType)) {
3389  case AdapterInterface::INDEX_TYPE_UNIQUE:
3390  $prefix = 'unq_';
3391  break;
3392  case AdapterInterface::INDEX_TYPE_FULLTEXT:
3393  $prefix = 'fti_';
3394  break;
3395  case AdapterInterface::INDEX_TYPE_INDEX:
3396  default:
3397  $prefix = 'idx_';
3398  }
3399  return strtoupper(ExpressionConverter::shortenEntityName($tableName . '_' . $fields, $prefix));
3400  }
3401 
3413  public function getForeignKeyName($priTableName, $priColumnName, $refTableName, $refColumnName)
3414  {
3415  $fkName = sprintf('%s_%s_%s_%s', $priTableName, $priColumnName, $refTableName, $refColumnName);
3416  return strtoupper(ExpressionConverter::shortenEntityName($fkName, 'fk_'));
3417  }
3418 
3426  public function disableTableKeys($tableName, $schemaName = null)
3427  {
3428  $tableName = $this->_getTableName($tableName, $schemaName);
3429  $query = sprintf('ALTER TABLE %s DISABLE KEYS', $this->quoteIdentifier($tableName));
3430  $this->query($query);
3431 
3432  return $this;
3433  }
3434 
3442  public function enableTableKeys($tableName, $schemaName = null)
3443  {
3444  $tableName = $this->_getTableName($tableName, $schemaName);
3445  $query = sprintf('ALTER TABLE %s ENABLE KEYS', $this->quoteIdentifier($tableName));
3446  $this->query($query);
3447 
3448  return $this;
3449  }
3450 
3460  public function insertFromSelect(Select $select, $table, array $fields = [], $mode = false)
3461  {
3462  $query = $mode === self::REPLACE ? 'REPLACE' : 'INSERT';
3463 
3464  if ($mode === self::INSERT_IGNORE) {
3465  $query .= ' IGNORE';
3466  }
3467  $query = sprintf('%s INTO %s', $query, $this->quoteIdentifier($table));
3468  if ($fields) {
3469  $columns = array_map([$this, 'quoteIdentifier'], $fields);
3470  $query = sprintf('%s (%s)', $query, join(', ', $columns));
3471  }
3472 
3473  $query = sprintf('%s %s', $query, $select->assemble());
3474 
3475  if ($mode === self::INSERT_ON_DUPLICATE) {
3476  $query .= $this->renderOnDuplicate($table, $fields);
3477  }
3478 
3479  return $query;
3480  }
3481 
3489  private function renderOnDuplicate($table, array $fields)
3490  {
3491  if (!$fields) {
3492  $describe = $this->describeTable($table);
3493  foreach ($describe as $column) {
3494  if ($column['PRIMARY'] === false) {
3495  $fields[] = $column['COLUMN_NAME'];
3496  }
3497  }
3498  }
3499  $update = [];
3500  foreach ($fields as $field) {
3501  $update[] = sprintf('%1$s = VALUES(%1$s)', $this->quoteIdentifier($field));
3502  }
3503 
3504  return count($update) ? ' ON DUPLICATE KEY UPDATE ' . join(', ', $update) : '';
3505  }
3506 
3517  public function selectsByRange($rangeField, \Magento\Framework\DB\Select $select, $stepCount = 100)
3518  {
3519  $iterator = $this->getQueryGenerator()->generate($rangeField, $select, $stepCount);
3520  $queries = [];
3521  foreach ($iterator as $query) {
3522  $queries[] = $query;
3523  }
3524  return $queries;
3525  }
3526 
3533  private function getQueryGenerator()
3534  {
3535  if ($this->queryGenerator === null) {
3536  $this->queryGenerator = \Magento\Framework\App\ObjectManager::getInstance()->create(QueryGenerator::class);
3537  }
3538  return $this->queryGenerator;
3539  }
3540 
3551  public function updateFromSelect(Select $select, $table)
3552  {
3553  if (!is_array($table)) {
3554  $table = [$table => $table];
3555  }
3556 
3557  // get table name and alias
3558  $keys = array_keys($table);
3559  $tableAlias = $keys[0];
3560  $tableName = $table[$keys[0]];
3561 
3562  $query = sprintf('UPDATE %s', $this->quoteTableAs($tableName, $tableAlias));
3563 
3564  // render JOIN conditions (FROM Part)
3565  $joinConds = [];
3566  foreach ($select->getPart(\Magento\Framework\DB\Select::FROM) as $correlationName => $joinProp) {
3567  if ($joinProp['joinType'] == \Magento\Framework\DB\Select::FROM) {
3568  $joinType = strtoupper(\Magento\Framework\DB\Select::INNER_JOIN);
3569  } else {
3570  $joinType = strtoupper($joinProp['joinType']);
3571  }
3572  $joinTable = '';
3573  if ($joinProp['schema'] !== null) {
3574  $joinTable = sprintf('%s.', $this->quoteIdentifier($joinProp['schema']));
3575  }
3576  $joinTable .= $this->quoteTableAs($joinProp['tableName'], $correlationName);
3577 
3578  $join = sprintf(' %s %s', $joinType, $joinTable);
3579 
3580  if (!empty($joinProp['joinCondition'])) {
3581  $join = sprintf('%s ON %s', $join, $joinProp['joinCondition']);
3582  }
3583 
3584  $joinConds[] = $join;
3585  }
3586 
3587  if ($joinConds) {
3588  $query = sprintf("%s\n%s", $query, implode("\n", $joinConds));
3589  }
3590 
3591  // render UPDATE SET
3592  $columns = [];
3593  foreach ($select->getPart(\Magento\Framework\DB\Select::COLUMNS) as $columnEntry) {
3594  list($correlationName, $column, $alias) = $columnEntry;
3595  if (empty($alias)) {
3596  $alias = $column;
3597  }
3598  if (!$column instanceof \Zend_Db_Expr && !empty($correlationName)) {
3599  $column = $this->quoteIdentifier([$correlationName, $column]);
3600  }
3601  $columns[] = sprintf('%s = %s', $this->quoteIdentifier([$tableAlias, $alias]), $column);
3602  }
3603 
3604  if (!$columns) {
3605  throw new LocalizedException(
3606  new \Magento\Framework\Phrase('The columns for UPDATE statement are not defined')
3607  );
3608  }
3609 
3610  $query = sprintf("%s\nSET %s", $query, implode(', ', $columns));
3611 
3612  // render WHERE
3613  $wherePart = $select->getPart(\Magento\Framework\DB\Select::WHERE);
3614  if ($wherePart) {
3615  $query = sprintf("%s\nWHERE %s", $query, implode(' ', $wherePart));
3616  }
3617 
3618  return $query;
3619  }
3620 
3628  public function deleteFromSelect(Select $select, $table)
3629  {
3630  $select = clone $select;
3631  $select->reset(\Magento\Framework\DB\Select::DISTINCT);
3632  $select->reset(\Magento\Framework\DB\Select::COLUMNS);
3633 
3634  $query = sprintf('DELETE %s %s', $this->quoteIdentifier($table), $select->assemble());
3635 
3636  return $query;
3637  }
3638 
3646  public function getTablesChecksum($tableNames, $schemaName = null)
3647  {
3648  $result = [];
3649  $tableNames = is_array($tableNames) ? $tableNames : [$tableNames];
3650 
3651  foreach ($tableNames as $tableName) {
3652  $query = 'CHECKSUM TABLE ' . $this->_getTableName($tableName, $schemaName);
3653  $checkSumArray = $this->fetchRow($query);
3654  $result[$tableName] = $checkSumArray['Checksum'];
3655  }
3656 
3657  return $result;
3658  }
3659 
3665  public function supportStraightJoin()
3666  {
3667  return true;
3668  }
3669 
3678  public function orderRand(Select $select, $field = null)
3679  {
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');
3684  } else {
3685  $spec = new \Zend_Db_Expr('RAND()');
3686  }
3687  $select->order($spec);
3688 
3689  return $this;
3690  }
3691 
3698  public function forUpdate($sql)
3699  {
3700  return sprintf('%s FOR UPDATE', $sql);
3701  }
3702 
3710  protected function _prepareInsertData($row, &$bind)
3711  {
3712  $row = (array)$row;
3713  $line = [];
3714  foreach ($row as $value) {
3715  if ($value instanceof \Zend_Db_Expr) {
3716  $line[] = $value->__toString();
3717  } else {
3718  $line[] = '?';
3719  $bind[] = $value;
3720  }
3721  }
3722  $line = implode(', ', $line);
3723 
3724  return sprintf('(%s)', $line);
3725  }
3726 
3736  protected function _getInsertSqlQuery($tableName, array $columns, array $values, $strategy = null)
3737  {
3738  $tableName = $this->quoteIdentifier($tableName, true);
3739  $columns = array_map([$this, 'quoteIdentifier'], $columns);
3740  $columns = implode(',', $columns);
3741  $values = implode(', ', $values);
3742  $strategy = $strategy === self::INSERT_IGNORE ? 'IGNORE' : '';
3743 
3744  $insertSql = sprintf('INSERT %s INTO %s (%s) VALUES %s', $strategy, $tableName, $columns, $values);
3745 
3746  return $insertSql;
3747  }
3748 
3758  protected function _getReplaceSqlQuery($tableName, array $columns, array $values)
3759  {
3760  $tableName = $this->quoteIdentifier($tableName, true);
3761  $columns = array_map([$this, 'quoteIdentifier'], $columns);
3762  $columns = implode(',', $columns);
3763  $values = implode(', ', $values);
3764 
3765  $replaceSql = sprintf('REPLACE INTO %s (%s) VALUES %s', $tableName, $columns, $values);
3766 
3767  return $replaceSql;
3768  }
3769 
3776  protected function _getDdlType($options)
3777  {
3778  $ddlType = null;
3779  if (isset($options['TYPE'])) {
3780  $ddlType = $options['TYPE'];
3781  } elseif (isset($options['COLUMN_TYPE'])) {
3782  $ddlType = $options['COLUMN_TYPE'];
3783  }
3784 
3785  return $ddlType;
3786  }
3787 
3794  protected function _getDdlAction($action)
3795  {
3796  switch ($action) {
3797  case AdapterInterface::FK_ACTION_CASCADE:
3798  return Table::ACTION_CASCADE;
3799  case AdapterInterface::FK_ACTION_SET_NULL:
3800  return Table::ACTION_SET_NULL;
3801  case AdapterInterface::FK_ACTION_RESTRICT:
3802  return Table::ACTION_RESTRICT;
3803  default:
3804  return Table::ACTION_NO_ACTION;
3805  }
3806  }
3807 
3815  protected function _prepareSqlDateCondition($condition, $key)
3816  {
3817  if (empty($condition['date'])) {
3818  if (empty($condition['datetime'])) {
3819  $result = $condition[$key];
3820  } else {
3821  $result = $this->formatDate($condition[$key]);
3822  }
3823  } else {
3824  $result = $this->formatDate($condition[$key]);
3825  }
3826 
3827  return $result;
3828  }
3829 
3837  public function getPrimaryKeyName($tableName, $schemaName = null)
3838  {
3839  $indexes = $this->getIndexList($tableName, $schemaName);
3840  if (isset($indexes['PRIMARY'])) {
3841  return $indexes['PRIMARY']['KEY_NAME'];
3842  } else {
3843  return 'PK_' . strtoupper($tableName);
3844  }
3845  }
3846 
3854  protected function _parseTextSize($size)
3855  {
3856  $size = trim($size);
3857  $last = strtolower(substr($size, -1));
3858 
3859  switch ($last) {
3860  case 'k':
3861  $size = intval($size) * 1024;
3862  break;
3863  case 'm':
3864  $size = intval($size) * 1024 * 1024;
3865  break;
3866  case 'g':
3867  $size = intval($size) * 1024 * 1024 * 1024;
3868  break;
3869  }
3870 
3871  if (empty($size)) {
3872  return Table::DEFAULT_TEXT_SIZE;
3873  }
3874  if ($size >= Table::MAX_TEXT_SIZE) {
3875  return Table::MAX_TEXT_SIZE;
3876  }
3877 
3878  return intval($size);
3879  }
3880 
3888  public function decodeVarbinary($value)
3889  {
3890  return $value;
3891  }
3892 
3900  public function createTrigger(\Magento\Framework\DB\Ddl\Trigger $trigger)
3901  {
3902  if (!$trigger->getStatements()) {
3903  throw new \Zend_Db_Exception(
3904  (string)new \Magento\Framework\Phrase(
3905  'Trigger %1 has not statements available',
3906  [$trigger->getName()]
3907  )
3908  );
3909  }
3910 
3911  $statements = implode("\n", $trigger->getStatements());
3912 
3913  $sql = sprintf(
3914  "CREATE TRIGGER %s %s %s ON %s FOR EACH ROW\nBEGIN\n%s\nEND",
3915  $trigger->getName(),
3916  $trigger->getTime(),
3917  $trigger->getEvent(),
3918  $trigger->getTable(),
3919  $statements
3920  );
3921 
3922  return $this->multiQuery($sql);
3923  }
3924 
3933  public function dropTrigger($triggerName, $schemaName = null)
3934  {
3935  if (empty($triggerName)) {
3936  throw new \InvalidArgumentException((string)new \Magento\Framework\Phrase('Trigger name is not defined'));
3937  }
3938 
3939  $triggerName = ($schemaName ? $schemaName . '.' : '') . $triggerName;
3940 
3941  $sql = 'DROP TRIGGER IF EXISTS ' . $this->quoteIdentifier($triggerName);
3942  $this->query($sql);
3943 
3944  return true;
3945  }
3946 
3952  public function __destruct()
3953  {
3954  if ($this->_transactionLevel > 0) {
3955  trigger_error('Some transactions have not been committed or rolled back', E_USER_ERROR);
3956  }
3957  }
3958 
3965  public function getTables($likeCondition = null)
3966  {
3967  $sql = ($likeCondition === null) ? 'SHOW TABLES' : sprintf("SHOW TABLES LIKE '%s'", $likeCondition);
3968  $result = $this->query($sql);
3969  $tables = [];
3970  while ($row = $result->fetchColumn()) {
3971  $tables[] = $row;
3972  }
3973  return $tables;
3974  }
3975 
3984  public function getAutoIncrementField($tableName, $schemaName = null)
3985  {
3986  $indexName = $this->getPrimaryKeyName($tableName, $schemaName);
3987  $indexes = $this->getIndexList($tableName);
3988  if ($indexName && count($indexes[$indexName]['COLUMNS_LIST']) == 1) {
3989  return current($indexes[$indexName]['COLUMNS_LIST']);
3990  }
3991  return false;
3992  }
3993 
4001  public function getSchemaListener()
4002  {
4003  if ($this->schemaListener === null) {
4004  $this->schemaListener = \Magento\Framework\App\ObjectManager::getInstance()->create(SchemaListener::class);
4005  }
4006  return $this->schemaListener;
4007  }
4008 }
Magento\Framework\DB\Adapter\Pdo\Mysql\newTable
newTable($tableName=null, $schemaName=null)
Definition: Mysql.php:2082
Magento\Framework\DB\Adapter\Pdo\Mysql\createTableByDdl
createTableByDdl($tableName, $newTableName)
Definition: Mysql.php:1735
$result
$result
Definition: product_configurable_not_available_rollback.php:26
Magento\Framework\DB\Adapter\Pdo\Mysql\changeColumn
changeColumn( $tableName, $oldColumnName, $newColumnName, $definition, $flushData=false, $schemaName=null)
Definition: Mysql.php:1082
Magento\Framework\DB\Adapter\Pdo\Mysql\addForeignKey
addForeignKey( $fkName, $tableName, $columnName, $refTableName, $refColumnName, $onDelete=AdapterInterface::FK_ACTION_CASCADE, $purge=false, $schemaName=null, $refSchemaName=null)
Definition: Mysql.php:2800
$tableName
$tableName
Definition: trigger.php:13
Magento\Framework\DB\Adapter\Pdo\Mysql\isTableExists
isTableExists($tableName, $schemaName=null)
Definition: Mysql.php:2611
Magento\Framework\DB\Adapter\Pdo\Mysql\rawFetchRow
rawFetchRow($sql, $field=null)
Definition: Mysql.php:479
Magento\Framework\DB\Adapter\Pdo\Mysql\getTables
getTables($likeCondition=null)
Definition: Mysql.php:3965
Magento\Framework\DB\Adapter\Pdo\Mysql\getCheckSql
getCheckSql($expression, $true, $false)
Definition: Mysql.php:3113
$map
$map
Definition: classmap_generator.php:167
elseif
elseif(isset( $params[ 'redirect_parent']))
Definition: iframe.phtml:17
Magento\Framework\DB\Adapter\Pdo\Mysql\disableTableKeys
disableTableKeys($tableName, $schemaName=null)
Definition: Mysql.php:3426
Magento\Framework\DB\Adapter\Pdo\Mysql\quoteInto
quoteInto($text, $value, $type=null, $count=null)
Definition: Mysql.php:1479
Magento\Framework\DB\Adapter\Pdo\Mysql\getPrimaryKeyName
getPrimaryKeyName($tableName, $schemaName=null)
Definition: Mysql.php:3837
Magento\Framework\DB\Adapter\Pdo\Mysql\prepareColumnValue
prepareColumnValue(array $column, $value)
Definition: Mysql.php:3029
Magento\Framework\DB\Adapter\Pdo\Mysql\getColumnDefinitionFromDescribe
getColumnDefinitionFromDescribe($options, $ddlType=null)
Definition: Mysql.php:2391
Magento\Framework\DB\Adapter\Pdo\Mysql\getSubstringSql
getSubstringSql($stringExpression, $pos, $len=null)
Definition: Mysql.php:3307
Magento\Framework\DB\Adapter\Pdo\Mysql\prepareSqlCondition
prepareSqlCondition($fieldName, $condition)
Definition: Mysql.php:2920
Zend_Db_Adapter_Pdo_Mysql\_dsn
_dsn()
Definition: Mysql.php:83
Magento\Framework\DB\ExpressionConverter\shortenEntityName
static shortenEntityName($entityName, $prefix)
Definition: ExpressionConverter.php:100
$config
$config
Definition: fraud_order.php:17
Zend_Db_Adapter_Abstract\getProfiler
getProfiler()
Definition: Abstract.php:423
Magento\Framework\DB\Adapter\Pdo\Mysql\selectsByRange
selectsByRange($rangeField, \Magento\Framework\DB\Select $select, $stepCount=100)
Definition: Mysql.php:3517
Magento\Framework\DB\Adapter\Pdo\Mysql\insertFromSelect
insertFromSelect(Select $select, $table, array $fields=[], $mode=false)
Definition: Mysql.php:3460
Magento\Framework\DB\Adapter\Pdo\Mysql\renameTablesBatch
renameTablesBatch(array $tablePairs)
Definition: Mysql.php:2191
Magento\Framework\DB\Adapter\Pdo\Mysql\addColumn
addColumn($tableName, $columnName, $definition, $schemaName=null)
Definition: Mysql.php:964
Magento\Framework\DB\Adapter\Pdo\Mysql\insertArray
insertArray($table, array $columns, array $data, $strategy=0)
Definition: Mysql.php:2036
$count
$count
Definition: recent.phtml:13
$case
$case
Definition: approved_case.php:24
Magento\Framework\DB\Adapter\Pdo\Mysql\createTrigger
createTrigger(\Magento\Framework\DB\Ddl\Trigger $trigger)
Definition: Mysql.php:3900
Magento\Framework\DB\Adapter\Pdo\Mysql\purgeOrphanRecords
purgeOrphanRecords( $tableName, $columnName, $refTableName, $refColumnName, $onDelete=AdapterInterface::FK_ACTION_CASCADE)
Definition: Mysql.php:895
Magento\Framework\DB\Adapter\Pdo\Mysql\modifyColumnByDdl
modifyColumnByDdl($tableName, $columnName, $definition, $flushData=false, $schemaName=null)
Definition: Mysql.php:1806
Magento\Framework\DB\Adapter\Pdo\Mysql\getIfNullSql
getIfNullSql($expression, $value=0)
Definition: Mysql.php:3131
Zend_Db_Adapter_Pdo_Abstract\closeConnection
closeConnection()
Definition: Abstract.php:164
$fields
$fields
Definition: details.phtml:14
$values
$values
Definition: options.phtml:88
Zend_Db_Adapter_Exception
Definition: Exception.php:35
Zend_Db_Adapter_Pdo_Mysql
Definition: Mysql.php:39
Magento\Framework\DB\Adapter\Pdo\Mysql\getIndexName
getIndexName($tableName, $fields, $indexType='')
Definition: Mysql.php:3382
$text
endifif( $block->getLastPageNum()>1)( 'Page') ?></strong >< ul class $text
Definition: pager.phtml:43
Zend_Db_Statement_Exception
Definition: Exception.php:37
Magento\Framework\DB\Adapter\Pdo\Mysql\setCacheAdapter
setCacheAdapter(FrontendInterface $cacheAdapter)
Definition: Mysql.php:2069
$mask
$mask
Definition: bootstrap.php:36
Magento\Framework\DB\Adapter\Pdo\Mysql\resetDdlCache
resetDdlCache($tableName=null, $schemaName=null)
Definition: Mysql.php:1578
Zend_Db_Select\FROM
const FROM
Definition: Select.php:49
Magento\Framework\DB\Adapter\Pdo\Mysql\showTableStatus
showTableStatus($tableName, $schemaName=null)
Definition: Mysql.php:1176
Magento\Framework\DB\Adapter\Pdo\Mysql\dropIndex
dropIndex($tableName, $keyName, $schemaName=null)
Definition: Mysql.php:2754
Magento\Framework\DB\Adapter\AdapterInterface\quoteTableAs
quoteTableAs($ident, $alias=null, $auto=false)
Zend_Db_Expr
Definition: Expr.php:49
$columns
$columns
Definition: default.phtml:15
$type
$type
Definition: item.phtml:13
Zend_Db_Select
Definition: Select.php:44
Magento\Framework\DB\Adapter\Pdo\Mysql\_getReplaceSqlQuery
_getReplaceSqlQuery($tableName, array $columns, array $values)
Definition: Mysql.php:3758
Magento\Framework\DB\Adapter\Pdo\Mysql\getStandardDeviationSql
getStandardDeviationSql($expressionField)
Definition: Mysql.php:3321
$select
$select
Definition: catalog_rule_10_off_not_logged_rollback.php:14
Magento\Framework\DB\Adapter\Pdo\Mysql\createTemporaryTableLike
createTemporaryTableLike($temporaryTableName, $originTableName, $ifNotExists=false)
Definition: Mysql.php:2173
Magento\Framework\DB\Adapter\Pdo\Mysql\_transformStringSqlCondition
_transformStringSqlCondition($conditionKey, $value)
Definition: Mysql.php:3008
$prefix
$prefix
Definition: name.phtml:25
$data
$data
Definition: attribute_set_with_image_attribute.php:16
Magento\Framework\DB\Adapter\Pdo\Mysql\deleteFromSelect
deleteFromSelect(Select $select, $table)
Definition: Mysql.php:3628
$value
$value
Definition: gender.phtml:16
$format
$format
Definition: list.phtml:12
Magento\Framework\DB\Adapter\Pdo\Mysql\getDateAddSql
getDateAddSql($date, $interval, $unit)
Definition: Mysql.php:3244
Magento\Framework\DB\Adapter\Pdo\Mysql\getForeignKeys
getForeignKeys($tableName, $schemaName=null)
Definition: Mysql.php:1229
Zend_Db_Select\DISTINCT
const DISTINCT
Definition: Select.php:47
Magento\Framework\DB\Adapter\Pdo\Mysql\renameTable
renameTable($oldTableName, $newTableName, $schemaName=null)
Definition: Mysql.php:2625
Magento\Framework\DB\Adapter\Pdo\Mysql\getAutoIncrementField
getAutoIncrementField($tableName, $schemaName=null)
Definition: Mysql.php:3984
$pos
$pos
Definition: list.phtml:42
Magento\Framework\DB\Adapter\Pdo\Mysql\_removeDuplicateEntry
_removeDuplicateEntry($table, $fields, $ids)
Definition: Mysql.php:1430
Zend_Db_Select\INNER_JOIN
const INNER_JOIN
Definition: Select.php:59
$mode
if($exist=($block->getProductCollection() && $block->getProductCollection() ->getSize())) $mode
Definition: grid.phtml:15
Zend_Db_Select\COLUMNS
const COLUMNS
Definition: Select.php:48
Magento\Framework\DB\Adapter\Pdo\Mysql\changeTableEngine
changeTableEngine($tableName, $engine, $schemaName=null)
Definition: Mysql.php:1870
Magento\Framework\DB\Adapter\Pdo\Mysql\saveDdlCache
saveDdlCache($tableCacheKey, $ddlType, $data)
Definition: Mysql.php:1554
Magento\Framework\DB\Adapter\Pdo\Mysql\loadDdlCache
loadDdlCache($tableCacheKey, $ddlType)
Definition: Mysql.php:1524
Magento\Framework\DB\Adapter\Pdo\Mysql\getTriggerName
getTriggerName($tableName, $time, $event)
Definition: Mysql.php:3367
Magento\Framework\DB\Adapter\Pdo\Mysql\_getColumnDefinition
_getColumnDefinition($options, $ddlType=null)
Definition: Mysql.php:2413
Zend_Db_Select\WHERE
const WHERE
Definition: Select.php:51
Magento\Framework\DB\Adapter\AdapterInterface\fetchRow
fetchRow($sql, $bind=[], $fetchMode=null)
Magento\Framework\DB\Adapter\Pdo\Mysql\tableColumnExists
tableColumnExists($tableName, $columnName, $schemaName=null)
Definition: Mysql.php:939
Magento\Framework\DB\Adapter\Pdo\Mysql\_prepareQuotedSqlCondition
_prepareQuotedSqlCondition($text, $value, $fieldName)
Definition: Mysql.php:2992
Magento\Framework\DB\Adapter\Pdo\Mysql\getTablesChecksum
getTablesChecksum($tableNames, $schemaName=null)
Definition: Mysql.php:3646
Magento\Framework\DB\Adapter\Pdo\Mysql\dropTrigger
dropTrigger($triggerName, $schemaName=null)
Definition: Mysql.php:3933
Magento\Framework\DB\Adapter\Pdo\Mysql\changeTableComment
changeTableComment($tableName, $comment, $schemaName=null)
Definition: Mysql.php:1886
Magento\Framework\DB\Adapter\Pdo\Mysql\insertOnDuplicate
insertOnDuplicate($table, array $data, array $fields=[])
Definition: Mysql.php:1922
$trigger
$trigger
Definition: trigger.php:27
Magento\Framework\DB\Adapter\Pdo\Mysql\_getIntervalUnitSql
_getIntervalUnitSql($interval, $unit)
Definition: Mysql.php:3225
$method
$method
Definition: info.phtml:13
$query
$query
Definition: popular_query.php:9
Magento\Framework\DB\Adapter\Pdo\Mysql\getDateSubSql
getDateSubSql($date, $interval, $unit)
Definition: Mysql.php:3260
Magento\Framework\DB\Adapter\Pdo\Mysql\_getInsertSqlQuery
_getInsertSqlQuery($tableName, array $columns, array $values, $strategy=null)
Definition: Mysql.php:3736
Magento\Framework\DB\Adapter\Pdo\Mysql\modifyColumn
modifyColumn($tableName, $columnName, $definition, $flushData=false, $schemaName=null)
Definition: Mysql.php:1139
Magento\Framework\DB\Adapter\Pdo\Mysql\dropTemporaryTable
dropTemporaryTable($tableName, $schemaName=null)
Definition: Mysql.php:2574
Magento\Framework\DB\Adapter\Pdo\Mysql\dropColumn
dropColumn($tableName, $columnName, $schemaName=null)
Definition: Mysql.php:1006
Magento\Framework\DB\Adapter\Pdo\Mysql\getForeignKeyName
getForeignKeyName($priTableName, $priColumnName, $refTableName, $refColumnName)
Definition: Mysql.php:3413
Magento\Framework\DB\Adapter\Pdo\Mysql\enableTableKeys
enableTableKeys($tableName, $schemaName=null)
Definition: Mysql.php:3442
Magento\Framework\DB\Adapter\Pdo\Mysql\orderRand
orderRand(Select $select, $field=null)
Definition: Mysql.php:3678
Magento\Framework\DB\Adapter\Pdo\Mysql\dropForeignKey
dropForeignKey($tableName, $fkName, $schemaName=null)
Definition: Mysql.php:863
Magento\Framework\DB\Adapter\Pdo\Mysql\_getIndexByColumns
_getIndexByColumns($tableName, array $columns, $schemaName)
Definition: Mysql.php:1058
Magento\Framework\DB\Adapter\Pdo\Mysql\insertForce
insertForce($table, array $bind)
Definition: Mysql.php:1902
Zend_Db_Adapter_Pdo_Mysql\listTables
listTables()
Definition: Mysql.php:127
Zend_Cache\CLEANING_MODE_MATCHING_TAG
const CLEANING_MODE_MATCHING_TAG
Definition: Cache.php:74
$alias
if(!trim($html)) $alias
Definition: details.phtml:20
Magento\Framework\DB\Adapter\Pdo\Mysql\_getTableName
_getTableName($tableName, $schemaName=null)
Definition: Mysql.php:1499
Magento\Framework\DB\Adapter\Pdo\Mysql\getCreateTable
getCreateTable($tableName, $schemaName=null)
Definition: Mysql.php:1194
Magento\Framework\DB\Adapter\Pdo\Mysql\formatDate
formatDate($date, $includeTime=true)
Definition: Mysql.php:2851
$connection
$connection
Definition: bulk.php:13
Magento\Framework\DB\Adapter\Pdo\Mysql\getCaseSql
getCaseSql($valueName, $casesResults, $defaultValue=null)
Definition: Mysql.php:3151
Magento\Framework\DB\Adapter\Pdo\Mysql\updateFromSelect
updateFromSelect(Select $select, $table)
Definition: Mysql.php:3551
$table
$table
Definition: trigger.php:14
Magento\Framework\DB\Adapter\Pdo\Mysql\dropTable
dropTable($tableName, $schemaName=null)
Definition: Mysql.php:2553
Magento\Framework\DB\Adapter\Pdo\Mysql\getIndexList
getIndexList($tableName, $schemaName=null)
Definition: Mysql.php:1372
Magento\Framework\DB\Adapter\Pdo\Mysql\describeTable
describeTable($tableName, $schemaName=null)
Definition: Mysql.php:1655
Magento\Framework\DB\Adapter\AdapterInterface\fetchAll
fetchAll($sql, $bind=[], $fetchMode=null)
Magento\Framework\DB\Adapter\Pdo\Mysql\insertMultiple
insertMultiple($table, array $data)
Definition: Mysql.php:1991
Magento\Framework\DB\Adapter\Pdo\Mysql\getConcatSql
getConcatSql(array $data, $separator=null)
Definition: Mysql.php:3173
$i
$i
Definition: gallery.phtml:31
Magento\Framework\DB\Adapter\Pdo\Mysql\createTemporaryTable
createTemporaryTable(\Magento\Framework\DB\Ddl\Table $table)
Definition: Mysql.php:2146
Magento\Framework\DB\Adapter\Pdo\Mysql\addIndex
addIndex( $tableName, $indexName, $fields, $indexType=AdapterInterface::INDEX_TYPE_INDEX, $schemaName=null)
Definition: Mysql.php:2663
$options
$options
Definition: multiple_mixed_products.php:29
Magento\Framework\DB\Adapter\Pdo\Mysql\_prepareSqlDateCondition
_prepareSqlDateCondition($condition, $key)
Definition: Mysql.php:3815
Magento\Framework\DB\Adapter\Pdo\Mysql\truncateTable
truncateTable($tableName, $schemaName=null)
Definition: Mysql.php:2591
$row
$row
Definition: cart_rule_free_shipping.php:12
Magento\Framework\DB\Adapter\Pdo\Mysql\_getCacheId
_getCacheId($tableKey, $ddlType)
Definition: Mysql.php:1511
Magento\Framework\DB\Adapter\Pdo\Mysql\__construct
__construct(StringUtils $string, DateTime $dateTime, LoggerInterface $logger, SelectFactory $selectFactory, array $config=[], SerializerInterface $serializer=null)
Definition: Mysql.php:237