Changeset 8802


Ignore:
Timestamp:
Jan 20, 2011, 2:32:34 PM (10 years ago)
Author:
plg
Message:

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 $confallow_random_representative = true;

Location:
trunk
Files:
1 added
9 edited

Legend:

Unmodified
Added
Removed
  • trunk/admin/include/functions.php

    r8762 r8802  
    884884
    885885/**
    886  * stupidly returns the current microsecond since Unix epoch
    887  */
    888 function micro_seconds()
    889 {
    890   $t1 = explode(' ', microtime());
    891   $t2 = explode('.', $t1[0]);
    892   $t2 = $t1[1].substr($t2[1], 0, 6);
    893   return $t2;
    894 }
    895 
    896 /**
    897886 * synchronize base users list and related users list
    898887 *
  • trunk/include/category_cats.inc.php

    r8728 r8802  
    2828 */
    2929
    30 if ($page['section']=='recent_cats')
    31 {
    32   // $user['forbidden_categories'] including with USER_CACHE_CATEGORIES_TABLE
    33   $query = '
     30// $user['forbidden_categories'] including with USER_CACHE_CATEGORIES_TABLE
     31$query = '
    3432SELECT
    35   c.*, nb_images, date_last, max_date_last, count_images, count_categories
    36   FROM '.CATEGORIES_TABLE.' c INNER JOIN '.USER_CACHE_CATEGORIES_TABLE.'
    37   ON id = cat_id and user_id = '.$user['id'].'
    38   WHERE date_last >= '.pwg_db_get_recent_period_expression($user['recent_period']).'
    39 '.get_sql_condition_FandF
    40   (
    41     array
    42       (
    43         'visible_categories' => 'id',
    44       ),
    45     'AND'
    46   ).'
     33    c.*,
     34    user_representative_picture_id,
     35    nb_images,
     36    date_last,
     37    max_date_last,
     38    count_images,
     39    count_categories
     40  FROM '.CATEGORIES_TABLE.' c
     41    INNER JOIN '.USER_CACHE_CATEGORIES_TABLE.' ucc ON id = cat_id AND user_id = '.$user['id'];
     42
     43if ('recent_cats' == $page['section'])
     44{
     45  $query.= '
     46  WHERE date_last >= '.pwg_db_get_recent_period_expression($user['recent_period']);
     47}
     48else
     49{
     50  $query.= '
     51  WHERE id_uppercat '.(!isset($page['category']) ? 'is NULL' : '= '.$page['category']['id']);
     52}
     53
     54$query.= get_sql_condition_FandF(
     55  array(
     56    'visible_categories' => 'id',
     57    ),
     58  'AND'
     59  );
     60
     61if ('recent_cats' != $page['section'])
     62{
     63  $query.= '
     64  ORDER BY rank';
     65}
     66
     67$query.= '
    4768;';
    48 }
    49 else
    50 {
    51   // $user['forbidden_categories'] including with USER_CACHE_CATEGORIES_TABLE
    52   $query = '
    53 SELECT
    54   c.*, nb_images, date_last, max_date_last, count_images, count_categories
    55   FROM '.CATEGORIES_TABLE.' c INNER JOIN '.USER_CACHE_CATEGORIES_TABLE.'
    56   ON id = cat_id and user_id = '.$user['id'].'
    57   WHERE id_uppercat '.
    58   (!isset($page['category']) ? 'is NULL' : '= '.$page['category']['id']).'
    59 '.get_sql_condition_FandF
    60   (
    61     array
    62       (
    63         'visible_categories' => 'id',
    64       ),
    65     'AND'
    66   ).'
    67   ORDER BY rank
    68 ;';
    69 }
    7069
    7170$result = pwg_query($query);
     
    7372$category_ids = array();
    7473$image_ids = array();
     74$user_representative_updates_for = array();
    7575
    7676while ($row = pwg_db_fetch_assoc($result))
     
    7878  $row['is_child_date_last'] = @$row['max_date_last']>@$row['date_last'];
    7979
    80   if (isset($row['representative_picture_id'])
    81       and is_numeric($row['representative_picture_id']))
     80  if (!empty($row['user_representative_picture_id']))
     81  {
     82    $image_id = $row['user_representative_picture_id'];
     83  }
     84  else if (!empty($row['representative_picture_id']))
    8285  { // if a representative picture is set, it has priority
    8386    $image_id = $row['representative_picture_id'];
    8487  }
    8588  else if ($conf['allow_random_representative'])
    86   {// searching a random representant among elements in sub-categories
    87     if ($row['count_images']>0)
    88     {
    89       $query = '
    90 SELECT image_id
    91   FROM '.CATEGORIES_TABLE.' AS c INNER JOIN '.IMAGE_CATEGORY_TABLE.' AS ic
    92     ON ic.category_id = c.id';
    93       $query.= '
    94   WHERE (c.id='.$row['id'].' OR uppercats LIKE \''.$row['uppercats'].',%\')'
    95     .get_sql_condition_FandF
    96     (
    97       array
    98         (
    99           'forbidden_categories' => 'c.id',
    100           'visible_categories' => 'c.id',
    101           'visible_images' => 'image_id'
    102         ),
    103       "\n  AND"
    104     ).'
    105   ORDER BY '.DB_RANDOM_FUNCTION.'()
    106   LIMIT 1
    107 ;';
    108       $subresult = pwg_query($query);
    109       if (pwg_db_num_rows($subresult) > 0)
    110       {
    111         list($image_id) = pwg_db_fetch_row($subresult);
    112       }
    113     }
     89  {
     90    // searching a random representant among elements in sub-categories
     91    $image_id = get_random_image_in_category($row);
    11492  }
    11593  else
     
    144122  if (isset($image_id))
    145123  {
     124    if ($row['user_representative_picture_id'] != $image_id)
     125    {
     126      $user_representative_updates_for[ $user['id'].'#'.$row['id'] ] = $image_id;
     127    }
     128   
    146129    $row['representative_picture_id'] = $image_id;
    147130    array_push($image_ids, $image_id);
     
    194177{
    195178  $thumbnail_src_of = array();
     179  $new_image_ids = array();
    196180
    197181  $query = '
    198 SELECT id, path, tn_ext
     182SELECT id, path, tn_ext, level
    199183  FROM '.IMAGES_TABLE.'
    200184  WHERE id IN ('.implode(',', $image_ids).')
     
    203187  while ($row = pwg_db_fetch_assoc($result))
    204188  {
    205     $thumbnail_src_of[$row['id']] = get_thumbnail_url($row);
    206   }
     189    if ($row['level'] <= $user['level'])
     190    {
     191      $thumbnail_src_of[$row['id']] = get_thumbnail_url($row);
     192    }
     193    else
     194    {
     195      // problem: we must not display the thumbnail of a photo which has a
     196      // higher privacy level than user privacy level
     197      //
     198      // * what is the represented category?
     199      // * find a random photo matching user permissions
     200      // * register it at user_representative_picture_id
     201      // * set it as the representative_picture_id for the category
     202
     203      foreach ($categories as &$category)
     204      {
     205        if ($row['id'] == $category['representative_picture_id'])
     206        {
     207          if ($category['count_images']>0)
     208          {
     209            // searching a random representant among elements in sub-categories
     210            $image_id = get_random_image_in_category($category);
     211
     212            if (isset($image_id))
     213            {
     214              if (!in_array($image_id, $image_ids))
     215              {
     216                array_push($new_image_ids, $image_id);
     217              }
     218             
     219              $user_representative_updates_for[ $user['id'].'#'.$category['id'] ] = $image_id;
     220
     221              $category['representative_picture_id'] = $image_id;
     222            }
     223          }
     224        }
     225      }
     226      unset($category);
     227    }
     228  }
     229
     230  if (count($new_image_ids) > 0)
     231  {
     232    $query = '
     233SELECT id, path, tn_ext
     234  FROM '.IMAGES_TABLE.'
     235  WHERE id IN ('.implode(',', $new_image_ids).')
     236;';
     237    $result = pwg_query($query);
     238    while ($row = pwg_db_fetch_assoc($result))
     239    {
     240      $thumbnail_src_of[$row['id']] = get_thumbnail_url($row);
     241    }
     242  }
     243}
     244
     245if (count($user_representative_updates_for))
     246{
     247  $updates = array();
     248 
     249  foreach ($user_representative_updates_for as $user_cat => $image_id)
     250  {
     251    list($user_id, $cat_id) = explode('#', $user_cat);
     252   
     253    array_push(
     254      $updates,
     255      array(
     256        'user_id' => $user_id,
     257        'cat_id' => $cat_id,
     258        'user_representative_picture_id' => $image_id,
     259        )
     260      );
     261  }
     262
     263  mass_updates(
     264    USER_CACHE_CATEGORIES_TABLE,
     265    array(
     266      'primary' => array('user_id', 'cat_id'),
     267      'update'  => array('user_representative_picture_id')
     268      ),
     269    $updates
     270    );
    207271}
    208272
  • trunk/include/functions.inc.php

    r8728 r8802  
    3333
    3434//----------------------------------------------------------- generic functions
     35
     36/**
     37 * stupidly returns the current microsecond since Unix epoch
     38 */
     39function micro_seconds()
     40{
     41  $t1 = explode(' ', microtime());
     42  $t2 = explode('.', $t1[0]);
     43  $t2 = $t1[1].substr($t2[1], 0, 6);
     44  return $t2;
     45}
    3546
    3647// The function get_moment returns a float value coresponding to the number
  • trunk/include/functions_category.inc.php

    r8728 r8802  
    496496}
    497497
     498/**
     499 * Find a random photo among all photos below a given album in the tree (not
     500 * only photo directly associated to the album but also to sub-albums)
     501 *
     502 * we need $category['uppercats'], $category['id'], $category['count_images']
     503 */
     504function get_random_image_in_category($category)
     505{
     506  $image_id = null;
     507  if ($category['count_images']>0)
     508  {
     509    $query = '
     510SELECT image_id
     511  FROM '.CATEGORIES_TABLE.' AS c
     512    INNER JOIN '.IMAGE_CATEGORY_TABLE.' AS ic ON ic.category_id = c.id
     513  WHERE (c.id='.$category['id'].' OR uppercats LIKE \''.$category['uppercats'].',%\')'
     514    .get_sql_condition_FandF
     515    (
     516      array
     517        (
     518          'forbidden_categories' => 'c.id',
     519          'visible_categories' => 'c.id',
     520          'visible_images' => 'image_id',
     521        ),
     522      "\n  AND"
     523    ).'
     524  ORDER BY '.DB_RANDOM_FUNCTION.'()
     525  LIMIT 1
     526;';
     527    $result = pwg_query($query);
     528    if (pwg_db_num_rows($result) > 0)
     529    {
     530      list($image_id) = pwg_db_fetch_row($result);
     531    }
     532  }
     533
     534  return $image_id;
     535}
    498536?>
  • trunk/install/piwigo_structure-mysql.sql

    r8651 r8802  
    369369  `count_images` mediumint(8) unsigned default '0',
    370370  `count_categories` mediumint(8) unsigned default '0',
     371  `user_representative_picture_id` mediumint(8) unsigned default NULL,
    371372  PRIMARY KEY  (`user_id`,`cat_id`)
    372373) TYPE=MyISAM;
  • trunk/install/piwigo_structure-pdo-sqlite.sql

    r8651 r8802  
    426426  "count_images" INTEGER default 0,
    427427  "count_categories" INTEGER default 0,
     428  "user_representative_picture_id" INTEGER,
    428429  PRIMARY KEY ("user_id","cat_id")
    429430);
  • trunk/install/piwigo_structure-pgsql.sql

    r8651 r8802  
    515515  "count_images" INTEGER default 0,
    516516  "count_categories" INTEGER default 0,
     517  "user_representative_picture_id" INTEGER,
    517518  PRIMARY KEY ("user_id","cat_id")
    518519);
  • trunk/install/piwigo_structure-sqlite.sql

    r8651 r8802  
    426426  "count_images" INTEGER default 0,
    427427  "count_categories" INTEGER default 0,
     428  "user_representative_picture_id" INTEGER,
    428429  PRIMARY KEY ("user_id","cat_id")
    429430);
     
    536537CREATE INDEX "comments_i2" ON "piwigo_comments" ("validation_date");
    537538CREATE INDEX "comments_i1" ON "piwigo_comments" ("image_id");
     539
  • trunk/picture.php

    r8728 r8802  
    285285;';
    286286        pwg_query($query);
     287
     288        $query = '
     289UPDATE '.USER_CACHE_CATEGORIES_TABLE.'
     290  SET user_representative_picture_id = NULL
     291  WHERE user_id = '.$user['id'].'
     292    AND cat_id = '.$page['category']['id'].'
     293;';
     294        pwg_query($query);
    287295      }
    288296
Note: See TracChangeset for help on using the changeset viewer.