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',
                'store' => 'store_id'
            ))
            ->where('attribute_id=?', $this->_attr->getId())
            ->group('value')
            ->group('store_id')
            ->order('num')
            ->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. Store: %s\n", $row['num'], $row['url_key'], $row['entities'], $row['store']);
        }
    }

    protected function _fixDupes(array $dupes)
    {
        $processed = array();
        foreach ($dupes as $row) {
            echo "Processing ids: {$row['entities']} for store{$row['store']}\n";
            $ids = explode(',', $row['entities']);
            foreach ($ids as $idx => $entityId) {
                if (0 === $idx) {
                    continue; // keep the first url key unmodified
                }
                if (isset($processed[$entityId])) {
                    echo "Already processed id: {$entityId}. Skipping.\n";
                    continue;
                }
                $key = $this->_qualifyUrlKey($row['url_key']);
                echo "$entityId: $key\n";
                $where = array(
                    'attribute_id=?' => $this->_attr->getId(),
                    'entity_id=?' => $entityId,
                    'store_id=?' => $row['store']
                );

                // If record exists in the new table, update it. If not, insert
                if ($this->_recordInNewTableExists($where)) {
                    echo "Updating\n";
                    try {
                        $this->_connection->update(
                            $this->_attr->getBackend()->getTable(), array('value' => $key), $where
                        );
                    } catch (Exception $e) {
                        echo 'ERROR: ' . $e->getMessage() . "\n";
                    }
                } else {
                    echo "Inserting\n";
                    try {
                        $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' => $row['store'],
                                'value' => $key
                            )
                        );
                    } catch (Exception $e) {
                        echo 'ERROR: ' . $e->getMessage() . "\n";
                    }
                }

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

    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 url_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();

some-background.md

url_key storage in Magento 1.8

(scroll down for the actuall script)

I assume you are basically familiar with Magento's EAV table structure. I'll won't explain that here, only the changes introduces in regards to the url_key attribute in Magento 1.8.

Up to Magento 1.8 the url_key attribute values for products and categories where stored in the table catalog_product_entity_varchar. Uniqueness was only enforced on an application level.

Since Magento 1.8 the values are stored in a new attribute value table called catalog_product_entity_url_key. There uniqueness is enforced on the database level by a unique index on store_id and value (that is, the URL key).

The problem is, the 1.6.1 sample data contains some non-unique url_key values.

When upgrading to 1.8, or when installing 1.8 with the 1.6.1 sample data, the duplicate url_key values are silently dropped during the migration to the new table (well, all duplicates except one each).

Because of that, the URL rewrite indexer refuses to run.

This script will find the duplicate URL keys using the old values in catalog_product_entity_varchar, uniquely qualify each one, update (or insert) the record into the new catalog_product_entity_url_key table, and then also update the old attribute value table so everything is consistant, even if the old table values aren't used any more (except maybe by third party extensions).