eschrade Magento EE Indexes

code_snippets.php

<?php
// Decoded HTML for http://www.eschrade.com/page/indexing-in-magento-or-the-wonderful-world-of-materialized-views/

$logTable = Mage::getModel('log/log')->getResource()->getTable('url_table');

//Separator

$pkData = $this->_conn->getIndexList($logTable);
if (!isset($pkData['PRIMARY']['COLUMNS_LIST'][0])) {
    Mage::throwException('Unable to find log table primary key');
}
$logPrimaryKey = $pkData['PRIMARY']['COLUMNS_LIST'][0];

//Separator


Mage::getModel('enterprise_mview/metadata')
    ->setTableName($logTable)
    ->setViewName($logTable)
    ->setKeyColumn($logPrimaryKey)
    ->setGroupCode('eschrade_useragent_report')
    ->setStatus(Enterprise_Mview_Model_Metadata::STATUS_INVALID)
    ->save();

//Separator
$client = Mage::getModel('enterprise_mview/client');

/* @var $client Enterprise_Mview_Model_Client */
$client->init($logTable);

//Separator

$client->execute('enterprise_mview/action_changelog_create', array(
    'table_name' => $logTable
));


//Separator

$client->execute('enterprise_mview/action_changelog_subscription_create', array(
    'target_table' => $logTable,
    'target_column' => $logPrimaryKey
));

//Separator


class Eschrade_UserAgent_Model_Index_Ua_Changed extends  Enterprise_Mview_Model_Action_Mview_Refresh_Changelog
{
    public function execute()
    {
        try {
            $this->_connection->beginTransaction();
            $this->preIndexHook();
            $this->populateUserAgentTable();
            $versionId = $this->_selectLastVersionId();
            $this->processIndex();
            $this->_metadata->setValidStatus()
                ->setVersionId($versionId)
                ->save();
            $this->_connection->commit();
        } catch (Exception $e) {
            $this->_connection->rollBack();
            $this->_metadata->setInvalidStatus()->save();
            throw $e;
        }
        return $this;
    }
}


//Separator


public function processIndex()
{
    $indexTable= Mage::getModel('eschrade_useragent/agentreport')->getResource()->getMainTable();
    $select = $this->_selectChangedRows();
    $this->joinSelectWithUrlAndVisitor($select);
    $stmt = $select->query();

    while (($row = $stmt->fetch()) !== false) {
        $this->_connection->insertOnDuplicate(
            $indexTable,
            array(
                'page' => $row['url'],
                'agent' => $row['normalized_user_agent'],
                'request_count' => $row['page_count']
            ),
            array(
                'request_count'  => new Zend_Db_Expr('request_count + VALUES(request_count)')
                )
            );
        }
}

//Separator


class Eschrade_UserAgent_Model_Index_Ua_All extends Eschrade_UserAgent_Model_Index_Ua_Changed
{
    public function preIndexHook()
    {
        $this->_connection->truncateTable(
            $this->getReportResource()->getTable('agentreport')
        );
    }

    protected function joinSelectWithUrlAndVisitor(Varien_Db_Select $select)
    {
        $select->reset(Varien_Db_Select::WHERE);
        parent::joinSelectWithUrlAndVisitor($select);
    }

}