new feature: source/destination links between categories. Will we keep this

feature? Code is complicated and very few people will understand how it
works...

modification: #images.storage_category_id replaced by
#image_category.is_storage

improvement: many code refactoring to improve readibility

improvement: virtual category creation code was moved to a dedicated
function in order to be called from admin/cat_list.php and
admin/cat_modify.php (create a new destination category)


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

1 files changed, 570 insertions, 20 deletions

diff --git a/admin/include/functions.php b/admin/include/functions.php
index 9c385092e..a1c13cd05 100644
--- a/admin/include/functions.php
+++ b/admin/include/functions.php
@@ -1,3 +1,4 @@
+
 <?php
 // +-----------------------------------------------------------------------+
 // | PhpWebGallery - a PHP based picture gallery                           |
@@ -152,16 +153,22 @@ function delete_categories($ids)
 
   // destruction of all the related elements
   $query = '
-SELECT id
-  FROM '.IMAGES_TABLE.'
-  WHERE storage_category_id IN (
-'.wordwrap(implode(', ', $ids), 80, "\n").')
+SELECT image_id
+  FROM '.IMAGE_CATEGORY_TABLE.'
+  WHERE is_storage = \'true\'
+    AND category_id IN ('.
+    wordwrap(
+      implode(', ', $ids),
+      80,
+      "\n"
+      ).
+    ')
 ;';
   $result = pwg_query($query);
   $element_ids = array();
   while ($row = mysql_fetch_array($result))
   {
-    array_push($element_ids, $row['id']);
+    array_push($element_ids, $row['image_id']);
   }
   delete_elements($element_ids);
 
@@ -180,6 +187,7 @@ DELETE FROM '.USER_ACCESS_TABLE.'
 '.wordwrap(implode(', ', $ids), 80, "\n").')
 ;';
   pwg_query($query);
+  
   $query = '
 DELETE FROM '.GROUP_ACCESS_TABLE.'
   WHERE cat_id IN (
@@ -187,6 +195,37 @@ DELETE FROM '.GROUP_ACCESS_TABLE.'
 ;';
   pwg_query($query);
 
+  // source/destination links deletion
+  $query = '
+SELECT destination, source
+  FROM '.CATEGORIES_LINK_TABLE.'
+  WHERE source IN ('.implode(',', $ids).')
+    OR destination IN ('.implode(',', $ids).')
+;';
+  $result = pwg_query($query);
+
+  $sources_of = array();
+  
+  while ($row = mysql_fetch_array($result))
+  {
+    if (!isset($sources_of[ $row['destination'] ]))
+    {
+      $sources_of[ $row['destination'] ] = array();
+    }
+
+    array_push(
+      $sources_of[ $row['destination'] ],
+      $row['source']
+      );
+  }
+
+  foreach ($sources_of as $destination => $sources)
+  {
+    delete_sources($destination, $sources);
+  }
+
+  update_category();
+
   // destruction of the category
   $query = '
 DELETE FROM '.CATEGORIES_TABLE.'
@@ -395,30 +434,48 @@ SELECT id
 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
+  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 ) )
+  while ($row = mysql_fetch_array($result))
   {
     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']));
+    
+    array_push(
+      $datas,
+      array(
+        'id'        => $row['category_id'],
+        'date_last' => $row['date_last'],
+        'nb_images' => $row['nb_images']
+        )
+      );
   }
   // 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)
   {
-    array_push($datas, array('id' => $id, 'nb_images' => 0));
+    array_push(
+      $datas,
+      array(
+        'id'        => $id,
+        'nb_images' => 0,
+        )
+      );
   }
 
-  $fields = array('primary' => array('id'),
-                  'update'  => array('date_last', 'nb_images'));
-  mass_updates(CATEGORIES_TABLE, $fields, $datas);
+  mass_updates(
+    CATEGORIES_TABLE,
+    array(
+      'primary' => array('id'),
+      'update'  => array('date_last', 'nb_images')
+      ),
+    $datas
+    );
 
   // representative pictures
   if (count($cat_ids) > 0)
