From dfb0b9d1faa0603393ddbcb2831da52ba76ea3e9 Mon Sep 17 00:00:00 2001 From: plegall Date: Thu, 20 Jan 2011 13:32:34 +0000 Subject: bug 937 fixed: makes sure a user won't see the thumbnail of a photo that has a higher privacy level than user privacy level. For an acceptable solution at performance level, I have implemented a cache: for a given user, each album has a representative_picture_id. This cache also avoids to perform numerous "order by rand()" SQL queries which is the case when $conf['allow_random_representative'] = true; git-svn-id: http://piwigo.org/svn/trunk@8802 68402e56-0260-453c-a942-63ccdbb3a9ee --- admin/include/functions.php | 11 -- include/category_cats.inc.php | 198 +++++++++++++++++++++----------- include/functions.inc.php | 11 ++ include/functions_category.inc.php | 38 ++++++ install/db/95-database.php | 53 +++++++++ install/piwigo_structure-mysql.sql | 1 + install/piwigo_structure-pdo-sqlite.sql | 1 + install/piwigo_structure-pgsql.sql | 1 + install/piwigo_structure-sqlite.sql | 2 + picture.php | 8 ++ 10 files changed, 246 insertions(+), 78 deletions(-) create mode 100644 install/db/95-database.php diff --git a/admin/include/functions.php b/admin/include/functions.php index 91931cf4d..77879b501 100644 --- a/admin/include/functions.php +++ b/admin/include/functions.php @@ -882,17 +882,6 @@ function get_fs($path, $recursive = true) return $fs; } -/** - * stupidly returns the current microsecond since Unix epoch - */ -function micro_seconds() -{ - $t1 = explode(' ', microtime()); - $t2 = explode('.', $t1[0]); - $t2 = $t1[1].substr($t2[1], 0, 6); - return $t2; -} - /** * synchronize base users list and related users list * diff --git a/include/category_cats.inc.php b/include/category_cats.inc.php index ce67777bb..6ca1817db 100644 --- a/include/category_cats.inc.php +++ b/include/category_cats.inc.php @@ -27,90 +27,68 @@ * */ -if ($page['section']=='recent_cats') -{ - // $user['forbidden_categories'] including with USER_CACHE_CATEGORIES_TABLE - $query = ' +// $user['forbidden_categories'] including with USER_CACHE_CATEGORIES_TABLE +$query = ' SELECT - c.*, nb_images, date_last, max_date_last, count_images, count_categories - FROM '.CATEGORIES_TABLE.' c INNER JOIN '.USER_CACHE_CATEGORIES_TABLE.' - ON id = cat_id and user_id = '.$user['id'].' - WHERE date_last >= '.pwg_db_get_recent_period_expression($user['recent_period']).' -'.get_sql_condition_FandF - ( - array - ( - 'visible_categories' => 'id', - ), - 'AND' - ).' -;'; + c.*, + user_representative_picture_id, + nb_images, + date_last, + max_date_last, + count_images, + count_categories + FROM '.CATEGORIES_TABLE.' c + INNER JOIN '.USER_CACHE_CATEGORIES_TABLE.' ucc ON id = cat_id AND user_id = '.$user['id']; + +if ('recent_cats' == $page['section']) +{ + $query.= ' + WHERE date_last >= '.pwg_db_get_recent_period_expression($user['recent_period']); } else { - // $user['forbidden_categories'] including with USER_CACHE_CATEGORIES_TABLE - $query = ' -SELECT - c.*, nb_images, date_last, max_date_last, count_images, count_categories - FROM '.CATEGORIES_TABLE.' c INNER JOIN '.USER_CACHE_CATEGORIES_TABLE.' - ON id = cat_id and user_id = '.$user['id'].' - WHERE id_uppercat '. - (!isset($page['category']) ? 'is NULL' : '= '.$page['category']['id']).' -'.get_sql_condition_FandF - ( - array - ( - 'visible_categories' => 'id', - ), - 'AND' - ).' - ORDER BY rank -;'; + $query.= ' + WHERE id_uppercat '.(!isset($page['category']) ? 'is NULL' : '= '.$page['category']['id']); +} + +$query.= get_sql_condition_FandF( + array( + 'visible_categories' => 'id', + ), + 'AND' + ); + +if ('recent_cats' != $page['section']) +{ + $query.= ' + ORDER BY rank'; } +$query.= ' +;'; + $result = pwg_query($query); $categories = array(); $category_ids = array(); $image_ids = array(); +$user_representative_updates_for = array(); while ($row = pwg_db_fetch_assoc($result)) { $row['is_child_date_last'] = @$row['max_date_last']>@$row['date_last']; - if (isset($row['representative_picture_id']) - and is_numeric($row['representative_picture_id'])) + if (!empty($row['user_representative_picture_id'])) + { + $image_id = $row['user_representative_picture_id']; + } + else if (!empty($row['representative_picture_id'])) { // if a representative picture is set, it has priority $image_id = $row['representative_picture_id']; } else if ($conf['allow_random_representative']) - {// searching a random representant among elements in sub-categories - if ($row['count_images']>0) - { - $query = ' -SELECT image_id - FROM '.CATEGORIES_TABLE.' AS c INNER JOIN '.IMAGE_CATEGORY_TABLE.' AS ic - ON ic.category_id = c.id'; - $query.= ' - WHERE (c.id='.$row['id'].' OR uppercats LIKE \''.$row['uppercats'].',%\')' - .get_sql_condition_FandF - ( - array - ( - 'forbidden_categories' => 'c.id', - 'visible_categories' => 'c.id', - 'visible_images' => 'image_id' - ), - "\n AND" - ).' - ORDER BY '.DB_RANDOM_FUNCTION.'() - LIMIT 1 -;'; - $subresult = pwg_query($query); - if (pwg_db_num_rows($subresult) > 0) - { - list($image_id) = pwg_db_fetch_row($subresult); - } - } + { + // searching a random representant among elements in sub-categories + $image_id = get_random_image_in_category($row); } else { // searching a random representant among representant of sub-categories @@ -143,6 +121,11 @@ SELECT image_id if (isset($image_id)) { + if ($row['user_representative_picture_id'] != $image_id) + { + $user_representative_updates_for[ $user['id'].'#'.$row['id'] ] = $image_id; + } + $row['representative_picture_id'] = $image_id; array_push($image_ids, $image_id); array_push($categories, $row); @@ -193,17 +176,98 @@ if ($page['section']=='recent_cats') if (count($categories) > 0) { $thumbnail_src_of = array(); + $new_image_ids = array(); $query = ' -SELECT id, path, tn_ext +SELECT id, path, tn_ext, level FROM '.IMAGES_TABLE.' WHERE id IN ('.implode(',', $image_ids).') ;'; $result = pwg_query($query); while ($row = pwg_db_fetch_assoc($result)) { - $thumbnail_src_of[$row['id']] = get_thumbnail_url($row); + if ($row['level'] <= $user['level']) + { + $thumbnail_src_of[$row['id']] = get_thumbnail_url($row); + } + else + { + // problem: we must not display the thumbnail of a photo which has a + // higher privacy level than user privacy level + // + // * what is the represented category? + // * find a random photo matching user permissions + // * register it at user_representative_picture_id + // * set it as the representative_picture_id for the category + + foreach ($categories as &$category) + { + if ($row['id'] == $category['representative_picture_id']) + { + if ($category['count_images']>0) + { + // searching a random representant among elements in sub-categories + $image_id = get_random_image_in_category($category); + + if (isset($image_id)) + { + if (!in_array($image_id, $image_ids)) + { + array_push($new_image_ids, $image_id); + } + + $user_representative_updates_for[ $user['id'].'#'.$category['id'] ] = $image_id; + + $category['representative_picture_id'] = $image_id; + } + } + } + } + unset($category); + } + } + + if (count($new_image_ids) > 0) + { + $query = ' +SELECT id, path, tn_ext + FROM '.IMAGES_TABLE.' + WHERE id IN ('.implode(',', $new_image_ids).') +;'; + $result = pwg_query($query); + while ($row = pwg_db_fetch_assoc($result)) + { + $thumbnail_src_of[$row['id']] = get_thumbnail_url($row); + } + } +} + +if (count($user_representative_updates_for)) +{ + $updates = array(); + + foreach ($user_representative_updates_for as $user_cat => $image_id) + { + list($user_id, $cat_id) = explode('#', $user_cat); + + array_push( + $updates, + array( + 'user_id' => $user_id, + 'cat_id' => $cat_id, + 'user_representative_picture_id' => $image_id, + ) + ); } + + mass_updates( + USER_CACHE_CATEGORIES_TABLE, + array( + 'primary' => array('user_id', 'cat_id'), + 'update' => array('user_representative_picture_id') + ), + $updates + ); } if (count($categories) > 0) diff --git a/include/functions.inc.php b/include/functions.inc.php index 8e1d6054a..b28a04d3d 100644 --- a/include/functions.inc.php +++ b/include/functions.inc.php @@ -33,6 +33,17 @@ include_once( PHPWG_ROOT_PATH .'include/functions_plugins.inc.php' ); //----------------------------------------------------------- generic functions +/** + * stupidly returns the current microsecond since Unix epoch + */ +function micro_seconds() +{ + $t1 = explode(' ', microtime()); + $t2 = explode('.', $t1[0]); + $t2 = $t1[1].substr($t2[1], 0, 6); + return $t2; +} + // The function get_moment returns a float value coresponding to the number // of seconds since the unix epoch (1st January 1970) and the microseconds // are precised : e.g. 1052343429.89276600 diff --git a/include/functions_category.inc.php b/include/functions_category.inc.php index 25ffd8117..8a0179418 100644 --- a/include/functions_category.inc.php +++ b/include/functions_category.inc.php @@ -495,4 +495,42 @@ function get_display_images_count($cat_nb_images, $cat_count_images, $cat_count_ return $display_text; } +/** + * Find a random photo among all photos below a given album in the tree (not + * only photo directly associated to the album but also to sub-albums) + * + * we need $category['uppercats'], $category['id'], $category['count_images'] + */ +function get_random_image_in_category($category) +{ + $image_id = null; + if ($category['count_images']>0) + { + $query = ' +SELECT image_id + FROM '.CATEGORIES_TABLE.' AS c + INNER JOIN '.IMAGE_CATEGORY_TABLE.' AS ic ON ic.category_id = c.id + WHERE (c.id='.$category['id'].' OR uppercats LIKE \''.$category['uppercats'].',%\')' + .get_sql_condition_FandF + ( + array + ( + 'forbidden_categories' => 'c.id', + 'visible_categories' => 'c.id', + 'visible_images' => 'image_id', + ), + "\n AND" + ).' + ORDER BY '.DB_RANDOM_FUNCTION.'() + LIMIT 1 +;'; + $result = pwg_query($query); + if (pwg_db_num_rows($result) > 0) + { + list($image_id) = pwg_db_fetch_row($result); + } + } + + return $image_id; +} ?> \ No newline at end of file diff --git a/install/db/95-database.php b/install/db/95-database.php new file mode 100644 index 000000000..0507b6c0f --- /dev/null +++ b/install/db/95-database.php @@ -0,0 +1,53 @@ + \ No newline at end of file diff --git a/install/piwigo_structure-mysql.sql b/install/piwigo_structure-mysql.sql index 39184daab..f9e01ddad 100644 --- a/install/piwigo_structure-mysql.sql +++ b/install/piwigo_structure-mysql.sql @@ -368,6 +368,7 @@ CREATE TABLE `piwigo_user_cache_categories` ( `nb_images` mediumint(8) unsigned NOT NULL default '0', `count_images` mediumint(8) unsigned default '0', `count_categories` mediumint(8) unsigned default '0', + `user_representative_picture_id` mediumint(8) unsigned default NULL, PRIMARY KEY (`user_id`,`cat_id`) ) TYPE=MyISAM; diff --git a/install/piwigo_structure-pdo-sqlite.sql b/install/piwigo_structure-pdo-sqlite.sql index c01f55892..a46c5929d 100644 --- a/install/piwigo_structure-pdo-sqlite.sql +++ b/install/piwigo_structure-pdo-sqlite.sql @@ -425,6 +425,7 @@ CREATE TABLE piwigo_user_cache_categories "nb_images" INTEGER default 0 NOT NULL, "count_images" INTEGER default 0, "count_categories" INTEGER default 0, + "user_representative_picture_id" INTEGER, PRIMARY KEY ("user_id","cat_id") ); diff --git a/install/piwigo_structure-pgsql.sql b/install/piwigo_structure-pgsql.sql index feab9acc3..ca1c003e0 100644 --- a/install/piwigo_structure-pgsql.sql +++ b/install/piwigo_structure-pgsql.sql @@ -514,6 +514,7 @@ CREATE TABLE "piwigo_user_cache_categories" "nb_images" INTEGER default 0 NOT NULL, "count_images" INTEGER default 0, "count_categories" INTEGER default 0, + "user_representative_picture_id" INTEGER, PRIMARY KEY ("user_id","cat_id") ); diff --git a/install/piwigo_structure-sqlite.sql b/install/piwigo_structure-sqlite.sql index 54e2f0d12..a46c5929d 100644 --- a/install/piwigo_structure-sqlite.sql +++ b/install/piwigo_structure-sqlite.sql @@ -425,6 +425,7 @@ CREATE TABLE piwigo_user_cache_categories "nb_images" INTEGER default 0 NOT NULL, "count_images" INTEGER default 0, "count_categories" INTEGER default 0, + "user_representative_picture_id" INTEGER, PRIMARY KEY ("user_id","cat_id") ); @@ -535,3 +536,4 @@ CREATE TABLE piwigo_comments CREATE INDEX "comments_i2" ON "piwigo_comments" ("validation_date"); CREATE INDEX "comments_i1" ON "piwigo_comments" ("image_id"); + diff --git a/picture.php b/picture.php index f9ff1d759..66bef5d4c 100644 --- a/picture.php +++ b/picture.php @@ -282,6 +282,14 @@ DELETE FROM '.FAVORITES_TABLE.' UPDATE '.CATEGORIES_TABLE.' SET representative_picture_id = '.$page['image_id'].' WHERE id = '.$page['category']['id'].' +;'; + pwg_query($query); + + $query = ' +UPDATE '.USER_CACHE_CATEGORIES_TABLE.' + SET user_representative_picture_id = NULL + WHERE user_id = '.$user['id'].' + AND cat_id = '.$page['category']['id'].' ;'; pwg_query($query); } -- cgit v1.2.3