PHP Varien_Db_Adapter_Interface::query方法代码示例

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