Updating insert_remote* function the same way the local ones : really faster !

git-svn-id: http://piwigo.org/svn/branches/release-1_3@285 68402e56-0260-453c-a942-63ccdbb3a9ee

1 files changed, 212 insertions, 151 deletions

diff --git a/admin/update.php b/admin/update.php
index 19a7ba85a..ddeeae98c 100644
--- a/admin/update.php
+++ b/admin/update.php
@@ -87,8 +87,7 @@ function insert_local_category( $id_uppercat )
   $result = mysql_query( $query );
   while ( $row = mysql_fetch_array( $result ) )
   {
-    $id = intval($row['id']);
-    $sub_category_dirs[$id] = $row['dir'];
+    $sub_category_dirs[$row['id']] = $row['dir'];
   }
   
   // 3. we have to remove the categories of the database not present anymore
@@ -418,96 +417,131 @@ function remote_images()
 // insert the contained categories if the are not in the database yet. The
 // function also deletes the categories that are in the database and not in
 // the xml_file.
-function insert_remote_category( $xml_dir, $site_id, $id_uppercat, $level )
+function insert_remote_category( $xml_content, $site_id, $id_uppercat, $level )
 {
-  global $conf,$user;
-
+  global $conf, $page, $user, $lang;
+ 
+  $uppercats = '';
   $output = '';
-  $categories = array();
-  $list_dirs = getChildren( $xml_dir, 'dir'.$level );
-  for ( $i = 0; $i < sizeof( $list_dirs ); $i++ )
+  // 0. retrieving informations on the category to display
+  $cat_directory = '../galleries';
+		
+  if ( is_numeric( $id_uppercat ) )
   {
-    // is the category already existing ?
-    $category_id = '';
-    $dir = getAttribute( $list_dirs[$i], 'name' );
-    $categories[$i] = $dir;
+    $query = 'SELECT name,uppercats,dir';
+    $query.= ' FROM '.PREFIX_TABLE.'categories';
+    $query.= ' WHERE id = '.$id_uppercat;
+    $query.= ';';
+    $row = mysql_fetch_array( mysql_query( $query ) );
+    $uppercats = $row['uppercats'];
+    $name      = $row['name'];
 
+    // 1. display the category name to update
     $src = '../template/'.$user['template'].'/admin/images/puce.gif';
-    $output.= '<img src="'.$src.'" alt="&gt;" />';
-    $output.= '<span style="font-weight:bold;">'.$dir.'</span>';
+    $output = '<img src="'.$src.'" alt="&gt;" />';
+    $output.= '<span style="font-weight:bold;">'.$name.'</span>';
+    $output.= ' [ '.$row['dir'].' ]';
     $output.= '<div class="retrait">';
 
-    $query = 'SELECT id';
-    $query.= ' FROM '.PREFIX_TABLE.'categories';
-    $query.= ' WHERE site_id = '.$site_id;
-    $query.= " AND dir = '".$dir."'";
-    if ( $id_uppercat == 'NULL' )
-    {
-      $query.= ' AND id_uppercat IS NULL';
-    }
-    else
-    {
-      $query.= ' AND id_uppercat = '.$id_uppercat;
-    }
-    $query.= ';';
-    $result = mysql_query( $query );
-    if ( mysql_num_rows( $result ) == 0 )
-    {
-      $name = str_replace( '_', ' ', $dir );
-      // we have to create the category
-      $query = 'INSERT INTO '.PREFIX_TABLE.'categories';
-      $query.= ' (name,dir,site_id,id_uppercat) VALUES ';
-      $query.= "('".$name."','".$dir."',".$site_id;
-      if ( !is_numeric( $id_uppercat ) )
-      {
-        $query.= ',NULL';
-      }
-      else
-      {
-        $query.= ','.$id_uppercat;
-      }
-      $query.= ');';
-      mysql_query( $query );
-      $category_id = mysql_insert_id();
-    }
-    else
-    {
-      // we get the already registered id
-      $row = mysql_fetch_array( $result );
-      $category_id = $row['id'];
-    }
-    $output.= insert_remote_image( $list_dirs[$i], $category_id );
-    $output.= insert_remote_category( $list_dirs[$i], $site_id,
-                                      $category_id, $level+1 );
-    $output.= '</div>';
+    // 2. we search pictures of the category only if the update is for all
+    //    or a cat_id is specified
+    $output.= insert_remote_image( $xml_content, $id_uppercat );
+  }
+
+  // $xml_dirs contains dir names contained in the xml file for this
+  // id_uppercat
+  $xml_dirs = array();
+  $temp_dirs = getChildren( $xml_content, 'dir'.$level );
+  foreach ( $temp_dirs as $temp_dir ) {
+    array_push( $xml_dirs, getAttribute( $temp_dir, 'name' ) );
   }
-  // we have to remove the categories of the database not present in the xml
-  // file (ie deleted from the picture storage server)
-  $query = 'SELECT dir,id';
+
+  // $database_dirs contains dir names contained in the database for this
+  // id_uppercat and site_id
+  $database_dirs = array();
+  $query = 'SELECT id,dir';
   $query.= ' FROM '.PREFIX_TABLE.'categories';
   $query.= ' WHERE site_id = '.$site_id;
-  if ( !is_numeric( $id_uppercat ) )
+  if (!is_numeric($id_uppercat)) $query.= ' AND id_uppercat IS NULL';
+  else                           $query.= ' AND id_uppercat = '.$id_uppercat;
+  $query.= ' AND dir IS NOT NULL'; // virtual categories not taken
+  $query.= ';';
+  $result = mysql_query( $query );
+  while ( $row = mysql_fetch_array( $result ) )
   {
-    $query.= ' AND id_uppercat IS NULL';
+    $database_dirs[$row['id']] = $row['dir'];
   }
-  else
+  
+  // 3. we have to remove the categories of the database not present anymore
+  foreach ( $database_dirs as $id => $dir ) {
+    if ( !in_array( $dir, $xml_dirs ) ) delete_category( $id );
+  }
+
+  // array of new categories to insert
+  $inserts = array();
+  
+  foreach ( $xml_dirs as $xml_dir ) {
+    // 5. Is the category already existing ? we create a subcat if not
+    //    existing
+    $category_id = array_search( $xml_dir, $database_dirs );
+    if ( !is_numeric( $category_id ) )
+    {
+      $name = str_replace( '_', ' ', $xml_dir );
+
+      $value = "('".$xml_dir."','".$name."',".$site_id;
+      if ( !is_numeric( $id_uppercat ) ) $value.= ',NULL';
+      else                               $value.= ','.$id_uppercat;
+      $value.= ",'undef'";
+      $value.= ')';
+      array_push( $inserts, $value );
+    }
+  }
+
+  // we have to create the category
+  if ( count( $inserts ) > 0 )
   {
-    $query.= ' AND id_uppercat = '.$id_uppercat;
+    $query = 'INSERT INTO '.PREFIX_TABLE.'categories';
+    $query.= ' (dir,name,site_id,id_uppercat,uppercats) VALUES ';
+    $query.= implode( ',', $inserts );
+    $query.= ';';
+    mysql_query( $query );
+    // updating uppercats field
+    $query = 'UPDATE '.PREFIX_TABLE.'categories';
+    $query.= ' SET uppercats = ';
+    if ( $uppercats != '' ) $query.= "CONCAT('".$uppercats."',',',id)";
+    else                    $query.= 'id';
+    $query.= ' WHERE id_uppercat ';
+    if (!is_numeric($id_uppercat)) $query.= 'IS NULL';
+    else                           $query.= '= '.$id_uppercat;
+    $query.= ';';
+    mysql_query( $query );
   }
+
+  // Recursive call on the sub-categories (not virtual ones)
+  $query = 'SELECT id,dir';
+  $query.= ' FROM '.PREFIX_TABLE.'categories';
+  $query.= ' WHERE site_id = '.$site_id;
+  if (!is_numeric($id_uppercat)) $query.= ' AND id_uppercat IS NULL';
+  else                           $query.= ' AND id_uppercat = '.$id_uppercat;
+  $query.= ' AND dir IS NOT NULL'; // virtual categories not taken
   $query.= ';';
   $result = mysql_query( $query );
   while ( $row = mysql_fetch_array( $result ) )
   {
-    // is the category in the xml file ?
-    if ( !in_array( $row['dir'], $categories ) )
-    {
-      delete_category( $row['id'] );
-    }
+    $database_dirs[$row['dir']] = $row['id'];
+  }
+  foreach ( $temp_dirs as $temp_dir ) {
+    $dir = getAttribute( $temp_dir, 'name' );
+    $id_uppercat = $database_dirs[$dir];
+    $output.= insert_remote_category( $temp_dir, $site_id,
+                                      $id_uppercat,$level+1 );
   }
 
+  if ( is_numeric( $id_uppercat ) ) $output.= '</div>';
+
   return $output;
 }
