/** * Gets attribute options from database * * @param \Mage_Eav_Model_Entity_Attribute $attribute * * @return array */ protected function getOptions($attribute) { $select = $this->readConnection->select()->from(array('o' => \Mage::getSingleton('core/resource')->getTableName('eav_attribute_option')))->join(array('ov' => \Mage::getSingleton('core/resource')->getTableName('eav_attribute_option_value')), 'o.option_id = ov.option_id')->where('o.attribute_id = ?', $attribute->getId())->where('ov.store_id = 0')->order('ov.option_id'); $query = $select->query(); $values = array(); foreach ($query->fetchAll() as $row) { $values[] = $row['value']; } return array('values' => $values); }
/** * Convert operator for sql where * * @param string $field * @param string $operator * @param string|array $value * @return string */ public function getOperatorCondition($field, $operator, $value) { switch ($operator) { case '!=': case '>=': case '<=': case '>': case '<': $selectOperator = sprintf('%s?', $operator); break; case '{}': case '!{}': if (preg_match('/^.*(category_id)$/', $field) && is_array($value)) { $selectOperator = ' IN (?)'; } else { $selectOperator = ' LIKE ?'; } if (substr($operator, 0, 1) == '!') { $selectOperator = ' NOT' . $selectOperator; } break; case '[]': case '![]': case '()': case '!()': $selectOperator = 'FIND_IN_SET(?,' . $this->_adapter->quoteIdentifier($field) . ')'; if (substr($operator, 0, 1) == '!') { $selectOperator = 'NOT ' . $selectOperator; } break; default: $selectOperator = '=?'; break; } $field = $this->_adapter->quoteIdentifier($field); if (is_array($value) && in_array($operator, array('==', '!=', '>=', '<=', '>', '<', '{}', '!{}'))) { $results = array(); foreach ($value as $v) { $results[] = $this->_adapter->quoteInto("{$field}{$selectOperator}", $v); } $result = implode(' AND ', $results); } elseif (in_array($operator, array('()', '!()', '[]', '![]'))) { if (!is_array($value)) { $value = array($value); } $results = array(); foreach ($value as $v) { $results[] = $this->_adapter->quoteInto("{$selectOperator}", $v); } $result = implode(in_array($operator, array('()', '!()')) ? ' OR ' : ' AND ', $results); } else { $result = $this->_adapter->quoteInto("{$field}{$selectOperator}", $value); } return $result; }
/** * @param $data */ protected function _processTemplates(&$data) { $config = $this->_adapter->getConfig(); $select = $this->_adapter->select(); $select->from('information_schema.tables', 'AUTO_INCREMENT')->where('table_schema = ?', $config['dbname'])->where('table_name = ?', $this->_adapter->getTableName('customer_entity')); $nextId = $this->_adapter->fetchOne($select); foreach ($data['account'] as &$field) { $field = str_replace('{id}', $nextId, $field); } foreach ($data['address'] as &$address) { foreach ($address as &$field) { $field = str_replace('{id}', $nextId, $field); } } }
/** * Loop through resource groups and truncate tables * * @return void */ protected function truncateResourceGroups() { foreach ($this->groups as $name => $resources) { $this->prepareResources($resources); if (!count($resources)) { continue; } array_walk($resources, array($this, 'prepareArrayForTableOutput')); if (!$this->force) { $this->output->writeln(sprintf('<info>%s</info> table(s):', $name)); $table = new Table($this->output); $table->setRows($resources)->render(); $confirm = $this->dialog->askConfirmation($this->output, '<question>Truncate table(s)?</question> ', false); if (!$confirm) { continue; } } foreach ($resources as $resourceName) { if (!$resourceName[0]) { continue; } $this->dbWrite->truncateTable($resourceName[0]); $this->output->writeln(sprintf('Truncate <info>%s</info>', $resourceName[0])); } } }
/** * Retrieve attribute codes using for flat * * @return array */ public function getAttributeCodes() { if ($this->_attributeCodes === null) { $this->_attributeCodes = array(); $systemAttributes = array(); $attributeNodes = Mage::getConfig()->getNode(self::XML_NODE_ATTRIBUTE_NODES)->children(); foreach ($attributeNodes as $node) { $attributes = Mage::getConfig()->getNode((string) $node)->asArray(); $attributes = array_keys($attributes); $systemAttributes = array_unique(array_merge($attributes, $systemAttributes)); } $bind = array('backend_type' => Mage_Eav_Model_Entity_Attribute_Abstract::TYPE_STATIC, 'entity_type_id' => $this->getEntityTypeId()); $select = $this->_connection->select()->from(array('main_table' => $this->getTable('eav/attribute')))->join(array('additional_table' => $this->getTable('catalog/eav_attribute')), 'additional_table.attribute_id = main_table.attribute_id')->where('main_table.entity_type_id = :entity_type_id'); $whereCondition = array('main_table.backend_type = :backend_type', $this->_connection->quoteInto('additional_table.is_used_for_promo_rules = ?', 1), $this->_connection->quoteInto('additional_table.used_in_product_listing = ?', 1), $this->_connection->quoteInto('additional_table.used_for_sort_by = ?', 1), $this->_connection->quoteInto('main_table.attribute_code IN(?)', $systemAttributes)); if ($this->getFlatHelper()->isAddFilterableAttributes()) { $whereCondition[] = $this->_connection->quoteInto('additional_table.is_filterable > ?', 0); } $select->where(implode(' OR ', $whereCondition)); $attributesData = $this->_connection->fetchAll($select, $bind); Mage::getSingleton('eav/config')->importAttributesData($this->getEntityType(), $attributesData); foreach ($attributesData as $data) { $this->_attributeCodes[$data['attribute_id']] = $data['attribute_code']; } unset($attributesData); } return $this->_attributeCodes; }
/** * @dataProvider insertDataProvider */ public function testInsertForce($data) { $this->assertEquals(1, $this->_connection->insertForce($this->_tableName, $data)); $select = $this->_connection->select()->from($this->_tableName); $result = $this->_connection->fetchRow($select); $this->assertEquals($data, $result); }
/** * Assert that session data reads from DB correctly regardless of encoding * * @param string $sessionData * * @dataProvider readEncodedDataProvider */ public function testReadEncoded($sessionData) { $sessionRecord = array(self::COLUMN_SESSION_ID => self::SESSION_ID, self::COLUMN_SESSION_DATA => $sessionData); $this->_connection->insertOnDuplicate($this->_sessionTable, $sessionRecord, array(self::COLUMN_SESSION_DATA)); $sessionData = $this->_model->read(self::SESSION_ID); $this->assertEquals($this->_sourceData[self::SESSION_NEW], unserialize($sessionData)); }
/** * Wrap trigger body with conditions * * @param string $body * @param array $conditions * @param string $conditionsLogic * @return string */ protected function _wrapBodyWithCond($body, $conditions = array(), $conditionsLogic = Zend_Db_Select::SQL_OR) { if (empty($conditions)) { return $body; } return $this->_connection->getCaseSql($this->getEventConditionsSql($conditions, $conditionsLogic), array('TRUE' => new Zend_Db_Expr('BEGIN ' . $body . ' END;')), new Zend_Db_Expr('BEGIN END;')) . " CASE;"; }
/** * 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 affected product */ protected function _prepareAffectedProduct() { /** @var $modelCondition Mage_Catalog_Model_Product_Condition */ $modelCondition = $this->_factory->getModel('catalog/product_condition'); $productCondition = $modelCondition->setTable($this->_resource->getTable('catalogrule/affected_product'))->setPkFieldName('product_id'); $this->_app->dispatchEvent('catalogrule_after_apply', array('product' => $this->_getProduct(), 'product_condition' => $productCondition)); $this->_connection->delete($this->_resource->getTable('catalogrule/affected_product')); }
/** * Returns column definition for column provided in key_column parameter. * * @return array * @throws Enterprise_Mview_Exception */ protected function _getKeyColumnConfig() { $describe = $this->_table->describe(); if (!array_key_exists($this->_metadata->getKeyColumn(), $describe)) { throw new Enterprise_Mview_Exception(sprintf("Column '%s' does not exist in the '%s' table", $this->_metadata->getKeyColumn(), $this->_table->getObjectName())); } return $this->_connection->getColumnCreateByDescribe($describe[$this->_metadata->getKeyColumn()]); }
/** * 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; }
/** * Returns last version_id from changelog table * * @return int */ protected function _selectLastVersionId() { $changelogName = $this->_metadata->getChangelogName(); if (empty($changelogName)) { return 0; } $select = $this->_connection->select()->from(array('changelog' => $changelogName), array('version_id'))->order('version_id DESC')->limit(1); return (int) $this->_connection->fetchOne($select); }
/** * 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); }
/** * Get case sql for define category seo suffix by store * * @param string $field * * @return Zend_Db_Expr */ protected function _getCategorySeoSuffixCaseSql($field) { /* @var $store Mage_Core_Model_Store */ foreach ($this->_app->getStores() as $store) { $seoSuffix = $this->_categoryHelper->getCategoryUrlSuffix($store->getId()); $casesResults[$store->getId()] = !empty($seoSuffix) ? '".' . $seoSuffix . '"' : '""'; } return $this->_connection->getCaseSql($field, $casesResults); }
/** * @param Mage_Customer_Model_Customer $customer */ protected function _moveAttributesData($customer) { $attributes = array('po_limit' => $this->_poLimitId, 'po_credit' => $this->_poCreditId); foreach ($attributes as $attributeCode => $attributeId) { try { $select = $this->_db->select()->from($customer->getResource()->getTable('customer_entity_int'), array('value'))->where('attribute_id = ?', $attributeId)->where('entity_id = ?', $customer->getId()); $value = $this->_db->fetchOne($select); if ((int) $value > 0) { $this->_db->insert($customer->getResource()->getTable('customer_entity_decimal'), array('entity_type_id' => $customer->getEntityTypeId(), 'attribute_id' => $attributeId, 'entity_id' => $customer->getId(), 'value' => (int) $value)); } } catch (Exception $e) { } try { $this->_db->delete($customer->getResource()->getTable('customer_entity_int'), 'entity_type_id = ' . $customer->getEntityTypeId() . ' AND attribute_id = ' . $attributeId . ' AND ' . 'entity_id = ' . $customer->getId()); } catch (Exception $e) { } } }
/** * Garbage collection * * @param int $sessMaxLifeTime ignored * @return boolean */ public function gc($sessMaxLifeTime) { if ($this->_automaticCleaningFactor > 0) { if ($this->_automaticCleaningFactor == 1 || rand(1, $this->_automaticCleaningFactor) == 1) { $where = array('session_expires < ?' => Varien_Date::toTimestamp(true)); $this->_write->delete($this->_sessionTable, $where); } } return true; }
/** * 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; }
/** * Return last version ID * * @return string */ protected function _getLastVersionId() { $changelogName = $this->_metadata->getChangelogName(); if (empty($changelogName)) { return 0; } if (!$this->_lastVersionId) { $select = $this->_connection->select()->from($changelogName, array('version_id'))->order('version_id DESC')->limit(1); $this->_lastVersionId = (int) $this->_connection->fetchOne($select); } return $this->_lastVersionId; }
/** * Generate and return trigger body's row * * @param string $event * @param Varien_Object $subscriber * @return string */ protected function _getInsertRow($event, Varien_Object $subscriber) { switch ($event) { case Magento_Db_Sql_Trigger::SQL_EVENT_INSERT: case Magento_Db_Sql_Trigger::SQL_EVENT_UPDATE: return sprintf("INSERT IGNORE INTO %s (%s) VALUES (NEW.%s);\n", $this->_connection->quoteIdentifier($subscriber->getChangelogName()), $this->_connection->quoteIdentifier($subscriber->getKeyColumn()), $this->_connection->quoteIdentifier($subscriber->getTargetColumn())); case Magento_Db_Sql_Trigger::SQL_EVENT_DELETE: return sprintf("INSERT IGNORE INTO %s (%s) VALUES (OLD.%s);\n", $this->_connection->quoteIdentifier($subscriber->getChangelogName()), $this->_connection->quoteIdentifier($subscriber->getKeyColumn()), $this->_connection->quoteIdentifier($subscriber->getTargetColumn())); default: return ''; } }
/** * 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; }
/** * Generate unique url key if current url key already occupied * * @param Mage_Catalog_Model_Abstract $object * @return Mage_Catalog_Model_Abstract */ protected function _generateNextUrlKeySuffix(Mage_Catalog_Model_Abstract $object) { $prefixValue = $object->getData($this->getAttribute()->getAttributeCode()); $requestPathField = new Zend_Db_Expr($this->_connection->quoteIdentifier('value')); //select increment part of request path and cast expression to integer $urlIncrementPartExpression = $this->_eavHelper->getCastToIntExpression($this->_connection->getSubstringSql($requestPathField, strlen($prefixValue) + 1, $this->_connection->getLengthSql($requestPathField) . ' - ' . strlen($prefixValue))); $prefixRegexp = preg_quote($prefixValue); $orCondition = $this->_connection->select()->orWhere($this->_connection->prepareSqlCondition('value', array('regexp' => '^' . $prefixRegexp . '$')))->orWhere($this->_connection->prepareSqlCondition('value', array('regexp' => '^' . $prefixRegexp . '-[0-9]*$')))->getPart(Zend_Db_Select::WHERE); $select = $this->_connection->select(); $select->from($this->getAttribute()->getBackendTable(), new Zend_Db_Expr('MAX(ABS(' . $urlIncrementPartExpression . '))'))->where('value LIKE :url_key')->where('entity_id <> :entity_id')->where(implode('', $orCondition)); $bind = array('url_key' => $prefixValue . '%', 'entity_id' => (int) $object->getId()); $suffix = $this->_connection->fetchOne($select, $bind); if (!is_null($suffix)) { $suffix = (int) $suffix; $object->setData($this->getAttribute()->getAttributeCode(), sprintf('%s-%s', $prefixValue, ++$suffix)); } return $object; }
/** * Create view and mview table * 1) Create view; * 2) Create table; * 3) Update view name and status in metadata. * * @return Enterprise_Mview_Model_Action_Mview_Create * @throws Exception */ public function execute() { try { if (!$this->_view->isExists()) { $this->_view->createFromSource($this->_select); } if (!$this->_table->isExists()) { $select = $this->_connection->select()->from($this->_view->getObjectName()); $this->_table->createFromSource($select); } $this->_metadata->setViewName($this->_view->getObjectName()); $this->_metadata->setValidStatus(); $this->_metadata->save(); } catch (Exception $e) { $this->_view->drop(); $this->_table->drop(); throw $e; } return $this; }
protected function _processTemplates(&$data) { $config = $this->_adapter->getConfig(); $select = $this->_adapter->select(); $magentoVersion = Mage::getVersion(); //vietdq fix checkout 1.5 if (version_compare($magentoVersion, '1.5', '>=') && version_compare($magentoVersion, '1.6', '<')) { $tableName = Mage::getSingleton('core/resource')->getTableName('customer_entity'); } else { $tableName = $this->_adapter->getTableName('customer_entity'); } $select->from('information_schema.tables', 'AUTO_INCREMENT')->where('table_schema = ?', $config['dbname'])->where('table_name = ?', $tableName); $nextId = $this->_adapter->fetchOne($select); foreach ($data['account'] as &$field) { $field = str_replace('{id}', $nextId, $field); } foreach ($data['address'] as &$address) { foreach ($address as &$field) { $field = str_replace('{id}', $nextId, $field); } } }
protected function _prepareLockName($name) { $config = $this->_connection->getConfig(); return $config['dbname'] . '.' . $name; }
/** * Join url rewrite to select * * @param Varien_Db_Select $select * @param int $storeId * @return Mage_Catalog_Helper_Category_Url_Rewrite */ public function joinTableToSelect(Varien_Db_Select $select, $storeId) { $select->joinLeft(array('url_rewrite' => $this->_resource->getTableName('core/url_rewrite')), 'url_rewrite.category_id=main_table.entity_id AND url_rewrite.is_system=1 AND ' . $this->_connection->quoteInto('url_rewrite.store_id = ? AND ', (int) $storeId) . $this->_connection->prepareSqlCondition('url_rewrite.id_path', array('like' => 'category/%')), array('request_path' => 'url_rewrite.request_path')); return $this; }
/** * Get downloadable link final price select * * @param Varien_Db_Adapter_Interface $write * @param string $table * * @return Zend_Db_Select */ public function getDownloadableLinkFinalPriceSelect($write, $table) { if ($this->getVersionHelper()->isGe1600()) { $ifTierPrice = $write->getCheckSql('i.tier_price IS NOT NULL', '(i.tier_price + id.min_price)', 'NULL'); if ($this->getVersionHelper()->isGe1700()) { $ifGroupPrice = $write->getCheckSql('i.group_price IS NOT NULL', '(i.group_price + id.min_price)', 'NULL'); } $tierPrice = new Zend_Db_Expr($ifTierPrice); } else { $tierPrice = new Zend_Db_Expr('IF(i.tier_price IS NOT NULL, i.tier_price + id.min_price, NULL)'); } $select = $write->select()->join(array('id' => $table), 'i.entity_id = id.entity_id AND i.customer_group_id = id.customer_group_id' . ' AND i.website_id = id.website_id', array())->columns(array('min_price' => new Zend_Db_Expr('i.min_price + id.min_price'), 'max_price' => new Zend_Db_Expr('i.max_price + id.max_price'), 'tier_price' => $tierPrice)); if ($this->getVersionHelper()->isGe1700()) { $select->columns(array('group_price' => new Zend_Db_Expr($ifGroupPrice))); } return $select; }
/** * Garbage collection * * @param int $maxLifeTime * @return boolean */ public function gc($maxLifeTime) { $where = array('session_expires < ?' => time() - $maxLifeTime); $this->_write->delete($this->_sessionTable, $where); return true; }
/** * 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; }
/** * Clear changelog table * * @return Enterprise_Mview_Model_Action_Changelog_Clear */ public function execute() { $this->_connection->delete($this->_metadata->getChangelogName(), array('version_id < ?' => $this->_metadata->getVersionId())); return $this; }