Changeset 2306

Timestamp:

Apr 21, 2008, 12:20:20 AM (16 years ago)

Author:

rvelices

Message:

• merged function ordering() with update_global_rank() and also optimized the queries

Location:

trunk

Files:

• admin/cat_list.php (modified) (4 diffs)
• admin/include/functions.php (modified) (4 diffs)
• admin/maintenance.php (modified) (1 diff)
• admin/site_update.php (modified) (3 diffs)
• install/upgrade_1.3.1.php (modified) (11 diffs)

trunk/admin/cat_list.php

@@ -58 +58 @@
   mass_updates(CATEGORIES_TABLE, $fields, $datas);
 
-  update_global_rank(@$_GET['parent_id']);
+  update_global_rank();
 }
 
@@ -67 +67 @@
 $categories = array();
 
-$base_url = PHPWG_ROOT_PATH.'admin.php?page=cat_list';
-$navigation = '<a class="" href="'.$base_url.'">';
+$base_url = get_root_url().'admin.php?page=cat_list';
+$navigation = '<a href="'.$base_url.'">';
 $navigation.= l10n('home');
 $navigation.= '</a>';
@@ -80 +80 @@
   delete_categories(array($_GET['delete']));
   array_push($page['infos'], l10n('cat_virtual_deleted'));
-  ordering();
   update_global_rank();
 }
@@ -237 +236 @@
       'U_CHILDREN' => $cat_list_url.'&amp;parent_id='.$category['id'],
       'U_EDIT'     => $base_url.'cat_modify&amp;cat_id='.$category['id'],
-      
+
       'IS_VIRTUAL' => empty($category['dir'])
     );

trunk/admin/include/functions.php

@@ -666 +666 @@
 
 /**
- * updates the global_rank of categories under the given id_uppercat
- *
- * @param int id_uppercat
+ * order categories (update categories.rank and global_rank database fields)
+ * so that rank field are consecutive integers starting at 1 for each child
  * @return void
  */
-function update_global_rank($id_uppercat = 'all')
-{
-  $query = '
-SELECT id,rank
+function update_global_rank()
+{
+  $query = '
+SELECT id, if(id_uppercat is null,\'\',id_uppercat) AS id_uppercat, uppercats, rank, global_rank
   FROM '.CATEGORIES_TABLE.'
-;';
-  $result = pwg_query($query);
-  $ranks_array = array();
-  while ($row = mysql_fetch_array($result))
-  {
-    $ranks_array[$row['id']] = $row['rank'];
-  }
-
-  // which categories to update ?
-  $uppercats_array = array();
-
-  $query = '
-SELECT id,uppercats
-  FROM '.CATEGORIES_TABLE;
-  if (is_numeric($id_uppercat))
-  {
-    $query.= '
-  WHERE uppercats REGEXP \'(^|,)'.$id_uppercat.'(,|$)\'
-    AND id != '.$id_uppercat.'
-';
-  }
-  $query.= '
-;';
+  ORDER BY id_uppercat,rank,name
+;';
+
+  $cat_map = array();
+
+  $current_rank = 0;
+  $current_uppercat = '';
+
   $result = pwg_query($query);
   while ($row = mysql_fetch_array($result))
   {
-    $uppercats_array[$row['id']] =  $row['uppercats'];
+    if ($row['id_uppercat'] != $current_uppercat)
+    {
+      $current_rank = 0;
+      $current_uppercat = $row['id_uppercat'];
+    }
+    ++$current_rank;
+    $cat =
+      array(
+        'rank' =>        $current_rank,
+        'rank_changed' =>$current_rank!=$row['rank'],
+        'global_rank' => $row['global_rank'],
+        'uppercats' =>   $row['uppercats'],
+        );
+    $cat_map[ $row['id'] ] = $cat;
   }
 
   $datas = array();
-  foreach ($uppercats_array as $id => $uppercats)
-  {
-    array_push(
-      $datas,
-      array(
-        'id'          => $id,
-        'global_rank' => preg_replace(
+
+  foreach( $cat_map as $id=>$cat )
+  {
+    $new_global_rank = preg_replace(
           '/(\d+)/e',
-          "\$ranks_array['$1']",
-          str_replace(',', '.', $uppercats)
-          ),
-        )
-      );
+          "\$cat_map['$1']['rank']",
+          str_replace(',', '.', $cat['uppercats'] )
+          );
+    if ( $cat['rank_changed']
+      or $new_global_rank!=$cat['global_rank']
+      )
+    {
+      $datas[] = array(
+          'id' => $id,
+          'rank' => $cat['rank'],
+          'global_rank' => $new_global_rank,
+        );
+    }
   }
 
@@ -725 +727 @@
     array(
       'primary' => array('id'),
-      'update'  => array('global_rank')
+      'update'  => array('rank', 'global_rank')
       ),
     $datas
     );
+  return count($datas);
 }
 
@@ -874 +877 @@
     $datas
     );
-}
-
-/**
- * order categories (update categories.rank and global_rank database fields)
- *
- * the purpose of this function is to give a rank for all categories
- * (insides its sub-category), even the newer that have none at te
- * beginning. For this, ordering function selects all categories ordered by
- * rank ASC then name ASC for each uppercat.
- *
- * @returns void
- */
-function ordering()
-{
-  $current_rank = 0;
-  $current_uppercat = '';
-
-  $query = '
-SELECT id, if(id_uppercat is null,\'\',id_uppercat) AS id_uppercat
-  FROM '.CATEGORIES_TABLE.'
-  ORDER BY id_uppercat,rank,name
-;';
-  $result = pwg_query($query);
-  $datas = array();
-  while ($row = mysql_fetch_array($result))
-  {
-    if ($row['id_uppercat'] != $current_uppercat)
-    {
-      $current_rank = 0;
-      $current_uppercat = $row['id_uppercat'];
-    }
-    $data = array('id' => $row['id'], 'rank' => ++$current_rank);
-    array_push($datas, $data);
-  }
-
-  $fields = array('primary' => array('id'), 'update' => array('rank'));
-  mass_updates(CATEGORIES_TABLE, $fields, $datas);
 }
 
@@ -1353 +1319 @@
 
   update_uppercats();
-  ordering();
   update_global_rank();
 

trunk/admin/maintenance.php

@@ -46 +46 @@
     update_uppercats();
     update_category('all');
-    ordering();
     update_global_rank();
     invalidate_user_cache();

trunk/admin/site_update.php

@@ -447 +447 @@
     }
 
