PHP AdapterInterface::query方法代码示例

 /**
  * Restore max_heap_table_size value
  *
  * @throws \RuntimeException
  * @return void
  */
 public function restore()
 {
     if (null === $this->currentMaxHeapTableSize) {
         throw new \RuntimeException('max_heap_table_size parameter is not set');
     }
     $this->connection->query('SET SESSION max_heap_table_size = ' . $this->currentMaxHeapTableSize);
 }

 public function runQuery($query)
 {
     $this->addQueryToLog($query);
     $this->connection->query($query);
     $this->connection->resetDdlCache();
     return $this;
 }

 /**
  * Execute full indexation
  *
  * @return void
  */
 public function executeFull()
 {
     $results = [];
     foreach ($this->getTables() as $table => $columns) {
         if (!count($columns)) {
             continue;
         }
         foreach ($columns as $idx => $col) {
             $columns[$idx] = '`' . $col . '`';
         }
         $select = $this->connection->select();
         $fromColumns = new \Zend_Db_Expr('CONCAT(' . implode(",' ',", $columns) . ') as data_index');
         $select->from($table, $fromColumns);
         $result = $this->connection->query($select);
         while ($row = $result->fetch()) {
             $data = $row['data_index'];
             $this->split($data, $results);
         }
     }
     $indexTable = $this->resource->getTableName('mst_misspell_index');
     $this->connection->delete($indexTable);
     $rows = [];
     foreach ($results as $word => $freq) {
         $rows[] = ['keyword' => $word, 'trigram' => $this->text->getTrigram($word), 'frequency' => $freq / count($results)];
         if (count($rows) > 1000) {
             $this->connection->insertArray($indexTable, ['keyword', 'trigram', 'frequency'], $rows);
             $rows = [];
         }
     }
     if (count($rows) > 0) {
         $this->connection->insertArray($indexTable, ['keyword', 'trigram', 'frequency'], $rows);
     }
     $this->connection->delete($this->resource->getTableName('mst_misspell_suggest'));
 }

 /**
  * Remove products from flat that are not exist
  *
  * @param array $ids
  * @param int $storeId
  * @return void
  */
 public function removeDeletedProducts(array &$ids, $storeId)
 {
     $select = $this->connection->select()->from($this->productIndexerHelper->getTable('catalog_product_entity'))->where('entity_id IN(?)', $ids);
     $result = $this->connection->query($select);
     $existentProducts = [];
     foreach ($result->fetchAll() as $product) {
         $existentProducts[] = $product['entity_id'];
     }
     $productsToDelete = array_diff($ids, $existentProducts);
     $ids = $existentProducts;
     $this->deleteProductsFromStore($productsToDelete, $storeId);
 }

 /**
  * @param string|int $eId
  * @param string|int $pId
  * @param string|int $aId
  * @return void
  * @SuppressWarnings(PHPMD.CyclomaticComplexity)
  * @SuppressWarnings(PHPMD.NPathComplexity)
  * @SuppressWarnings(PHPMD.ExcessiveMethodLength)
  * @SuppressWarnings(PHPMD.UnusedLocalVariable)
  * @SuppressWarnings(PHPMD.ExitExpression)
  */
 public function moveNodes($eId, $pId, $aId = 0)
 {
     $eInfo = $this->getNodeInfo($eId);
     if ($pId != 0) {
         $pInfo = $this->getNodeInfo($pId);
     }
     if ($aId != 0) {
         $aInfo = $this->getNodeInfo($aId);
     }
     $level = $eInfo[$this->_level];
     $leftKey = $eInfo[$this->_left];
     $rightKey = $eInfo[$this->_right];
     if ($pId == 0) {
         $levelUp = 0;
     } else {
         $levelUp = $pInfo[$this->_level];
     }
     $rightKeyNear = 0;
     $leftKeyNear = 0;
     if ($pId == 0) {
         //move to root
         $rightKeyNear = $this->_db->fetchOne('SELECT MAX(' . $this->_right . ') FROM ' . $this->_table);
     } elseif ($aId != 0 && $pId == $eInfo[$this->_pid]) {
         // if we have after ID
         $rightKeyNear = $aInfo[$this->_right];
         $leftKeyNear = $aInfo[$this->_left];
     } elseif ($aId == 0 && $pId == $eInfo[$this->_pid]) {
         // if we do not have after ID
         $rightKeyNear = $pInfo[$this->_left];
     } elseif ($pId != $eInfo[$this->_pid]) {
         $rightKeyNear = $pInfo[$this->_right] - 1;
     }
     $skewLevel = $pInfo[$this->_level] - $eInfo[$this->_level] + 1;
     $skewTree = $eInfo[$this->_right] - $eInfo[$this->_left] + 1;
     echo "alert('" . $rightKeyNear . "');";
     if ($rightKeyNear > $rightKey) {
         // up
         echo "alert('move up');";
         $skewEdit = $rightKeyNear - $leftKey + 1;
         $sql = 'UPDATE ' . $this->_table . ' SET ' . $this->_right . ' = IF(' . $this->_left . ' >= ' . $eInfo[$this->_left] . ', ' . $this->_right . ' + ' . $skewEdit . ', IF(' . $this->_right . ' < ' . $eInfo[$this->_left] . ', ' . $this->_right . ' + ' . $skewTree . ', ' . $this->_right . ')), ' . $this->_level . ' = IF(' . $this->_left . ' >= ' . $eInfo[$this->_left] . ', ' . $this->_level . ' + ' . $skewLevel . ', ' . $this->_level . '), ' . $this->_left . ' = IF(' . $this->_left . ' >= ' . $eInfo[$this->_left] . ', ' . $this->_left . ' + ' . $skewEdit . ', IF(' . $this->_left . ' > ' . $rightKeyNear . ', ' . $this->_left . ' + ' . $skewTree . ', ' . $this->_left . '))' . ' WHERE ' . $this->_right . ' > ' . $rightKeyNear . ' AND ' . $this->_left . ' < ' . $eInfo[$this->_right];
     } elseif ($rightKeyNear < $rightKey) {
         // down
         echo "alert('move down');";
         $skewEdit = $rightKeyNear - $leftKey + 1 - $skewTree;
         $sql = 'UPDATE ' . $this->_table . ' SET ' . $this->_left . ' = IF(' . $this->_right . ' <= ' . $rightKey . ', ' . $this->_left . ' + ' . $skewEdit . ', IF(' . $this->_left . ' > ' . $rightKey . ', ' . $this->_left . ' - ' . $skewTree . ', ' . $this->_left . ')), ' . $this->_level . ' = IF(' . $this->_right . ' <= ' . $rightKey . ', ' . $this->_level . ' + ' . $skewLevel . ', ' . $this->_level . '), ' . $this->_right . ' = IF(' . $this->_right . ' <= ' . $rightKey . ', ' . $this->_right . ' + ' . $skewEdit . ', IF(' . $this->_right . ' <= ' . $rightKeyNear . ', ' . $this->_right . ' - ' . $skewTree . ', ' . $this->_right . '))' . ' WHERE ' . $this->_right . ' > ' . $leftKey . ' AND ' . $this->_left . ' <= ' . $rightKeyNear;
     }
     $this->_db->beginTransaction();
     try {
         $this->_db->query($sql);
         $this->_db->commit();
     } catch (\Exception $e) {
         $this->_db->rollBack();
         echo $e->getMessage();
         echo "<br>\r\n";
         echo $sql;
         echo "<br>\r\n";
         exit;
     }
     echo "alert('node added')";
 }

 /**
  * Clean unused relation products
  *
  * @param int $storeId
  * @return \Magento\Catalog\Model\Indexer\Product\Flat\AbstractAction
  */
 protected function _cleanRelationProducts($storeId)
 {
     if (!$this->_productIndexerHelper->isAddChildData()) {
         return $this;
     }
     foreach ($this->_getProductTypeInstances() as $typeInstance) {
         /** @var $typeInstance \Magento\Catalog\Model\Product\Type\AbstractType */
         if (!$typeInstance->isComposite(null)) {
             continue;
         }
         $relation = $typeInstance->getRelationInfo();
         if ($relation && $relation->getTable() && $relation->getParentFieldName() && $relation->getChildFieldName()) {
             $select = $this->_connection->select()->distinct(true)->from($this->_productIndexerHelper->getTable($relation->getTable()), "{$relation->getParentFieldName()}");
             $joinLeftCond = ["e.entity_id = t.{$relation->getParentFieldName()}", "e.child_id = t.{$relation->getChildFieldName()}"];
             if ($relation->getWhere() !== null) {
                 $select->where($relation->getWhere());
                 $joinLeftCond[] = $relation->getWhere();
             }
             $entitySelect = new \Zend_Db_Expr($select->__toString());
             /** @var $select \Magento\Framework\DB\Select */
             $select = $this->_connection->select()->from(['e' => $this->_productIndexerHelper->getFlatTableName($storeId)], null)->joinLeft(['t' => $this->_productIndexerHelper->getTable($relation->getTable())], implode(' AND ', $joinLeftCond), [])->where('e.is_child = ?', 1)->where('e.entity_id IN(?)', $entitySelect)->where("t.{$relation->getChildFieldName()} IS NULL");
             $sql = $select->deleteFromSelect('e');
             $this->_connection->query($sql);
         }
     }
     return $this;
 }

 /**
  * Apply diff. between 0 store and current store to temporary flat table
  *
  * @param array $tables
  * @param array $changedIds
  * @param int|string $storeId
  * @param string $valueFieldSuffix
  * @return void
  */
 protected function _updateTemporaryTableByStoreValues(array $tables, array $changedIds, $storeId, $valueFieldSuffix)
 {
     $flatColumns = $this->_productIndexerHelper->getFlatColumns();
     $temporaryFlatTableName = $this->_getTemporaryTableName($this->_productIndexerHelper->getFlatTableName($storeId));
     $linkField = $this->metadataPool->getMetadata(ProductInterface::class)->getLinkField();
     foreach ($tables as $tableName => $columns) {
         foreach ($columns as $attribute) {
             /* @var $attribute \Magento\Eav\Model\Entity\Attribute */
             $attributeCode = $attribute->getAttributeCode();
             if ($attribute->getBackend()->getType() != 'static') {
                 $joinCondition = sprintf('t.%s = e.%s', $linkField, $linkField) . ' AND t.attribute_id=' . $attribute->getId() . ' AND t.store_id = ' . $storeId . ' AND t.value IS NOT NULL';
                 /** @var $select \Magento\Framework\DB\Select */
                 $select = $this->_connection->select()->joinInner(['t' => $tableName], $joinCondition, [$attributeCode => 't.value']);
                 if (!empty($changedIds)) {
                     $select->where($this->_connection->quoteInto('e.entity_id IN (?)', $changedIds));
                 }
                 $sql = $select->crossUpdateFromSelect(['e' => $temporaryFlatTableName]);
                 $this->_connection->query($sql);
             }
             //Update not simple attributes (eg. dropdown)
             if (isset($flatColumns[$attributeCode . $valueFieldSuffix])) {
                 $select = $this->_connection->select()->joinInner(['t' => $this->_productIndexerHelper->getTable('eav_attribute_option_value')], 't.option_id = e.' . $attributeCode . ' AND t.store_id=' . $storeId, [$attributeCode . $valueFieldSuffix => 't.value']);
                 if (!empty($changedIds)) {
                     $select->where($this->_connection->quoteInto('e.entity_id IN (?)', $changedIds));
                 }
                 $sql = $select->crossUpdateFromSelect(['e' => $temporaryFlatTableName]);
                 $this->_connection->query($sql);
             }
         }
     }
 }

 /**
  * Reindex all products to root category
  *
  * @param \Magento\Store\Model\Store $store
  * @return void
  */
 protected function reindexRootCategory(\Magento\Store\Model\Store $store)
 {
     if ($this->isIndexRootCategoryNeeded()) {
         $selects = $this->prepareSelectsByRange($this->getAllProducts($store), 'entity_id', self::RANGE_PRODUCT_STEP);
         foreach ($selects as $select) {
             $this->connection->query($this->connection->insertFromSelect($select, $this->getMainTmpTable(), ['category_id', 'product_id', 'position', 'is_parent', 'store_id', 'visibility'], \Magento\Framework\DB\Adapter\AdapterInterface::INSERT_ON_DUPLICATE));
         }
     }
 }

 /**
  * Checks database privileges
  *
  * @param \Magento\Framework\DB\Adapter\AdapterInterface $connection
  * @param string $dbName
  * @return bool
  * @throws \Magento\Setup\Exception
  */
 private function checkDatabasePrivileges(\Magento\Framework\DB\Adapter\AdapterInterface $connection, $dbName)
 {
     $grantInfo = $connection->query('SHOW GRANTS FOR current_user()')->fetchAll(\PDO::FETCH_NUM);
     foreach ($grantInfo as $grantRow) {
         if (preg_match('/(ALL|ALL\\sPRIVILEGES)\\sON\\s[^a-zA-Z\\d\\s]?(\\*|' . $dbName . ')/', $grantRow[0]) === 1) {
             return true;
         }
     }
     throw new \Magento\Setup\Exception('Database user does not have enough privileges.');
 }

 /**
  * Copy relations product index from primary index to temporary index table by parent entity
  *
  * @param null|array $parentIds
  * @param array $excludeIds
  * @return \Magento\Catalog\Model\Indexer\Product\Price\AbstractAction
  */
 protected function _copyRelationIndexData($parentIds, $excludeIds = null)
 {
     $select = $this->_connection->select()->from($this->_defaultIndexerResource->getTable('catalog_product_relation'), ['child_id'])->where('parent_id IN(?)', $parentIds);
     if (!empty($excludeIds)) {
         $select->where('child_id NOT IN(?)', $excludeIds);
     }
     $children = $this->_connection->fetchCol($select);
     if ($children) {
         $select = $this->_connection->select()->from($this->_defaultIndexerResource->getTable('catalog_product_index_price'))->where('entity_id IN(?)', $children);
         $query = $select->insertFromSelect($this->_defaultIndexerResource->getIdxTable(), [], false);
         $this->_connection->query($query);
     }
     return $this;
 }

 /**
  * Fill temporary table by data from products EAV attributes by type
  *
  * @param string $tableName
  * @param array  $tableColumns
  * @param array  $changedIds
  * @param string $valueFieldSuffix
  * @param int $storeId
  * @return void
  */
 protected function _fillTemporaryTable($tableName, array $tableColumns, array $changedIds, $valueFieldSuffix, $storeId)
 {
     $metadata = $this->metadataPool->getMetadata(\Magento\Catalog\Api\Data\ProductInterface::class);
     if (!empty($tableColumns)) {
         $columnsChunks = array_chunk($tableColumns, Action\Indexer::ATTRIBUTES_CHUNK_SIZE, true);
         foreach ($columnsChunks as $columnsList) {
             $select = $this->_connection->select();
             $selectValue = $this->_connection->select();
             $entityTableName = $this->_getTemporaryTableName($this->_productIndexerHelper->getTable('catalog_product_entity'));
             $temporaryTableName = $this->_getTemporaryTableName($tableName);
             $temporaryValueTableName = $temporaryTableName . $valueFieldSuffix;
             $keyColumn = ['entity_id'];
             $columns = array_merge($keyColumn, array_keys($columnsList));
             $valueColumns = $keyColumn;
             $flatColumns = $this->_productIndexerHelper->getFlatColumns();
             $iterationNum = 1;
             $select->from(['et' => $entityTableName], $keyColumn)->join(['e' => $this->resource->getTableName('catalog_product_entity')], 'e.entity_id = et.entity_id', []);
             $selectValue->from(['e' => $temporaryTableName], $keyColumn);
             /** @var $attribute \Magento\Catalog\Model\ResourceModel\Eav\Attribute */
             foreach ($columnsList as $columnName => $attribute) {
                 $countTableName = 't' . $iterationNum++;
                 $joinCondition = sprintf('e.%3$s = %1$s.%3$s AND %1$s.attribute_id = %2$d AND %1$s.store_id = 0', $countTableName, $attribute->getId(), $metadata->getLinkField());
                 $select->joinLeft([$countTableName => $tableName], $joinCondition, [$columnName => 'value']);
                 if ($attribute->getFlatUpdateSelect($storeId) instanceof \Magento\Framework\DB\Select) {
                     $attributeCode = $attribute->getAttributeCode();
                     $columnValueName = $attributeCode . $valueFieldSuffix;
                     if (isset($flatColumns[$columnValueName])) {
                         $valueJoinCondition = sprintf('e.%1$s = %2$s.option_id AND %2$s.store_id = 0', $attributeCode, $countTableName);
                         $selectValue->joinLeft([$countTableName => $this->_productIndexerHelper->getTable('eav_attribute_option_value')], $valueJoinCondition, [$columnValueName => $countTableName . '.value']);
                         $valueColumns[] = $columnValueName;
                     }
                 }
             }
             if (!empty($changedIds)) {
                 $select->where($this->_connection->quoteInto('e.entity_id IN (?)', $changedIds));
             }
             $sql = $select->insertFromSelect($temporaryTableName, $columns, true);
             $this->_connection->query($sql);
             if (count($valueColumns) > 1) {
                 if (!empty($changedIds)) {
                     $selectValue->where($this->_connection->quoteInto('e.entity_id IN (?)', $changedIds));
                 }
                 $sql = $selectValue->insertFromSelect($temporaryValueTableName, $valueColumns, true);
                 $this->_connection->query($sql);
             }
         }
     }
 }

 /**
  * Fetch Zend statement instance
  *
  * @param \Zend_Db_Statement_Interface|\Magento\Framework\DB\Select|string $query
  * @param AdapterInterface $connection
  * @return \Zend_Db_Statement_Interface
  * @throws LocalizedException
  */
 protected function _getStatement($query, AdapterInterface $connection = null)
 {
     if ($query instanceof \Zend_Db_Statement_Interface) {
         return $query;
     }
     if ($query instanceof \Zend_Db_Select) {
         return $query->query();
     }
     if (is_string($query)) {
         if (!$connection instanceof AdapterInterface) {
             throw new LocalizedException(new Phrase('Invalid connection'));
         }
         return $connection->query($query);
     }
     throw new LocalizedException(new Phrase('Invalid query'));
 }

 /**
  * @param array $index
  * @return $this
  */
 protected function addBundledOptions(&$index)
 {
     if (!$this->getIndex()->hasProperty('include_bundled')) {
         return $this;
     }
     $productIds = array_keys($index);
     $this->connection->query('SET SESSION group_concat_max_len = 1000000;');
     $select = $this->connection->select()->from(['main_table' => $this->resource->getTableName('catalog_product_entity')], ['sku' => new \Zend_Db_Expr("GROUP_CONCAT(main_table.`sku` SEPARATOR ' ')")])->joinLeft(['cpr' => $this->resource->getTableName('catalog_product_relation')], 'main_table.entity_id = cpr.child_id', ['parent_id'])->where('cpr.parent_id IN (?)', $productIds)->group('cpr.parent_id');
     foreach ($this->connection->fetchAll($select) as $row) {
         if (!isset($index[$row['parent_id']]['options'])) {
             $index[$row['parent_id']]['options'] = '';
         }
         $index[$row['parent_id']]['options'] .= ' ' . $row['sku'];
     }
     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;
 }

 /**
  * Load product(s) attributes
  *
  * @param int $storeId
  * @param array $productIds
  * @param array $attributeTypes
  * @return array
  */
 public function getProductAttributes($storeId, array $productIds, array $attributeTypes)
 {
     $result = [];
     $selects = [];
     $ifStoreValue = $this->connection->getCheckSql('t_store.value_id > 0', 't_store.value', 't_default.value');
     foreach ($attributeTypes as $backendType => $attributeIds) {
         if ($attributeIds) {
             $tableName = $this->getTable('catalog_product_entity_' . $backendType);
             $selects[] = $this->connection->select()->from(['t_default' => $tableName], ['entity_id', 'attribute_id'])->joinLeft(['t_store' => $tableName], $this->connection->quoteInto('t_default.entity_id=t_store.entity_id' . ' AND t_default.attribute_id=t_store.attribute_id' . ' AND t_store.store_id = ?', $storeId), ['value' => $this->unifyField($ifStoreValue, $backendType)])->where('t_default.store_id = ?', 0)->where('t_default.attribute_id IN (?)', $attributeIds)->where('t_default.entity_id IN (?)', $productIds);
         }
     }
     if ($selects) {
         $select = $this->connection->select()->union($selects, \Magento\Framework\DB\Select::SQL_UNION_ALL);
         $query = $this->connection->query($select);
         while ($row = $query->fetch()) {
             $result[$row['entity_id']][$row['attribute_id']] = $row['value'];
         }
     }
     return $result;
 }