From c08fa6f67ef5b149fdeb3bcc57045e629df8fff4 Mon Sep 17 00:00:00 2001 From: plegall Date: Sat, 4 Mar 2006 23:31:46 +0000 Subject: 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 --- admin/include/functions.php | 590 +++++++++++++++++++++++++++++++++-- admin/include/functions_metadata.php | 4 +- 2 files changed, 573 insertions(+), 21 deletions(-) (limited to 'admin/include') 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 @@ + $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, + ); +} ?> diff --git a/admin/include/functions_metadata.php b/admin/include/functions_metadata.php index 23dca79a9..337019b2e 100644 --- a/admin/include/functions_metadata.php +++ b/admin/include/functions_metadata.php @@ -224,7 +224,9 @@ SELECT id $query = ' SELECT id, path FROM '.IMAGES_TABLE.' - WHERE storage_category_id IN ('.implode(',', $cat_ids).')'; + INNER JOIN '.IMAGE_CATEGORY_TABLE.' ON image_id = id + WHERE is_storage = \'true\' + AND category_id IN ('.implode(',', $cat_ids).')'; if ($only_new) { $query.= ' -- cgit v1.2.3