diff options
| -rw-r--r-- | admin/include/functions.php | 11 | ||||
| -rw-r--r-- | include/category_cats.inc.php | 198 | ||||
| -rw-r--r-- | include/functions.inc.php | 11 | ||||
| -rw-r--r-- | include/functions_category.inc.php | 38 | ||||
| -rw-r--r-- | install/db/95-database.php | 53 | ||||
| -rw-r--r-- | install/piwigo_structure-mysql.sql | 1 | ||||
| -rw-r--r-- | install/piwigo_structure-pdo-sqlite.sql | 1 | ||||
| -rw-r--r-- | install/piwigo_structure-pgsql.sql | 1 | ||||
| -rw-r--r-- | install/piwigo_structure-sqlite.sql | 2 | ||||
| -rw-r--r-- | picture.php | 8 |
10 files changed, 246 insertions, 78 deletions
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 @@ -883,17 +883,6 @@ function get_fs($path, $recursive = true) } /** - * 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 * * compares and synchronizes base users table (USERS_TABLE) with its child 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 @@ +<?php +// +-----------------------------------------------------------------------+ +// | Piwigo - a PHP based photo gallery | +// +-----------------------------------------------------------------------+ +// | Copyright(C) 2008-2011 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 = 'New colum user_cache_categories.user_representative_picture_id'; + +// Add column +$query = 'ALTER TABLE '.USER_CACHE_CATEGORIES_TABLE.' ADD COLUMN '; + +if ('mysql' == $conf['dblayer']) +{ + $query.= ' `user_representative_picture_id` mediumint(8) unsigned default NULL'; +} + +if (in_array($conf['dblayer'], array('pgsql', 'sqlite', 'pdo-sqlite'))) +{ + $query.= ' "user_representative_picture_id" INTEGER'; +} + +$query.= ';'; + +pwg_query($query); + +echo +"\n" +. $upgrade_description +."\n" +; +?>
\ 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 @@ -284,6 +284,14 @@ UPDATE '.CATEGORIES_TABLE.' 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); } redirect($url_self); |