I wrote the following PHP script (symphony 1.4 Task), which loops over a CSV file an d imports the data via Doctrine into MySQL.
Unfortunately the script is aborted because it allocates to much memory. The used memory grows with each foreach cycle. I tried a lot of things, but i am not able to keep the used memory more stable.
Could anyone give me a hint?
<?php
class importERPTask extends sfBaseTask
{
protected function configure()
{
$this->addOptions(array(
new sfCommandOption('application', null, sfCommandOption::PARAMETER_REQUIRED, 'The application name', 'frontend'),
new sfCommandOption('env', null, sfCommandOption::PARAMETER_REQUIRED, 'The environment', 'dev'),
new sfCommandOption('connection', null, sfCommandOption::PARAMETER_REQUIRED, 'The connection name', 'doctrine'),
// add your own options here
));
$this->namespace = 'w2';
$this->name = 'importERP';
$this->briefDescription = 'Imports product data .';
}
public function execute($arguments = array(), $options = array())
{
// initialize the database connection
$databaseManager = new sfDatabaseManager($this->configuration);
$connection = $databaseManager->getDatabase($options['connection'])->getConnection();
$this->logSection('importERP', 'Start');
if(!(file_exists(sfConfig::get('sf_root_dir'). DIRECTORY_SEPARATOR .'import_data'.DIRECTORY_SEPARATOR.'VK.csv') && file_exists(sfConfig::get('sf_root_dir'). DIRECTORY_SEPARATOR .'import_data'.DIRECTORY_SEPARATOR.'Artikel.csv')))
{
$this->logSection('importERP', 'No import CSV');
$this->logSection('importERP', 'Done.');
return;
}
$this->importPrices();
//import products
$this->logSection('importERP', 'Import products');
Doctrine::getTable('CatalogueProduct')->setAllImportFalse();
$file_handle = fopen(sfConfig::get('sf_root_dir'). DIRECTORY_SEPARATOR .'import_data'.DIRECTORY_SEPARATOR.'Artikel.csv', 'r');
if($file_handle)
{
$i = 0;
while(!feof($file_handle))
{
//skip first line
if(++$i == 1)
{
continue;
}
$this->importProduct($file_handle);
}
fclose($file_handle);
}
$this->deleteProducts();
unlink(sfConfig::get('sf_root_dir'). DIRECTORY_SEPARATOR .'import_data'.DIRECTORY_SEPARATOR.'VK.csv');
unlink(sfConfig::get('sf_root_dir'). DIRECTORY_SEPARATOR .'import_data'.DIRECTORY_SEPARATOR.'Artikel.csv');
$this->logSection('importERP', 'Done.');
}
private function importPrices()
{
//import price helper table
$this->logSection('importERP', 'Import price helper table');
Doctrine::getTable('ImportHelperPrice')->clearAllData();
$file_handle = fopen(sfConfig::get('sf_root_dir'). DIRECTORY_SEPARATOR .'import_data'.DIRECTORY_SEPARATOR.'VK.csv', 'r');
if($file_handle)
{
$i = 0;
while(!feof($file_handle))
{
$line_of_text = fgetcsv($file_handle, 0, ';', '"');
//skip first line
if(++$i == 1)
{
continue;
}
$price = new ImportHelperPrice();
$price->setImportId($line_of_text[0]);
$price->setStartAmount($line_of_text[1]);
$price->setPrice(str_replace(',', '.', $line_of_text[2]));
$price->save();
}
}
}
private function importProduct($file_handle)
{
$line_of_text = fgetcsv($file_handle, 0, ';', '"');
$this->logSection('importERP', 'Import product '.$line_of_text[1]);
//no empty article number
if($line_of_text[0] == '')
{
$this->logSection('importERP', '... skipped');
return;
}
if($line_of_text[4] == '')
{
$this->logSection('importERP', '... has no category');
return;
}
$my_product = Doctrine::getTable('CatalogueProduct')->findOneByCode($line_of_text[0]);
$my_cat = Doctrine::getTable('CatalogueCategory')->findOneByImportCode($line_of_text[4]);
if(!$my_cat)
{
$this->logSection('importERP', '... has no category');
return;
}
if(!$my_product)
{
$this->logSection('importERP', '... is new');
$my_product = new CatalogueProduct();
$my_product->setCode($line_of_text[0]);
// do not overwrite handmade configurations from backend
$my_product->setVatId(1);
$my_product->setTemplateId(4);
}
else
{
$this->logSection('importERP', '... is updated');
}
//get prices
$price = Doctrine::getTable('ImportHelperPrice')->getPriceForImportId($line_of_text[0]);
if(!$price)
{
return;
}
$my_product->setPriceGrossEur($price->getPrice());
$my_product->Translation['de']->title = $line_of_text[2];
$my_product->Translation['de']->shortdescription = $line_of_text[3];
$my_product->Translation['de']->description =$line_of_text[3];
$my_product->setCatalogueCategory($my_cat);
$my_product->setHidden(false);
$my_product->setImportFlag(true);
$config_prices = Doctrine::getTable('ImportHelperPrice')->getPriceConfigForImportId($line_of_text[0]);
if($config_prices)
{
$price_config = '';
foreach($config_prices as $cp)
{
$discount = 100 - ($cp->getPrice() / ($price->getPrice() / 100));
if($discount == 0)
{
continue;
}
if($price_config != '')
{
$price_config .= ';';
}
$price_config .= $cp->getStartAmount() . ':' . number_format($discount, 2, ',', '');
}
$my_product->setPriceConfig($price_config);
}
//move images
$img_default = sfConfig::get('sf_root_dir'). DIRECTORY_SEPARATOR .'import_data'.DIRECTORY_SEPARATOR.'images'.DIRECTORY_SEPARATOR.$line_of_text[1].'_m.jpg';
if(file_exists($img_default))
{
rename($img_default, sfConfig::get('sf_web_dir').DIRECTORY_SEPARATOR.'products'.DIRECTORY_SEPARATOR.$line_of_text[1].'_m.jpg');
$my_product->setImageDefault($line_of_text[1].'_m.jpg');
$this->logSection('importERP', '... '.$my_product->getImageDefault().' saved');
}
else
{
$this->logSection('importERP', '... '.$img_default.' not found');
}
$img_zoom = sfConfig::get('sf_root_dir'). DIRECTORY_SEPARATOR .'import_data'.DIRECTORY_SEPARATOR.'images'.DIRECTORY_SEPARATOR.$line_of_text[1].'_gr.jpg';
if(file_exists($img_zoom))
{
rename($img_zoom, sfConfig::get('sf_web_dir').DIRECTORY_SEPARATOR.'products'.DIRECTORY_SEPARATOR.$line_of_text[1].'_zoom.jpg');
$my_product->setImageZoom($line_of_text[1].'_zoom.jpg');
$this->logSection('importERP', '... '.$my_product->getImageZoom().' saved');
}
else
{
$this->logSection('importERP', '... '.$img_zoom.' not found');
}
$img_icon = sfConfig::get('sf_root_dir'). DIRECTORY_SEPARATOR .'import_data'.DIRECTORY_SEPARATOR.'images'.DIRECTORY_SEPARATOR.$line_of_text[1].'_kl.jpg';
if(file_exists($img_icon))
{
rename($img_icon, sfConfig::get('sf_web_dir').DIRECTORY_SEPARATOR.'products'.DIRECTORY_SEPARATOR.$line_of_text[1].'_icon.jpg');
$my_product->setImageIcon($line_of_text[1].'_icon.jpg');
$this->logSection('importERP', '... '.$my_product->getImageIcon().' saved');
}
else
{
$this->logSection('importERP', '... '.$img_icon.' not found');
}
$my_product->save();
$this->logSection('importERP', 'Memory usage '.memory_get_peak_usage() / 1048576,2 .' MB');
}
private function deleteProducts()
{
//delete not mentioned products
$this->logSection('importERP', 'Delete not mentioned products');
$del_products = Doctrine::getTable('CatalogueProduct')->getAllImportFalse();
foreach($del_products as $dp)
{
$this->logSection('importERP', 'Delete '.$dp->getCode());
//delete images
$img_default = sfConfig::get('sf_web_dir').DIRECTORY_SEPARATOR.'products'.DIRECTORY_SEPARATOR.$dp->getImageDefault();
$img_zoom = sfConfig::get('sf_web_dir').DIRECTORY_SEPARATOR.'products'.DIRECTORY_SEPARATOR.$dp->getImageZoom();
$img_icon = sfConfig::get('sf_web_dir').DIRECTORY_SEPARATOR.'products'.DIRECTORY_SEPARATOR.$dp->getImageIcon();
if($dp->getImageDefault() != NULL && $dp->getImageDefault() != '' && file_exists($img_default))
{
unlink($img_default);
}
if($dp->getImageZoom() != NULL && $dp->getImageZoom() != '' && file_exists($img_zoom))
{
unlink($img_zoom);
}
if($dp->getImageIcon() != NULL && $dp->getImageIcon() != '' && file_exists($img_icon))
{
unlink($img_icon);
}
//delete product
$dp->delete();
}
}
}
The best solution would be to edit your php.ini file. Allocate:
memory_limit = 16M
Not sure if 16M is default but it should be close. Anyway, that's the amount of memory a PHP script is allowed to use. You can just set this number higher and solve the problem.
Alternatively you can write this on the top of your PHP script:
ini_set('memory_limit', '16M');
Where 16M can be changed to whatever amount of memory you wish the script to allow.
Watch out for ini_sets with values ending with 'M' - they don't work as expected. Shorthand notation works only in php.ini, appropriate info is included in the docs: http://php.net/manual/en/faq.using.php#faq.using.shorthandbytes
You should do:
...
$my_product->save();
$my_product->free();
unset($my_product);
...
Assuming your CSV is just too big for your server to handle in memory in one swoop, I would consider splitting the CSV into smaller files, which is quite a simple task with Unix commands (split, etc).
You would then execute each bitesize CSV asynchronously with your PHP script.