From c058388379edce8b0a71da8f1172caf9d29946b2 Mon Sep 17 00:00:00 2001 From: z0rglub Date: Sat, 17 Jan 2004 18:14:41 +0000 Subject: 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 --- admin/update.php | 363 ++++++++++++++++++++++++++++++++----------------------- 1 file 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.= '>'; - $output.= ''.$dir.''; + $output = '>'; + $output.= ''.$name.''; + $output.= ' [ '.$row['dir'].' ]'; $output.= '
'; - $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.= '
'; + // 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.= ''; + 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 ) ) { - // - $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.= ' '; - $output.= $lang['update_research_added'].''; - $output.= ' ('.$lang['update_research_tn_ext'].' '.$tn_ext.')
'; - - $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'].'
'; + 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 ) { + // + $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.= ' '; + $output.= $lang['update_research_added'].''; + $output.= ' ('.$lang['update_research_tn_ext'].' '.$tn_ext.')'; + $output.= '
'; + 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
'; + $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 )
'; + + $start = get_moment(); synchronize_all_users(); + $end = get_moment(); + echo get_elapsed_time( $start, $end ).' for synchronize_all_users
'; } //----------------------------------------------------------- sending html code $vtp->Parse( $handle , 'sub', $sub ); -- cgit v1.2.3