-	
+
 // insert_remote_image searchs the "root" node of the xml_dir given and
 // insert the contained pictures if the are not in the database yet.
 function insert_remote_image( $xml_dir, $category_id )
@@ -516,101 +550,117 @@ function insert_remote_image( $xml_dir, $category_id )
 
   $output = '';
   $root = getChild( $xml_dir, 'root' );
-  $pictures = array();
+
+  $fs_pictures = array();
   $xml_pictures = getChildren( $root, 'picture' );
-  for ( $j = 0; $j < sizeof( $xml_pictures ); $j++ )
+  foreach ( $xml_pictures as $xml_picture ) {
+    array_push( $fs_pictures, getAttribute( $xml_picture, 'file' ) );
+  }
+  
+  // we have to delete all the images from the database that are not in the
+  // directory anymore (not in the XML anymore)
+  $query = 'SELECT id,file';
+  $query.= ' FROM '.PREFIX_TABLE.'images';
+  $query.= ' WHERE storage_category_id = '.$category_id;
+  $query.= ';';
+  $result = mysql_query( $query );
+  while ( $row = mysql_fetch_array( $result ) )
   {
-    //<picture file="albatros.jpg" tn_ext="png" date="2002-04-14"
-    //  filesize="35" width="640" height="480" />
-    $file     = getAttribute( $xml_pictures[$j], 'file' );
-    $tn_ext   = getAttribute( $xml_pictures[$j], 'tn_ext' );
-    $date     = getAttribute( $xml_pictures[$j], 'date' ); 
-    $filesize = getAttribute( $xml_pictures[$j], 'filesize' );
-    $width    = getAttribute( $xml_pictures[$j], 'width' );
-    $height   = getAttribute( $xml_pictures[$j], 'height' );
-			
-    $pictures[$j] = $file;
-			
-    // is the picture already existing in the database ?
-    $query = 'SELECT id,tn_ext';
-    $query.= ' FROM '.PREFIX_TABLE.'images';
-    $query.= ' WHERE storage_category_id = '.$category_id;
-    $query.= " AND file = '".$file."'";
-    $query.= ';';
-    $result = mysql_query( $query );
-    $query = '';
-    if ( mysql_num_rows( $result ) == 0 )
+    if ( !in_array( $row['file'], $fs_pictures ) )
     {
-      $query = 'INSERT INTO '.PREFIX_TABLE.'images';
-      $query.= ' (file,storage_category_id,date_available,tn_ext';
-      $query.= ',filesize,width,height)';
-      $query.= ' VALUES (';
-      $query.= "'".$file."'";
-      $query.= ",'".$category_id."'";
-      $query.= ",'".$date."'";
-      $query.= ",'".$tn_ext."'";
-      $query.= ",'".$filesize."'";
-      $query.= ",'".$width."'";
-      $query.= ",'".$height."'";
-      $query.= ')';
-      $query.= ';';
-      mysql_query( $query );
-      // retrieving the id of newly inserted picture
-      $query = 'SELECT id';
-      $query.= ' FROM '.PREFIX_TABLE.'images';
-      $query.= ' WHERE storage_category_id = '.$category_id;
-      $query.= " AND file = '".$file."'";
-      $query.= ';';
-      list( $image_id ) = mysql_fetch_array( mysql_query( $query ) );
-      // adding the link between this picture and its storage category
-      $query = 'INSERT INTO '.PREFIX_TABLE.'image_category';
-      $query.= ' (image_id,category_id) VALUES ';
-      $query.= ' ('.$image_id.','.$category_id.')';
-      $query.= ';';
-      mysql_query( $query );
-
-      $output.= $file;
+      $output.= $row['file'];
       $output.= ' <span style="font-weight:bold;">';
-      $output.= $lang['update_research_added'].'</span>';
-      $output.= ' ('.$lang['update_research_tn_ext'].' '.$tn_ext.')<br />';
-
-      $count_new++;
-    }
-    else
-    {
-      // is the tn_ext the same in the xml file and in the database ?
-      $row = mysql_fetch_array( $result );
-      if ( $row['tn_ext'] != $tn_ext )
-      {
-        $query = 'UPDATE '.PREFIX_TABLE.'images';
-        $query.= ' SET';
-        $query.= " tn_ext = '".$tn_ext."'";
-        $query.= ' WHERE storage_category_id = '.$category_id;
-        $query.= " AND file = '".$file."'";
-        $query.= ';';
-      }
-    }
-    // execution of the query
-    if ( $query != '' )
-    {
-      mysql_query( $query );
+      $output.= $lang['update_disappeared'].'</span><br />';
+      delete_image( $row['id'] );
     }
   }