@@ -1291,19 +1348,42 @@ SELECT id, id_uppercat
  */
 function update_path()
 {
+  $images_of = array();
+  
   $query = '
-SELECT DISTINCT(storage_category_id)
-  FROM '.IMAGES_TABLE.'
+SELECT category_id, image_id
+  FROM '.IMAGE_CATEGORY_TABLE.'
+  WHERE is_storage = \'true\'
 ;';
-  $cat_ids = array_from_query($query, 'storage_category_id');
-  $fulldirs = get_fulldirs($cat_ids);
+  $result = pwg_query($query);
+  while ($row = mysql_fetch_array($result))
+  {
+    if (!isset($images_of[ $row['category_id'] ]))
+    {
+      $images_of[ $row['category_id'] ] = array();
+    }
 
-  foreach ($cat_ids as $cat_id)
+    array_push(
+      $images_of[ $row['category_id'] ],
+      $row['image_id']
+      );
+  }
+  
+  $fulldirs = get_fulldirs(
+    array_keys($images_of)
+    );
+
+  foreach ($images_of as $cat_id => $image_ids)
   {
     $query = '
 UPDATE '.IMAGES_TABLE.'
   SET path = CONCAT(\''.$fulldirs[$cat_id].'\',\'/\',file)
-  WHERE storage_category_id = '.$cat_id.'
+  WHERE id IN ('.
+      wordwrap(
+        implode(', ', $image_ids),
+        80,
+        "\n").
+      ')
 ;';
     pwg_query($query);
   }
@@ -1522,4 +1602,474 @@ DELETE FROM '.$table.'
       )
     );
 }
