How to alter catalog_product_flat column types

Question

A website I manage requires prices to have 5 decimals. I went ahead and modified at least a dozen file/tables altogether to be able to achieve this. But ('cause there's always a but) there seems to be a problem with catalog_product_flat_X.

Let me explain : for prices on the front, most of the time, it's come down to calling Mage_Catalog_Model_Product->getPrice(), which, as I understand it, will look for the price in the flat tables (if you have flat tables enabled). The issue is with the reindexing of data.

If my flat table has a price with the format DECIMAL(12,5), hitting the "Reindex" link on the "Reindex flat data" line will ALTER the flat table and put it back to DECIMAL(12,4).

After searching for quite an extented amount of time, I came accros a function in
app/code/core/Mage/Catalog/Model/Resource/Product/Flat/Indexer.php
which is called prepareFlatTable($storeId). This function then calls convertOldColumnDefinition($column) on every column of the flat table (in this case).

Finally, when looking at this function in
app/code/core/Mage/Catalog/Model/Resource/Helper/Abstract.php
it seems to be converting every decimal and numeric like so :

case 'decimal':
case 'numeric':
        $length = $proposedLength;
        $type = Varien_Db_Ddl_Table::TYPE_DECIMAL;
        break;  

in which $proposedLength is the result of this :

$definition = trim($column['type']);
if (!preg_match('/([^(]*)(\\((.*)\\))?/', $definition, $matches)) {
    throw Mage::exception(
        'Mage_Core',
        Mage::helper('core')->__("Wrong old style column type definition: {$definition}.")
    );
}

$proposedLength = (isset($matches[3]) && strlen($matches[3])) ? $matches[3] : null;  

I don't quite understand what's going on here, I'd put something hardcoded in there but I don't want to interfere with the normal process of all the indexers.

Can anyone enlighten me on this matter and offer me a solution?

Answer 1

Let me give you some explanation about the code that converts the column definition:

    $definition = trim($column['type']);
    if (!preg_match('/([^(]*)(\\((.*)\\))?/', $definition, $matches)) {
        throw Mage::exception(
            'Mage_Core',
            Mage::helper('core')->__("Wrong old style column type definition: {$definition}.")
        );
    }

So basically the preg_match method will give you an array in the $matches variable based on the column definition (stored in $definition).

Example of columns definition usually looks like this:

• int
• varchar(100)
• decimal(12,4)

Those column definitions will respectively give you the following result in the $matches array:

• array('int','int')
• array('varchar(100)','varchar','(100)','100')
• array('decimal(12,4)','decimal','(12,4)','12,4')

Then you've got the $proposedLength variable:

$proposedLength = (isset($matches[3]) && strlen($matches[3])) ? $matches[3] : null;

It is basically equal to the 4th entry in the $matches array if this one is set.

So in your case the value of the $proposeLength should be 12,5.

My suggestion:

Add the following code right after the $proposedLength variable declaration:

Mage::log($column);
Mage::log($matches);
Mage::log($proposedLength);

Try again and check your var/log/system.log file to ensure that the code behaves like it should.