/** * 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; }
/** * @param int $websiteId * @param Product|null $product * @return \Zend_Db_Statement_Interface * @throws \Magento\Framework\Exception\LocalizedException */ protected function getRuleProductsStmt($websiteId, Product $product = null) { /** * Sort order is important * It used for check stop price rule condition. * website_id customer_group_id product_id sort_order * 1 1 1 0 * 1 1 1 1 * 1 1 1 2 * if row with sort order 1 will have stop flag we should exclude * all next rows for same product id from price calculation */ $select = $this->connection->select()->from(['rp' => $this->getTable('catalogrule_product')])->order(['rp.website_id', 'rp.customer_group_id', 'rp.product_id', 'rp.sort_order', 'rp.rule_id']); if ($product && $product->getEntityId()) { $select->where('rp.product_id=?', $product->getEntityId()); } /** * Join default price and websites prices to result */ $priceAttr = $this->eavConfig->getAttribute(Product::ENTITY, 'price'); $priceTable = $priceAttr->getBackend()->getTable(); $attributeId = $priceAttr->getId(); $linkField = $this->metadataPool->getMetadata(ProductInterface::class)->getLinkField(); $select->join(['e' => $this->getTable('catalog_product_entity')], sprintf('e.entity_id = rp.product_id'), []); $joinCondition = '%1$s.' . $linkField . '=e.' . $linkField . ' AND (%1$s.attribute_id=' . $attributeId . ') and %1$s.store_id=%2$s'; $select->join(['pp_default' => $priceTable], sprintf($joinCondition, 'pp_default', \Magento\Store\Model\Store::DEFAULT_STORE_ID), []); $website = $this->storeManager->getWebsite($websiteId); $defaultGroup = $website->getDefaultGroup(); if ($defaultGroup instanceof \Magento\Store\Model\Group) { $storeId = $defaultGroup->getDefaultStoreId(); } else { $storeId = \Magento\Store\Model\Store::DEFAULT_STORE_ID; } $select->joinInner(['product_website' => $this->getTable('catalog_product_website')], 'product_website.product_id=rp.product_id ' . 'AND product_website.website_id = rp.website_id ' . 'AND product_website.website_id=' . $websiteId, []); $tableAlias = 'pp' . $websiteId; $select->joinLeft([$tableAlias => $priceTable], sprintf($joinCondition, $tableAlias, $storeId), []); $select->columns(['default_price' => $this->connection->getIfNullSql($tableAlias . '.value', 'pp_default.value')]); return $this->connection->query($select); }
/** * Write single product into flat product table * * @param int $storeId * @param int $productId * @param string $valueFieldSuffix * @return \Magento\Catalog\Model\Indexer\Product\Flat */ public function write($storeId, $productId, $valueFieldSuffix = '') { $flatTable = $this->_productIndexerHelper->getFlatTableName($storeId); $attributes = $this->_productIndexerHelper->getAttributes(); $eavAttributes = $this->_productIndexerHelper->getTablesStructure($attributes); $updateData = array(); $describe = $this->_connection->describeTable($flatTable); foreach ($eavAttributes as $tableName => $tableColumns) { $columnsChunks = array_chunk($tableColumns, self::ATTRIBUTES_CHUNK_SIZE, true); foreach ($columnsChunks as $columns) { $select = $this->_connection->select(); $selectValue = $this->_connection->select(); $keyColumns = array('entity_id' => 'e.entity_id', 'attribute_id' => 't.attribute_id', 'value' => $this->_connection->getIfNullSql('`t2`.`value`', '`t`.`value`')); if ($tableName != $this->_productIndexerHelper->getTable('catalog_product_entity')) { $valueColumns = array(); $ids = array(); $select->from(array('e' => $this->_productIndexerHelper->getTable('catalog_product_entity')), $keyColumns); $selectValue->from(array('e' => $this->_productIndexerHelper->getTable('catalog_product_entity')), $keyColumns); /** @var $attribute \Magento\Catalog\Model\Resource\Eav\Attribute */ foreach ($columns as $columnName => $attribute) { if (isset($describe[$columnName])) { $ids[$attribute->getId()] = $columnName; } } $select->joinLeft(array('t' => $tableName), 'e.entity_id = t.entity_id ' . $this->_connection->quoteInto(' AND t.attribute_id IN (?)', array_keys($ids)) . ' AND t.store_id = 0', array())->joinLeft(array('t2' => $tableName), 't.entity_id = t2.entity_id ' . ' AND t.attribute_id = t2.attribute_id ' . $this->_connection->quoteInto(' AND t2.store_id = ?', $storeId), array())->where('e.entity_id = ' . $productId)->where('t.attribute_id IS NOT NULL'); $cursor = $this->_connection->query($select); while ($row = $cursor->fetch(\Zend_Db::FETCH_ASSOC)) { $updateData[$ids[$row['attribute_id']]] = $row['value']; $valueColumnName = $ids[$row['attribute_id']] . $valueFieldSuffix; if (isset($describe[$valueColumnName])) { $valueColumns[$row['value']] = $valueColumnName; } } //Update not simple attributes (eg. dropdown) if (!empty($valueColumns)) { $valueIds = array_keys($valueColumns); $select = $this->_connection->select()->from(array('t' => $this->_productIndexerHelper->getTable('eav_attribute_option_value')), array('t.option_id', 't.value'))->where($this->_connection->quoteInto('t.option_id IN (?)', $valueIds)); $cursor = $this->_connection->query($select); while ($row = $cursor->fetch(\Zend_Db::FETCH_ASSOC)) { $valueColumnName = $valueColumns[$row['option_id']]; if (isset($describe[$valueColumnName])) { $updateData[$valueColumnName] = $row['value']; } } } } else { $columnNames = array_keys($columns); $columnNames[] = 'attribute_set_id'; $columnNames[] = 'type_id'; $select->from(array('e' => $this->_productIndexerHelper->getTable('catalog_product_entity')), $columnNames)->where('e.entity_id = ' . $productId); $cursor = $this->_connection->query($select); $row = $cursor->fetch(\Zend_Db::FETCH_ASSOC); if (!empty($row)) { foreach ($row as $columnName => $value) { $updateData[$columnName] = $value; } } } } } if (!empty($updateData)) { $updateData += array('entity_id' => $productId); $updateFields = array(); foreach ($updateData as $key => $value) { $updateFields[$key] = $key; } $this->_connection->insertOnDuplicate($flatTable, $updateData, $updateFields); } return $this; }
/** * Delete old relations * * @param string $tableName * * @return void */ protected function _deleteOldRelations($tableName) { $select = $this->_connection->select()->from(['s' => $tableName])->joinLeft(['w' => $this->_getTable('catalog_product_website')], 's.product_id = w.product_id AND s.website_id = w.website_id', [])->where('w.product_id IS NULL'); $sql = $select->deleteFromSelect('s'); $this->_connection->query($sql); }
/** * {@inheritdoc} */ public function deleteByFilter(Filter $filter) { $this->connection->query($this->prepareSelect($filter)->deleteFromSelect(self::TABLE_NAME)); }
/** * 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) { $requiredPrivileges = [ 'SELECT', 'INSERT', 'UPDATE', 'DELETE', 'CREATE', 'DROP', 'REFERENCES', 'INDEX', 'ALTER', 'CREATE TEMPORARY TABLES', 'LOCK TABLES', 'EXECUTE', 'CREATE VIEW', 'SHOW VIEW', 'CREATE ROUTINE', 'ALTER ROUTINE', 'EVENT', 'TRIGGER' ]; // check global privileges $userPrivilegesQuery = "SELECT PRIVILEGE_TYPE FROM USER_PRIVILEGES " . "WHERE REPLACE(GRANTEE, '\'', '') = current_user()"; $grantInfo = $connection->query($userPrivilegesQuery)->fetchAll(\PDO::FETCH_NUM); if (empty(array_diff($requiredPrivileges, $this->parseGrantInfo($grantInfo)))) { return true; } // check table privileges $schemaPrivilegesQuery = "SELECT PRIVILEGE_TYPE FROM SCHEMA_PRIVILEGES " . "WHERE '$dbName' LIKE TABLE_SCHEMA AND REPLACE(GRANTEE, '\'', '') = current_user()"; $grantInfo = $connection->query($schemaPrivilegesQuery)->fetchAll(\PDO::FETCH_NUM); if (empty(array_diff($requiredPrivileges, $this->parseGrantInfo($grantInfo)))) { return true; } $errorMessage = 'Database user does not have enough privileges. Please make sure ' . implode(', ', $requiredPrivileges) . " privileges are granted to table '$dbName'."; throw new \Magento\Setup\Exception($errorMessage); }
/** * Run sql code * * @param string $command * @return $this */ public function runCommand($command) { $this->connection->query($command); return $this; }
/** * {@inheritdoc} */ public function deleteByData(array $data) { $this->connection->query($this->prepareSelect($data)->deleteFromSelect($this->resource->getTableName(self::TABLE_NAME))); }