- update_global_rank new function : far more intelligent update. Take into

  account the possiblity to have category tree not in category id ascending
  order

- update global_rank when moving categories among the same parent

- new function mass_updates : depending on MySQL version, create a temporary
  table, make one big insert and one big update by joining 2 tables (4.0.4
  or above) or make 1 update per primary key

- function update_category improved for representative_picture_id check :
  only one useful query (equivalent to NOT EXISTS) instead of N (N = number
  of categories) queries


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

4 files changed, 218 insertions, 201 deletions

diff --git a/admin/cat_list.php b/admin/cat_list.php
index eac3869b3..b5404ea0c 100644
--- a/admin/cat_list.php
+++ b/admin/cat_list.php
@@ -208,6 +208,7 @@ UPDATE '.CATEGORIES_TABLE.'
     array_push($categories, $current);
     array_shift($categories);
   }
+  update_global_rank($_GET['parent_id']);
 }
 else if (isset($_GET['down']) and is_numeric($_GET['down']))
 {
@@ -267,6 +268,7 @@ UPDATE '.CATEGORIES_TABLE.'
     array_unshift($categories, $current);
     array_pop($categories);
   }
+  update_global_rank($_GET['parent_id']);
 }
 reset($categories);
 // +-----------------------------------------------------------------------+
diff --git a/admin/include/functions.php b/admin/include/functions.php
index c78df307e..9df0303c0 100644
--- a/admin/include/functions.php
+++ b/admin/include/functions.php
@@ -466,66 +466,59 @@ SELECT category_id, COUNT(image_id) AS count, max(date_available) AS date_last
   GROUP BY category_id
 ;';
   $result = pwg_query( $query );
+  $datas = array();
   while ( $row = mysql_fetch_array( $result ) )
   {
     array_push($cat_ids, $row['category_id']);
-    $query = '
-UPDATE '.CATEGORIES_TABLE.'
-  SET date_last = \''.$row['date_last'].'\'
-    , nb_images = '.$row['count'].'
-  WHERE id = '.$row['category_id'].'
-;';
-    pwg_query($query);
+    array_push($datas, array('id' => $row['category_id'],
+                             'date_last' => $row['date_last'],
+                             'count' => $row['count']));
   }
+  $fields = array('primary' => array('id'),
+                  'update'  => array('date_last', 'count'));
+  mass_updates(CATEGORIES_TABLE, $fields, $datas);
 
   if (count($cat_ids) > 0)
   {
+    // find all categories where the setted representative is not possible
     $query = '
-SELECT id, representative_picture_id
-  FROM '.CATEGORIES_TABLE.'
+SELECT id
+  FROM '.CATEGORIES_TABLE.' LEFT JOIN '.IMAGE_CATEGORY_TABLE.'
+    ON id = category_id AND representative_picture_id = image_id
   WHERE representative_picture_id IS NOT NULL
     AND id IN ('.implode(',', $cat_ids).')
+    AND category_id IS NULL
 ;';
     $result = pwg_query($query);
     while ($row = mysql_fetch_array($result))
     {
+      // set a new representative element for this category
       $query = '
 SELECT image_id
   FROM '.IMAGE_CATEGORY_TABLE.'
   WHERE category_id = '.$row['id'].'
-    AND image_id = '.$row['representative_picture_id'].'
+  ORDER BY RAND()
+  LIMIT 0,1
 ;';
       $sub_result = pwg_query($query);
-      if (mysql_num_rows($sub_result) == 0)
+      if (mysql_num_rows($sub_result) > 0)
       {
-        // set a new representative element for this category
+        list($representative) = mysql_fetch_array($sub_result);
         $query = '
-SELECT image_id
-  FROM '.IMAGE_CATEGORY_TABLE.'
-  WHERE category_id = '.$row['id'].'
-  ORDER BY RAND()
-  LIMIT 0,1
-;';
-        $sub_sub_result = pwg_query($query);
-        if (mysql_num_rows($sub_sub_result) > 0)
-        {
-          list($representative) = mysql_fetch_array(pwg_query($query));
-          $query = '
 UPDATE '.CATEGORIES_TABLE.'
   SET representative_picture_id = '.$representative.'
   WHERE id = '.$row['id'].'
 ;';
-          pwg_query($query);
-        }
-        else
-        {
-          $query = '
+        pwg_query($query);
+      }
+      else
+      {
+        $query = '
 UPDATE '.CATEGORIES_TABLE.'
   SET representative_picture_id = NULL
   WHERE id = '.$row['id'].'
 ;';
-          pwg_query($query);
-        }
+        pwg_query($query);
       }
     }
   }
