Symfony World blog is not maintained anymore. Check new sys.exit() programming blog.

svn log user

SVN log tool does not provide any option to filter output by the user. Using the --username option won't do the job, unfortunately.

But you may filter SVN changes commited by a chosen user using the following:

svn log | sed -n '/| username |/,/-----$/ p'
Basing on that, you may create a bash script, called svn-log-user for example, with the following content:
#!/bin/bash
svn log | sed -n "/| $1 |/,/-----$/ p"
Then, put it inside the /usr/local/bin directory or anywhere that is accessible from the path, run:
echo $PATH
to check which directories are in the path. Then, call it with:
./svn-log-user username

Prestashop 1.2 performance bug - customer groups

performance sufferings

If your prestashop 1.2 database is populated with few hundred customer records, your e-shop may be in real danger of very bad performance. The problem lies in lack of properly set MySQL indexes and primary keys. Within the time, your shop may be unusable, because MySQL server will harvest enormous piles of data, leaving the end-user to wait for the browser response half a minute or so. This is a serious threat which has been fixed in newer prestashop versions. But some people still run the 1.2 prestashop version since 2009/2010 was the time when prestashop was becoming very popular. This version is concerned, however, to be one of the worst versions of prestashop ever.

the problem

Group feature is used to define reductions, price displaying methods and some price-related stuff that refer to only a part of the shop. This may be: chosen customers (ps_customer_group table being used for that), chosen product categories (ps_category_group) and so on. Although, it is not commonly used by merchants. Fortunately not, because it makes performance extremely bad.

Below is an example of a bad query. I fetched it using MySQL slow logs:

# Query_time: 20.056626  Lock_time: 0.000182 Rows_sent: 1  Rows_examined: 372383                                                                                 
SELECT p.*, pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, p.`ean13`,i.`id_image`, il.`legend`, t.`rate`                                                                                                      
FROM `ps_product` p                                                                                                                              
LEFT JOIN `ps_product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = 3)
LEFT JOIN `ps_image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
LEFT JOIN `ps_image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = 3)
LEFT JOIN `ps_tax` t ON t.`id_tax` = p.`id_tax`
LEFT JOIN `ps_category_product` cp ON (cp.`id_product` = p.`id_product`)
INNER JOIN `ps_category_group` ctg ON (ctg.`id_category` = cp.`id_category`)
INNER JOIN `ps_customer_group` cg ON (cg.`id_group` = ctg.`id_group`)
WHERE (`reduction_price` > 0 OR `reduction_percent` > 0)
AND (`reduction_from` = `reduction_to` OR (`reduction_from` <= '2012-10-14' AND `reduction_to` >= '2012-10-14'))
AND p.`active` = 1
AND (cg.`id_customer` = 223 OR ctg.`id_group` = 1)
ORDER BY RAND() LIMIT 1;

Such query took me 20 seconds and in my database I've got around 500 orders, less than 300 customers and over 1700 products only. Now I call the MySQL EXPLAIN:

+----+-------------+-------+--------+-----------------------------------------+--------------------+---------+--------------------------------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys                           | key                | key_len | ref                                  | rows | Extra                                        |
+----+-------------+-------+--------+-----------------------------------------+--------------------+---------+--------------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | p     | ALL    | PRIMARY,reduction_date                  | NULL               | NULL    | NULL                                 | 1702 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | pl    | eq_ref | product_lang_index,id_lang              | product_lang_index | 8       | ad9bis_prestashop.p.id_product,const |    1 |                                              |
|  1 | SIMPLE      | cp    | ref    | category_product_index,product_category | product_category   | 4       | ad9bis_prestashop.p.id_product       |    3 | Using where                                  |
|  1 | SIMPLE      | ctg   | ref    | PRIMARY,group_category                  | PRIMARY            | 4       | ad9bis_prestashop.cp.id_category     |    1 | Using index                                  |
|  1 | SIMPLE      | i     | ref    | image_product                           | image_product      | 4       | ad9bis_prestashop.p.id_product       |    3 |                                              |
|  1 | SIMPLE      | cg    | index  | PRIMARY,customer_login,id_customer      | PRIMARY            | 8       | NULL                                 |  238 | Using where; Using index; Using join buffer  |
|  1 | SIMPLE      | il    | eq_ref | image_lang_index                        | image_lang_index   | 8       | ad9bis_prestashop.i.id_image,const   |    1 |                                              |
|  1 | SIMPLE      | t     | eq_ref | PRIMARY                                 | PRIMARY            | 4       | ad9bis_prestashop.p.id_tax           |    1 |                                              |
+----+-------------+-------+--------+-----------------------------------------+--------------------+---------+--------------------------------------+------+----------------------------------------------+
8 rows in set (0.02 sec)

As you can see, ALL type is used to seek ps_product records. Afterwards, index type is used to join ps_customer_group records. Such queries are not optimized at all (that's why all old prestashop applications shoud be upgraded to newer versions).

the clean solution

I spent some time trying to fix proper indexes on database tables. It's quite frustrating, because there are almost 150 tables and you should check most of them. Of course, the biggest problem is on the m:n relational tables (ps_product_tag, ps_category_group, etc.) but even the product table is missing a proper index then reduction_from and reduction_to column values are being compared (as you can see in the example above).

So, instead of the clean solution, I chose...

the brute-force solution

Finally, I decided to cut off (i.e. completely remove) the entire customer group functionality from PHP code level, because neither of two prestashop application owners I work with use the feature and this gave me 100% that prestashop will work as fast as possible. Take a look at the code:

$sql = '...
LEFT JOIN `'._DB_PREFIX_.'manufacturer` as m ON (m.`id_manufacturer`= p.`id_manufacturer`)
LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_product` = p.`id_product`)
INNER JOIN `'._DB_PREFIX_.'category_group` ctg ON (ctg.`id_category` = cp.`id_category`)
'.($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').'
WHERE ('.($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').' ctg.`id_group` = 1)
AND m.`id_manufacturer` = '.intval($manufacturer['id_manufacturer']).'
GROUP BY p.`id_product`';

