diff options
| -rw-r--r-- | admin/cat_list.php | 33 | ||||
| -rw-r--r-- | admin/cat_modify.php | 16 | ||||
| -rw-r--r-- | admin/cat_options.php | 7 | ||||
| -rw-r--r-- | admin/include/functions.php | 185 | ||||
| -rw-r--r-- | admin/site_manager.php | 21 | ||||
| -rw-r--r-- | include/functions_filter.inc.php | 2 | ||||
| -rw-r--r-- | include/functions_user.inc.php | 22 | ||||
| -rw-r--r-- | install/db/69-database.php | 58 | ||||
| -rw-r--r-- | install/phpwebgallery_structure.sql | 4 | ||||
| -rw-r--r-- | search.php | 4 |
10 files changed, 150 insertions, 202 deletions
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 ); diff --git a/include/functions_filter.inc.php b/include/functions_filter.inc.php index c52bb7517..24efb0994 100644 --- a/include/functions_filter.inc.php +++ b/include/functions_filter.inc.php @@ -48,7 +48,7 @@ function update_cats_with_filtered_data(&$cats) if ($filter['enabled']) { - $upd_fields = array('max_date_last', 'count_images', 'count_categories', 'nb_images'); + $upd_fields = array('date_last', 'max_date_last', 'count_images', 'count_categories', 'nb_images'); foreach ($cats as $cat_id => $category) { diff --git a/include/functions_user.inc.php b/include/functions_user.inc.php index cdf751979..6630e2dae 100644 --- a/include/functions_user.inc.php +++ b/include/functions_user.inc.php @@ -582,12 +582,10 @@ function compute_categories_data(&$cats) */ function get_computed_categories($userdata, $filter_days=null) { - $group_by = ''; - $query = 'SELECT c.id cat_id, global_rank'; // Count by date_available to avoid count null $query .= ', - MAX(date_available) cat_date_last, COUNT(date_available) cat_nb_images + MAX(date_available) date_last, COUNT(date_available) nb_images FROM '.CATEGORIES_TABLE.' as c LEFT JOIN '.IMAGE_CATEGORY_TABLE.' AS ic ON ic.category_id = c.id LEFT JOIN '.IMAGES_TABLE.' AS i @@ -598,7 +596,6 @@ FROM '.CATEGORIES_TABLE.' as c { $query .= ' AND i.date_available > SUBDATE(CURRENT_DATE,INTERVAL '.$filter_days.' DAY)'; } - $group_by = 'c.id'; if ( !empty($userdata['forbidden_categories']) ) { @@ -606,11 +603,8 @@ FROM '.CATEGORIES_TABLE.' as c WHERE c.id NOT IN ('.$userdata['forbidden_categories'].')'; } - if ( !empty($group_by) ) - { - $query.= ' - GROUP BY '.$group_by; - } + $query.= ' + GROUP BY c.id'; $result = pwg_query($query); @@ -619,8 +613,8 @@ FROM '.CATEGORIES_TABLE.' as c { $row['user_id'] = $userdata['id']; $row['count_categories'] = 0; - $row['count_images'] = $row['cat_nb_images']; - $row['max_date_last'] = $row['cat_date_last']; + $row['count_images'] = (int)$row['nb_images']; + $row['max_date_last'] = $row['date_last']; $cats += array($row['cat_id'] => $row); } @@ -639,9 +633,7 @@ FROM '.CATEGORIES_TABLE.' as c { // Re-init counters $category['count_categories'] = 0; - $category['count_images'] = $category['cat_nb_images']; - // next line for update_cats_with_filtered_data - $category['nb_images'] = $category['cat_nb_images']; + $category['count_images'] = (int)$category['nb_images']; // Keep category $cats[$category['cat_id']] = $category; } @@ -676,7 +668,7 @@ DELETE FROM '.USER_CACHE_CATEGORIES_TABLE.' array ( 'user_id', 'cat_id', - 'max_date_last', 'count_images', 'count_categories' + 'date_last', 'max_date_last', 'nb_images', 'count_images', 'count_categories' ), $cats ); diff --git a/install/db/69-database.php b/install/db/69-database.php new file mode 100644 index 000000000..dad302401 --- /dev/null +++ b/install/db/69-database.php @@ -0,0 +1,58 @@ +<?php +// +-----------------------------------------------------------------------+ +// | Piwigo - a PHP based picture gallery | +// +-----------------------------------------------------------------------+ +// | Copyright(C) 2008 Piwigo Team http://piwigo.org | +// | Copyright(C) 2003-2008 PhpWebGallery Team http://phpwebgallery.net | +// | Copyright(C) 2002-2003 Pierrick LE GALL http://le-gall.net/pierrick | +// +-----------------------------------------------------------------------+ +// | This program is free software; you can redistribute it and/or modify | +// | it under the terms of the GNU General Public License as published by | +// | the Free Software Foundation | +// | | +// | This program is distributed in the hope that it will be useful, but | +// | WITHOUT ANY WARRANTY; without even the implied warranty of | +// | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU | +// | General Public License for more details. | +// | | +// | You should have received a copy of the GNU General Public License | +// | along with this program; if not, write to the Free Software | +// | Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, | +// | USA. | +// +-----------------------------------------------------------------------+ + +if (!defined('PHPWG_ROOT_PATH')) +{ + die('Hacking attempt!'); +} + +$upgrade_description = 'Move #categories.date_last and nb_images to #user_cache_categories'; + +include_once(PHPWG_ROOT_PATH.'include/constants.php'); + +// +-----------------------------------------------------------------------+ +// | Upgrade content | +// +-----------------------------------------------------------------------+ + +$query = ' +ALTER TABLE '.USER_CACHE_CATEGORIES_TABLE.' + ADD COLUMN date_last datetime default NULL AFTER cat_id, + ADD COLUMN nb_images mediumint(8) unsigned NOT NULL default 0 AFTER max_date_last'; +pwg_query($query); + +$query = ' +ALTER TABLE '.CATEGORIES_TABLE.' + DROP COLUMN date_last, + DROP COLUMN nb_images + '; +pwg_query($query); + +invalidate_user_cache(); // just to force recalculation + +echo +"\n" +.'"'.$upgrade_description.'"'.' ended' +."\n" +; + +?> diff --git a/install/phpwebgallery_structure.sql b/install/phpwebgallery_structure.sql index 01626af0c..452d9f7a7 100644 --- a/install/phpwebgallery_structure.sql +++ b/install/phpwebgallery_structure.sql @@ -22,8 +22,6 @@ CREATE TABLE `phpwebgallery_caddie` ( DROP TABLE IF EXISTS `phpwebgallery_categories`; CREATE TABLE `phpwebgallery_categories` ( `id` smallint(5) unsigned NOT NULL auto_increment, - `date_last` datetime default NULL, - `nb_images` mediumint(8) unsigned NOT NULL default '0', `name` varchar(255) NOT NULL default '', `id_uppercat` smallint(5) unsigned default NULL, `comment` text, @@ -342,7 +340,9 @@ DROP TABLE IF EXISTS `phpwebgallery_user_cache_categories`; CREATE TABLE `phpwebgallery_user_cache_categories` ( `user_id` smallint(5) NOT NULL default '0', `cat_id` smallint(5) unsigned NOT NULL default '0', + `date_last` datetime default NULL, `max_date_last` datetime default NULL, + `nb_images` mediumint(8) unsigned NOT NULL default '0', `count_images` mediumint(8) unsigned default '0', `count_categories` mediumint(8) unsigned default '0', PRIMARY KEY (`user_id`,`cat_id`) diff --git a/search.php b/search.php index 36a93814a..fe161f476 100644 --- a/search.php +++ b/search.php @@ -173,7 +173,7 @@ $template->assign( array( 'F_SEARCH_ACTION' => 'search.php', 'U_HELP' => PHPWG_ROOT_PATH.'popuphelp.php?page=search', - + 'month_list' => $month_list, 'START_DAY_SELECTED' => @$_POST['start_day'], 'START_MONTH_SELECTED' => @$_POST['start_month'], @@ -200,7 +200,7 @@ if (count($available_tags) > 0) //------------------------------------------------------------- categories form $query = ' -SELECT name,id,date_last,nb_images,global_rank,uppercats +SELECT id,name,global_rank,uppercats FROM '.CATEGORIES_TABLE.' '.get_sql_condition_FandF ( |