@@ -790,18 +783,18 @@ function my_error($header, $echo = true)
  * inserts multiple lines in a table
  *
  * @param string table_name
- * @param array dbields
+ * @param array dbfields
  * @param array inserts
  * @return void
  */
-function mass_inserts($table_name, $dbfields, $inserts)
+function mass_inserts($table_name, $dbfields, $datas)
 {
   // inserts all found categories
   $query = '
 INSERT INTO '.$table_name.'
   ('.implode(',', $dbfields).')
    VALUES';
-  foreach ($inserts as $insert_id => $insert)
+  foreach ($datas as $insert_id => $insert)
   {
     $query.= '
   ';
@@ -832,4 +825,170 @@ INSERT INTO '.$table_name.'
 ;';
   pwg_query($query);
 }
+
+/**
+ * updates multiple lines in a table
+ *
+ * @param string table_name
+ * @param array dbfields
+ * @param array datas
+ * @return void
+ */
+function mass_updates($tablename, $dbfields, $datas)
+{
+  // depending on the MySQL version, we use the multi table update or N
+  // update queries
+  $query = 'SELECT VERSION() AS version;';
+  $row = mysql_fetch_array(pwg_query($query));
+  if (version_compare($row['version'],'4.0.4') < 0)
+  {
+    // MySQL is prior to version 4.0.4, multi table update feature is not
+    // available
+    echo 'MySQL is prior to version 4.0.4, multi table update feature is not available<br />';
+    foreach ($datas as $data)
+    {
+      $query = '
+UPDATE '.$tablename.'
+  SET ';
+      foreach ($dbfields['update'] as $num => $key)
+      {
+        if ($num >= 1)
+        {
+          $query.= ",\n      ";
+        }
+        $query.= $key.' = ';
+        if (isset($data[$key]))
+        {
+          $query.= '\''.$data[$key].'\'';
+        }
+        else
+        {
+          $query.= 'NULL';
+        }
+      }
+      $query.= '
+  WHERE ';
+      foreach ($dbfields['primary'] as $num => $key)
+      {
+        if ($num > 1)
+        {
+          $query.= ' AND ';
+        }
+        $query.= $key.' = \''.$data[$key].'\'';
+      }
+      $query.= '
+;';
+      pwg_query($query);
+    }
+  }
+  else
+  {
+    // creation of the temporary table
+    $query = '
+DESCRIBE '.$tablename.'
+;';
+    $result = pwg_query($query);
+    $columns = array();
+    $all_fields = array_merge($dbfields['primary'], $dbfields['update']);
+    while ($row = mysql_fetch_array($result))
+    {
+      if (in_array($row['Field'], $all_fields))
+      {
+        $column = $row['Field'];
+        $column.= ' '.$row['Type'];
+        if (!isset($row['Null']) or $row['Null'] == '')
+        {
+          $column.= ' NOT NULL';
+        }
+        if (isset($row['Default']))
+        {
+          $column.= " default '".$row['Default']."'";
+        }
+        array_push($columns, $column);
+      }
+    }
+    $query = '
+CREATE TEMPORARY TABLE '.$tablename.'_temporary
+(
+'.implode(",\n", $columns).',
+PRIMARY KEY (id)
+)
+;';
+    pwg_query($query);
+    mass_inserts($tablename, $all_fields, $datas);
+    // update of images table by joining with temporary table
+    $query = '
+UPDATE '.$tablename.' AS t1, '.$tablename.'_temporary AS t2
+  SET '.implode("\n    , ",
+                array_map(
+                  create_function('$s', 'return "t1.$s = t2.$s";')
+                  , $dbfields['update'])).'
+  WHERE '.implode("\n    AND ",
+                array_map(
+                  create_function('$s', 'return "t1.$s = t2.$s";')
+                  , $dbfields['primary'])).'
+;';
+    pwg_query($query);
+    $query = '
+DROP TABLE '.$tablename.'_temporary
+;';
+    pwg_query($query);
+  }
+}
+
+/**
+ * updates the global_rank of categories under the given id_uppercat
+ *
+ * @param int id_uppercat
+ * @return void
+ */
+function update_global_rank($id_uppercat = 'all')
+{
+  $query = '
+SELECT id,rank
+  FROM '.CATEGORIES_TABLE.'
+;';
+  $result = pwg_query($query);
+  $ranks_array = array();
+  while ($row = mysql_fetch_array($result))
+  {
+    $ranks_array[$row['id']] = $row['rank'];
+  }
+
+  // which categories to update ?
+  $uppercats_array = array();
+
+  $query = '
+SELECT id,uppercats
+  FROM '.CATEGORIES_TABLE;
+  if (is_numeric($id_uppercat))
+  {
+    $query.= '
+  WHERE uppercats REGEXP \'(^|,)'.$id_uppercat.'(,|$)\'
+    AND id != '.$id_uppercat.'
+';
+  }
+  $query.= '
+;';
+  $result = pwg_query($query);
+  while ($row = mysql_fetch_array($result))
+  {
+    $uppercats_array[$row['id']] =  $row['uppercats'];
+  }
+  
+  $datas = array();
+  foreach ($uppercats_array as $id => $uppercats)
+  {
+    $data = array();
+    $data['id'] = $id;
+    $global_rank = preg_replace('/(\d+)/e',
+                                "\$ranks_array['$1']",
+                                str_replace(',', '.', $uppercats));
+    $data['global_rank'] = $global_rank;
+    array_push($datas, $data);
+  }
+
+  $fields = array('primary' => array('id'), 'update' => array('global_rank'));
+  mass_updates(CATEGORIES_TABLE, $fields, $datas);
+}
 ?>