Look at the fragment using the cookie object:

$cookie->id_customer ? '--sql wipe out code--' : ''

PHP checks if user is logged in. If the user is not loggen in (anonymous), PHP won't know which group does this customer belong to, so it ommits the bad SQL clauses. And this makes sense - for anonymous users, prestashop 1.2 works like a charm.

You may save yourself a lot of pain by getting rid of those SQL clauses. Grep the classes directory for the cookie phrase:

grep -Rn '$cookie->id_customer ?' .
You'll have to modify just 6 classes:
  • Manufacturer.php
  • Product.php
  • ProductSale.php
  • Search.php
  • Supplier.php
  • Tag.php
Below I dump my git repository diff that applies those changes:

commit 32f6a9facaf77ca9c8326baf288187889b3e1fc4
Author: Tomasz Ducin
Date:   Sun Oct 14 12:07:09 2012 +0200

    customer_group removed entirely from all sql queries

diff --git a/classes/Manufacturer.php b/classes/Manufacturer.php
index 1b9d5df..e1cabe6 100644
--- a/classes/Manufacturer.php
+++ b/classes/Manufacturer.php
@@ -176,8 +176,8 @@ class        Manufacturer extends ObjectModel
                     LEFT JOIN `'._DB_PREFIX_.'manufacturer` as m ON (m.`id_manufacturer`= p.`id_manufacturer`)
                     LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_product` = p.`id_product`)
                     INNER JOIN `'._DB_PREFIX_.'category_group` ctg ON (ctg.`id_category` = cp.`id_category`)
-                    '.($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').'
-                    WHERE ('.($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').' ctg.`id_group` = 1)
+                    './*($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').*/'
+                    WHERE ('./*($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').*/' ctg.`id_group` = 1)
                     AND m.`id_manufacturer` = '.intval($manufacturer['id_manufacturer']).'
                     GROUP BY p.`id_product`';
                 $result = Db::getInstance()->ExecuteS($sql);
@@ -251,9 +251,9 @@ class        Manufacturer extends ObjectModel
             FROM `'._DB_PREFIX_.'product` p
             LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_product` = p.`id_product`)
             INNER JOIN `'._DB_PREFIX_.'category_group` ctg ON (ctg.`id_category` = cp.`id_category`)
-            '.($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').'
+            './*($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').*/'
             WHERE p.id_manufacturer = '.intval($id_manufacturer).'
-            AND ('.($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').' ctg.`id_group` = 1)'
+            AND ('./*($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').*/' ctg.`id_group` = 1)'
             .($active ? ' AND p.`active` = 1' : '')
             .' GROUP BY p.`id_product`');
             return intval(sizeof($result));
@@ -270,9 +270,9 @@ class        Manufacturer extends ObjectModel
         LEFT JOIN `'._DB_PREFIX_.'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
         LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_product` = p.`id_product`)
         INNER JOIN `'._DB_PREFIX_.'category_group` ctg ON (ctg.`id_category` = cp.`id_category`)
-        '.($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').'
+        './*($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').*/'
         WHERE p.`id_manufacturer` = '.intval($id_manufacturer).($active ? ' AND p.`active` = 1' : '').'
-        AND ('.($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').' ctg.`id_group` = 1)
+        AND ('./*($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').*/' ctg.`id_group` = 1)
         GROUP BY p.`id_product`
         ORDER BY '.(($orderBy == 'id_product') ? 'p.' : '').'`'.pSQL($orderBy).'` '.pSQL($orderWay).' 
         LIMIT '.((intval($p) - 1) * intval($n)).','.intval($n);