-  // we have to remove the pictures of the database not present in the xml file
-  // (ie deleted from the picture storage server)
-  $query = 'SELECT id,file';
+
+  $database_pictures = array();
+  $query = 'SELECT file';
   $query.= ' FROM '.PREFIX_TABLE.'images';
   $query.= ' WHERE storage_category_id = '.$category_id;
   $query.= ';';
   $result = mysql_query( $query );
   while ( $row = mysql_fetch_array( $result ) )
   {
-    // is the file in the xml file ?
-    if ( !in_array( $row['file'], $pictures ) )
+    array_push( $database_pictures, $row['file'] );
+  }
+
+  $inserts = array();
+  $xml_pictures = getChildren( $root, 'picture' );
+  foreach ( $xml_pictures as $xml_picture ) {
+    // <picture file="albatros.jpg" tn_ext="png" filesize="35" width="640"
+    // height="480" />
+    $file = getAttribute( $xml_picture, 'file' );
+
+    // is the picture already existing in the database ?
+    if ( !in_array( $file, $database_pictures ) )
     {
-      delete_image( $row['id'] );
+      $tn_ext = getAttribute( $xml_picture, 'tn_ext' );
+      // (file, storage_category_id, date_available, tn_ext, filesize,
+      // width, height)
+      $value = '(';
+      $value.= "'".$file."'";
+      $value.= ','.$category_id;
+      $value.= ",'".date( 'Y-m-d' )."'";
+      $value.= ",'".$tn_ext."'";
+      $value.= ','.getAttribute( $xml_picture, 'filesize' );
+      $value.= ','.getAttribute( $xml_picture, 'width' );
+      $value.= ','.getAttribute( $xml_picture, 'height' );
+      $value.= ')';
+
+      $count_new++;
+      $output.= $file;
+      $output.= ' <span style="font-weight:bold;">';
+      $output.= $lang['update_research_added'].'</span>';
+      $output.= ' ('.$lang['update_research_tn_ext'].' '.$tn_ext.')';
+      $output.= '<br />';
+      array_push( $inserts, $value );
+    }
+  }
+
+  if ( count( $inserts ) > 0 )
+  {
+    // inserts all found pictures
+    $query = 'INSERT INTO '.PREFIX_TABLE.'images';
+    $query.= ' (file,storage_category_id,date_available,tn_ext';
+    $query.= ',filesize,width,height)';
+    $query.= ' VALUES ';
+    $query.= implode( ',', $inserts );
+    $query.= ';';
+    mysql_query( $query );
+
+    // what are the ids of the pictures in the $category_id ?
+    $ids = array();
+
+    $query = 'SELECT id';
+    $query.= ' FROM '.PREFIX_TABLE.'images';
+    $query.= ' WHERE storage_category_id = '.$category_id;
+    $query.= ';';
+    $result = mysql_query( $query );
+    while ( $row = mysql_fetch_array( $result ) )
+    {
+      array_push( $ids, $row['id'] );
+    }
+
+    // recreation of the links between this storage category pictures and
+    // its storage category
+    $query = 'DELETE FROM '.PREFIX_TABLE.'image_category';
+    $query.= ' WHERE category_id = '.$category_id;
+    $query.= ' AND image_id IN ('.implode( ',', $ids ).')';
+    $query.= ';';
+    mysql_query( $query );
+
+    $query = 'INSERT INTO '.PREFIX_TABLE.'image_category';
+    $query.= '(category_id,image_id) VALUES ';
+    foreach ( $ids as $num => $image_id ) {
+      if ( $num > 0 ) $query.= ',';
+      $query.= '('.$category_id.','.$image_id.')';
     }
+    $query.= ';';
+    mysql_query( $query );
   }
+
   return $output;
 }
 //----------------------------------------------------- template initialization
@@ -662,8 +712,12 @@ if ( @is_file( './listing.xml' ) )
   $count_new = 0;
   $count_deleted = 0;
   $vtp->addSession( $sub, 'remote_update' );
-
+  
+  $start = get_moment();
   remote_images();
+  $end = get_moment();
+  echo get_elapsed_time( $start, $end ).' for remote_images<br />';
+  
   $vtp->setVar( $sub, 'remote_update.count_new', $count_new );
   $vtp->setVar( $sub, 'remote_update.count_deleted', $count_deleted );
 
@@ -674,8 +728,15 @@ if ( isset( $_GET['update'] )
      or isset( $page['cat'] )
      or @is_file( './listing.xml' ) )
 {
+  $start = get_moment();
   update_category( 'all' );
+  $end = get_moment();
+  echo get_elapsed_time( $start, $end ).' for update_category( all )<br />';
+
+  $start = get_moment();
   synchronize_all_users();
+  $end = get_moment();
+  echo get_elapsed_time( $start, $end ).' for synchronize_all_users<br />';
 }
 //----------------------------------------------------------- sending html code
 $vtp->Parse( $handle , 'sub', $sub );