- move #categories.date_last and nb_images to #user_cache_categories

git-svn-id: http://piwigo.org/svn/trunk@2324 68402e56-0260-453c-a942-63ccdbb3a9ee

10 files changed, 150 insertions, 202 deletions

diff --git a/admin/cat_list.php b/admin/cat_list.php
index 1ec2d4422..b8407c2f8 100644
--- a/admin/cat_list.php
+++ b/admin/cat_list.php
@@ -170,7 +170,7 @@ $template->assign(array(
 $categories = array();
 
 $query = '
-SELECT id, name, permalink, dir, rank, nb_images, status
+SELECT id, name, permalink, dir, rank, status
   FROM '.CATEGORIES_TABLE;
 if (!isset($_GET['parent_id']))
 {
@@ -185,34 +185,23 @@ else
 $query.= '
   ORDER BY rank ASC
 ;';
-$result = pwg_query($query);
-while ($row = mysql_fetch_array($result))
-{
-  $categories[$row['id']] = $row;
-  // by default, let's consider there is no sub-categories. This will be
-  // calculated after.
-  $categories[$row['id']]['nb_subcats'] = 0;
-}
+$categories = hash_from_query($query, 'id');
 
-if (count($categories) > 0)
+// get the categories containing images directly 
+$categories_with_images = array();
+if ( count($categories) )
 {
   $query = '
-SELECT id_uppercat, COUNT(*) AS nb_subcats
-  FROM '. CATEGORIES_TABLE.'
-  WHERE id_uppercat IN ('.implode(',', array_keys($categories)).')
-  GROUP BY id_uppercat
-;';
-  $result = pwg_query($query);
-  while ($row = mysql_fetch_array($result))
-  {
-    $categories[$row['id_uppercat']]['nb_subcats'] = $row['nb_subcats'];
-  }
+SELECT DISTINCT category_id 
+  FROM '.IMAGE_CATEGORY_TABLE.'
+  WHERE category_id IN ('.implode(',', array_keys($categories)).')';
+  $categories_with_images = array_flip( array_from_query($query, 'category_id') );
 }
 
 $template->assign('categories', array());
+$base_url = get_root_url().'admin.php?page=';
 foreach ($categories as $category)
 {
-  $base_url = PHPWG_ROOT_PATH.'admin.php?page=';
   $cat_list_url = $base_url.'cat_list';
 
   $self_url = $cat_list_url;
@@ -244,7 +233,7 @@ foreach ($categories as $category)
     $tpl_cat['U_DELETE'] = $self_url.'&delete='.$category['id'];
   }
 
-  if ($category['nb_images'] > 0)
+  if ( array_key_exists($category['id'], $categories_with_images) )
   {
     $tpl_cat['U_MANAGE_ELEMENTS']=
       $base_url.'element_set&cat='.$category['id'];
diff --git a/admin/cat_modify.php b/admin/cat_modify.php
index f2b16566b..90310527f 100644
--- a/admin/cat_modify.php
+++ b/admin/cat_modify.php
@@ -203,6 +203,12 @@ foreach (array('comment','dir','site_id', 'id_uppercat') as $nullable)
 
 $category['is_virtual'] = empty($category['dir']) ? true : false;
 
+$query = 'SELECT DISTINCT category_id
+  FROM '.IMAGE_CATEGORY_TABLE.'
+  WHERE category_id = '.$_GET['cat_id'].'
+  LIMIT 1';
+$result = pwg_query($query);
+$category['has_images'] = mysql_num_rows($result)>0 ? true : false;
 
 // Navigation path
 $navigation = get_cat_display_name_cache(
@@ -264,7 +270,7 @@ if ('private' == $category['status'])
 }
 
 // manage category elements link
-if ($category['nb_images'] > 0)
+if ($category['has_images'])
 {
   $template->assign( 'U_MANAGE_ELEMENTS',
         $base_url.'element_set&cat='.$category['id']
@@ -344,7 +350,7 @@ for ($i=0; $i<3; $i++) // 3 fields
 
 
 // representant management
-if ($category['nb_images'] > 0
+if ($category['has_images']
     or !empty($category['representative_picture_id']))
 {
   $tpl_representant = array();
@@ -371,14 +377,14 @@ SELECT id,tn_ext,path
   }
 
   // can the admin choose to set a new random representant ?
-  $tpl_representant['ALLOW_SET_RANDOM'] = ($category['nb_images']>0) ? true : false;
+  $tpl_representant['ALLOW_SET_RANDOM'] = ($category['has_images']) ? true : false;
 
   // can the admin delete the current representant ?
   if (
-    ($category['nb_images'] > 0
+    ($category['has_images']
      and $conf['allow_random_representative'])
     or
-    ($category['nb_images'] == 0
+    (!$category['has_images']
      and !empty($category['representative_picture_id'])))
   {
     $tpl_representant['ALLOW_DELETE'] = true;
diff --git a/admin/cat_options.php b/admin/cat_options.php
index 7f4b3f5e1..ca07d37a3 100644
--- a/admin/cat_options.php
+++ b/admin/cat_options.php
@@ -286,10 +286,9 @@ SELECT id,name,uppercats,global_rank
   WHERE representative_picture_id IS NOT NULL
 ;';
     $query_false = '
-SELECT id,name,uppercats,global_rank
-  FROM '.CATEGORIES_TABLE.'
-  WHERE nb_images != 0
-    AND representative_picture_id IS NULL
+SELECT DISTINCT id,name,uppercats,global_rank
+  FROM '.CATEGORIES_TABLE.' INNER JOIN '.IMAGE_CATEGORY_TABLE.' ON id=category_id
+  WHERE representative_picture_id IS NULL
 ;';
     $template->assign(
       array(
diff --git a/admin/include/functions.php b/admin/include/functions.php
index c6f72c5f4..94ac4c962 100644
--- a/admin/include/functions.php
+++ b/admin/include/functions.php
@@ -251,175 +251,72 @@ DELETE FROM '.USERS_TABLE.'
 }
 
 /**
- * updates calculated informations about a set of categories : date_last and
- * nb_images. It also verifies that the representative picture is really
- * linked to the category. Optionnaly recursive.
+ * Verifies that the representative picture really exists in the db and
+ * picks up a random represantive if possible and based on config.
  *
  * @param mixed category id
- * @param boolean recursive
  * @returns void
  */
-function update_category($ids = 'all', $recursive = false)
+function update_category($ids = 'all')
 {
   global $conf;
 
-  // retrieving all categories to update
-  $cat_ids = array();
-
-  $query = '
-SELECT id
-  FROM '.CATEGORIES_TABLE;
-  if (is_array($ids))
-  {
-    if ($recursive)
-    {
-      foreach ($ids as $num => $id)
-      {
-        if ($num == 0)
-        {
-          $query.= '
-  WHERE ';
-        }
-        else
-        {
-          $query.= '
-  OR    ';
-        }
-        $query.= 'uppercats REGEXP \'(^|,)'.$id.'(,|$)\'';
-      }
-    }
-    else
-    {
-      $query.= '
-  WHERE id IN ('.wordwrap(implode(', ', $ids), 80, "\n").')';
-    }
-  }
-  $query.= '
-;';
-  $cat_ids = array_unique(array_from_query($query, 'id'));
-
-  if (count($cat_ids) == 0)
+  if ($ids=='all')
   {
-    return false;
+    $where_cats = '1=1';
   }
-
-  // calculate informations about categories retrieved
-  $query = '
-SELECT category_id,
-       COUNT(image_id) AS nb_images,
-       MAX(date_available) AS date_last
-  FROM '.IMAGES_TABLE.' INNER JOIN '.IMAGE_CATEGORY_TABLE.' ON id = image_id
-  WHERE category_id IN ('.wordwrap(implode(', ', $cat_ids), 80, "\n").')
-  GROUP BY category_id
-;';
-  $result = pwg_query($query);
-  $datas = array();
-  $query_ids = array();
-  while ( $row = mysql_fetch_array( $result ) )
+  elseif ( !is_array($ids) )
   {
-    array_push($query_ids, $row['category_id']);
-
-    array_push(
-      $datas,
-      array(
-        'id'        => $row['category_id'],
-        'date_last' => $row['date_last'],
-        'nb_images' => $row['nb_images']
-        )
-      );
+    $where_cats = '%s='.$ids;
   }
-  // if all links between a category and elements have disappeared, no line
-  // is returned but the update must be done !
-  foreach (array_diff($cat_ids, $query_ids) as $id)
+  else
   {
-    array_push($datas, array('id' => $id, 'nb_images' => 0));
+    if (count($ids) == 0)
+    {
+      return false;
+    }
+    $where_cats = '%s IN('.wordwrap(implode(', ', $ids), 120, "\n").')';
   }
-
-  $fields = array('primary' => array('id'),
-                  'update'  => array('date_last', 'nb_images'));
-  mass_updates(CATEGORIES_TABLE, $fields, $datas);
-
-  // representative pictures
-  if (count($cat_ids) > 0)
-  {
-    // find all categories where the setted representative is not possible :
-    // the picture does not exist
-    $query = '
-SELECT c.id
+  
+  // find all categories where the setted representative is not possible :
+  // the picture does not exist
+  $query = '
+SELECT DISTINCT c.id
   FROM '.CATEGORIES_TABLE.' AS c LEFT JOIN '.IMAGES_TABLE.' AS i
     ON c.representative_picture_id = i.id
   WHERE representative_picture_id IS NOT NULL
-    AND c.id IN ('.wordwrap(implode(', ', $cat_ids), 80, "\n").')
+    AND '.sprintf($where_cats, 'c.id').'
     AND i.id IS NULL
 ;';
-    $wrong_representant = array_from_query($query, 'id');
-
-    if ($conf['allow_random_representative'])
-    {
-      if (count($wrong_representant) > 0)
-      {
-        $query = '
-UPDATE '.CATEGORIES_TABLE.'
-  SET representative_picture_id = NULL
-  WHERE id IN ('.wordwrap(implode(', ', $wrong_representant), 80, "\n").')
-;';
-        pwg_query($query);
-      }
-    }
-    else
-    {
-      $to_null = array();
-      $to_rand = array();
-
-      if (count($wrong_representant) > 0)
-      {
-        // among the categories with an unknown representant, we dissociate
-        // categories containing pictures and categories containing no
-        // pictures. Indeed, the representant must set to NULL if no picture
-        // in the category and set to a random picture otherwise.
-        $query = '
-SELECT id
-  FROM '.CATEGORIES_TABLE.'
-  WHERE id IN ('.wordwrap(implode(', ', $wrong_representant), 80, "\n").')
-    AND nb_images = 0
-;';
-        $to_null = array_from_query($query, 'id');
-        $to_rand = array_diff($wrong_representant, $to_null);
-      }
+  $wrong_representant = array_from_query($query, 'id');
 
-      if (count($to_null) > 0)
-      {
-        $query = '
+  if (count($wrong_representant) > 0)
+  {
+    $query = '
 UPDATE '.CATEGORIES_TABLE.'
   SET representative_picture_id = NULL
-  WHERE id IN ('.wordwrap(implode(', ', $to_null), 80, "\n").')
+  WHERE id IN ('.wordwrap(implode(', ', $wrong_representant), 120, "\n").')
 ;';
-        pwg_query($query);
-      }
+    pwg_query($query);
+  }
 
-      // If the random representant is not allowed, we need to find
-      // categories with elements and with no representant. Those categories
-      // must be added to the list of categories to set to a random
-      // representant.
-      $query = '
-SELECT id
-  FROM '.CATEGORIES_TABLE.'
+  if (!$conf['allow_random_representative'])
+  {
+    // If the random representant is not allowed, we need to find
+    // categories with elements and with no representant. Those categories
+    // must be added to the list of categories to set to a random
+    // representant.
+    $query = '
+SELECT DISTINCT id
+  FROM '.CATEGORIES_TABLE.' INNER JOIN '.IMAGE_CATEGORY_TABLE.'
+    ON id = category_id
   WHERE representative_picture_id IS NULL
-    AND nb_images != 0
-    AND id IN ('.wordwrap(implode(', ', $cat_ids), 80, "\n").')
+    AND '.sprintf($where_cats, 'category_id').'
 ;';
-      $to_rand =
-        array_unique(
-          array_merge(
-            $to_rand,
-            array_from_query($query, 'id')
-            )
-          );
-
-      if (count($to_rand) > 0)
-      {
-        set_random_representant($to_rand);
-      }
+    $to_rand = array_from_query($query, 'id');
+    if (count($to_rand) > 0)
+    {
+      set_random_representant($to_rand);
     }
   }
 }
diff --git a/admin/site_manager.php b/admin/site_manager.php
index abe9f811d..def9fe579 100644
--- a/admin/site_manager.php
+++ b/admin/site_manager.php
@@ -220,11 +220,18 @@ if ( is_file(PHPWG_ROOT_PATH.'listing.xml') )
 }
 
 $query = '
-SELECT s.*, COUNT(c.id) AS nb_categories, SUM(c.nb_images) AS nb_images
-  FROM '.SITES_TABLE.' AS s LEFT JOIN '.CATEGORIES_TABLE.' AS c
-  ON s.id=c.site_id
-  GROUP BY s.id'.
-';';
+SELECT c.site_id, COUNT(DISTINCT c.id) AS nb_categories, COUNT(i.id) AS nb_images
+  FROM '.CATEGORIES_TABLE.' AS c LEFT JOIN '.IMAGES_TABLE.' AS i
+  ON c.id=i.storage_category_id 
+  WHERE c.site_id IS NOT NULL
+  GROUP BY c.site_id
+;';
+$sites_detail = hash_from_query($query, 'site_id'); 
+
+$query = '
+SELECT *
+  FROM '.SITES_TABLE.'
+;';
 $result = pwg_query($query);
 
 while ($row = mysql_fetch_array($result))
@@ -243,8 +250,8 @@ while ($row = mysql_fetch_array($result))
     array(
       'NAME' => $row['galleries_url'],
       'TYPE' => l10n( $is_remote ? 'site_remote' : 'site_local' ),
-      'CATEGORIES' => $row['nb_categories'],
-      'IMAGES' => isset($row['nb_images']) ? $row['nb_images'] : 0,
+      'CATEGORIES' => (int)@$sites_detail[$row['id']]['nb_categories'],
+      'IMAGES' => (int)@$sites_detail[$row['id']]['nb_images'],
       'U_SYNCHRONIZE' => $update_url
      );
      
diff --git a/include/functions_filter.inc.php b/include/functions_filter.inc.php
index c52bb7517..24efb0994 100644
--- a/include/functions_filter.inc.php
+++ b/include/functions_filter.inc.php
@@ -48,7 +48,7 @@ function update_cats_with_filtered_data(&$cats)
 
   if ($filter['enabled'])
   {
-    $upd_fields = array('max_date_last', 'count_images', 'count_categories', 'nb_images');
+    $upd_fields = array('date_last', 'max_date_last', 'count_images', 'count_categories', 'nb_images');
 
     foreach ($cats as $cat_id => $category)
     {
diff --git a/include/functions_user.inc.php b/include/functions_user.inc.php
index cdf751979..6630e2dae 100644
--- a/include/functions_user.inc.php
+++ b/include/functions_user.inc.php
@@ -582,12 +582,10 @@ function compute_categories_data(&$cats)
  */
 function get_computed_categories($userdata, $filter_days=null)
 {
-  $group_by = '';
-
   $query = 'SELECT c.id cat_id, global_rank';
   // Count by date_available to avoid count null
   $query .= ',
-  MAX(date_available) cat_date_last,  COUNT(date_available) cat_nb_images
+  MAX(date_available) date_last, COUNT(date_available) nb_images
 FROM '.CATEGORIES_TABLE.' as c
   LEFT JOIN '.IMAGE_CATEGORY_TABLE.' AS ic ON ic.category_id = c.id
   LEFT JOIN '.IMAGES_TABLE.' AS i
@@ -598,7 +596,6 @@ FROM '.CATEGORIES_TABLE.' as c
   {
     $query .= ' AND i.date_available > SUBDATE(CURRENT_DATE,INTERVAL '.$filter_days.' DAY)';
   }
-  $group_by = 'c.id';
 
   if ( !empty($userdata['forbidden_categories']) )
   {
@@ -606,11 +603,8 @@ FROM '.CATEGORIES_TABLE.' as c
   WHERE c.id NOT IN ('.$userdata['forbidden_categories'].')';
   }
 
-  if ( !empty($group_by) )
-  {
-    $query.= '
-  GROUP BY '.$group_by;
-  }
+  $query.= '
+  GROUP BY c.id';
 
   $result = pwg_query($query);
 
@@ -619,8 +613,8 @@ FROM '.CATEGORIES_TABLE.' as c
   {
     $row['user_id'] = $userdata['id'];
     $row['count_categories'] = 0;
-    $row['count_images'] = $row['cat_nb_images'];
-    $row['max_date_last'] = $row['cat_date_last'];
+    $row['count_images'] = (int)$row['nb_images'];
+    $row['max_date_last'] = $row['date_last'];
 
     $cats += array($row['cat_id'] => $row);
   }
@@ -639,9 +633,7 @@ FROM '.CATEGORIES_TABLE.' as c
       {
         // Re-init counters
         $category['count_categories'] = 0;
-        $category['count_images'] = $category['cat_nb_images'];
-        // next line for update_cats_with_filtered_data
-        $category['nb_images'] = $category['cat_nb_images'];
+        $category['count_images'] = (int)$category['nb_images'];
         // Keep category
         $cats[$category['cat_id']] = $category;
       }
@@ -676,7 +668,7 @@ DELETE FROM '.USER_CACHE_CATEGORIES_TABLE.'
     array
     (
       'user_id', 'cat_id',
-      'max_date_last', 'count_images', 'count_categories'
+      'date_last', 'max_date_last', 'nb_images', 'count_images', 'count_categories'
     ),
     $cats
   );
diff --git a/install/db/69-database.php b/install/db/69-database.php
new file mode 100644
index 000000000..dad302401
--- /dev/null
+++ b/install/db/69-database.php
@@ -0,0 +1,58 @@
+<?php
+// +-----------------------------------------------------------------------+
+// | Piwigo - a PHP based picture gallery                                  |
+// +-----------------------------------------------------------------------+
+// | Copyright(C) 2008      Piwigo Team                  http://piwigo.org |
+// | Copyright(C) 2003-2008 PhpWebGallery Team    http://phpwebgallery.net |
+// | Copyright(C) 2002-2003 Pierrick LE GALL   http://le-gall.net/pierrick |
+// +-----------------------------------------------------------------------+
+// | This program is free software; you can redistribute it and/or modify  |
+// | it under the terms of the GNU General Public License as published by  |
+// | the Free Software Foundation                                          |
+// |                                                                       |
+// | This program is distributed in the hope that it will be useful, but   |
+// | WITHOUT ANY WARRANTY; without even the implied warranty of            |
+// | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU      |
+// | General Public License for more details.                              |
+// |                                                                       |
+// | You should have received a copy of the GNU General Public License     |
+// | along with this program; if not, write to the Free Software           |
+// | Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, |
+// | USA.                                                                  |
+// +-----------------------------------------------------------------------+
+
+if (!defined('PHPWG_ROOT_PATH'))
+{
+  die('Hacking attempt!');
+}
+
+$upgrade_description = 'Move #categories.date_last and nb_images to #user_cache_categories';
+
+include_once(PHPWG_ROOT_PATH.'include/constants.php');
+
+// +-----------------------------------------------------------------------+
+// |                            Upgrade content                            |
+// +-----------------------------------------------------------------------+
+
+$query = '
+ALTER TABLE '.USER_CACHE_CATEGORIES_TABLE.'
+  ADD COLUMN date_last datetime default NULL AFTER cat_id,
+  ADD COLUMN nb_images mediumint(8) unsigned NOT NULL default 0 AFTER max_date_last';
+pwg_query($query);
+
+$query = '
+ALTER TABLE '.CATEGORIES_TABLE.'
+  DROP COLUMN date_last,
+  DROP COLUMN nb_images
+  ';
+pwg_query($query);
+
+invalidate_user_cache(); // just to force recalculation
+
+echo
+"\n"
+.'"'.$upgrade_description.'"'.' ended'
+."\n"
+;
+
+?>
diff --git a/install/phpwebgallery_structure.sql b/install/phpwebgallery_structure.sql
index 01626af0c..452d9f7a7 100644
--- a/install/phpwebgallery_structure.sql
+++ b/install/phpwebgallery_structure.sql
@@ -22,8 +22,6 @@ CREATE TABLE `phpwebgallery_caddie` (
 DROP TABLE IF EXISTS `phpwebgallery_categories`;
 CREATE TABLE `phpwebgallery_categories` (
   `id` smallint(5) unsigned NOT NULL auto_increment,
-  `date_last` datetime default NULL,
-  `nb_images` mediumint(8) unsigned NOT NULL default '0',
   `name` varchar(255) NOT NULL default '',
   `id_uppercat` smallint(5) unsigned default NULL,
   `comment` text,
@@ -342,7 +340,9 @@ DROP TABLE IF EXISTS `phpwebgallery_user_cache_categories`;
 CREATE TABLE `phpwebgallery_user_cache_categories` (
   `user_id` smallint(5) NOT NULL default '0',
   `cat_id` smallint(5) unsigned NOT NULL default '0',
+  `date_last` datetime default NULL,
   `max_date_last` datetime default NULL,
+  `nb_images` mediumint(8) unsigned NOT NULL default '0',
   `count_images` mediumint(8) unsigned default '0',
   `count_categories` mediumint(8) unsigned default '0',
   PRIMARY KEY  (`user_id`,`cat_id`)
diff --git a/search.php b/search.php
index 36a93814a..fe161f476 100644
--- a/search.php
+++ b/search.php
@@ -173,7 +173,7 @@ $template->assign(
   array(
     'F_SEARCH_ACTION' => 'search.php',
     'U_HELP' => PHPWG_ROOT_PATH.'popuphelp.php?page=search',
-    
+
     'month_list' => $month_list,
     'START_DAY_SELECTED' => @$_POST['start_day'],
     'START_MONTH_SELECTED' => @$_POST['start_month'],
@@ -200,7 +200,7 @@ if (count($available_tags) > 0)
 
 //------------------------------------------------------------- categories form
 $query = '
-SELECT name,id,date_last,nb_images,global_rank,uppercats
+SELECT id,name,global_rank,uppercats
   FROM '.CATEGORIES_TABLE.'
 '.get_sql_condition_FandF
   (