diff --git a/admin/include/functions_metadata.php b/admin/include/functions_metadata.php
index 2e66606d2..023ab219e 100644
--- a/admin/include/functions_metadata.php
+++ b/admin/include/functions_metadata.php
@@ -68,18 +68,18 @@ function update_metadata($files)
     define('CURRENT_DATE', date('Y-m-d'));
   }
 
-  $inserts = array();
+  $datas = array();
 
   foreach ($files as $id => $file)
   {
-    $insert = array();
-    $insert['id'] = $id;
-    $insert['filesize'] = floor(filesize($file)/1024);
+    $data = array();
+    $data['id'] = $id;
+    $data['filesize'] = floor(filesize($file)/1024);
   
     if ($image_size = @getimagesize($file))
     {
-      $insert['width'] = $image_size[0];
-      $insert['height'] = $image_size[1];
+      $data['width'] = $image_size[0];
+      $data['height'] = $image_size[1];
     }
   
     if ($conf['use_exif'])
@@ -88,11 +88,8 @@ function update_metadata($files)
       {
         if (isset($exif['DateTime']))
         {
-          preg_match('/^(\d{4}).(\d{2}).(\d{2})/'
-                     ,$exif['DateTime']
-                     ,$matches);
-          $insert['date_creation'] =
-            "'".$matches[1].'-'.$matches[2].'-'.$matches[3]."'";
+          preg_match('/^(\d{4}).(\d{2}).(\d{2})/',$exif['DateTime'],$matches);
+          $data['date_creation'] = $matches[1].'-'.$matches[2].'-'.$matches[3];
         }
       }
     }
