Changeset 8802


Ignore:
Timestamp:
01/20/11 14:32:34 (9 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.