From c3614fd12c2296a7410d6bd26f35f988a4dd49f1 Mon Sep 17 00:00:00 2001 From: rvelices Date: Mon, 19 Feb 2007 16:25:47 +0000 Subject: web service: added method to query search elements picture: small correction on my last commit monthly calendar nice view: always use getimagesize instead of guessing the size query search: improved results (filename is searched separately) and sometimes less sql queries than before git-svn-id: http://piwigo.org/svn/trunk@1837 68402e56-0260-453c-a942-63ccdbb3a9ee --- include/calendar_monthly.class.php | 6 +- include/config_default.inc.php | 4 +- include/functions_search.inc.php | 136 +++++++++++++++++++++---------------- include/ws_functions.inc.php | 112 ++++++++++++++++++++++++++++++ picture.php | 2 +- ws.php | 21 ++++++ 6 files changed, 216 insertions(+), 65 deletions(-) diff --git a/include/calendar_monthly.class.php b/include/calendar_monthly.class.php index 0dfdbcf63..c7822394b 100644 --- a/include/calendar_monthly.class.php +++ b/include/calendar_monthly.class.php @@ -1,7 +1,7 @@ date_field.')-1 a } else { - // first try to guess thumbnail size +/* // first try to guess thumbnail size if ( !empty($items[$day]['width']) ) { $tn_size = get_picture_size( $items[$day]['width'], $items[$day]['height'], $conf['tn_width'], $conf['tn_height'] ); } - else + else*/ {// item not an image (tn is either mime type or an image) $thumb = get_thumbnail_path($items[$day]); $tn_size = @getimagesize($thumb); diff --git a/include/config_default.inc.php b/include/config_default.inc.php index 110accd46..cd02ce597 100644 --- a/include/config_default.inc.php +++ b/include/config_default.inc.php @@ -115,9 +115,7 @@ $conf['calendar_show_empty'] = true; // width and the height of a cell in the monthly calendar when viewing a // given month. a value of 0 means that the pretty view is not shown. // a good suggestion would be to have the width and the height equal -// and smaller than tn_width and tn_height. NOTE THAT tn_width AND tn_height -// MUST CORRESPOND APPROXIMATIVELY TO YOUR REAL THUMBNAIL SIZE, OTHERWISE -// THE IMAGES WILL NOT SHOW CORRECTLY +// and smaller than tn_width and tn_height. $conf['calendar_month_cell_width'] =80; $conf['calendar_month_cell_height']=80; diff --git a/include/functions_search.inc.php b/include/functions_search.inc.php index 24b676e1f..1f7469021 100644 --- a/include/functions_search.inc.php +++ b/include/functions_search.inc.php @@ -2,9 +2,8 @@ // +-----------------------------------------------------------------------+ // | PhpWebGallery - a PHP based picture gallery | // | Copyright (C) 2002-2003 Pierrick LE GALL - pierrick@phpwebgallery.net | -// | Copyright (C) 2003-2006 PhpWebGallery Team - http://phpwebgallery.net | +// | Copyright (C) 2003-2007 PhpWebGallery Team - http://phpwebgallery.net | // +-----------------------------------------------------------------------+ -// | branch : BSF (Best So Far) // | file : $Id$ // | last update : $Date$ // | last modifier : $Author$ @@ -301,40 +300,18 @@ function get_qsearch_like_clause($q, $field) */ function get_quick_search_results($q) { - global $user, $page, $filter; + global $page; $search_results = array(); - - // first search tag names corresponding to the query $q. we could also search - // tags later during the big join, but for the sake of the performance and - // because tags have only a simple name we do it separately - $q_like_clause = get_qsearch_like_clause($q, 'CONVERT(name, CHAR)' ); - $by_tag_weights=array(); - if (!empty($q_like_clause)) + $q = trim($q); + if (empty($q)) { - $query = ' -SELECT id - FROM '.TAGS_TABLE.' - WHERE '.$q_like_clause; - $tag_ids = array_from_query( $query, 'id'); - if (!empty($tag_ids)) - { // we got some tags - $query = ' -SELECT image_id, COUNT(tag_id) AS q - FROM '.IMAGE_TAG_TABLE.' - WHERE tag_id IN ('.implode(',',$tag_ids).') - GROUP BY image_id'; - $result = pwg_query($query); - while ($row = mysql_fetch_assoc($result)) - { // weight is important when sorting images by relevance - $by_tag_weights[(int)$row['image_id']] = $row['q']; - } - } + $search_results['items'] = array(); + return $search_results; } - // prepare the big join on images, comments and categories $query = ' SELECT - i.id, i.file, CAST( CONCAT_WS(" ", + i.id, CAST( CONCAT_WS(" ", IFNULL(i.name,""), IFNULL(i.comment,""), IFNULL(GROUP_CONCAT(DISTINCT co.content),""), @@ -356,7 +333,7 @@ FROM ( ( 'forbidden_categories' => 'category_id', 'visible_categories' => 'category_id', - 'visible_images' => 'ic.image_id' + 'visible_images' => 'i.id' ), 'WHERE' ).' @@ -365,35 +342,75 @@ GROUP BY i.id'; $query = 'SELECT id, MATCH(ft) AGAINST( "'.$q.'" IN BOOLEAN MODE) AS q FROM ('.$query.') AS Y WHERE MATCH(ft) AGAINST( "'.$q.'" IN BOOLEAN MODE)'; - //also inlcude the file name (but avoid full text which is slower because - //the filename in pwg doesn't have spaces so full text is meaningless anyway) - $q_like_clause = get_qsearch_like_clause($q, 'file' ); - if (! empty($q_like_clause) ) - { - $query .= ' OR '.$q_like_clause; - } - $by_weights=array(); $result = pwg_query($query); while ($row = mysql_fetch_array($result)) - { - $by_weights[(int)$row['id']] = $row['q'] ? $row['q'] : 0; + { // weight is important when sorting images by relevance + if ($row['q']) + { + $by_weights[(int)$row['id']] = 2*$row['q']; + } } - // finally merge the results (tags and big join) sorted by "relevance" - foreach ( $by_weights as $image=>$w ) + $permissions_checked = true; + // now search the file name separately (not done in full text because slower + // and the filename in pwg doesn't have spaces so full text is meaningless ) + $q_like_clause = get_qsearch_like_clause($q, 'file' ); + if (!empty($q_like_clause)) { - $by_tag_weights[$image] = 2*$w+ (isset($by_tag_weights[$image])?$by_tag_weights[$image]:0); + $query = ' +SELECT id + FROM '.IMAGES_TABLE.' + WHERE '.$q_like_clause. + get_sql_condition_FandF + ( + array + ( + 'visible_images' => 'id' + ), + 'AND' + ); + $result = pwg_query($query); + while ($row = mysql_fetch_assoc($result)) + { // weight is important when sorting images by relevance + $id=(int)$row['id']; + @$by_weights[$id] += 2; + $permissions_checked = false; + } } - //at this point, found images might contain images not allowed for the user - if ( empty($by_tag_weights) or isset($page['super_order_by']) ) + // now search tag names corresponding to the query $q. we could have searched + // tags earlier during the big join, but for the sake of the performance and + // because tags have only a simple name we do it separately + $q_like_clause = get_qsearch_like_clause($q, 'CONVERT(name, CHAR)' ); + if (!empty($q_like_clause)) { - // no aditionnal query here for permissions (will be done by section_init - // while sorting items as the user requested it) - $search_results['items'] = array_keys($by_tag_weights); + $query = ' +SELECT id + FROM '.TAGS_TABLE.' + WHERE '.$q_like_clause; + $tag_ids = array_from_query( $query, 'id'); + if (!empty($tag_ids)) + { // we got some tags + $query = ' +SELECT image_id, COUNT(tag_id) AS q + FROM '.IMAGE_TAG_TABLE.' + WHERE tag_id IN ('.implode(',',$tag_ids).') + GROUP BY image_id'; + $result = pwg_query($query); + while ($row = mysql_fetch_assoc($result)) + { // weight is important when sorting images by relevance + $image_id=(int)$row['image_id']; + @$by_weights[$image_id] += $row['q']; + $permissions_checked = false; + } + } } - else + + //at this point, found images might contain images not allowed for the user + if ( !$permissions_checked + and !empty($by_weights) + and !isset($page['super_order_by']) ) { // before returning the result "as is", make sure the user has the // permissions for every item @@ -401,25 +418,28 @@ WHERE MATCH(ft) AGAINST( "'.$q.'" IN BOOLEAN MODE)'; SELECT DISTINCT(id) FROM '.IMAGES_TABLE.' INNER JOIN '.IMAGE_CATEGORY_TABLE.' AS ic ON id = ic.image_id - WHERE id IN ('.implode(',', array_keys($by_tag_weights) ).') + WHERE id IN ('.implode(',', array_keys($by_weights) ).') '.get_sql_condition_FandF ( array ( 'forbidden_categories' => 'category_id', 'visible_categories' => 'category_id', - 'visible_images' => 'ic.image_id' + 'visible_images' => 'id' ), 'AND' ); $allowed_image_ids = array_from_query( $query, 'id'); - $by_tag_weights = array_intersect_key($by_tag_weights, array_flip($allowed_image_ids)); - arsort($by_tag_weights, SORT_NUMERIC); - $search_results = array( - 'items'=>array_keys($by_tag_weights), - 'as_is'=>1 - ); + $by_weights = array_intersect_key($by_weights, array_flip($allowed_image_ids)); + $permissions_checked = true; + } + arsort($by_weights, SORT_NUMERIC); + if ( $permissions_checked ) + { + $search_results['as_is']=1; } + + $search_results['items'] = array_keys($by_weights); return $search_results; } diff --git a/include/ws_functions.inc.php b/include/ws_functions.inc.php index 1c71a6900..01e749d6f 100644 --- a/include/ws_functions.inc.php +++ b/include/ws_functions.inc.php @@ -603,6 +603,118 @@ SELECT COUNT(rate) AS count return new PwgNamedStruct('image',$ret, null, array('name','comment') ); } +/** + * returns a list of elements corresponding to a query search + */ +function ws_images_search($params, &$service) +{ + global $page; + $images = array(); + include_once( PHPWG_ROOT_PATH .'include/functions_search.inc.php' ); + include_once(PHPWG_ROOT_PATH.'include/functions_picture.inc.php'); + + $where_clauses = ws_std_image_sql_filter( $params ); + $order_by = ws_std_image_sql_order($params); + + if ( !empty($where_clauses) and !empty($order_by) ) + { + $page['super_order_by']=1; // quick_search_result might be faster + } + $search_result = get_quick_search_results($params['query']); + + global $image_ids; //needed for sorting by rank (usort) + if ( ( !isset($search_result['as_is']) + or !empty($where_clauses) + or !empty($order_by) ) + and !empty($search_result['items']) ) + { + $where_clauses[] = 'id IN (' + .wordwrap(implode(', ', $search_result['items']), 80, "\n") + .')'; + $where_clauses[] = get_sql_condition_FandF( + array + ( + 'forbidden_categories' => 'category_id', + 'visible_categories' => 'category_id', + 'visible_images' => 'id' + ), + '', true + ); + $query = ' +SELECT DISTINCT id FROM '.IMAGES_TABLE.' INNER JOIN '.IMAGE_CATEGORY_TABLE.' ON id=image_id + WHERE '.implode(' + AND ', $where_clauses); + if (!empty($order_by)) + { + $query .= ' + ORDER BY '.$order_by; + } + $image_ids = array_from_query($query, 'id'); + global $ranks; + $ranks = array_flip( $search_result['items'] ); + usort( + $image_ids, + create_function('$i1,$i2', 'global $ranks; return $ranks[$i1]-$ranks[$i2];') + ); + unset ($ranks); + } + else + { + $image_ids = $search_result['items']; + } + + $image_ids = array_slice($image_ids, + $params['page']*$params['per_page'], + $params['per_page'] ); + + if ( count($image_ids) ) + { + $query = ' +SELECT * FROM '.IMAGES_TABLE.' + WHERE id IN (' + .wordwrap(implode(', ', $image_ids), 80, "\n") + .')'; + + $result = pwg_query($query); + while ($row = mysql_fetch_assoc($result)) + { + $image = array(); + foreach ( array('id', 'width', 'height', 'hit') as $k ) + { + if (isset($row[$k])) + { + $image[$k] = (int)$row[$k]; + } + } + foreach ( array('name', 'file') as $k ) + { + $image[$k] = $row[$k]; + } + $image = array_merge( $image, ws_std_get_urls($row) ); + array_push($images, $image); + } + + $image_ids = array_flip($image_ids); + usort( + $images, + create_function('$i1,$i2', 'global $image_ids; return $image_ids[$i1["id"]]-$image_ids[$i2["id"]];') + ); + } + + + return array( 'images' => + array ( + WS_XML_ATTRIBUTES => + array( + 'page' => $params['page'], + 'per_page' => $params['per_page'], + 'count' => count($images) + ), + WS_XML_CONTENT => new PwgNamedArray($images, 'image', + array('id', 'tn_url', 'element_url', 'file','width','height','hit') ) + ) + ); +} /** * perform a login (web service method) diff --git a/picture.php b/picture.php index 3d258fb32..6b5775560 100644 --- a/picture.php +++ b/picture.php @@ -754,7 +754,7 @@ if ( count($tags) ) $infos['INFO_TAGS'] = ''; foreach ($tags as $num => $tag) { - $infos['INFO_TAGS'] .= $num ? ' ,' : ''; + $infos['INFO_TAGS'] .= $num ? ', ' : ''; $infos['INFO_TAGS'] .= '