@@ -104,135 +101,23 @@ function update_metadata($files)
       {
         foreach (array_keys($iptc) as $key)
         {
-          $insert[$key] = "'".addslashes($iptc[$key])."'";
+          $data[$key] = "'".addslashes($iptc[$key])."'";
         }
       }
     }
 
-    $insert['date_metadata_update'] = "'".CURRENT_DATE."'";
+    $data['date_metadata_update'] = CURRENT_DATE;
 
-    array_push($inserts, $insert);
+    array_push($datas, $data);
   }
   
-  if (count($inserts) > 0)
+  if (count($datas) > 0)
   {
-    $dbfields = array(
-      'id','filesize','width','height','name','author','comment'
-      ,'date_creation','keywords','date_metadata_update'
-      );
-
-    // depending on the MySQL version, we use the multi table update or N
-    // update queries
-    $query = 'SELECT VERSION() AS version;';
-    $row = mysql_fetch_array(pwg_query($query));
-    if (version_compare($row['version'],'4.0.4') < 0)
-    {
-      // MySQL is prior to version 4.0.4, multi table update feature is not
-      // available
-      echo 'MySQL is prior to version 4.0.4, multi table update feature is not available<br />';
-      foreach ($inserts as $insert)
-      {
-        $query = '
-UPDATE '.IMAGES_TABLE.'
-  SET ';
-        foreach (array_diff(array_keys($insert),array('id')) as $num => $key)
-        {
-          if ($num > 1)
-          {
-            $query.= ', ';
-          }
-          $query.= $key.' = '.$insert[$key];
-        }
-        $query.= '
-  WHERE id = '.$insert['id'].'
-;';
-        // echo '<pre>'.$query.'</pre>';
-        pwg_query($query);
-      }
-    }
-    else
-    {
-      // creation of the temporary table
-      $query = '
-DESCRIBE '.IMAGES_TABLE.'
-;';
-      $result = pwg_query($query);
-      $columns = array();
-      while ($row = mysql_fetch_array($result))
-      {
-        if (in_array($row['Field'], $dbfields))
-        {
-          $column = $row['Field'];
-          $column.= ' '.$row['Type'];
-          if (!isset($row['Null']) or $row['Null'] == '')
-          {
-            $column.= ' NOT NULL';
-          }
-          if (isset($row['Default']))
-          {
-            $column.= " default '".$row['Default']."'";
-          }
-          array_push($columns, $column);
-        }
-      }
-      $query = '
-CREATE TEMPORARY TABLE '.IMAGE_METADATA_TABLE.'
-(
-'.implode(",\n", $columns).',
-PRIMARY KEY (id)
-)
-;';
-      // echo '<pre>'.$query.'</pre>';
-      pwg_query($query);
-      // inserts all found pictures
-      $query = '
-INSERT INTO '.IMAGE_METADATA_TABLE.'
-  ('.implode(',', $dbfields).')
-   VALUES
-   ';
-      foreach ($inserts as $insert_id => $insert)
-      {
-        $query.= '
-';
-        if ($insert_id > 0)
-        {
-          $query.= ',';
-        }
-        $query.= '(';
-        foreach ($dbfields as $field_id => $dbfield)
-        {
-          if ($field_id > 0)
-          {
-            $query.= ',';
-          }
-          
-          if (!isset($insert[$dbfield]) or $insert[$dbfield] == '')
-          {
-            $query.= 'NULL';
-          }
-          else
-          {
-            $query.= $insert[$dbfield];
-          }
-        }
-        $query.=')';
-      }
-      $query.= '
-;';
-      // echo '<pre>'.$query.'</pre>';
-      pwg_query($query);
-      // update of images table by joining with temporary table
-      $query = '
-UPDATE '.IMAGES_TABLE.' AS images, '.IMAGE_METADATA_TABLE.' as metadata
-  SET '.implode("\n    , ",
-                array_map(
-                  create_function('$s', 'return "images.$s = metadata.$s";')
-                  , array_diff($dbfields, array('id')))).'
-  WHERE images.id = metadata.id
-;';
-      echo '<pre>'.$query.'</pre>';
-      pwg_query($query);
-    }
+    $fields = array('primary' => array('id'),
+                    'update'  => array('filesize','width','height','name',
+                                       'author','comment','date_creation',
+                                       'keywords','date_metadata_update'));
+    mass_updates(IMAGES_TABLE, $fields, $datas);
   }
 }
 
