PHP AdapterInterface::quoteIdentifier方法代码示例

 /**
  * Prepare select statement for specific filter
  *
  * @param array $data
  * @return \Magento\Framework\DB\Select
  */
 protected function prepareSelect($data)
 {
     $select = $this->connection->select();
     $select->from($this->resource->getTableName(self::TABLE_NAME));
     foreach ($data as $column => $value) {
         $select->where($this->connection->quoteIdentifier($column) . ' IN (?)', $value);
     }
     return $select;
 }

 /**
  * Prepare select statement for specific filter
  *
  * @param Filter $filter
  * @return \Magento\Framework\DB\Select
  */
 protected function prepareSelect($filter)
 {
     $select = $this->connection->select();
     $select->from(self::TABLE_NAME);
     foreach ($filter->getFilter() as $column => $value) {
         $select->where($this->connection->quoteIdentifier($column) . ' IN (?)', $value);
     }
     return $select;
 }

 /**
  * Returns SQL expression
  *   TRIM(CONCAT_WS(separator, IF(str1 <> '', str1, NULL), IF(str2 <> '', str2, NULL) ...))
  *
  * @return string
  */
 public function __toString()
 {
     $columns = [];
     foreach ($this->columns as $key => $part) {
         if (isset($part['columnName']) && $part['columnName'] instanceof \Zend_Db_Expr) {
             $column = $part['columnName'];
         } else {
             $column = $this->adapter->quoteIdentifier((isset($part['tableAlias']) ? $part['tableAlias'] . '.' : '') . (isset($part['columnName']) ? $part['columnName'] : $key));
         }
         $columns[] = $this->adapter->getCheckSql($column . " <> ''", $column, 'NULL');
     }
     return sprintf('TRIM(%s)', $this->adapter->getConcatSql($columns, ' '));
 }

 /**
  * Populate the temporary category tree index table
  *
  * @param string $temporaryName
  */
 protected function fillTempCategoryTreeIndex($temporaryName)
 {
     // This finds all children (cc2) that descend from a parent (cc) by path.
     // For example, cc.path may be '1/2', and cc2.path may be '1/2/3/4/5'.
     $temporarySelect = $this->connection->select()->from(['cc' => $this->getTable('catalog_category_entity')], ['parent_id' => 'entity_id'])->joinInner(['cc2' => $this->getTable('catalog_category_entity')], 'cc2.path LIKE ' . $this->connection->getConcatSql([$this->connection->quoteIdentifier('cc.path'), $this->connection->quote('/%')]), ['child_id' => 'entity_id']);
     $this->connection->query($temporarySelect->insertFromSelect($temporaryName, ['parent_id', 'child_id']));
 }

 /**
  * @param FilterInterface $filter
  * @param bool $isNegation
  * @param string $query
  * @return string
  */
 private function processQueryWithField(FilterInterface $filter, $isNegation, $query)
 {
     /** @var Attribute $attribute */
     $attribute = $this->config->getAttribute(Product::ENTITY, $filter->getField());
     if ($filter->getField() === 'price') {
         $resultQuery = str_replace($this->connection->quoteIdentifier('price'), $this->connection->quoteIdentifier('price_index.min_price'), $query);
     } elseif ($filter->getField() === 'category_ids') {
         return 'category_ids_index.category_id = ' . $filter->getValue();
     } elseif ($attribute->isStatic()) {
         $alias = $this->tableMapper->getMappingAlias($filter);
         $resultQuery = str_replace($this->connection->quoteIdentifier($attribute->getAttributeCode()), $this->connection->quoteIdentifier($alias . '.' . $attribute->getAttributeCode()), $query);
     } elseif ($filter->getType() === FilterInterface::TYPE_TERM && in_array($attribute->getFrontendInput(), ['select', 'multiselect'], true)) {
         $resultQuery = $this->processTermSelect($filter, $isNegation);
     } elseif ($filter->getType() === FilterInterface::TYPE_RANGE && in_array($attribute->getBackendType(), ['decimal', 'int'], true)) {
         $resultQuery = $this->processRangeNumeric($filter, $query, $attribute);
     } else {
         $table = $attribute->getBackendTable();
         $select = $this->connection->select();
         $ifNullCondition = $this->connection->getIfNullSql('current_store.value', 'main_table.value');
         $currentStoreId = $this->scopeResolver->getScope()->getId();
         $select->from(['main_table' => $table], 'entity_id')->joinLeft(['current_store' => $table], 'current_store.attribute_id = main_table.attribute_id AND current_store.store_id = ' . $currentStoreId, null)->columns([$filter->getField() => $ifNullCondition])->where('main_table.attribute_id = ?', $attribute->getAttributeId())->where('main_table.store_id = ?', Store::DEFAULT_STORE_ID)->having($query);
         $resultQuery = 'search_index.entity_id IN (
             select entity_id from  ' . $this->conditionManager->wrapBrackets($select) . ' as filter
         )';
     }
     return $resultQuery;
 }

 /**
  * Create anchor select
  *
  * @param \Magento\Store\Model\Store $store
  * @return \Magento\Framework\DB\Select
  */
 protected function createAnchorSelect(\Magento\Store\Model\Store $store)
 {
     $isAnchorAttributeId = $this->config->getAttribute(\Magento\Catalog\Model\Category::ENTITY, 'is_anchor')->getId();
     $statusAttributeId = $this->config->getAttribute(\Magento\Catalog\Model\Product::ENTITY, 'status')->getId();
     $visibilityAttributeId = $this->config->getAttribute(\Magento\Catalog\Model\Product::ENTITY, 'visibility')->getId();
     $rootCatIds = explode('/', $this->getPathFromCategoryId($store->getRootCategoryId()));
     array_pop($rootCatIds);
     return $this->connection->select()->from(['cc' => $this->getTable('catalog_category_entity')], [])->joinInner(['cc2' => $this->getTable('catalog_category_entity')], 'cc2.path LIKE ' . $this->connection->getConcatSql([$this->connection->quoteIdentifier('cc.path'), $this->connection->quote('/%')]) . ' AND cc.entity_id NOT IN (' . implode(',', $rootCatIds) . ')', [])->joinInner(['ccp' => $this->getTable('catalog_category_product')], 'ccp.category_id = cc2.entity_id', [])->joinInner(['cpw' => $this->getTable('catalog_product_website')], 'cpw.product_id = ccp.product_id', [])->joinInner(['cpsd' => $this->getTable('catalog_product_entity_int')], 'cpsd.entity_id = ccp.product_id AND cpsd.store_id = 0' . ' AND cpsd.attribute_id = ' . $statusAttributeId, [])->joinLeft(['cpss' => $this->getTable('catalog_product_entity_int')], 'cpss.entity_id = ccp.product_id AND cpss.attribute_id = cpsd.attribute_id' . ' AND cpss.store_id = ' . $store->getId(), [])->joinInner(['cpvd' => $this->getTable('catalog_product_entity_int')], 'cpvd.entity_id = ccp.product_id AND cpvd.store_id = 0' . ' AND cpvd.attribute_id = ' . $visibilityAttributeId, [])->joinLeft(['cpvs' => $this->getTable('catalog_product_entity_int')], 'cpvs.entity_id = ccp.product_id AND cpvs.attribute_id = cpvd.attribute_id ' . 'AND cpvs.store_id = ' . $store->getId(), [])->joinInner(['ccad' => $this->getTable('catalog_category_entity_int')], 'ccad.entity_id = cc.entity_id AND ccad.store_id = 0' . ' AND ccad.attribute_id = ' . $isAnchorAttributeId, [])->joinLeft(['ccas' => $this->getTable('catalog_category_entity_int')], 'ccas.entity_id = cc.entity_id AND ccas.attribute_id = ccad.attribute_id' . ' AND ccas.store_id = ' . $store->getId(), [])->where('cpw.website_id = ?', $store->getWebsiteId())->where($this->connection->getIfNullSql('cpss.value', 'cpsd.value') . ' = ?', \Magento\Catalog\Model\Product\Attribute\Source\Status::STATUS_ENABLED)->where($this->connection->getIfNullSql('cpvs.value', 'cpvd.value') . ' IN (?)', [\Magento\Catalog\Model\Product\Visibility::VISIBILITY_IN_CATALOG, \Magento\Catalog\Model\Product\Visibility::VISIBILITY_IN_SEARCH, \Magento\Catalog\Model\Product\Visibility::VISIBILITY_BOTH])->where($this->connection->getIfNullSql('ccas.value', 'ccad.value') . ' = ?', 1)->columns(['category_id' => 'cc.entity_id', 'product_id' => 'ccp.product_id', 'position' => new \Zend_Db_Expr('ccp.position + 10000'), 'is_parent' => new \Zend_Db_Expr('0'), 'store_id' => new \Zend_Db_Expr($store->getId()), 'visibility' => new \Zend_Db_Expr($this->connection->getIfNullSql('cpvs.value', 'cpvd.value'))]);
 }

 /**
  * Process ORDER BY clause
  *
  * @param Select $select
  * @return void
  */
 protected function processSortOrder(Select $select)
 {
     foreach ($this->sortOrder as $direction => $column) {
         if (!in_array($direction, [Select::SQL_ASC, Select::SQL_DESC])) {
             $direction = '';
         }
         $expr = new \Zend_Db_Expr(sprintf('%s %s', $this->adapter->quoteIdentifier('lookup.' . $column), $direction));
         $select->order($expr);
     }
 }

 /**
  * @param Dimension $dimension
  * @param AdapterInterface $adapter
  * @return string
  */
 private function generateExpression(Dimension $dimension, AdapterInterface $adapter)
 {
     $identifier = $dimension->getName();
     $value = $dimension->getValue();
     if (self::DEFAULT_DIMENSION_NAME === $identifier) {
         $identifier = self::STORE_FIELD_NAME;
         $value = $this->scopeResolver->getScope($value)->getId();
     }
     return sprintf('%s = %s', $adapter->quoteIdentifier($identifier), $adapter->quote($value));
 }

 /**
  * Build trigger statement for INSER, UPDATE, DELETE events
  *
  * @param string $event
  * @param \Magento\Framework\Mview\View\ChangelogInterface $changelog
  * @return string
  */
 protected function buildStatement($event, $changelog)
 {
     switch ($event) {
         case Trigger::EVENT_INSERT:
         case Trigger::EVENT_UPDATE:
             return sprintf("INSERT IGNORE INTO %s (%s) VALUES (NEW.%s);", $this->connection->quoteIdentifier($this->resource->getTableName($changelog->getName())), $this->connection->quoteIdentifier($changelog->getColumnName()), $this->connection->quoteIdentifier($this->getColumnName()));
         case Trigger::EVENT_DELETE:
             return sprintf("INSERT IGNORE INTO %s (%s) VALUES (OLD.%s);", $this->connection->quoteIdentifier($this->resource->getTableName($changelog->getName())), $this->connection->quoteIdentifier($changelog->getColumnName()), $this->connection->quoteIdentifier($this->getColumnName()));
         default:
             return '';
     }
 }

 /**
  * Load node
  *
  * @param int|string $nodeId
  * @return Node
  */
 public function loadNode($nodeId)
 {
     $select = clone $this->_select;
     if (is_numeric($nodeId)) {
         $condField = $this->_conn->quoteIdentifier([$this->_table, $this->_idField]);
     } else {
         $condField = $this->_conn->quoteIdentifier([$this->_table, $this->_pathField]);
     }
     $select->where("{$condField} = ?", $nodeId);
     $node = new Node($this->_conn->fetchRow($select), $this->_idField, $this);
     $this->addNode($node);
     return $node;
 }

 /**
  * @param AbstractCondition $condition
  * @param string $value
  * @return string
  * @throws \Magento\Framework\Exception
  */
 protected function _getMappedSqlCondition(AbstractCondition $condition, $value = '')
 {
     $argument = $condition->getMappedSqlField();
     if ($argument) {
         $conditionOperator = $condition->getOperatorForValidate();
         if (!isset($this->_conditionOperatorMap[$conditionOperator])) {
             throw new \Magento\Framework\Exception('Unknown condition operator');
         }
         $sql = str_replace(':field', $this->_connection->getIfNullSql($this->_connection->quoteIdentifier($argument), 0), $this->_conditionOperatorMap[$conditionOperator]);
         return $this->_expressionFactory->create(['expression' => $value . $this->_connection->quoteInto($sql, $condition->getBindArgumentValue())]);
     }
     return '';
 }

 /**
  * @param Node $node
  * @return $this
  * @throws \Exception
  */
 public function removeNode($node)
 {
     // For reorder old node branch
     $dataReorderOld = [$this->_orderField => new \Zend_Db_Expr($this->_conn->quoteIdentifier($this->_orderField) . '-1')];
     $conditionReorderOld = $this->_conn->quoteIdentifier($this->_parentField) . '=' . $node->getData($this->_parentField) . ' AND ' . $this->_conn->quoteIdentifier($this->_orderField) . '>' . $node->getData($this->_orderField);
     $this->_conn->beginTransaction();
     try {
         $condition = $this->_conn->quoteInto("{$this->_idField}=?", $node->getId());
         $this->_conn->delete($this->_table, $condition);
         // Update old node branch
         $this->_conn->update($this->_table, $dataReorderOld, $conditionReorderOld);
         $this->_conn->commit();
     } catch (\Exception $e) {
         $this->_conn->rollBack();
         throw new \Exception('Can\'t remove tree node');
     }
     parent::removeNode($node);
     return $this;
 }

 /**
  * Get all database triggers
  *
  * @return void
  */
 protected function loadTriggers()
 {
     $schema = $this->getSchemaName();
     if ($schema) {
         $sqlFilter = $this->resourceAdapter->quoteIdentifier('TRIGGER_SCHEMA') . ' = ' . $this->resourceAdapter->quote($schema);
     } else {
         $sqlFilter = $this->resourceAdapter->quoteIdentifier('TRIGGER_SCHEMA') . ' != ' . $this->resourceAdapter->quote('INFORMATION_SCHEMA');
     }
     $select = $this->getSelect()->from(new \Zend_Db_Expr($this->resourceAdapter->quoteIdentifier(['INFORMATION_SCHEMA', 'TRIGGERS'])))->where($sqlFilter);
     $results = $this->resourceAdapter->query($select);
     $data = [];
     foreach ($results as $row) {
         $row = array_change_key_case($row, CASE_LOWER);
         $row['action_statement'] = $this->convertStatement($row['action_statement']);
         $key = $row['event_object_table'] . $row['event_manipulation'] . $row['action_timing'];
         $data[$key] = $row;
     }
     $this->triggers = $data;
 }

 /**
  * Update suffix for url rewrites
  *
  * @return $this
  */
 protected function updateSuffixForUrlRewrites()
 {
     $map = [ProductUrlPathGenerator::XML_PATH_PRODUCT_URL_SUFFIX => ProductUrlRewriteGenerator::ENTITY_TYPE, CategoryUrlPathGenerator::XML_PATH_CATEGORY_URL_SUFFIX => CategoryUrlRewriteGenerator::ENTITY_TYPE];
     if (!isset($map[$this->getPath()])) {
         return $this;
     }
     $dataFilter = [UrlRewrite::ENTITY_TYPE => $map[$this->getPath()]];
     $storesIds = $this->getStoreIds();
     if ($storesIds) {
         $dataFilter[UrlRewrite::STORE_ID] = $storesIds;
     }
     $entities = $this->urlFinder->findAllByData($dataFilter);
     $oldSuffixPattern = '~' . preg_quote($this->getOldValue()) . '$~';
     $suffix = $this->getValue();
     foreach ($entities as $urlRewrite) {
         $bind = $urlRewrite->getIsAutogenerated() ? [UrlRewrite::REQUEST_PATH => preg_replace($oldSuffixPattern, $suffix, $urlRewrite->getRequestPath())] : [UrlRewrite::TARGET_PATH => preg_replace($oldSuffixPattern, $suffix, $urlRewrite->getTargetPath())];
         $this->connection->update(DbStorage::TABLE_NAME, $bind, $this->connection->quoteIdentifier(UrlRewrite::URL_REWRITE_ID) . ' = ' . $urlRewrite->getUrlRewriteId());
     }
     return $this;
 }

 /**
  * Fill temporary flat table by data from temporary flat table parts
  *
  * @param array $tables
  * @param int|string $storeId
  * @param string $valueFieldSuffix
  * @return void
  */
 protected function _fillTemporaryFlatTable(array $tables, $storeId, $valueFieldSuffix)
 {
     $linkField = $this->metadataPool->getMetadata(ProductInterface::class)->getLinkField();
     $select = $this->_connection->select();
     $temporaryFlatTableName = $this->_getTemporaryTableName($this->_productIndexerHelper->getFlatTableName($storeId));
     $flatColumns = $this->_productIndexerHelper->getFlatColumns();
     $entityTableName = $this->_productIndexerHelper->getTable('catalog_product_entity');
     $entityTemporaryTableName = $this->_getTemporaryTableName($entityTableName);
     $columnsList = array_keys($tables[$entityTableName]);
     $websiteId = (int) $this->_storeManager->getStore($storeId)->getWebsiteId();
     unset($tables[$entityTableName]);
     $allColumns = array_merge(['entity_id', 'type_id', 'attribute_set_id'], $columnsList);
     /* @var $status \Magento\Eav\Model\Entity\Attribute */
     $status = $this->_productIndexerHelper->getAttribute('status');
     $statusTable = $this->_getTemporaryTableName($status->getBackendTable());
     $statusConditions = [sprintf('e.%s = dstatus.%s', $linkField, $linkField), 'dstatus.store_id = ' . (int) $storeId, 'dstatus.attribute_id = ' . (int) $status->getId()];
     $statusExpression = $this->_connection->getIfNullSql('dstatus.value', $this->_connection->quoteIdentifier("{$statusTable}.status"));
     $select->from(['e' => $entityTemporaryTableName], $allColumns)->joinInner(['wp' => $this->_productIndexerHelper->getTable('catalog_product_website')], 'wp.product_id = e.entity_id AND wp.website_id = ' . $websiteId, [])->joinLeft(['dstatus' => $status->getBackend()->getTable()], implode(' AND ', $statusConditions), [])->where($statusExpression . ' = ' . \Magento\Catalog\Model\Product\Attribute\Source\Status::STATUS_ENABLED);
     foreach ($tables as $tableName => $columns) {
         $columnValueNames = [];
         $temporaryTableName = $this->_getTemporaryTableName($tableName);
         $temporaryValueTableName = $temporaryTableName . $valueFieldSuffix;
         $columnsNames = array_keys($columns);
         $select->joinLeft($temporaryTableName, "e.{$linkField} = " . $temporaryTableName . ".{$linkField}", $columnsNames);
         $allColumns = array_merge($allColumns, $columnsNames);
         foreach ($columnsNames as $name) {
             $columnValueName = $name . $valueFieldSuffix;
             if (isset($flatColumns[$columnValueName])) {
                 $columnValueNames[] = $columnValueName;
             }
         }
         if (!empty($columnValueNames)) {
             $select->joinLeft($temporaryValueTableName, "e.{$linkField} = " . $temporaryValueTableName . ".{$linkField}", $columnValueNames);
             $allColumns = array_merge($allColumns, $columnValueNames);
         }
     }
     $sql = $select->insertFromSelect($temporaryFlatTableName, $allColumns, false);
     $this->_connection->query($sql);
 }