-    if ( isset( $conf['flip_picture_ext'][get_extension($filename)] ) 
+    if ( isset( $conf['flip_picture_ext'][get_extension($filename)] )
           and !isset($fs[$path]['tn_ext']) )
-    { // For a picture thumbnail is mandatory and for non picture element, 
+    { // For a picture thumbnail is mandatory and for non picture element,
       // thumbnail and representative are optionnal
       array_push(
@@ -471 +471 @@
         'storage_category_id' => $db_fulldirs[$dirname],
         );
-        
+
       if ( $_POST['privacy_level']!=0 )
       {
@@ -622 +622 @@
       . ' -->' );
     $start = get_moment();
-    ordering();
     update_global_rank();
     $template->append('footer_elements', '<!-- ordering categories : '

trunk/install/upgrade_1.3.1.php

@@ -68 +68 @@
   ADD COLUMN commentable enum('true','false') NOT NULL default 'true'
 ;",
-  
+
   "
 ALTER TABLE phpwebgallery_categories
@@ -88 +88 @@
   SET date_temp = date
 ;",
-  
+
   "
 ALTER TABLE phpwebgallery_comments
@@ -123 +123 @@
   SET date_temp = date
 ;",
-  
+
   "
 ALTER TABLE phpwebgallery_history
@@ -204 +204 @@
   ADD INDEX images_i5 (date_creation)
 ;",
-  
+
   "
 ALTER TABLE phpwebgallery_sessions
@@ -219 +219 @@
   SET expiration_temp = expiration
 ;",
-  
+
   "
 ALTER TABLE phpwebgallery_sessions
@@ -234 +234 @@
   DROP COLUMN expiration_temp
 ;",
-  
+
   "
 ALTER TABLE phpwebgallery_sites
@@ -248 +248 @@
 DROP TABLE phpwebgallery_user_category
 ;",
-  
+
   "
 ALTER TABLE phpwebgallery_users
@@ -273 +273 @@
   ADD UNIQUE users_ui1 (username)
 ;",
-  
+
   "
 CREATE TABLE phpwebgallery_rate (
@@ -339 +339 @@
 {
   $existing_indexes = array();
-  
+
   $query = '
 SHOW INDEX
@@ -526 +526 @@
 
 // refresh calculated datas
-ordering();
 update_global_rank();
 update_category();
@@ -584 +583 @@
   array(
     'all sub-categories of private categories become private',
-    
+
     'user permissions and group permissions have been erased',