diff --git a/admin/update.php b/admin/update.php
index ac364e465..45bef3bce 100644
--- a/admin/update.php
+++ b/admin/update.php
@@ -42,8 +42,7 @@ define('CURRENT_DATE', date('Y-m-d'));
  * the purpose of this function is to give a rank for all categories
  * (insides its sub-category), even the newer that have none at te
  * beginning. For this, ordering function selects all categories ordered by
- * rank ASC then name ASC for each uppercat. It also updates the global rank
- * which is able to order any two categorie in the whole tree
+ * rank ASC then name ASC for each uppercat.
  *
  * @returns void
  */
@@ -58,6 +57,7 @@ SELECT id, if(id_uppercat is null,\'\',id_uppercat) AS id_uppercat
   ORDER BY id_uppercat,rank,name
 ;';
   $result = pwg_query($query);
+  $datas = array();
   while ($row = mysql_fetch_array($result))
   {
     if ($row['id_uppercat'] != $current_uppercat)
@@ -65,42 +65,12 @@ SELECT id, if(id_uppercat is null,\'\',id_uppercat) AS id_uppercat
       $current_rank = 0;
       $current_uppercat = $row['id_uppercat'];
     }
-    $query = '
-UPDATE '.CATEGORIES_TABLE.'
-  SET rank = '.++$current_rank.'
-  WHERE id = '.$row['id'].'
-;';
-    pwg_query($query);
+    $data = array('id' => $row['id'], 'rank' => ++$current_rank);
+    array_push($datas, $data);
   }
-  // global rank update
-  $query = '
-UPDATE '.CATEGORIES_TABLE.'
-  SET global_rank = rank
-  WHERE id_uppercat IS NULL
-;';
-  pwg_query($query);
 
-  $query = '
-SELECT DISTINCT(id_uppercat)
-  FROM '.CATEGORIES_TABLE.'
-  WHERE id_uppercat IS NOT NULL
-;';
-  $result = pwg_query($query);
-  while ($row = mysql_fetch_array($result))
-  {
-    $query = '
-SELECT global_rank
-  FROM '.CATEGORIES_TABLE.'
-  WHERE id = '.$row['id_uppercat'].'
-;';
-    list($uppercat_global_rank) = mysql_fetch_array(pwg_query($query));
-    $query = '
-UPDATE '.CATEGORIES_TABLE.'
-  SET global_rank = CONCAT(\''.$uppercat_global_rank.'\', \'.\', rank)
-  WHERE id_uppercat = '.$row['id_uppercat'].'
-;';
-    pwg_query($query);
-  }
+  $fields = array('primary' => array('id'), 'update' => array('rank'));
+  mass_updates(CATEGORIES_TABLE, $fields, $datas);
 }
 
 function insert_local_category($id_uppercat)
@@ -650,6 +620,7 @@ else if (isset($_POST['submit'])
   echo get_elapsed_time($start,get_moment()).' for update_category(all)<br />';
   $start = get_moment();
   ordering();
+  update_global_rank();
   echo get_elapsed_time($start, get_moment()).' for ordering categories<br />';
 }
 // +-----------------------------------------------------------------------+