Changeset 285


Ignore:
Timestamp:
Jan 17, 2004, 7:14:41 PM (20 years ago)
Author:
z0rglub
Message:

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

File:
1 edited

Legend:

Unmodified
Added
Removed

  • branches/release-1_3/admin/update.php

    r278 r285  
    8888  while ( $row = mysql_fetch_array( $result ) )
    8989  {
    90     $id = intval($row['id']);
    91     $sub_category_dirs[$id] = $row['dir'];
     90    $sub_category_dirs[$row['id']] = $row['dir'];
    9291  }
    9392 
    … …  
    419418// function also deletes the categories that are in the database and not in
    420419// the xml_file.
    421 function insert_remote_category( $xml_dir, $site_id, $id_uppercat, $level )
    422 {
    423   global $conf,$user;
    424 
     420function insert_remote_category( $xml_content, $site_id, $id_uppercat, $level )
     421{
     422  global $conf, $page, $user, $lang;
     423 
     424  $uppercats = '';
    425425  $output = '';
    426   $categories = array();
    427   $list_dirs = getChildren( $xml_dir, 'dir'.$level );
    428   for ( $i = 0; $i < sizeof( $list_dirs ); $i++ )
    429   {
    430     // is the category already existing ?
    431     $category_id = '';
    432     $dir = getAttribute( $list_dirs[$i], 'name' );
    433     $categories[$i] = $dir;
    434 
     426  // 0. retrieving informations on the category to display
     427  $cat_directory = '../galleries';
     428               
     429  if ( is_numeric( $id_uppercat ) )
     430  {
     431    $query = 'SELECT name,uppercats,dir';
     432    $query.= ' FROM '.PREFIX_TABLE.'categories';
     433    $query.= ' WHERE id = '.$id_uppercat;
     434    $query.= ';';
     435    $row = mysql_fetch_array( mysql_query( $query ) );
     436    $uppercats = $row['uppercats'];
     437    $name      = $row['name'];
     438
     439    // 1. display the category name to update
    435440    $src = '../template/'.$user['template'].'/admin/images/puce.gif';
    436     $output.= '<img src="'.$src.'" alt="&gt;" />';
    437     $output.= '<span style="font-weight:bold;">'.$dir.'</span>';
     441    $output = '<img src="'.$src.'" alt="&gt;" />';
     442    $output.= '<span style="font-weight:bold;">'.$name.'</span>';
     443    $output.= ' [ '.$row['dir'].' ]';
    438444    $output.= '<div class="retrait">';
    439445
    440     $query = 'SELECT id';
    441     $query.= ' FROM '.PREFIX_TABLE.'categories';
    442     $query.= ' WHERE site_id = '.$site_id;
    443     $query.= " AND dir = '".$dir."'";
    444     if ( $id_uppercat == 'NULL' )
    445     {
    446       $query.= ' AND id_uppercat IS NULL';
    447     }
    448     else
    449     {
    450       $query.= ' AND id_uppercat = '.$id_uppercat;
    451     }
    452     $query.= ';';
    453     $result = mysql_query( $query );
    454     if ( mysql_num_rows( $result ) == 0 )
    455     {
    456       $name = str_replace( '_', ' ', $dir );
    457       // we have to create the category
    458       $query = 'INSERT INTO '.PREFIX_TABLE.'categories';
    459       $query.= ' (name,dir,site_id,id_uppercat) VALUES ';
    460       $query.= "('".$name."','".$dir."',".$site_id;
    461       if ( !is_numeric( $id_uppercat ) )
    462       {
    463         $query.= ',NULL';
    464       }
    465       else
    466       {
    467         $query.= ','.$id_uppercat;
    468       }
    469       $query.= ');';
    470       mysql_query( $query );
    471       $category_id = mysql_insert_id();
    472     }
    473     else
    474     {
    475       // we get the already registered id
    476       $row = mysql_fetch_array( $result );
    477       $category_id = $row['id'];
    478     }
    479     $output.= insert_remote_image( $list_dirs[$i], $category_id );
    480     $output.= insert_remote_category( $list_dirs[$i], $site_id,
    481                                       $category_id, $level+1 );
    482     $output.= '</div>';
    483   }
    484   // we have to remove the categories of the database not present in the xml
    485   // file (ie deleted from the picture storage server)
    486   $query = 'SELECT dir,id';
     446    // 2. we search pictures of the category only if the update is for all
     447    //    or a cat_id is specified
     448    $output.= insert_remote_image( $xml_content, $id_uppercat );
     449  }
     450
     451  // $xml_dirs contains dir names contained in the xml file for this
     452  // id_uppercat
     453  $xml_dirs = array();
     454  $temp_dirs = getChildren( $xml_content, 'dir'.$level );
     455  foreach ( $temp_dirs as $temp_dir ) {
     456    array_push( $xml_dirs, getAttribute( $temp_dir, 'name' ) );
     457  }
     458
     459  // $database_dirs contains dir names contained in the database for this
     460  // id_uppercat and site_id
     461  $database_dirs = array();
     462  $query = 'SELECT id,dir';
    487463  $query.= ' FROM '.PREFIX_TABLE.'categories';
    488464  $query.= ' WHERE site_id = '.$site_id;
    489   if ( !is_numeric( $id_uppercat ) )
    490   {
    491     $query.= ' AND id_uppercat IS NULL';
    492   }
    493   else
    494   {
    495     $query.= ' AND id_uppercat = '.$id_uppercat;
    496   }
    497   $query.= ';';
    498   $result = mysql_query( $query );
    499   while ( $row = mysql_fetch_array( $result ) )
    500   {
    501     // is the category in the xml file ?
    502     if ( !in_array( $row['dir'], $categories ) )
    503     {
    504       delete_category( $row['id'] );
    505     }
    506   }
     465  if (!is_numeric($id_uppercat)) $query.= ' AND id_uppercat IS NULL';
     466  else                           $query.= ' AND id_uppercat = '.$id_uppercat;
     467  $query.= ' AND dir IS NOT NULL'; // virtual categories not taken
     468  $query.= ';';
     469  $result = mysql_query( $query );
     470  while ( $row = mysql_fetch_array( $result ) )
     471  {
     472    $database_dirs[$row['id']] = $row['dir'];
     473  }
     474 
     475  // 3. we have to remove the categories of the database not present anymore
     476  foreach ( $database_dirs as $id => $dir ) {
     477    if ( !in_array( $dir, $xml_dirs ) ) delete_category( $id );
     478  }
     479
     480  // array of new categories to insert
     481  $inserts = array();
     482 
     483  foreach ( $xml_dirs as $xml_dir ) {
     484    // 5. Is the category already existing ? we create a subcat if not
     485    //    existing
     486    $category_id = array_search( $xml_dir, $database_dirs );
     487    if ( !is_numeric( $category_id ) )
     488    {
     489      $name = str_replace( '_', ' ', $xml_dir );
     490
     491      $value = "('".$xml_dir."','".$name."',".$site_id;
     492      if ( !is_numeric( $id_uppercat ) ) $value.= ',NULL';
     493      else                               $value.= ','.$id_uppercat;
     494      $value.= ",'undef'";
     495      $value.= ')';
     496      array_push( $inserts, $value );
     497    }
     498  }
     499
     500  // we have to create the category
     501  if ( count( $inserts ) > 0 )
     502  {
     503    $query = 'INSERT INTO '.PREFIX_TABLE.'categories';
     504    $query.= ' (dir,name,site_id,id_uppercat,uppercats) VALUES ';
     505    $query.= implode( ',', $inserts );
     506    $query.= ';';
     507    mysql_query( $query );
     508    // updating uppercats field
     509    $query = 'UPDATE '.PREFIX_TABLE.'categories';
     510    $query.= ' SET uppercats = ';
     511    if ( $uppercats != '' ) $query.= "CONCAT('".$uppercats."',',',id)";
     512    else                    $query.= 'id';
     513    $query.= ' WHERE id_uppercat ';
     514    if (!is_numeric($id_uppercat)) $query.= 'IS NULL';
     515    else                           $query.= '= '.$id_uppercat;
     516    $query.= ';';
     517    mysql_query( $query );
     518  }
     519
     520  // Recursive call on the sub-categories (not virtual ones)
     521  $query = 'SELECT id,dir';
     522  $query.= ' FROM '.PREFIX_TABLE.'categories';
     523  $query.= ' WHERE site_id = '.$site_id;
     524  if (!is_numeric($id_uppercat)) $query.= ' AND id_uppercat IS NULL';
     525  else                           $query.= ' AND id_uppercat = '.$id_uppercat;
     526  $query.= ' AND dir IS NOT NULL'; // virtual categories not taken
     527  $query.= ';';
     528  $result = mysql_query( $query );
     529  while ( $row = mysql_fetch_array( $result ) )
     530  {
     531    $database_dirs[$row['dir']] = $row['id'];
     532  }
     533  foreach ( $temp_dirs as $temp_dir ) {
     534    $dir = getAttribute( $temp_dir, 'name' );
     535    $id_uppercat = $database_dirs[$dir];
     536    $output.= insert_remote_category( $temp_dir, $site_id,
     537                                      $id_uppercat,$level+1 );
     538  }
     539
     540  if ( is_numeric( $id_uppercat ) ) $output.= '</div>';
    507541
    508542  return $output;
    509543}
    510        
     544
    511545// insert_remote_image searchs the "root" node of the xml_dir given and
    512546// insert the contained pictures if the are not in the database yet.
    … …  
    517551  $output = '';
    518552  $root = getChild( $xml_dir, 'root' );
    519   $pictures = array();
     553
     554  $fs_pictures = array();
    520555  $xml_pictures = getChildren( $root, 'picture' );
    521   for ( $j = 0; $j < sizeof( $xml_pictures ); $j++ )
    522   {
    523     //<picture file="albatros.jpg" tn_ext="png" date="2002-04-14"
    524     //  filesize="35" width="640" height="480" />
    525     $file     = getAttribute( $xml_pictures[$j], 'file' );
    526     $tn_ext   = getAttribute( $xml_pictures[$j], 'tn_ext' );
    527     $date     = getAttribute( $xml_pictures[$j], 'date' );
    528     $filesize = getAttribute( $xml_pictures[$j], 'filesize' );
    529     $width    = getAttribute( $xml_pictures[$j], 'width' );
    530     $height   = getAttribute( $xml_pictures[$j], 'height' );
    531                        
    532     $pictures[$j] = $file;
    533                        
     556  foreach ( $xml_pictures as $xml_picture ) {
     557    array_push( $fs_pictures, getAttribute( $xml_picture, 'file' ) );
     558  }
     559 
     560  // we have to delete all the images from the database that are not in the
     561  // directory anymore (not in the XML anymore)
     562  $query = 'SELECT id,file';
     563  $query.= ' FROM '.PREFIX_TABLE.'images';
     564  $query.= ' WHERE storage_category_id = '.$category_id;
     565  $query.= ';';
     566  $result = mysql_query( $query );
     567  while ( $row = mysql_fetch_array( $result ) )
     568  {
     569    if ( !in_array( $row['file'], $fs_pictures ) )
     570    {
     571      $output.= $row['file'];
     572      $output.= ' <span style="font-weight:bold;">';
     573      $output.= $lang['update_disappeared'].'</span><br />';
     574      delete_image( $row['id'] );
     575    }
     576  }
     577
     578  $database_pictures = array();
     579  $query = 'SELECT file';
     580  $query.= ' FROM '.PREFIX_TABLE.'images';
     581  $query.= ' WHERE storage_category_id = '.$category_id;
     582  $query.= ';';
     583  $result = mysql_query( $query );
     584  while ( $row = mysql_fetch_array( $result ) )
     585  {
     586    array_push( $database_pictures, $row['file'] );
     587  }
     588
     589  $inserts = array();
     590  $xml_pictures = getChildren( $root, 'picture' );
     591  foreach ( $xml_pictures as $xml_picture ) {
     592    // <picture file="albatros.jpg" tn_ext="png" filesize="35" width="640"
     593    // height="480" />
     594    $file = getAttribute( $xml_picture, 'file' );
     595
    534596    // is the picture already existing in the database ?
    535     $query = 'SELECT id,tn_ext';
    536     $query.= ' FROM '.PREFIX_TABLE.'images';
    537     $query.= ' WHERE storage_category_id = '.$category_id;
    538     $query.= " AND file = '".$file."'";
    539     $query.= ';';
    540     $result = mysql_query( $query );
    541     $query = '';
    542     if ( mysql_num_rows( $result ) == 0 )
    543     {
    544       $query = 'INSERT INTO '.PREFIX_TABLE.'images';
    545       $query.= ' (file,storage_category_id,date_available,tn_ext';
    546       $query.= ',filesize,width,height)';
    547       $query.= ' VALUES (';
    548       $query.= "'".$file."'";
    549       $query.= ",'".$category_id."'";
    550       $query.= ",'".$date."'";
    551       $query.= ",'".$tn_ext."'";
    552       $query.= ",'".$filesize."'";
    553       $query.= ",'".$width."'";
    554       $query.= ",'".$height."'";
    555       $query.= ')';
    556       $query.= ';';
    557       mysql_query( $query );
    558       // retrieving the id of newly inserted picture
    559       $query = 'SELECT id';
    560       $query.= ' FROM '.PREFIX_TABLE.'images';
    561       $query.= ' WHERE storage_category_id = '.$category_id;
    562       $query.= " AND file = '".$file."'";
    563       $query.= ';';
    564       list( $image_id ) = mysql_fetch_array( mysql_query( $query ) );
    565       // adding the link between this picture and its storage category
    566       $query = 'INSERT INTO '.PREFIX_TABLE.'image_category';
    567       $query.= ' (image_id,category_id) VALUES ';
    568       $query.= ' ('.$image_id.','.$category_id.')';
    569       $query.= ';';
    570       mysql_query( $query );
    571 
     597    if ( !in_array( $file, $database_pictures ) )
     598    {
     599      $tn_ext = getAttribute( $xml_picture, 'tn_ext' );
     600      // (file, storage_category_id, date_available, tn_ext, filesize,
     601      // width, height)
     602      $value = '(';
     603      $value.= "'".$file."'";
     604      $value.= ','.$category_id;
     605      $value.= ",'".date( 'Y-m-d' )."'";
     606      $value.= ",'".$tn_ext."'";
     607      $value.= ','.getAttribute( $xml_picture, 'filesize' );
     608      $value.= ','.getAttribute( $xml_picture, 'width' );
     609      $value.= ','.getAttribute( $xml_picture, 'height' );
     610      $value.= ')';
     611
     612      $count_new++;
    572613      $output.= $file;
    573614      $output.= ' <span style="font-weight:bold;">';
    574615      $output.= $lang['update_research_added'].'</span>';
    575       $output.= ' ('.$lang['update_research_tn_ext'].' '.$tn_ext.')<br />';
    576 
    577       $count_new++;
    578     }
    579     else
    580     {
    581       // is the tn_ext the same in the xml file and in the database ?
    582       $row = mysql_fetch_array( $result );
    583       if ( $row['tn_ext'] != $tn_ext )
    584       {
    585         $query = 'UPDATE '.PREFIX_TABLE.'images';
    586         $query.= ' SET';
    587         $query.= " tn_ext = '".$tn_ext."'";
    588         $query.= ' WHERE storage_category_id = '.$category_id;
    589         $query.= " AND file = '".$file."'";
    590         $query.= ';';
    591       }
    592     }
    593     // execution of the query
    594     if ( $query != '' )
    595     {
    596       mysql_query( $query );
    597     }
    598   }
    599   // we have to remove the pictures of the database not present in the xml file
    600   // (ie deleted from the picture storage server)
    601   $query = 'SELECT id,file';
    602   $query.= ' FROM '.PREFIX_TABLE.'images';
    603   $query.= ' WHERE storage_category_id = '.$category_id;
    604   $query.= ';';
    605   $result = mysql_query( $query );
    606   while ( $row = mysql_fetch_array( $result ) )
    607   {
    608     // is the file in the xml file ?
    609     if ( !in_array( $row['file'], $pictures ) )
    610     {
    611       delete_image( $row['id'] );
    612     }
    613   }
     616      $output.= ' ('.$lang['update_research_tn_ext'].' '.$tn_ext.')';
     617      $output.= '<br />';
     618      array_push( $inserts, $value );
     619    }
     620  }
     621
     622  if ( count( $inserts ) > 0 )
     623  {
     624    // inserts all found pictures
     625    $query = 'INSERT INTO '.PREFIX_TABLE.'images';
     626    $query.= ' (file,storage_category_id,date_available,tn_ext';
     627    $query.= ',filesize,width,height)';
     628    $query.= ' VALUES ';
     629    $query.= implode( ',', $inserts );
     630    $query.= ';';
     631    mysql_query( $query );
     632
     633    // what are the ids of the pictures in the $category_id ?
     634    $ids = array();
     635
     636    $query = 'SELECT id';
     637    $query.= ' FROM '.PREFIX_TABLE.'images';
     638    $query.= ' WHERE storage_category_id = '.$category_id;
     639    $query.= ';';
     640    $result = mysql_query( $query );
     641    while ( $row = mysql_fetch_array( $result ) )
     642    {
     643      array_push( $ids, $row['id'] );
     644    }
     645
     646    // recreation of the links between this storage category pictures and
     647    // its storage category
     648    $query = 'DELETE FROM '.PREFIX_TABLE.'image_category';
     649    $query.= ' WHERE category_id = '.$category_id;
     650    $query.= ' AND image_id IN ('.implode( ',', $ids ).')';
     651    $query.= ';';
     652    mysql_query( $query );
     653
     654    $query = 'INSERT INTO '.PREFIX_TABLE.'image_category';
     655    $query.= '(category_id,image_id) VALUES ';
     656    foreach ( $ids as $num => $image_id ) {
     657      if ( $num > 0 ) $query.= ',';
     658      $query.= '('.$category_id.','.$image_id.')';
     659    }
     660    $query.= ';';
     661    mysql_query( $query );
     662  }
     663
    614664  return $output;
    615665}
    … …  
    663713  $count_deleted = 0;
    664714  $vtp->addSession( $sub, 'remote_update' );
    665 
     715 
     716  $start = get_moment();
    666717  remote_images();
     718  $end = get_moment();
     719  echo get_elapsed_time( $start, $end ).' for remote_images<br />';
     720 
    667721  $vtp->setVar( $sub, 'remote_update.count_new', $count_new );
    668722  $vtp->setVar( $sub, 'remote_update.count_deleted', $count_deleted );
    … …  
    675729     or @is_file( './listing.xml' ) )
    676730{
     731  $start = get_moment();
    677732  update_category( 'all' );
     733  $end = get_moment();
     734  echo get_elapsed_time( $start, $end ).' for update_category( all )<br />';
     735
     736  $start = get_moment();
    678737  synchronize_all_users();
     738  $end = get_moment();
     739  echo get_elapsed_time( $start, $end ).' for synchronize_all_users<br />';
    679740}
    680741//----------------------------------------------------------- sending html code
Note: See TracChangeset for help on using the changeset viewer.