+
+/**
+ * Returns all destinations of a list of source categories. This function
+ * solves transitivity.
+ *
+ * @param mixed array of category ids, empty for all categories
+ */
+function get_destinations($categories = 'all')
+{
+  $query = '
+SELECT source, destination
+  FROM '.CATEGORIES_LINK_TABLE.'
+';
+  $result = pwg_query($query);
+
+  $destinations_of = array();
+  
+  while ($row = mysql_fetch_array($result))
+  {
+    if (!isset($destinations_of[ $row['source'] ]))
+    {
+      $destinations_of[ $row['source'] ] = array();
+    }
+
+    array_push(
+      $destinations_of[ $row['source'] ],
+      $row['destination']
+      );
+  }
+
+  // transitivity resolution: if " => " means "source of", if A=>B=>C
+  // implies A=>B and A=>C. So A has 2 destinations: B and C.
+  do
+  {
+    // let's suppose we only need a single turn
+    $need_new_turn = false;
+    
+    foreach ($destinations_of as $source => $destinations)
+    {
+      foreach ($destinations as $destination)
+      {
+        // does the current destination has destinations itself?
+        if (isset($destinations_of[$destination]))
+        {
+          // are there destinations of current destination not already among
+          // destinations of the current source? (advise: take a piece of
+          // paper and draw a schema). The source itself must not be counted
+          // as a destination, thus avoiding cyclic links.
+          $missing_destinations = array_diff(
+            $destinations_of[$destination],
+            $destinations,
+            array($source) // no cyclic link
+            );
+          
+          if (count($missing_destinations) > 0)
+          {
+            $destinations_of[$source] = array_unique(
+              array_merge(
+                $destinations,
+                $missing_destinations
+                )
+              );
+
+            // a category has a least one new destination, we have to check
+            // one more time that it doesn't generate more destinations
+            $need_new_turn = true;
+          }
+        }
+      }
+    }
+  } while ($need_new_turn);
+
+  if (is_array($categories))
+  {
+    $filtered_destinations_of = array();
+
+    // Even if there is no destinations for the requested categories, we
+    // return empty arrays
+    foreach ($categories as $category)
+    {
+      $filtered_destinations_of[$category] = array();
+    }
+    
+    foreach ($destinations_of as $source => $destinations)
+    {
+      if (in_array($source, $categories))
+      {
+        $filtered_destinations_of[$source] = $destinations;
+      }
+    }
+
+    return $filtered_destinations_of;
+  }
+  else
+  {
+    return $destinations_of;
+  }
+}
+
+/**
+ * Returns all sources of a list of destination categories. This function
+ * solves transitivity.
+ *
+ * @param mixed array of category ids, empty for all categories
+ */
+function get_sources($categories = 'all')
+{
+  $destinations_of = get_destinations();
+
+  $sources_of = array();
+  
+  foreach ($destinations_of as $source => $destinations)
+  {
+    foreach ($destinations as $destination)
+    {
+      if (!isset($sources_of[$destination]))
+      {
+        $sources_of[$destination] = array();
+      }
+
+      array_push($sources_of[$destination], $source);
+    }
+  }
+  
+  // eventually, filter
+  if (is_array($categories))
+  {
+    $filtered_sources_of = array();
+
+    // Even if there is no sources for the requested categories, we return
+    // empty arrays
+    foreach ($categories as $category)
+    {
+      $filtered_sources_of[$category] = array();
+    }
+    
+    foreach ($sources_of as $destination => $sources)
+    {
+      if (in_array($destination, $categories))
+      {
+        $filtered_sources_of[$destination] = $sources;
+      }
+    }
+
+    return $filtered_sources_of;
+  }
+  else
+  {
+    return $sources_of;
+  }
+}
+
+/**
+ * Checks categories links are respected for a given list of destinations.
+ *
+ * Checking categories links means that each destination must be associated
+ * to the images of its sources.
+ *
+ * @param mixed source category ids
+ */
+function check_links($destinations = 'all')
+{
+  $sources_of = get_sources($destinations);
+
+  if (empty($sources_of))
+  {
+    return true;
+  }
+
+  // we need to search images of all sources and destinations
+  $images_of = array();
+
+  foreach ($sources_of as $destination => $sources)
+  {
+    $images_of[$destination] = array();
+
+    foreach ($sources as $source)
+    {
+      $images_of[$source] = array();
+    }
+  }
+  
+  $query = '
+SELECT image_id, category_id
+  FROM '.IMAGE_CATEGORY_TABLE.'
+  WHERE category_id IN ('.
+    implode(',', array_keys($images_of)).
+    ')
+;';
+  $result = pwg_query($query);
+
+  while ($row = mysql_fetch_array($result))
+  {
+    array_push(
+      $images_of[ $row['category_id'] ],
+      $row['image_id']
+      );
+  }
+
+  $inserts = array();
+  
+  foreach ($sources_of as $destination => $sources)
+  {
+    // merge all images from the sources of this destination
+    $sources_images = array();
+    
+    foreach ($sources as $source)
+    {
+      $sources_images = array_merge(
+        $sources_images,
+        $images_of[$source]
+        );
+    }
+
+    $sources_images = array_unique($sources_images);
+
+    // are there images among the sources that are not linked to the
+    // destination?
+    $missing_images = array_diff(
+      $sources_images,
+      $images_of[$destination]
+      );
+
+    // if we find missing images (missing links in reality), we prepare the
+    // final mass_inserts
+    if (count($missing_images) > 0)
+    {
+      foreach ($missing_images as $missing_image)
+      {
+        array_push(
+          $inserts,
+          array(
+            'category_id' => $destination,
+            'image_id'    => $missing_image,
+            )
+          );
+      }
+    }
+  }
+
+  if (count($inserts) > 0)
+  {
+    mass_inserts(
+      IMAGE_CATEGORY_TABLE,
+      array_keys($inserts[0]),
+      $inserts
+      );
+  }
+}
+
+/**
+ * Based on categories links, delete image_category links on destinations.
+ *
+ * The rule is the following: if an image belong to the category and to the
+ * source, we suppose it comes from the source. If the source/destination
+ * link is broken, we delete the image/category link if the only origin of
+ * the link was the broken categories link.
+ *
+ * Example: "=>" means "source of". Between brackets the associated images.
+ *
+ * A (1,2,9) => \
+ *               |=> C (1,2,3,4,5,9) => D (1,2,3,4,5,6,9)
+ * B (3,4,9) => /
+ *
+ * In category C, we suppose (1,2) come from A, (3,4) from B, 9 from A or B
+ * and 5 was manually added. In category D, 6 was added manually.
+ *
+ * If we break A=>C, C and D loose (1,2) but not 9 because it can come from
+ * B. If we break C=>D, D loose (3,4,5,9) but not 6 because it was
+ * associated manually to 9.
+ *
+ * Warning: only virtual links can be removed, physical links are protected.
+ * 
+ * @param int destination
+ * @param array sources
+ */
+function delete_sources($destination, $sources)
+{
+  // if no sources to unlink, we stop with OK status
+  if (count($sources) == 0)
+  {
+    return true;
+  }
+
+  $query = '
+DELETE
+  FROM '.CATEGORIES_LINK_TABLE.'
+  WHERE destination = '.$destination.'
+    AND source IN ('.implode(',', $sources).')
+;';
+  pwg_query($query);
+  
+  // The strategy is the following:
+  //
+  // * first we brutally delete the image/category associations on
+  // destinations categories for all images belonging to sources.
+  //
+  // * then we check_links on destinations to rebuild missing image/category
+  // associations.
+
+  // what are the images associated to the sources to unlink
+  $query = '
+SELECT image_id
+  FROM '.IMAGE_CATEGORY_TABLE.'
+  WHERE category_id IN ('.
+    implode(',', $sources).
+    ')
+;';
+  $sources_images = array_unique(
+    array_from_query($query, 'image_id')
+    );
+
+  if (count($sources_images) == 0)
+  {
+    return true;
+  }
+
+  // retrieve all direct and indirect destinations of the current
+  // destination
+  $destinations_of = get_destinations(array($destination));
+
+  $destinations = array_merge(
+    array($destination),
+    $destinations_of[$destination]
+    );
+  
+  // unlink sources images from destinations
+  $query = '
+DELETE
+  FROM '.IMAGE_CATEGORY_TABLE.'
+  WHERE category_id IN ('.implode(',', $destinations).')
+    AND image_id IN ('.implode(',', $sources_images).')
+    AND is_storage = \'false\'
+;';
+  pwg_query($query);
+
+  // if the representative thumbnail of destinations was a picture from
+  // $sources_images, we request a new random representant
+  $query = '
+SELECT id, representative_picture_id
+  FROM '.CATEGORIES_TABLE.'
+  WHERE id IN ('.implode(',', $destinations).')
+;';
+  $result = pwg_query($query);
+
+  $request_random = array();
+  
+  while ($row = mysql_fetch_array($result))
+  {
+    if (isset($row['representative_picture_id']))
+    {
+      if (in_array($row['representative_picture_id'], $sources_images))
+      {
+        array_push($request_random, $row['id']);
+      }
+    }
+  }
+
+  set_random_representant($request_random);
+
+  // eventually, we check_links to rebuild missing associations
+  check_links($destinations);
+
+  return true;
+}
+
+/**
+ * create a virtual category
+ *
+ * @param string category name
+ * @param int parent category id
+ * @return array with ('info' and 'id') or ('error') key
+ */
+function create_virtual_category($category_name, $parent_id=null)
+{
+  global $conf;
+  
+  // is the given category name only containing blank spaces ?
+  if (preg_match('/^\s*$/', $category_name))
+  {
+    return array('error' => l10n('cat_error_name'));
+  }
+	
+  $parent_id = !empty($parent_id) ? $parent_id : 'NULL';
+
+  $query = '
+SELECT MAX(rank)
+  FROM '.CATEGORIES_TABLE.'
+  WHERE id_uppercat '.(is_numeric($parent_id) ? '= '.$parent_id : 'IS NULL').'
+;';
+  list($current_rank) = mysql_fetch_array(pwg_query($query));
+  
+  $insert = array(
+    'name' => $category_name,
+    'rank' => ++$current_rank,
+    'commentable' => $conf['newcat_default_commentable'],
+    'uploadable' => 'false',
+    );
+    
+  if ($parent_id != 'NULL')
+  {
+    $query = '
+SELECT id, uppercats, global_rank, visible, status
+  FROM '.CATEGORIES_TABLE.'
+  WHERE id = '.$parent_id.'
+;';
+    $parent = mysql_fetch_array(pwg_query($query));
+
+    $insert{'id_uppercat'} = $parent{'id'};
+    $insert{'global_rank'} = $parent{'global_rank'}.'.'.$insert{'rank'};
+    
+    // at creation, must a category be visible or not ? Warning : if the
+    // parent category is invisible, the category is automatically create
+    // invisible. (invisible = locked)
+    if ('false' == $parent['visible'])
+    {
+      $insert{'visible'} = 'false';
+    }
+    else
+    {
+      $insert{'visible'} = $conf['newcat_default_visible'];
+    }
+    
+    // at creation, must a category be public or private ? Warning : if the
+    // parent category is private, the category is automatically create
+    // private.
+    if ('private' == $parent['status'])
+    {
+      $insert{'status'} = 'private';
+    }
+    else
+    {
+      $insert{'status'} = $conf['newcat_default_status'];
+    }
+  }
+  else
+  {
+    $insert{'visible'} = $conf['newcat_default_visible'];
+    $insert{'status'} = $conf['newcat_default_status'];
+    $insert{'global_rank'} = $insert{'rank'};
+  }
+
+  // we have then to add the virtual category
+  mass_inserts(
+    CATEGORIES_TABLE,
+    array(
+      'site_id', 'name', 'id_uppercat', 'rank', 'commentable',
+      'uploadable', 'visible', 'status', 'global_rank',
+      ),
+    array($insert)
+    );
+
+  $inserted_id = mysql_insert_id();
+
+  $query = '
+UPDATE
+  '.CATEGORIES_TABLE.'
+  SET uppercats = \''.
+    (isset($parent) ? $parent{'uppercats'}.',' : '').
+    $inserted_id.
+    '\'
+  WHERE id = '.$inserted_id.'
+;';
+  pwg_query($query);
+  
+  return array(
+    'info' => l10n('cat_virtual_added'),
+    'id'   => $inserted_id,
+    );
+}
 ?>