diff options
Diffstat (limited to '')
| -rw-r--r-- | admin/cat_list.php | 2 | ||||
| -rw-r--r-- | admin/include/functions.php | 225 | ||||
| -rw-r--r-- | admin/include/functions_metadata.php | 149 | ||||
| -rw-r--r-- | admin/update.php | 43 |
4 files changed, 218 insertions, 201 deletions
diff --git a/admin/cat_list.php b/admin/cat_list.php index eac3869b3..b5404ea0c 100644 --- a/admin/cat_list.php +++ b/admin/cat_list.php @@ -208,6 +208,7 @@ UPDATE '.CATEGORIES_TABLE.' array_push($categories, $current); array_shift($categories); } + update_global_rank($_GET['parent_id']); } else if (isset($_GET['down']) and is_numeric($_GET['down'])) { @@ -267,6 +268,7 @@ UPDATE '.CATEGORIES_TABLE.' array_unshift($categories, $current); array_pop($categories); } + update_global_rank($_GET['parent_id']); } reset($categories); // +-----------------------------------------------------------------------+ diff --git a/admin/include/functions.php b/admin/include/functions.php index c78df307e..9df0303c0 100644 --- a/admin/include/functions.php +++ b/admin/include/functions.php @@ -466,66 +466,59 @@ SELECT category_id, COUNT(image_id) AS count, max(date_available) AS date_last GROUP BY category_id ;'; $result = pwg_query( $query ); + $datas = array(); while ( $row = mysql_fetch_array( $result ) ) { array_push($cat_ids, $row['category_id']); - $query = ' -UPDATE '.CATEGORIES_TABLE.' - SET date_last = \''.$row['date_last'].'\' - , nb_images = '.$row['count'].' - WHERE id = '.$row['category_id'].' -;'; - pwg_query($query); + array_push($datas, array('id' => $row['category_id'], + 'date_last' => $row['date_last'], + 'count' => $row['count'])); } + $fields = array('primary' => array('id'), + 'update' => array('date_last', 'count')); + mass_updates(CATEGORIES_TABLE, $fields, $datas); if (count($cat_ids) > 0) { + // find all categories where the setted representative is not possible $query = ' -SELECT id, representative_picture_id - FROM '.CATEGORIES_TABLE.' +SELECT id + FROM '.CATEGORIES_TABLE.' LEFT JOIN '.IMAGE_CATEGORY_TABLE.' + ON id = category_id AND representative_picture_id = image_id WHERE representative_picture_id IS NOT NULL AND id IN ('.implode(',', $cat_ids).') + AND category_id IS NULL ;'; $result = pwg_query($query); while ($row = mysql_fetch_array($result)) { + // set a new representative element for this category $query = ' SELECT image_id FROM '.IMAGE_CATEGORY_TABLE.' WHERE category_id = '.$row['id'].' - AND image_id = '.$row['representative_picture_id'].' + ORDER BY RAND() + LIMIT 0,1 ;'; $sub_result = pwg_query($query); - if (mysql_num_rows($sub_result) == 0) + if (mysql_num_rows($sub_result) > 0) { - // set a new representative element for this category + list($representative) = mysql_fetch_array($sub_result); $query = ' -SELECT image_id - FROM '.IMAGE_CATEGORY_TABLE.' - WHERE category_id = '.$row['id'].' - ORDER BY RAND() - LIMIT 0,1 -;'; - $sub_sub_result = pwg_query($query); - if (mysql_num_rows($sub_sub_result) > 0) - { - list($representative) = mysql_fetch_array(pwg_query($query)); - $query = ' UPDATE '.CATEGORIES_TABLE.' SET representative_picture_id = '.$representative.' WHERE id = '.$row['id'].' ;'; - pwg_query($query); - } - else - { - $query = ' + pwg_query($query); + } + else + { + $query = ' UPDATE '.CATEGORIES_TABLE.' SET representative_picture_id = NULL WHERE id = '.$row['id'].' ;'; - pwg_query($query); - } + pwg_query($query); } } } @@ -790,18 +783,18 @@ function my_error($header, $echo = true) * inserts multiple lines in a table * * @param string table_name - * @param array dbields + * @param array dbfields * @param array inserts * @return void */ -function mass_inserts($table_name, $dbfields, $inserts) +function mass_inserts($table_name, $dbfields, $datas) { // inserts all found categories $query = ' INSERT INTO '.$table_name.' ('.implode(',', $dbfields).') VALUES'; - foreach ($inserts as $insert_id => $insert) + foreach ($datas as $insert_id => $insert) { $query.= ' '; @@ -832,4 +825,170 @@ INSERT INTO '.$table_name.' ;'; pwg_query($query); } + +/** + * updates multiple lines in a table + * + * @param string table_name + * @param array dbfields + * @param array datas + * @return void + */ +function mass_updates($tablename, $dbfields, $datas) +{ + // depending on the MySQL version, we use the multi table update or N + // update queries + $query = 'SELECT VERSION() AS version;'; + $row = mysql_fetch_array(pwg_query($query)); + if (version_compare($row['version'],'4.0.4') < 0) + { + // MySQL is prior to version 4.0.4, multi table update feature is not + // available + echo 'MySQL is prior to version 4.0.4, multi table update feature is not available<br />'; + foreach ($datas as $data) + { + $query = ' +UPDATE '.$tablename.' + SET '; + foreach ($dbfields['update'] as $num => $key) + { + if ($num >= 1) + { + $query.= ",\n "; + } + $query.= $key.' = '; + if (isset($data[$key])) + { + $query.= '\''.$data[$key].'\''; + } + else + { + $query.= 'NULL'; + } + } + $query.= ' + WHERE '; + foreach ($dbfields['primary'] as $num => $key) + { + if ($num > 1) + { + $query.= ' AND '; + } + $query.= $key.' = \''.$data[$key].'\''; + } + $query.= ' +;'; + pwg_query($query); + } + } + else + { + // creation of the temporary table + $query = ' +DESCRIBE '.$tablename.' +;'; + $result = pwg_query($query); + $columns = array(); + $all_fields = array_merge($dbfields['primary'], $dbfields['update']); + while ($row = mysql_fetch_array($result)) + { + if (in_array($row['Field'], $all_fields)) + { + $column = $row['Field']; + $column.= ' '.$row['Type']; + if (!isset($row['Null']) or $row['Null'] == '') + { + $column.= ' NOT NULL'; + } + if (isset($row['Default'])) + { + $column.= " default '".$row['Default']."'"; + } + array_push($columns, $column); + } + } + $query = ' +CREATE TEMPORARY TABLE '.$tablename.'_temporary +( +'.implode(",\n", $columns).', +PRIMARY KEY (id) +) +;'; + pwg_query($query); + mass_inserts($tablename, $all_fields, $datas); + // update of images table by joining with temporary table + $query = ' +UPDATE '.$tablename.' AS t1, '.$tablename.'_temporary AS t2 + SET '.implode("\n , ", + array_map( + create_function('$s', 'return "t1.$s = t2.$s";') + , $dbfields['update'])).' + WHERE '.implode("\n AND ", + array_map( + create_function('$s', 'return "t1.$s = t2.$s";') + , $dbfields['primary'])).' +;'; + pwg_query($query); + $query = ' +DROP TABLE '.$tablename.'_temporary +;'; + pwg_query($query); + } +} + +/** + * updates the global_rank of categories under the given id_uppercat + * + * @param int id_uppercat + * @return void + */ +function update_global_rank($id_uppercat = 'all') +{ + $query = ' +SELECT id,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.= ' +;'; + $result = pwg_query($query); + while ($row = mysql_fetch_array($result)) + { + $uppercats_array[$row['id']] = $row['uppercats']; + } + + $datas = array(); + foreach ($uppercats_array as $id => $uppercats) + { + $data = array(); + $data['id'] = $id; + $global_rank = preg_replace('/(\d+)/e', + "\$ranks_array['$1']", + str_replace(',', '.', $uppercats)); + $data['global_rank'] = $global_rank; + array_push($datas, $data); + } + + $fields = array('primary' => array('id'), 'update' => array('global_rank')); + mass_updates(CATEGORIES_TABLE, $fields, $datas); +} ?> diff --git a/admin/include/functions_metadata.php b/admin/include/functions_metadata.php index 2e66606d2..023ab219e 100644 --- a/admin/include/functions_metadata.php +++ b/admin/include/functions_metadata.php @@ -68,18 +68,18 @@ function update_metadata($files) define('CURRENT_DATE', date('Y-m-d')); } - $inserts = array(); + $datas = array(); foreach ($files as $id => $file) { - $insert = array(); - $insert['id'] = $id; - $insert['filesize'] = floor(filesize($file)/1024); + $data = array(); + $data['id'] = $id; + $data['filesize'] = floor(filesize($file)/1024); if ($image_size = @getimagesize($file)) { - $insert['width'] = $image_size[0]; - $insert['height'] = $image_size[1]; + $data['width'] = $image_size[0]; + $data['height'] = $image_size[1]; } if ($conf['use_exif']) @@ -88,11 +88,8 @@ function update_metadata($files) { if (isset($exif['DateTime'])) { - preg_match('/^(\d{4}).(\d{2}).(\d{2})/' - ,$exif['DateTime'] - ,$matches); - $insert['date_creation'] = - "'".$matches[1].'-'.$matches[2].'-'.$matches[3]."'"; + preg_match('/^(\d{4}).(\d{2}).(\d{2})/',$exif['DateTime'],$matches); + $data['date_creation'] = $matches[1].'-'.$matches[2].'-'.$matches[3]; } } } @@ -104,135 +101,23 @@ function update_metadata($files) { foreach (array_keys($iptc) as $key) { - $insert[$key] = "'".addslashes($iptc[$key])."'"; + $data[$key] = "'".addslashes($iptc[$key])."'"; } } } - $insert['date_metadata_update'] = "'".CURRENT_DATE."'"; + $data['date_metadata_update'] = CURRENT_DATE; - array_push($inserts, $insert); + array_push($datas, $data); } - if (count($inserts) > 0) + if (count($datas) > 0) { - $dbfields = array( - 'id','filesize','width','height','name','author','comment' - ,'date_creation','keywords','date_metadata_update' - ); - - // depending on the MySQL version, we use the multi table update or N - // update queries - $query = 'SELECT VERSION() AS version;'; - $row = mysql_fetch_array(pwg_query($query)); - if (version_compare($row['version'],'4.0.4') < 0) - { - // MySQL is prior to version 4.0.4, multi table update feature is not - // available - echo 'MySQL is prior to version 4.0.4, multi table update feature is not available<br />'; - foreach ($inserts as $insert) - { - $query = ' -UPDATE '.IMAGES_TABLE.' - SET '; - foreach (array_diff(array_keys($insert),array('id')) as $num => $key) - { - if ($num > 1) - { - $query.= ', '; - } - $query.= $key.' = '.$insert[$key]; - } - $query.= ' - WHERE id = '.$insert['id'].' -;'; - // echo '<pre>'.$query.'</pre>'; - pwg_query($query); - } - } - else - { - // creation of the temporary table - $query = ' -DESCRIBE '.IMAGES_TABLE.' -;'; - $result = pwg_query($query); - $columns = array(); - while ($row = mysql_fetch_array($result)) - { - if (in_array($row['Field'], $dbfields)) - { - $column = $row['Field']; - $column.= ' '.$row['Type']; - if (!isset($row['Null']) or $row['Null'] == '') - { - $column.= ' NOT NULL'; - } - if (isset($row['Default'])) - { - $column.= " default '".$row['Default']."'"; - } - array_push($columns, $column); - } - } - $query = ' -CREATE TEMPORARY TABLE '.IMAGE_METADATA_TABLE.' -( -'.implode(",\n", $columns).', -PRIMARY KEY (id) -) -;'; - // echo '<pre>'.$query.'</pre>'; - pwg_query($query); - // inserts all found pictures - $query = ' -INSERT INTO '.IMAGE_METADATA_TABLE.' - ('.implode(',', $dbfields).') - VALUES - '; - foreach ($inserts as $insert_id => $insert) - { - $query.= ' -'; - if ($insert_id > 0) - { - $query.= ','; - } - $query.= '('; - foreach ($dbfields as $field_id => $dbfield) - { - if ($field_id > 0) - { - $query.= ','; - } - - if (!isset($insert[$dbfield]) or $insert[$dbfield] == '') - { - $query.= 'NULL'; - } - else - { - $query.= $insert[$dbfield]; - } - } - $query.=')'; - } - $query.= ' -;'; - // echo '<pre>'.$query.'</pre>'; - pwg_query($query); - // update of images table by joining with temporary table - $query = ' -UPDATE '.IMAGES_TABLE.' AS images, '.IMAGE_METADATA_TABLE.' as metadata - SET '.implode("\n , ", - array_map( - create_function('$s', 'return "images.$s = metadata.$s";') - , array_diff($dbfields, array('id')))).' - WHERE images.id = metadata.id -;'; - echo '<pre>'.$query.'</pre>'; - pwg_query($query); - } + $fields = array('primary' => array('id'), + 'update' => array('filesize','width','height','name', + 'author','comment','date_creation', + 'keywords','date_metadata_update')); + mass_updates(IMAGES_TABLE, $fields, $datas); } } diff --git a/admin/update.php b/admin/update.php index ac364e465..45bef3bce 100644 --- a/admin/update.php +++ b/admin/update.php @@ -42,8 +42,7 @@ define('CURRENT_DATE', date('Y-m-d')); * 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. It also updates the global rank - * which is able to order any two categorie in the whole tree + * rank ASC then name ASC for each uppercat. * * @returns void */ @@ -58,6 +57,7 @@ SELECT id, if(id_uppercat is null,\'\',id_uppercat) AS id_uppercat ORDER BY id_uppercat,rank,name ;'; $result = pwg_query($query); + $datas = array(); while ($row = mysql_fetch_array($result)) { if ($row['id_uppercat'] != $current_uppercat) @@ -65,42 +65,12 @@ SELECT id, if(id_uppercat is null,\'\',id_uppercat) AS id_uppercat $current_rank = 0; $current_uppercat = $row['id_uppercat']; } - $query = ' -UPDATE '.CATEGORIES_TABLE.' - SET rank = '.++$current_rank.' - WHERE id = '.$row['id'].' -;'; - pwg_query($query); + $data = array('id' => $row['id'], 'rank' => ++$current_rank); + array_push($datas, $data); } - // global rank update - $query = ' -UPDATE '.CATEGORIES_TABLE.' - SET global_rank = rank - WHERE id_uppercat IS NULL -;'; - pwg_query($query); - $query = ' -SELECT DISTINCT(id_uppercat) - FROM '.CATEGORIES_TABLE.' - WHERE id_uppercat IS NOT NULL -;'; - $result = pwg_query($query); - while ($row = mysql_fetch_array($result)) - { - $query = ' -SELECT global_rank - FROM '.CATEGORIES_TABLE.' - WHERE id = '.$row['id_uppercat'].' -;'; - list($uppercat_global_rank) = mysql_fetch_array(pwg_query($query)); - $query = ' -UPDATE '.CATEGORIES_TABLE.' - SET global_rank = CONCAT(\''.$uppercat_global_rank.'\', \'.\', rank) - WHERE id_uppercat = '.$row['id_uppercat'].' -;'; - pwg_query($query); - } + $fields = array('primary' => array('id'), 'update' => array('rank')); + mass_updates(CATEGORIES_TABLE, $fields, $datas); } function insert_local_category($id_uppercat) @@ -650,6 +620,7 @@ else if (isset($_POST['submit']) echo get_elapsed_time($start,get_moment()).' for update_category(all)<br />'; $start = get_moment(); ordering(); + update_global_rank(); echo get_elapsed_time($start, get_moment()).' for ordering categories<br />'; } // +-----------------------------------------------------------------------+ |