From 7942dccf9b682d359a89636528e96af5b38327da Mon Sep 17 00:00:00 2001 From: rvelices Date: Thu, 1 May 2008 01:12:34 +0000 Subject: - move #categories.date_last and nb_images to #user_cache_categories git-svn-id: http://piwigo.org/svn/trunk@2324 68402e56-0260-453c-a942-63ccdbb3a9ee --- admin/cat_list.php | 33 +++----- admin/cat_modify.php | 16 ++-- admin/cat_options.php | 7 +- admin/include/functions.php | 185 ++++++++++---------------------------------- admin/site_manager.php | 21 +++-- 5 files changed, 80 insertions(+), 182 deletions(-) (limited to 'admin') diff --git a/admin/cat_list.php b/admin/cat_list.php index 1ec2d4422..b8407c2f8 100644 --- a/admin/cat_list.php +++ b/admin/cat_list.php @@ -170,7 +170,7 @@ $template->assign(array( $categories = array(); $query = ' -SELECT id, name, permalink, dir, rank, nb_images, status +SELECT id, name, permalink, dir, rank, status FROM '.CATEGORIES_TABLE; if (!isset($_GET['parent_id'])) { @@ -185,34 +185,23 @@ else $query.= ' ORDER BY rank ASC ;'; -$result = pwg_query($query); -while ($row = mysql_fetch_array($result)) -{ - $categories[$row['id']] = $row; - // by default, let's consider there is no sub-categories. This will be - // calculated after. - $categories[$row['id']]['nb_subcats'] = 0; -} +$categories = hash_from_query($query, 'id'); -if (count($categories) > 0) +// get the categories containing images directly +$categories_with_images = array(); +if ( count($categories) ) { $query = ' -SELECT id_uppercat, COUNT(*) AS nb_subcats - FROM '. CATEGORIES_TABLE.' - WHERE id_uppercat IN ('.implode(',', array_keys($categories)).') - GROUP BY id_uppercat -;'; - $result = pwg_query($query); - while ($row = mysql_fetch_array($result)) - { - $categories[$row['id_uppercat']]['nb_subcats'] = $row['nb_subcats']; - } +SELECT DISTINCT category_id + FROM '.IMAGE_CATEGORY_TABLE.' + WHERE category_id IN ('.implode(',', array_keys($categories)).')'; + $categories_with_images = array_flip( array_from_query($query, 'category_id') ); } $template->assign('categories', array()); +$base_url = get_root_url().'admin.php?page='; foreach ($categories as $category) { - $base_url = PHPWG_ROOT_PATH.'admin.php?page='; $cat_list_url = $base_url.'cat_list'; $self_url = $cat_list_url; @@ -244,7 +233,7 @@ foreach ($categories as $category) $tpl_cat['U_DELETE'] = $self_url.'&delete='.$category['id']; } - if ($category['nb_images'] > 0) + if ( array_key_exists($category['id'], $categories_with_images) ) { $tpl_cat['U_MANAGE_ELEMENTS']= $base_url.'element_set&cat='.$category['id']; diff --git a/admin/cat_modify.php b/admin/cat_modify.php index f2b16566b..90310527f 100644 --- a/admin/cat_modify.php +++ b/admin/cat_modify.php @@ -203,6 +203,12 @@ foreach (array('comment','dir','site_id', 'id_uppercat') as $nullable) $category['is_virtual'] = empty($category['dir']) ? true : false; +$query = 'SELECT DISTINCT category_id + FROM '.IMAGE_CATEGORY_TABLE.' + WHERE category_id = '.$_GET['cat_id'].' + LIMIT 1'; +$result = pwg_query($query); +$category['has_images'] = mysql_num_rows($result)>0 ? true : false; // Navigation path $navigation = get_cat_display_name_cache( @@ -264,7 +270,7 @@ if ('private' == $category['status']) } // manage category elements link -if ($category['nb_images'] > 0) +if ($category['has_images']) { $template->assign( 'U_MANAGE_ELEMENTS', $base_url.'element_set&cat='.$category['id'] @@ -344,7 +350,7 @@ for ($i=0; $i<3; $i++) // 3 fields // representant management -if ($category['nb_images'] > 0 +if ($category['has_images'] or !empty($category['representative_picture_id'])) { $tpl_representant = array(); @@ -371,14 +377,14 @@ SELECT id,tn_ext,path } // can the admin choose to set a new random representant ? - $tpl_representant['ALLOW_SET_RANDOM'] = ($category['nb_images']>0) ? true : false; + $tpl_representant['ALLOW_SET_RANDOM'] = ($category['has_images']) ? true : false; // can the admin delete the current representant ? if ( - ($category['nb_images'] > 0 + ($category['has_images'] and $conf['allow_random_representative']) or - ($category['nb_images'] == 0 + (!$category['has_images'] and !empty($category['representative_picture_id']))) { $tpl_representant['ALLOW_DELETE'] = true; diff --git a/admin/cat_options.php b/admin/cat_options.php index 7f4b3f5e1..ca07d37a3 100644 --- a/admin/cat_options.php +++ b/admin/cat_options.php @@ -286,10 +286,9 @@ SELECT id,name,uppercats,global_rank WHERE representative_picture_id IS NOT NULL ;'; $query_false = ' -SELECT id,name,uppercats,global_rank - FROM '.CATEGORIES_TABLE.' - WHERE nb_images != 0 - AND representative_picture_id IS NULL +SELECT DISTINCT id,name,uppercats,global_rank + FROM '.CATEGORIES_TABLE.' INNER JOIN '.IMAGE_CATEGORY_TABLE.' ON id=category_id + WHERE representative_picture_id IS NULL ;'; $template->assign( array( diff --git a/admin/include/functions.php b/admin/include/functions.php index c6f72c5f4..94ac4c962 100644 --- a/admin/include/functions.php +++ b/admin/include/functions.php @@ -251,175 +251,72 @@ DELETE FROM '.USERS_TABLE.' } /** - * updates calculated informations about a set of categories : date_last and - * nb_images. It also verifies that the representative picture is really - * linked to the category. Optionnaly recursive. + * Verifies that the representative picture really exists in the db and + * picks up a random represantive if possible and based on config. * * @param mixed category id - * @param boolean recursive * @returns void */ -function update_category($ids = 'all', $recursive = false) +function update_category($ids = 'all') { global $conf; - // retrieving all categories to update - $cat_ids = array(); - - $query = ' -SELECT id - FROM '.CATEGORIES_TABLE; - if (is_array($ids)) - { - if ($recursive) - { - foreach ($ids as $num => $id) - { - if ($num == 0) - { - $query.= ' - WHERE '; - } - else - { - $query.= ' - OR '; - } - $query.= 'uppercats REGEXP \'(^|,)'.$id.'(,|$)\''; - } - } - else - { - $query.= ' - WHERE id IN ('.wordwrap(implode(', ', $ids), 80, "\n").')'; - } - } - $query.= ' -;'; - $cat_ids = array_unique(array_from_query($query, 'id')); - - if (count($cat_ids) == 0) + if ($ids=='all') { - return false; + $where_cats = '1=1'; } - - // calculate informations about categories retrieved - $query = ' -SELECT category_id, - COUNT(image_id) AS nb_images, - MAX(date_available) AS date_last - FROM '.IMAGES_TABLE.' INNER JOIN '.IMAGE_CATEGORY_TABLE.' ON id = image_id - WHERE category_id IN ('.wordwrap(implode(', ', $cat_ids), 80, "\n").') - GROUP BY category_id -;'; - $result = pwg_query($query); - $datas = array(); - $query_ids = array(); - while ( $row = mysql_fetch_array( $result ) ) + elseif ( !is_array($ids) ) { - array_push($query_ids, $row['category_id']); - - array_push( - $datas, - array( - 'id' => $row['category_id'], - 'date_last' => $row['date_last'], - 'nb_images' => $row['nb_images'] - ) - ); + $where_cats = '%s='.$ids; } - // if all links between a category and elements have disappeared, no line - // is returned but the update must be done ! - foreach (array_diff($cat_ids, $query_ids) as $id) + else { - array_push($datas, array('id' => $id, 'nb_images' => 0)); + if (count($ids) == 0) + { + return false; + } + $where_cats = '%s IN('.wordwrap(implode(', ', $ids), 120, "\n").')'; } - - $fields = array('primary' => array('id'), - 'update' => array('date_last', 'nb_images')); - mass_updates(CATEGORIES_TABLE, $fields, $datas); - - // representative pictures - if (count($cat_ids) > 0) - { - // find all categories where the setted representative is not possible : - // the picture does not exist - $query = ' -SELECT c.id + + // find all categories where the setted representative is not possible : + // the picture does not exist + $query = ' +SELECT DISTINCT c.id FROM '.CATEGORIES_TABLE.' AS c LEFT JOIN '.IMAGES_TABLE.' AS i ON c.representative_picture_id = i.id WHERE representative_picture_id IS NOT NULL - AND c.id IN ('.wordwrap(implode(', ', $cat_ids), 80, "\n").') + AND '.sprintf($where_cats, 'c.id').' AND i.id IS NULL ;'; - $wrong_representant = array_from_query($query, 'id'); - - if ($conf['allow_random_representative']) - { - if (count($wrong_representant) > 0) - { - $query = ' -UPDATE '.CATEGORIES_TABLE.' - SET representative_picture_id = NULL - WHERE id IN ('.wordwrap(implode(', ', $wrong_representant), 80, "\n").') -;'; - pwg_query($query); - } - } - else - { - $to_null = array(); - $to_rand = array(); - - if (count($wrong_representant) > 0) - { - // among the categories with an unknown representant, we dissociate - // categories containing pictures and categories containing no - // pictures. Indeed, the representant must set to NULL if no picture - // in the category and set to a random picture otherwise. - $query = ' -SELECT id - FROM '.CATEGORIES_TABLE.' - WHERE id IN ('.wordwrap(implode(', ', $wrong_representant), 80, "\n").') - AND nb_images = 0 -;'; - $to_null = array_from_query($query, 'id'); - $to_rand = array_diff($wrong_representant, $to_null); - } + $wrong_representant = array_from_query($query, 'id'); - if (count($to_null) > 0) - { - $query = ' + if (count($wrong_representant) > 0) + { + $query = ' UPDATE '.CATEGORIES_TABLE.' SET representative_picture_id = NULL - WHERE id IN ('.wordwrap(implode(', ', $to_null), 80, "\n").') + WHERE id IN ('.wordwrap(implode(', ', $wrong_representant), 120, "\n").') ;'; - pwg_query($query); - } + pwg_query($query); + } - // If the random representant is not allowed, we need to find - // categories with elements and with no representant. Those categories - // must be added to the list of categories to set to a random - // representant. - $query = ' -SELECT id - FROM '.CATEGORIES_TABLE.' + if (!$conf['allow_random_representative']) + { + // If the random representant is not allowed, we need to find + // categories with elements and with no representant. Those categories + // must be added to the list of categories to set to a random + // representant. + $query = ' +SELECT DISTINCT id + FROM '.CATEGORIES_TABLE.' INNER JOIN '.IMAGE_CATEGORY_TABLE.' + ON id = category_id WHERE representative_picture_id IS NULL - AND nb_images != 0 - AND id IN ('.wordwrap(implode(', ', $cat_ids), 80, "\n").') + AND '.sprintf($where_cats, 'category_id').' ;'; - $to_rand = - array_unique( - array_merge( - $to_rand, - array_from_query($query, 'id') - ) - ); - - if (count($to_rand) > 0) - { - set_random_representant($to_rand); - } + $to_rand = array_from_query($query, 'id'); + if (count($to_rand) > 0) + { + set_random_representant($to_rand); } } } diff --git a/admin/site_manager.php b/admin/site_manager.php index abe9f811d..def9fe579 100644 --- a/admin/site_manager.php +++ b/admin/site_manager.php @@ -220,11 +220,18 @@ if ( is_file(PHPWG_ROOT_PATH.'listing.xml') ) } $query = ' -SELECT s.*, COUNT(c.id) AS nb_categories, SUM(c.nb_images) AS nb_images - FROM '.SITES_TABLE.' AS s LEFT JOIN '.CATEGORIES_TABLE.' AS c - ON s.id=c.site_id - GROUP BY s.id'. -';'; +SELECT c.site_id, COUNT(DISTINCT c.id) AS nb_categories, COUNT(i.id) AS nb_images + FROM '.CATEGORIES_TABLE.' AS c LEFT JOIN '.IMAGES_TABLE.' AS i + ON c.id=i.storage_category_id + WHERE c.site_id IS NOT NULL + GROUP BY c.site_id +;'; +$sites_detail = hash_from_query($query, 'site_id'); + +$query = ' +SELECT * + FROM '.SITES_TABLE.' +;'; $result = pwg_query($query); while ($row = mysql_fetch_array($result)) @@ -243,8 +250,8 @@ while ($row = mysql_fetch_array($result)) array( 'NAME' => $row['galleries_url'], 'TYPE' => l10n( $is_remote ? 'site_remote' : 'site_local' ), - 'CATEGORIES' => $row['nb_categories'], - 'IMAGES' => isset($row['nb_images']) ? $row['nb_images'] : 0, + 'CATEGORIES' => (int)@$sites_detail[$row['id']]['nb_categories'], + 'IMAGES' => (int)@$sites_detail[$row['id']]['nb_images'], 'U_SYNCHRONIZE' => $update_url ); -- cgit v1.2.3