diff --git a/classes/Product.php b/classes/Product.php
index 3f54c90..0ffba15 100644
--- a/classes/Product.php
+++ b/classes/Product.php
@@ -1006,10 +1006,10 @@ class        Product extends ObjectModel
             LEFT JOIN `'._DB_PREFIX_.'manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`)
             LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_product` = p.`id_product`)
             INNER JOIN `'._DB_PREFIX_.'category_group` ctg ON (ctg.`id_category` = cp.`id_category`)
-            '.($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').'
+            './*($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').*/'
             WHERE p.`active` = 1
             AND DATEDIFF(p.`date_add`, DATE_SUB(NOW(), INTERVAL '.(Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20).' DAY)) > 0
-            AND ('.($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').' ctg.`id_group` = 1)
+            AND ('./*($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').*/' ctg.`id_group` = 1)
             GROUP BY p.`id_product`
             ORDER BY '.(isset($orderByPrefix) ? pSQL($orderByPrefix).'.' : '').'`'.pSQL($orderBy).'` '.pSQL($orderWay).'
             LIMIT '.intval($pageNumber * $nbProducts).', '.intval($nbProducts));
@@ -1041,14 +1041,14 @@ class        Product extends ObjectModel
         LEFT JOIN `'._DB_PREFIX_.'tax` t ON t.`id_tax` = p.`id_tax`
         LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_product` = p.`id_product`)
         INNER JOIN `'._DB_PREFIX_.'category_group` ctg ON (ctg.`id_category` = cp.`id_category`)
-        '.($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').'
+        './*($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').*/'
         WHERE (`reduction_price` > 0 OR `reduction_percent` > 0)
         '.((!$beginning AND !$ending) ?
             'AND (`reduction_from` = `reduction_to` OR (`reduction_from` <= \''.pSQL($currentDate).'\' AND `reduction_to` >= \''.pSQL($currentDate).'\'))'
         :
             ($beginning ? 'AND `reduction_from` <= \''.pSQL($beginning).'\'' : '').($ending ? 'AND `reduction_to` >= \''.pSQL($ending).'\'' : '')).'
         AND p.`active` = 1
-        AND ('.($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').' ctg.`id_group` = 1)
+        AND ('./*($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').*/' ctg.`id_group` = 1)
         ORDER BY RAND()');
 
         if ($row)
@@ -1090,9 +1090,9 @@ class        Product extends ObjectModel
             FROM `'._DB_PREFIX_.'product` p
             LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_product` = p.`id_product`)
             INNER JOIN `'._DB_PREFIX_.'category_group` ctg ON (ctg.`id_category` = cp.`id_category`)
-            '.($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').'
+            './*($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').*/'
             WHERE (p.`reduction_price` > 0 OR p.`reduction_percent` > 0)
-            AND ('.($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').' ctg.`id_group` = 1)
+            AND ('./*($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').*/' ctg.`id_group` = 1)
             AND p.`active` = 1';
             $result = Db::getInstance()->getRow($sql);
             return intval($result['nb']);
@@ -1108,14 +1108,14 @@ class        Product extends ObjectModel
         LEFT JOIN `'._DB_PREFIX_.'manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`)
         LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_product` = p.`id_product`)
         INNER JOIN `'._DB_PREFIX_.'category_group` ctg ON (ctg.`id_category` = cp.`id_category`)
-        '.($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').'
+        './*($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').*/'
         WHERE (`reduction_price` > 0 OR `reduction_percent` > 0)
         '.((!$beginning AND !$ending) ?
             'AND (`reduction_from` = `reduction_to` OR (`reduction_from` <= \''.pSQL($currentDate).'\' AND `reduction_to` >= \''.pSQL($currentDate).'\'))'
         :
             ($beginning ? 'AND `reduction_from` <= \''.pSQL($beginning).'\'' : '').($ending ? 'AND `reduction_to` >= \''.pSQL($ending).'\'' : '')).'
         AND p.`active` = 1
-        AND ('.($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').' ctg.`id_group` = 1)
+        AND ('./*($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').*/' ctg.`id_group` = 1)
         GROUP BY p.`id_product`
         ORDER BY '.(isset($orderByPrefix) ? pSQL($orderByPrefix).'.' : '').'`'.pSQL($orderBy).'`'.' '.pSQL($orderWay).'
         LIMIT '.intval($pageNumber * $nbProducts).', '.intval($nbProducts);
diff --git a/classes/ProductSale.php b/classes/ProductSale.php
index a55ec36..3016557 100644
--- a/classes/ProductSale.php
+++ b/classes/ProductSale.php
@@ -71,9 +71,9 @@ class        ProductSale
         LEFT JOIN `'._DB_PREFIX_.'tax` t ON (t.`id_tax` = p.`id_tax`)
         LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_product` = p.`id_product`)
         INNER JOIN `'._DB_PREFIX_.'category_group` ctg ON (ctg.`id_category` = cp.`id_category`)
-        '.($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').'
+        './*($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').*/'
         WHERE p.`active` = 1
-        AND ('.($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').' ctg.`id_group` = 1)
+        AND ('./*($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').*/' ctg.`id_group` = 1)
         GROUP BY p.`id_product`
         ORDER BY '.(isset($orderByPrefix) ? $orderByPrefix.'.' : '').'`'.pSQL($orderBy).'` '.pSQL($orderWay).'
         LIMIT '.intval($pageNumber * $nbProducts).', '.intval($nbProducts));
@@ -112,9 +112,9 @@ class        ProductSale
         LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON (cl.`id_category` = p.`id_category_default` AND cl.`id_lang` = '.intval($id_lang).')
         LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_product` = p.`id_product`)
         INNER JOIN `'._DB_PREFIX_.'category_group` ctg ON (ctg.`id_category` = cp.`id_category`)
