fix-sample-data.php

<?php

/**
 * Drop this into the shell directory in the Magento root and run without any arguments.
 */

require_once 'abstract.php';

/**
 * Clean up the 1.6.1 sample data to work with the 1.8 CE url key constraints.
 * Run this if the URL Rewrite index does not want to run.
 *
 * @author      Vinai Kopp <vinai@netzarbeiter.com>
 */
class Netzarbeiter_Fix_Sampledata extends Mage_Shell_Abstract
{
    /** @var Mage_Eav_Model_Entity_Attribute */
    protected $_attr;
    /** @var Varien_Db_Adapter_Pdo_Mysql */
    protected $_connection;
    /** @var string */
    protected $_table;

    public function run()
    {
        $this->_showHelp();
        $dupes = $this->_gatherDupeUrlKeys();
        if ($this->getArg('list')) {
            $this->_listDupes($dupes);
        } else {
            $this->_fixDupes($dupes);
        }
    }

    protected function _gatherDupeUrlKeys()
    {
        $this->_attr = Mage::getSingleton('eav/config')->getAttribute(Mage_Catalog_Model_Product::ENTITY, 'url_key');
        $this->_connection = Mage::getSingleton('core/resource')->getConnection('eav_write');
        $this->_table = Mage::getSingleton('core/resource')->getTableName('catalog_product_entity_varchar');
        /** @var Varien_Db_Select $select */
        $select = $this->_connection->select()->from($this->_table, array(
                'num' => new Zend_Db_Expr('COUNT(*)'),
                'url_key' => 'value'
            ))
            ->where('attribute_id=?', $this->_attr->getId())
            ->group('value')
            ->having('num > 1');
        Mage::getResourceHelper('core')->addGroupConcatColumn($select, 'entities', 'entity_id');
        $result = $this->_connection->fetchAll($select);
        return $result;
    }

    protected function _listDupes(array $dupes)
    {
        foreach ($dupes as $row) {
            printf("Found %d entities with url_key '%s': %s\n", $row['num'], $row['url_key'], $row['entities']);
        }
    }

    protected function _fixDupes(array $dupes)
    {
        foreach ($dupes as $row) {
            $ids = explode(',', $row['entities']);
            foreach ($ids as $idx => $entityId) {
                if (0 === $idx) {
                    continue; // keep the first url key unmodified
                }
                $key = $this->_qualifyUrlKey($row['url_key']);
                $where = array(
                    'attribute_id=?' => $this->_attr->getId(),
                    'entity_id=?' => $entityId
                );

                // If record exists in the new table, update it. If not, insert
                if ($this->_recordInNewTableExists($where)) {
                    $this->_connection->update(
                        $this->_attr->getBackend()->getTable(), array('value' => $key), $where
                    );
                } else {
                    $this->_connection->insert(
                        $fields =
                        $this->_attr->getBackend()->getTable(),
                        array(
                            'entity_type_id' => $this->_attr->getEntityTypeId(),
                            'attribute_id' => $this->_attr->getId(),
                            'entity_id' => $entityId,
                            'store_id' => 0,
                            'value' => $key
                        )
                    );
                }

                // Just for consistency, update the old url_key eav value table, too
                $this->_connection->update($this->_table, array('value' => $key), $where);

            }
        }
    }

    protected function _recordInNewTableExists(array $where)
    {
        $select = $this->_connection->select()
            ->from($this->_attr->getBackend()->getTable(), array(
                new Zend_Db_Expr('COUNT(*)'),
            ));
        foreach ($where as $cond => $bind) {
            $select->where($cond, $bind);
        }
        $count = $this->_connection->fetchOne($select);
        return (bool) $count;
    }

    protected function _qualifyUrlKey($key)
    {
        $sentry = 0;
        $select = $this->_connection->select()->from($this->_table, array(
                new Zend_Db_Expr('COUNT(*)'),
            ))
            ->where('attribute_id=?', $this->_attr->getId())
            ->where('value=:key');
        do {
            if ($sentry++ == 1000) {
                Mage::throwException(sprintf('Unable to qualify urk_key "%s": reached 1000 tries', $key));
            }
            $candidate = $key . '-'. $sentry;
            $bind = array('key' => $candidate);
        } while ($this->_connection->fetchOne($select, $bind));
        return $candidate;
    }

    /**
     * Retrieve Usage Help Message
     *
     */
    public function usageHelp()
    {
        return <<<USAGE
Usage:  php -f fix-sample-data.php
  list                   List entites with duplicate url keys
  fix                    Uniquely qualify duplicate URL keys (default)
  help                   This help

USAGE;
    }
}

$shell = new Netzarbeiter_Fix_Sampledata();
$shell->run();