/** * Reindex all products to root category * * @param Mage_Core_Model_Store $store */ protected function _reindexRootCategory(Mage_Core_Model_Store $store) { $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(), array('category_id', 'product_id', 'position', 'is_parent', 'store_id', 'visibility'), Varien_Db_Adapter_Interface::INSERT_IGNORE)); } }
/** * Prepare data for group website relation */ protected function _prepareGroupWebsite($timestamp) { $this->_connection->delete($this->_resource->getTable('catalogrule/rule_group_website'), array()); $select = $this->_connection->select()->distinct(true)->from($this->_resource->getTable('catalogrule/rule_product'), array('rule_id', 'customer_group_id', 'website_id'))->where(new Zend_Db_Expr("{$timestamp} >= from_time"))->where($this->_connection->getCheckSql(new Zend_Db_Expr('to_time = 0'), new Zend_Db_Expr(1), new Zend_Db_Expr("{$timestamp} <= to_time"))); $query = $select->insertFromSelect($this->_resource->getTable('catalogrule/rule_group_website')); $this->_connection->query($query); }
/** * Copy data from temporary to main redirects table * * @return void */ protected function _copyRedirectsToMainTable() { $select = $this->_connection->select(); $select->from(array('t' => self::TMP_TABLE_NAME), array('*')); $query = $select->insertFromSelect($this->_resource->getTableName('enterprise_urlrewrite/redirect'), array('identifier', 'target_path', 'store_id', 'category_id', 'product_id'), Varien_Db_Adapter_Interface::INSERT_ON_DUPLICATE); $this->_connection->query($query); }
/** * Get tables in DB * * @return string[] */ protected function getTables() { $stmt = $this->dbRead->query('SHOW TABLES'); $stmt->execute(); $tables = array(); return $stmt->fetchAll(\PDO::FETCH_COLUMN); }
/** * Get current DB version * * @return int */ protected function _getCurrentVersionId() { if (empty($this->_currentVersionId)) { // zend select query permanently requires FROM statement, so executing raw query $this->_currentVersionId = $this->_connection->query($this->_connection->select()->from($this->_metadata->getChangelogName(), array())->columns(array('max' => 'MAX(version_id)')))->fetchColumn(); } return $this->_currentVersionId; }
/** * Add products to changelog by conditions * * @param int $storeId * @param string $attrCode * @param Zend_Db_Expr $attrConditionValue */ protected function _refreshSpecialPriceByStore($storeId, $attrCode, $attrConditionValue) { $attribute = Mage::getSingleton('eav/config')->getAttribute(Mage_Catalog_Model_Product::ENTITY, $attrCode); $attributeId = $attribute->getAttributeId(); $select = $this->_connection->select()->from($this->_getTable(array('catalog/product', 'datetime')), array('entity_id'))->where('attribute_id = ?', $attributeId)->where('store_id = ?', $storeId)->where('value = ?', $attrConditionValue); $client = $this->_getClient(Mage::helper('enterprise_index')->getIndexerConfigValue('catalog_product_price', 'index_table')); $query = $select->insertFromSelect($client->getMetadata()->changelog_name, array('entity_id'), false); $this->_connection->query($query); }
/** * Removes redirect rows which have no corresponding records in redirect table from index. * * @return Enterprise_UrlRewrite_Model_Index_Action_Url_Rewrite_Redirect_Refresh_Orphan * @throws Enterprise_Index_Model_Action_Exception */ public function execute() { try { $select = $this->_connection->select()->from(array('ur' => $this->_getTable('enterprise_urlrewrite/url_rewrite')), '*')->joinInner(array('rr' => $this->_getTable('enterprise_urlrewrite/redirect_rewrite')), 'ur.url_rewrite_id = rr.url_rewrite_id')->joinLeft(array('redirect' => $this->_getTable('enterprise_urlrewrite/redirect')), 'redirect.redirect_id = rr.redirect_id')->where('ur.entity_type = ?', Enterprise_UrlRewrite_Model_Redirect::URL_REWRITE_ENTITY_TYPE)->where('redirect.redirect_id IS NULL'); $this->_connection->query($this->_connection->deleteFromSelect($select, 'ur')); } catch (Exception $e) { $this->_metadata->setInvalidStatus()->save(); throw new Enterprise_Index_Model_Action_Exception($e->getMessage(), $e->getCode(), $e); } return $this; }
/** * Refresh materialized view * * @return Enterprise_Mview_Model_Action_Mview_Refresh * @throws Exception */ public function execute() { try { $insert = $this->_connection->insertFromSelect($this->_connection->select()->from($this->_metadata->getViewName()), $this->_metadata->getTableName()); $this->_connection->delete($this->_metadata->getTableName()); $this->_connection->query($insert); $this->_metadata->setValidStatus()->setVersionId($this->_selectLastVersionId())->save(); } catch (Exception $e) { $this->_metadata->setInvalidStatus()->save(); throw $e; } return $this; }
/** * Method deletes old row in the mview table and insert new one from view. * * @return Enterprise_Mview_Model_Action_Mview_Create * @throws Enterprise_Mview_Exception */ public function execute() { $this->_validate(); $this->_connection->beginTransaction(); try { $this->_connection->delete($this->_metadata->getTableName(), array($this->_metadata->getKeyColumn() . '=?' => $this->_keyColumnIdValue)); $this->_connection->query($this->_getInsertSql()); $this->_connection->commit(); } catch (Exception $e) { $this->_connection->rollBack(); throw new Enterprise_Mview_Exception($e->getMessage(), $e->getCode()); } return $this; }
/** * Refresh rows by ids from changelog * * @return Enterprise_Mview_Model_Action_Mview_Refresh_Changelog * @throws Enterprise_Mview_Exception */ public function execute() { $this->_validate(); $this->_connection->beginTransaction(); try { $this->_connection->delete($this->_metadata->getTableName(), array($this->_metadata->getKeyColumn() . ' IN ( ' . $this->_selectChangedIds() . ' )')); $this->_connection->query($this->_connection->insertFromSelect($this->_selectChangedRows(), $this->_metadata->getTableName())); $this->_metadata->setVersionId($this->_selectLastVersionId()); $this->_metadata->save(); $this->_connection->commit(); } catch (Exception $e) { $this->_connection->rollBack(); $this->_metadata->setOutOfDateStatus()->save(); throw new Enterprise_Mview_Exception($e->getMessage(), $e->getCode()); } return $this; }
/** * Prepare and create triggers for target_table table */ protected function _createTriggers() { $sqlTrigger = $this->_factory->getMagentoDbSqlTrigger(); foreach ($sqlTrigger->getEventTypes() as $event) { $body = $this->_prepareBody($event); // Set trigger's data $sqlTrigger->reset(); $sqlTrigger->setTarget($this->_targetTable); $sqlTrigger->setEvent($event); $objTrigger = $this->_factory->getMagentoDbObjectTrigger($this->_connection, $sqlTrigger->getName()); // Drop trigger before insert with updated body if ($objTrigger->isExists()) { $objTrigger->drop(); } // Create trigger only if trigger's body is not empty if (!empty($body)) { $sqlTrigger->setBody($body); $this->_connection->query((string) $sqlTrigger); } } }
/** * Drop database object * * @return Magento_Db_Object */ public function drop() { $query = 'DROP ' . $this->getDbType() . ' IF EXISTS ' . $this->_adapter->quoteIdentifier($this->_objectName); $this->_adapter->query($query); return $this; }
/** * Migrate Entries of non-select/multiselect Entities to new Entity Table * * @param string $targetType Target Type as String * @param Zend_Db_Statement_Interface $sourceQuery Source Entity Table Query with old Entity Data to Transfer * @param string $sourceType Source Type as String * @param string $targetTable Target Entity Table * @param Varien_Db_Adapter_Interface $_dbConnection Database Connection * @param string $sourceTable Source Entity Table * @return void */ protected function _migrateNonSelect($targetType, $sourceQuery, $sourceType, $targetTable, $_dbConnection, $sourceTable) { while ($row = $sourceQuery->fetch()) { $currentValue = $row['value']; if (!is_null($currentValue)) { // Cast Value Type to new Type (e.g. decimal to text) $targetValue = $this->_typeCast($currentValue, $sourceType, $targetType); // Insert Value to target Entity $sql = 'INSERT' . ' INTO ' . $targetTable . ' (entity_type_id, attribute_id, store_id, entity_id, value) VALUES (?,?,?,?,?)'; try { $_dbConnection->query($sql, [$row['entity_type_id'], $row['attribute_id'], $row['store_id'], $row['entity_id'], $targetValue]); } catch (Exception $e) { $this->_getHelper()->log(sprintf('Exception occured while migrating Data. See exception log.'), $e); } } // Delete Value from source Entity $sql = 'DELETE' . ' FROM ' . $sourceTable . ' WHERE value_id = ?'; $_dbConnection->query($sql, $row['value_id']); } }