-        '.($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').'
+        './*($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').*/'
         WHERE p.`active` = 1
-        AND ('.($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').' ctg.`id_group` = 1)
+        AND ('./*($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').*/' ctg.`id_group` = 1)
         GROUP BY p.`id_product`
         ORDER BY sales DESC
         LIMIT '.intval($pageNumber * $nbProducts).', '.intval($nbProducts));
diff --git a/classes/Search.php b/classes/Search.php
index 6811f69..1faaf18 100644
--- a/classes/Search.php
+++ b/classes/Search.php
@@ -180,10 +180,10 @@ class Search
             LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON (p.`id_category_default` = cl.`id_category` AND cl.`id_lang` = '.intval($id_lang).')
             LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_product` = p.`id_product`)
             INNER JOIN `'._DB_PREFIX_.'category_group` ctg ON (ctg.`id_category` = cp.`id_category`)
-            '.($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').'
+            './*($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').*/'
             WHERE '.implode(' AND ', $whereArray).'
             AND p.active = 1
-            AND ('.($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').' ctg.`id_group` = 1)
+            AND ('./*($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').*/' ctg.`id_group` = 1)
             GROUP BY p.`id_product`
             ORDER BY position DESC
             LIMIT 10';
@@ -192,7 +192,7 @@ class Search
         
         $queryResults = '
         SELECT SQL_CALC_FOUND_ROWS p.*, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`name`,
-        t.`rate`, i.`id_image`, il.`legend`, m.`name` AS manufacturer_name '.($cookie->id_customer ? ', cg.`id_group`' : '').'
+        t.`rate`, i.`id_image`, il.`legend`, m.`name` AS manufacturer_name './*($cookie->id_customer ? ', cg.`id_group`' : '').*/'
         '.$score.'
         FROM '._DB_PREFIX_.'product p
         LEFT JOIN `'._DB_PREFIX_.'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = '.intval($id_lang).')
@@ -202,10 +202,10 @@ class Search
         LEFT JOIN `'._DB_PREFIX_.'manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`)
         LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_product` = p.`id_product`)
         INNER JOIN `'._DB_PREFIX_.'category_group` ctg ON (ctg.`id_category` = cp.`id_category`)
-        '.($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').'
+        './*($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').*/'
         WHERE '.implode(' AND ', $whereArray).'
         AND p.active = 1
-        AND ('.($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').' ctg.`id_group` = 1)
+        AND ('./*($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').*/' ctg.`id_group` = 1)
         GROUP BY p.`id_product`
         '.($orderBy ? 'ORDER BY  '.$orderBy : '').($orderWay ? ' '.$orderWay : '').'
         LIMIT '.intval(($pageNumber - 1) * $pageSize).','.intval($pageSize);
diff --git a/classes/Supplier.php b/classes/Supplier.php
index 0052572..904d15d 100644
--- a/classes/Supplier.php
+++ b/classes/Supplier.php
@@ -109,8 +109,8 @@ class        Supplier extends ObjectModel
                     LEFT JOIN `'._DB_PREFIX_.'supplier` as m ON (m.`id_supplier`= p.`id_supplier`)
                     LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_product` = p.`id_product`)
                     INNER JOIN `'._DB_PREFIX_.'category_group` ctg ON (ctg.`id_category` = cp.`id_category`)
-                    '.($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').'
-                    WHERE ('.($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').' ctg.`id_group` = 1)
+                    './*($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').*/'
+                    WHERE ('./*($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').*/' ctg.`id_group` = 1)
                     AND m.`id_supplier` = '.intval($supplier['id_supplier']).'
                     GROUP BY p.`id_product`';
                 $result = Db::getInstance()->ExecuteS($sql);
@@ -170,9 +170,9 @@ class        Supplier extends ObjectModel
             FROM `'._DB_PREFIX_.'product` p
             LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_product` = p.`id_product`)
             INNER JOIN `'._DB_PREFIX_.'category_group` ctg ON (ctg.`id_category` = cp.`id_category`)
-            '.($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').'
+            './*($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').*/'
             WHERE p.id_supplier = '.intval($id_supplier).'
-            AND ('.($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').' ctg.`id_group` = 1)'
+            AND ('./*($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').*/' ctg.`id_group` = 1)'
             .($active ? ' AND p.`active` = 1' : '')
             .' GROUP BY p.`id_product`');
             return intval(sizeof($result));
@@ -189,9 +189,9 @@ class        Supplier extends ObjectModel
                 LEFT JOIN `'._DB_PREFIX_.'supplier` s ON s.`id_supplier` = p.`id_supplier`
                 LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_product` = p.`id_product`)
                 INNER JOIN `'._DB_PREFIX_.'category_group` ctg ON (ctg.`id_category` = cp.`id_category`)
