aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorplegall <plg@piwigo.org>2011-01-20 13:32:34 +0000
committerplegall <plg@piwigo.org>2011-01-20 13:32:34 +0000
commitdfb0b9d1faa0603393ddbcb2831da52ba76ea3e9 (patch)
treef19283fe3294804165ae99681f94ffa85d2e7e71
parentd59827398d74626dc7525b337ade43e8a31d4e23 (diff)
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
Diffstat (limited to '')
-rw-r--r--admin/include/functions.php11
-rw-r--r--include/category_cats.inc.php198
-rw-r--r--include/functions.inc.php11
-rw-r--r--include/functions_category.inc.php38
-rw-r--r--install/db/95-database.php53
-rw-r--r--install/piwigo_structure-mysql.sql1
-rw-r--r--install/piwigo_structure-pdo-sqlite.sql1
-rw-r--r--install/piwigo_structure-pgsql.sql1
-rw-r--r--install/piwigo_structure-sqlite.sql2
-rw-r--r--picture.php8
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);