Changeset 423 for trunk


Ignore:
Timestamp:
May 28, 2004, 11:56:07 PM (20 years ago)
Author:
z0rglub
Message:

table user_category dropped

Location:
trunk
Files:
7 edited

Legend:

Unmodified
Added
Removed

  • trunk/admin/include/functions.php

    r394 r423  
    276276  mysql_query( $query );
    277277
    278   // destruction of the categories informations linked with the user
    279   $query = 'DELETE FROM '.PREFIX_TABLE.'user_category';
    280   $query.= ' WHERE user_id = '.$user_id;
    281   $query.= ';';
    282   mysql_query( $query );
    283 
    284278  // destruction of the user
    285279  $query = 'DELETE FROM '.USERS_TABLE;
    … …  
    632626
    633627/**
    634  * prepares the query to update the table user_category
    635  *
    636  * Prepares the query (global variable $values) to update table
    637  * user_category : for a couple (user,category) the number of sub-categories
    638  * and the last date of the category (all sub-categories taken into
    639  * account). It also calls function update_uppercats for each category. The
    640  * function is recursive.
    641  *
    642  * @param array $categories
    643  * @return void
    644  */
    645 function update_user_category( $categories )
    646 {
    647   global $page,$user_restrictions,$value_num,$values;
    648 
    649   foreach ( $categories as $category ) {
    650     // recursive call
    651     update_user_category( $category['subcats'] );
    652     // 1. update the table user_category
    653     foreach ( $user_restrictions as $user_id => $restrictions ) {
    654       // if the category is forbidden to this user, go to next user
    655       if ( in_array( $category['id'], $restrictions ) ) continue;
    656 
    657       // how many sub_categories for this user ?
    658       $user_subcats = array_diff(
    659         $page['plain_structure'][$category['id']]['direct_subcats_ids'],
    660         $restrictions );
    661       $user_nb_subcats = count( array_unique( $user_subcats ) );
    662       // last date of the category
    663       $user_all_subcats = array_unique( array_diff(
    664         $page['plain_structure'][$category['id']]['all_subcats_ids'],
    665         $restrictions ) );
    666            
    667       $query = 'SELECT MAX(date_last) AS last_date';
    668       $query.= ' FROM '.CATEGORIES_TABLE;
    669       $query.= ' WHERE id IN ('.$category['id'];
    670       if ( count( $user_all_subcats ) > 0 )
    671         $query.= ','.implode( ',', $user_all_subcats );
    672       $query.= ')';
    673       $query.= ';';
    674       $row = mysql_fetch_array( mysql_query( $query ) );
    675 
    676       // insert a new line in database
    677       if ( $value_num++ > 0 ) $values.= ', ';
    678       else                    $values.= ' ';
    679       $values.= '('.$user_id.",".$category['id'];
    680       if ( isset( $row['last_date'] ) ) $values.= ",'".$row['last_date']."'";
    681       else                              $values.= ',NULL';
    682       $values.= ','.$user_nb_subcats.')';
    683     }
    684     update_uppercats( $category['id'] );
    685   }
    686 }
    687 
    688 /**
    689628 * updates the column categories.uppercats
    690629 *
    … …  
    799738
    800739/**
    801  * finalizes operation for user_category table update
    802  *
    803  * This function is called by synchronization_*. It creates the
    804  * $page['plain_structure'] and $page['structure'], get the SQL query to
    805  * update user_category, clean user_category, and finally update the
    806  * table. The users updates depends on the global array $user_restrictions.
    807  *
    808  * @return void
    809  */
    810 function synchronize()
    811 {
    812   global $user_restrictions,$page,$values;
    813 
    814   update_user_category( $page['structure'] );
    815 
    816   // cleaning user_category table for users to update
    817   foreach( $user_restrictions as $user_id => $restrictions ) {
    818     $query = 'DELETE';
    819     $query.= ' FROM '.USER_CATEGORY_TABLE;
    820     $query.= ' WHERE user_id = '.$user_id;
    821     $query.= ';';
    822     mysql_query( $query );
    823   }
    824 
    825   $query = 'INSERT INTO '.USER_CATEGORY_TABLE;
    826   $query.= ' (user_id,category_id,date_last,nb_sub_categories) VALUES ';
    827   $query.= $values;
    828   $query.= ';';
    829   mysql_query( $query );
    830 }
    831 
    832 /**
    833  * synchronizes all users calculated informations
    834  *
    835  * fills global array $user_restrictions with all users and related
    836  * restrictions before calling synchronize.
    837  *
    838  * @return void
    839  */
    840 function synchronize_all_users()
    841 {
    842   global $user_restrictions,$page;
    843 
    844   $page['plain_structure'] = get_plain_structure();
    845   $page['structure']       = create_structure( '' );
    846  
    847   $user_restrictions = array();
    848  
    849   $query = 'SELECT id';
    850   $query.= ' FROM '.USERS_TABLE;
    851   $query.= ';';
    852   $result = mysql_query( $query );
    853   while ( $row = mysql_fetch_array( $result ) )
    854   {
    855     $user_restrictions[$row['id']] = update_user_restrictions( $row['id'] );
    856   }
    857   synchronize();
    858 }
    859 
    860 /**
    861  * synchronizes 1 user calculated informations
    862  *
    863  * fills global array $user_restrictions with the user id and its related
    864  * restrictions before calling synchronize.
    865  *
    866  * @param int $user_id
    867  * @return void
    868  */
    869 function synchronize_user( $user_id )
    870 {
    871   global $user_restrictions,$page;
    872 
    873   $page['plain_structure'] = get_plain_structure();
    874   $page['structure']       = create_structure( '' );
    875  
    876   $user_restrictions = array();
    877   $user_restrictions[$user_id] = update_user_restrictions( $user_id );
    878   synchronize();
    879 }
    880 
    881 /**
    882  * synchronizes all users (belonging to the group) calculated informations
    883  *
    884  * fills global array $user_restrictions with all users and related
    885  * restrictions before calling synchronize.
    886  *
    887  * @return void
    888  */
    889 function synchronize_group( $group_id )
    890 {
    891   global $user_restrictions,$page;
    892 
    893   $page['plain_structure'] = get_plain_structure();
    894   $page['structure']       = create_structure( '' );
    895  
    896   $user_restrictions = array();
    897  
    898   $query = 'SELECT id';
    899   $query.= ' FROM '.USERS_TABLE;
    900   $query.= ', '.USER_GROUP_TABLE;
    901   $query.= ' WHERE group_id = '.$group_id;
    902   $query.= ' AND id = user_id';
    903   $query.= ';';
    904   $result = mysql_query( $query );
    905   while ( $row = mysql_fetch_array( $result ) )
    906   {
    907     $user_restrictions[$row['id']] = update_user_restrictions( $row['id'] );
    908   }
    909   synchronize();
    910 }
    911 
    912 /**
    913740 * updates the calculated data users.forbidden_categories, it includes
    914741 * sub-categories of the direct forbidden categories

  • trunk/admin/update.php

    r394 r423  
    764764  $end = get_moment();
    765765  echo get_elapsed_time( $start, $end ).' for update_category( all )<br />';
    766 
    767   $start = get_moment();
    768   synchronize_all_users();
    769   $end = get_moment();
    770   echo get_elapsed_time( $start, $end ).' for synchronize_all_users<br />';
    771766}
    772767//----------------------------------------------------------- sending html code

  • trunk/include/constants.php

    r393 r423  
    5252define('SITES_TABLE', $table_prefix.'sites');
    5353define('USER_ACCESS_TABLE', $table_prefix.'user_access');
    54 define('USER_CATEGORY_TABLE', $table_prefix.'user_category');
    5554define('USER_GROUP_TABLE', $table_prefix.'user_group');
    5655define('USERS_TABLE', $table_prefix.'users');

  • trunk/include/functions_category.inc.php

    r395 r423  
    2626// +-----------------------------------------------------------------------+
    2727
     28/**
     29 * Provides functions to handle categories.
     30 *
     31 *
     32 */
     33
     34/**
     35 * Is the category accessible to the connected user ?
     36 *
     37 * Note : if the user is not authorized to see this category, page creation
     38 * ends (exit command in this function)
     39 *
     40 * @param int category id to verify
     41 * @return void
     42 */
    2843function check_restrictions( $category_id )
    2944{
    … …  
    3954}
    4055
    41 // the check_cat_id function check whether the $cat is a right parameter :
    42 //  - $cat is numeric and corresponds to a category in the database
    43 //  - $cat equals 'fav' (for favorites)
    44 //  - $cat equals 'search' (when the result of a search is displayed)
     56/**
     57 * Checks whether the argument is a right parameter category id
     58 *
     59 * The argument is a right parameter if corresponds to one of these :
     60 *
     61 *  - is numeric and corresponds to a category in the database
     62 *  - is equals 'fav' (for favorites)
     63 *  - is equals 'search' (when the result of a search is displayed)
     64 *  - is equals 'most_visited'
     65 *  - is equals 'best_rated'
     66 *  - is equals 'recent'
     67 *
     68 * The function fills the global var $page['cat'] and returns nothing
     69 *
     70 * @param mixed category id or special category name
     71 * @return void
     72 */
    4573function check_cat_id( $cat )
    4674{
    … …  
    79107  global $page,$user;
    80108 
    81   $infos = array( 'name','id','uc.date_last','nb_images','dir','id_uppercat',
    82                   'rank','site_id','nb_sub_categories','uppercats');
     109  $infos = array( 'name','id','date_last','nb_images','dir','id_uppercat',
     110                  'rank','site_id','uppercats');
    83111 
    84112  $query = 'SELECT '.implode( ',', $infos );
    85   $query.= ' FROM '.CATEGORIES_TABLE.' AS c';
    86 //  $query.= ' ,'.PREFIX_TABLE.'user_category AS uc';
    87   $query.= ' INNER JOIN '.USER_CATEGORY_TABLE.' AS uc';
    88   $query.= ' ON c.id = uc.category_id';
    89   $query.= ' WHERE user_id = '.$user['id'];
     113  $query.= ' FROM '.CATEGORIES_TABLE;
     114  $query.= ' WHERE 1 = 1'; // stupid but permit using AND after it !
    90115  if ( !$user['expand'] )
    91116  {
    … …  
    102127    $query.= '('.$user['forbidden_categories'].')';
    103128  }
    104 //  $query.= ' AND c.id = uc.category_id';
    105129  $query.= ' ORDER BY id_uppercat ASC, rank ASC';
    106130  $query.= ';';
    … …  
    114138      if ( $info == 'uc.date_last')
    115139      {
    116             if (empty($row['date_last']))
    117                 {
    118                   $category['date_last']= 0;
    119                 }
    120                 else
    121                 {
     140        if ( empty( $row['date_last'] ) )
     141        {
     142          $category['date_last'] = 0;
     143        }
     144        else
     145        {
    122146          list($year,$month,$day) = explode( '-', $row['date_last'] );
    123147          $category['date_last'] = mktime(0,0,0,$month,$day,$year);
    124                 }
     148        }
    125149      }
    126150      else if ( isset( $row[$info] ) ) $category[$info] = $row[$info];
    … …  
    234258}
    235259
    236 // variables :
    237 // $cat['comment']
    238 // $cat['dir']
    239 // $cat['dir']
    240 // $cat['name'] is an array :
    241 //      - $cat['name'][0] is the lowest cat name
    242 //      and
    243 //      - $cat['name'][n] is the most uppercat name findable
    244 // $cat['nb_images']
    245 // $cat['id_uppercat']
    246 // $cat['site_id']
     260/**
     261 * Retrieve informations about a category in the database
     262 *
     263 * Returns an array with following keys :
     264 *
     265 *  - comment
     266 *  - dir : directory, might be empty for virtual categories
     267 *  - name : an array with indexes from 0 (lowest cat name) to n (most
     268 *           uppercat name findable)
     269 *  - nb_images
     270 *  - id_uppercat
     271 *  - site_id
     272 *  -
     273 *
     274 * @param int category id
     275 * @return array
     276 */
    247277function get_cat_info( $id )
    248278{

  • trunk/include/functions_user.inc.php

    r398 r423  
    154154      mysql_query ( $query );
    155155    }
    156     // 6. has the same categories informations than guest
    157     $query = 'SELECT category_id,date_last,nb_sub_categories';
    158     $query.= ' FROM '.PREFIX_TABLE.'user_category AS uc';
    159     $query.= ',     '.PREFIX_TABLE.'users         AS u';
    160     $query.= " WHERE u.username = 'guest'";
    161     $query.= ' AND uc.user_id = u.id';
    162     $query.= ';';
    163     $result = mysql_query( $query );
    164     while( $row = mysql_fetch_array( $result ) )
    165     {
    166       $query = 'INSERT INTO '.PREFIX_TABLE.'user_category';
    167       $query.= ' (user_id,category_id,date_last,nb_sub_categories) VALUES';
    168       $query.= ' ('.$user_id.','.$row['category_id'];
    169       $query.= ",'".$row['date_last']."',".$row['nb_sub_categories'].')';
    170       $query.= ';';
    171       mysql_query ( $query );
    172     }
    173156  }
    174157  return $error;

  • trunk/install/dbscheme.txt

    r420 r423  
    1212table:sites
    1313table:user_access
    14 table:user_category
    1514table:user_group
    1615table:users
    … …  
    7675column:user_id                   table:user_access    type:smallint                 nullable:Y length:5   signed:N
    7776column:cat_id                    table:user_access    type:smallint                 nullable:Y length:5   signed:N
    78 column:user_id                   table:user_category  type:smallint                 nullable:Y length:5   signed:N
    79 column:category_id               table:user_category  type:smallint                 nullable:Y length:5   signed:N
    80 column:date_last                 table:user_category  type:date                     nullable:N
    81 column:nb_sub_categories         table:user_category  type:smallint                 nullable:Y length:5   signed:N
    8277column:user_id                   table:user_group     type:smallint                 nullable:Y length:5   signed:N
    8378column:group_id                  table:user_group     type:smallint                 nullable:Y length:5   signed:N
    … …  
    123118PK:user_access_pk    table:user_access    column:user_id
    124119PK:user_access_pk    table:user_access    column:cat_id
    125 PK:user_category_pk  table:user_category  column:user_id
    126 PK:user_category_pk  table:user_category  column:category_id
    127120PK:user_group_pk     table:user_group     column:group_id
    128121PK:user_group_pk     table:user_group     column:user_id

  • trunk/install/phpwebgallery_structure.sql

    r420 r423  
    174174  cat_id smallint(5) unsigned NOT NULL default '0',
    175175  PRIMARY KEY  (user_id,cat_id)
    176 ) TYPE=MyISAM;
    177 
    178 --
    179 -- Table structure for table 'phpwebgallery_user_category'
    180 --
    181 
    182 DROP TABLE IF EXISTS phpwebgallery_user_category;
    183 CREATE TABLE phpwebgallery_user_category (
    184   user_id smallint(5) unsigned NOT NULL default '0',
    185   category_id smallint(5) unsigned NOT NULL default '0',
    186   date_last date default NULL,
    187   nb_sub_categories smallint(5) unsigned NOT NULL default '0',
    188   PRIMARY KEY  (user_id,category_id)
    189176) TYPE=MyISAM;
    190177
Note: See TracChangeset for help on using the changeset viewer.