-                '.($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').'
+                './*($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').*/'
             WHERE p.`id_supplier` = '.intval($id_supplier).($active ? ' AND p.`active` = 1' : '').'
-            AND ('.($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').' ctg.`id_group` = 1)
+            AND ('./*($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').*/' ctg.`id_group` = 1)
             GROUP BY p.`id_product`
             ORDER BY '.(($orderBy == 'id_product') ? 'p.' : '').'`'.pSQL($orderBy).'` '.pSQL($orderWay).' 
             LIMIT '.((intval($p) - 1) * intval($n)).','.intval($n);
diff --git a/classes/Tag.php b/classes/Tag.php
index d2ebbeb..cf272ea 100644
--- a/classes/Tag.php
+++ b/classes/Tag.php
@@ -119,10 +119,10 @@ class Tag extends ObjectModel
         LEFT JOIN `'._DB_PREFIX_.'product` p ON p.id_product = pt.id_product
         LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_product` = p.`id_product`)
         INNER JOIN `'._DB_PREFIX_.'category_group` ctg ON (ctg.`id_category` = cp.`id_category`)
-        '.($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').'
+        './*($cookie->id_customer ? 'INNER JOIN `'._DB_PREFIX_.'customer_group` cg ON (cg.`id_group` = ctg.`id_group`)' : '').*/'
         WHERE id_lang = '.intval($id_lang).'
         AND p.active = 1
-        AND ('.($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').' ctg.`id_group` = 1)
+        AND ('./*($cookie->id_customer ? 'cg.`id_customer` = '.intval($cookie->id_customer).' OR' : '').*/' ctg.`id_group` = 1)
         GROUP BY t.id_tag
         ORDER BY times DESC
         LIMIT 0, '.intval($nb));

If you ever need to undo this change, just uncomment those lines and you're done (but think twice before you go back to customer group joins).

Prestashop Category Tree as a Nested Set

Nested set is one of the approaches to represent a tree in relational databases.

how it works

Take a look at the example pictures from wikipedia:

This is just a tree with some numbers attached to node labels. Those numbers make a lot more sense if the tree is presented a nested sets (sets inside sets):

Note that reading all numbers from left to right is the ordered sequence of integer numbers. This is how the nested set are built and how the hierarchy is restored basing on those numbers.

Let's say, we've got a leaf node inside a tree. If this is just a normal tree (not a nested set), walking through all parents until reaching the root node will cost one subquery per each parent. But if we have a nested set, we can do this all with just one query by manipulating the left/right values of the sets - this is all what nested sets are here for.

parent of (a parent of (...)) a node

Walking from a specific node to the root node of the tree is used in the product display page. The breadcrumb section shows the path from the root node to the main category the product belongs to (product.id_category_default in the database). The Product::getParentCategories() method is called to find all parents of a chosen category:

WHERE c.nleft < (int)$category['nleft']
AND c.nright > (int)$category['nright']
ORDER BY c.nleft
You can imagine this as widening the left/right range. Take a look at the picture above: Jackets have the range of 6 : 7 (7 - 6 = 1 which is the smallest possible subset = the leaf). The left value will be decreased (the smaller one) while the right value will be increased (the bigger one). The parent of Jackets is Suits with the range of 3 : 8 (8 - 3 = 5). Men's category, the parent of Suits, will be found in the same way - decreasing the left value and increasing the right value, 2:9. If the left value equals 1, you're already at the root node.

children of a node

Finding all children of a specific node is done in the opposite way. You take all subsets of a given set. As everything is ordered, all nested subsets will have their left/right values inside the range of the chosen node:

WHERE c.nleft > (int)$category['nleft']
AND c.nright < (int)$category['nright']
ORDER BY c.nleft
In other words, we'll get all nodes that are situated between the left and the right border of the chosen node area. All nodes that are beyond this area are not the children of the chosen category.

Finding all children of a category tree node is used in the Category::getAllChildren() method.

nested sets in prestashop

Such approach was introduced in category structure in prestashop since version 1.4.0.5, released on December 22, 2010.

For anyone having older versions of the platform, prestashop provides special functionality which fills up all nested set data. It is implemented in Category class in Category::regenerateEntireNtree() method.

upgrading

Each new version of prestashop is provided with a small SQL script which enables to update the database structure to fit new functionalities - take a look at the install/sql/upgrade directory. It works just like doctrine migrations in symfony 1.x. The 1.4.0.5.sql script from the directory above includes the following lines:

ALTER TABLE `PREFIX_category` ADD `nleft` INT UNSIGNED NOT NULL DEFAULT '0' AFTER `level_depth`;
ALTER TABLE `PREFIX_category` ADD `nright` INT UNSIGNED NOT NULL DEFAULT '0' AFTER `nleft`;
ALTER TABLE `PREFIX_category` ADD INDEX `nleftright` (`nleft`, `nright`);
Those lines create 2 columns which store the left/right values which will be used to traverse the tree from child nodes to their parents.

database performance

Selects are faster with such structure. But be aware that each modification you make on any category tree node will fire regenerating entire nested set values - prestashop doesn't check where the modified node is - it builds the nested set from scratch (Category::regenerateEntireNtree()). If you have a really big tree, inserting a new category node can take quite a lot of time.

svn dump subrepo

Subversion tools enable you to dump only chosen parts of the original repository. This may be especially useful, when you want to separate a smaller project out from a bigger thing. svnadmin dump will be used along with svndumpfilter command. svndumpfilter must be run using one of the following subcommands: include or exclude. Depending on what is easier, you may specify which path prefixes (or directories) will be included into new subrepo dump - or which will be excluded.

For example, let's assume our original repository structure looks like the following:

/abc
  some content
/def
  some content
/ghi
  some content
/jkl
  some content
If we run:
$ svndumpfilter include /abc /def # pseudo command
our subrepo will consist of:
/abc
  some content
/def
  some content
but if we run:
$ svndumpfilter exclude /abc # pseudo command
our subrepo will consist of:
/def
  some content
/ghi
  some content
/jkl
  some content
I wrote pseudo command, since you have to stream the original repository first, so that svndumpfilter can filter its content. Real commands would look like:
$ svnadmin dump /home/tomasz/svn/original_repo | svndumpfilter include /abc /def --drop-empty-revs --renumber-revs > subrepo.svndump

$ svnadmin dump /home/tomasz/svn/original_repo | svndumpfilter exclude /abc --drop-empty-revs --renumber-revs > subrepo.svndump

Now let's take a look at some svndumpfilter options:

--drop-empty-revs

If filtering causes any revision to be empty (i.e., causes no change to the repository), removes these revisions from the final dump file.

--renumber-revs

Renumbers revisions that remain after filtering.

Thanks to above options, the result repository dump will look as if it always consisted of the chosen parts (no empty commits).

PHP code security in prestashop

"Make Your Home Unattractive to Thieves" available at doityourself.com

Recently I had to improve mailing system of an existing prestashop platform. It was based on 1.2.2.0 version, released on 2009-08-27. The task was to use a gmail account over SMTP to authorize e-mail sender. It seems quite easy thanks to the swift mailer, which is included in prestashop package (prestashop 1.2.2.0 used swift 3.3.2). Unfortunately, the task turned out to be very painful since swift mailer was included in prestashop in a very bad way. Each time I tried to connect to the smtp service I was redirected to the project homepage with absolutely no errors, no logs and no any kind of information provided. But this was just the tip of the iceberg - the main problem was (and still is) elsewhere.

bubble index.php redirect

It's a security technique, adapted by prestashop, trying to block unwanted access to the file structure. The mechanism is based on a simple index.php file containing following code:

header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
header("Last-Modified: ".gmdate("D, d M Y H:i:s")." GMT");

header("Cache-Control: no-store, no-cache, must-revalidate");
header("Cache-Control: post-check=0, pre-check=0", false);
header("Pragma: no-cache");

header("Location: ../");
exit;
All directories inside the project, no matter where or how deep inside the file structure they are, contain this obligatory index.php file. The workflow is really simple - if you make a request (e.g. from a browser) to an existing directory (different from the root directory of the project) you'll step on the index.php that redirects your browser one directory up. There you'll step on another index.php file. You go up and up like a bubble in a water pool, until you get to the top level directory (could be document root), where the index.php controller file resides - it will generate the homepage. The browser will not display the entire redirecting route - it'll just load the homepage.

and my problem was

... that Swift Mailer stepped on index.php bubble each time a mail was going to be sent. And it was really frustrating and time-consuming to spot such bug.

If no authenticators are set before the SMTP session is started, Swift tries to handle it by himself. Swift iterates through all PHP files inside the Swift/Authenticator directory: loads them and tries one by one to authorize. Swift 3.3.2 authenticators directory contains: LOGIN.php, PLAIN.php, CRAMMD5.php, PopB4Smtp.php and... index.php bubble. The solution was to replace:

if (preg_match("/^[A-Za-z0-9-]+\\.php\$/", $file))
with:
if (preg_match("/^[A-Za-z0-9-]+\\.php\$/", $file) && $file != 'index.php')

prestashop security

As I said before, problem with badly included Swift Mailer is just the tip of the iceberg. The main problem in my opinion is using the "bubble redirect" system...

is it secure?

Let's focus on the weaknesses that a potential attacker could use. First of all, all PHP and other files are still accessible from the browser. Moreover, all those file paths are very easy to track. It's not difficult for a PHP developer to find out that a shop is based on the prestashop engine (routing should be enough to determine this, especially that routing is not configurable). Just download a prestashop package to discover entire file structure, which is probably unmodified in most of the shops available online (prestashop's aim is to be used mainly by non-PHP-developers so original structure will probably be kept). If any major prestashop security bug will be tracked in future, all online shops based on older versions will be defenseless.

Another danger is the (almost) omnipresent chmod -R 777. Prestashop installer script forces the user to grant recursive writable permissions to most of top level directories of the project.

how about a better solution?

The following question arises: how about virtual hosts and setting the document root to the public directory of the project? Such approach is adopted in most PHP frameworks (e.g. cakePHP or all versions of symfony framework: 1 and 2). Hiding files (moving them outside the document root) disables direct access to the files, which dramatically reduces the risk of a successful attack. Another possibility is to use .htaccess files among project file structure.

The above approach is clearly defined in the PHP manual security chapter. Ocrow wrote:

[...] The most robust way to guard against this possibility is to prevent your webserver from calling the library scripts directly, either by moving them out of the document root, or by putting them in a folder configured to refuse web server access. [...]

Note that it was submitted in 2003...

virtual hosts? how about shared hosting limitations?

Most shared hosting providers allow their customers access to the /home/user directory where they can put whatever they want and it won't be accessible directly from the web. Only /home/user/public_html (or a similar directory) is accessible. And it is feasible to design the installer script to copy or move the files outside the document root - PHP will need only the autoloader properly set. Or use .htaccess instead.

prestashop 1.5 still uses bubble redirect

The only thing that changed, concerning above topic, is a three-line comment inside the swift mailer included. If you look at tools/swift/Swift/Authenticator/index.php, you'll find:

header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
header("Last-Modified: ".gmdate("D, d M Y H:i:s")." GMT");

header("Cache-Control: no-store, no-cache, must-revalidate");
header("Cache-Control: post-check=0, pre-check=0", false);
header("Pragma: no-cache");

// The header location has been commented because swift do a require() on each .php of this folder
// (and this relocation make it do it recursively on each PrestaShop folder and so PrestaShop PHP file)
//header("Location: ../");
exit;

Well, this is not a solution. It's just a problem workaround. I understand, that a PHP framework is a complex tool and is used by programmers who can adopt webserver features, chmod directories and so on - and e-commerce package should be easy enough to be installed by an average internet user. But "bubble index.php redirect" is not the best way to secure the application code.

it doesn't mean that prestashop is bad, though...

I want to make it clear: I do appreciate all the hard work the prestashop team has done already (really)! Prestashop is becoming more and more popular - it's a great success. And I'm a prestashop user/developer after all :). Anyway, I strongly encourage the developers team to discuss the security topic and the installer script.

symfony cron task logging example

cron task logging

Some time ago I wrote about a magnificent plugin managing cron task runtime, sfTaskLoggerPlugin and I promised to provide some real world examples of the plugin's usage. The time has come, so let's go on!

archiving outdated products

I'll start with an easy functionality which is executed each day using a cron task. ProductManager class is just a high-level model class, which is separated from ORM business logic, since it has nothing to do with Doctrine directly. The archiveProducts method fetches all products that meet some specific criteria (in this case - products that are not active no more). The first lines use the ORM-level layer to fetch Doctrine records. Then, all products are again checked against another criteria, inside a loop, and if any pass, they're permanently archived. The result of this method is an array of archived product IDs - this is very important data for the task logger. Take a look at the following code:

class ProductManager
{
  static public function archiveProducts()
  {
    $products = Doctrine::getTable('Product')
      ->findProductsQuery()
      ->execute();
 
    $result = array();
    foreach ($products as $product)
    {
      $availability_history = $product->getAvailabilityHistory()->getLast();
      if ($availability_history->shouldBeArchived())
      {
        $product->archive();
        $result[] = $product['id'];
      }
    }
    return $result;
  }
 
...

The model layer is done, now it's time to prepare the controller layer - and this is the main thing here. Let's start with implementing the command line symfony task. We create a lib/task/archiveProductsTask.class.php file:

<?php
 
require_once(dirname(__FILE__) . '/../../plugins/sfTaskLoggerPlugin/lib/task/sfBaseTaskLoggerTask.class.php');
 
class archiveProductsTask extends sfBaseTaskLoggerTask
{
}

We will put all necessary task stuff here. Note that our new custom task archiveProductsTask class extends the plugin sfBaseTaskLoggerTask class. And plugin sfBaseTaskLoggerTask class extends the base symfony sfBaseTask class (without sfTaskLoggerPlugin, a custom task would just directly extend sfBaseTask). Thanks to it, you may adapt the sfBaseTaskLoggerTask class to meet your needs, if you want some custom mechanisms to be available in all your custom tasks.

We define the constants that would be used to state the return code of a task run:

const ERROR_CODE_FAILURE = -1;
const ERROR_CODE_SUCCESS = 1;

Now let's define the symfony task standards - task name, namespace and descriptions, which are available in the command line:

protected function configure()
  {
    parent::configure();
 
    $this->namespace = 'cron';
    $this->name = 'archiveProducts';
    $this->briefDescription = 'Mark products as archived';
    $this->detailedDescription = <<<EOF
The [cron:archiveProducts|INFO] task finds all products that were
for a given amount of time and sets their status to archival. Call it
with:
 
  [php symfony cron:archiveProducts|INFO].
EOF;
  }

Having this class defined, you may run:

./symfony
in your project root directory to find, that a new command cron:archiveProducts is now available. Simply, run:
./symfony cron:archiveProducts

And here comes the task logger part of the task implementation. Just to remind - each time the cron:archiveProducts is executed, a single task_logger record is inserted into the database. This record holds all important information about the task runtime, these are:

  • task name
  • task arguments
  • task options
  • records processed
  • records not processed
  • start time
  • end time
  • task is still running
  • task has successfuly finished
  • error code
  • comment
and some more. Of course, you may modify it and do whatever you want (add new or remove existing information) - these are just the most basic information each task execution should log somewhere.

Let's implement the comment generator - you may either generate a plain text comment or even HTML code (if you want lists or links to be nicely displayed). You need to consider it yourself, since big amounts of HTML, generated every day or even more frequently, will use lots of disk space in MySQL, so that your database grows bigger and bigger each day. Think about that ;).

protected function generateComment($result)
  {
    $comment = '';
    $count = count($result);
    if ($count > 0)
    {
      $comment = 'Products changed into archival<ul>';
      foreach ($result as $product)
      {
        $comment .= '<li>'.$product.'</li>';
      }
      $comment .= '</ul>';
    }
    $this->task->setCountProcessed($count);
    return $comment;
  }

And finally, let's implement the rest of task logger logic. The most important function here is the doProcess. Everything is done inside the try-catch block. First, the model layer is called to do the data processing stuff. Once the data is processed, we may interpret the results. We generate the comments (basing on the result array), we set the error code and set the is_ok field if no exceptions were raised. If any problem was encountered, is_ok has to be set to false, using setNOk() method:

protected function doProcess($arguments = array(), $options = array())
  {
    try
    {
      $result = ProductManager::archiveProducts();
      $this->task->setComments($this->generateComment($result));
      $this->task->setErrorCode(self::ERROR_CODE_SUCCESS);
      $this->setOk();
    } catch (Exception $e)
    {
      $this->task->setErrorCode(self::ERROR_CODE_FAILURE);
      $this->setNOk($e);
    }
  }

Our new custom task class looks like this now:

require_once(dirname(__FILE__) . '/../../plugins/sfTaskLoggerPlugin/lib/task/sfBaseTaskLoggerTask.class.php');
 
class archiveProductsTask extends sfBaseTaskLoggerTask
{
  const ERROR_CODE_FAILURE = -1;
  const ERROR_CODE_SUCCESS = 1;
 
  protected function configure()
  {
    parent::configure();
 
    $this->namespace = 'cron';
    $this->name = 'archiveProducts';
    $this->briefDescription = 'Mark products as archived';
    $this->detailedDescription = <<<EOF
The [cron:archiveProducts|INFO] task finds all products that were
for a given amount of time and sets their status to archival. Call it
with:
 
  [php symfony cron:archiveProducts|INFO].
EOF;
  }
 
  protected function generateComment($result)
  {
    $comment = '';
    $count = count($result);
    if ($count > 0)
    {
      $comment = 'Produkty zmienione na archiwalne<ul>';
      foreach ($result as $product)
      {
        $comment .= '<li>'.$product.'</li>';
      }
      $comment .= '</ul>';
    }
    $this->task->setCountProcessed($count);
    return $comment;
  }
 
  protected function doProcess($arguments = array(), $options = array())
  {
    try
    {
      $result = ProductManager::archiveProducts();
      $this->task->setComments($this->generateComment($result));
      $this->task->setErrorCode(self::ERROR_CODE_SUCCESS);
      $this->setOk();
    } catch (Exception $e)
    {
      $this->task->setErrorCode(self::ERROR_CODE_FAILURE);
      $this->setNOk($e);
    }
  }
}

one example is enough. Think about the new possibilities

I'm not going to give dozens of similar examples of code implementation, since the task logger plugin logic would be mostly the same.

If you have a big symfony 1.x application, I strognly encourage you to install the sfTaskLoggerPlugin and redefine some of your task, so that they would be logged each time they're executed. After some time take a look at your task logs - you'll see how many information you can get:

  • analyse your task runtime performance
  • analyse how many times task have failed
  • analyse how many records are being processed
  • analyse what time the task processes the most data and what time there is no processed data
  • implement another mechanism to monitor all your task logs and alarm, when any task fails (check the is_running against is_ok task_logger record fields)
and so on. Give it a try! ;)