/**
* 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)));
}
